MySQL索引原理笔记

注意：本博客为个人学习记录，不保证正确性，仅供参考指正

MySQL索引原理以及SQL优化

索引与约束

• 索引是什么：

  • 一种有序的存储结构

  • 按照单个或者多个列的值进行排序

• 索引的目的

  • 提升搜索效率

• 索引分类

  • 数据结构：

    • B+树索引；
    • 自适应hash索引；
    • 全文索引（elasticsearch）

  • 物理存储：

    • 聚集（聚簇）索引，一种B+树的类型
    • 辅助索引（二级索引），会导致回表查询

  • 列属性划分

    • 主键索引：非空唯一索引

    • 唯一索引：允许出现一个null

    • 普通索引

    • 前缀索引：在长字符串中只比较前几个字符

  • 列的个数划分

    • 单列索引
    • 组合索引

• 索引代价

  • 占用空间
  • 维护的代价，增删改（DML）操作变慢

• innodb 中表是索引组织表，每张表有且仅有一个主键；

  1. 如果显示设置 PRIMARY KEY ，则该设置的 key 为该表的主
     键；
  2. 如果没有显示设置，则从非空唯一索引中选择；
     1. 只有一个非空唯一索引，则选择该索引为主键；
     2. 有多个非空唯一索引，则选择声明的第一个为主键；
  3. 没有非空唯一索引，则自动生成一个 6 字节的_rowid作为
     主键；

为什么一定要确定一个主键索引呢？

因为在innodb中数据是存在于聚集索引B+树。

• B+树以及Innodb B+树特征是什么？

  • 多路平衡搜索树：搜索指有序，中序遍历，比较key进行排序

  • Innodb B+树特征是：

    • 多路平衡搜索树，是矮胖的结构，层数低，磁盘IO少
    • 所有叶子节点都在同一层
    • 叶子节点间构成一个双向链表
    • 节点大小是固定的，数据页都是16KB
    • 扇区 512B，一次磁盘IO，8个扇区一次来取，即4KB，通常设置一个16KB，数据如果超过16KB，则至少存储两行
    • 非叶子节点只存储索引信息，叶子节点记录数据信息

• 索引实现：为什么是B+树而不是其他树（如平衡二叉搜索树）

  • 降低磁盘IO
  • 范围查询
  • 跳表也可以范围查询

• 索引使用场景

  • where条件语句
  • group by
  • order by

• 不要使用索引的场景

  • 没有where/group by/ order by
  • 列区分度不高则不需要使用索引
  • 经常修改的列
  • 表的数据量少（全表扫描可能会更快）

• 外键约束：innodb可以用
  父表改动会影响子表

  create table parent (
  	id int not null,
  	primary key(id)
  	) engine=innodb;
  	create table child (
  	id int,
  	parent_id int,
  	foreign key(parent_id) references parent(id)
  	ON DELETE CASCADE ON UPDATE CASCADE
  	) engine=innodb;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

  parent表中删除一行，对应id在child中的相同parent_id的那一行也会删除。

覆盖索引

• 一种数据查询的方式
• 针对是辅助索引
• 直接通过辅助索引B+树就能获取要查询的值，而无需通过回表查询
• 如果查询的内容就是索引值，那么不会进行回表查询
• 在select中尽量只写需要的字段，不要select 。

最左匹配规则

• 针对组合索引
• 从左到右依次匹配，遇到 < >,between就会停止匹配

索引下推（面试重点）

• 减少了回表查询次数，提高查询效率
• 5.6版本后支持
• 没有索引下推，server层向存储引擎层请求数据，在server层根据索引条件判断进行数据过滤
• 有索引下推，可以将索引条件下推到存储引擎中过滤数据，最终由存储引擎把数据汇总再返回给server层。

索引存储

• 数据页 16KB
• 辅助索引在Change Pool：缓存非唯一索引的增删改数据（DML） 面试会问
• 聚集索引Buffer Pool，缓存数据页；降低磁盘IO次数
• 怎么判断某个页是否在缓存中？
  • 自适应hash索引，O(1)，有在就去Buffer Pool取出来，增删改时，修改BufferPool中的内容，标记为脏页，异步刷盘

索引失效

• 左模糊

  explain select * from index_failure_t where name like '谢%';
  1

  

  explain select * from index_failure_t where name like '%谢';
  1

  

• 索引参与运算

  • 对索引使用函数
  • 进行表达式运算
  • 索引进行了隐式转换（本质还是使用函数）
  • 字符串和数字比较时，字符串会转换成数字

• where

  • or非索引
  • in子查询

尽量减少索引，因为一个索引就是一颗B+树了

如何解决sql比较慢的问题

1. 找到慢的sql语句：

   1. SHOW FULL PROCESSLIST

   2. 开启慢查询日志

   SET GLOBAL slow_query_log = ON; -- on 开启 off关闭
   SET GLOBAL long_query_time = 4; -- 单位秒；默认10s；此时设置为4s
   1
   2

2. 分析sql语句

   1. 索引
      1. where
      2. group by
      3. order by
   2. SQL语句
   3. in优化成联合查询
   4. 减少联合查询。。？

工作中不要用age字段，要存储生日。