联合索引命中率问题导致SQL查询效率慢的问题.docxVIP

联合索引命中率问题导致SQL查询效率慢的问题 2021-09-18 执行 MySQL DumpSlow 结果是： 1 Count: 1358? Time=0.33s (448s)? Lock=0.00s (0s)? Rows=2.5 (3343) Count：消灭次数Time：执行最长时间(累计总耗费时间)Lock：等待锁的时间Rows：发送给客户端的行总数(扫描的行总数) 其中：Count 会告知我们这品种型的语句执行了几次，Time会告知我们这品种型的语句执行的最大时间，Time=0.33s (448s) 中（448s）是指这类型的语句执行总共花费的时间。 上述结果告知我们执行了 1358 次，最大时间是 0.33s，总共花费时间 448s，Lock时间 0s，单次前往的结果数是 2.5 条记录，Rows=2.5 (3343) 中的 3343 是指在 Count: 1358 次数总共前往了 3343 条记录集，Rows=2.5 显示 3343 / Count: 1358。 SQL 查询为什么慢？ 首先查看 table_name 的能否有索引： 1 show index from table_name Table Non_unique Key_name Seq_in_index Column_name Collation ...? table_name 0 table_index 1 city A ...? table_name 0 table_index 2 name A ...? table_name 0 table_index 3 sex A ...? 用 explain 分析查询 SQL 调用，结果如下： 1 explain select * from table_name where name= zhangsr and city = beijing id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE table_name ALL NULL NULL NULL NULL 123456 Using where explain 列的解释： table：显示这一行的数据是关于哪张表的；type：这是重要的列，显示连接使用了何品种型。从最好到最差的连接类型为 const、eq_reg、ref、range、indexhe 和 ALL；possible_keys：显示可能应用在这张表中的索引。假如为空，没有可能的索引。可以为相关的域从 WHERE 语句中选择一个合适的语句；key： 实际使用的索引。假如为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽视索引；key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好；ref：显示索引的哪一列被使用了，假如可能的话，是一个常数；rows：MYSQL认为必需检查的用来前往恳求数据的行数；Extra：关于MYSQL如何解析查询的额外信息。 结果发觉，虽然我们创建了索引，但是 SQL 并没有命中索引，这是为什么？ 联合索引 先说一下联合索引的概念，MySQL 中的索引可以以肯定挨次引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组，其中各个元素均为数据表的一列。另外，单列索引可以看成联合索引元素数为 1 的特例。 情况一：全列婚配 1 explain select * from table_name where city = beijing and name= zhangsr and sex=man id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE table_name ref city city 59 const,const,const 1 Using index 很明显，当依据索引中全部列进行精确婚配时，索引可以被用到。这里有一点需要留意，理论上索引对挨次是敏感的，但是由于 MySQL 的查询优化器会自动调整 where 子句的条件挨次以使用适合的索引，例如我们将 where 中的条件挨次颠倒： 1 explain select * from table_name where name= zhangsr city = beijing and and sex=man 效果是一样的。 情况二：最左前缀婚配 1 explain selec