MySQL常見優化方案匯總

Posted on by

mysql優化是我們日常工作經常遇到的問題，今天給大傢說下MySQL常見的幾種優化方案。

註：原始資料來自享學課堂，自己加上整理和思考

思考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.字符類型加引號

字符串不加單引號索引失效（這個看著有點雞肋瞭，一般查詢字符串都會加上引號）

使用覆蓋索引解決

三、優化3

1.OR 改 UNION 效率高

未使用索引
EXPLAIN select * from student where name='泰羅' or name = '雷歐';
 
使用索引
EXPLAIN
select * from student where name='泰羅'
UNION
select * from student where name = '雷歐';
 
解決方式：覆蓋索引
EXPLAIN select name,age from student where name='泰羅' or name = '雷歐';

使用or未使用到索引

使用union，使用瞭索引

解決方式：覆蓋索引

到此這篇關於MySQL常見優化方案匯總的文章就介紹到這瞭,更多相關MySQL優化方案內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：