mysql索引測試案例 實驗條件： mysql> show create table users\G; *************************** 1. row *************************** Table: users Create Table: CREATE TABLE `users` ( `id` int(11) NOT NULL, `name` varchar(45) DEFAULT NULL, `c1` text, `c2` int(6) unsigned zerofill DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c2_UNIQUE` (`c2`), KEY `idx_users` (`c1`(10)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified mysql> show create table user_action\G; *************************** 1. row *************************** Table: user_action Create Table: CREATE TABLE `user_action` ( `user_id` int(11) NOT NULL, `action` varchar(45) DEFAULT NULL, `name` varchar(100) DEFAULT NULL, PRIMARY KEY (`user_id`), UNIQUE KEY `idx_userid` (`user_id`), KEY `idx_action` (`action`), KEY `idx_useraction_action_name` (`action`,`name`(10)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified mysql> select * from users; +----+--------+------+--------+ | id | name | c1 | c2 | +----+--------+------+--------+ | 1 | libk | NULL | NULL | | 2 | zyfon | NULL | NULL | | 3 | daodao | NULL | NULL | | 4 | 3333 | NULL | 000002 | | 5 | 444 | NULL | NULL | +----+--------+------+--------+ 5 rows in set (0.00 sec) mysql> select * from user_action; +---------+--------+---------------+ | user_id | action | name | +---------+--------+---------------+ | 1 | jump | aaaaaaaaaaaaa | | 2 | run | bbbbbbbbbbb | | 4 | swim | cccccc | | 6 | kick | dd | | 15 | jump1 | fff | +---------+--------+---------------+ 5 rows in set (0.00 sec) mysql> 1. 復合索引 復合索引的使用原則是索引的前導列必須在條件裡出現，否則將不使用符合索引 mysql> explain select * from user_action where action='run'; +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+ | 1 | SIMPLE | user_action | ref | idx_action,idx_useraction_action_name | idx_action | 138 | const | 1 | Using where | +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from user_action where name='dd'; +----+-------------+-------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | user_action | ALL | NULL | NULL | NULL | NULL | 5 | Using where | +----+-------------+-------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) 從type=ALL可以知道，使用了全表掃描 mysql> explain select * from user_action where action='run' and name='dd'; +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+ | 1 | SIMPLE | user_action | ref | idx_action,idx_useraction_action_name | idx_action | 138 | const | 1 | Using where | +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql選擇執行路徑使用的是和oracle類似的基於cost，只不過mysql沒有oracle做的那麼強悍 2. 在索引列上不要用函數，否則會不使用索引 mysql> explain select * from user_action where abs(user_id)>0; +----+-------------+-------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | user_action | ALL | NULL | NULL | NULL | NULL | 5 | Using where | +----+-------------+-------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) 3. 覆蓋索引使用 mysql> explain select * from user_action where action='run'; +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+ | 1 | SIMPLE | user_action | ref | idx_action,idx_useraction_action_name | idx_action | 138 | const | 1 | Using where | +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+ 1 row in set (0.00 sec) 從上面的type=ref和“Using where”，可以看出，mysql先ref的方式找到索引，再通過索引回表找到數據 說明： ref：當通過鍵值讀數據時，鍵不是UNIQUE或PRIMARY KEY（換句話說，如果不能基於關鍵字選擇單個行的話），則使用ref Using where：提示mysql用where過濾結果集 mysql> explain select action from user_action where action='run'; +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | user_action | ref | idx_action,idx_useraction_action_name | idx_action | 138 | const | 1 | Using where; Using index | +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) 從上面可以看到“Using where; Using index”，說明是通過全索引掃描獲得數據並過濾結果集，不用回表取數據，使用了覆蓋索引掃描 mysql> explain select action,user_id from user_action where action='run'; +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | user_action | ref | idx_action,idx_useraction_action_name | idx_action | 138 | const | 1 | Using where; Using index | +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) 上面這個例子中，也使用了覆蓋索引掃描，但idx_action所以裡沒有user_id字段，這是什麼原因？因為idx_action是輔助索引，它要通過主鍵才能找到數據，而user_id是主鍵索引。所以也不用回表就可以得到所需數據。所以要想使用覆蓋索引，就不要用"select *" mysql> explain select action,a1 from user_action where action='run'; +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+ | 1 | SIMPLE | user_action | ref | idx_action,idx_useraction_action_name | idx_action | 138 | const | 1 | Using where | +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+ 1 row in set (0.00 sec) 上面添加了一個字段a1，然後把a1放到select列表裡，再看執行計劃，發現只有“Using where”,說明需要回表取數據了。 mysql> explain select action from user_action order by action; +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | user_action | index | NULL | idx_action | 138 | NULL | 5 | Using index | +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ 1 row in set (0.00 sec) 從上面可以看到type=index和“Using index”,說明只掃描了索引，使用的覆蓋索引掃描，沒有回表就把排序好的數據取出來 4. 數據排序（order by） mysql> explain select * from user_action order by action; +----+-------------+-------------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | user_action | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort | +----+-------------+-------------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec) 這個type=ALL和“Using filesort”可以知道使用了全表掃描，然後對結果集排序 mysql> explain select * from user_action where user_id>1 order by action; +----+-------------+-------------+-------+--------------------+---------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+--------------------+---------+---------+------+------+-----------------------------+ | 1 | SIMPLE | user_action | range | PRIMARY,idx_userid | PRIMARY | 4 | NULL | 2 | Using where; Using filesort | +----+-------------+-------------+-------+--------------------+---------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) 上一個sql排序因為沒有使用索引，所以需要排序（Using filesort）,而這個是使用了主鍵，為什麼還需要排序，因為需要的順序和主鍵的順序不一樣。 說明 mysql的排序有兩種：index和filesort，index效率高，它指MySQL掃描索引本身而完成排序。FileSort方式效率較低，因為沒有利用索引取到數據，需要mysql通過將取得的數據在內存中進行排序然後再將數據返回給客戶。 mysql中filesort 的實現算法實際上是有兩種的： 1) 在mysql4.1版本之前只有一種排序算法，首先根據相應的條件取出相應的排序字段和可以直接定位行數據的行指針信息，然後在sort buffer中進行排序，然後再回表取需要 的數據,這裡需要二次回表，速度會慢些 2) 從mysql4.1開始,改進了第一種排序算法，一次性取出滿足條件行的所有字段，然後在sort buffer中進行排序。目的是為了減少第一次算法中需要二次回表的IO操作，將兩 次變成了一次，但相應也會耗用更多的sort buffer空間。 mysql4.1開始以後所有版本同時支持兩種算法，mysql主要通過比較我們所設定的系統參數max_length_for_sort_data 的大小和“select語句所取出的字段類型大小總和”，來判定需要使用哪一種排序算法。如果max_length_for_sort_data 更大，則使用第二種優化後的算法，否則使用第一種算法。如果希望ORDER BY操作的效率盡可能的高，一定要注意max_length_for_sort_data 參數的設置。 mysql> explain select * from user_action where action>'run' order by action,user_id; +----+-------------+-------------+-------+---------------------------------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------------------------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | user_action | range | idx_action,idx_useraction_action_name | idx_action | 138 | NULL | 1 | Using where | +----+-------------+-------------+-------+---------------------------------------+------------+---------+------+------+-------------+ 1 row in set (0.00 sec) 這個sql使用了索引的排序，然後回表取數據得到結果集，沒有對查詢結果排序 mysql> explain select * from user_action where action>'run' order by action,a1; +----+-------------+-------------+-------+---------------------------------------+------------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------------------------------+------------+---------+------+------+-----------------------------+ | 1 | SIMPLE | user_action | range | idx_action,idx_useraction_action_name | idx_action | 138 | NULL | 1 | Using where; Using filesort | +----+-------------+-------------+-------+---------------------------------------+------------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) 這個sql也使用了索引“idx_action”，為什麼就需要對結果集排序呢？因為a1即不再索引idx_action裡，也不是主鍵；所以需要對結果集排序，要使用filesort，可以通過增大max_length_for_sort_data來優化filesort。或者就避免filesort mysql> explain select action,name from user_action order by action asc, name desc; +----+-------------+-------------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | user_action | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort | +----+-------------+-------------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec) 對索引列同時使用了ASC和DESC時，需要使用filesort mysql> explain select action,name from user_action where user_id=1 order by action asc, name desc; +----+-------------+-------------+-------+--------------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+--------------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | user_action | const | PRIMARY,idx_userid | PRIMARY | 4 | const | 1 | | +----+-------------+-------------+-------+--------------------+---------+---------+-------+------+-------+ 1 row in set (0.00 sec) 雖然也對索引列同時使用了ASC和DESC，但是通過where語句（主鍵）將order by中索引列轉為常量，則直接根據索引順序回表讀數據 mysql> explain select action,name from user_action where action='run' order by action asc, name desc; +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | user_action | ref | idx_action,idx_useraction_action_name | idx_action | 138 | const | 1 | Using where; Using filesort | +----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-----------------------------+ 1 row in set (0.00 sec) where語句與order by語句使用了不同的索引也是需要排序的，where使用idx_action，order by使用了idx_useraction_action_name 總上面使用filesort的情況： 1) 查詢的行數過多，優化器認為要全表掃描，且沒有使用覆蓋索引 2) 對索引列同時使用了ASC和DESC時，但是通過where語句（主鍵）將order by中索引列轉為常量例外 3) where語句與order by語句使用了不同的索引也是需要排序的 4) order by子句中加入了非索引列,且非索引列不在where子句中 5) ORDER BY語句中索引列使用了表達式 ------end------
