MySQL高级调优及面试点详解·优
2024-01-03 18:47:30 0 举报AI智能生成
登录查看完整内容
"MySQL高级调优及面试点详解·优"是一份关于MySQL数据库性能优化和面试技巧的专业资料。它涵盖了MySQL的高级调优技术，以及面试中可能遇到的问题和解答策略。资料详细解释了各种优化技巧，如查询优化、索引优化、锁优化等，以及面试中常见的问题，如MySQL架构、数据类型、存储引擎等。通过对这些内容的学习和理解，读者可以提高MySQL技能，为面试做好充分准备。
作者其他创作
大纲/内容
CPU的选择1. OLTP业务需求：联机事务处理（电商，金融支付，网络游戏等场景），面临MySQL高并发低延迟的挑战选择更多核心的CPU比选择主频更高的CPU效果更显著。2. OLAP业务需求：联机分析处理（处理复杂分析操作的系统，报表计算，复杂SQL等场景），SQL复杂度高，处理时间长，处理大量数据的情况下选择主频更高的CPU比更多核心的CPU更合适。3. 成本相同条件下的建议：建议为生产环境选择更多核心的CPU，可以更有效的处理更多客户端的SQL请求，还能为MySQL后台任务提供更多的支持，比如InnoDB刷脏页，UndoLog等，更有效的维持系统的稳定性和可靠性。
CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
存储介质的选择：1. 机械硬盘：机械寻址的方式2. SSD：利用闪存技术显著提高IO读写速度，随机IO效果卓越，生产环境建议使用此磁盘SSD类型的选择：1. SATA2. PCI-E 性能更为突出，速度是SATA的2~3倍平衡性能和成本：有时主库选择SSD硬盘，在核心业务的从库或者备份机上选择机械硬盘，但是可能会导致主从延迟
IO：磁盘 I/O 瓶颈发生在装入数据远大于内存容量的时候
尽可能的覆盖热数据和索引
内存资源
独立磁盘冗余队列：把多个较小的磁盘组成一个较大的磁盘并提供数据冗余技术一般建议使用RAID10
RAID
硬件相关优化
CFQ：为每一个进程提供公平的IO服务，为每一个进程维护一个IO队列，并在这些队列之间进行轮转。强调公平性。
Deadline：基于截止时间，为每一个IO请求设置截止时间以便在截止时间前完成，关注IO请求的实时性。MySQL服务器推荐此算法
NOOP：先进先出调度算法。低延迟的场景。建议使用
IO调度算法
在操作系统中每一个打开的文件，网络连接或者其它类型的IO资源都会分配一个唯一的标识符，这个标识符就是文件句柄
ulimit
file-max（系统所有进程总共可以打开的文件数量）
文件句柄数
用来存放不活跃内存页的一块磁盘，当内存不够的时候系统会将一些不活跃的数据和代码页移到swap中
控制swap使用的偏好
取值范围0~100：0：系统会尽量避免使用swap空间，更倾向于使用物理内存100：系统更倾向将不活跃的页迁移到swap空间进而释放更多的内存对于MySQL服务器需要降低使用swap的概率，尽量不要设置为0，可能会引起OOM
swappiness设置建议
swap（交换空间使用控制）
dirty_ratio设置建议：对于MySQL所在服务器可以设置为5，设置过高系统崩溃会丢失更多的数据
系统内存中脏页的最大百分比控制（脏页：已经修改但还未写到磁盘的页面）
操作系统参数优化
Myql 常见性能瓶颈
插入数据花费的时间分布
查看执行的命令花费时间：time + 命令例如：time mysql -utest_user3 -p'xxxxxx' -h127.0.0.1 martin < t1.sql
介绍
导出一条SQL包含多行数据的数据文件（mysqldump默认此方式）mysqldump -utest_user3 -p'xxxxxx' -h127.0.0.1 --set-gtid-purged=off --single-transaction --skip-add-locks martin t1 > t1.sql
导出一条SQL只包含一行数据的数据文件mysqldump -utest_user3 -p'xxxxxx' -h127.0.0.1 --set-gtid-purged=off --single-transaction --skip-add-locks --skip-extended-insert martin t1 > t1.sql
大数据导入时建议一条SQL插入多行数据
开启自动提交：show global variables like \"autocommit\";关闭自动提交：在所有insert语句前加set autocommit = 0;在所有insert语句后加commit;如果有大量的insert建议拆分成多个事务提交避免大事务
关闭自动提交
show global variables like \"innodb_flush_log_at_trx_commit\";
show global variables like \"sync_binlog\";
需要导入大量数据时可以将两个参数临时设置为0
参数调整
优化方案
数据导入优化
1. 先改表名alter table t1 rename t1_bak_20231114;2. 观察一段时间，看是否有其它程序找不到这张表而报错3. 再删除表drop table t1_bak_20231114;
删除表
1. 清空前备份2. 避免使用delete。delete不会释放空间，单次delete过多数据可能造成长时间的锁等待。如果binlog的格式为row，delete需要逐行删除，每一行都需要写binlog，会导致binlog暴增3. 推荐使用truncate。不会逐行删除而是直接删除表的数据并且binlog里的记录只有一行
清空表
不归档直接删除部分数据
归档删除部分数据
删除分区和分区数据：（比delete效率高很多且释放空间）alter table t3_log drop partition p2016;
分区表删除部分分区（日志表，监控数据表等）
大表删除数据优化
分页查询优化
MySQL没有采用此算法，即使关联字段没有索引也会采用Block Nested-Loop Join算法
算法大致流程（如果没有强制指定驱动表一般MySQL优化器优先会选择小表作为驱动表，explain决定）1. 从驱动表读取一行数据2. 从第一步的数据中，取出关联字段a，到表t1中查找3. 根据索引取出被驱动表中满足条件的行，跟驱动表中获取到的结果合并，作为结果返回给客户端4. 重复上面3步
MySQL8.0.20之前的版本，关联字段没有索引就会使用此算法；MySQL8.0.20开始，联字段没有索引就会使用hash join算法。
执行流程：1. 将驱动表的数据加载到内存中构建哈希表2. 把被驱动表的数据取出，并通过哈希函数将连接条件的列的映射为哈希值，跟前面的哈希表进行对比3. 一旦跟哈希表里的记录匹配，就查到结果，并返回给客户端
当表很大并且没有存储在缓存时，使用辅助索引上的范围扫描读取行可能会导致对表有很多随机访问；MRR优化思路：查询辅助索引时，对查询结果按照主键进行排序并按照主键排序后的顺序进行顺序查找，从而减少随机访问磁盘次数
控制MRR的参数：show global variables like \"optimizer_switch\";mrr_cost_based=on：表示优化器尝试在使用和不使用mrr之间进行基于成本的选择；off表示一直使用mrr。
MRR
BKA的开启
MySQL5.6出现的算法
关联查询的算法
1. 关联字段添加索引2. 选择小表作为驱动表（驱动表：最开始查询的一张表，提供了一开始我们想要的数据。）驱动表会扫码所有记录，被驱动表会扫码满足条件的记录3. 大型数据集可以采用BKA优化
Join语句的优化
永远小表驱动大表
in 和 exstis的优化
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 有两种算法：双路排序 和 单路排序
set session optimizer_trace=\"enable=on\
开启trace
多个字段排序优化
排序字段添加索引
去掉不必要的返回字段
增大 sort_buffer_size 参数的设置
MySQL8.0.20开始已经弃用
增大 max_length_for_sort_data 参数的设置
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
优化策略
order by 关键字优化
group by字段没索引：group by的方式会扫描整张表并创建临时表使每一个组的所有行在这个临时表中连续排列，再使用这个临时表来查询组内数据或者应用一些聚合函数。group by字段有索引：就会避免使用临时表，但是要求group by字段使用的是同一个索引。
group by 实质是先排序后分组， 准照索引建的最佳左前缀
合理使用索引
避免不必要的列
当无法使用索引列，增大 max_length_for_sort_data 参数的设置 + 增大 sort_buffer_size 参数的设置
where 高于 having ， 能写在 where 中的限定就不要写在 having 中
group by 关键字优化
查询优化
count(a) 不会统计null的行
MySQL5.7.18前后count(*)的区别：MySQL5.7.18开始通过遍历最小的可用二级索引来处理count(*)语句，如果不存在二级索引则扫码聚簇索引；如果索引记录不完全在缓冲池中的话处理count(*)也会比较久。新版本为什么会使用二级索引来处理count(*)：InnoDB二级索引树叶子节点存放的是主键，而主键索引树的叶子节点上存放的是整行数据，因此二级索引树比主键索引树小，基于成本考虑优先选择二级索引。
count(1)和count(*) 统计结果没有差别；explain（性能）一样
优化思路：1. show table status like 't1'; 这是InnoDB的预估值，结果不准确2. 用Redis做计数器数据库新增数据Redis计数器就+1；存在时间差3. 增加计数表在同一个事务内操作
count(*)优化
innodb_buffer_pool_size：控制innodb缓存表和索引数据的内存区域大小，建议设置为机器的60%~80%innodb_flush_log_at_trx_commit：innodb RedoLog刷新的方式sync_binlog：控制累计多少个事务后才将二进制日志刷新到磁盘innodb_file_per_table：开启独立表空间max_connection：MySQL最大的连接数，一般配置为1000long_query_time：慢查询时间的阈值，一般为1秒
参数优化
SQL优化
加锁：flush tables with read lock;
解锁：unlock tables;
全局锁
1. 事务需要更新某张大表的大部分或者全部数据，如果使用行锁事务执行效率低，且可能造成其它事务长时间等待和锁冲突2. 事务涉及到多个表，比较复杂可能引起死锁导致大量事务回滚
表读锁加锁和解锁命令：加锁： lock tables 表名 read；解锁： unlock tables; 加锁后本线程和其他线程可读，本线程写会报错，其它线程写会等待
表写锁加锁和解锁命令：加锁： lock tables 表名 write；解锁： unlock tables;加锁后本线程读写都可执行，其它线程读写都会阻塞
表锁
共享锁（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使用间隙锁的目的
RR(可重复读)隔离级别才有间隙锁
对没有索引的字段进行更新会锁住全表记录
RR隔离级别下的唯一索引当前读不会用到GAP锁
注意事项
show global variables like \"innodb_lock_waut_timeout\"set innodb_lock_waut_timeout=300
锁等等待时间
InnoDB的间隙锁
解决了同一张表上事务和DDL并行执行时可能导致数据不一致的情况
select * from performance_schema.metadata_locks;或者 show processlist;
查看锁信息
MDL读锁和写锁之前相互阻塞
MDL写锁和写锁之前相互阻塞
MDL读锁和读锁之间不会产生阻塞
MDL读锁和写锁的关系
元数据锁MDL
死锁产生：死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。innodb_deadlock_detect 参数控制死锁检测。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决
等查询的时间达到锁等待超时的设定后放弃锁请求。innodb_lock_wait_timeout 锁等待时间参数
InnoDB解锁死锁方式
间隙锁导致的死锁
在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。
MyISAM避免死锁
1. 更新语句的where条件尽量用索引2. 基于primary或unique key更新数据3. 减少范围更新4. 加锁顺序一致5. 将RR隔离级别调整为RC隔离级别
InnoDB降低死锁概率
如果出现死锁，可以用 SHOW INNODB STATUS 命令分析死锁产生的原因和改进措施。
死锁
1. show processlist;2. select * from information_schema.innodb_trx\\G;3. 查看当前所有没有释放的锁：select * from performance_schema.data_locks;4. 查看锁之间等待关系：select * from performance_schema.data_lock_waits\\G;select * from performance_schema.events_statements_current\\G;通过关联语句查询持有阻塞锁和正在请求锁定select esc.sql_text from performance_schema.data_lock_waits dlw join performance_schema.events_statements_current esc on dlw.blocking_thread_id esc.thread_id;select esc.sql_text from performance_schema.data_lock_waits dlw joinperformance_schema.events_statements_current esc on dlw.requesting_thread_id esc.thread_id;5. 死锁：show engine innodb status;6. metadata_locks 表获取元数据锁7. table_handles 表
某个事物向表中写包含自增列数据时，InnoDB会先获取这张表的自增锁其它事务也在写自增数据时就会阻塞
1. simple inserts 简单插入2. bulk inserts 批量插入3. mixed-mode inserts 混合插入
MySQL写入数据类型
show global variables like \"innodb_autoinc_lock_mode\";0 : 传统模式；插入的时候获取一个级锁，插入SQL执行完毕后释放锁。可以确保批量插入时自增主键是连续的1：连续模式；对于简单的插入会用轻量级的互斥锁，对于批量插入还是用传统的自增锁的方式2：交错模式；对于所有的写入语句自增都是使用轻量级互斥锁，性能最好，但是并发插入时自增长的值可能不连续且该模式下binlog的格式必须为row
模式
MySQL自增锁
乐观锁(Optimistic Lock)：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。
每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
乐观锁、悲观锁
mysql锁
atomicity(原子性)：一个事务要么全执行，要么全都不执行；实现方式：通过UndoLog来实现原子性
consistency(一致性)：在事务开始和完成时，数据都必须保持一致状态；实现方式：通过RedoLog和UndoLog来实现一致性
isolation(隔离性)：事务处理过程中的中间状态对外部是不可见的；实现方式：通过锁来实现写和写之间事务的隔离性MVCC来实现读和写的隔离性
durability(持久性)：事务完成之后，它对于数据的修改是永久性的，即使发生了断电也能恢复数据
事务的特性
1. 数据一致性2. 并发控制3. 故障恢复4. 应用程序逻辑的一致性
事务在数据库的作用
DB_ROW_ID（影藏的自增id）
DB_TRX_ID（事务ID，记录最后一次修改这条记录的事务ID）
DB_ROLL_PTR（回滚指针，指向这条记录的上一个版本的UndoLog记录，如果记录更新了那么UndoLog包含在更新之前重建行所需要的信息）
隐藏列(InnoDB包含三个隐藏列)
当多个事务对同一行记录进行更新时，每一个事务的修改会生产一个历史快照，这些快照会被保存在数据库的UndoLog中
UndoLog
当一个事务需要查询这行记录时，可能面临多个版本的选择，这时需要Read view来确定该读取哪一个版本的记录
Read View包含的内容1. trx_ids （生成Read View时，数据库系统当前活跃事务的ID集合）2. low_limit_id （生成Read View时，系统中应该分配下一个事务的ID值）3. up_limit_id （生成Read View时，活跃事务中最小的事务ID）4. creator_trx_id （创建这个Read View的事务ID）
Read View如何判断行数据的哪个版本可用？
Read View
MVCC相关知识点
MVCC整体流程1. 获取事务ID2. 获取read view3. 查询数据4. 比较事务ID5. 符合规则的数据6. 返回数据
原理：InnoDB每行数据都有一个影藏的回滚指针，用于指向该行修改前的一些历史版本，这些历史版本存放在UndoLog中，如果要执行更新操作，会将原记录放入UndoLog中并通过影藏的回滚指针指向UndoLog中的原记录，其它事务此时需要查询时就是查询UndoLog中这行数据的最后一个历史版本。
MVCC给MySQL带来的好处：读库加锁，读写不冲突极大增加了MySQL并发性，保证事务当中隔离特性
MVCC只在RC和RR隔离级别下有
MVCC(多版本并发控制)
1. Read uncommitted（读未提交，简称RU）；解释：所有事务都可以看到其它未提交事务的执行结果。可能出现脏读。2. Read Committed（读已提交，简称RC）；解释：一个事务只能看见已经提交事务所做的改变，某个事务执行期间可能有其他事务提交，所以可能出现幻读。3. Repeatable Read（可重复读，简称RR）；解释：这是MySQL的默认事务隔离级别，它确保同一事务相同的语句多次查询时，会看到同样的数据行。消除了脏读，不可重复读，默认也不会出现幻读。4. Serializable（串行）；解释：这是最高的隔离级别，它通过强制事务排序，使不同事务之间不可能相互冲突，从而解决幻读问题。
四种隔离级别压测对比
RU：会导致脏读，从性能上看，也不会比其它隔离级别好太多，因此生成环境不建议使用。RC：相比RU隔离级别，不会出现脏读；但是会出现幻读，一个事务中的两次执行同样的查询，可能得到不一样的结果。RR：相比RC隔离级别，解决了部分幻读，但是相对于RC，锁的范围可能更大了。Serializable：因为它强制事务串行执行，会在读取的每一行数据上都加锁，因此可能会导致大量的超时和锁争用的问题。生成环境不建议使用。总结：建议在RC和RR两个隔离级别中选一种，如果能接受幻读，需要并发高点，就可以配置成RC，如果不能接受幻读的情况，就设置成RR隔离级别。
如何选择合适的事务隔离级别
1. 更新数据时条件字段没索引RC和RR都会大范围加锁2. RR相对于RC，锁的范围可能更大3. 以唯一索引作为条件的当前读，RR和RC都不会有间隙锁
事务隔离级别和锁的关系
1. 在循环中提交2. 大事务3. 不关注同一个事务里语句顺序4. 不关注不同事务访问资源的顺序5. 不关注事务隔离级别6. 在事务中混合使用存储引擎
不好的事务习惯
1. 循环次数不多的情况，在循环结束后统一提交2. 将大事务拆分成小事务3. 优化事务里的语句顺序4. 关注不同事务访问资源的顺序5. 创建事务之前，关注事务隔离级别6. 不在事务中混合使用存储引擎
好的事务习惯
事务隔离级别
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. 经常增删改的字段
1. 假如一个表由10万记录，由一个字段A只有 T 和 F 两种值，且每个值的分布概率大约为 50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
2. 索引的选择性是指索引列中不同值的数目与表中记录数相比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是 1980/2000=0.99， 一个索引的选择性越接近于1，这个索引的效率就越高
3. 数据重复，且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。如果某个数据列包含多个重复的内容，为它建立索引就没有太大的实际效果。
因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。
4.尽可能的考虑建立联合索引而不是单列索引
5.注意避免冗余索引
哪些情况下不需要创建索引？
索引简介
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
简单查询(不使用关联查询或子查询)
simple
如果包含关联查询或者子查询，则最外层的查询部分标记为primary
primary
联合查询中第二个及后面的查询
union
满足依赖外部的关联查询中第二个及后面的查询
dependent union
联合查询的结果
union result
子查询中的第一个查询
subquery
子查询中的第一个查询，并且依赖外部查询
dependent subquery
用到派生表的查询
derived
被物化的子查询
materialized
一个子查询的结果不能被缓存，必须重新评估外层查询的每一行
uncacheable subquery
关联查询第二个或后面的语句属于不可缓存的子查询
uncacheable union
查询的类型
select_type
涉及到的表
table
将匹配记录所在的分区
partitions
查询对象表只有一行数据，且只能用于MyISAM和Memory引擎的表
system
基于主键或唯一索引查询，最多返回一条结果
const
唯一性索引扫描：表连接时基于主键或非NULL的唯一索引完成扫描
eq_ref
非唯一性索引扫描：基于普通索引的等值查询，或者表间等值连接
ref
全文检索
fulltext
表连接类似是ref，但进行扫描的索引列中可能包含NULL值
ref_or_null
利用多个索引
index_merge
子查询中使用唯一索引
unique_subquery
子查询中使用普通索引
index_subquery
利用索引进行范围查询。一般就是你在 where 语句中出现了 between、<、>、in 等的查询 这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某个点，而结束于另一个点，不用全表扫描
rang
Full Index Scan ，全索引扫描。 index 于 ALL的区别 ，index 类型只遍历索引树， 这通常比 ALL 快， 因为索引文件通常比数据文件小。（也就是说虽然 all 和 index 都是读全表，但是index 是从索引中读取的， 而 all 是从硬盘中读取的 ）
index
Full Table Scan 全表扫描
all
备注：一般来说，得以保证查询至少达到 rang 级别， 最好能达到 ref。
访问类型排列，从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL
type
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
possible_keys
实际使用的索引，如果为NULL，则没有使用索引
示例1
示例2
查询中若使用了覆盖索引，则该索引仅出现在KEY列表中
key
表示索引中使用的字节数，可通过该列计算查询中的使用的索引的长度，在不损失精确性的情况下，长度越短越好
key_len 显示的只是索引字段的最大可能长度， 并非实际使用长度。即 key_len 是更具表定义计算而得，不是通过表内检索出的。
总结：条件越多，付出的代价越大，key_len 的长度也就越大，建议在一定条件的情况下，key_len 越短，效率越高。
key_len
显示索引的哪一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值
预计需要扫描的行数，对InnoDB来说这个值是估值，并不一定准确
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
名词解释
show processlist;
explain for connection 连接ID;
获取正在执行语句的执行计划
explain format=tree + SQL语句cost 预估成本信息；rows预估扫描行数
树状执行计划
会执行SQL，并且会包含执行成本返回行数，执行时间以及循环次数的信息
explain analyze + SQL语句
MySQL8.0执行计划新用法
性能分析 Explain
可以完整用到联合索引的情况
只能部分使用联合索引的情况
可以用到覆盖索引的情况
不能使用联合索引的情况
如果索引了多列，需要准守最左前缀法则，指的是查询从索引的最左前列开始并且 不跳过索引中的列。
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';
索引正常
不符合最佳左前缀法则
不在索引列上做任何操作 （计算、函数、（自动 or 手动）类型转换）， 会导致索引失效而转向全表扫描
在 5.6 +， ICP特征可以使用到 Using index condition
存储引擎不能使用索引中范围条件右边的列
合理使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少 select *
mysql 在适应不等于 （!= 或者 <>）的时候无法使用索引会导致全表扫描注意：范围查询太大不会使用索引查询，小范围会使用索引查询
建表SQL
完整 SQL（含优化结论）
覆盖索引
问题：解决 like '% 字符串 %' 索引不被使用的方法 ？？
示例：
like 以通配符开头 （'%abc ...'）mysql 索引失效会变成全表扫描的操作
字符串不加单引号索引失效
案例（索引失效）
索引失效（应该避免）
索引优化
1. 观察，至少一天， 看看生产慢SQL的情况
2. 开启慢查询日志， 设置阈值，比如超过5秒钟就是是慢SQL ， 并将他们抓取出来。
3. explain + 慢SQL分析
4. show profile
5. 运维经理 + DBA，进行 SQL数据库服务器的参数调优。
分析过程
1. 慢查询开启并捕获
2. explain + 慢 SQL 分析
3. show profile 查询SQL服务器里面的执行细节和生命周期情况
4. SQL 数据库服务器的参数调优
总结
小总结
索引优化分析
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 的慢查询日志是 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%'
查询当前系统中有多少慢查询记录
Case
怎么玩
查看帮助信息
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
慢日志查询
是什么：是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;
记录日志
显示全部开销信息
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;
编码启用
永远不要在生产环境启用这个功能
全局查询日志（测试环境使用）
分析扩展
左子树的键值小于根的键值，右子树的键值大于根的键值。为什么不用二叉树，当索引是自增整数的时候，退化成链表，就会生成很深的树，遍历查找数据效率很慢。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 条评论
回复 删除
下一页
