MySQL数据库索引_数据库范围索引

索引是什么

• 索引是对数据库表中一个或多个列的值进行排序的结构，是帮助MySQL高效获取数据的数据结构。
• 索引类似于书的目录，允许数据库程序不需要扫描整个数据库就可以迅速的找到想要的数据
• 索引是唯一的，创建索引可以允许指定单个列或多个列。

问：什么样的字段适合建索引
答：唯一、不为空、经常被查询的字段

索引的优缺点

• 索引加快了数据库的检索速度
• 通过使用索引可以在查询过程中使用优化隐藏器，提高系统的性能
• 建立索引需要时间且需要维护，进行插入、删除、修改等维护任务时也要对作业进行动态维护，降低了效率
• 索引需要占物理和数据空间

索引有哪些结构

Hash索引和B+树索引

Hash索引：底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储上没有任何顺序关系，对于区间查询无法直接通过索引查询，需要全表扫描。哈希索引只适合用于等值查询的场景
B+树索引：底层是多路平衡查询树，节点天然有序（左小于父，父小于右），所以对于范围查询不需要全表扫描，对任意节点所耗费的时间都是完全相同的

• 哈希索引适合等值查询，无法进行范围查询
• 哈希索引不能利用索引完成排序
• 哈希索引不支持多列联合索引的最左匹配规则
• 如果有大量重复键值，哈希索引效率会很低，因为存在哈希碰撞问题

InnoDB存储引擎绝大多数情况下使用B+树建立索引。B+树索引并不能找到给定键对应的具体值，只能找到数据行对应的页，数据库把整个页读入内存中，并在内存中查找具体的数据行。

基本的索引类型

• 主键索引（聚集索引/聚簇索引）（primary key）：数据列不允许重复，并且不能为null，具有唯一性
• 唯一索引（UNIQUE INDEX）：不允许重复，可以为null，一个表允许创建唯一索引
• 普通索引（index）：没有唯一性限制，可以为null
• 全文索引（FULLTEXT）：是目前搜索引擎使用的一种关键技术。能够用分词技术等多种算法只能分析出文本文字中的关键词的频率和重要性，然后按照一定的算法规则智能的筛选出我们想要的搜索结果。 一般用于关键字匹配来过滤查询的情况。
  对于大量的文本数据检索不适合模糊匹配，效率极低。
  全文索引工作原理：
  • 索引程序从数据库读取数据
  • 索引程序对需要索引的内容进行“分词”——分词程序
  • 索引程序对分好词的一个个词条加入索引文件
  • 注意：
    • MySQL自带的全文索引只能对英文进行全文检索，如果需要对包含中文的文本进行全文索引，需要采用Sphinx/Coreseek技术来处理中文。
    • 使用MySQL自带的全文索引时，如果查询字符串的长度果断将无法得到预期结果（默认最小长度为4个字符）。
    • 先创建表并插入数据后再创建全文索引，比 创建表时就直接创建全文索引效率要高很多。

聚簇索引和非聚簇索引

索引又可以分为聚簇索引和非聚簇索引：
聚簇索引：对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。存储数据的顺序和索引顺序一致，且一个表只能有一个聚簇索引。主键索引一般都是聚餐索引。
非聚簇索引（辅助索引）：表数据存储顺序与索引顺序无关，叶子节点包含索引字段值以及指向数据页数据行的逻辑指针。

• 主键默认创建聚簇索引，一张表只允许存在一个聚簇索引
• 聚簇索引的叶子节点就是数据节点，存放着一条行记录的全部信息。非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针（在InnoDB中也就是当前记录的主键）。
• 聚簇索引主键的插入速度比非聚簇索引主键的插入速度慢很多
• 聚簇索引适合排序，因为本身是按照物理顺序放置，排序很快。
• 非聚簇索引用于加快数据查找，所以一张表上往往有多个非聚簇索引提升数据库的性能

问：主键就是聚簇索引吗
答：InnoDB作为MySQL存储引擎时，默认按照主键进行聚簇，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。如果没有，InnoDB就会定义影藏的主键然后进行聚簇。所以一般在在创建主键的时候就自动创建的主键的聚簇索引

回表

非聚簇的叶子节点存储的不是数据而是对应的主键的索引，根据这个主键的索引去聚簇索引查找数据，叫做回表。

问：非聚簇索引一定会回表查询吗
答：不一定，如果要查询的字段都命中了主键索引，也就是刚好在该B+树的叶子结点里，则不需要回表查询

问：聚簇索引查询只会查一次，而非聚簇索引一定需要回表查询多次吗
答：通过覆盖索引也可以只查询一次
覆盖索引：指查询语句的执行只用从索引中就能够取得，不必从数据表中读取，也可以称之为实现了索引覆盖。

查询优化器

查询优化器会比较所有的查询方案，最终选择成本最低的方案来执行
优化过程：

• 根据搜索条件找出所有可能使用的索引
• 计算全表扫描的代价
• 计算使用不同索引执行查询的代价
• 对比各种执行方案的代价，找出成本最低的那个

问：什么时候设置了索引但无法使用
答：根本原因是查询优化器决定不适用索引

