数据库的索引

数据库索引

1. 索引是一种“排好序的快速查找的”数据结构
2. 优势：提高检索效率，降低数据库IO成本；通过索引对数据排序，降低数据排序的成本，降低CPU消耗；加速表间连接；
3. 劣势：更新表时需要维护索引，增加更新操作的消耗；储存索引需要占用额外的空间，索引也是一张表，储存在磁盘中；

MySQL索引数据结构

1. BTree：结点包括key值，指向数据的指针，指向下一个索引结点的指针；当前结点命中时，直接通过数据指针指向真实数据；
   

2. B+Tree：结点包括key值，指向下一个索引结点的指针；无论是否命中，走到叶子结点才有指向数据的指针；

3. 为什么MySQL不选择查找更快的BTree而选择B+Tree作为索引

   • 计算机实际查找效率，不仅考虑算法的时间复杂度，还要考虑IO访问的次数，相比内存时间，缺页中断调IO的时间消耗更大
   • B+树的磁盘读写代价更低 ：B+Tree的结点不包含指向数据的指针，所占空间较小，一个结点可以存放更多的元素，树的高度较低。内存可以一次性读取更多的元素信息，命中率更高，从而调用IO的次数也更低。
   • B+树的查询效率更加稳定：于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

4. 哈希索引：基于哈希表实现的索引

5. Hash索引和B+树的区别？
   因为两者数据结构上的差异导致它们的使用场景也不同，哈希索引一般多用于精确的等值查找，B+索引则多用于除了精确的等值查找外的其他查找。在大多数情况下，会选择使用B+树索引。

   • 哈希索引不支持排序，因为哈希表是无序的。
   • 哈希索引不、支持范围查找。
   • 哈希索引不支持模糊查询及多列索引的最左前缀匹配。
   • 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点

MySQL索引类型

MySQL主要的索引类型主要有FULLTEXT，HASH，BTREE，RTREE。

• FULLTEXT

  FULLTEXT即全文索引，MyISAM存储引擎和InnoDB存储引擎在MySQL5.6.4以上版本支持全文索引，一般用于查找文本中的关键字，而不是直接比较是否相等，多在CHAR，VARCHAR，TAXT等数据类型上创建全文索引。全文索引主要是用来解决WHERE name LIKE "%zhang%"等针对文本的模糊查询效率低的问题。

• HASH

  HASH即哈希索引，哈希索引多用于等值查询，时间复杂夫为o(1)，效率非常高，但不支持排序、范围查询及模糊查询等。

• BTREE

  BTREE即B+树索引，INnoDB存储引擎默认的索引，支持排序、分组、范围查询、模糊查询等，并且性能稳定。

• RTREE

  RTREE即空间数据索引，多用于地理数据的存储，相比于其他索引，空间数据索引的优势在于范围查找

索引的种类

• 主键索引：数据列不允许重复，不能为NULL，一个表只能有一个主键索引
• 组合索引：由多个列值组成的索引。
• 唯一索引：数据列不允许重复，可以为NULL，索引列的值必须唯一的，如果是组合索引，则列值的组合必须唯一。
• 全文索引：对文本的内容进行搜索。
• 普通索引：基本的索引类型，可以为NULL

聚簇索引与非聚簇索引

定义：聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

聚簇索引

将数据和索引放到一起存储，索引结构的叶子节点保留了数据行。

优点：

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 聚簇索引对于主键的排序查找和范围查找速度非常快

缺点

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
• 更新主键的代价很高*，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
• 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

非聚簇索引

辅助索引，将数据进和索引分开存储，索引叶子节点存储的是指向数据行的地址

应用

在InnoDB存储引擎中，默认的索引为B+树索引，利用主键创建的索引为主索引，也是聚簇索引，在主索引之上创建的索引为辅助索引，也是非聚簇索引。辅助索引中的叶子节点存储的是主键。

在MyISAM存储引擎中，默认的索引也是B+树索引，但主索引和辅助索引都是非聚簇索引，也就是说索引结构的叶子节点存储的都是一个指向数据行的地址。并且使用辅助索引检索无需访问主键的索引。

回表查询

