mysql索引类型

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不
是组合索引。组合索引，即一个索引包含多个列。

主键（聚簇）索引 PRIMARY KEY

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。
一个表只能有一个主键索引。

唯一索引 UNIQUE

唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

普通索引 INDEX

这是最基本的索引，它没有任何限制。

组合索引 INDEX

组合索引，即一个索引包含多个列。

全文索引 FULLTEXT

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分
析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

不同索引实现

哈希索引

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的值即 key， 就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。
不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的
一种方法是，拉出一个链表。
哈希表示例图：
image.png

innodb存储引擎支持的哈希索引是自适应的，innodb存储引擎会根据表的使用情况自动为表生成哈希索
引，不能人为干预是否在一张表中生成哈希索引。
哈希表这种结构适用于只有等值查询的场景，不能用于排序。

Btree索引

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。
系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的
大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令
查看页的大小：
image.png
而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。
B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。
image.png
每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，
指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据
的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

B+tree索引（InnoDB的索引结构）

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构;
在B+Tree中， 所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息， 这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。
B+Tree相对于B-Tree有几点不同：

  1. 非叶子节点只存储键值信息。
  2. 所有叶子节点之间都有一个链指针。
  3. 数据记录都存放在叶子节点中。

image.png
通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点
（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查
找和分页查找，另一种是从根节点开始，进行随机查找。
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引 （clustered index）。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引 （secondary index）。如果使用二级索引，则需要通过回表才能拿到整行数据。

索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。
当当前的数据页满了之后，根据B+树的算法，需要申请一个新的数据页，然后将部分数据挪动过去，这个过程称为页分裂。
当相邻的两个数据页由于删除了数据，利用率很低之后，会将数据页合并。这个过程称为页合并。
问：为什么通常建表语句要加一个自增的主键ID？
答：因为主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。但是如果表中只有一个索引，且为唯一索引的情况下，因为没有其他索引，所以不需要考虑其他索引叶子节点的大小，则可以直接将该字段设置为主键索引。

覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。覆盖索引可以显著提高性能。

最左前缀原则

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。
不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。
在建立联合索引的时候，如何安排索引内的字段顺序？

  1. 如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
  2. 第二个需要考虑的是空间问题，选用小的字段放在左边。

    索引下推

    在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
    无索引下推：
    image.png
    有索引下推：
    image.png

    重建主键索引

    问：如何重建主键索引
    答案1（错误）：先删除主键索引，在重新建立索引。即先运行alter table T drop primary key，在运行alter table T add primary key(id)。因为不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。
    答案2（正确）：运行alter table T engine=InnoDB。

    索引选择原则

    explain分析语句与profile分析

    1. EXPLAIN [EXTENDED] SELECT select_ options
    使用EXTENED关键字，EXPLAIN语句将产生附加信息。select options 是SELECT语句的查询选项，包括
    FROM WHERE子句等。
    执行该语句，可以分析EXPLAIN后面的SELECT语句的执行情况，并且能够分析出所查 询的表的一些特征。
    image.png
    字段解析：
字段 描述
id select识别符。这是select的查询序列号，id的值越大优先级别越高，越先被执行，如果id相同，执行顺序由上至下
select_type select_type表示select语句的类型。
（1）SIMPLE ：表示简单查询，其中不包括连接查询和子查询。
（2）PRIMARY ：表示主查询或者最外层的查询语句
（3）SUBQUERY：子查询
（4）DERIVED ：衍生查询-在select出一批自定义列的数据，概念上相当于一张表，但是该表只在语句执行过程出现
（5）UNION : 联合查询，union 后面的那张表就会表示成它
（6）UNION RESULT : 联合结果
table type表示这一行的数据是关于哪张表的。
partitions
type 对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
ALL : Full Table Scan， MySQL将遍历全表以找到匹配的行
index : Full Index Scan，index与ALL区别为index类型只遍历索引树
range : 只检索给定范围的行，使用一个索引来选择行
ref : 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref : 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配， 简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys possible_keys列指出mysql能使用哪个索引在该表中找到行。如果这列是null，则
没有相关的索引。在这种情况下，可以通过检查where子句查看它是否引用某些
列或者适合索引的列来提高查询性能。如果是这样，可以创建适合的索引来提高
查询的性能
key key表示查询实际应用到的索引，如果没有选择索引，该列的值是null。想要强制
使mysql使用或者忽视possible_type列中的索引，在查询中使用force index，use
index 或者 ignore index。
key_len key_len表示mysql选择的索引字段按字节计算的长度，如果键是null，则长度为
null，注意通过key_len值可以确定mysql将实际使用一个多列索引中的几个字段
ref ref表示使用哪个列或常数与索引一起来查询记录
rows rows显示mysql表中进行查询时必须检查的行数
filtered
Extra extra显示mysql在处理查询时的详细信息。
① Using filesort: 如果根据索引列进行排序（order by 索引列）是可以用到索引的，SQL查询引擎会先根据索引列进行排序，然后获取对应记录的主键id执行回表操作，如果排序字段用不到索引则只能在内存中或磁盘中进行排序操作，MySQL把这种在内存或者磁盘上进行排序的方式统称为文件排序（英文名：filesort），如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort
② Using temporary: 许多查询的执行过程中，MySQL会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含distinct、group by、union等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary提示.
③ USING index: 表示相应的select操作中使用了覆盖索引（Covering Index）,避免回表操作，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查找动作。
④ Using where: 使用了where过滤
⑤ using join buffer: 在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度， MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度
⑥ impossible where: where子句的值总是false，不能用来获取任何元组
⑦ select tables optimized away: 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT()操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
⑧ distinct: 优化distinct，在找到第一匹配的元组后即停止找同样值的工作
⑨ *Using index condition：查找使用了索引，但是需要回表查询数据

profile分析

对定位一条语句的I/O消耗和CPU消耗 非常重要。(SQL 语句执行所消耗的最大两部分资源就是IO和CPU)
在mysql5.7之后，profile信息将逐渐被废弃，mysql推荐使用performance schema
image.png
image.png
image.png
image.png

索引优化原则

  1. 唯一字段可以单独建立单索引，非唯一考虑联合索引,推荐尽量使用唯一字段建立索引
  2. 索引的个数，联合索引的个数 最佳 6个 以内，如果索引因为项目需求：最多 10个
  3. 索引的使用遵循最左匹配原则其次覆盖索引
  4. 尽量选择小的字段建立索引 int ，varchar(10)， char(5)
  5. 避免<，<= ，> ,>= , % ,between 之前的条件。选择索引的字段的范围和模糊之前，因为范围与模糊

会引起索引失效，针对于联合索引，就是联合索引的中间尽量不要有范围查询的字段

  1. 尽量多使用explain分析
  2. 避免更新频繁的字段 （二叉树会一直变化，导致性能变慢）
  3. 建立的索引- 优先考虑 建立 联合索引
  4. 索引字段不要有 null， 不是 ‘’