MySQL - 索引

索引

索引 : 非叶子结点中存储的是索引和指针, 叶子结点中存储的是数据和指针

索引概述:

索引(index)是帮助MySQL高效获取数据的数据结构, 在数据之外, 数据库系统还维护着满足特定查找算法的数据结构, 这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找算法, 这种数据结构就是索引

• 如果表中某个字段没有索引, 那么以该字段进行查找的时候就要进行一个全表扫描, 效率是非常低的

• 如果表中某个字段是有索引, 那么通过索引这个数据结构, 我们查找的效率就会非常高

  数据库在存储数据本身之外, 还维护着一个满足特定查找算法的数据结构, 这些数据结构以某种方式指向数据, 这样就可以在这些数据结构的基础上实现高级查找算法, 这种数据结构就是索引

索引优缺点:

优势	劣势
提高数据检索的效率, 降低数据库的IO成本	索引列也是要占用空间的
通过索引列队数据进行排序, 降低数据排序的成本, 降低cpu的消耗	索引大大提高了查询效率, 但是同时也降低了更新表的速度, 如对表进行insert, update, delete时效率降低

• 但是总的来说 : 索引列占用的空间首先是比较少的, 所以对于空间的占用其实是可以忽略不计的, 其次我们使用索引的场景其实就是查询比较多的时候, 所以我们对于比较慢的查询语句我们就可以为对应的字段添加索引, 从而提高查询效率
  • 后面我们将会讲解SQL优化, 其实我们的SQL优化主要就是从索引的角度出发对SQL进行优化

索引结构:

MySQL的索引是在存储引擎层实现的, 不同的存储引擎有不同的结构, 主要包含以下几种:

索引结构	描述
B+Tree索引	最常见的索引, 大部分的存储引擎都支持B+树索引 (InnoDB, MyISAM, Memory都支持B+树索引)
Hash索引	底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询
R-Tree(空间索引)	空间索引是MyISAM引擎的一个特殊索引类型, 主要用于地理空间数据类型, 通常使用较少
Full-Text(全文索引)	是一种通过建立倒排索引, 快速匹配文档的方式, 类似于Lucene, Solr, ES

InnoDB存储引擎1的默认索引结构就是B+Tree索引

索引	InnoDB	MyISAM	Memory
B+Tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-Tree索引(空间索引)	不支持	支持	不支持
Full-Text索引(全文索引)	5.6之后支持	支持	不支持

我们平常所说的索引, 如果没有特别的指明, 都是B+树结构组织的索引

索引结构 - B树:

• 注意: 这里的B树并不是真正的MySQL的索引的结构, MySQL索引结构是B+树, 并且是经过一些变化的B+树
• 很多同学搞不清楚B树的构架, 其实就是添加, 然后添加到一定程度的时候就进行一个分裂, 要知道无论如何B树都是一个满二叉树, 记住这一点自己就一定是可以推导出来的

索引结构 - B+树(未变化, 不是真实的索引结构):

• 这个是一个传统的B+树, 是没有经过改变的, MySQL中的B+Tree索引结构并不是这样的, 而是将叶子结点的结构从单向链表修改成为了双向链表

• 有的同学觉得这不是一个B+树, 这其实是因为B+树是有两种的:

  1. M阶, M - 1个关键字, M个指针指向子节点 (也就是当前给出的B+树)
     • 这种B+树是小于的在左子节点, 大于等于的在右子节点
  2. M阶, M个关键字, M个指针指向子节点
     • 这种B+树好像是等于在左边和右边都是可以的, 但是我们就统一记忆, 就小于在左边, 大于等于在右边即可
  • 注意: 其实B树和B+树对于节点大小在那边是没有影响的, 我们可以类比如果添加的所有元素都是相同的情况分析

• 要知道B+树中数据都是存储在叶子结点的, 非叶子结点的作用就是一个索引的结构, 只要能找到叶子结点就可以了

• B+树的叶子节点是以单向链表, 这样是利于排序的

索引结构 - B+树(对B+树进行了改变, 是真正的索引结构):

