版權(quán)聲明：本文為博主原創(chuàng)文章，未經(jīng)博主允許不得轉(zhuǎn)載。 https://blog.csdn.net/a724888/article/details/79394168
本文不堆疊網(wǎng)上海量的sql優(yōu)化技巧或是訣竅。只通過(guò)兩個(gè)淺顯易懂又實(shí)用的例子介紹mysql的sql語(yǔ)句優(yōu)化。
首先介紹一下一般的大表優(yōu)化方案。當(dāng)MySQL單表記錄數(shù)過(guò)大時(shí)，增刪改查性能都會(huì)急劇下降，可以參考以下步驟來(lái)優(yōu)化：
單表優(yōu)化
微信公眾號(hào)【黃小斜】大廠程序員，互聯(lián)網(wǎng)行業(yè)新知，終身學(xué)習(xí)踐行者。關(guān)注后回復(fù)「Java」、「Python」、「C++」、「大數(shù)據(jù)」、「機(jī)器學(xué)習(xí)」、「算法」、「AI」、「Android」、「前端」、「iOS」、「考研」、「BAT」、「校招」、「筆試」、「面試」、「面經(jīng)」、「計(jì)算機(jī)基礎(chǔ)」、「LeetCode」 等關(guān)鍵字可以獲取對(duì)應(yīng)的免費(fèi)學(xué)習(xí)資料。
?
除非單表數(shù)據(jù)未來(lái)會(huì)一直不斷上漲，否則不要一開(kāi)始就考慮拆分，拆分會(huì)帶來(lái)邏輯、部署、運(yùn)維的各種復(fù)雜度，一般以整型值為主的表在千萬(wàn)級(jí)以下，字符串為主的表在五百萬(wàn)以下是沒(méi)有太大問(wèn)題的。而事實(shí)上很多時(shí)候MySQL單表的性能依然有不少優(yōu)化空間，甚至能正常支撐千萬(wàn)級(jí)以上的數(shù)據(jù)量：
字段
盡量使用
TINYINT、SMALLINT、MEDIUM_INT作為整數(shù)類(lèi)型而非INT，如果非負(fù)則加上UNSIGNEDVARCHAR的長(zhǎng)度只分配真正需要的空間使用枚舉或整數(shù)代替字符串類(lèi)型
盡量使用
TIMESTAMP而非DATETIME，單表不要有太多字段，建議在20以內(nèi)
避免使用NULL字段，很難查詢優(yōu)化且占用額外索引空間
用整型來(lái)存IP
索引
索引并不是越多越好，要根據(jù)查詢有針對(duì)性的創(chuàng)建，考慮在
WHERE和ORDER BY命令上涉及的列建立索引，可根據(jù)EXPLAIN來(lái)查看是否用了索引還是全表掃描應(yīng)盡量避免在
WHERE子句中對(duì)字段進(jìn)行NULL值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描值分布很稀少的字段不適合建索引，例如"性別"這種只有兩三個(gè)值的字段
字符字段只建前綴索引
字符字段最好不要做主鍵
不用外鍵，由程序保證約束
盡量不用
UNIQUE，由程序保證約束使用多列索引時(shí)主意順序和查詢條件保持一致，同時(shí)刪除不必要的單列索引
查詢SQL
可通過(guò)開(kāi)啟慢查詢?nèi)罩緛?lái)找出較慢的SQL
不做列運(yùn)算：
SELECT id WHERE age + 1 = 10，任何對(duì)列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫(kù)教程函數(shù)、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至等號(hào)右邊sql語(yǔ)句盡可能簡(jiǎn)單：一條sql只能在一個(gè)cpu運(yùn)算；大語(yǔ)句拆小語(yǔ)句，減少鎖時(shí)間；一條大sql可以堵死整個(gè)庫(kù)
不用
SELECT *OR改寫(xiě)成IN：OR的效率是n級(jí)別，IN的效率是log(n)級(jí)別，in的個(gè)數(shù)建議控制在200以內(nèi)不用函數(shù)和觸發(fā)器，在應(yīng)用程序?qū)崿F(xiàn)
避免
%xxx式查詢少用
JOIN使用同類(lèi)型進(jìn)行比較，比如用
'123'和'123'比，123和123比盡量避免在
WHERE子句中使用!=或<>操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描對(duì)于連續(xù)數(shù)值，使用
BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5列表數(shù)據(jù)不要拿全表，要使用
LIMIT來(lái)分頁(yè)，每頁(yè)數(shù)量也不要太大
引擎
目前廣泛使用的是MyISAM和InnoDB兩種引擎：
MyISAM
MyISAM引擎是MySQL 5.1及之前版本的默認(rèn)引擎，它的特點(diǎn)是：
不支持行鎖，讀取時(shí)對(duì)需要讀到的所有表加鎖，寫(xiě)入時(shí)則對(duì)表加排它鎖
不支持事務(wù)
不支持外鍵
不支持崩潰后的安全恢復(fù)
在表有讀取查詢的同時(shí)，支持往表中插入新紀(jì)錄
支持
BLOB和TEXT的前500個(gè)字符索引，支持全文索引支持延遲更新索引，極大提升寫(xiě)入性能
對(duì)于不會(huì)進(jìn)行修改的表，支持壓縮表，極大減少磁盤(pán)空間占用
InnoDB
InnoDB在MySQL 5.5后成為默認(rèn)索引，它的特點(diǎn)是：
支持行鎖，采用MVCC來(lái)支持高并發(fā)
支持事務(wù)
支持外鍵
支持崩潰后的安全恢復(fù)
不支持全文索引
總體來(lái)講，MyISAM適合SELECT密集型的表，而InnoDB適合INSERT和UPDATE密集型的表
0、自己寫(xiě)的海量數(shù)據(jù)sql優(yōu)化實(shí)踐
首先是建表和導(dǎo)數(shù)據(jù)的過(guò)程。
參考https://nsimple.top/archives/mysql-create-million-data.html
有時(shí)候我們需要對(duì)大數(shù)據(jù)進(jìn)行測(cè)試，本地一般沒(méi)有那么多數(shù)據(jù)，就需要我們自己生成一些。下面會(huì)借助內(nèi)存表的特點(diǎn)進(jìn)行生成百萬(wàn)條測(cè)試數(shù)據(jù)。
- 創(chuàng)建一個(gè)臨時(shí)內(nèi)存表, 做數(shù)據(jù)插入的時(shí)候會(huì)比較快些
SQL
-- 創(chuàng)建一個(gè)臨時(shí)內(nèi)存表
DROP TABLE IF EXISTS `vote_record_memory`;
CREATE TABLE `vote_record_memory` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` varchar(20) NOT NULL DEFAULT '',
`vote_num` int(10) unsigned NOT NULL DEFAULT '0',
`group_id` int(10) unsigned NOT NULL DEFAULT '0',
`status` tinyint(2) unsigned NOT NULL DEFAULT '1',
`create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
- -- 創(chuàng)建一個(gè)普通表，用作模擬大數(shù)據(jù)的測(cè)試用例
SQL
DROP TABLE IF EXISTS `vote_record`;
CREATE TABLE `vote_record` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` varchar(20) NOT NULL DEFAULT '' COMMENT '用戶Id',
`vote_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '投票數(shù)',
`group_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用戶組id 0-未激活用戶 1-普通用戶 2-vip用戶 3-管理員用戶',
`status` tinyint(2) unsigned NOT NULL DEFAULT '1' COMMENT '狀態(tài) 1-正常 2-已刪除',
`create_time` int(10) unsigned NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '創(chuàng)建時(shí)間',
PRIMARY KEY (`id`),
KEY `index_user_id` (`user_id`) USING HASH COMMENT '用戶ID哈希索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='投票記錄表';
- 為了數(shù)據(jù)的隨機(jī)性和真實(shí)性，我們需要?jiǎng)?chuàng)建一個(gè)可生成長(zhǎng)度為n的隨機(jī)字符串的函數(shù)。
SQL
-- 創(chuàng)建生成長(zhǎng)度為n的隨機(jī)字符串的函數(shù)
DELIMITER // -- 修改MySQL delimiter：'//'
DROP FUNCTION IF EXISTS `rand_string` //
SET NAMES utf8 //
CREATE FUNCTION `rand_string` (n INT) RETURNS VARCHAR(255) CHARSET 'utf8'
BEGIN
DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE return_str varchar(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1));
SET i = i+1;
END WHILE;
RETURN return_str;
END //
- 為了操作方便，我們?cè)賱?chuàng)建一個(gè)插入數(shù)據(jù)的存儲(chǔ)過(guò)程
SQL
-- 創(chuàng)建插入數(shù)據(jù)的存儲(chǔ)過(guò)程
DROP PROCEDURE IF EXISTS `add_vote_record_memory` //
CREATE PROCEDURE `add_vote_record_memory`(IN n INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE vote_num INT DEFAULT 0;
DECLARE group_id INT DEFAULT 0;
DECLARE status TINYINT DEFAULT 1;
WHILE i < n DO
SET vote_num = FLOOR(1 + RAND() * 10000);
SET group_id = FLOOR(0 + RAND()*3);
SET status = FLOOR(1 + RAND()*2);
INSERT INTO `vote_record_memory` VALUES (NULL, rand_string(20), vote_num, group_id, status, NOW());
SET i = i + 1;
END WHILE;
END //
DELIMITER ; -- 改回默認(rèn)的 MySQL delimiter：';'
- 開(kāi)始執(zhí)行存儲(chǔ)過(guò)程，等待生成數(shù)據(jù)(10W條生成大約需要40分鐘)
SQL
-- 調(diào)用存儲(chǔ)過(guò)程 生成100W條數(shù)據(jù)
CALL add_vote_record_memory(1000000);
- 查詢內(nèi)存表已生成記錄(為了下步測(cè)試，目前僅生成了105645條)
SQL
SELECT count(*) FROM `vote_record_memory`;
-- count(*)
-- 105646
- 把數(shù)據(jù)從內(nèi)存表插入到普通表中(10w條數(shù)據(jù)13s就插入完了)
SQL
INSERT INTO vote_record SELECT * FROM `vote_record_memory`;
- 查詢普通表已的生成記錄
SQL
SELECT count(*) FROM `vote_record`;
-- count(*)
-- 105646
- 如果一次性插入普通表太慢，可以分批插入，這就需要寫(xiě)個(gè)存儲(chǔ)過(guò)程了：
SQL
-- 參數(shù)n是每次要插入的條數(shù)
-- lastid是已導(dǎo)入的最大id
CREATE PROCEDURE `copy_data_from_tmp`(IN n INT)
BEGIN
DECLARE lastid INT DEFAULT 0;
SELECT MAX(id) INTO lastid FROM `vote_record`;
INSERT INTO `vote_record` SELECT * FROM `vote_record_memory` where id > lastid LIMIT n;
END
- 調(diào)用存儲(chǔ)過(guò)程:
SQL
-- 調(diào)用存儲(chǔ)過(guò)程 插入60w條
CALL copy_data_from_tmp(600000);
SELECT * FROM vote_record；
全表查詢
建完表以后開(kāi)啟慢查詢?nèi)罩?，具體參考下面的例子，然后學(xué)會(huì)用explain。windows慢日志的位置在c盤(pán)，另外，使用client工具也可以記錄慢日志，所以不一定要用命令行來(lái)執(zhí)行測(cè)試，否則大表數(shù)據(jù)在命令行中要顯示的非常久。
1 全表掃描select * from vote_record
慢日志
SET timestamp=1529034398;
select * from vote_record;
Time: 2018-06-15T03:52:58.804850Z
User@Host: root[root] @ localhost [::1] Id: 74
Query_time: 3.166424 Lock_time: 0.000000 Rows_sent: 900500 Rows_examined: 999999
耗時(shí)3秒，我設(shè)置的門(mén)檻是一秒。所以記錄了下來(lái)。
explain執(zhí)行計(jì)劃
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE vote_record \N ALL \N \N \N \N 996507 100.00 \N
全表掃描耗時(shí)3秒多，用不到索引。
2 select * from vote_record where vote_num > 1000
沒(méi)有索引，所以相當(dāng)于全表掃描，一樣是3.5秒左右
3 select * from vote_record where vote_num > 1000
**加索引create **
CREATE INDEX vote ON vote_record(vote_num);
/索引信息/--------------
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
vote_record 0 PRIMARY 1 id A 996507 (NULL) (NULL) BTREE
vote_record 1 votenum 1 vote_num A 9942 (NULL) (NULL) BTREE
/DDL 信息/------------
CREATE TABLE vote_record (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
user_id varchar(20) NOT NULL DEFAULT '' COMMENT '用戶Id',
vote_num int(10) unsigned NOT NULL DEFAULT '0' COMMENT '投票數(shù)',
group_id int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用戶組id 0-未激活用戶 1-普通用戶 2-vip用戶 3-管理員用戶',
status tinyint(2) unsigned NOT NULL DEFAULT '1' COMMENT '狀態(tài) 1-正常 2-已刪除',
create_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '創(chuàng)建時(shí)間',
PRIMARY KEY (id),
KEY votenum (vote_num)
) ENGINE=InnoDB AUTO_INCREMENT=1000000 DEFAULT CHARSET=utf8 COMMENT='投票記錄表'
explain查看執(zhí)行計(jì)劃
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE vote_record \N ALL votenum,vote \N \N \N 996507 50.00 Using where
還是沒(méi)用到索引，因?yàn)椴环献钭笄熬Y匹配。查詢需要3.5秒左右
最后修改一下sql語(yǔ)句
EXPLAIN SELECT * FROM vote_record WHERE id > 0 AND vote_num > 1000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE vote_record \N range PRIMARY,votenum,vote PRIMARY 4 \N 498253 50.00 Using where
用到了索引，但是只用到了主鍵索引。再修改一次
EXPLAIN SELECT * FROM vote_record WHERE id > 0 AND vote_num = 1000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE vote_record \N index_merge PRIMARY,votenum,vote votenum,PRIMARY 8,4 \N 51 100.00 Using intersect(votenum,PRIMARY); Using where
用到了兩個(gè)索引，votenum,PRIMARY。
這是為什么呢。
再看一個(gè)語(yǔ)句
EXPLAIN SELECT * FROM vote_record WHERE id = 1000 AND vote_num > 1000
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE vote_record \N const PRIMARY,votenum PRIMARY 4 const 1 100.00 \N
也只有主鍵用到了索引。這是因?yàn)橹挥凶钭笄熬Y索引可以用>或<，其他索引用<或者>會(huì)導(dǎo)致用不到索引。
下面是幾個(gè)網(wǎng)上參考的例子：
一：索引是sql語(yǔ)句優(yōu)化的關(guān)鍵，學(xué)會(huì)使用慢日志和執(zhí)行計(jì)劃分析sql
背景：使用A電腦安裝mysql,B電腦通過(guò)xshell方式連接，數(shù)據(jù)內(nèi)容我都已經(jīng)創(chuàng)建好，現(xiàn)在我已正常的進(jìn)入到mysql中
步驟1：設(shè)置慢查詢?nèi)罩镜某瑫r(shí)時(shí)間，先查看日志存放路徑查詢慢日志的地址，因?yàn)橛新樵兊膬?nèi)容，就會(huì)到這個(gè)日志中：
<pre>show global variables like "%slow%";</pre>
2.開(kāi)啟慢查詢?nèi)罩?/p>
<pre>set global slow_query_log=on;</pre>
3.查看慢查詢?nèi)罩镜脑O(shè)置時(shí)間，是否是自己需要的
<pre>show global variables like "%long%";</pre>
4.如果不是自己想的時(shí)間，修改慢查詢時(shí)間，只要超過(guò)了以下的設(shè)置時(shí)間，查詢的日志就會(huì)到剛剛的日志中，我設(shè)置查詢時(shí)間超過(guò)1S就進(jìn)入到慢查詢?nèi)罩局?/p>
<pre>set global long_query_time=1;</pre>
5.大數(shù)據(jù)已準(zhǔn)備，進(jìn)行數(shù)據(jù)的查詢，xshell最好開(kāi)兩個(gè)窗口，一個(gè)查看日志，一個(gè)執(zhí)行內(nèi)容
<pre>Sql查詢語(yǔ)句：select sql_no_cache * from employees_tmp where first_name='Duangkaew' and gender='M'</pre>
發(fā)現(xiàn)查數(shù)據(jù)的總時(shí)間去掉了17.74S
查看日志：打開(kāi)日志
標(biāo)記1：執(zhí)行的sql語(yǔ)句
標(biāo)記2：執(zhí)行sql的時(shí)間，我的是10點(diǎn)52執(zhí)行的
標(biāo)記3：使用那臺(tái)機(jī)器
標(biāo)記4：執(zhí)行時(shí)間，query_tims,查詢數(shù)據(jù)的時(shí)間
標(biāo)記5：不知道是干嘛的
標(biāo)記6：執(zhí)行耗時(shí)的sql語(yǔ)句，我在想我1的應(yīng)該是截取錯(cuò)了！但是記住最后一定是顯示耗時(shí)是因?yàn)閳?zhí)行什么sql造成的
6.執(zhí)行打印計(jì)劃，主要是查看是否使用了索引等其他內(nèi)容,主要就是在sql前面加上explain 關(guān)鍵字
<pre>explain select sql_no_cache * from employees_tmp where first_name='Duangkaew' and gender='M';</pre>
描述extra中，表示只使用了where條件，沒(méi)有其他什么索引之類(lèi)的
7.進(jìn)行sql優(yōu)化，建一個(gè)fist_name的索引，索引就是將你需要的數(shù)據(jù)先給篩選出來(lái)，這樣就可以節(jié)省很多掃描時(shí)間
<pre>create index firstname on employees_tmp(first_name);</pre>
注：創(chuàng)建索引時(shí)會(huì)很慢，是對(duì)整個(gè)表做了一個(gè)復(fù)制功能，并進(jìn)行數(shù)據(jù)的一些分類(lèi)（我猜是這樣，所以會(huì)很慢）
8.查看建立的索引
<pre>show index from employees_tmp;</pre>
9.在執(zhí)行查詢語(yǔ)句，查看語(yǔ)句的執(zhí)行時(shí)間
<pre>select sql_no_cache * from employees_tmp where first_name='Duangkaew' and gender='M'</pre>
發(fā)現(xiàn)時(shí)間已經(jīng)有所提升了，其實(shí)選擇索引也不一開(kāi)始就知道，我們?cè)谠囋囀褂眯詣e，gender進(jìn)行索引
10.刪除已經(jīng)有的索引，刪除索引：
<pre>drop index first_name on employees_tmp;</pre>
11.創(chuàng)建性別的索引(性別是不怎么好的索引方式，因?yàn)橛泻芏嘀貜?fù)數(shù)據(jù))
<pre>create index index_gendar on employees_tmp(gender);</pre>
在執(zhí)行sql語(yǔ)句查詢數(shù)據(jù)，查看查詢執(zhí)行時(shí)間，沒(méi)有創(chuàng)建比較優(yōu)秀的索引，導(dǎo)致查詢時(shí)間還變長(zhǎng)了，
為嘛還變長(zhǎng)了，這個(gè)我沒(méi)有弄懂
12.我們?cè)谠囋囀褂脛?chuàng)建組合索引，使用性別和姓名
<pre>alter table employees_tmp add index idx_union (first_name,gender);</pre>
在執(zhí)行sql查看sql數(shù)據(jù)的執(zhí)行時(shí)間
<pre>select sql_no_cache * from employees_tmp where first_name='Duangkaew' and gender='M'</pre>
速度提升了N多倍啊
查看創(chuàng)建的索引
<pre>show index from employees_tmp;</pre>
索引建的好真的一個(gè)好幫手，建不好就是費(fèi)時(shí)的一個(gè)操作
目前還不知道為什么建立性別的索引會(huì)這么慢
二：sql優(yōu)化注意要點(diǎn)，比如索引是否用到，查詢優(yōu)化是否改變了執(zhí)行計(jì)劃，以及一些細(xì)節(jié)
場(chǎng)景
我用的數(shù)據(jù)庫(kù)是mysql5.6，下面簡(jiǎn)單的介紹下場(chǎng)景
課程表
create table Course(
c_id int PRIMARY KEY,
name varchar(10)
)
數(shù)據(jù)100條
學(xué)生表:
create table Student(
id int PRIMARY KEY,
name varchar(10)
)
數(shù)據(jù)70000條
學(xué)生成績(jī)表SC
CREATE table SC(
sc_id int PRIMARY KEY,
s_id int,
c_id int,
score int
)
數(shù)據(jù)70w條
查詢目的：
查找語(yǔ)文考100分的考生
查詢語(yǔ)句：
select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )
執(zhí)行時(shí)間：30248.271s
暈,為什么這么慢，先來(lái)查看下查詢計(jì)劃：
EXPLAIN
select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )
發(fā)現(xiàn)沒(méi)有用到索引，type全是ALL，那么首先想到的就是建立一個(gè)索引，建立索引的字段當(dāng)然是在where條件的字段。
先給sc表的c_id和score建個(gè)索引
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
再次執(zhí)行上述查詢語(yǔ)句，時(shí)間為: 1.054s
快了3w多倍，大大縮短了查詢時(shí)間，看來(lái)索引能極大程度的提高查詢效率，看來(lái)建索引很有必要，很多時(shí)候都忘記建
索引了，數(shù)據(jù)量小的的時(shí)候壓根沒(méi)感覺(jué)，這優(yōu)化感覺(jué)挺爽。
但是1s的時(shí)間還是太長(zhǎng)了，還能進(jìn)行優(yōu)化嗎，仔細(xì)看執(zhí)行計(jì)劃：
查看優(yōu)化后的sql:
SELECT
`YSB`.`s`.`s_id` AS `s_id`,
`YSB`.`s`.`name` AS `name`
FROM
`YSB`.`Student` `s`
WHERE
< in_optimizer > (
`YSB`.`s`.`s_id` ,< EXISTS > (
SELECT
1
FROM
`YSB`.`SC` `sc`
WHERE
(
(`YSB`.`sc`.`c_id` = 0)
AND (`YSB`.`sc`.`score` = 100)
AND (
< CACHE > (`YSB`.`s`.`s_id`) = `YSB`.`sc`.`s_id`
)
)
)
)
補(bǔ)充：這里有網(wǎng)友問(wèn)怎么查看優(yōu)化后的語(yǔ)句
方法如下：
在命令窗口執(zhí)行
有type=all
按照我之前的想法，該sql的執(zhí)行的順序應(yīng)該是先執(zhí)行子查詢
select s_id from SC sc where sc.c_id = 0 and sc.score = 100
耗時(shí)：0.001s
得到如下結(jié)果：
然后再執(zhí)行
select s.* from Student s where s.s_id in(7,29,5000)
耗時(shí)：0.001s
這樣就是相當(dāng)快了啊，Mysql竟然不是先執(zhí)行里層的查詢，而是將sql優(yōu)化成了exists子句，并出現(xiàn)了EPENDENT SUBQUERY，
mysql是先執(zhí)行外層查詢，再執(zhí)行里層的查詢，這樣就要循環(huán)70007*11=770077次。
那么改用連接查詢呢？
SELECT s.* from
Student s
INNER JOIN SC sc
on sc.s_id = s.s_id
where sc.c_id=0 and sc.score=100
這里為了重新分析連接查詢的情況，先暫時(shí)刪除索引sc_c_id_index，sc_score_index
執(zhí)行時(shí)間是：0.057s
效率有所提高，看看執(zhí)行計(jì)劃：
這里有連表的情況出現(xiàn)，我猜想是不是要給sc表的s_id建立個(gè)索引
CREATE index sc_s_id_index on SC(s_id);
show index from SC
在執(zhí)行連接查詢
時(shí)間: 1.076s，竟然時(shí)間還變長(zhǎng)了，什么原因？查看執(zhí)行計(jì)劃：
優(yōu)化后的查詢語(yǔ)句為：
SELECT
`YSB`.`s`.`s_id` AS `s_id`,
`YSB`.`s`.`name` AS `name`
FROM
`YSB`.`Student` `s`
JOIN `YSB`.`SC` `sc`
WHERE
(
(
`YSB`.`sc`.`s_id` = `YSB`.`s`.`s_id`
)
AND (`YSB`.`sc`.`score` = 100)
AND (`YSB`.`sc`.`c_id` = 0)
)
貌似是先做的連接查詢，再執(zhí)行的where過(guò)濾
回到前面的執(zhí)行計(jì)劃：
這里是先做的where過(guò)濾，再做連表，執(zhí)行計(jì)劃還不是固定的，那么我們先看下標(biāo)準(zhǔn)的sql執(zhí)行順序：
正常情況下是先join再where過(guò)濾，但是我們這里的情況，如果先join，將會(huì)有70w條數(shù)據(jù)發(fā)送join做操，因此先執(zhí)行where
過(guò)濾是明智方案，現(xiàn)在為了排除mysql的查詢優(yōu)化，我自己寫(xiě)一條優(yōu)化后的sql
SELECT
s.*
FROM
(
SELECT
*
FROM
SC sc
WHERE
sc.c_id = 0
AND sc.score = 100
) t
INNER JOIN Student s ON t.s_id = s.s_id
即先執(zhí)行sc表的過(guò)濾，再進(jìn)行表連接，執(zhí)行時(shí)間為：0.054s
和之前沒(méi)有建s_id索引的時(shí)間差不多
查看執(zhí)行計(jì)劃：
先提取sc再連表，這樣效率就高多了，現(xiàn)在的問(wèn)題是提取sc的時(shí)候出現(xiàn)了掃描表，那么現(xiàn)在可以明確需要建立相關(guān)索引
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
再執(zhí)行查詢：
SELECT
s.*
FROM
(
SELECT
*
FROM
SC sc
WHERE
sc.c_id = 0
AND sc.score = 100
) t
INNER JOIN Student s ON t.s_id = s.s_id
執(zhí)行時(shí)間為：0.001s，這個(gè)時(shí)間相當(dāng)靠譜，快了50倍
執(zhí)行計(jì)劃：
我們會(huì)看到，先提取sc，再連表，都用到了索引。
那么再來(lái)執(zhí)行下sql
SELECT s.* from
Student s
INNER JOIN SC sc
on sc.s_id = s.s_id
where sc.c_id=0 and sc.score=100
執(zhí)行時(shí)間0.001s
執(zhí)行計(jì)劃：
這里是mysql進(jìn)行了查詢語(yǔ)句優(yōu)化，先執(zhí)行了where過(guò)濾，再執(zhí)行連接操作，且都用到了索引。
總結(jié)：
1.mysql嵌套子查詢效率確實(shí)比較低
2.可以將其優(yōu)化成連接查詢
3.建立合適的索引
4.學(xué)會(huì)分析sql執(zhí)行計(jì)劃，mysql會(huì)對(duì)sql進(jìn)行優(yōu)化，所以分析執(zhí)行計(jì)劃很重要
由于時(shí)間問(wèn)題，這篇文章先寫(xiě)到這里，后續(xù)再分享其他的sql優(yōu)化經(jīng)歷。
三、海量數(shù)據(jù)分頁(yè)查找時(shí)如何使用主鍵索引進(jìn)行優(yōu)化
mysql百萬(wàn)級(jí)分頁(yè)優(yōu)化
普通分頁(yè)
數(shù)據(jù)分頁(yè)在網(wǎng)頁(yè)中十分多見(jiàn)，分頁(yè)一般都是limit start,offset,然后根據(jù)頁(yè)碼page計(jì)算start
<pre> select * from user limit 1,20</pre>
這種分頁(yè)在幾十萬(wàn)的時(shí)候分頁(yè)效率就會(huì)比較低了，MySQL需要從頭開(kāi)始一直往后計(jì)算，這樣大大影響效率
<pre>SELECT * from user limit 100001,20; //time 0.151s
explain SELECT * from user limit 100001,20;</pre>
我們可以用explain分析下語(yǔ)句，沒(méi)有用到任何索引，MySQL執(zhí)行的行數(shù)是16W+，于是我們可以想用到索引去實(shí)現(xiàn)分頁(yè)
優(yōu)化分頁(yè)
使用主鍵索引來(lái)優(yōu)化數(shù)據(jù)分頁(yè)
<pre> select * from user where id>(select id from user where id>=100000 limit 1) limit 20; //time 0.003s</pre>
使用explain分析語(yǔ)句，MySQL這次掃描的行數(shù)是8W+，時(shí)間也大大縮短。
<pre> explain select * from user where id>(select id from user where id>=100000 limit 1) limit 20;</pre>
總結(jié)
在數(shù)據(jù)量比較大的時(shí)候，我們盡量去利用索引來(lái)優(yōu)化語(yǔ)句。上面的優(yōu)化方法如果id不是主鍵索引，查詢效率比第一種還要低點(diǎn)。我們可以先使用explain來(lái)分析語(yǔ)句，查看語(yǔ)句的執(zhí)行順序和執(zhí)行性能。
