Mysql索引

1.索引是一种可以快速查找和检索数据的数据结构，本质可以看成是一种排序号的数据结构

2.MySQL中使用的是B+树作为索引结构。

3.索引的优缺点

        优点：加快数据的检索速度

                   创建唯一性索引，保证数据库表中每一行数据的唯一性

        缺点：创建和维护索引需要耗费大量的时间，如果进行增删改查的时候需要对表内的索引动态的修改，减低sql执行效率。

                索引会额外的占用物理存储空间。

使用索引不一定能提升效率，如果数据库不够大，不用索引可能更快

4.索引的底层数据结构： 

        hash表: 拉链->红黑树

        B树/B+ 树： B树也成为了B-树，全称为多路平衡查找树。B是平衡的意思。

        B树对比B+树：

                        1.B树采用的是类似于二分查找的方式，可能查不到叶子结点就停止查找。B+是非常稳定的从跟节点查到叶子节点。。

                         2.B树的每个节点都是存放的key：value对， B+ 树在非叶子节点存放的是key，在叶子节点存放的是key：value。

                         3.B树的每个叶子结点都是独立的，但是B+树的每个叶子结点有一条引用链指向与他相邻的叶子结点。

5.MyIsam和InnoDB的使用B+树的不同：

       非聚集索引： MyISAM是索引和数据分离的，所以在B+树的叶子节点存放的是索引，然后根据拿到的索引去寻找相应的数据。

        聚集索引：InnoDB采用的是文件索引在一起的形式，所以数据文件就是主索引，在B+树的叶子节点中，有主索引和其辅助索引，在根据主索引检索时，直接找到key所在的节点即可取出数据，在根据辅助索引查找的时候，需要先取出主键的值，然后在走一遍主索引。所以设计主键的时候，不适用过长的字段作为主键，也不建议使用非递增的字段作为主键。 

6.主键索引

每个数据表的主键使用的就是主键索引，主键不能为null，不能重复。一个数据表只有一个主键。

在mysql 中，当没有显示的指定是否是主键的时候，会寻找是否有唯一索引，且不允许存在null值的字段，如果有，设置为主键索引，否则会自己创建一个6byte的自增主键。

 

 7.二级索引

二级索引的叶子结点存的是主键，也就是说通过二级索引可以定位主键的位置。

几种典型的二级索引：

1.唯一索引: 不允许出现重复的数据，但是允许数据位null，一张表可以创建多个唯一索引

2.普通索引：无限制，仅仅为了加速查找

3.前缀索引：主要为了查找字符串，对文本的前几个字符创建索引，相比普通索引创建的数据更小。

4.全文索引: 主要是为了检索大文本数据中的关键字，是常用技术之一。

普通索引。唯一索引。全文索引。前缀索引

8.聚集索引和非聚集索引

 聚簇索引（Clustered Index）即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。

在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

优点：

        1.加速查找：聚簇索引的查询速度非常快，因为整个B+ 树本身就是一个多叉平衡树，叶子结点还都是有序的。  定位到索引节点就相当于定位到了数据，少了一次IO操作

        2.对排序查找和范围查找优化： 聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：

        1.依赖于有序数据： 因为B+树是多路平衡树，所以如果插入的数据不是有序的，需要排序

        2.维护代价大： 若果索引对应数据被修改，那么对应的索引也会被修改，而且聚簇索引的叶子节点还存放这数据，所以对于主键索引来说，一般不允许修改数据。

非聚簇索引介绍

非聚簇索引(Non-Clustered Index)即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据

优点 ：

更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的

缺点 ：

• 依赖于有序的数据 ：跟聚簇索引一样，非聚簇索引也依赖于有序的数据
• 可能会二次查询(回表) ：这应该是非聚簇索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询

9.覆盖索引

非聚簇索引不一定回表查询。

试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

 SELECT name FROM table WHERE name='guang19';

那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

即使是 MYISAM 也是这样，虽然 MYISAM 的主键索引确实需要回表，因为它的主键索引的叶子节点存放的是指针。但是！如果 SQL 查的就是主键呢?

SELECT id FROM table WHERE id=1;

主键索引本身的 key 就是主键，查到返回就行了。这种情况就称之为覆盖索引了。

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 覆盖索引（Covering Index） 。我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

10.联合索引

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

以 score 和 name 两个字段建立联合索引：

ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name);

11.

正确使用索引的一些建议

# 选择合适的字段创建索引

• 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
• 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
• 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
• 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

# 被频繁更新的字段应该慎重建立索引

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

# 限制每张表上的索引数量

索引并不是越多越好，建议单张表索引不超过 5 个！索引可以提高效率同样可以降低效率。

索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。

因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

# 尽可能的考虑建立联合索引而不是单列索引

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

# 注意避免冗余索引

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

# 字符串类型的字段使用前缀索引代替普通索引

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

# 避免索引失效

索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这些：

• 使用 SELECT * 进行查询;
• 创建了组合索引，但查询条件未遵守最左匹配原则;
• 在索引列上进行计算、函数、类型转换等操作;
• 以 % 开头的 LIKE 查询比如 like '%abc';
• 查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
• 发生隐式转换open in new window;
• ......

# 删除长期未使用的索引

删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗。

MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用。

# 知道如何分析语句是否走索引查询

我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 ，这样就知道语句是否命中索引了。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。

EXPLAIN 的输出格式如下：

mysql> EXPLAIN SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | cus_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997572 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

各个字段的含义如下：

列名	含义
id	SELECT 查询的序列标识符
select_type	SELECT 关键字对应的查询类型
table	用到的表名
partitions	匹配的分区，对于未分区的表，值为 NULL
type	表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	所选索引的长度
ref	当使用索引等值查询时，与索引作比较的列或常量
rows	预计要读取的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息