• MySQL索引数据结构相对经典的B+Tree进行了优化, 在原有B+tree的基础上, 增加一个指向相邻叶子结点的链表指针, 就形成了带有顺序指针的B+Tree, 提高区间访问性能(原本叶子结点是单向链表, 叶子结点添加一个指针之后变成了双向链表)
  • 可以看到MySQL索引结构的B+树中每个结点都放到了一个独立的页中, 前面我们讲解存储引擎的时候讲过MySQL的体系结构的时候说过 : 一个表空间对应多个段, 一个段对应多个区, 一个区对应多个页, 一页中又对应了多个行
    • 其中一个区的大小就是1M, 一个页的大小是16k

索引结构 - hash:

hash索引结构其实是和Java中的HashMap很像的, jdk7之前的时候HashMap就是数组 + 链表, 也就是使用的链表解决的hash冲突, MySQL中的hash索引也是使用的链表解决的hash冲突

hash索引特点:

1. Hash索引只能用于对等比较(=, in), 不支持范围查询(between, >, <, …)
2. 无法利用索引完成排序操作
3. 查询效率高, 通常只需要一次检索就可以了, 效率通常是要高于B+tree索引

存储引擎支持:

在MySQL中, 支持hash索引的是Memory引擎, 而InnoDB中具有自适应hash功能, hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的

• 关于InnoDB的自适应hash功能, 我们会在后面学习InnoDB引擎的时候深入学习

问题思考: 为什么InnoDB存储引擎使用B+树索引结构?(其实就是和二叉树还有B树还有hash进行比较)

• 相对于二叉树, 层级更加少, 搜索效率高;
• 相对于B-树, 因为B-树无论是叶子结点还是非叶子结点, 都会保存数据, 这样导致一页中存储的键值减少, 指针就会跟着减少, 要同样保存大量数据, 只能是增加树的高度, 导致性能降低
  • 我们讲解B+树索引结构的时候说过: 索引中每个结点都是一个页, 那么如果我们将索引结构设计为B-树, 这样我们非叶子结点中也是存储了数据的, 那么非叶子结点存储的指针就会大量减少, 那么能存储的指针减少了, 一个指针就是对应一个子节点, 那么指针减少了, 就是同一层存储的子节点减少了, 那么同一层存储的子节点减少之后存储同样数目的结点所需的高度就会增加了
• 相对Hash索引, B+Tree支持范围匹配及排序操作

索引分类:

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建, 只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快读定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词, 而不是比较索引中的值	可以有多个	FULLTEXT

• 全文索引使用的是比较少的

在InnoDB存储引擎中, 根据索引的存储形式, 又可以分为以下两种:

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放到了一块, 索引结构的叶子结点保存了行数据	必须有, 而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储, 索引结构的叶子结点关联的是对应的主键	可以存在多个

• 注意: 一张表中聚集索引是必须有的, 要不然表中的数据如何存放
• 那么二级索引该如何判断? —> 不是聚集索引的其他索引就是二级索引
• 二级索引也可以称之为辅助索引

聚集索引的选取规则(因为必须要有, 那么就要有选取规则):

1. 如果存在主键, 主键索引就是聚集索引
2. 如果不存在主键, 将使用第一个非空(not null)且唯一(UNIQUE)索引作为聚集索引
3. 如果表没有主键, 或没有合适的唯一索引, 则InnoDB会自动生成一个rowid作为隐藏的聚集索引

如果我们通过一个二级索引列查询, 最终要查询整行记录, 就要进行一个回表查询:

问题思考: 以下SQL语句, 哪个执行效率高, 为什么?

select * from user where id = 10;

select * from user where name = "Arm";

• 备注 : id为主键, name字段创建的有索引

解答 : 第一条语句执行的效率高, 第二条比较慢, 因为第二条sql需要进行回表查询

问题思考: InnoDB主键索引的B+tree高度为多高呐?

解答 : 假设一行数据大小为1k, 一页中就可以存储16行这样的数据. InnoDB的指针占用6个字节的空间, 主键即使为bigint, 占用字节数目为8(如果是int, 占用字节数为4)

