原文链接:http://www.linkedkeeper.com/detail/blog.action?bid=6
执行 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 select * from table_name where city =
'beijing'
|
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | table_name | ref | city | city | 11 | const | 1 | Using 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'
|
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 |
由于不是最左前缀,索引这样的查询显然用不到索引。
情况五:匹配某列的前缀字符串
1
|
explain select * from table_name where city =
'beijing'
and name like
'zhang%'
|
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | table_name | range | city | city | 11 | NULL | 11 | Using index |
此时可以用到索引,但是如果通配符不是只出现在末尾,则无法使用索引。
情况六:范围查询
1
|
explain select * from table_name where city in (
'beijing'
,
'shanghai'
) and sex =
'man'
|
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | table_name | range | city | city | 11 | NULL | 201 | Using index |
范围列可以用到索引(必须是最左前缀),但是范围列后面的列无法用到索引。同时,索引最多用于一个范围列,因此如果查询条件中有两个范围列则无法全用到索引。
情况七:查询条件中含有函数或表达式
如果查询条件中含有函数或表达式,则 MySQL 不会为这列使用索引。
1
|
explain select * from table_name where city =
'beijing'
and left(name,
5
) =
'zhang'
|
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | table_name | ALL | NULL | NULL | NULL | NULL | 11 | Using 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 降了下来。