非聚簇索引的叶子节点存储的是主键，也就是说要先通过非聚簇索引找到主键，再通过聚簇索引找到主键所对应的数据，后面这个再通过聚簇索引找到主键对应的数据的过程就是回表查询

索引覆盖

如果查询的数据再辅助索引上完全能获取到便不需要回表查询。例如有一张表存储着个人信息包括id、name、age等字段。假设聚簇索引是以ID为键值构建的索引，非聚簇索引是以name为键值构建的索引，select id,name from user where name = ‘zhangsan’;这个查询便不需要进行回表查询因为，通过非聚簇索引已经能全部检索出数据，这就是索引覆盖的情况。如果查询语句是这样，select id,name,age from user where name = ‘zhangsan’;则需要进行回表查询，因为通过非聚簇索引不能检索出age的值。那应该如何解决那呢？只需要将索引覆盖即可，建立age和name的联合索引再使用select id,name,age from user where name = ‘zhangsan’;进行查询即可。

索引的使用场景

• 对于中大型表建立索引非常有效，对于非常小的表，一般全部表扫描速度更快些。
• 对于超大型的表，建立和维护索引的代价也会变高，这时可以考虑分区技术。
• 如何表的增删改非常多，而查询需求非常少的话，那就没有必要建立索引了，因为维护索引也是需要代价的。
• 一般不会出现再where条件中的字段就没有必要建立索引了。
• 多个字段经常被查询的话可以考虑联合索引。
• 字段多且字段值没有重复的时候考虑唯一索引。
• 字段多且有重复的时候考虑普通索引。

索引的设计原则

• 最适合索引的列是在where后面出现的列或者连接句子中指定的列，而不是出现在SELECT关键字后面的选择列表中的列。
• 索引列的基数越大，索引的效果越好，换句话说就是索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差，因为列的基数最多也就是三种，大多不是男性就是女性。
• 尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，并且索引高速缓存中的块可以容纳更多的键值，会使得查询速度更快。
• 尽量利用最左前缀。
• 不要过度索引，每个索引都需要额外的物理空间，维护也需要花费时间，所以索引不是越多越好。

如何对索引进行优化

对索引的优化其实最关键的就是要符合索引的设计原则和应用场景，将不符合要求的索引优化成符合索引设计原则和应用场景的索引。

除了索引的设计原则和应用场景那几点外，还可以从以下两方面考虑。

• 在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，因为这样无法使用索引。例如select * from table_name where a + 1 = 2
• 将区分度最高的索引放在前面
• 尽量少使用select*

索引失效

最左匹配原则：

从最左边为起点开始连续匹配，遇到范围查询（<、>、between、like）会停止匹配

在上面介绍了几种不符合最左匹配原则的情况会导致索引失效，除此之外，以下这几种情况也会导致索引失效。

• 不符合最左匹配原则的情况会导致索引失效
• 条件中有or，例如select * from table_name where a = 1 or b = 3
• 在索引上进行计算会导致索引失效，例如select * from table_name where a + 1 = 2
• 在索引的类型上进行数据类型的隐形转换，会导致索引失效，例如字符串一定要加引号，假设 select * from table_name where a = '1'会使用到索引，如果写成select * from table_name where a = 1则会导致索引失效。
• 在索引中使用函数会导致索引失效，例如select * from table_name where abs(a) = 1
• 在使用like查询时以%开头会导致索引失效
• 索引上使用！、=、<>进行判断时会导致索引失效，例如select * from table_name where a != 1
• 索引字段上使用 is null/is not null判断时会导致索引失效，例如select * from table_name where a is null

SQL索引相关操作

• 创建

  CREATE INDEX index_name ON table_name (column_list);	#在table上创建
  

      CREATE TABLE user(
      id INT PRIMARY KEY,
      information text,
      FULLTEXT KEY (information)
  );															#建表时创建
  

  ALTER TABLE table_name ADD INDEX index_name (column_list);	#修改表时创建
  

  前缀索引

  ALTER TABLE table_name ADD KEY(column_name(prefix_length));	#建立prefix_length长度的前缀索引
  #估计prefix_length？
  SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;	#计算全列区分度
  SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;#使用前缀的分度
  

• 删除

  alter table 表名 drop primary key			#删除主键索引
  alter table 表名 drop key 索引名			  #删除其他索引