本文主要講關於SELECT語句的優化問題. 會涉及到一些關於表索引的知識.
性能
很簡單, 你每建立一個索引, 數據庫就會根據索引類型，幫你建立一個索引數據結構（B+樹非常常用）.
NOTE:
B+樹B+樹對範圍查詢和直接查詢都很在行. 直接查詢的時間複雜度均為O(log), 也就是用的二分法, 具體為什麼就去看
B+樹的數據結構, 在看B+樹之前最好先看B樹, 不然東西太多消化不了.
不過每次插入數據和刪除數據就需要重構索引樹的結構, 濫用索引反而會降低寫入效率.
設計
索引有好幾種, 最常用的有:
- 主鍵索引: 比如用戶表的ID字段
- 唯一索引: 比如不可重複的用戶名
- 組合索引: 需要經常被一起查詢的字段, 比如用戶名, 密碼
- 普通索引: 這個怎麼用就仁者見仁智者見智了.
索引一經創建不能修改，如果要修改索引，只能刪除重建. 具體怎麼創建就baidu吧。
如果要查看一張表的索引, 如下:
1mysql> SHOW INDEX FROM users;
2
3+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
5+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6| users | 0 | PRIMARY | 1 | id | A | 9847 | NULL | NULL | | BTREE | | |
7| users | 0 | id_UNIQUE | 1 | id | A | 9847 | NULL | NULL | | BTREE | | |
8| users | 0 | username | 1 | username | A | 9847 | NULL | NULL | | BTREE | | |
9+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10
113 rows in set (0.00 sec)
不重要的就不說了。
- Non_unique: 能不能重複，不能為0，可以為1.
- Seq_in_index: 組合索引的一個位置。等會就知道。
- Sub_part: 前置索引長度，不使用前置索引則為NULL。
- Cardinality: 基數，是否做為索引的最重要的因素，基數越大說明重複性越低，越有可能查詢時觸發索引。
- Index_type: 索引類型，常用的有
FULLTEXT(用與搜索技術，主要解決模糊查詢效率低下的問題)，和BTREE（就是樹形數據結構存儲），還有Hash（由於Hash的特點，只能判斷值相等，不能判斷值範圍）。
QUESION: 什麼是前置索引？
對於一些特殊字段比如
TEXT，或者VARCHAR(255)這種很大的字段，建立的索引樹會非常肥，這樣的索引非常慢，我們對與這種字段會採用它們的前幾個字符做為索引。選擇幾個字符的訣竅就是要保證要極高的非重複性，和儘量短的字符，來節省空間。NOTE: 關於基數
通常我們在選擇索引時會儘可能選擇基數大的列做為索引。 因為MySQL在執行查詢時，基數對比總數是是否觸發索引的一個判斷條件，這個值大概在30%左右。
調優
查看一次SELECT在MySQL使用了哪些策略，我們可以通過在SQL語句前加上EXPLAIN來做到。
1mysql> EXPLAIN SELECT id, username FROM users WHERE username = 'sdttttt';
2+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
3| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
5| 1 | SIMPLE | users | NULL | const | username | username | 50 | const | 1 | 100.00 | Using index |
6+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
71 row in set, 1 warning (0.00 sec)
- possible_keys: 可能觸發的索引。
- key: 本次SQL執行觸發的索引。
- filtered: 過濾了多少數據，單位：百分比。
- Extra: 執行策略。
下面說一下Extra，這個比較重要：
Using index
查詢的列被索引覆蓋，並且where篩選條件是索引的是前導列，Extra中為Using index。
Using where Using index
- 查詢的列被索引覆蓋，並且
where篩選條件是索引列之一, 但是不是索引的不是前導列，Extra中為Using where;Using index，意味著無法直接通過索引查找來查詢到符合條件的數據。 - 查詢的列被索引覆蓋，並且
where篩選條件是索引列前導列的一個範圍，同樣意味著無法直接通過索引查找查詢到符合條件的數據。
(查詢的列被索引覆蓋，這種情況是不會發生回表的，只是進行索引掃描罷了)
NULL
（既沒有Using index，也沒有Using where Using index，也沒有using where）
1，查詢的列未被索引覆蓋，並且where篩選條件是索引的前導列，意味著用到了索引，但是部分字段未被索引覆蓋，必須通過“回表”來實現，不是純粹地用到了索引，也不是完全沒用到索引，Extra中為NULL(沒有信息)。
Using where
1，查詢的列未被索引覆蓋，where篩選條件非索引的前導列，Extra中為Using where.
（using where 意味著通過索引或者表掃描的方式進程where條件的過濾，
反過來說，也就是沒有可用的索引查找，這裡的type都是all，說明MySQL認為全表掃描是一種比較低的代價。）
EXT: ICP(Index Condition Pushdown)
這個是
MySQL5.6新加入的特新，MySQL的設計分為兩層，服務層和存儲引擎。在沒有的ICP的時候，你對索引執行過濾：首先SE（Storage Engine）將一條條索引數據取出，並且一條條給服務層看，由服務層來對照where條件。
在使用ICP後，你對索引執行過濾：首先SE（Storage Engine）將一條條索引數據取出，並且對照下推的索引條件，如果滿足條件，就返回給服務層，服務層下推到沒有被SE執行的where條件。
你是不是覺得很繞？其實就是索引的where條件SE幫服務層做了，服務層不>用去管索引的Where條件了。
說實話我對這個功能還是挺迷的，服務層的執行速度是不如SE麼？為什麼ICP能提高速度？MySQL中的秘密還挺多。
All in All
在設計數據表的時候需要考慮這個表的讀寫情況，根據字段來適當的增加索引。
在編寫SQL語句時，儘可能使用索引。能調優就儘量調優。