一、什么是索引？

  1. 1、对数据库表中一列或多列的值进行排序的一种数据存储结构。
  2. 2、类似书的目录，使用索引可以快速访问数据库表中的行记录数据。
  3. 3、基本原理：存储引擎通过遍历索引定位基表中的行，然后返回给Server层，再去为这些行记录数据进行where条件过滤。
  4. 4、InnoDB存储引擎的叶子结点数据是按照主键rowid进行排序的，目的是方便对磁盘做顺序I/O

二、索引的数据结构

  1.   MySQL数据库使用B+tree索引结构，B+tree是由二叉树 -> 平衡二叉树 -> B-tree树逐步演化而成。

1、二叉树结构

  1. 每个结点至多有2个子结点
  2. 二叉树有左右序之分，次序不能颠倒
  3. 二叉树中，左子树的键值永远比右子树的键值小，并且小于根结点的键值

2、平衡二叉树结构

  1. - 解决了二叉树节点深度增加，查询的均分复杂度上升的问题，拥有比二叉树更快的查询速度
  3. - 条件：
  4. ​   左右两个子树的高度差的绝对值不超过1
  5. ​   左右两个子树也都是平衡二叉树

3、B-tree结构(Btree、B树)

  1. 一个结点可以拥有多于2个子结点的多叉查找树
  2. 所有叶子结点都在同一层
  3. 叶子结点不包含任何关键字信息

4、B+tree索引结构

  1. B-tree结构的变体，双向链表结构，一种多路搜索树
  2. 所有关键字信息都存放在叶子结点，并包含这些关键字记录的指针
  3. 叶子结点按照关键字大小顺序连接，因此访问关键字的顺序是连续性的，不需要回溯上一个结点
  4. 所有数据都存放在叶子结点中

三、B+tree索引分类

  1.   show index from 表名，
  2.   可以查看表中有哪些索引，使用expalin命令可以查看SQL语句的执行计划(优化器对SQL语句的优化执行方案)。

1、聚集索引和普通索引

  • B+tree索引分为两大类：聚集索引和非聚集索引

聚集索引

  1.   叶子结点存放表中所有行数据记录的信息；
  2. ​    对于聚集索引，数据即索引，索引即数据；
  3. ​    创建主键时会自动创建；

普通索引

  1.     叶子结点不包含所有行的数据记录，只存放索引字段的键值和行记录数据的主键值；
  2. ​    检索数据时，通过普通索引叶子结点找到主键，再通过主键来获取行数据记录；

MySQL语法：

  1.   ALTER TABLE 表名 ADD INDEX 索引名(列名);
  2. ​      CREATE INDEX 索引名 ON 表名(列名);
  4. create index ename_index on emp(ename);
  5. drop index ename_index on emp;
  6. show index on emp;

2、主键索引(聚集索引)和唯一索引

主键索引必须满足三个条件：

主键索引就是聚集索引

  1. 主键值必须唯一；
  3. 不能包含NULL值；
  5. 必须保证值是自增的(自增保证insert是顺序的，基于磁盘的顺序访问特性，提高了存取效率)；

  • 唯一索引必须满足一个条件：

    1. 值必须是唯一的，但是值允许是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优化特性，

    1. 把普通索引的叶子结点上查找到的主键值的集合存储到read_rnd_buffer；
    3. 在read_rnd_buffer中对主键值排序；
    5. 最后利用排序后的主键值集合进行顺序回表读取行记录数据；
    7. 因此达到了进行顺序I/0访问磁盘的目标，避免查询过程中出现I/0随机访问，提高了I/O效率；

  • 出现Using join buffer(Batched Key Access)关键字，说明表连接使用BKA优化特性，

    1. BKA是提高join性能的算法，在读取被join表的记录时使用顺序I/0；
    3. 当MySQL使用索引访问第二个join表时，使用一个join buffer收集第一个操作对象生成的相关列值，批量传递给引擎层做索引查找；
    5. 使用时基于MRR特性；

5、filtered列，表示返回结果的行占需要读取到的行(rows列的值)的百分比

五、索引的利弊

1、使用索引的好处

  • 提高数据检索效率
  • 提高聚合函数的效率 (组函数 max(), min()…)
  • 提高排序效率
  • 使用覆盖索引可以避免回表

2、使用索引的正确方式

  • 适合创建索引的列：

    经常被查询的条件列；

    经常用于表连接的列；

    经常排序分组的列；

  • 不适合创建索引的列：

    选择性低的字段不要创建索引，如性别字段只能筛选出1/2的数据，区分度不大，不该创建索引；

    很少查询的列不要创建索引，需要事先和需求方沟通确认；

    大数据类型字段不要创建索引，如text、blob类型字段不要创建索引；

  • 避免的情况

    尽量避免不要使用NULL，MySQL对含有NULL值的列很难进行查询优化，会让索引、索引的统计信息及比较运算更加复杂，因此DBA经常推荐使用NOT NULL；

    尽量在MySQL客户端的应用程序中做运算和判断，不要让数据库做各种运算；

3、索引失效的情况

  • 通过索引扫描的行记录数超过全表的30%，优化器不会走索引，变成全表扫描
  • 联合索引中，第一个查询条件不是最左索引列
  • 联合索引中，第一个索引列使用范围查询，只能使用到部分索引，有ICP出现
  • 联合索引中，第一个查询条件不是最左前缀列
  • 模糊查询条件列，最左以通配符%开始(可以放到子查询里)
  • 两个单列索引，一个检索，一个排序，只能使用其中一个索引(查询语句中最多只能使用一个索引)，这种情况考虑创建联合索引
  • 查询字段有索引，但是使用了函数运算

4、索引的副作用

  • 创建太多的索引，在DML操作时，将会增加维护索引的负担