做業務開發的同學都知道，有些SQL看起來沒啥問題，索引也用到了，但就是很慢.......
今天我們來通過幾個常見的例子來深入探究下SQL慢的原因
案例一
假設你現在維護了一個帖子表，包含帖子id（tweet_id）、用戶id（user_id）、時間（gmt_create）等字段。為了便于描述，我們先忽略其他字段。這個表的建表語句如下：
MySQL> CREATE TABLE `t_tweet` (
`id` int(11) NOT NULL,
`tweet_id` varchar(32) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`gmt_create` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `tweet_id` (`tweet_id`),
KEY `gmt_create` (`gmt_create`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
復制代碼
假設，現在已經記錄了從2016年初到2018年底的所有數據，有一個需求是，要統計發 生在所有年份中7月份的帖子記錄總數。這個邏輯看上去并不復雜，你的SQL語句可能會這么 寫：
select count(*) from t_tweet where month(gmt_create)=7;
復制代碼
由于gmt_create字段上有索引，于是你就很放心地在生產庫中執行了這條語句，但卻發現執行了 特別久，才返回了結果。
如果你問DBA同事為什么會出現這樣的情況，他大概會告訴你：如果對字段做了函數計算，就 用不上索引了，這是MySQL的規定。
如果你已經學過了InnoDB的索引結構了，可以再追問一句，為什么？
為什么條件是where gmt_create='2018-7-1’的時候可以用上索引，而改成where month(gmt_create)=7的時候就不行了？
下面是這個gmt_create索引的示意圖。方框上面的數字就是month()函數對應的值。
如果你的SQL語句條件用的是where gmt_create='2018-7-1’的話，引擎就會按照上面綠色箭頭的 路線，快速定位到 gmt_create='2018-7-1’需要的結果。
實際上，B+樹提供的這個快速定位能力，來源于同一層兄弟節點的有序性。
但是，如果計算month()函數的話，你會看到傳入7的時候，在樹的第一層就不知道該怎么辦了。 也就是說，對索引字段做函數操作， 可能會破壞索引值的有序性， 因此優化器就決定放棄 走樹搜索功能。
需要注意的是，優化器并不是要放棄使用這個索引。
在這個例子里，放棄了樹搜索功能，優化器可以選擇遍歷主鍵索引，也可以選擇遍歷索引 gmt_create，優化器對比索引大小后發現，索引gmt_create更小，遍歷這個索引比遍歷主鍵索引 來得更快。因此最終還是會選擇索引gmt_create。 可以使用explain命令，查看一下這條SQL語句的執行結果
key="gmt_create"表示的是，使用了gmt_create這個索引；我在測試表數據中插入了10萬行數 據，rows=100335，說明這條語句掃描了整個索引的所有值；Extra字段的Using index，表示的是使用了覆蓋索引。
也就是說，由于在gmt_create字段加了month()函數操作，導致了全索引掃描。
到這里我給你說明了，由于加了month()函數操作，MySQL無法再使用索引快速定位功能，而只能使用全索引掃描。
優化器在某些問題上確實有“偷懶”行為，即使是對于不改變有序性的函數，也不會考慮使用索 引。
比如，對于select * from t_tweet where id + 1 = 10000這個SQL語句，這個加1操作并不會改變有序性，但是MySQL優化器還是不能用id索引快速定位到9999這一行。所以，需要你在寫 SQL語句的時候，手動改寫成 where id = 10000 -1才可以。
案例二
我們一起看一下這條SQL語句：
select * from t_tweet where tweet_id=110717;
復制代碼
交易編號tweet_id這個字段上，本來就有索引，但是explain的結果卻顯示，這條語句需要走全表 掃描。你可能也發現了，tweet_id的字段類型是varchar(32)，而輸入的參數卻是整型，所以需要做類型轉換。
那么，現在這里就有兩個問題：
- 數據類型轉換的規則是什么？
- 為什么有數據類型轉換，就需要走全索引掃描？
先來看第一個問題，你可能會說，數據庫里面類型這么多，這種數據類型轉換規則更多，我記不 住，應該怎么辦呢？ 這里有一個簡單的方法，看 select “10” > 9的結果：
- 如果規則是“將字符串轉成數字”，那么就是做數字比較，結果應該是1；
- 如果規則是“將數字轉成字符串”，那么就是做字符串比較，結果應該是0。
這時，你再看這個全表掃描的語句：
select * from t_tweet where tweet_id=110717;
復制代碼
就知道對于優化器來說，這個語句相當于：
select * from t_tweet where CAST(tweet_id AS tweetId int) = 110717;
復制代碼
也就是說，這條語句觸發了我們上面說到的規則：對索引字段做函數操作，優化器會放棄走樹搜 索功能。
案例三
假設系統里還有另外一個表tweet_detail，用于記錄帖子的詳情數據。
mysql> CREATE TABLE `t_tweet_detail` (
`id` int(11) NOT NULL,
`tweet_id` varchar(32) DEFAULT NULL,
`author_id` int(11) DEFAULT NULL, /* 帖子作者id*/
`tweet_info` varchar(32) DEFAULT NULL, /* 帖子內容 */
PRIMARY KEY (`id`),
KEY `tweet_id` (`tweet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
復制代碼
這時候，如果要查詢id=2的帖子的所有信息，SQL語句可以這么寫：
select d.* from t_tweet l, t_tweet_detail d where t1.tweet_id=t2.tweet_id and t2.id=2;
復制代碼
explain的結果里面第二行的key=NULL表示的就是，這個過程是通過遍歷主鍵索引的方式，一個一個地判斷tweet_id的值是否匹配。
表示沒有用上帖子詳情表t_tweet_detail上的tweet_id索引，進行了全表掃描。
進行到這里，你會發現第3步不符合我們的預期。因為表t_tweet_detail里tweet_id字段上是有索引 的，我們本來是希望通過使用tweet_id索引能夠快速定位到等值的行。但，這里并沒有。
如果你去問DBA同學，他們可能會告訴你，因為這兩個表的字符集不同，一個是utf8，一個是 utf8mb4，所以做表連接查詢的時候用不上關聯字段的索引。
這個回答，也是通常你搜索這個問 題時會得到的答案。 但是你應該再追問一下，為什么字符集不同就用不上索引呢？
結論是：字符集utf8mb4是utf8的超集，所以當這兩個類型的字 符串在做比較的時候，MySQL內部的操作是，先把utf8字符串轉成utf8mb4字符集，再做比較。
這個設定很好理解，utf8mb4是utf8的超集。類似地，在程序設計語言里面，做自動類型轉換 的時候，為了避免數據在轉換過程中由于截斷導致數據錯誤，也都是“按數據長度增加的方 向”進行轉換的。
因此， 在執行上面這個語句的時候，需要將被驅動數據表里的字段一個個地轉換成utf8mb4
這就再次觸發了我們上面說到的原則：對索引字段做函數操作，優化器會放棄走樹搜索功能。
優化：把t_tweet_detail表上的tweet_id字段的字符集也改成utf8mb4，這樣就 沒有字符集轉換的問題了。
原文鏈接：https://juejin.im/post/6860513755000733709
