MySQL索引失效

哪些情況需要創(chuàng)建索引

主鍵自動建立唯一索引

頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引

多表關(guān)聯(lián)查詢中，關(guān)聯(lián)字段應(yīng)該創(chuàng)建索引 on 兩邊都要創(chuàng)建索引

查詢中排序的字段，應(yīng)該創(chuàng)建索引

頻繁查找字段覆蓋索引

查詢中統(tǒng)計或者分組字段卧檐，應(yīng)該創(chuàng)建索引 group by

哪些情況不需要創(chuàng)建索引

表記錄太少

經(jīng)常進(jìn)行增刪改操作的表

頻繁更新的字段

where條件里使用頻率不高的字段

為什么使用組合索引

MySQL 創(chuàng)建組合索引的規(guī)則是首先會對組合索引的最左邊的，也就是第一個name字段的數(shù)據(jù)進(jìn)行排序雷则，在第一個字段的排序基礎(chǔ)上死嗦，然后再對后面第二個的cid字段進(jìn)行排序。其實(shí)就相當(dāng)于實(shí)現(xiàn)了類似 order by name cid 這樣一種排序規(guī)則牡辽。
為了節(jié)省mysql索引存儲空間以及提升搜索性能喳篇，可建立組合索引（能使用組合索引就不使用單列索引）

查看執(zhí)行計劃

介紹

MySQL 提供了一個 EXPLAIN 命令, 它可以對SELECT 語句的執(zhí)行計劃進(jìn)行分析，并輸出 SELECT 執(zhí)行的詳細(xì)信息态辛，以供開發(fā)人員針對性優(yōu)化麸澜。
使用explain這個命令來查看一個這些SQL語句的執(zhí)行計劃，查看該SQL語句有沒有使用上了索引奏黑，有沒有做全表掃描炊邦，這都可以通過explain命令來查看。
可以通過explain命令深入了解MySQL的基于開銷的優(yōu)化器熟史，還可以獲得很多可能被優(yōu)化器考慮到的訪問策略的細(xì)節(jié)馁害，以及當(dāng)運(yùn)行SQL語句時哪種策略預(yù)計會被優(yōu)化器采用。
EXPLAIN 命令用法十分簡單蹂匹，在 SELECT 語句前加上explain就可以了碘菜，例如：

mysql> explain select * from t_user;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t_user | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

參數(shù)說明

expain出來的信息有10列，分別是：

id、select_type炉媒、table、type昆烁、possible_keys吊骤、key、key_len静尼、ref白粉、rows、Extras

案例表

-- 用戶表
create table t_user(
    id int primary key,
    login_name varchar(100),
    name varchar(100),
    age int,
    sex char(1),
    dep int,
    address varchar(100)
);
-- 部門表
create table t_dep(
    id int primary key,
    name varchar(100)
);
-- 地址表
create table t_addr(
    id int primary key,
    addr varchar(100)
);
-- 創(chuàng)建普通索引
alter table t_user add index idx_dep(dep);
alter table t_dep add index idx_name(name);
-- 創(chuàng)建唯一索引
alter table t_user add unique index idx_login_name(login_name);
-- 創(chuàng)建組合索引
alter table t_user add index idx_name_age_sex(name, age, sex);
-- 創(chuàng)建全文索引
alter table t_addr add fulltext ft_addr(addr);

id

每個 SELECT 語句都會自動分配的一個唯一標(biāo)識符鼠渺。
表示查詢中操作表的順序鸭巴，有三種情況：
- id相同：執(zhí)行順序由上到下。
- id不同：如果是子查詢拦盹，id號會自增鹃祖，id值越大優(yōu)先級越高，越先被執(zhí)行普舆。
- id如果相同恬口，可以認(rèn)為是一組，從上往下順序執(zhí)行沼侣；在所有組中祖能，id值越大，優(yōu)先級越高蛾洛，越先執(zhí)行养铸。
id列為null的就表示這是一個結(jié)果集，不需要使用它來進(jìn)行查詢轧膘。

select_type

查詢類型钞螟，主要用于區(qū)別普通查詢、聯(lián)合查詢(union扶供、union all)筛圆、子查詢等復(fù)雜查詢。

SIMPLE

表示不需要union操作或者不包含子查詢的簡單select查詢椿浓。有連接查詢時太援，外層的查詢?yōu)閟imple，且只有一個