n * 8 + (n + 1) * 6 = 16 * 1024, 算出n约为1170

• 计算的n就是一页中可以有多少个关键字, 那么关键字个数 + 1就是子节点个数

那么如果高度为2 : 1171 * 16 = 18736

• 高度为2, 那么叶子结点的个数一共是1171个, 一个叶子结点是一页, 那么一页可以有16条记录, 那么一共有18736条记录

如果高度为3 : 1171 * 1171 * 16 = 21939856

• 也就是多了一行, 多了的这一行都是非叶子结点, 也就是多乘以一个1171
• 我们可以看到 : 即使是由两千万条记录B+树的高度也仅仅是3

索引语法 :

创建索引:

CREATE [UNIQUE | FULLTEXT] INDEX index_name on table_name(index_col_name, ...);

• 如果添加了UNIQUE关键字就是唯一索引
• 如果添加了FULLTEXT就是全文索引
• 如果什么都不添加就是常规索引
• 主键索引是默认自动创建的, 不用我们创建

查看索引:

SHOW INDEX FROM table_name;	

删除索引:

DROP INDEX index_name ON table_name;

性能分析:

性能分析之查看执行频次:

索引可以大大提高搜索的效率, 会降低更新表(插入, 修改, 删除)的效率, 所以我们可以先进行执行频次的查看, 判断是不是执行的查询操作比较多, 如果是查询操作比较多, 那么我们就可以添加索引, 如果是更新表操作比较多, 那么我们就没有添加索引的必要

SQL执行频率 :

MySQL客户端连接成功后, 通过show [session | global] status命令可以提供服务器状态信息, 通过如下指令, 可以查看当前数据库INSERT, UPDATE, DELETE, SELECT的访问频次

SHOW GLOBAL STATUS LIKE 'Com_______(这里一共是七个下划线)'

• 这里其实就是一个模糊匹配, 一个下划线就是匹配一个任意的字符, 当然我们也是可以使用%代替, %匹配任意数目的字符

性能分析之慢日志查询:

慢查询日志记录了所有执行时间超过指定参数(long_query_time, 单位 : 秒, 默认10秒)的所有SQL语句的日志

MySQL的慢查询日志默认没有开启, 需要在MySQL的配置文件(/etc/my.cnf) [linux]中配置如下信息:

• 开启MySQL慢日志查询开关

  slow_query_log=1
  

• 设置慢日志的时间为2秒, SQL语句执行时间超过2秒, 就会被视为慢查询, 记录到慢查询日志当中

  long_query_time=2
  

慢日志文件保存为/var/lib/mysql/localhost-slow.log

其实我们可以发现, 这里慢查询日志对于慢查询的评判标准是时间, 是超过了指定的时间的查询就被视为了慢查询, 但是其实执行时间并不是唯一标准, 对于规模不同的情况下如果我们还是使用时间来评判其实是不够准确的

性能分析之profile详情:

执行一系列的业务SQL的操作, 然后通过如下指令查看指令的执行耗时

• 查看每一条SQL的耗时情况(以及query_id)

show profiles;

• 查看指定的query_id的SQL语句各个阶段的耗时情况

show profile for query query_id;

• 查看指定query_id的SQL语句CPU的使用情况

性能分析之explain执行计划:

explain执行计划各个字段的含义:

• id :

  select 查询的序列号, 表示查询中执行select子句(或者是操作表)的顺序(id相同, 执行顺序从上到下, id不同, 值越大, 越先执行)

• select_type:

  表示select的类型, 常见的取值有SIMPLE(简单表, 即不使用表连接或者是子查询), PRIMARY(主查询, 即外层的查询), UNION(UNION中第二个或者后面的查询语句), SUBQUERY(select/where之后包含了子查询)等

