SQL 調(diào)優(yōu)實戰(zhàn)
MySQL優(yōu)化
配置優(yōu)化
配置優(yōu)化指的MySQL 的 server端的配置，一般對于業(yè)務(wù)方而言，可以不用關(guān)注，畢竟會有專門的DBA來處理，但是對于原理的了解，我想，我們開發(fā)，是需要了解的。
MySQL優(yōu)化，也可以參考：超級全面的MySQL優(yōu)化面試解析
基本配置
innodb_buffer_pool_size
這是安裝完InnoDB后第一個應(yīng)該設(shè)置的選項。緩沖池是數(shù)據(jù)和索引緩存的地方：這個值越大越好，這能保證你在大多數(shù)的讀取操作時使用的是內(nèi)存而不是硬盤。典型的值是5-6GB(8GB內(nèi)存)，20-25GB(32GB內(nèi)存)，100-120GB(128GB內(nèi)存)。
innodb_log_file_size
這是redo日志的大小。redo日志被用于確保寫操作快速而可靠并且在崩潰時恢復(fù)。一直到MySQL 5.1，它都難于調(diào)整，因為一方面你想讓它更大來提高性能，另一方面你想讓它更小來使得崩潰后更快恢復(fù)。
幸運(yùn)的是從MySQL 5.5之后，崩潰恢復(fù)的性能的到了很大提升，這樣你就可以同時擁有較高的寫入性能和崩潰恢復(fù)性能了。
一直到MySQL 5.5，redo日志的總尺寸被限定在4GB(默認(rèn)可以有2個log文件)。這在MySQL 5.6里被提高了。
如果你知道你的應(yīng)用程序需要頻繁的寫入數(shù)據(jù)并且你使用的時MySQL 5.6，你可以一開始就把它這是成4G。
max_connections
如果你經(jīng)常看到‘Too many connections'錯誤，是因為max_connections的值太低了。這非常常見因為應(yīng)用程序沒有正確的關(guān)閉數(shù)據(jù)庫連接，你需要比默認(rèn)的151連接數(shù)更大的值。
max_connection值被設(shè)高了(例如1000或更高)之后一個主要缺陷是當(dāng)服務(wù)器運(yùn)行1000個或更高的活動事務(wù)時會變的沒有響應(yīng)。在應(yīng)用程序里使用連接池或者在MySQL里使用進(jìn)程池有助于解決這一問題。
InnoDB配置
innodb_file_per_table
這項設(shè)置告知InnoDB是否需要將所有表的數(shù)據(jù)和索引存放在共享表空間里（innodb_file_per_table = OFF） 或者為每張表的數(shù)據(jù)單獨(dú)放在一個.ibd文件（innodb_file_per_table = ON）。每張表一個文件允許你在drop、truncate或者rebuild表時回收磁盤空間。
這對于一些高級特性也是有必要的，比如數(shù)據(jù)壓縮。但是它不會帶來任何性能收益。你不想讓每張表一個文件的主要場景是：有非常多的表（比如10k+）。MySQL 5.6中，這個屬性默認(rèn)值是ON，因此大部分情況下你什么都不需要做。對于之前的版本你必需在加載數(shù)據(jù)之前將這個屬性設(shè)置為ON，因為它只對新創(chuàng)建的表有影響。
innodb_flush_log_at_trx_commit
默認(rèn)值為1，表示InnoDB完全支持ACID特性。當(dāng)你的主要關(guān)注點(diǎn)是數(shù)據(jù)安全的時候這個值是最合適的，比如在一個主節(jié)點(diǎn)上。但是對于磁盤（讀寫）速度較慢的系統(tǒng)，它會帶來很巨大的開銷，因為每次將改變flush到redo日志都需要額外的fsyncs。
將它的值設(shè)置為2會導(dǎo)致不太可靠（reliable）因為提交的事務(wù)僅僅每秒才flush一次到redo日志，但對于一些場景是可以接受的，比如對于主節(jié)點(diǎn)的備份節(jié)點(diǎn)這個值是可以接受的。如果值為0速度就更快了，但在系統(tǒng)崩潰時可能丟失一些數(shù)據(jù)：只適用于備份節(jié)點(diǎn)。
innodb_flush_method
這項配置決定了數(shù)據(jù)和日志寫入硬盤的方式。一般來說，如果你有硬件RAID控制器，并且其獨(dú)立緩存采用write-back機(jī)制，并有著電池斷電保護(hù)，那么應(yīng)該設(shè)置配置為O_DIRECT；否則，大多數(shù)情況下應(yīng)將其設(shè)為fdatasync（默認(rèn)值）。sysbench是一個可以幫助你決定這個選項的好工具。
innodb_log_buffer_size
這項配置決定了為尚未執(zhí)行的事務(wù)分配的緩存。其默認(rèn)值（1MB）一般來說已經(jīng)夠用了，但是如果你的事務(wù)中包含有二進(jìn)制大對象或者大文本字段的話，這點(diǎn)緩存很快就會被填滿并觸發(fā)額外的I/O操作。看看Innodb_log_waits狀態(tài)變量，如果它不是0，增加innodb_log_buffer_size。
其他設(shè)置
query_cache_size
query cache（查詢緩存）是一個眾所周知的瓶頸，甚至在并發(fā)并不多的時候也是如此。最佳選項是將其從一開始就停用，設(shè)置query_cache_size = 0（現(xiàn)在MySQL 5.6的默認(rèn)值）并利用其他方法加速查詢：優(yōu)化索引、增加拷貝分散負(fù)載或者啟用額外的緩存（比如memcache或redis）。
如果你已經(jīng)為你的應(yīng)用啟用了query cache并且還沒有發(fā)現(xiàn)任何問題，query cache可能對你有用。這是如果你想停用它，那就得小心了。
log_bin
如果你想讓數(shù)據(jù)庫服務(wù)器充當(dāng)主節(jié)點(diǎn)的備份節(jié)點(diǎn)，那么開啟二進(jìn)制日志是必須的。
如果這么做了之后，還別忘了設(shè)置server_id為一個唯一的值。就算只有一個服務(wù)器，如果你想做基于時間點(diǎn)的數(shù)據(jù)恢復(fù)，這（開啟二進(jìn)制日志）也是很有用的：從你最近的備份中恢復(fù)（全量備份），并應(yīng)用二進(jìn)制日志中的修改（增量備份）。
二進(jìn)制日志一旦創(chuàng)建就將永久保存。所以如果你不想讓磁盤空間耗盡，你可以用 PURGE BINARY LOGS 來清除舊文件，或者設(shè)置 expire_logs_days 來指定過多少天日志將被自動清除。
記錄二進(jìn)制日志不是沒有開銷的，所以如果你在一個非主節(jié)點(diǎn)的復(fù)制節(jié)點(diǎn)上不需要它的話，那么建議關(guān)閉這個選項。
skip_name_resolve
當(dāng)客戶端連接數(shù)據(jù)庫服務(wù)器時，服務(wù)器會進(jìn)行主機(jī)名解析，并且當(dāng)DNS很慢時，建立連接也會很慢。
因此建議在啟動服務(wù)器時關(guān)閉skip_name_resolve選項而不進(jìn)行DNS查找。唯一的局限是之后GRANT語句中只能使用IP地址了，因此在添加這項設(shè)置到一個已有系統(tǒng)中必須格外小心。
SQL 調(diào)優(yōu)
一般要進(jìn)行SQL調(diào)優(yōu)，那么就說有慢查詢的SQL，系統(tǒng)或者server可以開啟慢查詢?nèi)罩荆绕涫蔷€上系統(tǒng)，一般都會開啟慢查詢?nèi)罩荆绻新樵儯梢酝ㄟ^日志來過濾。但是知道了有需要優(yōu)化的SQL后，下面要做的就是如何進(jìn)行調(diào)優(yōu)
慢查詢優(yōu)化基本步驟
先運(yùn)行看看是否真的很慢，注意設(shè)置SQL_NO_CACHE
where條件單表查，鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應(yīng)用到表中返回的記錄數(shù)最小的表開始查起，單表每個字段分別查詢，看哪個字段的區(qū)分度最高
explain查看執(zhí)行計劃，是否與1預(yù)期一致（從鎖定記錄較少的表開始查詢）
order by limit 形式的sql語句讓排序的表優(yōu)先查
了解業(yè)務(wù)方使用場景
加索引時參照建索引的幾大原則
觀察結(jié)果，不符合預(yù)期繼續(xù)從0分析
常用調(diào)優(yōu)手段
執(zhí)行計劃explain
在日常工作中，我們有時會開慢查詢?nèi)ビ涗浺恍﹫?zhí)行時間比較久的SQL語句，找出這些SQL語句并不意味著完事了，我們常常用到explain這個命令來查看一個這些SQL語句的執(zhí)行計劃，查看該SQL語句有沒有使用上了索引，有沒有做全表掃描，這都可以通過explain命令來查看。
所以我們深入了解MySQL的基于開銷的優(yōu)化器，還可以獲得很多可能被優(yōu)化器考慮到的訪問策略的細(xì)節(jié)，以及當(dāng)運(yùn)行SQL語句時哪種策略預(yù)計會被優(yōu)化器采用。
使用explain 只需要在原有select 基礎(chǔ)上加上explain關(guān)鍵字就可以了，如下：
mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | servers | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)
簡要解釋下explain各個字段的含義
id : 表示SQL執(zhí)行的順序的標(biāo)識,SQL從大到小的執(zhí)行
select_type：表示查詢中每個select子句的類型
table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的，有時不是真實的表名字
type：表示MySQL在表中找到所需行的方式，又稱“訪問類型”。常用的類型有：ALL, index, range, ref, eq_ref, const, system, NULL（從左到右，性能從差到好）
possible_keys：指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用
Key：key列顯示MySQL實際決定使用的鍵（索引），如果沒有選擇索引，鍵是NULL。
key_len：表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據(jù)表定義計算而得，不是通過表內(nèi)檢索出的）
ref：表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值
rows：表示MySQL根據(jù)表統(tǒng)計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)，理論上行數(shù)越少，查詢性能越好
Extra：該列包含MySQL解決查詢的詳細(xì)信息
EXPLAIN的特性
EXPLAIN不會告訴你關(guān)于觸發(fā)器、存儲過程的信息或用戶自定義函數(shù)對查詢的影響情況
EXPLAIN不考慮各種Cache
EXPLAIN不能顯示MySQL在執(zhí)行查詢時所作的優(yōu)化工作
部分統(tǒng)計信息是估算的，并非精確值
EXPALIN只能解釋SELECT操作，其他操作要重寫為SELECT后查看執(zhí)行計劃。
實戰(zhàn)演練
表結(jié)構(gòu)和查詢語句
假如有如下表結(jié)構(gòu)
circlemessage_idx_0 | CREATE TABLE `circlemessage_idx_0` (
`circle_id` bigint(20) unsigned NOT NULL COMMENT '群組id',
`from_id` bigint(20) unsigned NOT NULL COMMENT '發(fā)送用戶id',
`to_id` bigint(20) unsigned NOT NULL COMMENT '指定接收用戶id',
`msg_id` bigint(20) unsigned NOT NULL COMMENT '消息ID',
`type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '消息類型',
PRIMARY KEY (`msg_id`,`to_id`),
KEY `idx_from_circle` (`from_id`,`circle_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
通過執(zhí)行計劃explain分析如下查詢語句
mysql> explain select msg_id from circlemessage_idx_0 where to_id = 113487 and circle_id=10019063 and msg_id>=6273803462253938690 and from_id != 113487 order by msg_id asc limit 30;
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | circlemessage_idx_0 | range | PRIMARY,idx_from_circle | PRIMARY | 16 | NULL | 349780 | Using where |
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)
mysql> explain select msg_id from circlemessage_idx_0 where to_id = 113487 and circle_id=10019063 and from_id != 113487 order by msg_id asc limit 30;
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | circlemessage_idx_0 | index | idx_from_circle | PRIMARY | 16 | NULL | 30 | Using where |
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
問題分析
通過上面兩個執(zhí)行計劃可以發(fā)現(xiàn)當(dāng)沒有msg_id >= xxx這個查詢條件的時候，檢索的rows要少很多，并且兩者查詢的時候都用到了索引，而且用到的還只是主鍵索引。那說明索引應(yīng)該是不合理的，沒有發(fā)揮最大作用。
分析這個執(zhí)行計劃可以看到，當(dāng)包含msg_id >= xxx 查詢條件的時候，rows有34w多行，這種情況，說明檢索太多，要么就是表里面確實有這么大，要么就是索引不合理沒有用到索引，大都情況是沒用合理用到索引。
列中所用到的索引也是PRIMARY，那就可能是(msg_id,to_id)的其中一個，注意我們建立表的時候msg_id索引的順序是在to_id前面的，因此MySQL查詢一定會優(yōu)先用msg_id索引，在使用了msg_id索引后，就已經(jīng)檢索出了34w行，并且由于msg_id的查詢條件是大于等于，因此，再這個查詢條件后，就不能再用到to_id的索引。
然后再看key_len長度為16，結(jié)合 key為PRIMARY，那么可以分析得知，只有一個主鍵索引被用到。
最后看看 type 值，是range，那么就說明這個查詢要么是范圍查詢，要么就是多值匹配。
請注意，
from_id != xxx這樣的語句，是無法用到索引的。只有from_id = xxx就可以用到所以，因此from id 的索引其實可以不用，建立索引的時候就要考慮清楚
如何優(yōu)化
既然知道索引不合理，那么就要分析并調(diào)整索引。一般而言，我們既然要從單表里面查詢，那么就需要能夠知道大體，單表里面大致會有哪些數(shù)據(jù)，現(xiàn)在的量級大概是多少。
然后開始下一步的分析，既然msgid是被設(shè)置為了主鍵，那一定是全局唯一的，所有，有多少數(shù)據(jù)量就至少會有多少條msgid；那么檢索msg_id基本就是檢索整個表了。
我們要做的優(yōu)化就是要盡量減少索引，減少查詢的行數(shù)；那么就需要思考，通過查詢哪些字段才能夠減少行數(shù)？比如，一個張表里面，所屬某個用戶的數(shù)據(jù)，會不會比查詢msgid的行數(shù)要少？查詢某個用戶并且是屬于某個圈子的，那會不會就更少了？等等。
然后根據(jù)實際情況分析，單表里面命中to_id 的行數(shù)應(yīng)該是會小于命中msg_id的，因此要首先保證能夠使用到to_id的索引
為此，可以設(shè)置主鍵的時候把msg_id和to_id的順序交互一下；但是，由于已經(jīng)是線上的表，已經(jīng)有了大量數(shù)據(jù)，并且業(yè)務(wù)開始運(yùn)行，這種情況下，修改主鍵會引發(fā)很多問題（當(dāng)然修改索引是OK的），因此，不建議直接修改主鍵。
那么，為了保證有效使用to_id的索引，就要新建一個聯(lián)合索引；那么新建的聯(lián)合索引的第一索引字段必然是to_id
針對此業(yè)務(wù)場景，最好能夠再加上circle_id索引，這樣可以快速索引；這樣就得到了新的聯(lián)合索引(to_id,circle_id)的索引，然后，因為要找msg_id，為此，在此基礎(chǔ)上，再加上msg_id。最終得到的聯(lián)合索引為(to_id,circle_id,msg_id)；這樣的話，就能夠快速檢索這樣的查詢語句了：where to_id = xxx and circle_id = xxx and msgId >= xxx
當(dāng)然，索引的建立，也不是說某個sql 語句需要啥索引，就建立某個聯(lián)合索引，這樣的話，索引太多的話，寫的性能受影響（插入、刪除、修改），然后存儲空間也會相應(yīng)增大；另外mysql在運(yùn)行時也會消耗資源維護(hù)索引，所以，索引并不是越多越好，需要結(jié)合查詢最頻繁、最影響性能的sql來建立合適的索引。需要再說明的是，一個聯(lián)合索引或者一組主鍵就是一個btree，多個索引就是多個btree
總結(jié)
首先我們需要深入理解索引的原理和實現(xiàn)，當(dāng)理解了原理后，才能夠更有助于我們建立合適的索引。然后我們建立索引的時候，不要想當(dāng)然，要先想清楚業(yè)務(wù)邏輯，再建立對應(yīng)的表結(jié)構(gòu)和索引。
需要再次強(qiáng)調(diào)如下幾點(diǎn):
索引不是越多越好
區(qū)分主鍵和索引
理解索引結(jié)構(gòu)原理
理解查詢索引規(guī)則