mysql> explain select * from t_user;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t_user | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

PRIMARY

一個需要union操作或者含有子查詢的select扳碍，位于最外層的的select_type即為primary提岔。且只有一個

mysql> explain select (select name from t_user) from t_user;
+----+-------------+--------+-------+---------------+------------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key              | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+------------------+---------+------+------+-------------+
|  1 | PRIMARY     | t_user | index | NULL          | idx_dep          | 5       | NULL |    1 | Using index |
|  2 | SUBQUERY    | t_user | index | NULL          | idx_name_age_sex | 312     | NULL |    1 | Using index |
+----+-------------+--------+-------+---------------+------------------+---------+------+------+-------------+
2 rows in set (0.00 sec)

SUBQUERY

除了 from 子句中包含的子查詢外，其他地方出現(xiàn)的子查詢都可能是 subquery

mysql> explain select * from t_user where id = (select max(id) from t_user);
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
|  2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No matching min/max row                             |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
2 rows in set (0.01 sec)

DEPENDENT SUBQUERY

與dependent union類似笋敞，表示這個subquery的查詢要受到外部表查詢的影響

mysql> explain select id, name, (select name from t_dep a where a.id = b.dep) from t_user b;
+----+--------------------+-------+--------+---------------+---------+---------+-------------------+------+-------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref               | rows | Extra |
+----+--------------------+-------+--------+---------------+---------+---------+-------------------+------+-------+
|  1 | PRIMARY            | b     | ALL    | NULL          | NULL    | NULL    | NULL              |    1 | NULL  |
|  2 | DEPENDENT SUBQUERY | a     | eq_ref | PRIMARY       | PRIMARY | 4       | review_test.b.dep |    1 | NULL  |
+----+--------------------+-------+--------+---------------+---------+---------+-------------------+------+-------+
2 rows in set (0.00 sec)

UNION

union連接的兩個select查詢碱蒙，第一個查詢是PRIMARY，除了第一個表外，第二個以后的表select_type都是union

mysql> explain select * from t_user where sex='1' union select * from t_user where sex='2';
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | t_user     | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where     |
|  2 | UNION        | t_user     | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where     |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
3 rows in set (0.00 sec)

DEPENDENT UNION

與union一樣赛惩，出現(xiàn)在union 或union all語句中哀墓，但是這個查詢要受到外部查詢的影響

mysql> explain select * from t_user where sex in (select sex from t_user where sex='1' union select sex from t_user where sex='2');
+----+--------------------+------------+-------+---------------+------------------+---------+------+------+--------------------------+
| id | select_type        | table      | type  | possible_keys | key              | key_len | ref  | rows | Extra                    |
+----+--------------------+------------+-------+---------------+------------------+---------+------+------+--------------------------+
|  1 | PRIMARY            | t_user     | ALL   | NULL          | NULL             | NULL    | NULL |    1 | Using where              |
|  2 | DEPENDENT SUBQUERY | t_user     | index | NULL          | idx_name_age_sex | 312     | NULL |    1 | Using where; Using index |
|  3 | DEPENDENT UNION    | t_user     | index | NULL          | idx_name_age_sex | 312     | NULL |    1 | Using where; Using index |
| NULL | UNION RESULT       | <union2,3> | ALL   | NULL          | NULL             | NULL    | NULL | NULL | Using temporary          |
+----+--------------------+------------+-------+---------------+------------------+---------+------+------+--------------------------+
4 rows in set (0.00 sec)

UNION RESULT

如上所示，包含union的結(jié)果集喷兼，在union和union all語句中篮绰，因?yàn)樗恍枰獏⑴c查詢，所以id字段為null

DERIVED

from字句中出現(xiàn)的子查詢季惯，也叫做派生表吠各，其他數(shù)據(jù)庫中可能叫做內(nèi)聯(lián)視圖或嵌套select

mysql> explain select * from (select * from t_user where sex='1') b;
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL        |
|  2 | DERIVED     | t_user     | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

table

顯示的查詢表名，如果查詢使用了別名勉抓，那么這里顯示的是別名
如果不涉及對數(shù)據(jù)表的操作贾漏，那么這顯示為null
如果顯示為尖括號括起來的就表示這個是臨時表，后邊的N就是執(zhí)行計劃中的id藕筋，表示結(jié)果來自于這個查詢產(chǎn)生纵散。
如果是尖括號括起來的<union M,N>，與之類似念逞，也是一個臨時表困食，表示這個結(jié)果來自于union查詢的id為M, N的結(jié)果集。