• 查询语句没有按照索引的要求来
  • 对单字段建了索引，where条件多字段
  • 建立组合索引，where条件单字段
  • 条件中用or，即使其中有条件带索引，也不会使用索引查询
  • like的模糊查询的模糊词在字符串前面，比如以%或_开头，索引失效
  • 在使用不等于（is null、is not null，！=，<>）的时候无法使用索引，会导致全表扫描
  • 类型错误
  • 对索引应用内部函数，这种情况下应该建立基于函数的索引
  • 索引列不能是表达式的一部分，也不能是函数的参数
  • MySQL预计全表扫描比使用索引快，则不使用索引

单列索引和联合索引（组合索引/复合索引/多列索引）

• 联合索引是指几个字段一起作为索引（多个索引列）
• 利用索引的附加列可以缩小搜索范围
• 使用一个具有两列的索引 不同于 使用两个单列索引
  如果查询条件为a,b;如果我们在a和b上分别创建单列索引，但mysql查询每次只能使用一个索引，虽然相对不做索引提高了效率，但如果在a,b上创建联合索引的话会带来更高的效率。
• 创建联合索引时，要仔细考虑列的顺序
  若顺序为a,b；会先选符合a的，然后再在符合a的里面选符合b的。所以如果只知道b不知道a，则该联合索引就没用了
• sql语句中的字段顺序不需要和联合索引中定义的字段顺序一致。因为查询优化器会自己调整顺序
• 测试联合索引时不要直接用具体数据，可以模糊，因为用具体的会直接命中表中大部分数据，这样MySQL查询优化器会认为全表扫描比索引扫描效率更高而选择的全表扫描

最左前缀原则

主要使用在联合索引中。
如果我们创建了(a,b,c)的联合索引，那么其实相当于创建了(a,b,c)、(a,b)、(a)三个索引，这就是最佳左前缀特性。所以我们在创建联合索引时应该将最常用作限制条件的列放在最左边，一次递减。

explain关键字

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。通过explain可以过得以下信息：

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

使用方法：explain + sql语句。
执行计划部分字段含义：
id：id相同，执行顺序由上至下。id不同，如果是子查询id序号会递增，id越大优先级越高，越先被执行。id又有相同又有不同时，id相同认为是一组，同一组从上往下，所有组中id大的优先执行
type：显示查询用了哪种类型，类型从好到差依次是：
system>const>eq_ref>ref>range>index>all

• system表中只有一行记录
• const表示通过索引一次性找到
• eq_ref唯一性索引扫描
• ref非唯一性索引扫描
• range只检索给定范围的行
• index 该类型只遍历索引树，所以比all速度更快
• all 将遍历全表以找到匹配的行

possible_keys和key：possible_keys显示可能应用在这张表中的一个或多个索引，查询涉及到的字段若存在索引，则该索引被列出，但不一定被查询实际使用。key实际使用的索引，null代表没有使用
key_len：索引中使用的字节数，长度越短越好
rows：根据表统计信息及索引选用情况，大致估算出需要读取的行数。

所以可以通过explain关键字来测试单列索引和联合索引

explain关键字

使用索引一定能提高查询性能吗

问:使用索引一定能提高查询性能吗
答：不一定。

• 索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。
• 在查询时，有一个查询优化器，太多的索引会让优化器困惑，可能没有办法找到正确的查询路径，从而选择了慢的索引
• 索引的范围查询适用于两种情况：
  • 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
  • 基于非唯一性索引的检索
  • 直接晋升为覆盖索引，避免多次查表

建立索引优化

• 创建合适的索引：不建索引的话，不管什么查询都会进行一次全表扫描，效率很低
• 利用最左前缀原则创建复合索引
• 索引列中不能包含null值，如果复合索引中有一列含有null，那么此复合索引就无效，所为我们在数据库设计时不要让字段默认值为null。
• 使用短索引：可以提高查询速度，节省磁盘空间和I/O操作
• 排序的索引问题：mysql查询只使用一个索引，所以数据库默认排序可以符合要求的话不要使用排序操作，尽量不要包含多个列的排序
• 不要在列上进行运算

什么时候需要建立索引

问：哪些情况需要建立索引，哪些情况不需要
答：为经常查询和排序的数据列建立索引
需要：

• 主键自动建立唯一索引
• 频繁作为查询条件的字段
• 查询中与其他表关联的字段，外键关系建立索引
• 单键/组合索引的选择问题（在高并发下倾向创建组合索引）
• 查询中排序的字段，排序字段通过索引访问将大大提高排序速度
• 查询中统计或者分组字段

不需要：

• 表记录太少
• 经常增删改的表（不仅要保存数据，还要保存索引文件）
• 数据重复且分布平均的字段
• where条件里面用不到的字段

MySQL5.6中对索引的优化

• 索引下推优化（Index Condition Pushdown），默认开启。

people表中（zipcode，lastname，firstname）构成一个索引

SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

如果没有使用索引下推技术，则MySQL会通过zipcode='95054'从存储引擎中查询对应的数据，返回到MySQL服务端，
然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。

如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054'的索引，然后根据lastname LIKE '%etrunia%'
和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。
有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。
1
2
3
4
5
6
7
8
9
10

参考链接（里面还有两个小链接）
联合索引
索引原理