本篇是MySQL知識(shí)體系總結(jié)系列的第二篇，該篇的主要內(nèi)容是通過explain逐步分析sql，并通過修改sql語句與建立索引的方式對(duì)sql語句進(jìn)行調(diào)優(yōu)，也可以通過查看日志的方式，了解sql的執(zhí)行情況，還介紹了MySQL數(shù)據(jù)庫的行鎖和表鎖。

一、explain返回列簡介

1、type常用關(guān)鍵字

system > const > eq_ref > ref > range > index > all。

1. system：表僅有一行，基本用不到；
2. const：表最多一行數(shù)據(jù)配合，主鍵查詢時(shí)觸發(fā)較多；
3. eq_ref：對(duì)于每個(gè)來自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯(lián)接類型，除了const類型；
4. ref：對(duì)于每個(gè)來自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀取；
5. range：只檢索給定范圍的行，使用一個(gè)索引來選擇行。當(dāng)使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關(guān)鍵字列時(shí)，可以使用range；
6. index：該聯(lián)接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小；
7. all：全表掃描；

實(shí)際sql優(yōu)化中，最后達(dá)到ref或range級(jí)別。

2、Extra常用關(guān)鍵字

Using index：只從索引樹中獲取信息，而不需要回表查詢；

Using where：WHERE子句用于限制哪一個(gè)行匹配下一個(gè)表或發(fā)送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where并且表聯(lián)接類型為ALL或index，查詢可能會(huì)有一些錯(cuò)誤。需要回表查詢。

Using temporary：mysql常建一個(gè)臨時(shí)表來容納結(jié)果，典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時(shí)；

索引原理及explain用法請(qǐng)參照前一篇：MySQL索引原理，explain詳解

二、觸發(fā)索引代碼實(shí)例

1、建表語句 + 聯(lián)合索引

CREATE TABLE `student` (
  `id` int(10) NOT NULL,
  `name` varchar(20) NOT NULL,
  `age` int(10) NOT NULL,
  `sex` int(11) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `phone` varchar(100) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT NULL,
  `deleted` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_union_index` (`name`,`age`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、使用主鍵查詢

3、使用聯(lián)合索引查詢

4、聯(lián)合索引，但與索引順序不一致

備注：因?yàn)閙ysql優(yōu)化器的緣故，與索引順序不一致，也會(huì)觸發(fā)索引，但實(shí)際項(xiàng)目中盡量順序一致。

5、聯(lián)合索引，但其中一個(gè)條件是 >

6、聯(lián)合索引，order by

where和order by一起使用時(shí)，不要跨索引列使用。

三、單表sql優(yōu)化

1、刪除student表中的聯(lián)合索引。

2、添加索引

alter table student add index student_union_index(name,age,sex);

優(yōu)化一點(diǎn)，但效果不是很好，因?yàn)閠ype是index類型，extra中依然存在using where。

3、更改索引順序

因?yàn)閟ql的編寫過程

select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

解析過程

from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

 因此我懷疑是聯(lián)合索引建的順序問題，導(dǎo)致觸發(fā)索引的效果不好。are you sure？試一下就知道了。

alter table student add index student_union_index2(age,sex,name);

刪除舊的不用的索引：

drop index student_union_index on student

索引改名

ALTER TABLE student RENAME INDEX student_union_index2 TO student_union_index

更改索引順序之后，發(fā)現(xiàn)type級(jí)別發(fā)生了變化，由index變?yōu)榱藃ange。

range：只檢索給定范圍的行，使用一個(gè)索引來選擇行。

備注：in會(huì)導(dǎo)致索引失效，所以觸發(fā)using where，進(jìn)而導(dǎo)致回表查詢。

4、去掉in

ref：對(duì)于每個(gè)來自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀??；

index 提升為ref了，優(yōu)化到此結(jié)束。

5、小結(jié)

1. 保持索引的定義和使用順序一致性；
2. 索引需要逐步優(yōu)化，不要總想著一口吃成胖子；
3. 將含in的范圍查詢，放到where條件的最后，防止索引失效；

四、雙表sql優(yōu)化

1、建表語句

CREATE TABLE `student` (
  `id` int(10) NOT NULL,
  `name` varchar(20) NOT NULL,
  `age` int(10) NOT NULL,
  `sex` int(11) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `phone` varchar(100) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT NULL,
  `deleted` int(11) DEFAULT NULL,
  `teacher_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `teacher` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `course` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、左連接查詢

explain select s.name,t.name from student s left join teacher t on s.teacher_id = t.id where t.course = '數(shù)學(xué)'

上一篇介紹過，聯(lián)合查詢時(shí)，小表驅(qū)動(dòng)大表。小表也稱為驅(qū)動(dòng)表。其實(shí)就相當(dāng)于雙重for循環(huán)，小表就是外循環(huán)，第二張表（大表）就是內(nèi)循環(huán)。

雖然最終的循環(huán)結(jié)果都是一樣的，都是循環(huán)一樣的次數(shù)，但是對(duì)于雙重循環(huán)來說，一般建議將數(shù)據(jù)量小的循環(huán)放外層，數(shù)據(jù)量大的放內(nèi)層，這是編程語言的優(yōu)化原則。

再次代碼測試：

student數(shù)據(jù)：四條

teacher數(shù)據(jù)：三條

按照理論分析，teacher應(yīng)該為驅(qū)動(dòng)表。

sql語句應(yīng)該改為：

explain select teacher.name,student.name from teacher left join student on teacher.id = student.id  where teacher.course = '數(shù)學(xué)'

優(yōu)化一般是需要索引的，那么此時(shí)，索引應(yīng)該怎么加呢？往哪個(gè)表上加索引？

索引的基本理念是：索引要建在經(jīng)常使用的字段上。

由on teacher.id = student.id可知，teacher表的id字段使用較為頻繁。

left join on，一般給左表加索引；因?yàn)槭球?qū)動(dòng)表嘛。

