工作中遇到的99%SQL優(yōu)化，這里都能給你解決方案
前幾篇文章介紹了mysql的底層數(shù)據(jù)結(jié)構(gòu)和mysql優(yōu)化的神器explain。后臺(tái)有些朋友說小強(qiáng)只介紹概念，平時(shí)使用還是一臉懵，強(qiáng)烈要求小強(qiáng)來一篇實(shí)戰(zhàn)sql優(yōu)化，經(jīng)過周末兩天的整理和總結(jié)，sql優(yōu)化實(shí)戰(zhàn)新鮮出爐， 大家平時(shí)學(xué)習(xí)和工作中，遇到的90% 的sql優(yōu)化都會(huì)介紹到，介意篇幅過長，分成3篇文章哈。
- CREATE TABLE `employees` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
- `age` int(20) NOT NULL DEFAULT '0' COMMENT '年齡',
- `position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',
- `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '入職時(shí)間',
- PRIMARY KEY (`id`),
- KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='員工表';
- insert into employees(name,age,position,hire_time) values('LiLei', 22, 'manager', NOW())
- insert into employees(name,age,position,hire_time) values('HanMeimei', 23, 'dev', NOW())
- insert into employees(name,age,position,hire_time) values('Lucy', 23, 'dev', NOW())
全值匹配
索引的字段類型是varchar(n)：2字節(jié)存儲(chǔ)字符串長度，如果是utf-8， 則長度是3n+2
- EXPLAIN select * from employees where name='LiLei';
- EXPLAIN select * from employees where name='LiLei' AND age = 22;
- EXPLAIN select * from employees where name='LiLei' AND age = 22 AND position = 'manager';
最左前綴法則
如果索引是多列，要最受最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。以下三條sql根據(jù)最左前綴法則，都不會(huì)走索引。
- EXPLAIN select * from employees where age = 22 AND position='manager';
- EXPLAIN select * from employees where position ='manager';
- EXPLAIN select * from employees where age=17;
索引失效
不要在索引列上做任何操作（計(jì)算、函數(shù)、類型轉(zhuǎn)換），會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描。
- EXPLAIN select * from employees where name='LiLei';
- EXPLAIN select * from employees where left(name, 3)='LiLei';
給hire_time增加一個(gè)普通索引：
- alter table `employees` ADD INDEX `idx_hire_time`(`hire_time`) USING BTREE;
- EXPLAIN select * from employees where date(hire_time) = '2019-08-25';
還原最初索引狀態(tài)
- ALTER TABLE `employees` DROP INDEX `idx_hire_time`;
存儲(chǔ)引擎不能使用索引中范圍條件右邊的列
- -- EXPLAIN SELECT * FROM employees WHERE name ='LiLei' AND age=22 AND position ='manager';
- EXPLAIN SELECT * FROM employees WHERE name ='LiLei' AND age>22 AND position ='manager';
看到key_len這個(gè)索引長度是78， 也就是只使用到了前兩個(gè)字段name和age，postition沒有使用到索引的。
覆蓋索引
盡量使用覆蓋索引（只訪問索引的查詢（索引列包含查詢列）），減少selelct * 語句。
- EXPLAIN SELECT name,age,position FROM employees WHERE name ='LiLei' AND age=22 AND position ='manager';
條件判斷
mysql在使用不等于（! = 或者 <>）的時(shí)候無法使用索引會(huì)導(dǎo)致全表掃描
- EXPLAIN SELECT * FROM employees WHERE name !='LiLei' ;
空值判斷
is null，is not null也無法使用索引
- EXPLAIN SELECT * FROM employees WHERE name is null;
like
like以通配符開頭（‘$abc’）mysql索引失效會(huì)變成全表掃描操作
- EXPLAIN SELECT * FROM employees WHERE name LIKE '%Lei';
字符串不加單引號索引失效
- EXPLAIN SELECT * FROM employees WHERE name ='1000';
- EXPLAIN SELECT * FROM employees WHERE name =1000;
不加單引號的字符串，mysql底層會(huì)使用cust函數(shù)將其轉(zhuǎn)換為字符串，此時(shí)索引失效。
or&in少使用
少用or或in，用它查詢時(shí)，mysql不一定使用索引，mysql內(nèi)部優(yōu)化器會(huì)根據(jù)索引比例、表大小等多個(gè)因素整體評估是否使用索引。
- EXPLAIN SELECT * FROM employees WHERE name ='LiLei' or name='HanMeimei';
范圍查詢優(yōu)化
給年齡添加單值索引
- ALTER TABLE `employees`ADD INDEX `idx_age`(`age`) USING BTREE;
- EXPLAIN select * from employees where age > 1 and age <= 2000;
沒有走索引原因：mysql內(nèi)部優(yōu)化器會(huì)根據(jù)檢索比例、表大小等多個(gè)因素整體評估是否使用索引。這個(gè)例子沒有走索引可能是因?yàn)閱未螖?shù)據(jù)量查詢過大導(dǎo)致優(yōu)化器最終選擇不走索引。優(yōu)化方法：可以將大的范圍拆分成多個(gè)小范圍。
