MySQL索引篇

作者: MiniSoulBigBang | 来源:发表于2020-05-03 13:44 被阅读0次

1 索引介绍

1.1 索引是什么

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

1.2 索引的优势和劣势

1.2.1 索引的优势

1.2.1.1 检索

可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。

1.2.1.2 排序

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

1.2.1.2.1 where子句中

被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。
如果按照索引列的顺序进行排序，对于order by语句来说，效率就会提高很多。
where＋索引列，会把条件下推到存储引擎层处理从而加快数据的过滤，这就是索引下推（ICP），5.6以后的新特征。

1.2.1.2.2 select子句中

select ＋索引字段，会生成覆盖索引，减少检索次数。

1.2.2 索引的劣势

索引会占据磁盘空间。

索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

2 索引分类

单列索引、组合索引、全文索引、空间索引（根本看不到）、位图索引（oracle所特有的）

3 索引的使用

3.1 创建索引

单列索引之普通索引

create index index_name on table(column(length));

alter table table_name add index index_name(column(length));

单列索引之唯一索引

create unique index index_name on table(column(length));

alter table table_name add unique index index_name(column(length));

单列索引之全文索引

create fulltext index index_name on table(column(length))

alter table table_name add fulltext index index_name(column(length))

组合索引

alter table table_name add index index_name(column1(length1),column2(length2));

3.2 删除索引

drop index index_name on table_name;

alter table table_name drop index index_name;

3.3 查看索引

show index from table_name;

4 索引原理分析

4.1 索引存储结构

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。

MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换。

MEMORY/HEAP存储引擎：支持HASH和BTREE索引。

4.2 B树和B+树

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B树图示

B树是为了磁盘或其它存储设备而设计的一种多叉（下面你会看到，相对于二叉，B树每个内结点有多个分支，即多叉）平衡查找树。

005.png

B树的高度一般都是在2-4这个高度，树的高度直接影响IO读写的次数。

如果是三层树结构，支撑的数据可以达到20G；如果是四层树结构，支撑的数据可以达到几十T。

B+树图示

006.png

B树和B+树的区别

B树和B+树的最大区别在于非叶子节点是否存储数据的问题。

B树是非叶子节点和叶子节点都会存储数据。

B+树只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，也就是有顺序的。

4.3 聚集索引（InnoDB）

聚集索引也叫聚簇索引，指的是索引和数据在一个文件上。

主键索引

007.png

一棵主键索引树上的叶子节点挂载着所有的数据。

不建主键的情况下mysql找唯一的字段当主键，如果没有唯一字段，则自动生成伪列当主键。

不建主键会影响性能。

建表不建议使用uuid，可以使用雪花算法。

原因一：uuid无序。

原因二：辅助索引挂载的数据（主键）比较大，占用空间。

辅助索引（次要索引）

008.png

辅助索引下挂载的是主键的值。

回表

在一次的查询过程中，先从辅助索引树上找到主键的值，然后再到主键索引树上找到数据，这个过程称为回表。

select * from t where id ='1';此过程不涉及回表（主键是id）

select * from t where name = 'Alice';此过程涉及回表

select id,name from t where name = 'Alice';此语句形成覆盖索引（只在辅助索引树上进行查找即可返回数据）

若想形成覆盖索引，则select的字段要么是where后的索引字段，要么是和where后的字段形成组合索引中的字段，当然也可以将主键和上述两种情况进行组合。

上述表主键是id，并且name是单列索引。

4.4 非聚集索引（MyISAM）

非聚集索引也叫非聚簇索引，指的是索引和数据不在一个文件上。

主键索引

009.png

主键索引树上所挂载的是该主键的地址值，可以理解为数据记录的地址值。

辅助索引（次要索引）

010.png

5 索引使用场景

5.1 哪些情况需要创建索引

1、主键自动建立唯一索引。

2、频繁作为查询条件（即 where 后面）的字段，应该创建索引。

3、查询中排序的字段，应该创建索引，因为 B + tree 有顺序。

4、统计或者分组字段，应该创建索引。

5、覆盖索引好处是什么？不需要回表，用到组合索引可形成覆盖索引。

