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

转自：联合索引命中率问题导致SQL查询效率慢的问题


执行 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

TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollation...
table_name0table_index1cityA...
table_name0table_index2nameA...
table_name0table_index3sexA...

用 explain 分析查询 SQL 调用，结果如下：

1
explain select * from table_name where name= 'zhangsr' and city = 'beijing'

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtable_nameALLNULLNULLNULLNULL123456Using 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'
    idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
    1SIMPLEtable_namerefcitycity59const,const,const1Using index

    很明显，当按照索引中所有列进行精确匹配时，索引可以被用到。这里有一点需要注意，理论上索引对顺序是敏感的，但是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，例如我们将 where 中的条件顺序颠倒：

    1
    explain select * from table_name where name= 'zhangsr' city = 'beijing' and and sex='man'

    效果是一样的。


  • 情况二：最左前缀匹配

    • 1
    explain select * from table_name where city = 'beijing'

    idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
    1SIMPLEtable_namerefcitycity11const1Using index

    当查询条件精确匹配索引的左边连续一个或几个列时，索引可以被用到，但是只能用到一部分，即条件所组成的最左前缀。上面的查询从分析结果看用到了 table_index 索引，但是 key_len为11，说明只用到了索引的第一列前缀。


  • 情况三：查询条件用到了索引中列的精确匹配，但是中间某个条件未提供

    • 1
    explain select * from table_name where city = 'beijing' and sex = 'man'

    此时索引使用情况和情况二相同，查询只用到了索引的第一列，而后面的 sex 虽然也在索引中，但是无法和左前缀连接，因此需要对结果进行扫描过滤 sex)。如果想让 sex 也使用索引而不是 where 过滤，可以增加一个辅助索引，此时上面的查询会使用这个索引。


  • 情况四：查询条件没有指定索引第一列

    • 1
    explain select * from table_name where sex = 'man'

    idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
    1SIMPLEtable_nameALLNULLNULLNULLNULL123456Using where

    由于不是最左前缀，索引这样的查询显然用不到索引。


  • 情况五：匹配某列的前缀字符串

    • 1
    explain select * from table_name where city = 'beijing' and name like 'zhang%'

    idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
    1SIMPLEtable_namerangecitycity11NULL11Using index

    此时可以用到索引，但是如果通配符不是只出现在末尾，则无法使用索引。


  • 情况六：范围查询

    • 1
    explain select * from table_name where city in ('beijing','shanghai')  and sex = 'man'

    idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
    1SIMPLEtable_namerangecitycity11NULL201Using index

    范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。


  • 情况七：查询条件中含有函数或表达式

    • 如果查询条件中含有函数或表达式，则 MySQL 不会为这列使用索引。

    1
    explain select * from table_name where city = 'beijing' and left(name,5) = 'zhang'


    idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
    1SIMPLEtable_nameALLNULLNULLNULLNULL11Using where

    索引的选择性（Selectivity）与前缀索引

    既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引? 答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL 在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。

    第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。

    另一种不建议建索引的情况是索引的选择性较低。

    所谓索引的选择性 Selectivity ，是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：

    1
    Index Selectivity = Cardinality / #T

    显然选择性的取值范围为 (0, 1]，选择性越高的索引价值越大，这是由 B+Tree 的性质决定的。例如，上文用到的 table_name 表，如果 name 字段经常被单独查询，是否需要建索引，我们看一下它的选择性：

    1
    SELECT count(DISTINCT(name))/count(*) AS Selectivity FROM table_name

    Selectivity
    0.0000

    name 的选择性不足 0.0001，所以实在没有什么必要为其单独建索引。

    有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引 key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引 key 变短而减少了索引文件的大小和维护开销。

    1
    SELECT count(DISTINCT(city))/count(*) AS Selectivity FROM table_name

    Selectivity
    0.1108

    1
    SELECT count(DISTINCT(city,name))/count(*) AS Selectivity FROM table_name

    Selectivity
    1.0000

    总结

    通过以上的分析，调整了联合索引的先后顺序，并根据索引选择性，重现创建了索引，数据库 load 降了下来。


    转载请并标注: “本文转载自 linkedkeeper.com (文／张松然)”


    时间：