MySQL的索引

Java知识点总结：想看的可以从这里进入

5、索引的使用

---

5.1、简介

索引（Index） 是帮助MySQL高效获取数据的数据结构。我们读书的时候往往会根据书的目录快速的找到我们需要的章节，而索引的功能就类似于这个目录，把数据库中无序的数据变成相对有序的数据。

索引一般是存放在本地磁盘文件上的，有两种存放方式：

• 聚簇索引：把索引和数据放在一起，对主键查询有高性能。

  • 优点：

    数据访问更快 ，聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。

    聚簇索引对于主键的 排序查找 和 范围查找 速度非常快

    按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以 节省了大量的io操作 。

  • 缺点：

    插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键，且不可更新

• 非聚簇索引，单独存放

索引可以提高数据的检索效率，降低数据排序成本，减少cpu的消耗，但是索引会占据磁盘的空间，降低更新表的效率，索引在数据量较小的情况下，用处不是很大，但是在数据量很大的情况下，索引的速度提升会非常的明显。

• 索引的优点

  • 通过唯一索引可以保证数据的唯一性
  • 索引可以提高数据的检索效率，降低数据排序成本，减少cpu的消耗
  • 加速表和表之间的连接

• 索引的缺点：

  • 如果表中添加了索引，当表中数据发生改变时需要同时对索引进行维护，会增加维护的时间

  • 索引的使用会导致占用更大的物理空间

  • 索引的创建和维护都需要一定时间，且随数据增加而增加

5.2、索引的分类

• 单列索引

  • 主键索引（PRIMARY）：由主键形成的索引，一个表只能有一个，不能为null，不能重复
  • 唯一索引（UNIQUE）：数据列不能重复，可以为null，值唯一
  • 普通索引（KEY）：基本索引类型，无限制，允许在定义索引的列中插入重复值和空值。

• 复合索引：多个字段组合上创建的索引，只有使用了这些字段的左边字段时，索引才会使用

• 全文索引（FULLTEXT）：仅可用于 MyISAM 表，针对较大的数据，生成全文索引耗时耗空间。

• 空间索引（SPATIAL）：MySQL5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。

• 前缀索引：在文本类型如CHAR、VARCHAR、TEXT类列上创建索引时，可以指定索引列的长度，不能指定数值类型

  index(field(10))：使用字段前10个字符创建索引（默认使用全部字符），必须要辨识度高

5.3、索引的类型

索引常见的类型有哈希索引、有序数组索引、树索引、跳表等等。InnoDB引擎的索引类型有B+树索引和哈希索引，默认的索引类型为B+树索引。

MySQL是一种持久化的数据库，它的数据是存放在本地磁盘文件中的，在查询数据时需要把文件先加载到内存中，而磁盘的IO非常耗时，所以如何优化存储结构来降低IO的操作是非常有必要的。

• 哈希索引：通过哈希表存放数据，对于每一行数据，存储引擎会对索引列通过哈希算法进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是o(1)。一般多用于精确查找，无法进行区间的查询。

• 二叉树：二叉树的深度和对磁盘的操作次数对应，磁盘每次的读取都是一页，二叉树的每个节点(一页)只能存放一个数据，所以MySQL大量的数据，自然会导致二叉树过深，这也就意味着每层都需要去读取一次磁盘数据，耗费的时间会大大的增加。

  

• b树：根据降低二叉树的高度从而做到降低IO操作的设想，诞生了B树，在二叉树的基础上，每个节点尽可能多的存储数据。这样二叉树相当与变成了多叉树，这样会大大的降低树的高度。所以b树 在I/O操作次数上大大减少，但在范围查找的时候每次都会进行重新检索，效率并没有提升，且如果value的所占空间增加，会导致每个节点存放的数据量变小，b数也会随之变高。

  

• b+树：针对b树优化而来，它只在叶子节点存放数据，且叶子节点之间使用双向链表连接，其余节点存放key方便查询。所以它即保留了b树的特点，又优化了范围查询。

  它在每个节点内尽可能多的存放数据，1000个索引(16k/16=1000)，通过增加树杈，将二叉树变成多叉树，有效降低树的高度（100万数据只需2层），会大大提高查询效率。

  在B+树中，所有的记录节点都是按照键值大小的顺序放在叶子节点上，如下图。

  

  从上图可以看出 ，因为B+树具有有序性，并且所有的数据都存放在叶子节点，所以查找的效率非常高，并且支持排序和范围查找。

5.4、存储引擎的索引

MySQL的两种存储引擎为 MyISAM 和 InnoDB，其中MyISAM 使用非聚簇索引InnoDB使用了聚簇索引。

• MyISAM非聚簇索引：索引存储在索引文件.MYI中，数据文件存储在数据文件 .MYD中

  • 主键索引：在B+树的底层叶子节点中，key保存的主键，而value中保存的是对应在磁盘中的位置。当根据sql语句检索到相应的叶子节点后，将数据保存到缓冲中，并检索出需要的数据，再根据数据中保存的地址，查到在MYD文件中的数据。
  • 在 MyISAM 中，辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。

