Mysql数据库服务器的CPU占用很高分析

浏览数：32 / 时间：2015年06月12日

　　MySQl服务器CPU占用很高

　　1. ?问题描述

　　一个简单的接口，根据传入的号段查询号码归属地，运行性能测试脚本，20个并发mysql的CPU就很高，监控发现只有一个select语句，且表建立了索引

　　2. ?问题原因

　　查询语句索引没有命中导致

　　开始时的select

SELECT
`province_name`,
`city_name`
FROM `phoneno_section`
WHERE SUBSTRING(?, phoneno_section_len) = phoneno_section
LIMIT ?
咨询说where中使用SUBSTRING函数不行，修改函数为LEFT，语句为
SELECT
`province_name`,
`city_name`
FROM `conf_phoneno_section`
WHERE LEFT(?, phoneno_section_len) = phoneno_section
LIMIT ?

?测试发现CPU占用还是很高，LEFT函数中的参数是变量不是常量，再次修改select语句，指定LEFT函数中的phoneno_section_len为固定值，CPU占用正常

　　3. ?MYSQL索引介绍

　　ü ?先举个例子

　　表a，字段: ?id(自增id),user(用户名),pass（密码）,type（类型 0,1）,

　　索引： user + pass 建立联合索引，user唯一索引，pass普通索引，type 普通索引

　　ü ?索引命中说明

　　（1）SELECT ? * ? FROM ? a ?WHERE user = ‘t‘ ?AND PASS = ‘p‘会命中user+pass的联合索引

　　（2）SQL: ?SELECT ? * ? FROM ? a ?WHERE user = ‘t‘ OR user= ‘f‘ ?不能命中任何索引

　　（3）SQL: ?SELECT ? * ? FROM ? a ?WHERE user = ‘t‘会命中user唯一索引

　　（4）SQL: ?SELECT ? * ? FROM ? a ?WHERE pass = ‘p‘ ? 不能命中任何索引

　　（5）SELECT ?* ?FROM ?a WHERE user = ‘t‘ ?OR ? user= ‘f‘ 相对于SELECT ?user,pass ?FROM ?a ?WHERE user = ‘t‘ ?OR ? user= ‘f‘ ?会慢

　　（6）SELECT * FROM a WHERE length(user) = 3 不能命中

　　（7）user唯一索引、type索引可以删除

　　索引就是排序，目前的计算机技术和数学理论还不支持一次同时按照两个关键字进行排序，即使是联合索引，也是先按照最左边的关键字先排，然后在左边的关键字排序基础上再对其他的关键字排序，是一个多次排序的结果。所以，单表查询，一次最多只能命中一个索引，并且索引必须遵守最左前缀。于是基于索引的结构和最左前缀，像 OR ,like ‘%%‘都是不能命中索引的，而like ‘aa%‘则是可以命中的。

　　无论是innodb还是myisam，索引只记录被排序的行的主键或者地址，其他的字段还是需要二次查询，因此，如果查询的字段刚好只是包含在索引中，那么索引覆盖将是高效的。

　　如果所有的数据都一样，或者基本一样，那么就没有排序的必要了。像例子中的type只有1或者0，选择性是0.5，极低的样纸，所以可以忽视，即使建立了，也是浪费空间，mysql在查询的时候也会选择丢弃。

　　类似最左前缀，查询索引的时候，如果列被应用了函数，那么在查询的时候，是不会用到索引的。道理很简单，函数运算已经改变了列的内容，而原始的索引是对列内容全量排序的。

　　综上所述，索引的几个知识点：最左前缀，索引覆盖，索引选择性，列隔离在建立和使用索引的时候需要格外注意。

　　4. ?MySQl索引无效场景补充

　　ü ?WHERE子句的查询条件里有不等于号（WHERE column!=...），MYSQL将无法使用索引

　　ü ?WHERE子句的查询条件里使用了函数（如：WHERE DAY(column)=...），MYSQL将无法使用索引，实验中LEFT函数是可以的，但是条件不能是变量，使用LEFT函数且条件是变量，也无法使用索引，LEFT函数之外是否有其它函数有待验证

　　ü ?在JOIN操作中（需要从多个数据表提取数据时），MYSQL只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用

　　ü ?如果WHERE子句的查询条件里使用了比较操作符LIKE和REGEXP，MYSQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说，如果查询条件是LIKE ‘abc%‘,MYSQL将使用索引；如果条件是LIKE ‘%abc‘，MYSQL将不使用索引。

　　ü ?在ORDER BY操作中，MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。尽管如此，在涉及多个数据表的查询里，即使有索引可用，那些索引在加快ORDER BY操作方面也没什么作用。

　　ü ?如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含了净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。

　　只要建立了索引，除了上面提到的索引不会使用的情况下之外，其他情况只要是使用在WHERE条件里，ORDER BY 字段，联表字段，索引一般都是有效的。