联合索引命中率问题导致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 (文/张松然)”

    此条目发表在mysql分类目录,贴了标签。将固定链接加入收藏夹。

    发表评论

    电子邮件地址不会被公开。 必填项已用*标注