一、索引概述
1、介绍
索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。在无索引情况下，如果查找数据，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为全表扫描，性能很低。 |
2、特点
优势 | 劣势 |
提高数据检索的效率，降低数据库的 IO 成本 | 索引列也是要占用空间的 |
通过索引列对数据进行排序，降低数据排序的成本，降低CPU 的消耗。 | 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT 、 UPDATE 、 DELETE 时，效率降低。 |
二、索引结构【不要和索引弄混】
1、概述
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构
索引结构 | 描述 |
B+Tree | 最常见的索引类型，大部分引擎都支持 |
Hash索引 | 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询 |
R-tree（空间索引） | 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用少 |
Full-tet（全文索引） | 是一种通过建立倒赔索引，快速匹配问文档的方式。类似于Lucene，Sole，ES |
不同存储引擎对于索引结构的支持情况。
索引 | InnoDB | MyISAM | Memory |
B+tree 索引 | 支 持 | 支 持 | 支 持 |
Hash 索引 | 不支持 | 不支持 | 支 持 |
R-tree 索引 | 不支持 | 支 持 | 不支持 |
Full-text 索引 | 5.6版本之后支持 | 支 持 | 不支持 |
2、二叉树
如果MySQL的索引结构采用二叉树的数据结构，存在以下缺点
|
3、B-Tree
B-Tree，B树是一种多叉路平衡查找树，类似于二叉树，B树每个节点可以有多个分支，即多叉。以一颗最大度数（max-degree）为5阶的B-Tree为例，那这个B树每个节点最多存储4个key，5个指针（分支）： |
插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 。 然后观察一些数据插入过程中，节点的变化情况。 |
为什么索引不采用B树？
①：B树只适合随机检索，不支持顺序检索 ②：B树在一定程度上也提高了磁盘IO性能，但没有解决遍历效率低下的问题。 ③：增删文件时，B树需要重新调整树结构。 |
4、B+Tree
B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例 |
我们可以看到，两部分： ①：绿色框框起来的部分，是 索引部分 ，仅仅起到索引数据的作用，不存储数据。 ②：红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据， 总结：B+树内部节点是不保存数据的，只作索引作用，它的叶子节点才保存数据。 |
5、 Hash
1). 结构
①：哈希索引就是采用一定的Hash算法，将数据库中的数据换算成Hash值，将数据地址存在Hash表里面； ②：如果两个 ( 或多个 ) 键值，映射到一个相同的槽位上，他们就产生了 hash 冲突（也称为 hash 碰撞），可以通过链表来解决 |
2). 特点
A. Hash 索引只能用于 对等比较 (= ， in) ，不支持范围查询（ between ， > ， < ， ... ） B. 无法利用索引完成排序操作 C. 查询效率高，通常 ( 不存在 hash 冲突的情况 ) 只需要一次检索就可以了，效率通常要高于 B+tree 索引 |
3). 存储引擎支持
在MySQL中，支持hash索引的是Memory存储引擎，而Inno DB中具有自适应hash功能，hash索引是Inno DB存储引擎根据B+Tree索引在指定条件下自动构建的 |
思考题：为什么InnoDB存储引擎选择使用B+Tree索引结构？
A：相对于二叉树，层级更少，搜索效率高； B：B-Tree，内部和外部节点都会存储数据，导致一页中存储的键值减少，树高度会增加，导致性能降低。 C：相对Hash索引，B+Tree支持范围匹配及排序操作。 |
三、索引分类
1、概述
注意事项：
①：索引和索引结构，是一对多的关系；【主键索引可以用B+Tree，也可以用Hash，但是不建议用Hash】
②：索引结构，并不要求数据在列存中具备唯一性，唯一特性不是索引结构的事情
在MySQL数据库，将缩影的具体类型主要分为以下几类：主键、唯一、常规、全文索引 | |||
索引分类 | 索引含义 | 索引特点 | 建表字段关键字 |
主键索引 | 针对于表中主键创建的索引 | 默认自动创建，只能有一个 | PRIMARY |
唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULL-TEXT |
2、聚焦索引&二级索引
分类 | 含义 | 特点 | 描述 |
聚集索引（Clustered Index） | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有，而且只有一个 | 如果存在主键，主键索引就是聚集索引。 如果不存在主键，将使用第一个唯一（ UNIQUE ）索引作为聚集索引。 如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。 |
二级索引（Secondary Index） | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 | 二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询 |
思考题：InnoDB主键索引的B+Tree高度为多高呐？
假设 : 一行数据大小为1k ，一页中可以存储 16 行这样的数据。 InnoDB 的指针占用 6 个字节的空间，主键即使为bigint ，占用字节数为 8 。 高度为2： n * 8 + (n + 1) * 6 = 16*1024 , 算出 n 约为 1170 1171* 16 = 18736 也就是说，如果树的高度为2 ，则可以存储 18000 多条记录。 高度为 3 ： 1171 * 1171 * 16 = 21939856 也就是说，如果树的高度为3 ，则可以存储 2200w 左右的记录。 |
四、索引语法
1、创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ;2、查看索引
SHOW INDEX FROM table_name ;3、删除索引
DROP INDEX index_name ON table_name ;五、SQL性能分析
1、SQL执行频率
MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT 、 UPDATE 、 DELETE 、 SELECT 的访问频次： |
# -- session 是查看当前会话 ;
# -- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';
# Com_delete: 删除次数
# Com_insert: 插入次数
# Com_select: 查询次数
# Com_update: 更新次数通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。如果是以查询为主，那么就要考虑对数据库的索引进行优化了
2、慢查询日志
慢查询日志记录了所有执行时间超过指定参数（ long_query_time ，单位：秒，默认 10 秒）的所有SQL语句的日志。 MySQL 的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log【show variables like 'slow_query_log'】。 |
如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=23、profile详情
show profiles 能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了。通过 have_profiling参数，能够看到当前MySQL 是否支持 profile 操作： |
# 查询开关是否开着
SELECT @@have_profiling ;
# 开启开关语句
SET profiling = 1;
SELECT * FROM DGS
SELECT * FROM DGS WHERE ID = 'DDD'
# 查看每一条SQL的耗时基本情况
show profiles;
# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;4、explain / desc
EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。 |
语法：
# 直接在select语句之前加上关键字
# explain / desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
EXPLAIN SELECT * FROM DGSExplain 执行计划中各个字段的含义：
字段 | 含义 |
id | select 查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行） |
select_type | 表示 SELECT 的类型,常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUEERY（SELECT/WHERE之后包含了子查询）等 |
type | 表示连接类型，性能由好到差的连接类型为NULL，system、const、eq_ref、ref、range、index、all。 |
possible_key | 显示可能应用在这张表上的索引，一个或多个。 |
key | 实际使用的索引，如果为NULL，则没有索引 |
key_len | 表示索引中使用的字节数，该键为索引字段最大可能长度，并非实际使用长度，在不损失精确度的前提下，长度越短越好。 |
rows | MySQL认为必须要执行查询的行数，在Innodb引擎的表中，是一个估计值，可能并不总是精确的。 |
filtered | 表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好 |
5、索引使用
1). 最左前缀法则
①：概述
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。 |
②：简述
创建索引SQL如下：
# 在 tb_user 表中，建联合索引，这个联合索引涉及到三个字段，顺序分别为：profession，age，status。 CREATE INDEX IDX_USER_PRO_AGE_STA ON TB_USER (PROFESSION,AGE,STATUS); SHOW INDEX FROM TB_USER ; # 查询该表的索引 |
对于最左前缀法则指的是，查询时， 索引最左边的列 ，也就是 profession 必须存在 ，否则索引全部失效。 注意： 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段) 必须存在，与我们编写 SQL 时， 条件编写的先后顺序无关。 |
2). 范围查询
联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。但是当前索引不会失效
如下：explain select * from tb_user where profession = ' 软件工程 ' and age > 30 and status = '0'; 解释：profession 和 age 用到了索引，但是 status 字 段是没有走索引的。 |
当范围查询使用 >= 或 <= 时，会走联合索引
如下：explain select * from tb_user where profession = ' 软件工程 ' and age >= 30 and status = '0'; 解释：profession 、 age 和 status都用到了索引 |
3). 索引失效情况
①：索引列运算
不用substring() 运算时，phone索引会生效，用了之后，索引失效。
select * from tb_user where substring(phone, 10 , 2 ) = '15' ; |
②：字符串不加引号
字符串类型字段使用时，不加引号，索引将失效。因为：数据库存在隐式类型转换，索引将失效。
③：模糊查询
如果仅仅是尾部模糊匹配（like 'dgs%'），索引不会失效。如果是头部模糊匹配（like '%dgs'），索引失效。
④：or连接条件
用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
explain select * from tb_user where id = 10 or age = 23 ; explain select * from tb_user where phone = '17799990017' or age = 23 ; 由于 age没有索引 ，所以即使 id、phone有索引 ，索引也会失效。所以需要针对于 age 也要建立索引。 |
⑤：数据分布影响
因为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。
4). SQL提示
SQL 提示，是优化数据库的一个重要手段，简单来说，就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。
①：建议使用指定索引
# use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。 # 建议使用profession字段的索引idx_user_pro |
②：忽略指定的索引
# ignore index ： 忽略指定的索引。 # 忽略profession字段的索引idx_user_pro |
③：强制使用索引
# ignore index ： 忽略指定的索引。 # 忽略profession字段的索引idx_user_pro |
5). 覆盖索引
覆盖索引是指 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。
比如：在 tb_user 表中，有联合索引，涉及到三个字段，顺序分别为：profession，age，status。
# 建议使用
SELECT profession，age，status where profession = '软件工程' and age > 30 and status = '0';
# 不建议使用
SELECT * where profession = '软件工程' and age > 30 and status = '0';图文解释：
6). 前缀索引
当字段类型为字符串（ varchar ， text ， longtext 等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO ， 影响查询效率。此时可以 只将字符串的一部分前缀 ，建立索引，这样可以大大 节约索引空间 ，从而提高索引效率。
# 语法
# create index idx_xxxx on table_name(column(n)) ;
# 示例，建立字段email前5个长度的索引
create index idx_email_5 on tb_user(email(5));
6、索引设计原则
1 | 针对于数据量较大，且查询比较频繁的表建立索引。 |
2 | 针对于常作为查询条件（ where ）、排序（ order by ）、分组（ group by ）操作的字段建立索引。 |
3 | 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。 |
4 | 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。 |
5 | 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。 |
6 | 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。 |
7 | 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。 |