• type:

  表示连接类型, 性能好到差的连接类型为NULL, system, const, eqref, ref, range, index, all

  • NULL一般是查询一个字面量的时候才会出现, 也就是不需要查表
    • eg: select ‘A’;]
  • 访问系统表的时候才会出现system
  • 访问主键或者唯一索引访问一般会出现const
  • 一般通过非唯一性的索引访问的时候会出现ref
  • index表示是使用了索引, 但是也是会扫描整个索引树
  • all表示全表扫描, 没有使用到索引的时候会出现

  我们在业务中尽量往前优化即可

• possible_key:

  显示可能使用在这张表上的索引, 一个或者多个 (如果有多个就会显示多个)

• key:

  实际使用到的索引, 如果为NULL, 则表示没有使用到索引

• key_len

  表示索引中使用的字节数, 该值为索引字段的最大可能长度, 并非实际使用长度, 在不损失精度的情况之下, 长度越短越好

• rows

  MySQL认为必须要执行查询的行数, 在innoDB引擎的表中, 是一个预估值, 可能并不是完全准确的

• filtered

  表示返回结果的行数占需要读取行数的百分比,filtered值越大越好

• Extra

  额外信息, 前面的字段中没有展示的信息将会在extra字段中展示出来

这几个字段中我们最需要关注的有 : type, possible_key, key, key_len, Extra

索引使用:

最左前缀法则(联合索引):

如果索引了多列(联合索引), 要遵守最左前缀法则, 最左前缀法则指的是查询从索引的最左列开始, 并且不跳过索引中的列. 如果跳过了某一列, 索引将会失效(后面的字段索引失效)

• 跳过的某个列之后的索引失效

范围查询(联合索引):

联合索引中, 出现范围查询(>, <), 范围查询右侧的索引列将会失效

• eg: 注意: 我们创建了联合索引(以profession, age, status三个字段创建的(上述顺序))

  select * from tb_user where profession = '软件工程' and age > 30 and status = '0';
  
  

  • 这个时候age > 30是范围查询, 这个时候status将会失效, 但是注意: age并不失效, 所以我们是使用的联合索引的profession, age部分完成的查询

• 但是注意: >=和<=并不在范围查询的范围内, 所以我们可以将>和<在业务中尽量的替换为>=,<=, 由于对应列可能要创建联合索引进行查询的情况之下, 这样就不会导致索引失效问题了

  select * from tb_user where profession = '软件工程' and age >= 31 and status = '0';
  
  

索引失效情况:

1. 索引列运算:

不要在索引列上进行运算操作, 索引将会失效

select * from tb_user where substring(phone,10,2);

• phone字段是一个添加了索引的字段, 此时表示从phone第10位开始截取两位, 此时堆phone字段做了运算, 那么此时索引就会失效

2. 字符串不加引号

字符串类型字段使用时, 不加引号, 索引将会失效

3. 模糊查询(头部模糊)

如果仅仅是尾部模糊匹配, 索引不会失效, 如果是头部模糊匹配, 索引失效

• 上述第一个是尾部模糊, 第二个是头部模糊, 第三个是头部模糊 + 尾部模糊

4. or连接的条件

用or分割开的条件, 如果or前面的条件中的列有索引, 而后面的列中没有索引, 那么涉及的索引都不会被用到

• 注意: 我们之前将最左连续法则的时候是针对and连接说的, or连接的并没有最左前缀法则

5. 数据分布影响

如果MySQL评估使用索引比全表更慢, 就不会使用索引

• 如果我们通过二级索引列作为判断条件来查找数据, 而此时查找的是表中的全部数据, 此时就会走全表查询, 因为如果走二级索引, 那么还要执行回表查询, 效率还不如直接全表扫描
• 也就是如果要查询的是表中少数数据, mysql就直接走索引, 如果要查询的是表中的多数数据, Mysql就会评估,看是走索引好还是走全表扫描比较好, 会进行一个评估

SQL提示:

SQL提示是优化数据库的一个重要手段, 简单来说, 就是在SQL语句中加入一些人为的提示来达到优化操作的目的

• use index(索引) : 表示建议使用该索引
• ignore index(索引) : 表示忽略该索引
• force index(索引) : 表示强制使用该索引