type

依次從好到差：

system翎承，const硕盹，eq_ref，ref叨咖，fulltext瘩例，ref_or_null，unique_subquery甸各，index_subquery垛贤，range， index_merge趣倾，index聘惦，ALL

除了all之外，其他的type都可以使用到索引儒恋，除了index_merge之外善绎，其他的type只可以用到一個索引
注意事項(xiàng)：最少要索引使用到range級別。

system

表中只有一行數(shù)據(jù)或者是空表（這里的“表”指的是子查詢的“表”诫尽，而不是t_user表）

mysql> explain select * from (select * from t_user where id=1) a;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |
|  2 | DERIVED     | t_user     | const  | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
2 rows in set (0.00 sec)

const（重要）

使用唯一索引或者主鍵禀酱，返回記錄一定是1行記錄的等值where條件時，通常type是const牧嫉。其他數(shù)據(jù)庫也叫做唯一索引掃描

-- 主鍵
mysql> explain select * from t_user where id=1;
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t_user | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
-- 唯一索引
mysql> explain select * from t_user where login_name = 'Rose';
+----+-------------+--------+-------+----------------+----------------+---------+-------+------+-------+
| id | select_type | table  | type  | possible_keys  | key            | key_len | ref   | rows | Extra |
+----+-------------+--------+-------+----------------+----------------+---------+-------+------+-------+
|  1 | SIMPLE      | t_user | const | idx_login_name | idx_login_name | 303     | const |    1 | NULL  |
+----+-------------+--------+-------+----------------+----------------+---------+-------+------+-------+
1 row in set (0.00 sec)

eq_ref（重要）

關(guān)鍵字：連接字段主鍵或者唯一性索引剂跟。
此類型通常出現(xiàn)在多表的 join 查詢减途，表示對于前表的每一個結(jié)果，都只能匹配到后表的一行結(jié)果曹洽，并且查詢的比較操作通常是 '='鳍置，查詢效率較高。

mysql> explain select a.id from t_user a left join t_dep b on a.dep=b.id;
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | a     | index  | NULL          | idx_dep | 5       | NULL              |    5 | Using index |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | review_test.a.dep |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
2 rows in set (0.00 sec)

ref（重要）

針對非唯一索引送淆，使用等值（=）查詢非主鍵墓捻。或者是使用了最左前綴規(guī)則索引 [參看：MySQL索引最左前綴原則）]的查詢坊夫。

-- 非唯一索引
mysql> explain select * from t_user where dep=1;
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t_user | ref  | idx_dep       | idx_dep | 5       | const |    1 | NULL  |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
-- 等值非主鍵連接 
mysql> explain select a.id from t_user a left join t_dep b on a.name=b.name;
+----+-------------+-------+-------+---------------+------------------+---------+--------------------+------+-------------+
| id | select_type | table | type  | possible_keys | key              | key_len | ref                | rows | Extra       |
+----+-------------+-------+-------+---------------+------------------+---------+--------------------+------+-------------+
|  1 | SIMPLE      | a     | index | NULL          | idx_name_age_sex | 312     | NULL               |    5 | Using index |
|  1 | SIMPLE      | b     | ref   | idx_name      | idx_name         | 303     | review_test.a.name |    1 | Using index |
+----+-------------+-------+-------+---------------+------------------+---------+--------------------+------+-------------+
2 rows in set (0.00 sec)
-- 最左前綴
mysql> explain select * from t_user where name = 'zhaoyun';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t_user | ref  | idx_name_age_sex | idx_name_age_sex | 303     | const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

fulltext

全文索引檢索，要注意撤卢，全文索引的優(yōu)先級很高环凿，若全文索引和普通索引同時存在時，mysql不管代價放吩，優(yōu)先選擇使用全文索引

 mysql> explain select * from t_addr where match(addr) against('bei');
+----+-------------+--------+----------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type     | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+----------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t_addr | fulltext | ft_addr       | ft_addr | 0       | NULL |    1 | Using where |
+----+-------------+--------+----------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

ref_or_null

與ref方法類似智听，只是增加了null值的比較。實(shí)際用的不多渡紫。