6、多表关联查询中，关联字段（即 on 两边）应该创建索引。

5.2 哪些情况不需要创建索引

1、表记录太少（万级别）索引是要有存储的开销

2、频繁更新索引要维护

3、查询字段使用频率不高

5.3 为什么使用组合索引

由多个字段组成的索引就是组合索引，索引的使用顺序就是创建的顺序

使用组合索引的话，在一颗索引树上有多个字段，容易形成覆盖索引，效率高

索引使用:遵循最左前缀原则

1、前缀索引

like 常量% 使用索引

like %常量不使用索引

2、最左前缀

从左向右匹配直到遇到范围查询 > < between 索引失效

alter table t1 add index idx_a_b_c_d(a,b,c,d);

explain select * from t1 where a=1 and b=1 and c=1 and d=1;key_len=20

explain select * from t1 where a=1 and b=1 and c>1 and d=1;key_len=15

explain select * from t1 where a=1 and b=1 and d=1 and c>1;key_len=15

drop index idx_a_b_c_d on t1;

alter table t1 add index idx_a_b_c_d(a,b,d,c);

explain select * from t1 where a=1 and b=1 and d=1 and c>1;key_len=20

6 索引失效

6.1 查看执行计划

参数说明

explain出来的信息有10列，分别是

id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

案例表

 --用户表
 create table tuser(
 id int primary key, 
 loginname varchar(100), 
 name varchar(100),age int,sex char(1),
 dep int,
 address varchar(100) );
 --部门表
 create table tdep( id int primary key, name varchar(100) ); 
 --地址表
 create table taddr( id int primary key, addr varchar(100) );  

 --创建普通索引
 mysql> alter table tuser add index idx_dep(dep);
 --创建唯一索引
 mysql> alter table tuser add unique index idx_loginname(loginname); 
 --创建组合索引
 mysql> alter table tuser add index idx_name_age_sex(name,age,sex); 
 --创建全文索引
 mysql> alter table taddr add fulltext ft_addr(addr);

6.1.1 id

每个 select 语句都会自动分配的一个唯一标识符，表示查询中操作表的顺序，有三种情况:

id相同：执行顺序由上到下

id不同：如果是子查询，id号会自增，id越大，优先级越高。

id相同的不同的同时存在

id列为null的就表示这是一个结果集，不需要使用它来进行查询。

6.1.2 select_type(重要)

查询类型，主要用于区别普通查询、联合查询 (union 、 union all) 、子查询等复杂查询。

simple

表示不需要union操作或者不包含子查询的简单select查询。

有连接查询时，外层的查询为simple，且只有一个。

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

primary

一个需要union操作或者含有子查询的select,位于最外层的查询为primary。且只有一个

explain select (select name from tuser) from tuser ;
| id | select_type | table | type | possible_keys | key | key_len |ref |rows|Extra |  
| 1|PRIMARY |tuser|index|NULL |idx_dep |5 |NULL| 1|Usingindex|
| 2 | SUBQUERY | tuser | index | NULL | idx_name_age_sex | 312 |NULL| 1|Usingindex|

subquery

除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery

explain select * from tuser where  id = (select max(id) from tuser);
| id | select_type | table | type | possible_keys | key | key_len |ref |rows|Extra |
| 1|PRIMARY |tuser|const|PRIMARY |PRIMARY|4 |const|1 | NULL|
| 2|SUBQUERY |NULL |NULL |NULL |NULL |NULL |NULL | NULL | Select tables optimized away |

dependent subquery

与dependent union类似，表示这个subquery的查询要受到外部表查询的影响

explain select id,name,(select name from tdep a where a.id=b.dep) from tuser b;
| id | select_type | table | type | possible_keys | key | key_len |ref |rows|Extra |
| 1|PRIMARY |b |ALL |NULL |NULL |NULL | NULL | 2|NULL |
| 2 | DEPENDENT SUBQUERY | a | eq_ref | PRIMARY | PRIMARY | 4 | demo1.b.dep | 1 | NULL |

union todo

union连接的两个select查询，第一个查询是PRIMARY，