alter table teacher add index teacher_index(id);
alter table teacher add index teacher_course(course);

備注：如果extra中出現(xiàn)using join buffer，表明mysql底層覺得sql寫的太差了，mysql加了個(gè)緩存，進(jìn)行優(yōu)化了。

3、小結(jié)

1. 小表驅(qū)動(dòng)大表
2. 索引建立在經(jīng)常查詢的字段上
3. sql優(yōu)化，是一種概率層面的優(yōu)化，是否實(shí)際使用了我們的優(yōu)化，需要通過explain推測。

五、避免索引失效的一些原則

1、復(fù)合索引，不要跨列或無序使用（最佳左前綴）；

2、符合索引，盡量使用全索引匹配；

3、不要在索引上進(jìn)行任何操作，例如對(duì)索引進(jìn)行（計(jì)算、函數(shù)、類型轉(zhuǎn)換），索引失效；

4、復(fù)合索引不能使用不等于（!=或<>）或 is null（is not null），否則索引失效；

5、盡量使用覆蓋索引（using index）；

6、like盡量以常量開頭，不要以%開頭，否則索引失效；如果必須使用%name%進(jìn)行查詢，可以使用覆蓋索引挽救，不用回表查詢時(shí)可以觸發(fā)索引；

7、盡量不要使用類型轉(zhuǎn)換，否則索引失效；

8、盡量不要使用or，否則索引失效；

六、一些其他的優(yōu)化方法

1、exist和in

select name,age from student exist/in (子查詢);

如果主查詢的數(shù)據(jù)集大，則使用in；

如果子查詢的數(shù)據(jù)集大，則使用exist；

2、order by 優(yōu)化

using filesort有兩種算法：雙路排序、雙路排序（根據(jù)IO的次數(shù)）

MySQL4.1之前，默認(rèn)使用雙路排序；雙路：掃描兩次磁盤（①從磁盤讀取排序字段，對(duì)排序字段進(jìn)行排序；②獲取其它字段）。

MySQL4.1之后，默認(rèn)使用單路排序；單路：只讀取一次（全部字段），在buffer中進(jìn)行排序。但單路排序會(huì)有一定的隱患（不一定真的是只有一次IO，有可能多次IO）。

注意：單路排序會(huì)比雙路排序占用更多的buffer。

單路排序時(shí)，如果數(shù)據(jù)量較大，可以調(diào)大buffer的容量大小。

set max_length_for_sort_data = 1024;單位是字節(jié)byte。

如果max_length_for_sort_data值太低，MySQL底層會(huì)自動(dòng)將單路切換到雙路。

太低指的是列的總大小超過了max_length_for_sort_data定義的字節(jié)數(shù)。

提高order by查詢的策略：

1. 選擇使用單路或雙路，調(diào)整buffer的容量大??；
2. 避免select * from student;（① MySQL底層需要對(duì)*進(jìn)行翻譯，消耗性能；② *永遠(yuǎn)不會(huì)觸發(fā)索引覆蓋 using index）；
3. 符合索引不要跨列使用，避免using filesort；
4. 保證全部的排序字段，排序的一致性（都是升序或降序）；