unique_subquery

用于where中的in形式子查詢到推，子查詢返回不重復(fù)值唯一值

index_subquery

用于in形式子查詢使用到了輔助索引或者in常數(shù)列表，子查詢可能返回重復(fù)值惕澎，可以使用索引將子查詢?nèi)ブ亍?/p>

range（重要）

索引范圍掃描莉测，常見于使用>, <, is null, between, in, like等運(yùn)算符的查詢中。

-- 范圍查詢
mysql> explain select id from t_user where id>1;
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t_user | range | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.01 sec)
-- like 前綴索引
mysql> explain select * from t_user where name like 'z%';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t_user | range | idx_name_age_sex | idx_name_age_sex | 303     | NULL |    1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

-- 注：like '%z' 不使用索引

index_merge

表示查詢使用了兩個以上的索引唧喉，最后取交集或者并集捣卤，常見and、or的條件使用了不同的索引八孝，官方排序這個在ref_or_null之后董朝，但是實(shí)際上由于要讀取多個索引，性能可能大部分時間都不如range干跛。

index（重要）

** 關(guān)鍵字：條件是出現(xiàn)在索引樹中的節(jié)點(diǎn)的子姜。可能沒有完全匹配索引楼入。**
索引全表掃描哥捕，把索引從頭到尾掃一遍，常見于使用索引列就可以處理不需要讀取數(shù)據(jù)文件的查詢浅辙、可以使用索引排序或者分組的查詢扭弧。

-- 單索引
mysql> explain select login_name from t_user;
+----+-------------+--------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+----------------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user | index | NULL          | idx_login_name | 303     | NULL |    5 | Using index |
+----+-------------+--------+-------+---------------+----------------+---------+------+------+-------------+
-- 組合索引
mysql> explain select age from t_user;
+----+-------------+--------+-------+---------------+------------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key              | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user | index | NULL          | idx_name_age_sex | 312     | NULL |    5 | Using index |
+----+-------------+--------+-------+---------------+------------------+---------+------+------+-------------+

-- 思考：explain select login_name,age from t_user;
-- 覆蓋索引

ALL（重要）

這個就是全表掃描數(shù)據(jù)文件，然后再在server層進(jìn)行過濾返回符合要求的記錄记舆。

mysql> explain select * from t_user;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t_user | ALL  | NULL          | NULL | NULL    | NULL |    5 | NULL  |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
-- 回表查詢

possible_keys

此次查詢中可能選用的索引细溅，一個或多個

key

查詢真正使用到的索引，type為index_merge時季惩，這里可能出現(xiàn)兩個以上的索引，其他的type這里只會出現(xiàn)一個衣赶。

key_len

用于處理查詢的索引長度，如果是單列索引厚满，那就整個索引長度算進(jìn)去府瞄，如果是多列索引，那么查詢不一定都能使用到所有的列碘箍，具體使用到了多少個列的索引遵馆，這里就會計算進(jìn)去，沒有使用到的列丰榴，這里不會計算進(jìn)去货邓。
留意下這個列的值，算一下你的多列索引總長度就知道有沒有使用到所有的列了四濒。
另外换况，key_len只計算where條件用到的索引長度，而排序和分組就算用到了索引盗蟆，不會計算到key_len中戈二。

ref

如果是使用的常數(shù)等值查詢，這里會顯示const
如果是連接查詢喳资，被驅(qū)動表的執(zhí)行計劃這里會顯示驅(qū)動表的關(guān)聯(lián)字段
如果是條件使用了表達(dá)式或者函數(shù)觉吭，或者條件列發(fā)生了內(nèi)部隱式轉(zhuǎn)換，這里可能顯示為func

extra（重要）

這個列包含不適合在其他列中顯示但十分重要的額外的信息仆邓，這個列可以顯示的信息非常多亏栈，有幾十種，常用的有：distinct宏赘、no tables used绒北、using filesort（重要）、using index（重要）察署、using join buffer（block nested loop）闷游、using join buffer（batched key accss）、using sort_union贴汪、using_union脐往、using intersect、using sort_intersection扳埂、using temporary业簿、using where（重要）、firstmatch(tb_name)阳懂、loosescan(m..n)梅尤、filtered

distinct

在select部分使用了distinct關(guān)鍵字

no tables used

不帶from字句的查詢或者From dual查詢