除了第一个表外，第二个以后的表select_type 都是union

explain select * from tuser where sex='1'  union select * from tuser where sex='2';

dependent union todo

与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响

explain select * from tuser where sex in 
(select sex from tuser where sex='1' union select sex from tuser where sex='2');

union result

包含union的结果集，出现在union和union all语句中。

因为它不需要参与查询，所以id字段为null 。

derived

from子句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select

explain select * from (select * from tuser 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 |tuser |ALL |NULL |NULL|NULL |NULL| 2 | Using where |

6.1.3 table

显示的查询表名，如果查询使用了别名，那么这里显示的是别名

如果不涉及对数据表的操作，那么这显示为null 。

如果显示为尖括号括起来的，就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。

如果是尖括号括起来的<union M,N>，与上类似，也表示是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

6.1.4 type (重要)

依次从好到差:

system, const, eq_ref, ref, fulltext, ref_or_null, 
unique_subquery, index_subquery, range, index_merge, index, ALL

除了 all 之外，其他的 type 都可以使用到索引；

除了 index_merge之外，其他的type 只可以用到一个索引，优化器会选用最优索引（一个）。

***索引使用至少要到range级别***

system

表中只有一行数据或者是空表。

explain select * from (select * from tuser 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 |tuser |const |PRIMARY |PRIMARY|4 | const| 1|NULL |

const (重要)

使用唯一索引或者主键，返回记录一定是1行记录的等值where条件，通常type是const。其他数据库也叫做唯一索引扫描。

explain select * from tuser where id=1;
explain select * from tuser where loginname = 'zhy';

eq_ref (重要)

连接字段为主键或者唯一索引。此类型通常出现在多表的 join 查询，表示对于前表的每一个结果, 都只能匹配到后表的一行结果，并且查询的比较操作通常是 '='，查询效率较高。

explain select a.id from tuser a left join tdep 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| 2 | Using index |
| 1|SIMPLE |b |eq_ref|PRIMARY |PRIMARY|4 | demo1.a.dep | 1 | Using index |

ref (重要)

针对使用非唯一性索引查询、或者使用等值(=)非主键连接查询、或者是使用最左前缀规则索引查询。


explain select * from tuser where dep=1; -- 非唯一索引
| id | select_type | table | type | possible_keys | key | key_len |ref |rows|Extra |
| 1|SIMPLE |tuser|ref |idx_dep |idx_dep|5 |const|1|NULL |

explain select a.id from tuser a left join tdep 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 | 2|Usingindex |
| 1|SIMPLE |b |ref |ind_name |ind_name |72| demo1.a.name | 1 | Using where; Using index |

explain select * from tuser where name = 'zhaoyun'; -- 最左前缀
| id | select_type | table | type | possible_keys | key | key_len |ref |rows|Extra |
|1| SIMPLE |tuser|ref|idx_name_age_sex|idx_name_age_sex|303|const|1| Using index condition|

思考:explain select * from tuser where sex = '1'; todo

fulltext

全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存，mysql不管代价，优先选择使用全文索引。

explain select * from taddr where match(addr) against('bei');
| id | select_type | table | type | possible_keys | key | key_len |ref |rows|Extra |
| 1 | SIMPLE | tuser | fulltext | ft_addr | ft_addr | 0| NULL| 1 | Using where |

ref_or_null

与ref方法类似，只是增加了null值的比较，实际场景中用的不多。

unique_subquery

todo 。

index_subquery

todo。

range (重要)

    **索引范围扫描**，常见于使用 > 、< 、is null 、between 、in 、like 等运算符的查询中。

explain select * from tuser where id>1;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | tuser | range | PRIMARY | PRIMARY |4 | NULL | 1 | Using where |


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

explain select * from tuser where loginname like 'a%';
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | tuser | range | idx_loginname | idx_loginname | 303 | NULL | 1 | Using index condition |

index_merge

表示查询使用了两个以上的索引，最后取交集或者并集。常见于 and 、or 的条件使用了不同的索引。官方排序，这个在 ref_or_null 之后，但是实际上由于要读取多个索引，性能可能大部分情况下都不如 range 。

index (重要)todo

条件是出现在索引树中的节点的，可能没有完全匹配索引。

索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

explain select loginname from tuser; -- 单索引
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
| 1 | SIMPLE | tuser | index | NULL | idx_loginname | 303 | NULL | 2 | Using index | 

explain select age from tuser; -- 组合索引
| id | select_type | table | type | possible_keys | key | key_len |ref |rows|Extra |
| 1 | SIMPLE | tuser | index | NULL | idx_name_age_sex | 312 |NULL| 2|Usingindex|


思考 : explain select loginname,age from tuser; -- 覆盖索引

all (重要)

这个就是全表扫描数据文件,然后再在 server层进行过滤返回符合要求的记录。todo

mysql> explain select * from tuser; 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
| 1|SIMPLE |tuser|ALL |NULL |NULL|NULL |NULL| 2 | NULL | 

思考:回表查询如何使用索引?

6.1.5 possible_keys

此次查询中可能选用的索引，一个或多个

6.1.6 key

查询真正使用到的索引，select_type 为 index_merge 时这里可能出现两个以上的索引，其他的 select_type这里只会出现一个。

6.1.7 key_len

用于处理查询的索引长度。如果是单列索引，那就整个索引长度算进去；如果是多列索引，那么查询不一定能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。

另外，key_len 只计算 where 条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到 key_len中。

6.1.8 ref

如果是使用的常数等值查询，这里会显示const 。

如果是连接查询，被驱动表的执行计划里会显示驱动表的关联字段。

如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

6.1.9 rows

这里是执行计划中估算的扫描行数，不是精确值( InnoDB 不是精确的值，MyISAM 是精确的值，主要原因是InnoDB 里面使用了MVCC并发机制) 。

6.1.10 extra (重要)

这个列包含不适合在其他列中显示但十分重要的额外的信息，这个列可以显示的信息非常多，有几十种，常用的有以下几种。

使用 not in() 形式子查询或 not exists 运算符的连接查询，这种叫做反连接 ，即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。todo

no tables used

不带from字句的查询或者From dual查询

即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。

using filesort (重要)

排序时无法使用到索引时就会出现这个，常见于order by和group by语句中。说明MySQL会使用一个外部的索引排序，而不是按照索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序”

explain select * from tuser order by address; 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
| 1| SIMPLE |tuser|ALL |NULL |NULL|NULL |NULL| 2 | Using filesort |

using index (重要)

查询时不需要回表查询,直接通过索引就可以获取查询的数据。表示相应的 select 查询中使用到了覆盖索引 ( covering index )，避免访问表的数据行，效率不错。

如果同时出现 Using Where ，说明索引被用来执行查找索引键值；

如果没有同时出现 Using Where ，表明索引用来读取数据而非执行查找动作。

explain select name,age,sex from tuser ;
| id | select_type | table | type | possible_keys | key | key_len |ref | rows | Extra | 
| 1 | SIMPLE | tuser | index | NULL | idx_name_age_sex | 312 |NULL| 2|Usingindex|

using temporary

表示使用了临时表存储中间结果。 MySQL在对查询结果order by 和 group by时使用临时表。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status中的 used_tmp_table、used_tmp_disk_table才能看出来。

explain select distinct a.id from tuser a,tdep b where a.dep=b.id;
| id | select_type | table | type | possible_keys | key | key_len |ref | rows | Extra | 
| 1 | SIMPLE | a |index | PRIMARY,idx_loginname,idx_name_age_sex,idx_dep | idx_dep | 5 | NULL| 2 | Using where; Using index; Using temporary | 
| 1 |SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | kkb2.a.dep | 1 | Using index; Distinct |

using where (重要)

表示存储引擎返回的记录并不是所有的都满足查询条件，需要在 server 层进行过滤。

explain select * from tuser where address='beijing'; -- 查询条件无索引
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
| 1|SIMPLE |tuser|ALL |NULL |NULL|NULL |NULL| 2 | Using where | 

explain select * from tuser where age=1; -- 索引失效
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE |tuser|ALL |NULL |NULL|NULL |NULL| 2 | Using where | 

explain select * from tuser where id in(1,2); 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
| 1 | SIMPLE |tuser|range|PRIMARY |PRIMARY|4 |NULL|2 | Using where |

using index condition

查询条件中分为限制条件和检查条件。5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后 server 层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据直接不读取，这样就大大减少了存储引擎扫描的记录数量。

explain select * from tuser where name='asd';
| id | select_type | table | type | possible_keys | key | key_len | ref   | rows | Extra |
| 1 | SIMPLE | tuser | ref | idx_name_age_sex | idx_name_age_sex | 303 | const | 1 | Using index condition |

6.2 索引失效分析

1. 全值匹配我最爱

条件与索引一一对应

explain select * from tuser where name='zhaoyun' and age=1 and sex='1';
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | tuser | ref | idx_name_age_sex | idx_name_age_sex | 312| const,const,const |    1 | Using index condition

2. 最佳左前缀法则

针对组合索引，如果索引了多个列,要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

explain select * from tuser where age=23; -- 带头大哥死
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1| SIMPLE |tuser|ALL |NULL |NULL|NULL |NULL| 2 | Using where |

explain select * from tuser where name='aa' and sex='1'; -- 中间索引断
| 1 | SIMPLE | tuser | ref | idx_name_age_sex | idx_name_age_sex | 303 | const | 1 | Using index condition |
  
explain select * from tuser where name='aa' and sex='1' and age=23; -- 比较
  
| 1 | SIMPLE | tuser | ref | idx_name_age_sex | idx_name_age_sex | 312 | const,const,const 
| 1 | Using index condition |
  
explain select * from tuser where name='aa' and sex=1 and age=23; -- 比较  注意sex的写法
| 1 | SIMPLE | tuser | ref | idx_name_age_sex | idx_name_age_sex | 308 | const,const | 1 | Using index condition |

3. 在索引上做计算

不要进行这些操作：计算、函数、自动/手动类型转换，不然会导致索引失效而转向全表扫描

explain select * from tuser where loginname='zy';
| 1 | SIMPLE | tuser | const | idx_loginname | idx_loginname | 303 | const| 1|NULL |

explain select * from tuser where left(loginname,1)='zy';
| 1 | SIMPLE | tuser | ALL |NULL |NULL|NULL |NULL| 2 | Using where |

4. 范围条件右边的列失效

不能继续使用索引中范围：条件（ bettween、<、>、in 等）右边的

explain select * from tuser where name='asd' and age>20 and sex='1';
| 1 | SIMPLE | tuser | range | idx_name_age_sex | idx_name_age_sex | 308| NULL | 1 | Using index condition |

5. 尽量使用覆盖索引

尽量使用覆盖索引（只查询索引的列），也就是查询列和索引列一致，减少 select *

explain select * from tuser ; 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1|SIMPLE |tuser|ALL |NULL |NULL|NULL |NULL| 2 | NULL | 

explain select name,loginname from tuser ; 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
| 1|SIMPLE |tuser|ALL |NULL |NULL|NULL |NULL| 2 | NULL | 

explain select name,age,sex from tuser ; 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | tuser | index | NULL | idx_name_age_sex | 312 |NULL| 2|Usingindex| 

explain select loginname  from tuser ;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | tuser | index | NULL | idx_loginname | 303 | NULL | 2 | Using index |

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

索引字段上使用(!= 或者 < >)判断时，会导致索引失效而转向全表扫描。

注：主键索引会使用范围索引，辅助索引会失效

mysql> explain select * from tuser where loginname='zhy'; 
| id | select_type | table | type  | possible_keys | key | key_len |ref   | rows | Extra |
| 1 | SIMPLE | tuser | const | idx_loginname | idx_loginname | 303 | const| 1|NULL | 

mysql> explain select * from tuser where loginname!='zhy'; 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1|SIMPLE |tuser|ALL |idx_loginname|NULL|NULL |NULL| 1 | Using where |

7. 主键索引字段上不可以判断 null

主键字段上不可以使用 null
索引字段上使用 is null 判断时，可使用索引

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

explain select * from tuser where loginname is  null;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | tuser | ref | idx_loginname | idx_loginname | 303 |const | 1 | Using index condition |

explain select * from tuser where id is not null; -- 主键非空 不使用索引
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
| 1|SIMPLE |tuser|ALL |PRIMARY |NULL|NULL |NULL| 2 | Using where |

8. 索引字段使用 like， 以通配符开头

索引字段使用like以通配符开头('%字符串')时，会导致索引失效而转向全表扫描。

explain select * from tuser where name like 'a%';
|  1 | SIMPLE | tuser | range | idx_name_age_sex | idx_name_age_sex | 303| NULL | 1 | Using index condition |

explain select * from tuser where name like '%a';
| 1|SIMPLE |tuser|ALL |NULL |NULL|NULL |NULL| 2 | Using where |

由结果可知，like 以通配符结束相当于范围查找，索引不会失效。与范围条件 ( bettween、<、>、in 等 ) 不同的是，不会导致右边的索引失效。

问题：如何解决 like ‘% 字符串 %’ 时索引失效的问题? 使用覆盖索引可以解决。

explain select name,age,sex from tuser where name like '%a%'; 
| id | select_type | table | type | possible_keys | key | key_len |ref |rows|Extra |
| 1 | SIMPLE | tuser | index | NULL | idx_name_age_sex | 312 | NULL | 2 | Using where; Using index |

9. 索引字段字符串要加单引号

索引字段是字符串，但查询时不加单引号会导致索引失效而转向全表扫描。

explain select * from tuser where name=123;
| 1 | SIMPLE | tuser | ALL | idx_name_age_sex | NULL | NULL | NULL |2 | Using where |

10. 索引字段不要使用 or

索引字段使用 or 时,会导致索引失效而转向全表扫描

explain select * from tuser where name='asd' or age=23;
| 1 | SIMPLE | tuser | ALL | idx_name_age_sex | NULL | NULL | NULL | 2 | Using where |

6.3 总结（假设index（a,b,c））

where语句	索引是否被使用
where a = 3	使用到a
where a = 3 and b = 4	使用到a,b
where a = 3 and b = 4 and c = 5	使用到a,b,c
where b = 4 或者 where b = 4 and c =5 或者 where c =5	未使用到
where a = 3 and c = 5	使用到a,未使用c,因为b中间断了
where a = 3 and b > 4 and c = 5	使用到a，b，未使用到c
where a = 3 and b like 'kk%' and c = 5	使用到a,b,c
where a = 3 and b like '%kk' and c = 5	使用到a
where a = 3 and b like '%kk%' and c = 5	使用到a
where a = 3 and b like 'kk%kk%' and c = 5	使用到a,b,c

优化口诀总结

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

like百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用。

MySQL索引篇

1 索引介绍

1.1 索引是什么

1.2 索引的优势和劣势

1.2.1 索引的优势

1.2.1.1 检索

1.2.1.2 排序

1.2.1.2.1 where子句中

1.2.1.2.2 select子句中

1.2.2 索引的劣势

2 索引分类

3 索引的使用

3.1 创建索引

3.2 删除索引

3.3 查看索引

4 索引原理分析

4.1 索引存储结构

4.2 B树和B+树

B树图示

B+树图示

B树和B+树的区别

4.3 聚集索引（InnoDB）

主键索引

辅助索引（次要索引）

回表

4.4 非聚集索引（MyISAM）

主键索引

辅助索引（次要索引）

5 索引使用场景

5.1 哪些情况需要创建索引

5.2 哪些情况不需要创建索引

5.3 为什么使用组合索引

索引使用:遵循最左前缀原则

6 索引失效

6.1 查看执行计划

6.1.1 id

6.1.2 select_type(重要)

6.1.3 table

6.1.4 type (重要)

6.1.5 possible_keys

6.1.6 key

6.1.7 key_len

6.1.8 ref

6.1.9 rows

6.1.10 extra (重要)

6.2 索引失效分析

6.3 总结 （ 假设index（a,b,c））

优化口诀总结

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

6.3 总结（假设index（a,b,c））