注:原始資料來自享學課堂,自己加上整理和思考
思考sql最佳化的幾個地方,我把他做了個分類,方便理解
select [欄位
最佳化1
]:主要是覆蓋索引
from []
where [條件
最佳化2
]
union [聯合查詢
最佳化3
]
新建表格
CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘主鍵’, `name` varchar(50) DEFAULT NULL COMMENT ‘姓名’, `age` int(11) DEFAULT NULL COMMENT ‘年齡’, `phone` varchar(12) DEFAULT NULL, `create_time` datetime DEFAULT NULL COMMENT ‘建立時間’, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
新增索引,新增索引之後
key_len:
根據這個值,就可以判斷索引使用情況,特別是在組合索引的時候,判斷所有的索引欄位是否都被查詢用到。
key_len計算方式簡單介紹
latin1佔用1個位元組,gbk佔用2個位元組,utf8佔用3個位元組
不允許為空:
varchar(10):10*3
char(10):10*3+2
int:4
允許為空:
varchar(10):10*3+1
char(10):10*3+2+1
int:4+1
使用完全索引
key_len=name(50*3+2+1=153)+age(4+1)+phone(12*3+2+1=39)
alter table studen add index name_age_phone(name, age, phone);
新增資料
insert into student(name,age,phone,create_time) values(‘賽文’,1000,‘15717177664’,now());insert into student(name,age,phone,create_time) values(‘雷歐’,1200,‘15733337664’,now());insert into student(name,age,phone,create_time) values(‘泰羅’,800,‘15714447664’,now());
一、最佳化點1:欄位最佳化
覆蓋索引儘量用
簡單解釋解釋,索引是哪幾個列,就查詢哪幾個列: 覆蓋索引的原因:索引是高效找到行的一個方法,但是一般資料庫也能使用
索引
找到一個列的資料,因此它
不必讀取整個行
。畢竟索引頁子節點儲存了它們索引的資料; 當能透過讀取索引就可以得到想要的資料,那就不需要讀取行了。一個索引
包含了
(或
覆蓋了
)滿足查詢結果的資料就叫做覆蓋索引
注意:
有索引儘量不要使用select *
#未覆蓋索引EXPLAIN SELECT * FROM student WHERE NAME = ‘泰羅’ and age =1000 and phone=‘15717177664’;#覆蓋了索引EXPLAIN SELECT name,age,phone FROM student WHERE NAME = ‘泰羅’ and age =1000 and phone=‘15717177664’;#包含了索引EXPLAIN SELECT name FROM student WHERE NAME = ‘泰羅’ and age =1000 and phone=‘15717177664’;#加上主鍵也還是覆蓋索引EXPLAIN SELECT id, name,age,phone FROM student WHERE NAME = ‘泰羅’ and age =1000 and phone=‘15717177664’;
未使用覆蓋索引
使用完全覆蓋索引
使用包含覆蓋索引
加上主鍵還是覆蓋索引
二、最佳化點2:where最佳化
1。儘量全值匹配
EXPLAIN SELECT * FROM student WHERE NAME = ‘賽文’;EXPLAIN SELECT * FROM student WHERE NAME = ‘雷歐’ AND age = 1200;EXPLAIN SELECT * FROM student WHERE NAME = ‘泰羅’ AND age = 800 AND phone = ‘15714447664’;
執行結果,三個都用到了索引,但是key_len是不同的,key_len=197,表示所有索引都使用到了
當建立了索引列後,能在 wherel條件中使用索引的儘量所用。
2。最佳左字首法則
最左字首法則:
指的是查詢從索引的最左前列開始並且不跳過索引中的列。
我們定義的索引順序是
name_age_phone
,所以查詢的時候也應該從name開始,然後age,然後phone
情況1
:從age、phone開始查詢,tpye=All,key = null,沒使用索引
情況2
:從phone開始查詢,type=All,key=null,未使用索引
情況3
:從name開始,type=ref,使用了索引
3.範圍條件放最後
沒有使用範圍查詢,key_len=197,使用到了name+age+phone組合索引
EXPLAIN SELECT * FROM student WHERE NAME = ‘泰羅’ AND age = 1000 AND phone = ‘15717177664’;
使用了範圍查詢,key_len從197變為158,即除了name和age,phone索引失效了
EXPLAIN SELECT * FROM student WHERE NAME = ‘泰羅’ AND age > 800 AND phone = ‘15717177664’;
key_len=name(153)+age(5)
4.不在索引列上做任何操作
EXPLAIN SELECT * FROM student WHERE NAME = ‘泰羅’;EXPLAIN SELECT * FROM student WHERE left(NAME,1) = ‘泰羅’;
不做計算,key_len有值,key_len=153,有使用name索引
做了擷取結算,type=All,key_len=null,未使用索引
5。不等於要甚用
mysql 在使用不等於(!= 或者<>)的時候無法使用索引會導致全表掃描
#有使用到索引EXPLAIN SELECT * FROM student WHERE NAME = ‘泰羅’;#不等於查詢,未使用到索引EXPLAIN SELECT * FROM student WHERE NAME != ‘泰羅’;EXPLAIN SELECT * FROM student WHERE NAME <> ‘泰羅’;#如果定要需要使用不等於,請用覆蓋索引EXPLAIN SELECT name,age,phone FROM student WHERE NAME != ‘泰羅’;EXPLAIN SELECT name,age,phone FROM student WHERE NAME <> ‘泰羅’;
使用不等於查詢,跳過索引
使用不等於查詢,同時使用覆蓋索引,此時可以使用到索引
6。Null/Not null有影響
修改為非空
那麼為not null,此時導致索引失效
EXPLAIN select * from student where name is null;EXPLAIN select * from student where name is not null;
改為可以為空
查詢為空,索引起作用了
查詢非空索引失效
解決方法:
使用覆蓋索引(覆蓋索引解千愁)
7、Like 查詢要當心
like 以萬用字元開頭(‘%abc。。。’)mysql 索引失效會變成全表掃描的操作
#like 以萬用字元開頭(‘%abc。。。’)mysql 索引失效會變成全表掃描的操作#索引有效EXPLAIN select * from student where name =‘泰羅’;#索引失效EXPLAIN select * from student where name like ‘%泰羅%’;#索引失效EXPLAIN select * from student where name like ‘%泰羅’;#索引有效EXPLAIN select * from student where name like ‘泰羅%’;解決方式:覆蓋索引EXPLAIN select name,age,phone from student where name like ‘%泰羅%’;
使用覆蓋索引能夠解決
8.字元型別加引號
字串不加單引號索引失效(這個看著有點雞肋了,一般查詢字串都會加上引號)
#不加引號導致索引失效EXPLAIN select * from student where name =11;EXPLAIN select * from student where name =‘泰羅’;
使用覆蓋索引解決
三、最佳化3
1。OR 改 UNION 效率高
未使用索引EXPLAIN select * from student where name=‘泰羅’ or name = ‘雷歐’;使用索引EXPLAINselect * from student where name=‘泰羅’UNIONselect * from student where name = ‘雷歐’;解決方式:覆蓋索引EXPLAIN select name,age from student where name=‘泰羅’ or name = ‘雷歐’;
使用or未使用到索引
使用union,使用了索引
解決方式:覆蓋索引