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