mysql> explain select 1 from dual;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

使用not in()形式子查詢或not exists運(yùn)算符的連接查詢柜思，這種叫做反連接，即巷燥，一般連接查詢是先查詢內(nèi)表赡盘，再查詢外表，反連接就是先查詢外表缰揪，再查詢內(nèi)表陨享。

using filesort（重要）

排序時無法使用到索引時，就會出現(xiàn)這個钝腺。常見于order by和group by語句中抛姑。
說明MySQL會使用一個外部的索引排序，而不是按照索引順序進(jìn)行讀取艳狐。
MySQL中無法利用索引完成的排序操作稱為“文件排序”途戒。

mysql> explain select * from t_user order by address;
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | t_user | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+

using index（重要）

查詢時不需要回表查詢，直接通過索引就可以獲取查詢的數(shù)據(jù)僵驰。

表示相應(yīng)的SELECT查詢中使用到了覆蓋索引（Covering Index），避免訪問表的數(shù)據(jù)行唁毒，效率不錯蒜茴！
如果同時出現(xiàn)Using Where ，說明索引被用來執(zhí)行查找索引鍵值浆西。
如果沒有同時出現(xiàn)Using Where 粉私，表明索引用來讀取數(shù)據(jù)而非執(zhí)行查找動作。

-- 全值匹配近零，覆蓋索引
mysql> explain select name, age, sex from t_user ;
+----+-------------+--------+-------+---------------+------------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key              | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user | index | NULL          | idx_name_age_sex | 312     | NULL |    5 | Using index |
+----+-------------+--------+-------+---------------+------------------+---------+------+------+-------------+
-- 對比select * from ...
mysql> explain select * from t_user ;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t_user | ALL  | NULL          | NULL | NULL    | NULL |    5 | NULL  |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
-- 索引用來讀取數(shù)據(jù)而非執(zhí)行查找動作
mysql> explain select id from t_user where id>1;
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t_user | range | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+

using join buffer（block nested loop）诺核，using join buffer（batched key accss）

5.6.x之后的版本優(yōu)化關(guān)聯(lián)查詢的BNL、BKA特性久信。主要是減少內(nèi)表的循環(huán)數(shù)量以及比較順序的掃描查詢窖杀。

