MySQL高级调优及面试点详解
2024-01-03 18:49:07 0 举报AI智能生成
登录查看完整内容
MySQL高级优化及面试重点知识，精通MySQL看此篇就够了。
作者其他创作
大纲/内容
Case
explain select * from tb_emp where exists (select 1 from tb_dept where tb_dept.id = tb_emp.dept_id);
explain select * from tb_emp where tb_emp.dept_id in (select id from tb_dept);
in 和 exstis
永远小表驱动大表类似嵌套循环 Nested Loop
建表 SQL
1
示例
2
CASE
ORDER BY 语句使用索引最左前列
使用 Where 子句与 Order By 子句条件列组合满足索引左前列
ORDER BY 满足的两种情况， 会使用 Index 方式排序
order by 子句，尽量使用 index 方式排序， 避免使用filesort 方式排序
尽可能在索引列上完成排序操作，准照索引建立的最佳左前缀
mysql 4.1 之前使用的是双路排序， 字面意思就是两次扫描磁盘， 最终得到数据读取行指针和orderby 列， 对他们进行排序， 然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
从磁盘取排序字段，在 buffer 进行排序， 再从磁盘中取其他字段
双路排序
取一批数据，要对磁盘进行了两次扫描，众所周知， I/O 是很耗费时间的，所以在 mysql 4.1 之后，出现了第二种改进的算法，就是单路排序
从磁盘读取查询需要的所有列， 按照 order by 列在 buffer 对他们进行排序， 然后扫描排序后的列表进行输出，它的效率更快一些， 避免了第二次读取数据。并且把随机IO 变成了顺序 iO， 但是它会使用更多的空间，因为他把每一行都保存到了内存中了。
单路排序
由于单路是后出的算法， 整体而言是要好过双路
在 sort_buffer 中， 方法B比方法 A要多占很多空间，因为方法B是把所有的字段都取出，所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并，排完后再取）
本想的是节省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。
但是单路也是有问题的
结论引出的问题
如果不再索引列上， filesort 有两种算法：mysql 就要启动双路排序 和 单路排序
增大 sort_buffer_size 参数的设置
增大 max_length_for_sort_data 参数的设置
提到 order by 的速度
1.1 当 Query 的字段大小总和小于 max_lenght_for_sort_data 而且排序字段不是 text|blob 类型时，会用改进后的算法--单路排序，否则采用老算法--多路排序
1. Order by 时 select * 是一个大忌只 Query 需要的字段，这点非常重要。在这里的影响是：
2. 尝试提高 sort_buffer_size不管用那种算法， 提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
3. 尝试提高 max_length_for_sort_data提高这个参数，会增加改进算法的效率。但是如果设的太高，数据总容量超出 sort_buffer_size的概率将增大，明显现象是高的磁盘 I/O 活动和低的处理器使用率
Why
优化策略
为排序使用索引
* MySQL 两种排序方式：文件排序和扫描有序索引排序* MySQL 能为排序和查询使用相同的索引
小总结
order by 关键字优化
group by 实质是先排序后分组， 准照索引建的最佳左前缀
当无法使用索引列，增大 max_length_for_sort_data 参数的设置 + 增大 sort_buffer_size 参数的设置
where 高于 having ， 能写在 where 中的限定就不要写在 having 中
group by 关键字优化
查询优化
MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阈值的语句，具体指的 运行时间超过 long_query_time 值的 SQL， 则被记录到慢查询日志中
具体值运行时间超过 long_query_time 的SQL， 则会别记录到查询日志中。 long_query_time 的默认时间为10， 是指运行10秒以上的语句
由它来查看哪些 SQL 超出了我们的最大忍耐时间值， 比如一条 SQL 执行超过了5 秒， 我们就算慢 SQL，希望能收集超过 5秒的 SQL， 结合之前的 explain 进行全面分析
是什么
默认情况下，MySQL 数据库没有开启慢查询日志，需要我们来手动设置这个参数。当让，如果不是调优需要的话，一般不建议启动该参数， 因为开启慢查询日志或多或少会带来一定的性能影响。慢查询日志支持将日志记录写入文件。
说明
查询结果
show variables like '%slow_query_log%';
默认
使用了 set global slow_query_log = 1; 开启了慢查询日志只对当前数据库生效。如果 mysql 重启后会失效
执行后的结果
set global slow_query_log = 1;
开启
查看是否开启以及如何开启
这个是由参数 long_query_time 控制，默认情况下 long_query_time 的值为 10秒
执行结果
命令 show variables like '%long_query_time%';
可以使用命令修改，也可以在 my.cnf 命令里修改
假如运行时间正好等于 long_query_time 的情况，并不会被记录下来。也就是说，在 mysql 源码里是font color=\"#c41230\
那么慢查询开启了慢查询日志后， 怎么样的 SQL 才会记录到慢查询日志中呢？
show variables like '%long_query_time%';
查看当前多少秒算慢
set global long_query_time = 1;
设置慢的阈值时间
需要重新链接或新开一个会话才能看到修改值。show variables like '%long_query_time%';
show global variables like '%long_query_time%';
为什么设置后看不出变化
模拟慢 SQL ： select sleep(4);
记录慢 SQL 并后续分析
show global status like '%Slow_queries%'
查询当前系统中有多少慢查询记录
怎么玩
查看帮助信息
s: 是表示按照何种方式排序；
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al: 平均锁定时间
ar: 平均返回记录数
at: 平均查询时间
t: 即为返回前面多少条的数据
g: 后面搭配一个正则匹配模式，大小写不敏感
查看 mysqldumpslow 的帮助信息
得到返回记录集最多的 10 个SQLmysqldumpslow -s r -t 10 /var/bin/mysql/xx-slow.log
得到访问次数最多的 10 个SQLmysqldumpslow -s c -t 10 /var/bin/mysql/xx-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句mysqldumpslow -s c -t 10 -g \"left join\" /var/bin/mysql/xx-slow.log
另外建议在使用这些命令 结合 ｜ 和 more 使用，否则有可能出现爆屏现象mysqldumpslow -s r -t 10 /var/bin/mysql/xx-slow.log | more
工作常用参考
日志分析工具 mysqldumpslow
慢日志查询
1. 建表
创建函数， 假如报错：this function has none of DETERMINISTIC ...
show variables like 'log_bin_trust_function_creators'
set global log_bin_trust_function_creators = 1
这样添加参数后，如果mysql 重启，上述参数又会消失， 永久方法：
windows 下 my.ini 加上 log_bin_trust_function_creators = 1
liunx 下 /etc/my.conf 下 my.conf[mysqld] 加上 log_bin_trust_function_creators = 1
2. 设置参数 log_bin_trust_function_creators
随机字符串
随机产生部门编号
3. 创建函数，保证每条数据都不同
dept
emp
4. 创建存储过程
5. 调用存储过程
插入 1000w 数据
批量数据脚本
是什么：是mysql 提供用来分析当前会话中语句执行的资源消耗情况。可以用于 SQL 的调优的测量
官网：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
默认情况下， 参数处于关闭状态，并且保存最近15次的运行结果
show variables like 'profiling'默认关闭，使用前需要开启
或者: show variables like 'profiling%';
1. 是否支持， 看看当前的 mysql 版本是否支持
show variables like 'profiling%';
set profiling=on;
子主题
2. 开启功能默认是关闭， 使用前需要开启
select `id` % 10 from `emp` group by `id` % 10 limit 150000;
select id%20 from emp group by id%20 order by id;
3. 运行 SQL
4. 查看运行结果：show profiles;
记录日志
type
显示全部开销信息
ALL
显示块 IO 相关开销
BLOCK IO
上下文切换相关开销
CONTEXXTSWITCHAES
显示 CPU 相关的开销信息
CPU
显示发生和接收相关的信息
IPC
显示内存相关的信息
MEMORY
显示页面错误相关的开销信息
PAGE FAULTS
SOURCE
显示交换次数相关的开销的信息。
SWAPS
参数备注
converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上面搬了
拷贝数据到临时表
用完再删除
Create tmp table 创建临时表
Copying to tmp table on disk 把内存中的临时表复制到磁盘， 危险！！！！
bocked
6. 日常开发需要注意的事项
分析步骤
Show Profile
在 mysql 的 my.cnf 中设置如下：# 开启general_log=1#记录日志文件的路径general_log_file=/path/logfile#输出格式log_output=FILE
配置启用
set global general_log = 1;
set global log_output='TABLE';
此后， 你所编写的 SQL 语句，都将会记录到 mysql 库的 general_log 表中，可以用如下命令查看
select * from mysql.general_log;
编码启用
永远不要在生产环境启用这个功能
全局查询日志（测试环境使用）
查询截取分析
共享锁（S）：其他session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
排他锁（X）：其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁。
为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（IntentionLocks），这两种意向锁都是表锁：意向共享锁（IS）：事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。意向排他锁（IX）：事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。如果一个事务请求的锁模式与当前的锁兼容， InnoDB 就将请求的锁授予该事务； 反之， 如果两者不兼容，该事务就要等待锁释放。
①InnoDB锁模式
意向锁是 InnoDB 自动加的， 不需用户干预。
对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)：隐式锁定
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。确保最新数据，只允许自己修改，自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。
排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。允许其他事物查询，不能够进行 DML 操作。确保查到的数据是最新的数据，不允许其事务修改数据。
对于普通 SELECT 语句，InnoDB 不会加任何锁，通过语句显式给记录集加共享锁或排他锁：显示锁定
锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。
如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待
性能影响
span style=\
lock in share mode共享锁（S 锁），多个事务可以同时的对相同数据执行 lock in share mode。
for update 和 lock in share mode 的区别：
②InnoDB加锁方法
InnoDB 行锁是通过给索引上的索引项加锁来实现的。只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。
由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。
③InnoDB 行锁实现方式
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，会造成严重的锁等待。
1.防止幻读，以满足相关隔离级别的要求；
2.满足恢复和复制的需要
InnoDB使用间隙锁的目的
④InnoDB的间隙锁
死锁产生：死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。
死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。
外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决
死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。
在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。
MyISAM避免死锁
1.在单个InnoDB表上执行多个并发写入操作时避免死锁，使用SELECT ... FOR UPDATE语句来获取必要的锁；2.在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，当用户在申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁3.如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。4.通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
InnoDB避免死锁
如果出现死锁，可以用 SHOW INNODB STATUS 命令分析死锁产生的原因和改进措施。
⑤死锁
1.尽量使用较低的隔离级别，精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会选择合理的事务大小，小事务发生锁冲突的几率也更小2.给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改>时再请求排他锁，这样容易产生死锁3.不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响不要申请超过实际需要的锁级别5.除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能
一些优化锁性能的建议
乐观锁(Optimistic Lock)：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。
每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
乐观锁、悲观锁
mysql锁
二分查找法
二叉查找树
平衡二叉树
B树
借鉴了
B+树-高度一般在2到4层MySQL的B+树索引不能找到一个给定键值的具体行
【查找过程】：如果要查找数据项29， 那么首先会报磁盘块 1 由磁盘加载到内存，此时发生一次 IO，在内存中用二分查找确定29 在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存， 发生第二次 IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载到磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29， 结束查询，共计三次IO。真实的请款是， 3层B+树可以表示上百万的数据，如果上班玩的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次 IO， 显然成本非常的高
检索原理
B+tree索引
MySQL 索引结构
按照每张表的主键构造一颗B+树；叶子节点存放的是整行数据
InnoDB的主键一定是聚集索引
每张表只能有一个聚集索引
聚集索引
叶子节点并不会放整行数据
先查到主键，再通过聚集索引树找到行数据
一颗高度为3的辅助索引树查询记录需要至少6次逻辑IO
辅助索引
索引列的值必须唯一，但允许有空值
唯一索引的更新不能使用change buffer
唯一索引
联合索引
前缀索引
添加全文索引 alter table 表名 add fulltext(字段);
查询语句 select * from 表名 where match(字段) against('所搜的关键字');
如果内容为单词字母，会更具空格切割创建索引 nice to meet you 通过ee查询不到数据，meet可以
全文索引
索引类型
1. 主键自动建立唯一索引
2.单键/组合索引的选择， who? （高并发下倾向于创建组合索引）
3. 频繁作为查询条件的字段应该创建索引
4.查询中与其他关联的字段，外键关系建立索引
5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
6.查询中统计或分组字段
哪些情况下需要创建索引？
1. 表记录太少
2. 经常增删改的字段
1. 假如一个表由10万记录，由一个字段A只有 T 和 F 两种值，且每个值的分布概率大约为 50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
2. 索引的选择性是指索引列中不同值的数目与表中记录数相比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是 1980/2000=0.99， 一个索引的选择性越接近于1，这个索引的效率就越高
3. 数据重复，且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。如果某个数据列包含多个重复的内容，为它建立索引就没有太大的实际效果。
因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。
4.尽可能的考虑建立联合索引而不是单列索引
5.注意避免冗余索引
哪些情况下不需要创建索引？
索引简介
CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
IO：磁盘 I/O 瓶颈发生在装入数据远大于内存容量的时候
Myql 常见性能瓶颈
Explain + SQL 语句
包含信息
执行计划的包含信息
怎么使用执行计划？
select 查询的序列号，包含一组数字， 表示查询中执行 select 子句或操作表的顺序
运行结果
id 相同，执行顺序由上而下
id 不同，如果是子查询，id 的序号会递增， id 值越大优先级越高， 越先被执行
id 如果相同，可以认为是一组的，从上往下执行；在所有组中，id 值越大，优先级越高，越先执行衍生 = DERIVED
derived_merge是MySQL 5.7引入的，其会试图将Derived Table（派生表，from后面的子查询），视图引用，公用表表达式（Common table expressions）与外层查询进行合并。
MySQL 5.7中不再兼容的实现方式，可以通过调整optimizer_switch来加以规避set optimizer_switch='derived_merge=off';
id 相同不同，同时存在
三种情况
id
简单的 select 查询，查询中不包含子查询或者union
1. simple
查询中若包含任何复杂的子部分，最外层查询则被标记为
2. primary
在 select 或 where 列表中包含子查询
3. subquery
在 from 列表中包含的子查询被标记为 derived (衍生) MySQL 会递归执行这些子查询，把结果放在临时表中。
4. derived
5. union
从 union 表获取结果的 select
6. union result
查询的类型，主要是用于区别普通查询，联合查询，子查询等复杂的查询
select_type
这行数据是关于哪张表的
table
all
index
range
ref
eq_ref
NULL
类型
type 显示的是访问类型， 是较为重要的一个指标，结果值从最好到最坏依次是：
system > const > eq_ref > ref > range > index > ALL
一般来说得保证查询至少达到 range 级别， 最好能达到 ref
访问类型排列
表只有一行记录（等于系统表），这是 const 类型的特列， 平时不会出现，这个也可以忽略不计
sytem
表示通过索引一次就找到了， const 用于比较 primary key 或者 unique 索引。 因为只匹配一行数据，所以很快如将主键置于where 列表中， MySQL 就能将该查询转换为一个常量
const
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
非唯一性索引扫描， 返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独的行，然而，它可能会找到多个符合个条件的行，所以它应该属于查找和扫描的混合体
只检索给定范围内的行，使用一个索引来选择行。key 列显示使用了哪个索引一般就是你在 where 语句中出现了 between、<、>、in 等的查询 这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某个点，而结束于另一个点，不用全表扫描
rang
Full Index Scan ， index 于 ALL的区别 ，index 类型只遍历索引树， 这通常比 ALL 快， 因为索引文件通常比数据文件小。（也就是说虽然 all 和 index 都是读全表，但是index 是从索引中读取的， 而 all 是从硬盘中读取的 ）
Full Table Scan 将遍历全表找到匹配的行
备注：一般来说，得以保证查询至少达到 rang 级别， 最好能达到 ref。
显示查询使用了何种类型，从最好到最差依次是：system>const>eq_ref>range>index>ALL
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
possible_keys
实际使用的索引，如果为NULL，则没有使用索引
示例1
示例2
查询中若使用了覆盖索引，则该索引仅出现在KEY列表中
key
表示索引中使用的字节数，可通过该列计算查询中的使用的索引的长度，在不损失精确性的情况下，长度越短越好
key_len 显示的只是索引字段的最大可能长度， 并非实际使用长度。即 key_len e是更具表定义计算而得，不是通过表内检索出的。
总结：条件越多，付出的代价越大，key_len 的长度也就越大，建议在一定条件的情况下，key_len 越短，效率越高。
key_len
显示索引的哪一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值
查询中与其他表关联的字段，外键关系建立索引。（实际开发中不推荐）
根据表统计信息及索引选用情况， 大致估算出找到所需的记录所需读取的行数
rows
filtered
说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取mysql 中无法利用索引完成的排序叫做 “文件排序”
1. Using filesort
使用了临时表保存中间结果， MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by 。
2. Using temporary
理解方式一：就是 select 的数据列只用从索引中就能取得，不必读取数据行， MySQL 可以利用你索引返回 select 列表的字段， 而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖
理解方式二：索引是高效找到的行的一个方法， 但是一般数据库也能使用索引找到一个列的数据， 因此它不必读取整个行，毕竟索引叶子节点存储了他们索引的数据；当能通过读取索引就可以得到想要的数据， 那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
如果要使用覆盖索引，一定要注意 select 列表汇总只取出需要的列，不可 select *
因为如果将所有字段一起做索引将会导致索引文件过大，查询性能下降。
注意
覆盖索引 （Covering Index）
3. Using index
表明使用了 where 过滤
4. Using where
使用了链接缓存
5. using join buffer
6. impossible where
在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUT（*） 操作不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
7. select tbale optimized away
优化 distinct 操作 在找到第一匹配的元祖后立即停止找相同值的动作。
8. distinct
包含不适合其他列中显示但十分重要的额外信息
Extra
名词解释
Explain
性能分析
建SQL
案例
一表
建表SQL
总结：左链接，索引建立到右表，右链接，索引建立到左表
两表
案例(包含结论)
三表
索引分析
1. 全值匹配
如果索引了多列，需要准守最左前缀法则，指的是查询从索引的最左前列开始并且 不跳过索引中的列。
explain select * from staffs where age = 25 and pos = 'dev';explain select * from staffs where pos = 'dev';
索引失效
explain select * from staffs where name = 'July';explain select * from staffs where name = 'July' and age = 25;explain select * from staffs where name = 'July' and age = 25 and pos = 'dev';
ICP（index condition pushdown）是MySQL利用索引（二级索引）元组和筛字段在索引中的WHERE条件从表中提取数据记录的一种优化操作。ICP的思想是：存储引擎在访问索引的时候检查筛选字段在索引中的WHERE条件（pushed index condition，推送的索引条件），如果索引元组中的数据不满足推送的索引条件，那么就过滤掉该条数据记录。ICP（优化器）尽可能的把index condition的处理从Server层下推到Storage Engine层。Storage Engine使用索引过过滤不相关的数据，仅返回符合Index Condition条件的数据给Server层。也是说数据过滤尽可能在Storage Engine层进行，而不是返回所有数据给Server层，然后后再根据WHERE条件进行过滤。
图示1
图示2
官方解释：https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html
explain select * from staffs where name = 'July' and pos = 'dev1';
索引正常
2. 最佳左前缀法则
3. 不在索引列上做任何操作 （计算、函数、（自动 or 手动）类型转换）， 会导致索引失效而转向全表扫描
在 5.6 +， ICP特征可以使用到 Using index condition
4. 存储引擎不能使用索引中范围条件右边的列
5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少 select *
6. mysql 在适应不等于 （!= 或者 <>）的时候无法使用索引会导致全表扫描
完整 SQL（含优化结论）
覆盖索引
问题：解决 like '% 字符串 %' 索引不被使用的方法 ？？
示例：
8. like 以通配符开头 （'%abc ...'）mysql 索引失效会变成全表扫描的操作
9. 字符串不加单引号索引失效
总结
总结2
11. 小总结
案例（索引失效）
SQL 题目
解题和分析
定值、范围还是排序，一般order by是给一个范围
group by 基本上都是需要排序的， 会有临时表产生
面试题讲解
索引失效（应该避免）
索引优化
1. 观察，至少一天， 看看生产慢SQL的情况
2. 开启慢查询日志， 设置阈值，比如超过5秒钟就是是慢SQL ， 并将他们抓取出来。
3. explain + 慢SQL分析
4. show profile
5. 运维经理 + DBA，进行 SQL数据库服务器的参数调优。
分析过程
1. 慢查询开启并捕获
2. explain + 慢 SQL 分析
3. show profile 查询SQL服务器里面的执行细节和生命周期情况
4. SQL 数据库服务器的参数调优
索引优化分析
左子树的键值小于根的键值，右子树的键值大于根的键值。为什么不用二叉树，当索引是自增整数的时候，退化成链表，就会生成很深的树，遍历查找数据效率很慢。mysql很早以前版本索引用的红黑树。为什么不用红黑树（二叉平衡树），因为层级比较多，检索数据慢。
二叉树
1> 每个节点都存储key和data。任何关键字出现且只出现在一个节点中2> 叶子节点指针为null。3> 搜索有可能在非叶子节点结束，在关键字全集内做一次查找，性能逼近二分查找算法。
B tree
叶子结点中包含了全部关键字的信息，非叶子节点只存储键值信息，及指向含有这些关键字记录的指针。2> 为所有叶子节点增加了一个链指针，且叶子结点本身依关键字的大小自小而大的顺序链接。3> 每个节点中的key个数越多，那么树的高度越小，需要 I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。非叶子结点直接在内存里面折半查找，只有叶子节点才会进行一次磁盘IO。
B+ tree
索引都有哪些数据结构？
数据和索引设计到一个文件中。叶节点包含了所有数据记录。
优点：查询速度非常的快， B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。
缺点：依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。更新代价大 ： 索引列的数据被修改时，对应的索引也将会被修改， 而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的。
什么是聚集索引？都有哪些优缺点？
数据和索引设计到不同文件中。
优点：更新代价比聚集索引要小 。因为非聚集索引的叶子节点是不存放数据的。
缺点：跟聚集索引一样，非聚集索引也依赖于有序的数据，可能会二次查询(回表) 。当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。
什么是非聚集索引？都有哪些优缺点？
聚集索引检索数据会比非聚集索引快。
原因：聚集索引只要找到索引就能一次性把索引所在行的数据一次性加载出来，因为之前已经加载到内存中了，而非聚集索引需要先在索引文件（MYI）中把磁盘文件地址找到，然后再去数据文件（MYD）中再做一次磁盘io，所以需要两次磁盘io，而聚集索引只需要一次磁盘io，所以聚集索引会快。
聚集索引和非聚集索引哪个检索数据快？为什么？
需要扫描两遍索引树，先通过普通索引定位到主键值，再通过聚集索引定位到行记录。
什么是回表？
对索引的key进行一个hash运算，就可以定位出数据存储的位置。仅能满足“=” 或者 “IN”，不支持范围查找（工作中很少用hash索引的原因）。会发生hash碰撞，但概率很低。
什么是hash索引？
where col3 = “tom”，只要把tom的hash值算出来，然后定位到数据存储的位置，逐个在hash链表中进行比对，找到和Tom相同的元素，拿出他的磁盘文件地址，去磁盘里面定位这行的所有元素。
hash索引如何定位索引数据的？
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就称之为“覆盖索引”。
在 InnoDB 存储引擎中，如果不是主键索引（叶子节点存储的是主键+列值），最终还是要“回表”。也就是要通过主键再查找一次。这样就会比较慢，覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！（即需要查询的字段正好是索引的字段，而无需回表查询。）
什么是覆盖索引？
一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段。如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
什么是主键索引(Primary Key)？
二级索引又称为辅助索引，属于非聚集索引。是因为二级索引的叶子节点存储的数据是主键。通过二级索引，可以定位主键的位置。唯一索引，普通索引，前缀索引等索引属于二级索引。
什么是二级索引(辅助索引)？
（1）为什么必须建主键？innodb整个表的结构，他的设计就是必须有一个b+tree来组织整张表的数据。如果设置了主键，主键自带索引，他就会默认用主键来组织整张表的数据，如果没有建索引，他会帮我们找一个主键，他会在整张表逐列去找，找到一列所有数据都不重复，就是那种可以添加唯一索引的列，他找到这个列之后，会用这列数据来建一个b+tree的结构来组织整张表的所有数据；如果找不到这样不重复的列，mysql会维护一个隐藏列，类似rowid，他会用这个隐藏列来组织维护整张表的所有数据。
（2）为什么用整型？和uuid对比，找索引元素的时候，会有很多次元素大小的比对，对于比较大小肯定是整型比uuid那种字符串比较要快，从存储空间来看，整型也比uuid占用的少。
（3）为什么要用自增？自增的话，永远是往后面插入，如果不是自增，插入的时候会插入到中间，插入到中间的话，如果节点满了，树就会进行分裂，分裂后还会做一下平衡。这样效率就比较低，所以自增往后面插入效率会高。
为什么建议innodb表必须建主键，并且推荐使用整型的自增主键？
（1）B-tree：叶子节点没有维护双向指针，没有指针就没有办法更好支持范围查找。（2）B+tree：非叶子结点只放了索引元素，没有放数据，整个数据都放到叶子节点，这样可以降低树的高度，查找效率会更高。
B+tree 与 B-tree区别有哪些？
（1）InnoDB支持事务，MyISAM不支持（2）InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁（3）InnoDB支持外键，而MyISAM不支持
使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
（4）InnoDB是聚集索引
也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
（4）MyISAM是非聚集索引
（5）InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，
（6）Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了。
（7）MyISAM表格可以被压缩后进行查询操作。
（8）InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有。
（9）InnoDB存储文件有frm、ibd，而Myisam是frm、MYD、MYIInnoDB：frm是表定义文件，ibd是数据文件MyISAM：frm是表定义文件，myd是数据文件，myi是索引文件
InnoDB与MyISAM有什么区别？
面试常见题
MySQL调优(MySQL 5.7)
0 条评论
回复 删除
下一页