七、sql順序 -> 慢日志查詢

慢查詢?nèi)罩揪褪荕ySQL提供的一種日志記錄，用于記錄MySQL響應(yīng)時(shí)間超過閾值的SQL語句（long_query_time，默認(rèn)10秒） ；

慢日志默認(rèn)是關(guān)閉的，開發(fā)調(diào)優(yōu)時(shí)打開，最終部署時(shí)關(guān)閉。

1、慢查詢?nèi)罩?/h3>

（1）檢查是否開啟了慢查詢?nèi)罩荆?/p>

show variables like '%slow_query_log%'

（2）臨時(shí)開啟：

set global slow_query_log = 1;

（3）重啟MySQL： 

service mysql restart;

（4）永久開啟：

/etc/my.cnf中追加配置：

放到[mysqld]下：

slow_query_log=1

slow_query_log_file=/var/lib/mysql/localhost-slow.log

2、閾值

（1）查看默認(rèn)閾值：

show variables like '%long_query_time%'

（2）臨時(shí)修改默認(rèn)閾值：

set global long_query_time = 5;

（3）永久修改默認(rèn)閾值：

/etc/my.cnf中追加配置：

放到[mysqld]下：

long_query_time = 5;

（4）MySQL中的sleep：

select sleep(5);

（5）查看執(zhí)行時(shí)間超過閾值的sql：

show global status like '%slow_queries%';

八、慢查詢?nèi)罩?--> mysqldumpslow工具

1、mysqldumpslow工具

慢查詢的sql被記錄在日志中，可以通過日志查看具體的慢sql。

cat /var/lib/mysql/localhost-slow.log

通過mysqldumpslow工具查看慢sql，可以通過一些過濾條件，快速查出需要定位的慢sql。

mysqldumpslow --help

參數(shù)簡要介紹：

s：排序方式

r：逆序

l：鎖定時(shí)間

g：正則匹配模式

2、查詢不同條件下的慢sql

（1）返回記錄最多的3個(gè)SQL

mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log

（2）獲取訪問次數(shù)最多的3個(gè)SQL

mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

（3）按時(shí)間排序，前10條包含left join查詢語句的SQL

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log

九、分析海量數(shù)據(jù)

1、show profiles

打開此功能：set profiling = on;

show profiles會(huì)記錄所有profileing打來之后，全部SQL查詢語句所花費(fèi)的時(shí)間。

缺點(diǎn)是不夠精確，確定不了是執(zhí)行哪部分所消耗的時(shí)間，比如CPU、IO。

2、精確分析，sql診斷

show profile all for query  上一步查詢到的query_id。

3、全局查詢?nèi)罩?/h3>

show variables like '%general_log%'

開啟全局日志：

set global general_log = 1;

set global log_output = table;

十、鎖機(jī)制詳解

1、操作分類

讀寫：對(duì)同一個(gè)數(shù)據(jù)，多個(gè)讀操作可以同時(shí)進(jìn)行，互不干擾。

寫鎖：如果當(dāng)前寫操作沒有完畢，則無法進(jìn)行其它的讀寫操作。

2、操作范圍

表鎖：一次性對(duì)一張表整體加鎖。

如MyISAM存儲(chǔ)引擎使用表鎖，開銷小、加鎖快、無死鎖；但鎖的范圍大，容易發(fā)生沖突、并發(fā)度低。

行鎖：一次性對(duì)一條數(shù)據(jù)加鎖。

如InnoDB存儲(chǔ)引擎使用的就是行鎖，開銷大、加鎖慢、容易出現(xiàn)死鎖；鎖的范圍較小，不易發(fā)生鎖沖突，并發(fā)度高（很小概率發(fā)生高并發(fā)問題：臟讀、幻讀、不可重復(fù)讀）

lock table 表1 read/write，表2 read/write，...

查看加鎖的表：

show open tables;

3、加讀鎖，代碼實(shí)例

會(huì)話0：
lock table student read;
select * from student; --查，可以
delete from student where id = 1;--增刪改，不可以
select * from user; --查，不可以
delete from user where id = 1;--增刪改，不可以

如果某一個(gè)會(huì)話對(duì)A表加了read鎖，則該會(huì)話可以對(duì)A表進(jìn)行讀操作、不能進(jìn)行寫操作。即如果給A表加了讀鎖，則當(dāng)前會(huì)話只能對(duì)A表進(jìn)行讀操作，其它表都不能操作

