【MySQL】索引基础介绍

一、常见的存储引擎有哪些？区别是什么？

• Mysql 默认的存储引擎是：InnoDB，具有支持事务、行级锁、高并发访问性能。
• MyIsAM：访问速度快，实际运用如果是以读和插入操作为主，并对事物完整性和并发性要求不高，推荐选择，因为 MyISAM 最小的锁粒度是表锁，一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。（在 MySQL 5.5之前，当您创建表而未明确指定存储引擎时，MyISAM 是默认存储引擎，这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一）
• Memory：不支持事务安全，支持表锁，不支持外键，所有数据储存在内存中，访问速度快。常用来做缓存。
• InnoDB：Innodb 是 MySQL 5.5 之后默认的存储引擎，支持事务安全，锁机制是行锁，支持外键，一般运用在对数据完整性较高的核心数据。（选择最多）

二、索引结构

• 二叉树：弊端当数据顺序插入时，纵深过长就导致性能降低
• 红黑树：是自平衡的二叉树，本质也是一个二叉树，也有大数据层级深，导致检索速度慢
• B-Tree：多路平衡查找树（几个指针就是几阶，每个指针指向一个key），每一个子节点都会放数据
• B+Tree：（最大度数为4,4阶B+Tree），所有的元素都会出现在叶子结点，且所有的数据都会储存在叶子结点，叶子结点组成了一个双向链表。
• Hash：Hash索引只能用于对等比较，不支持范围查询，无序，查询效率高，高于B+Tree索引。通常只需要一次检索就可以了，除遇到hash碰撞，（槽位后面的链表）。

InnoDB 为什么选择 B+Tree 

 三、索引分类

• 主键索引

        索引列中的值必须是唯一的，不允许有空值。

• 唯一索引

        索引列中的值必须是唯一的，但是允许为空值。

• 全文索引

        只能在文本类型 CHAR，VARCHAR，TEXT 类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行 like 模糊查询时效率比较低，这时可以创建全文索引。

三、什么是聚簇索引和非聚簇索引

• 聚簇索引：将数据存储的顺序与索引顺序相同，找到索引也就找到了数据
• 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，本质上非聚簇索引存储的是聚簇索引的值，比如主键ID

​ 每个InnoDB表具有一个特殊的索引称为聚簇索引（也叫聚集索引，聚类索引，簇集索引），聚集索引选取规则：

• 如果表上定义有主键，该主键索引就是聚簇索引。
• 如果未定义主键，MySQL 取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB 使用它作为聚簇索引。如果没有这样的列，InnoDB就自己产生一个这样的ROWID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

表中的聚簇索引（clustered index ）就是一级索引，除此之外，表上的其他非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

聚簇索引( 主键索引）和非聚簇索引（非主键索引）有什么区别？

聚集索引就是主键，主键id下面挂的是这一行的数据，而二级索引下面挂的是这一数据的id值

当执行select * from user where name=‘ARM’这条sql语句时

通过name查询，数据库会先到二级索引中去查到询该name所在的id，再根据id到聚集索引中去查到name=‘ARM’这一条具体数据。这一过程被称为 回表查询。

归根到底是因为，普通索引无法直接定位行记录。

所以，并不是创建完索引就完事的，在写 sql 语句的时候，尽量避免回表。

四、索引建立原则有哪些？

创建索引的命名规范：idx_user_name，idx 是 index 的简写，后面跟表名 user 跟字段名 name

创建唯一索引需要加unique，命名规范：uniq_idx_user_name，

创建索引之前要做SQL性能分析，查看服务器的状态信息、查看数据库的 增删改查的访问频率： SHOW GLOBAL STATUS LIKE 'Com_______'; ，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次等。

五、哪些情况下索引会失效？

• 索引使用最左前缀法则 如果索引了多列（联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列如果跳跃某一列，索引将部分失效(后面的字段索引失效)。 （如果跳过最左边的列后面的索引就会失效）
• 范围查询，联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效，如何规避：使用>=或<=
• 字符串不加引号，字符串类型的字段使用时不加引号，索 引将会失效
• 模糊查询，如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效（"%工"）
• or连接的条件用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。只有两侧都有索引的时候所有索引才不会失效

六、explain 查看 sql 语句 type 说明

表示连接类型，性能由好到差的连接类型为（system > const > eq_ref > ref > range > index > all ），其中 system、const 只是理想类型，基本达不到。我们自己实际能优化到 ref、range 这两个类型，ref 级别基本达不到。要对 type 优化的前提是，需要有索引，如果你连索引都没有创建，那你就不用优化了，肯定是 all。

• NULL（对正常的业务系统来说，不可能优化为 null），一般情况下不会为 NULL，只有在没有查询任何表的时候才会返回 NULL；

explain select 'Hello World'

• system（表中仅有一行（相当于系统表），这是 const 联结类型的一个特例）
• const（表示通过索引一次就找到，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以如果主键置于 where 列表中， mysql 能将该查询转换为一个常量。）
• eq_ref（唯一性索引扫描，对于每一个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描。）
• ref（非唯一性的索引扫描，返回匹配某个单独值得所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体）
• range（使用索引返回一个范围中的行，比如使用 between、<、>等的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描。）
• index（用了索引，但是扫描了全部的索引）
• all（查询全部数据，扫描全表，这一般比较糟糕，尽量避免）

注意：一般保证查询至少达到 range 级别，最好能达到 ref 级别。

六、平常遇到的一些总结

1、一个值只有0和1的情况，需不需要创建索引

不需要

2、经常删除操作的sql，对索引有没有影响

会有影响，insert 开销会比较大。delete 开销稍微小一点，但是删除大批量的数据会有很大影响。update 操作除非修改的是索引字段的值，不然对索引没有影响。

七、参考文档

• 聚簇索引与非聚簇索引概念
• Mysql 索引总结
• 索引的缺点（3）：降低增删改的性能