MySQl服務(wù)器CPU占用很高
1. 問(wèn)題描述
一個(gè)簡(jiǎn)單的接口，根據(jù)傳入的號(hào)段查詢號(hào)碼歸屬地，運(yùn)行
性能測(cè)試腳本，20個(gè)并發(fā)mysql的CPU就很高，監(jiān)控發(fā)現(xiàn)只有一個(gè)select語(yǔ)句，且表建立了索引
2. 問(wèn)題原因
查詢語(yǔ)句索引沒(méi)有命中導(dǎo)致
開始時(shí)的select
SELECT `province_name`, `city_name` FROM `phoneno_section` WHERE SUBSTRING(?, phoneno_section_len) = phoneno_section LIMIT ? 咨詢說(shuō)where中使用SUBSTRING函數(shù)不行，修改函數(shù)為L(zhǎng)EFT，語(yǔ)句為 SELECT `province_name`, `city_name` FROM `conf_phoneno_section` WHERE LEFT(?, phoneno_section_len) = phoneno_section LIMIT ? |
測(cè)試發(fā)現(xiàn)CPU占用還是很高，LEFT函數(shù)中的參數(shù)是變量不是常量，再次修改select語(yǔ)句，指定LEFT函數(shù)中的phoneno_section_len為固定值，CPU占用正常
3. MYSQL索引介紹
ü 先舉個(gè)例子
表a， 字段: id(自增id),user(用戶名),pass（密碼）,type（類型 0,1）,
索引： user + pass 建立聯(lián)合索引 ，user唯一索引，pass普通索引 ，type 普通索引
ü 索引命中說(shuō)明
（1）SELECT * FROM a WHERE user = 't' AND PASS = 'p'會(huì)命中user+pass的聯(lián)合索引
（2）
SQL: SELECT * FROM a WHERE user = 't' OR user= 'f' 不能命中任何索引
（3）SQL: SELECT * FROM a WHERE user = 't'會(huì)命中user唯一索引
（4）SQL: SELECT * FROM a WHERE pass = 'p' 不能命中任何索引
（5）SELECT * FROM a WHERE user = 't' OR user= 'f' 相對(duì)于SELECT user,pass FROM a WHERE user = 't' OR user= 'f' 會(huì)慢
（6）SELECT * FROM a WHERE length(user) = 3 不能命中
（7）user唯一索引 、type索引可以刪除
索引就是排序，目前的計(jì)算機(jī)技術(shù)和數(shù)學(xué)理論還不支持一次同時(shí)按照兩個(gè)關(guān)鍵字進(jìn)行排序，即使是聯(lián)合索引，也是先按照最左邊的關(guān)鍵字先排，然后在左邊的關(guān)鍵字排序基礎(chǔ)上再對(duì)其他的關(guān)鍵字排序，是一個(gè)多次排序的結(jié)果。 所以，單表查詢，一次最多只能命中一個(gè)索引，并且索引必須遵守最左前綴。于是基于索引的結(jié)構(gòu)和最左前綴，像 OR ,like '%%'都是不能命中索引的，而like 'aa%'則是可以命中的。
無(wú)論是innodb還是myisam，索引只記錄被排序的行的主鍵或者地址，其他的字段還是需要二次查詢，因此，如果查詢的字段剛好只是包含在索引中，那么索引覆蓋將是高效的。
如果所有的數(shù)據(jù)都一樣，或者基本一樣，那么就沒(méi)有排序的必要了。像例子中的type只有1或者0，選擇性是0.5，極低的樣紙，所以可以忽視，即使建立了，也是浪費(fèi)空間，mysql在查詢的時(shí)候也會(huì)選擇丟棄。
類似最左前綴，查詢索引的時(shí)候，如果列被應(yīng)用了函數(shù)，那么在查詢的時(shí)候，是不會(huì)用到索引的。道理很簡(jiǎn)單，函數(shù)運(yùn)算已經(jīng)改變了列的內(nèi)容，而原始的索引是對(duì)列內(nèi)容全量排序的。
綜上所述，索引的幾個(gè)知識(shí)點(diǎn)：最左前綴，索引覆蓋，索引選擇性，列隔離在建立和使用索引的時(shí)候需要格外注意。
4. MySQl索引無(wú)效場(chǎng)景補(bǔ)充
ü WHERE子句的查詢條件里有不等于號(hào)（WHERE column!=...），MYSQL將無(wú)法使用索引
ü WHERE子句的查詢條件里使用了函數(shù)（如：WHERE DAY(column)=...），MYSQL將無(wú)法使用索引，實(shí)驗(yàn)中LEFT函數(shù)是可以的，但是條件不能是變量，使用LEFT函數(shù)且條件是變量，也無(wú)法使用索引，LEFT函數(shù)之外是否有其它函數(shù)有待驗(yàn)證
ü 在JOIN操作中（需要從多個(gè)數(shù)據(jù)表提取數(shù)據(jù)時(shí)），MYSQL只有在主鍵和外鍵的數(shù)據(jù)類型相同時(shí)才能使用索引，否則即使建立了索引也不會(huì)使用
ü 如果WHERE子句的查詢條件里使用了比較操作符LIKE和REGEXP，MYSQL只有在搜索模板的第一個(gè)字符不是通配符的情況下才能使用索引。比如說(shuō)，如果查詢條件是LIKE 'abc%',MYSQL將使用索引；如果條件是LIKE '%abc'，MYSQL將不使用索引。
ü 在ORDER BY操作中，MYSQL只有在排序條件不是一個(gè)查詢條件表達(dá)式的情況下才使用索引。盡管如此，在涉及多個(gè)數(shù)據(jù)表的查詢里，即使有索引可用，那些索引在加快ORDER BY操作方面也沒(méi)什么作用。
ü 如果某個(gè)數(shù)據(jù)列里包含著許多重復(fù)的值，就算為它建立了索引也不會(huì)有很好的效果。比如說(shuō)，如果某個(gè)數(shù)據(jù)列里包含了凈是些諸如“0/1”或“Y/N”等值，就沒(méi)有必要為它創(chuàng)建一個(gè)索引。
只要建立了索引，除了上面提到的索引不會(huì)使用的情況下之外，其他情況只要是使用在WHERE條件里，ORDER BY 字段，聯(lián)表字段，索引一般都是有效的。
English » | | | | | | | | |
Text-to-speech function is limited to 100 characters