會(huì)話1：
select * from student; --查，可以
delete from student where id = 1;--增刪改，會(huì)“等待”會(huì)話0將鎖釋放
會(huì)話1：
select * from user; --查，可以
delete from user where id = 1;--增刪改，可以

會(huì)話0給A表加了鎖，其它會(huì)話的操作①可以對(duì)其它表進(jìn)行讀寫操作②對(duì)A表：讀可以，寫需要等待釋放鎖。

4、加寫鎖

會(huì)話0：
lock table student write;

當(dāng)前會(huì)話可以對(duì)加了寫鎖的表，可以進(jìn)行任何增刪改查操作；但是不能操作其它表；

其它會(huì)話：

對(duì)會(huì)話0中對(duì)加寫鎖的表，可以進(jìn)行增刪改查的前提是：等待會(huì)話0釋放寫鎖。

5、MyISAM表級(jí)鎖的鎖模式

MyISAM在執(zhí)行查詢語句前，會(huì)自動(dòng)給涉及的所有表加讀鎖，在執(zhí)行增刪改前，會(huì)自動(dòng)給涉及的表加寫鎖。

所以對(duì)MyISAM表進(jìn)行操作，會(huì)有如下情況發(fā)生：

（1）對(duì)MyISAM表的讀操作（加讀鎖），不會(huì)阻塞其它會(huì)話（進(jìn)程）對(duì)同一表的讀請(qǐng)求。但會(huì)阻塞對(duì)同一表的寫操作。只有當(dāng)讀鎖釋放后，才會(huì)執(zhí)行其它進(jìn)程的寫操作。

（2）對(duì)MyISAM表的寫操作（加寫鎖），會(huì)阻塞其它會(huì)話（進(jìn)程）對(duì)同一表的讀和寫操作，只有當(dāng)寫鎖釋放后，才會(huì)執(zhí)行其它進(jìn)程的讀寫操作。

6、MyISAM分析表鎖定

查看哪些表加了鎖：

show open tables;1代表被加了鎖

分析表鎖定的嚴(yán)重程度：

show status like 'table%';

Table_locks_immediate：可能獲取到的鎖數(shù)

Table_locks_waited：需要等待的表鎖數(shù)（該值越大，說明存在越大的鎖競爭）

一般建議：Table_locks_immediate/Table_locks_waited > 5000，建議采用InnoDB引擎，否則采用MyISAM引擎。

7、InnoDB分析表鎖定

為了研究行鎖，暫時(shí)將自動(dòng)commit關(guān)閉，set autocommit = 0;

show status like '%innodb_row_lock%';

Innodb_row_lock_current_waits：當(dāng)前正在等待鎖的數(shù)量
Innodb_row_lock_time：等待總時(shí)長。從系統(tǒng)啟動(dòng)到現(xiàn)在一共等待的時(shí)間
Innodb_row_lock_time_avg：平均等待時(shí)長。從系統(tǒng)啟動(dòng)到現(xiàn)在一共等待的時(shí)間
Innodb_row_lock_time_max：最大等待時(shí)長。從系統(tǒng)啟動(dòng)到現(xiàn)在一共等待的時(shí)間
Innodb_row_lock_waits：等待次數(shù)。從系統(tǒng)啟動(dòng)到現(xiàn)在一共等待的時(shí)間

8、加行鎖代碼實(shí)例

（1）查詢student

select id,name,age from student

（2）更新student 

update student set age = 18 where id = 1

（3）加行鎖 

通過select id,name,age from student for update;給查詢加行鎖。

依舊修改成功，原因是MySQL默認(rèn)是自動(dòng)提交的，因此需要暫時(shí)將自動(dòng)commit關(guān)閉

set autocommit = 0;

9、行鎖的注意事項(xiàng)

（1）如果沒有索引，行鎖自動(dòng)轉(zhuǎn)為表鎖。

（2）行鎖只能通過事務(wù)解鎖。

（3）InnoDB默認(rèn)采用行鎖

優(yōu)點(diǎn)：并發(fā)能力強(qiáng)，性能高，效率高

缺點(diǎn)：比表鎖性能損耗大

高并發(fā)用InnoDb，否則用MyISAM。

到此這篇關(guān)于MySql基礎(chǔ)知識(shí)總結(jié)（SQL優(yōu)化篇）的文章就介紹到這了,更多相關(guān)mysql基礎(chǔ)知識(shí)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論