MySQL常見優(yōu)化方案匯總

更新時(shí)間：2022年01月17日 11:46:38 作者：一點(diǎn)光輝

mysql數(shù)據(jù)庫是中小微企業(yè)常用的一種數(shù)據(jù)化管理工具，它具有輕便，簡潔，免費(fèi)等特點(diǎn)。今天通過本文給大家介紹MySQL常見優(yōu)化方案匯總，感興趣的朋友一起看看吧

思考sql優(yōu)化的幾個(gè)地方，我把他做了個(gè)分類，方便理解

select [字段優(yōu)化1]：主要是覆蓋索引
from []
where [條件優(yōu)化2]
union [聯(lián)合查詢優(yōu)化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 '創(chuàng)建時(shí)間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

添加索引，添加索引之后

key_len：根據(jù)這個(gè)值，就可以判斷索引使用情況，特別是在組合索引的時(shí)候，判斷所有的索引字段是否都被查詢用到。

key_len計(jì)算方式簡單介紹

latin1占用1個(gè)字節(jié)，gbk占用2個(gè)字節(jié)，utf8占用3個(gè)字節(jié)

不允許為空：

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);

添加數(shù)據(jù)

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());

一、優(yōu)化點(diǎn)1：字段優(yōu)化

覆蓋索引盡量用

簡單解釋解釋，索引是哪幾個(gè)列，就查詢哪幾個(gè)列：覆蓋索引的原因：索引是高效找到行的一個(gè)方法，但是一般數(shù)據(jù)庫也能使用索引找到一個(gè)列的數(shù)據(jù)，因此它不必讀取整個(gè)行。畢竟索引葉子節(jié)點(diǎn)存儲了它們索引的數(shù)據(jù); 當(dāng)能通過讀取索引就可以得到想要的數(shù)據(jù)，那就不需要讀取行了。一個(gè)索引包含了(或覆蓋了)滿足查詢結(jié)果的數(shù)據(jù)就叫做覆蓋索引注意：有索引盡量不要使用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';

未使用覆蓋索引

使用完全覆蓋索引

使用包含覆蓋索引

加上主鍵還是覆蓋索引

二、優(yōu)化點(diǎn)2：where優(yōu)化

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';

執(zhí)行結(jié)果，三個(gè)都用到了索引，但是key_len是不同的，key_len=197，表示所有索引都使用到了

當(dāng)建立了索引列后，能在 wherel 條件中使用索引的盡量所用。

2.最佳左前綴法則

最左前綴法則：指的是查詢從索引的最左前列開始并且不跳過索引中的列。我們定義的索引順序是 name_age_phone ，所以查詢的時(shí)候也應(yīng)該從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變?yōu)?58，即除了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) = '泰羅';

不做計(jì)算,key_len有值，key_len=153，有使用name索引

做了截取結(jié)算，type=All,key_len=null，未使用索引

5.不等于要甚用

mysql 在使用不等于 (!= 或者 <>) 的時(shí)候無法使用索引會導(dǎo)致全表掃描

#有使用到索引
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 <> '泰羅';

使用不等于查詢，跳過索引

使用不等于查詢，同時(shí)使用覆蓋索引，此時(shí)可以使用到索引

6.Null/Not null有影響

修改為非空

那么為not null，此時(shí)導(dǎo)致索引失效

EXPLAIN select * from student where name is null;
EXPLAIN select * from student where name is not null;

改為可以為空

查詢?yōu)榭?，索引起作用?/p>

查詢非空索引失效

解決方法：

使用覆蓋索引（覆蓋索引解千愁）

7、Like 查詢要當(dāng)心 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.字符類型加引號

字符串不加單引號索引失效（這個(gè)看著有點(diǎn)雞肋了，一般查詢字符串都會加上引號）

使用覆蓋索引解決

三、優(yōu)化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未使用到索引