一、什么是索引？
1、对数据库表中一列或多列的值进行排序的一种数据存储结构。 2、类似书的目录，使用索引可以快速访问数据库表中的行记录数据。 3、基本原理：存储引擎通过遍历索引定位基表中的行，然后返回给Server层，再去为这些行记录数据进行where条件过滤。 4、InnoDB存储引擎的叶子结点数据是按照主键rowid进行排序的，目的是方便对磁盘做顺序I/O
二、索引的数据结构
MySQL数据库使用B+tree索引结构，B+tree是由二叉树 -> 平衡二叉树 -> B-tree树逐步演化而成。
1、二叉树结构
每个结点至多有2个子结点 二叉树有左右序之分，次序不能颠倒 二叉树中，左子树的键值永远比右子树的键值小，并且小于根结点的键值
2、平衡二叉树结构
- 解决了二叉树节点深度增加，查询的均分复杂度上升的问题，拥有比二叉树更快的查询速度 - 条件： 左右两个子树的高度差的绝对值不超过1 左右两个子树也都是平衡二叉树
3、B-tree结构(Btree、B树)
一个结点可以拥有多于2个子结点的多叉查找树 所有叶子结点都在同一层 叶子结点不包含任何关键字信息
4、B+tree索引结构
B-tree结构的变体，双向链表结构，一种多路搜索树 所有关键字信息都存放在叶子结点，并包含这些关键字记录的指针 叶子结点按照关键字大小顺序连接，因此访问关键字的顺序是连续性的，不需要回溯上一个结点 所有数据都存放在叶子结点中
三、B+tree索引分类
show index from 表名， 可以查看表中有哪些索引，使用expalin命令可以查看SQL语句的执行计划(优化器对SQL语句的优化执行方案)。
1、聚集索引和普通索引
- B+tree索引分为两大类：聚集索引和非聚集索引
聚集索引
叶子结点存放表中所有行数据记录的信息； 对于聚集索引，数据即索引，索引即数据； 创建主键时会自动创建；
普通索引
叶子结点不包含所有行的数据记录，只存放索引字段的键值和行记录数据的主键值； 检索数据时，通过普通索引叶子结点找到主键，再通过主键来获取行数据记录；
MySQL语法：
ALTER TABLE 表名 ADD INDEX 索引名(列名); CREATE INDEX 索引名 ON 表名(列名); create index ename_index on emp(ename); drop index ename_index on emp; show index on emp;
2、主键索引(聚集索引)和唯一索引
主键索引必须满足三个条件：
主键索引就是聚集索引
主键值必须唯一；
不能包含NULL值；
必须保证值是自增的(自增保证insert是顺序的，基于磁盘的顺序访问特性，提高了存取效率)；
- 唯一索引必须满足一个条件：
值必须是唯一的，但是值允许是NULL；
- 一张表只能有一个主键索引，但是唯一索引可以有多个
- MySQL语法：
创建主键索引：创建主键时自动创建；
创建唯一索引：ALTER TABLE emp add unique(列名)；
3、覆盖索引
- 通过索引就可以直接返回查询数据，不需要在查询索引之后再去回表查询数据，减少了磁盘I/O操作，因此查询速度快
- 使用explain命令查看SQL语句的执行计划
extra列出现Using index关键字，表明SQL语句使用了覆盖索引； - 符合覆盖索引的场景
必须在select列出所需要的列，而不能写select * from emp;
4、前缀索引
- BLOB、TEXT、较长的VARCHAR类型的列，为前面最左侧的若干个字符创建的一种索引
- MySQL语法：
ALTER TABLE emp add key(columnName(prefixLength)); - 这种索引很小，所查询较快
- 不足之处：
不能在ORDER BY和GROUP BY中使用；
不能用做覆盖索引
5、联合索引(复合索引)
- 在表中两个或者两个以上的列上创建的索引
- 利用索引上的附加列，可以缩小检索的段池范围，更快的检索数据
- MySQL语法：
create index idx_c1_c2 epm(列名c1,列名c2); - 在使用过程中，必须要满足最左前缀原则：
创建索引时，将选择性高的列放在前面；
一条查询语句可以只使用索引中的一部分，但是必须从最左索引列开始；
如使用索引idx_c1_c2，可以检索c1或者c1，c2，但是不能直接检索c2；
- 例外情况select * from emp where c1=? or c2=?
不能使用联合索引，需要创建两个单列索引或者考虑使用union；
6、哈希索引
- 采用哈希算法，把键值换算成哈希值
- 只能进行等值查询，不能进行排序、模糊查找、范围查询
- 查询速度快，检索时不需要像B+tree从根节点到叶子结点逐级查找，只需一次哈希算法即可立刻定位到数据位置
四、使用explain命令查看SQL语句的执行计划
1、type列，表示查询类型
- 出现all关键字，说明全表扫描
2、key列，表示是否使用索引
- 出现null关键字，说明没有使用索引
3、rows列，表示在SQL语句执行过程中扫描行数的估算值
- 数值越大，说明需要扫描的行数越多，耗时越长
4、extra列，
- 出现Using filesort关键字，说明排序使用了文件系统，性能较差
- 出现Using temporary关键字，说明使用了内存临时表空间，性能较差
- 出现Using index condition关键字，说明使用了索引过滤(ICP优化)，where条件可以使用索引，在存储引擎层直接做过滤操作
- 出现Using MRR关键字，说明范围扫描使用了MRR优化特性，
把普通索引的叶子结点上查找到的主键值的集合存储到read_rnd_buffer；
在read_rnd_buffer中对主键值排序；
最后利用排序后的主键值集合进行顺序回表读取行记录数据；
因此达到了进行顺序I/0访问磁盘的目标，避免查询过程中出现I/0随机访问，提高了I/O效率；
- 出现Using join buffer(Batched Key Access)关键字，说明表连接使用BKA优化特性，
BKA是提高join性能的算法，在读取被join表的记录时使用顺序I/0；
当MySQL使用索引访问第二个join表时，使用一个join buffer收集第一个操作对象生成的相关列值，批量传递给引擎层做索引查找；
使用时基于MRR特性；
5、filtered列，表示返回结果的行占需要读取到的行(rows列的值)的百分比
五、索引的利弊
1、使用索引的好处
- 提高数据检索效率
- 提高聚合函数的效率 (组函数 max(), min()...)
- 提高排序效率
- 使用覆盖索引可以避免回表
2、使用索引的正确方式
- 适合创建索引的列：
经常被查询的条件列；
经常用于表连接的列；
经常排序分组的列；
- 不适合创建索引的列：
选择性低的字段不要创建索引，如性别字段只能筛选出1/2的数据，区分度不大，不该创建索引；
很少查询的列不要创建索引，需要事先和需求方沟通确认；
大数据类型字段不要创建索引，如text、blob类型字段不要创建索引；
- 避免的情况
尽量避免不要使用NULL，MySQL对含有NULL值的列很难进行查询优化，会让索引、索引的统计信息及比较运算更加复杂，因此DBA经常推荐使用NOT NULL；
尽量在MySQL客户端的应用程序中做运算和判断，不要让数据库做各种运算；
3、索引失效的情况
- 通过索引扫描的行记录数超过全表的30%，优化器不会走索引，变成全表扫描
- 联合索引中，第一个查询条件不是最左索引列
- 联合索引中，第一个索引列使用范围查询，只能使用到部分索引，有ICP出现
- 联合索引中，第一个查询条件不是最左前缀列
- 模糊查询条件列，最左以通配符%开始(可以放到子查询里)
- 两个单列索引，一个检索，一个排序，只能使用其中一个索引(查询语句中最多只能使用一个索引)，这种情况考虑创建联合索引
- 查询字段有索引，但是使用了函数运算
4、索引的副作用
- 创建太多的索引，在DML操作时，将会增加维护索引的负担
