MySQL进阶：2.索引

【专栏目录】
MySQL进阶：1.MySQL基础环境搭建
MySQL进阶：2.索引
MySQL进阶：3.视图
MySQL进阶：4.为什么不推荐使用存储过程和触发器
MySQL进阶：5.存储引擎
MySQL进阶：6.定位SQL性能问题
MySQL进阶：7.SQL优化

本文导读

本文主要对索引进行详解

1.索引概述

索引的本质：一种有序的数据结构，一般常用的是多路平衡搜索树，即BTREE

索引的作用：帮助MySQL高效获取数据

条件	查询数据方式
没有索引	顺序遍历数据表查询
使用索引	根据数据结构（索引）的查询顺序查询

【☆☆☆必看】

什么是索引

2.索引的优缺点

优点	缺点
类似书本的目录索引，提高数据检索效率，降低数据库的IO成本	索引本质是数据结构，也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引需要占用额外的磁盘空间
索引会对数据进行排序，降低数据排序的成本，降低CPU的消耗	索引会降低表的更新速度，如增删改，因为更新表时需要更新索引，调整索引的结构（这点结合更新二叉树理解）

3.索引结构

索引	说明
BTREE索引	最常见的索引类型，大部分存储引擎都支持BTREE索引，对应数据结构是B+树
HASH索引	只有Memory引擎支持，使用场景简单
R-tree索引	空间索引，只支持MyISAM引擎，主要用于地理空间数据类型，通常使用较少
Full-text索引	全文索引，支持MyISAM引擎，5.6版本之后支持InnoDB引擎

一般没有特殊说明，默认使用的都是B+树（多路平衡搜索树，不一定是二叉）结构的索引；

其中聚合索引、复合索引、前缀索引、唯一索引默认使用的都是B+树，统称为索引。

3.1B树结构

可参考我的另一篇博客图解B树构建过程

B树和二叉搜索树相比，查询数据效率更高，因为对于相同数量来说，BTREE的层级更少，因此搜索速度更快。

3.2B+树结构

可参考我的另一篇博客图解B+树构建过程

B+树是B树的另一种形态，区别如下：

1. n叉B树每个节点最多包含n-1个key，n叉B+树每个节点最多包含n个key；
2. B+树的叶子节点包含所有的key，并且按照key的大小有序排列；
3. B+树的非叶子节点都可以看作是key的索引部分

4.索引分类

分类	解释
单值索引	一个索引只包含单个列，一个表可以有多个单值索引
唯一索引	单值索引的特殊情况，索引列的值必须唯一，但是允许有空值
复合索引	一个索引包含多个列

建议使用复合索引，原因是复合索引可以给MySQL提供的选择比较多，能够根据实际情况选择最优的索引。比如：对某个表的a、b、c列创建复合索引，那就相当于创建了三个索引：[a]、[a,b]、[a,b,c]。

5.索引语法

下面的语句均是在mysql环境中执行

5.1创建索引

mysql> create index [index_name] on [table_name]([col_name...]);

// 创建单值索引
// 例如，给student表的stu_number列创建索引，索引名为index_stu_number：
mysql> create index index_stu_number on student(stu_number);

// 创建复合索引
// 例如，给student表的stu_number、stu_age列创建索引，索引名为index_stu_number：
mysql> create index index_stu_number on student(stu_number,stu_age);

5.2查看索引

mysql> show index form [table_name];

5.3删除索引

mysql> drop index [index_name] on [table_name];

6.索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候需要尽量考虑符合这些原则，便于提升索引的使用效率，更高效地使用索引

1. 查询频次较高，且数据量较大的数据表，适合建立索引；
2. 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合；
3. 使用唯一索引：区分度越高，效率越高；
4. 索引不是越多越好，索引越多维护成本越高，对于增删改这些操作，都会影响索引的构建；
5. 建议缩小索引的大小，即使用短索引。因为索引也是占据磁盘块空间的，如果索引相对较小，那么一块内就可以存储多个索引，从而减少IO次数，进而提升效率；
6. 利用最左索引（复合索引中的第一个列，后面会详细讲）。

7.索引的最左前缀法则

假设对某数据表中的a、b、c三列创建复合索引，那么实际就创建了三个索引：[a]、[a,b]、[a,b,c]，那么查询列项只要属于这三种索引，就满足最左前缀法则，除此之外都属于违背最左前缀法则，会导致索引失效。即以下几种查询列项的组合就不会导致索引失效：

• [a]
• [a,b]
• [b,a]
• [a.b.c]
• [a,c,b]
• [b,a,c]
• [b,c,a]
• [c,a,b]
• [c,b,a]

示例：

# 对UserEO表中的name和passwd创建一个复合索引
mysql> create index index_name_passwd_token on UserEO (name,passwd,token);

# 满足最左前缀法则的情况
mysql> select * from UserEO where name ='wy';
mysql> select * from UserEO where name ='wy' and passwd ='123123';
mysql> select * from UserEO where passwd ='123123' and name ='wy';
mysql> select * from UserEO where name ='wy' and passwd ='123123' and token ='';
mysql> select * from UserEO where name ='wy' and token ='' and passwd ='123123';
mysql> select * from UserEO where passwd ='123123' and name ='wy' and token ='';
mysql> select * from UserEO where passwd ='123123' and token ='' and name ='wy';
mysql> select * from UserEO where token ='' and name ='wy' and passwd ='123123';
mysql> select * from UserEO where token ='' and passwd ='123123' and name ='wy';

8.索引失效情况

1. like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效；
2. or语句前后没有同时使用索引；
3. 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描；
4. 违背最左前缀法则；
5. 在索引列上使用 IS NULL 或 IS NOT NULL有时会导致索引失效，这需要结合第8点分析，当索引列里的值为NULL的情况占多数时，IS NULL就不会走索引，因为这种情况全表扫描比走索引更快；反过来，索引列不为NULL的情况占多数时，IS NOT NULL不会走索引；
6. 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0;
7. 对索引字段进行计算操作、字段上使用函数。（索引为 emp(ename,empno,sal)）;
8. 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效；
9. 在索引字段上使用in会走索引，not in不走索引。