1.表創(chuàng)建語句
CREATE TABLE`user`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET=utf8 COMMENT ='用戶表';
INSERT INTO user(name,age,position,hire_time)VALUES('LiLei',22,'manager',NOW()); INSERT INTO user(name,age,position,hire_time)VALUES('HanMeimei',23,'dev',NOW()); INSERT INTO user(name,age,position,hire_time)VALUES('Lucy',23,'dev',NOW());
2.Mysql如何選擇合適的索引
表中字段沒有索引
EXPLAIN select * from user where name > 'a';
[圖片上傳失敗...(image-ba338-1594189637012)]
如果用name索引需要遍歷name字段聯(lián)合索引樹红省，然后還需要根據(jù)遍歷出來的主鍵值去主鍵索引樹里再去查出最終數(shù)據(jù)，成本比全表掃描 還高污它，可以用覆蓋索引優(yōu)化液兽，這樣只需要遍歷name字段的聯(lián)合索引樹就能拿到所有結(jié)果，如下:
加上name age position 字段索引儒老，走了覆蓋索引type=index
EXPLAIN select name,age,position from user where name > 'a'
image-01.png
EXPLAIN select * from user where name > 'cc' ;
image-03.png
3.常見sql深入優(yōu)化
3.1Order by與Group by優(yōu)化
3.1.利用最左前綴法則:中間字段不能斷蝴乔，出現(xiàn)了Using filesort，因為索引的創(chuàng)建順序為 name,age,position
EXPLAIN
select *
from user
where
position = 'dev'
order by age ;
image-04.png
3.2利用最左前綴法則:中間字段不能斷驮樊，因此查詢用到了name索引薇正，從key_len=74也能看出，age索引列用 在排序過程中囚衔，因為Extra字段里沒有using filesort
EXPLAIN select * from user where name = 'lilei' order by age
image-05.png
4.優(yōu)化總結(jié):
1挖腰、MySQL支持兩種方式的排序filesort和index，Using index是指MySQL掃描索引本身完成排序练湿。index
效率高猴仑，filesort效率低。
2鞠鲜、order by滿足兩種情況會使用Using index宁脊。
order by語句使用索引最左前列断国。
使用where子句與order by子句條件列組合滿足索引最左前列。 3榆苞、盡量在索引列上完成排序稳衬，遵循索引建立(索引創(chuàng)建的順序)時的最左前綴法則。
4坐漏、如果order by的條件不在索引列上薄疚，就會產(chǎn)生Using filesort。
5赊琳、能用覆蓋索引盡量用覆蓋索引
6街夭、group by與order by很類似，其實質(zhì)是先排序后分組躏筏，遵照索引創(chuàng)建順序的最左前綴法則板丽。對于group by的優(yōu)化如果不需要排序的可以加上order by null禁止排序。注意趁尼，where高于having埃碱，能寫在where中 的限定條件就不要去having限定了。
