MySQL索引调优和SQL调优，b站自整理笔记

为什么写笔记

最近找工作，怎么是被问道sql调优，但是自己只看过几个视频，回答的不清楚，支支吾吾，所以感觉要记录一下，后续面试也用的上，从上大学开始，就没认真写过笔记，看过技术，可能也是生活所迫，再不换工作，就废了；

本文书写较口语化，加自己理解，有错误，请指正

什么是索引

在关系数据库中，索引是一种数据结构，他将数据提前按照一定的规则进行排序和组织，能够帮助快速定位到数据记录的数据

索引的种类

mysql索引在，存储引擎层实现，不同的存储引擎，有不同多的索引类型

• 按数据结构：B+tree，Hash，Full-text
• 物理存储：聚集、非聚集；
• 字段特性：主键（primary key），唯一索引（unique），普通（index），全文索引（fulltext）
• 字段个数： 单列，复合

优化

基础知识

 聚集，非聚集：表索引和数据是否分开

二级索引：主键索引以外都称二级索引，二级索引都是非聚集

索引覆盖：创建的索引字段，正好是select 的字段，不用回表

索引下推：5.6以后，回表不一次次回表，而是最后统一回表

单列索引：一个字段

Alter TABLE test.user ADD INDEX('A','B','C')

复合索引：多个字段，排序按照，字段顺序，先排序A，再B，C

最左前缀原则  A，AB，ABC

优化实践---索引优化

全值匹配，查询条件包含索引的所有列

最左前缀原则

因为排序是顺序排的，name有序，但是不表示age会有序，

不在索引列上做任何操作

范围查询不受影响，like '%%',>=,<=

范围查询后，全失效

force index(索引名称) 强制使用索引

尽量使用覆盖索引(只访问索引的查询(索引列包含查询列))，减少 select*语句
覆盖索引不写星

 不等空值还有or，索引失效要少用

Like百分写最右

 

 字符串不加单引号索引失效

varchar 记得单引号，不加会涉及类型转换

范围查询优化

大范围，拆成小范围

 trace跟踪工具使用

set session optimizer_trace="enabled=on",end_marks_in_json=on; --开启trace
select * from employees where age = 18 and name = '张三';
select * from information_schema.OPTIMIZER_TRACE;

 优化实践---SQL语句优化

避免select * 

小表驱动大表

数据小的，索引完善的表做主表对大表筛选数据

连接查询代替子查询（具体场景具体分析）

提升group by的效率（group by 字段加索引）

 批量操作

动态语句，尽量控制500以内

 使用limit

限制返回数据条数 分页页数大也会变慢

可以子查询，查出索引列，再通过索引列查需要数据

数据量大，in的效率不高，可以改用表连接代替in

 用union all 代替union

join表不宜过多 

可以表设计冗余字段去除不用的关联表

不利于表维护，分库分表时维护难度加大

总结

减少数据扫描

返回更少数据

减少交互次数

减少服务器CPU及内存开销

面试题

没有主键索引还会有B+数吗

 B和B+的区别，为什么选B+

1.非叶子节点存更多键值，树的阶数大，查数据进行磁盘I/O次数少，效率高

2.数据都有序存在叶子节点，范围查找，排列查找，分组查找更简单

3.b+数据页，数据记录都用双向链表，升序降序更方便

 单列索引联合索引分别什么场景创建，优势是什么

 索引的优缺点，什么时候该用和不该用