注意:本博客为个人学习记录,不保证正确性,仅供参考指正
MySQL索引原理以及SQL优化
索引与约束
-
索引是什么:
-
一种有序的存储结构
-
按照单个或者多个列的值进行排序
-
-
索引的目的
- 提升搜索效率
-
索引分类
-
数据结构:
- B+树索引;
- 自适应hash索引;
- 全文索引(elasticsearch)
-
物理存储:
- 聚集(聚簇)索引,一种B+树的类型
- 辅助索引(二级索引),会导致回表查询
-
列属性划分
-
主键索引:非空唯一索引
-
唯一索引:允许出现一个null
-
普通索引
-
前缀索引:在长字符串中只比较前几个字符
-
-
列的个数划分
- 单列索引
- 组合索引
-
-
索引代价
- 占用空间
- 维护的代价,增删改(DML)操作变慢
-
innodb 中表是索引组织表,每张表有且仅有一个主键;
- 如果显示设置
PRIMARY KEY
,则该设置的 key 为该表的主
键; - 如果没有显示设置,则从非空唯一索引中选择;
- 只有一个非空唯一索引,则选择该索引为主键;
- 有多个非空唯一索引,则选择声明的第一个为主键;
- 没有非空唯一索引,则自动生成一个 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;
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 '谢%';
explain select * from index_failure_t where name like '%谢';
-
索引参与运算
- 对索引使用函数
- 进行表达式运算
- 索引进行了隐式转换(本质还是使用函数)
- 字符串和数字比较时,字符串会转换成数字
-
where
- or非索引
- in子查询
尽量减少索引,因为一个索引就是一颗B+树了
如何解决sql比较慢的问题
-
找到慢的sql语句:
-
SHOW FULL PROCESSLIST
-
开启慢查询日志
SET GLOBAL slow_query_log = ON; -- on 开启 off关闭 SET GLOBAL long_query_time = 4; -- 单位秒;默认10s;此时设置为4s
-
-
分析sql语句
- 索引
- where
- group by
- order by
- SQL语句
- in优化成联合查询
- 减少联合查询。。?
- 索引
工作中不要用age字段,要存储生日。