mysql> explain select a.id, a.name from t_user a, t_dep b where a.dep=b.id;
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | b     | index | PRIMARY       | idx_name | 303     | NULL |    2 | Using index                                        |
|  1 | SIMPLE      | a     | ALL   | NULL          | NULL     | NULL    | NULL |    5 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------------------------------------------------+
-- 對應(yīng)的表結(jié)構(gòu)
-- t_user
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL,
  `login_name` varchar(100) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `dep` int(11) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_login_name` (`login_name`),
  KEY `idx_name_age_sex` (`name`,`age`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- t_dep
CREATE TABLE `t_dep` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

using sort_union，using_union裙士，using intersect入客，using sort_intersection

using intersect：表示使用and的各個索引的條件時，該信息表示是從處理結(jié)果獲取交集腿椎。
using union：表示使用or連接各個使用索引的條件時桌硫，該信息表示從處理結(jié)果獲取并集。
using sort_union和using sort_intersection：與前面兩個對應(yīng)的類似啃炸，只是他們是出現(xiàn)在用and和or查詢信息量大時铆隘，先查詢主鍵，然后進(jìn)行排序合并后南用，才能讀取記錄并返回膀钠。

using temporary

表示使用了臨時表存儲中間結(jié)果掏湾。
MySQL在對查詢結(jié)果order by和group by時使用臨時表。
臨時表可以是內(nèi)存臨時表和磁盤臨時表托修，執(zhí)行計劃中看不出來忘巧，需要查看status變量，used_tmp_table睦刃，used_tmp_disk_table才能看出來砚嘴。

mysql> explain select distinct a.id from t_user a, t_dep b where a.dep=b.id;
+----+-------------+-------+-------+-----------------------------------------+----------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys                           | key      | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+-------+-----------------------------------------+----------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | b     | index | PRIMARY                                 | idx_name | 303     | NULL |    2 | Using index; Using temporary                       |
|  1 | SIMPLE      | a     | ALL   | PRIMARY,idx_login_name,idx_name_age_sex | NULL     | NULL    | NULL |    5 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+-----------------------------------------+----------+---------+------+------+----------------------------------------------------+

using where（重要）

表示存儲引擎返回的記錄并不是所有的都滿足查詢條件，需要在server層進(jìn)行過濾涩拙。

-- 查詢條件無索引
mysql> explain select * from t_user where address='USA';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
-- 索引失效
mysql> explain select * from t_user where age=1;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
mysql> explain select * from t_user where id in(1, 2);
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

查詢條件中分為限制條件和檢查條件际长，5.6之前，存儲引擎只能根據(jù)限制條件掃描數(shù)據(jù)并返回兴泥，然后server層根據(jù)檢查條件進(jìn)行過濾再返回真正符合查詢的數(shù)據(jù)工育。5.6.x之后支持ICP特性，可以把檢查條件也下推到存儲引擎層搓彻，不符合檢查條件和限制條件的數(shù)據(jù)如绸，直接不讀取，這樣就大大減少了存儲引擎掃描的記錄數(shù)量旭贬。extra列顯示using index condition

mysql> explain select * from t_user where name='Rose';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t_user | ref  | idx_name_age_sex | idx_name_age_sex | 303     | const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+

firstmatch(tb_name)

5.6.x開始引入的優(yōu)化子查詢的新特性之一怔接，常見于where字句含有in()類型的子查詢。如果內(nèi)表的數(shù)據(jù)量比較大稀轨，就可能出現(xiàn)這個扼脐。

loosescan(m..n)

5.6.x之后引入的優(yōu)化子查詢的新特性之一，在in()類型的子查詢中奋刽，子查詢返回的可能有重復(fù)記錄時瓦侮，就可能出現(xiàn)這個。
除了這些之外佣谐，還有很多查詢數(shù)據(jù)字典庫肚吏，執(zhí)行計劃過程中就發(fā)現(xiàn)不可能存在結(jié)果的一些提示信息。

filtered

使用explain extended時會出現(xiàn)這個列狭魂，5.7之后的版本默認(rèn)就有這個字段须喂，不需要使用explain extended了。這個字段表示存儲引擎返回的數(shù)據(jù)在server層過濾后趁蕊，剩下多少滿足查詢的記錄數(shù)量的比例坞生，注意是百分比，不是具體記錄數(shù)掷伙。

索引失效分析

1是己、全值匹配我最愛
2、最佳左前綴法則
3任柜、不在索引列上做任何操作（計算卒废、函數(shù)沛厨、自動or手動類型轉(zhuǎn)換），會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描
4摔认、存儲引擎不能使用索引中范圍條件右邊的列
5逆皮、盡量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一致）），減少select *
6参袱、MySQL在使用不等于（电谣！=或者<>)的時候無法使用索引會導(dǎo)致全表掃描
7、is null, is not null也無法使用索引
8抹蚀、like以通配符開頭（'%abc')mysql索引失效剿牺，變成全表掃描的操作（注：以通配符結(jié)尾('abc%')不會失效）
9、字符串不加引號索引失效
10环壤、少用or晒来，用它來連接時會索引失效

1. 全值匹配我最愛

-- 條件與索引一一對應(yīng)
mysql> explain select * from t_user where name='Rose' and age=20 and sex='f';
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | t_user | ref  | idx_name_age_sex | idx_name_age_sex | 312     | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+

2. 最佳左前綴法則

組要針對的是組合索引而言，如果索引了多個列郑现，要遵守最佳左前綴法則湃崩，指的是查詢從索引的最左前列開始并且不跳過索引中的列。
1接箫、where條件列的攒读，從索引的最左前列開始，且不跳過索引中的列
2列牺、違背原則：未以最左前列開始，索引失效
3拗窃、違背原則：跳過索引中間列（age）瞎领，只引用了部分索引
結(jié)論： where條件要滿足最佳左前綴法則。口訣：帶頭大哥不能死随夸，中間兄弟不能斷

-- 反例：帶頭大哥不死（where條件中沒有組合索引的帶頭大哥name）
mysql> explain select * from t_user where age=23;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
-- 中間索引斷（where條件中沒有age九默，帶頭索引生效，其他索引失效）
mysql> explain select * from t_user where name='aa' and sex='1';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t_user | ref  | idx_name_age_sex | idx_name_age_sex | 303     | const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
-- 比較
mysql> explain select * from t_user where name='aa' and sex='f' and age=23;
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | t_user | ref  | idx_name_age_sex | idx_name_age_sex | 312     | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
-- 對比
mysql> explain select * from t_user where name='aa' and age=23 and sex='1';
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | t_user | ref  | idx_name_age_sex | idx_name_age_sex | 312     | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+

3. 不要在索引上做計算

不要進(jìn)行這些操作：計算宾毒、函數(shù)驼修、自動/手動類型轉(zhuǎn)換，不然會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描诈铛，即使?jié)M足最左前綴原則乙各，但where條件中使用了函數(shù)后，索引失效幢竹。

mysql> explain select * from t_user where login_name='Bob';
+----+-------------+--------+-------+----------------+----------------+---------+-------+------+-------+
| id | select_type | table  | type  | possible_keys  | key            | key_len | ref   | rows | Extra |
+----+-------------+--------+-------+----------------+----------------+---------+-------+------+-------+
|  1 | SIMPLE      | t_user | const | idx_login_name | idx_login_name | 303     | const |    1 | NULL  |
+----+-------------+--------+-------+----------------+----------------+---------+-------+------+-------+

mysql> explain select * from t_user where left(login_name, 1)='B';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

4. 范圍條件右邊的列失效

不能繼續(xù)使用索引中范圍條件（bettween耳峦、<、>焕毫、in等）右邊的列

mysql> explain select * from t_user where name='Jack' and age>20 and sex='f';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t_user | range | idx_name_age_sex | idx_name_age_sex | 308     | NULL |    1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
-- 對比
mysql> explain select name from t_user where name='Jack' and age>20 and sex='f';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+--------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                    |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t_user | ref  | idx_name_age_sex | idx_name_age_sex | 303     | const |    1 | Using where; Using index |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+--------------------------+

5. 盡量使用覆蓋索引

盡量使用覆蓋索引（只查詢索引的列）蹲坷，也就是索引列和查詢列一致驶乾，減少select *，按需要查字段循签，從索引中檢索數(shù)據(jù)级乐。

mysql> explain select * from t_user ;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t_user | ALL  | NULL          | NULL | NULL    | NULL |    5 | NULL  |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+

mysql> explain select name, login_name from t_user ;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t_user | ALL  | NULL          | NULL | NULL    | NULL |    5 | NULL  |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+

-- 覆蓋索引
mysql> explain select name, age, sex from t_user;
+----+-------------+--------+-------+---------------+------------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key              | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user | index | NULL          | idx_name_age_sex | 312     | NULL |    5 | Using index |
+----+-------------+--------+-------+---------------+------------------+---------+------+------+-------------+
mysql> explain select login_name from t_user;
+----+-------------+--------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+----------------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user | index | NULL          | idx_login_name | 303     | NULL |    5 | Using index |
+----+-------------+--------+-------+---------------+----------------+---------+------+------+-------------+

6. 索引字段上不要使用不等

索引字段上使用（！= 或者 < >）判斷時县匠，會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描

7. 主鍵索引字段上不可以判斷null

主鍵字段上不可以使用 null
索引字段上使用 is null / is not null 判斷時风科，可使用索引
對于已經(jīng)有 NOT NULL 修飾的字段來說，不要再使用 IS NULL 或者 IS NOT NULL 來作為查詢條件聚唐，沒有意義丐重。

mysql> explain select * from t_user where name is null;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t_user | ref  | idx_name_age_sex | idx_name_age_sex | 303     | const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+

8. 索引字段使用like不以通配符開頭

索引字段使用like以通配符開頭（‘%字符串’）時，會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描

mysql> explain select * from t_user where name like 'a%';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t_user | range | idx_name_age_sex | idx_name_age_sex | 303     | NULL |    1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+

mysql> explain select * from t_user where name like '%a';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

由結(jié)果可知杆查，like以通配符結(jié)束相當(dāng)于范圍查找扮惦，索引不會失效。與范圍條件（bettween亲桦、<崖蜜、>、in等）不同的是：不會導(dǎo)致右邊的索引失效客峭。
問題：解決like ‘%字符串%’時豫领，索引失效問題的方法？使用覆蓋索引可以解決舔琅。

mysql> explain select name, age, sex from t_user where name like '%a%';
+----+-------------+--------+-------+---------------+------------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key              | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t_user | index | NULL          | idx_name_age_sex | 312     | NULL |    6 | Using where; Using index |
+----+-------------+--------+-------+---------------+------------------+---------+------+------+--------------------------+

9. 索引字段字符串要加單引號

索引字段是字符串等恐，但查詢時不加單引號，會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描

mysql> explain select * from t_user where name=123;
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys    | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user | ALL  | idx_name_age_sex | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+

mysql> explain select * from t_user where name='123';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t_user | ref  | idx_name_age_sex | idx_name_age_sex | 303     | const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+

10. 索引字段不要使用or

索引字段使用 or 時备蚓，會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描

mysql> explain select * from t_user where name='asd' or age=23;
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys    | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user | ALL  | idx_name_age_sex | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+

mysql> explain select * from t_user where name='asd' and age=23;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | t_user | ref  | idx_name_age_sex | idx_name_age_sex | 308     | const,const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+

總結(jié)

組合索引索引失效總結(jié)

索引失效口訣

最后編輯于：2020.11.29 20:56:27

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末课蔬，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子郊尝，更是在濱河造成了極大的恐慌二跋，老刑警劉巖，帶你破解...
沈念sama閱讀 211,123評論 6贊 490
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件流昏，死亡現(xiàn)場離奇詭異扎即，居然都是意外死亡，警方通過查閱死者的電腦和手機(jī)况凉，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 90,031評論 2贊 384
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門谚鄙，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人刁绒，你說我怎么就攤上這事襟锐。” “怎么了膛锭？”我有些...
開封第一講書人閱讀 156,723評論 0贊 345
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵粮坞，是天一觀的道長蚊荣。經(jīng)常有香客問我，道長莫杈，這世上最難降的妖魔是什么互例？我笑而不...
開封第一講書人閱讀 56,357評論 1贊 283
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮筝闹，結(jié)果婚禮上媳叨，老公的妹妹穿的比我還像新娘。我一直安慰自己关顷，他們只是感情好糊秆，可當(dāng)我...
茶點(diǎn)故事閱讀 65,412評論 5贊 384
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布。她就那樣靜靜地躺著议双，像睡著了一般痘番。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上平痰，一...
開封第一講書人閱讀 49,760評論 1贊 289
城市分裂傳說
那天汞舱，我揣著相機(jī)與錄音，去河邊找鬼宗雇。笑死昂芜，一個胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的赔蒲。我是一名探鬼主播泌神，決...
沈念sama閱讀 38,904評論 3贊 405
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼舞虱！你這毒婦竟也來了欢际？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 37,672評論 0贊 266
萬榮殺人案實(shí)錄
序言：老撾萬榮一對情侶失蹤砾嫉，失蹤者是張志新（化名）和其女友劉穎幼苛，沒想到半個月后窒篱，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體焕刮，經(jīng)...
沈念sama閱讀 44,118評論 1贊 303
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 36,456評論 2贊 325
?白月光啟示錄
正文我和宋清朗相戀三年墙杯，在試婚紗的時候發(fā)現(xiàn)自己被綠了配并。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點(diǎn)故事閱讀 38,599評論 1贊 340
活死人
序言：一個原本活蹦亂跳的男人離奇死亡高镐，死狀恐怖溉旋，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情嫉髓，我是刑警寧澤观腊，帶...
沈念sama閱讀 34,264評論 4贊 328
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布邑闲，位于F島的核電站，受9級特大地震影響梧油，放射性物質(zhì)發(fā)生泄漏苫耸。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 39,857評論 3贊 312
男人毒藥：我在死后第九天來索命
文/蒙蒙一儡陨、第九天我趴在偏房一處隱蔽的房頂上張望褪子。院中可真熱鬧，春花似錦骗村、人聲如沸嫌褪。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,731評論 0贊 21
一樁弒父案胚股，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽笼痛。三九已至，卻和暖如春信轿，著一層夾襖步出監(jiān)牢的瞬間晃痴，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 31,956評論 1贊 264
情欲美人皮
我被黑心中介騙來泰國打工财忽，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留倘核，地道東北人。一個月前我還...
沈念sama閱讀 46,286評論 2贊 360
代替公主和親
正文我出身青樓即彪，卻偏偏與公主長得像紧唱，于是被迫代替她去往敵國和親。傳聞我的和親對象是個殘疾皇子隶校，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 43,465評論 2贊 348