Mysql
為什麼 Mysql 在連接時不使用索引
我有一個查詢將表 SPACES 與 3 行連接起來。但是 MySql 沒有使用索引。
EXPLAIN SELECT environmen0_.description AS col_0_0_, environmen0_.fk_user_profile_id AS col_1_0_, environmen0_.id AS col_2_0_, environmen0_.ukey AS col_3_0_, environmen0_.environment_name AS col_4_0_, lifecycle5_.state AS col_6_0_, environmen0_.updated_date AS col_7_0_, environmen0_.updated_by AS col_8_0_, usersecuri1_.id AS id1_238_, usersecuri1_.primary_entity AS primary_2_238_, space4_.alias_name AS type_alias FROM ENVIRONMENTS environmen0_ STRAIGHT_JOIN SPACES space4_ ON environmen0_.fk_space_id = space4_.id STRAIGHT_JOIN USER_SECURITY_PROFILES usersecuri1_ ON environmen0_.fk_user_profile_id = usersecuri1_.id STRAIGHT_JOIN SECURITY_ROLE_PERMISSIONS permission2_ ON usersecuri1_.id = permission2_.fk_profile_id WHERE permission2_.permission_read = 1 AND (permission2_.fk_security_role_id IN (2, 1)) AND 1 = 1 AND 1 = 1 AND 1 = 1 AND 1 = 1 AND (environmen0_.operation_scope = 'design' OR environmen0_.operation_scope = 'design') AND 1 = 1 AND 1 = 1 GROUP BY environmen0_.id ORDER BY environmen0_.id ASC LIMIT 25MySql為什麼拒絕使用索引？連接類型為ALL，該問題導致ENVIRONMENTS表的 JOIN 類型將為ALL。如果我從選擇此行中刪除
space4_.alias_name AS type_alias解釋輸出
CREATE TABLE 的輸出
CREATE TABLE `environments` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `alias_name` varchar(255) DEFAULT NULL, `created_date` datetime NOT NULL, `created_by` varchar(255) DEFAULT NULL, `description` longtext, `ukey` varchar(255) DEFAULT NULL, `label` varchar(255) DEFAULT NULL, `namespace` varchar(255) DEFAULT NULL, `updated_date` datetime NOT NULL, `updated_by` varchar(255) DEFAULT NULL, `vstamp` int(11) DEFAULT NULL, `builder_view` longtext, `environment_name` varchar(255) NOT NULL, `operation_scope` varchar(255) DEFAULT NULL, `fk_provision_profile_id` bigint(20) DEFAULT NULL, `fk_user_profile_id` bigint(20) DEFAULT NULL, `fk_mainApplication_id` bigint(20) DEFAULT NULL, `fk_organization_id` bigint(20) DEFAULT NULL, `fk_security_profile_id` bigint(20) NOT NULL, `fk_space_id` bigint(20) NOT NULL, `fk_state_id` bigint(20) DEFAULT NULL, `fk_environment_type` bigint(20) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `UK_ad600i5g7rewdxmuc1elmekdh` (`ukey`), KEY `FK_pxiie9k1gm297ejhr5y8k56a9` (`fk_provision_profile_id`), KEY `FK_35i1f8xibrr2apesdu9ahcpnt` (`fk_user_profile_id`), KEY `FK_b959faehk11qrxb06die67ukm` (`fk_mainApplication_id`), KEY `FK_554d71l9bovc8u804wghgyo38` (`fk_organization_id`), KEY `FK_bomv382tbq3rf4ae54ewkr8rs` (`fk_security_profile_id`), KEY `FK_9xype9ap7gcmdm5t077gdgor3` (`fk_space_id`), KEY `FK_th5888gw8704s2bfcw0r8hmr2` (`fk_state_id`), KEY `FK_aryfg8vfjdrhxrdxd3ykptsfq` (`fk_environment_type`), CONSTRAINT `FK_35i1f8xibrr2apesdu9ahcpnt` FOREIGN KEY (`fk_user_profile_id`) REFERENCES `user_security_profiles` (`id`), CONSTRAINT `FK_554d71l9bovc8u804wghgyo38` FOREIGN KEY (`fk_organization_id`) REFERENCES `organizations` (`id`), CONSTRAINT `FK_9xype9ap7gcmdm5t077gdgor3` FOREIGN KEY (`fk_space_id`) REFERENCES `spaces` (`id`), CONSTRAINT `FK_aryfg8vfjdrhxrdxd3ykptsfq` FOREIGN KEY (`fk_environment_type`) REFERENCES `environment_types` (`id`), CONSTRAINT `FK_b959faehk11qrxb06die67ukm` FOREIGN KEY (`fk_mainApplication_id`) REFERENCES `services` (`id`), CONSTRAINT `FK_bomv382tbq3rf4ae54ewkr8rs` FOREIGN KEY (`fk_security_profile_id`) REFERENCES `security_profiles` (`id`), CONSTRAINT `FK_pxiie9k1gm297ejhr5y8k56a9` FOREIGN KEY (`fk_provision_profile_id`) REFERENCES `provision_profiles` (`id`), CONSTRAINT `FK_th5888gw8704s2bfcw0r8hmr2` FOREIGN KEY (`fk_state_id`) REFERENCES `lifecycles` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3686 DEFAULT CHARSET=utf8顯示創建表空間輸出
CREATE TABLE `spaces` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `alias_name` varchar(255) DEFAULT NULL, `created_date` datetime NOT NULL, `created_by` varchar(255) DEFAULT NULL, `description` longtext, `ukey` varchar(255) DEFAULT NULL, `label` varchar(255) DEFAULT NULL, `namespace` varchar(255) DEFAULT NULL, `updated_date` datetime NOT NULL, `updated_by` varchar(255) DEFAULT NULL, `vstamp` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `registered` bit(1) NOT NULL, `computeGroup_id` bigint(20) DEFAULT NULL, `fk_datacenter_id` bigint(20) DEFAULT NULL, `fk_environment_type_id` bigint(20) DEFAULT NULL, `fk_firewall_details_id` bigint(20) DEFAULT NULL, `fk_user_profile_id` bigint(20) DEFAULT NULL, `fk_infrastructure_space_id` bigint(20) DEFAULT NULL, `fk_network_details_id` bigint(20) DEFAULT NULL, `networkGroup_id` bigint(20) DEFAULT NULL, `fk_organization_id` bigint(20) DEFAULT NULL, `fk_space_type_id` bigint(20) DEFAULT NULL, `fk_state_id` bigint(20) DEFAULT NULL, `storageGroup_id` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `UK_sjlchubxlbvgi5ulks9wp9sde` (`ukey`), UNIQUE KEY `spaces_name_uindex` (`name`), KEY `FK_9nvkdrx0f46h75d6sa5oy6iwr` (`computeGroup_id`), KEY `FK_ejq2ibt89frkg86smcrmhyt5v` (`fk_datacenter_id`), KEY `FK_6ajs9lyo7mjmjjj4y3qjsc31g` (`fk_environment_type_id`), KEY `FK_7hm1q5w10wt88uxnhhgprxb` (`fk_firewall_details_id`), KEY `FK_bato9w3gb8niubughim3co8ep` (`fk_user_profile_id`), KEY `FK_ilpdrrgxshsrd3vfx3xtdfegx` (`fk_infrastructure_space_id`), KEY `FK_4lstxys6ovkiut13ja3r5rcpd` (`fk_network_details_id`), KEY `FK_n1633yw9h1jfv1wpa8hhtmafa` (`networkGroup_id`), KEY `FK_l9pbefokdexmg051uxucrue2i` (`fk_organization_id`), KEY `FK_gyvfscvcyyvwpxugo8sbcv7mc` (`fk_space_type_id`), KEY `FK_jjhvvr4g3v55iylkh54mee86d` (`fk_state_id`), KEY `FK_v4q19t54g2ecqslfdyit0gn7` (`storageGroup_id`), CONSTRAINT `FK_4lstxys6ovkiut13ja3r5rcpd` FOREIGN KEY (`fk_network_details_id`) REFERENCES `component_details` (`id`), CONSTRAINT `FK_6ajs9lyo7mjmjjj4y3qjsc31g` FOREIGN KEY (`fk_environment_type_id`) REFERENCES `environment_types` (`id`), CONSTRAINT `FK_7hm1q5w10wt88uxnhhgprxb` FOREIGN KEY (`fk_firewall_details_id`) REFERENCES `component_details` (`id`), CONSTRAINT `FK_9nvkdrx0f46h75d6sa5oy6iwr` FOREIGN KEY (`computeGroup_id`) REFERENCES `compute_groups` (`id`), CONSTRAINT `FK_bato9w3gb8niubughim3co8ep` FOREIGN KEY (`fk_user_profile_id`) REFERENCES `user_security_profiles` (`id`), CONSTRAINT `FK_ejq2ibt89frkg86smcrmhyt5v` FOREIGN KEY (`fk_datacenter_id`) REFERENCES `data_centers` (`id`), CONSTRAINT `FK_gyvfscvcyyvwpxugo8sbcv7mc` FOREIGN KEY (`fk_space_type_id`) REFERENCES `space_type` (`id`), CONSTRAINT `FK_ilpdrrgxshsrd3vfx3xtdfegx` FOREIGN KEY (`fk_infrastructure_space_id`) REFERENCES `infrastructure_spaces` (`id`), CONSTRAINT `FK_jjhvvr4g3v55iylkh54mee86d` FOREIGN KEY (`fk_state_id`) REFERENCES `lifecycles` (`id`), CONSTRAINT `FK_l9pbefokdexmg051uxucrue2i` FOREIGN KEY (`fk_organization_id`) REFERENCES `organizations` (`id`), CONSTRAINT `FK_n1633yw9h1jfv1wpa8hhtmafa` FOREIGN KEY (`networkGroup_id`) REFERENCES `network_groups` (`id`), CONSTRAINT `FK_v4q19t54g2ecqslfdyit0gn7` FOREIGN KEY (`storageGroup_id`) REFERENCES `storage_groups` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8解釋輸出格式 JSON
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "5401.30" }, "ordering_operation": { "using_filesort": false, "grouping_operation": { "using_temporary_table": true, "using_filesort": true, "nested_loop": [ { "table": { "table_name": "environmen0_", "access_type": "ALL", "possible_keys": [ "PRIMARY", "UK_ad600i5g7rewdxmuc1elmekdh", "FK_pxiie9k1gm297ejhr5y8k56a9", "FK_35i1f8xibrr2apesdu9ahcpnt", "FK_b959faehk11qrxb06die67ukm", "FK_554d71l9bovc8u804wghgyo38", "FK_bomv382tbq3rf4ae54ewkr8rs", "FK_9xype9ap7gcmdm5t077gdgor3", "FK_th5888gw8704s2bfcw0r8hmr2", "FK_aryfg8vfjdrhxrdxd3ykptsfq" ], "rows_examined_per_scan": 3026, "rows_produced_per_join": 574, "filtered": "19.00", "cost_info": { "read_cost": "3517.21", "eval_cost": "114.99", "prefix_cost": "3632.20", "data_read_per_join": "3M" }, "used_columns": [ "id", "description", "ukey", "updated_date", "updated_by", "environment_name", "operation_scope", "fk_user_profile_id", "fk_space_id" ], "attached_condition": "(((`p3c`.`environmen0_`.`operation_scope` = 'design') or (`p3c`.`environmen0_`.`operation_scope` = 'design')) and ((`p3c`.`environmen0_`.`fk_user_profile_id` is not null) and (`p3c`.`environmen0_`.`fk_user_profile_id` is not null)))" } }, { "table": { "table_name": "space4_", "access_type": "ALL", "possible_keys": [ "PRIMARY" ], "rows_examined_per_scan": 3, "rows_produced_per_join": 574, "filtered": "33.33", "using_join_buffer": "Block Nested Loop", "cost_info": { "read_cost": "1.27", "eval_cost": "114.99", "prefix_cost": "3978.44", "data_read_per_join": "3M" }, "used_columns": [ "id", "alias_name" ], "attached_condition": "(`p3c`.`space4_`.`id` = `p3c`.`environmen0_`.`fk_space_id`)" } }, { "table": { "table_name": "usersecuri1_", "access_type": "eq_ref", "possible_keys": [ "PRIMARY" ], "key": "PRIMARY", "used_key_parts": [ "id" ], "key_length": "8", "ref": [ "p3c.environmen0_.fk_user_profile_id" ], "rows_examined_per_scan": 1, "rows_produced_per_join": 574, "filtered": "100.00", "cost_info": { "read_cost": "574.94", "eval_cost": "114.99", "prefix_cost": "4668.37", "data_read_per_join": "440K" }, "used_columns": [ "id", "primary_entity" ] } }, { "table": { "table_name": "permission2_", "access_type": "ref", "possible_keys": [ "UK_6xoibydyrd0ygb48fin59dqpi", "FK_9aqihyu0wbjupml42sqqs6nj5" ], "key": "UK_6xoibydyrd0ygb48fin59dqpi", "used_key_parts": [ "fk_profile_id" ], "key_length": "8", "ref": [ "p3c.environmen0_.fk_user_profile_id" ], "rows_examined_per_scan": 1, "rows_produced_per_join": 287, "filtered": "47.08", "index_condition": "(`p3c`.`permission2_`.`fk_security_role_id` in (2,1))", "cost_info": { "read_cost": "610.78", "eval_cost": "57.51", "prefix_cost": "5401.30", "data_read_per_join": "224K" }, "used_columns": [ "id", "permission_read", "fk_profile_id", "fk_security_role_id" ], "attached_condition": "(`p3c`.`permission2_`.`permission_read` = 1)" } } ] } } } }將應用連接表的索引，並且 ENVIRONMENT TABLE 不會獲取所有記錄。有人可以解釋這種行為嗎？在這種情況下我該怎麼辦？
修改後的查詢，經過所有討論
為 Environment 和 SECURITY_ROLE_PERMISSIONS 表添加了索引，但性能沒有變化
EXPLAIN SELECT environmen0_.description AS col_0_0_, environmen0_.fk_user_profile_id AS col_1_0_, environmen0_.id AS col_2_0_, environmen0_.ukey AS col_3_0_, environmen0_.environment_name AS col_4_0_, environmen0_.updated_date AS col_7_0_, environmen0_.updated_by AS col_8_0_, usersecuri1_.id AS id1_238_, usersecuri1_.primary_entity AS primary_2_238_, space4_.alias_name AS type_alias FROM ENVIRONMENTS environmen0_ JOIN SPACES space4_ ON environmen0_.fk_space_id = space4_.id JOIN USER_SECURITY_PROFILES usersecuri1_ ON environmen0_.fk_user_profile_id = usersecuri1_.id JOIN SECURITY_ROLE_PERMISSIONS permission2_ ON usersecuri1_.id = permission2_.fk_profile_id WHERE permission2_.permission_read = 1 AND (permission2_.fk_security_role_id IN (2, 1)) AND environmen0_.operation_scope = 'design' GROUP BY environmen0_.id ORDER BY environmen0_.id ASC LIMIT 25已編輯查詢的 EXPLAIN JSON 輸出
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "7475.71" }, "ordering_operation": { "using_filesort": false, "grouping_operation": { "using_temporary_table": true, "using_filesort": true, "cost_info": { "sort_cost": "1685.00" }, "nested_loop": [ { "table": { "table_name": "permission2_", "access_type": "index", "possible_keys": [ "UK_6xoibydyrd0ygb48fin59dqpi", "FK_9aqihyu0wbjupml42sqqs6nj5", "security_index" ], "key": "security_index", "used_key_parts": [ "fk_profile_id", "permission_read", "fk_security_role_id" ], "key_length": "18", "rows_examined_per_scan": 3579, "rows_produced_per_join": 1684, "filtered": "47.08", "using_index": true, "cost_info": { "read_cost": "391.80", "eval_cost": "337.00", "prefix_cost": "728.80", "data_read_per_join": "1M" }, "used_columns": [ "id", "permission_read", "fk_profile_id", "fk_security_role_id" ], "attached_condition": "((`p3c`.`permission2_`.`permission_read` = 1) and (`p3c`.`permission2_`.`fk_security_role_id` in (2,1)))" } }, { "table": { "table_name": "environmen0_", "access_type": "ref", "possible_keys": [ "PRIMARY", "UK_ad600i5g7rewdxmuc1elmekdh", "FK_pxiie9k1gm297ejhr5y8k56a9", "FK_35i1f8xibrr2apesdu9ahcpnt", "FK_b959faehk11qrxb06die67ukm", "FK_554d71l9bovc8u804wghgyo38", "FK_bomv382tbq3rf4ae54ewkr8rs", "FK_9xype9ap7gcmdm5t077gdgor3", "FK_th5888gw8704s2bfcw0r8hmr2", "FK_aryfg8vfjdrhxrdxd3ykptsfq", "environments_operation_scope_id_index" ], "key": "FK_35i1f8xibrr2apesdu9ahcpnt", "used_key_parts": [ "fk_user_profile_id" ], "key_length": "9", "ref": [ "p3c.permission2_.fk_profile_id" ], "rows_examined_per_scan": 1, "rows_produced_per_join": 1684, "filtered": "100.00", "cost_info": { "read_cost": "1685.00", "eval_cost": "337.00", "prefix_cost": "2750.80", "data_read_per_join": "10M" }, "used_columns": [ "id", "description", "ukey", "updated_date", "updated_by", "environment_name", "operation_scope", "fk_user_profile_id", "fk_space_id" ], "attached_condition": "(`p3c`.`environmen0_`.`operation_scope` = 'design')" } }, { "table": { "table_name": "usersecuri1_", "access_type": "eq_ref", "possible_keys": [ "PRIMARY" ], "key": "PRIMARY", "used_key_parts": [ "id" ], "key_length": "8", "ref": [ "p3c.permission2_.fk_profile_id" ], "rows_examined_per_scan": 1, "rows_produced_per_join": 1684, "filtered": "100.00", "cost_info": { "read_cost": "1685.00", "eval_cost": "337.00", "prefix_cost": "4772.80", "data_read_per_join": "1M" }, "used_columns": [ "id", "primary_entity" ] } }, { "table": { "table_name": "space4_", "access_type": "ALL", "possible_keys": [ "PRIMARY" ], "rows_examined_per_scan": 3, "rows_produced_per_join": 1685, "filtered": "33.33", "using_join_buffer": "Block Nested Loop", "cost_info": { "read_cost": "6.91", "eval_cost": "337.00", "prefix_cost": "5790.71", "data_read_per_join": "8M" }, "used_columns": [ "id", "alias_name" ], "attached_condition": "(`p3c`.`space4_`.`id` = `p3c`.`environmen0_`.`fk_space_id`)" } } ] } } } }謝謝
擺脫
STRAIGHT_JOIN擺脫
AND 1=1不要把
OR相同的子句放在一起，只說一次：AND (environmen0_.operation_scope = ‘design’ 或 environmen0_.operation_scope = ‘design’)
需要的索引：
environmen0_: INDEX(operation_scope, id) permission2_: INDEX(fk_profile_id, permission_read, fk_security_role_id)當一個表只有 3 行時，是否使用索引無關緊要。隨著表的增長或參數的變化，優化器可能會選擇不同的索引來使用。
除了 Rick James 的回答
- 什麼是 STRAIGHT_JOIN？：
STRAIGHT_JOIN 與 JOIN 類似，只是左表總是在右表之前讀取。這可用於連接優化器以錯誤順序放置表的那些（少數）情況。
因此，在使用 fk_space_id 的索引之前，MySQL 將首先讀取 ENVIRONMENTS 並且您只有 1 個過濾條件：
AND (environmen0_.operation_scope = 'design' OR environmen0_.operation_scope = 'design')operation_scope - 未編入索引…平均 == FULL SCAN
- 1=1 - 全部刪除，這是一個 DUMMY 條件，在您的查詢中不執行任何操作
- 如果您將 STRAIGHT_JOIN 更改為正常的 JOIN，MySQL 可以或不能更改讀取順序。如果它決定改變它 - 您預期的索引開始工作。選擇與否 - 取決於許多不干淨的參數，
基於所有這些 - 為 (operation_scope, id) 創建索引 - 將是最佳選擇