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常見優化方案是什么的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！