• InnoDB索引

  • 聚簇索引：索引和数据都保存在一个文件中，聚簇索引默认是主键，如果表中没有主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，则隐式定义一个主键作为聚簇索引。

    在B+数的底层叶子节点中，存储的是一个数据表整行的数据，以主键为索引保存。将查找到的叶子节点内数据加载到缓存中，并检索出所需数据，直接返回即可。

  • 辅助索引：除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值。

    辅助索引需要检索两次，第一根据辅助索引获取主键的值，第二次根据主键的值再获取相应的数据（就是回表）。

    但是当索引上包含了查询语句中的所有列时，那么就不必再进行回表查询（就是索引覆盖）。

5.5、索引的创建使用

--  索引在建表的时候给字段直接添加索引，或者给某字段增加一个索引

 -- 创建索引
create  index 索引名 on  表名(列名(长度))
create index ind_name on student(name);
 -- 删除索引
drop index ind_name;
-- 重命名索引：
alter index index_old rename to index_new;--重新命名索引
-- 查看表的索引：
show index from 表名

-- 索引分类：
-- 普通索引：normal
create index 索引名 on 表名（列名）;
create index ind_name on student(name);
-- 唯一性索引：unique
create unique index 索引名 on 表名（列名）;
create unique index ind_name on school(phone);
-- 位图（分类）索引：bitmap
-- 数据量比较大，基数比较小     比如：男/女
create bitmap index 索引名 on 表名（列名）;
create bitmap index ind_sid on student(sid);
-- 函数索引：
create index ind_email on student(length(email));

5.6、使用场景

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

5.7、使用原则

• 首选唯一性索引，因为其唯一性所以能更快的来查询某条记录

• 经常作为查询条件、分组、连接条件的列加索引（ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段），而不是为查询输出结果的列加索引。

• 限制索引的数量，每个索引都需要额外的物理空间，维护也需要花费时间，所以索引不是越多越好。

• 尽量使用数据量少的索引，索引的值很长，那么查询的速度会受到影响

  如果需要对于较长的字符串进行索引时，应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，并且索引高速缓存中的块可以容纳更多的键值，会使得查询速度更快。

• 删除不再使用或很少使用的索引

• 选择区分度高的列作为索引（当查询优化处理器发现查询结果超过全表的30%的时候，就会跳过索引，直接进行全表扫描）。比如使用性别这种区分度很低的列作为索引，效果就会很差，因为列的基数最多也就是三种，大多不是男性就是女性。

• 尽量利用最左匹配原则：以联合索引为基础，将联合索引中属性识别度最高的查询语句放在最前面。（因为底层的B+树是按照从左到右比较大小进行排序的）

  • 全值匹配的时候优化器会自动改变顺序
  • 索引匹配从最左边的地方开始，如果没有则会进行全表扫描，比如一个(a,b,c)的联合索引，可以使用(a)、(a,b)、(a,b,c)， 当使用 (b)、(b,c)、©时就用不到索引了。
  • 遇到范围匹配会取消索引

• 索引不参与计算，带函数的查询不参与索引

• 尽量在原索引上扩展，而不是直接创建新索引。

• 最适合索引的列是在where后面出现的列或者连接句子中指定的列，而不是出现在SELECT关键字后面的选择列表中的列。

• 写操作比较频繁的列慎重加索引

• 当多表关联查询时，关联字段应该创建索引

• 如果数据的基数较小的表，不建议使用索引，因为需要单独维护索引表的开销。

• text、image、bit等数据类型的列不适合建索引

5.8、优化

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

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

• 在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，因为这样无法使用索引。例如select * from table_name where a + 1 = 2
• 将区分度最高的索引放在前面
• 尽量少使用select*
• explain分析查询语句
• 考虑使用数值类型代替字符串：MySQL对数值类型的处理速度要远远快于字符串
• 数据列声明为NOT NULL：减少了判断，可以降低复杂性，提高查询速度。

索引的使用场景、索引的设计原则和如何对索引进行优化可以看成一个问题。

5.9、索引失效

• 使用or时，or前后没有同时使用索引，当且仅当or语句查询条件的前后列均为索引列时，索引失效
• 对索引计算会导致索引失效，函数不涉及索引
• link语句以通配符开头时（’%字符串%’）索引无效，以&结尾时索引有效
• 复合索引索引没有使用第一个索引列时，索引失效（最左匹配原则）：例如索引是key index （a,b,c）。可以支持（a）、（ a,b）、（ a,b,c） 3种组合进行查找，但不支持 （b,c）进行查找 。当最左侧字段是常量引用时，索引就有效。
• 索引字段上使用 is null、is not null判断时索引失效，不索引空值，例如select * from table_name where a is null
• 在索引的类型上进行数据类型的隐形转换，会导致索引失效。例如字符串一定要加引号，假设 select * from table_name where a = '1'会使用到索引，如果写成select * from table_name where a = 1则会导致索引失效。
• 索引上使用！、=、<>进行判断时会导致索引失效，例如select * from table_name where a != 1
• 当全表查询速度比使用索引的速度快时不会使用索引