MySQL高级调优及面试点详解

2024-09-27 16:18:14   2  举报





AI智能生成

"MySQL高级调优及面试点详解·优"是一份关于MySQL数据库性能优化和面试技巧的专业资料。它涵盖了MySQL的高级调优技术，以及面试中可能遇到的问题和解答策略。资料详细解释了各种优化技巧，如查询优化、索引优化、锁优化等，以及面试中常见的问题，如MySQL架构、数据类型、存储引擎等。通过对这些内容的学习和理解，读者可以提高MySQL技能，为面试做好充分准备。

MySQL

Java

MYSQL调优

SQL优化

作者其他创作

大纲/内容

Myql 常见性能瓶颈

硬件相关优化

CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候

CPU的选择 1. OLTP业务需求：联机事务处理（电商，金融支付，网络游戏等场景），面临MySQL高并发低延迟的挑战选择更多核心的CPU比选择主频更高的CPU效果更显著。 2. OLAP业务需求：联机分析处理（处理复杂分析操作的系统，报表计算，复杂SQL等场景），SQL复杂度高，处理时间长，处理大量数据的情况下选择主频更高的CPU比更多核心的CPU更合适。 3. 成本相同条件下的建议：建议为生产环境选择更多核心的CPU，可以更有效的处理更多客户端的SQL请求，还能为MySQL后台任务提供更多的支持，比如InnoDB刷脏页，UndoLog等，更有效的维持系统的稳定性和可靠性。

IO：磁盘 I/O 瓶颈发生在装入数据远大于内存容量的时候

存储介质的选择： 1. 机械硬盘：机械寻址的方式 2. SSD：利用闪存技术显著提高IO读写速度，随机IO效果卓越，生产环境建议使用此磁盘 SSD类型的选择： 1. SATA 2. PCI-E 性能更为突出，速度是SATA的2~3倍 平衡性能和成本：有时主库选择SSD硬盘，在核心业务的从库或者备份机上选择机械硬盘，但是可能会导致主从延迟

内存资源

尽可能的覆盖热数据和索引

RAID

独立磁盘冗余队列：把多个较小的磁盘组成一个较大的磁盘并提供数据冗余技术 一般建议使用RAID10

服务器硬件的性能瓶颈：top, free, iostat 和 vmstat 来查看系统的性能状态

操作系统参数优化

IO调度算法

CFQ：为每一个进程提供公平的IO服务，为每一个进程维护一个IO队列，并在这些队列之间进行轮转。强调公平性。

Deadline：基于截止时间，为每一个IO请求设置截止时间以便在截止时间前完成，关注IO请求的实时性。MySQL服务器推荐此算法

NOOP：先进先出调度算法。低延迟的场景。建议使用

文件句柄数

在操作系统中每一个打开的文件，网络连接或者其它类型的IO资源都会分配一个唯一的标识符，这个标识符就是文件句柄

ulimit

file-max（系统所有进程总共可以打开的文件数量）

swap（交换空间使用控制）

用来存放不活跃内存页的一块磁盘，当内存不够的时候系统会将一些不活跃的数据和代码页移到swap中

控制swap使用的偏好

swappiness设置建议

取值范围0~100： 0：系统会尽量避免使用swap空间，更倾向于使用物理内存 100：系统更倾向将不活跃的页迁移到swap空间进而释放更多的内存 对于MySQL服务器需要降低使用swap的概率，尽量不要设置为0，可能会引起OOM

系统内存中脏页的最大百分比控制 （脏页：已经修改但还未写到磁盘的页面）

dirty_ratio设置建议： 对于MySQL所在服务器可以设置为5，设置过高系统崩溃会丢失更多的数据

SQL优化

数据导入优化

介绍

插入数据花费的时间分布

查看执行的命令花费时间： time + 命令 例如： time mysql -utest_user3 -p'xxxxxx' -h127.0.0.1 martin < t1.sql

优化方案

大数据导入时建议一条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

关闭自动提交

开启自动提交： 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里的记录只有一行

不归档直接删除部分数据

1. 备份要删除部分数据的表 mysqldump -uroot -p martin t2 > t2.sql 或者 create table t2_bak_1114 like t2; insert into t2_bak_1114 select * from t2; 2. 使用命令删除 delete from t2 where b < 50000 limit 1000; delete from t2 where b < 50000 limit 1000; delete from t2 where b < 50000 limit 1000; 可以写个循环脚本直到删除完。 3. 使用pt-archiver工具删除 pt-archiver --source h=192.168.12.161,u=dba,p='xxxxxx',D=martin,t=t2 --where "b<50000" --progress 10000 --limit=1000 --txn-size 10000 --no-safe-auto-increment --statistics --purge

归档删除部分数据

归档删除部分数据： pt-archiver --source h=192.168.12.161,u=dba,p='xxxxxx',D=martin,t=t2 --dest h=192.128.12.162,u=dba,p='xxxxxx',D=archiver_db,t=t2_archiver  --where "b<50000" --progress 10000 --limit=1000 --txn-size 10000 --no-safe-auto-increment --statistics --purge 重建表：（delete删除数据后实际表文件大小是不会有变化的，如果通过delete删除某条记录，InnoDB存储引擎会把这条记录标记为删除，但是磁盘文件的大小并不会缩小，如果之后要在这中间插入一行数据则可以复用这个位置，如果一直没有数据插入就会形成一个空洞，因此delete命令是不能回收空间的。）释放空间可以重建表，但是会锁表，建议在低峰执行。 alter table t2 engine=InnoDB; 或者 optimize table t2；

分区表删除部分分区（日志表，监控数据表等）

删除分区和分区数据：（比delete效率高很多且释放空间） alter table t3_log drop partition p2016;

查询优化

分页查询优化

1. 根据自增且连续主键排序的分页查询（通过explain分析两条SQL的不同之处） 原：select a,b from t1 limit 10000,10; 优化：select a,b from t1 where id > 10000 and id <= 10010; 2. 根据非主键字段排序的分页查询（a有索引） 原：select * from t1 order by a limit 99000,2; 优化：select f.* from t1 f inner join (select id from t1 order by a limit 99000,2) g on f.id=g.id;

Join语句的优化

关联查询的算法

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. 一旦跟哈希表里的记录匹配，就查到结果，并返回给客户端

MySQL5.6出现的算法

MRR

当表很大并且没有存储在缓存时，使用辅助索引上的范围扫描读取行可能会导致对表有很多随机访问； MRR优化思路：查询辅助索引时，对查询结果按照主键进行排序并按照主键排序后的顺序进行顺序查找，从而减少随机访问磁盘次数

控制MRR的参数： show global variables like "optimizer_switch"; mrr_cost_based=on：表示优化器尝试在使用和不使用mrr之间进行基于成本的选择；off表示一直使用mrr。

BKA的开启

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

1. 关联字段添加索引 2. 选择小表作为驱动表（驱动表：最开始查询的一张表，提供了一开始我们想要的数据。） 驱动表会扫码所有记录，被驱动表会扫码满足条件的记录 3. 大型数据集可以采用BKA优化

in 和 exstis的优化

永远小表驱动大表

order by 关键字优化

order by 子句，尽量使用 index 方式排序，避免使用filesort 方式排序

MySQL 支持两种方式的排序， FileSort 和 Index,  Index 效率高 指 MySQL 扫描索引本身完成排序， FlleSort 方式效率低

ORDER BY 满足的两种情况，会使用  Index 方式排序

ORDER BY 语句使用索引最左前列

使用 Where 子句与 Order By 子句条件列组合满足索引左前列

如果不再索引列上 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 操作，反而得不偿失。

开启trace

set session optimizer_trace="enable=on",end_markers_in_json=on; 开启后会显示SQL执行的详细信息；执行SQL语句后查看表信息 select * from information_schema.optimizer_trace\G; memory_available：表示filesort可以使用的内存量，和sort_buffer_size一样 key_size：排序字段所占用的字节 row_size：进行排序的每一行数据的大小 max_rows_per_buffer：每个排序缓冲区可以容纳的最大行数 num_rows_estimate：优化器预估需要排序的行数 num_rows_found：实际参与的排序行数 num_initial_chunks_spilled_to_disk：在排序过程中有多少溢出到磁盘的排序 peak_memory_used：filesort过程中使用的内存最大量 sort_algorithm：排序的具体算法 sort_mode：排序的模式

多个字段排序优化

Key a_b_c(a, b, c)

order by 能使用索引最左前缀 -order by a -order by a,b -order by a,b,c -order by a desc, b desc, cdesc

如果 where 使用索引的最左前缀定义为常量，则 order by 能使用索引 -where a=const order by b,c -where a=const and b=const order by c -where a=const and b > const order by b, c

不能使用索引进行排序 -order by a asc，b desc, c desc   正序和倒序混合使用 -where a > 900 order by b    使用范围查询再排序 -where g = const order by b,c   丢失 a 的索引 -where a = const order by c   丢失 b 的索引 -where a = const order by a, d   d 不是索引的一部分 -where a in (..) order by b,c   对于排序来说，多个相等条件也是范围查询

优化策略

排序字段添加索引

去掉不必要的返回字段

增大 sort_buffer_size 参数的设置

增大 max_length_for_sort_data 参数的设置

MySQL8.0.20开始已经弃用

Why

1. Order by 时 select * 是一个大忌只 Query 需要的字段，这点非常重要。在这里的影响是：

1.1 当 Query 的字段大小总和小于  max_lenght_for_sort_data 而且排序字段不是 text|blob 类型时，会用改进后的算法--单路排序，否则采用老算法--多路排序

1.2 两种算法的数据都有可能超过 sort_buffer 的容量，超出之后，会创建 tmp 文件进合并排序，导致多次 I/O, 但是用单路排序算法的风险会更大一些，所以需要提高 sort_buffer_size

2. 尝试提高 sort_buffer_size 不管用那种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的

3. 尝试提高 max_length_for_sort_data 提高这个参数，会增加改进算法的效率。但是如果设的太高，数据总容量超出 sort_buffer_size的概率将增大， 明显现象是高的磁盘 I/O 活动和低的处理器使用率

group by 关键字优化

group by字段没索引：group by的方式会扫描整张表并创建临时表使每一个组的所有行在这个临时表中连续排列，再使用这个临时表来查询组内数据或者应用一些聚合函数。 group by字段有索引：就会避免使用临时表，但是要求group by字段使用的是同一个索引。

合理使用索引

group by 实质是先排序后分组，准照索引建的最佳左前缀

避免不必要的列

当无法使用索引列，增大 max_length_for_sort_data 参数的设置 + 增大 sort_buffer_size 参数的设置

where 高于 having ，能写在 where 中的限定就不要写在 having 中

count(*)优化

count(a) 不会统计null的行

MyISAM的count(*)：会维护表的总行数到磁盘中,查询快 InnoDB的count(*)：并发事务可能同时读取到不同的行数，所以临时计算。遍历普通索引树去计算总量。

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. 增加计数表 在同一个事务内操作

参数优化

innodb_buffer_pool_size：控制innodb缓存表和索引数据的内存区域大小，建议设置为机器的60%~80% innodb_flush_log_at_trx_commit：innodb RedoLog刷新的方式 sync_binlog：控制累计多少个事务后才将二进制日志刷新到磁盘 innodb_file_per_table：开启独立表空间 max_connection：MySQL最大的连接数，一般配置为1000 long_query_time：慢查询时间的阈值，一般为1秒

mysql锁

全局锁

一般备份时使用,所有表只读

加锁：flush tables with read lock;

解锁：unlock tables;

表锁

1. 事务需要更新某张大表的大部分或者全部数据，如果使用行锁事务执行效率低，且可能造成其它事务长时间等待和锁冲突 2. 事务涉及到多个表，比较复杂可能引起死锁导致大量事务回滚

表读锁加锁和解锁命令：加锁： lock tables 表名 read；解锁： unlock tables; 加锁后本线程和其他线程可读，本线程写会报错，其它线程写会等待

表写锁加锁和解锁命令：加锁： lock tables 表名 write；解锁： unlock tables; 加锁后本线程读写都可执行，其它线程读写都会阻塞

InnoDB锁模式：行锁

共享锁（S）：其他session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。

排他锁（X）：其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（IntentionLocks），这两种意向锁都是表锁： 意向共享锁（IS）：事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。 意向排他锁（IX）：事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。 如果一个事务请求的锁模式与当前的锁兼容， InnoDB 就将请求的锁授予该事务； 反之， 如果两者不兼容，该事务就要等待锁释放。

InnoDB加锁方法

意向锁是 InnoDB 自动加的， 不需用户干预。

对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)：隐式锁定

对于普通 SELECT 语句，InnoDB 不会加任何锁，通过语句显式给记录集加共享锁或排他锁：显示锁定

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 确保最新数据，只允许自己修改，自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。

排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。 允许其他事物查询，不能够进行 DML 操作。确保查到的数据是最新的数据，不允许其事务修改数据。

锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

性能影响

如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待

for update 和 lock in share mode 的区别：

for update 排他锁（X 锁），一旦一个事务获取了这个锁，其他的事务是没法在这些数据上执行 forupdate ；

lock in share mode共享锁（S 锁），多个事务可以同时的对相同数据执行 lock in share mode。

InnoDB 行锁实现方式

InnoDB 行锁是通过给索引上的索引项加锁来实现的。 只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！

只有执行计划真正使用了索引，才能使用行锁： 即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。

由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。

InnoDB的间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，会造成严重的锁等待。

InnoDB使用间隙锁的目的

1.防止幻读，以满足相关隔离级别的要求；

2.满足恢复和复制的需要

注意事项

RR(可重复读)隔离级别才有间隙锁

对没有索引的字段进行更新会锁住全表记录

RR隔离级别下的唯一索引当前读不会用到GAP锁

锁等等待时间

show global variables like "innodb_lock_waut_timeout" set innodb_lock_waut_timeout=300

元数据锁MDL

解决了同一张表上事务和DDL并行执行时可能导致数据不一致的情况

查看锁信息

select * from performance_schema.metadata_locks; 或者 show processlist;

MDL读锁和写锁的关系

MDL读锁和写锁之前相互阻塞

MDL写锁和写锁之前相互阻塞

MDL读锁和读锁之间不会产生阻塞

死锁

死锁产生：死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。

InnoDB解锁死锁方式

InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。innodb_deadlock_detect 参数控制死锁检测。 但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

等查询的时间达到锁等待超时的设定后放弃锁请求。innodb_lock_wait_timeout 锁等待时间参数

间隙锁导致的死锁

MyISAM避免死锁

在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。

InnoDB降低死锁概率

1. 更新语句的where条件尽量用索引 2. 基于primary或unique key更新数据 3. 减少范围更新 4. 加锁顺序一致 5. 将RR隔离级别调整为RC隔离级别

如果出现死锁，可以用 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 join performance_schema.events_statements_current esc on dlw.requesting_thread_id esc.thread_id; 5. 死锁：show engine innodb status; 6. metadata_locks 表获取元数据锁 7. table_handles 表

MySQL自增锁

某个事物向表中写包含自增列数据时，InnoDB会先获取这张表的自增锁 其它事务也在写自增数据时就会阻塞

MySQL写入数据类型

1. simple inserts 简单插入 2. bulk inserts 批量插入 3. mixed-mode inserts 混合插入

模式

show global variables like "innodb_autoinc_lock_mode"; 0 : 传统模式；插入的时候获取一个级锁，插入SQL执行完毕后释放锁。可以确保批量插入时自增主键是连续的 1：连续模式；对于简单的插入会用轻量级的互斥锁，对于批量插入还是用传统的自增锁的方式 2：交错模式；对于所有的写入语句自增都是使用轻量级互斥锁，性能最好，但是并发插入时自增长的值可能不连续且该模式下binlog的格式必须为row

乐观锁、悲观锁

乐观锁(Optimistic Lock)：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。

每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。 比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

MySQL事务

事务的特性

atomicity(原子性)：一个事务要么全执行，要么全都不执行；实现方式：通过UndoLog来实现原子性

consistency(一致性)：在事务开始和完成时，数据都必须保持一致状态；实现方式：通过RedoLog和UndoLog来实现一致性

isolation(隔离性)：事务处理过程中的中间状态对外部是不可见的；实现方式：通过锁来实现写和写之间事务的隔离性MVCC来实现读和写的隔离性

durability(持久性)：事务完成之后，它对于数据的修改是永久性的，即使发生了断电也能恢复数据

事务在数据库的作用

1. 数据一致性 2. 并发控制 3. 故障恢复 4. 应用程序逻辑的一致性

MVCC(多版本并发控制)

MVCC相关知识点

隐藏列(InnoDB包含三个隐藏列)

DB_ROW_ID（影藏的自增id）

DB_TRX_ID（事务ID，记录最后一次修改这条记录的事务ID）

DB_ROLL_PTR（回滚指针，指向这条记录的上一个版本的UndoLog记录， 如果记录更新了那么UndoLog包含在更新之前重建行所需要的信息）

UndoLog

当多个事务对同一行记录进行更新时，每一个事务的修改会生产一个历史快照，这些快照会被保存在数据库的UndoLog中

Read View

当一个事务需要查询这行记录时，可能面临多个版本的选择，这时需要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如何判断行数据的哪个版本可用？

MVCC整体流程 1. 获取事务ID 2. 获取read view 3. 查询数据 4. 比较事务ID 5. 符合规则的数据 6. 返回数据

原理：InnoDB每行数据都有一个影藏的回滚指针，用于指向该行修改前的一些历史版本，这些历史版本存放在UndoLog中， 如果要执行更新操作，会将原记录放入UndoLog中并通过影藏的回滚指针指向UndoLog中的原记录，其它事务此时需要查询 时就是查询UndoLog中这行数据的最后一个历史版本。

MVCC给MySQL带来的好处：读库加锁，读写不冲突极大增加了MySQL并发性，保证事务当中隔离特性

MVCC只在RC和RR隔离级别下有

事务隔离级别

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+tree索引

借鉴了

二分查找法

二叉查找树

平衡二叉树

缺点：每个节点最多只有两个分支，如果数据量太大需要查询多层节点才能查询在叶子节点的数据

B树

一个节点可以拥有多个分支 缺点：每个节点都包含key值和data值，如果data值比较大每一叶存储的key会比较少

检索原理

B+树-高度一般在2到4层 MySQL的B+树索引不能找到一个给定键值的具体行

【初始化介绍】： 一颗B+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示） 如磁盘块1包含数据项 17 和 35 , 包含指针 P1, P2, P3 P1 表示小雨 17 的磁盘块， P2 表示在17 和 35 之间的磁盘块， P3 表示大于 35 的磁盘块 真实的数据存在于叶子节点 即3，5，9，10，13，15，28，29，36，60，75，79，90，99。 非叶子节点不存储真实的数据，只存储指引搜索的数据项，如 17，35 并不是真实存在与数据表中

【查找过程】： 如果要查找数据项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+树；叶子节点存放的是整行数据

InnoDB的主键一定是聚集索引

每张表只能有一个聚集索引

辅助索引

叶子节点并不会放整行数据

先查到主键，再通过聚集索引树找到行数据

一颗高度为3的辅助索引树查询记录需要至少6次逻辑IO

唯一索引

索引列的值必须唯一，但允许有空值

唯一索引的更新不能使用change buffer

联合索引

前缀索引

全文索引

添加全文索引 alter table 表名 add fulltext(字段);

查询语句 select * from 表名 where match(字段) against('所搜的关键字');

如果内容为单词字母，会更具空格切割创建索引 nice to meet you 通过ee查询不到数据，meet可以

哪些情况下需要创建索引？

避免回表

覆盖索引不需要回表

使用聚合函数的字段

频繁作为查询条件的字段应该创建索引

查询中与其他关联的字段，外键关系建立索引

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。 多个字段使用联合索引

查询中统计或分组字段

哪些情况下不需要创建索引？

1. 表记录太少

2. 经常增删改的字段

提高了查询速度，同时会降低更新表的速度，如对表进行 insert , update , delete 因为表更新时， MySQL 不仅仅要保存数据，还要保存索引文件。

3. 数据重复，且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。 如果某个数据列包含多个重复的内容，为它建立索引就没有太大的实际效果。

1. 假如一个表由10万记录，由一个字段A只有 T 和 F 两种值，且每个值的分布概率大约为 50%， 那么对这种表A字段建索引一般不会提高数据库的查询速度。

2. 索引的选择性是指索引列中不同值的数目与表中记录数相比。如果一个表中有2000条记录，表索引列有1980个不同的值， 那么这个索引的选择性就是 1980/2000=0.99，一个索引的选择性越接近于1，这个索引的效率就越高

4.尽可能的考虑建立联合索引而不是单列索引

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。 如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

5.注意避免冗余索引 

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。 如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的引而不是创建新索引。

性能分析 Explain

怎么使用执行计划？

Explain + SQL 语句

名词解释

select 查询编号，包含一组数字，表示查询中执行 select 子句或操作表的顺序

三种情况

id 相同，执行顺序由上而下

运行结果

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';

select_type

查询的类型

simple

简单查询(不使用关联查询或子查询)

primary

如果包含关联查询或者子查询，则最外层的查询部分标记为primary

union

联合查询中第二个及后面的查询

dependent union

满足依赖外部的关联查询中第二个及后面的查询

union result

联合查询的结果

subquery

子查询中的第一个查询

dependent subquery

子查询中的第一个查询，并且依赖外部查询

derived

用到派生表的查询

materialized

被物化的子查询

uncacheable subquery

一个子查询的结果不能被缓存，必须重新评估外层查询的每一行

uncacheable union

关联查询第二个或后面的语句属于不可缓存的子查询

table

涉及到的表

partitions

将匹配记录所在的分区

type

访问类型排列，从最好到最差依次是： system>const>eq_ref>ref>range>index>ALL

system 

查询对象表只有一行数据，且只能用于MyISAM和Memory引擎的表

const 

基于主键或唯一索引查询，最多返回一条结果

eq_ref

唯一性索引扫描：表连接时基于主键或非NULL的唯一索引完成扫描

ref

非唯一性索引扫描：基于普通索引的等值查询，或者表间等值连接

fulltext

全文检索

ref_or_null

表连接类似是ref，但进行扫描的索引列中可能包含NULL值

index_merge

利用多个索引

unique_subquery

子查询中使用唯一索引

index_subquery

子查询中使用普通索引

rang

利用索引进行范围查询。一般就是你在 where 语句中出现了 between、<、>、in 等的查询  这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某个点，而结束于另一个点，不用全表扫描

index

Full Index Scan ，全索引扫描。 index 于 ALL的区别，index 类型只遍历索引树，这通常比 ALL 快，因为索引文件通常比数据文件小。 （也就是说虽然 all 和 index 都是读全表，但是index 是从索引中读取的，而 all 是从硬盘中读取的 ）

all

Full Table Scan 全表扫描

备注：一般来说，得以保证查询至少达到 rang 级别，最好能达到 ref。

possible_keys

显示可能应用在这张表中的索引，一个或多个。 查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引，如果为NULL，则没有使用索引

查询中若使用了覆盖索引，则该索引仅出现在KEY列表中

示例1

示例2

key_len

表示索引中使用的字节数，可通过该列计算查询中的使用的索引的长度，在不损失精确性的情况下，长度越短越好

key_len 显示的只是索引字段的最大可能长度， 并非实际使用长度。 即 key_len 是更具表定义计算而得，不是通过表内检索出的。

总结：条件越多，付出的代价越大，key_len 的长度也就越大，建议在一定条件的情况下，key_len 越短，效率越高。

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值

rows

预计需要扫描的行数，对InnoDB来说这个值是估值，并不一定准确

filtered

按条件筛选的行的百分比

Extra

附加信息

id, select_type, table, type , possible_keys, key, key_len, ref, rows, Extra

1. Using filesort

说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取 mysql 中无法利用索引完成的排序叫做 “文件排序”

示例

2. Using temporary

使用了临时表保存中间结果， MySQL 在对查询结果排序时使用临时表。 常见于排序 order by 和分组查询 group by 。

示例

3. Using index

表示相应的 select 操作使用了覆盖索引 (Covering Index), 避免了访问表的数据行，效率不错～ 如果同时出现 using where , 表示索引被用来执行索引键值的查找； 如果没有同时出现 using where , 表明索引引用来读取数据而非执行查找动作。

示例

覆盖索引（Covering Index）

覆盖索引（Covering Index）, 一说为索引覆盖

理解方式一：就是 select 的数据列只用从索引中就能取得，不必读取数据行， MySQL 可以利用你索引返回 select 列表的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖

理解方式二：索引是高效找到的行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行，毕竟索引叶子节点存储了他们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。

注意

如果要使用覆盖索引，一定要注意 select 列表汇总只取出需要的列，不可 select * 

因为如果将所有字段一起做索引将会导致索引文件过大，查询性能下降。

4. Using where

表明使用了 where 过滤

5. using join buffer

使用了链接缓存

6. impossible where

where 子句的值总是 false , 不能用来获取任何元组

示例

7. select tbale optimized away

在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUT（*）操作 不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

8. distinct 

优化 distinct 操作在找到第一匹配的元祖后立即停止找相同值的动作。

获取正在执行语句的执行计划

show processlist;

explain for connection 连接ID;

MySQL8.0执行计划新用法

树状执行计划

explain format=tree + SQL语句 cost 预估成本信息；rows预估扫描行数

explain analyze + SQL语句

会执行SQL，并且会包含执行成本返回行数，执行时间以及循环次数的信息

索引优化

联合索引

可以完整用到联合索引的情况

只能部分使用联合索引的情况

可以用到覆盖索引的情况

不能使用联合索引的情况

索引失效（应该避免）

案例（索引失效）

不符合最佳左前缀法则

如果索引了多列，需要准守最左前缀法则，指的是查询从索引的最左前列开始并且 不跳过索引中的列。

索引失效

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';

explain select * from staffs where name = 'July' and pos = 'dev1';

MySQL 5.6 以上版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP开启时的执行计划含有 Using index condition 标示，表示优化器使用了ICP对数据访问进行优化。

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

不在索引列上做任何操作（计算、函数、（自动 or 手动）类型转换），会导致索引失效而转向全表扫描

示例

存储引擎不能使用索引中范围条件右边的列

示例

在 5.6 +， ICP特征可以使用到 Using index condition

合理使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少 select *

示例

mysql 在适应不等于（!= 或者 <>）的时候无法使用索引会导致全表扫描 注意：范围查询太大不会使用索引查询，小范围会使用索引查询

示例

is null, is not null 也无法使用索引

示例

like 以通配符开头（'%abc ...'）mysql 索引失效会变成全表扫描的操作

问题：解决 like '% 字符串 %' 索引不被使用的方法？？

建表SQL

#like 关键字 '%%' create table `tb_user` ( `id` int(11) not null auto_increment, `name` varchar(20) default null, `age` int(11) default null, `email` varchar(20) default null, primary key(id) ) engine = innodb auto_increment=1 default charset = utf8; select * from `tb_user`; #drop table `tb_user`; insert into tb_user(name, age, email) values ('1aa1', 21, 'b@163.com'); insert into tb_user(name, age, email) values ('2aa2', 222, 'a@163.com'); insert into tb_user(name, age, email) values ('3aa3', 256, 'c@163.com'); insert into tb_user(name, age, email) values ('4aa4', 21, 'd@163.com');

完整 SQL（含优化结论）

覆盖索引

示例：

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

示例

少用 or, 用它来连接时会索引失效

示例

总结

1. 慢查询开启并捕获，开启慢查询日志， 设置阈值，比如超过5秒钟就是是慢SQL，至少一天， 看看生产慢SQL的情况

2. explain +  慢 SQL 分析

3. show profile 查询SQL服务器里面的执行细节和生命周期情况

4. SQL 数据库服务器的参数调优

分析扩展

批量数据脚本

插入 1000w 数据

1. 建表

2.  设置参数 log_bin_trust_function_creators

创建函数，假如报错：this function has none of DETERMINISTIC ...

# 由于开启过慢查询日志，因为我们开启了bin-log, 我们就必须为我们的 function 指定一个参数。

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

3. 创建函数，保证每条数据都不同

随机字符串

随机产生部门编号

4. 创建存储过程

dept

emp

5. 调用存储过程

dept

# dept 表中插入数据 call insert_dept(100, 1000000); call insert_dept(2000000, 1000000); call insert_dept(3000000, 1000000); call insert_dept(4000000, 1000000); call insert_dept(5000000, 1000000); call insert_dept(6000000, 1000000); call insert_dept(7000000, 1000000); call insert_dept(8000000, 1000000); call insert_dept(9000000, 1000000);

emp

# emp 表中插入数据 call insert_emp(100, 1000000); call insert_emp(2000000, 1000000); call insert_emp(3000000, 1000000); call insert_emp(4000000, 1000000); call insert_emp(5000000, 1000000); call insert_emp(6000000, 1000000); call insert_emp(7000000, 1000000); call insert_emp(8000000, 1000000); call insert_emp(9000000, 1000000); call insert_emp(10000000, 1000000);

慢日志查询

是什么

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%';

默认情况下 slow_query_log 的为 off , 表示慢查询日志是禁用的， 可以通过设置 slow_query_log 的值来开启

查询结果

开启

set global slow_query_log = 1;

使用了 set global slow_query_log = 1;  开启了慢查询日志只对当前数据库生效。 如果 mysql 重启后会失效

执行后的结果

那么慢查询开启了慢查询日志后，怎么样的 SQL 才会记录到慢查询日志中呢？

这个是由参数 long_query_time 控制，默认情况下 long_query_time 的值为 10秒

命令 show variables like '%long_query_time%';

执行结果

可以使用命令修改，也可以在 my.cnf 命令里修改

假如运行时间正好等于 long_query_time 的情况，并不会被记录下来。也就是说， 在 mysql 源码里是判断大于 long_query_time , 而非大于等于。

Case

查看当前多少秒算慢

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 并后续分析

模拟慢 SQL ： select sleep(4);

查询当前系统中有多少慢查询记录

show global status like '%Slow_queries%'

示例

日志分析工具 mysqldumpslow

查看 mysqldumpslow 的帮助信息

查看帮助信息

s: 是表示按照何种方式排序；

c: 访问次数

l: 锁定时间

r: 返回记录

t: 查询时间

al: 平均锁定时间

ar: 平均返回记录数

at: 平均查询时间

t: 即为返回前面多少条的数据

g: 后面搭配一个正则匹配模式，大小写不敏感

工作常用参考

得到返回记录集最多的 10 个SQL mysqldumpslow -s r -t 10 /var/bin/mysql/xx-slow.log

得到访问次数最多的 10 个SQL mysqldumpslow -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

Show Profile

是什么：是mysql 提供用来分析当前会话中语句执行的资源消耗情况。可以用于 SQL 的调优的测量

官网：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

默认情况下，参数处于关闭状态，并且保存最近15次的运行结果

分析步骤

1. 是否支持，看看当前的 mysql 版本是否支持

show variables like 'profiling' 默认关闭，使用前需要开启

查询结果

或者: show variables like 'profiling%';

2. 开启功能默认是关闭，使用前需要开启

show variables like 'profiling%';

set profiling=on;

子主题

3. 运行 SQL 

select `id` % 10 from `emp` group by `id` % 10 limit 150000;

select id%20 from emp group by id%20 order by id;

4. 查看运行结果：show profiles;

5. 诊断SQL ： show profile cpu, block io for query 上一步前面的问题 SQL 数字号码；

示例 show profile cpu, block io for query 3;

记录日志

参数备注

type

ALL 

显示全部开销信息

BLOCK IO

显示块 IO 相关开销

CONTEXXTSWITCHAES

上下文切换相关开销

CPU

显示 CPU 相关的开销信息

IPC

显示发生和接收相关的信息

MEMORY

显示内存相关的信息

PAGE FAULTS

显示页面错误相关的开销信息

SOURCE

显示和 Source_function, Source_file, Source_line 相关的开销信息

SWAPS

显示交换次数相关的开销的信息。

6. 日常开发需要注意的事项

converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上面搬了

Create tmp table 创建临时表

拷贝数据到临时表

 用完再删除

示例

Copying to tmp table on disk 把内存中的临时表复制到磁盘，危险！！！！

bocked

全局查询日志（测试环境使用）

配置启用

在  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很早以前版本索引用的红黑树。为什么不用红黑树（二叉平衡树），因为层级比较多，检索数据慢。

B tree

1> 每个节点都存储key和data。任何关键字出现且只出现在一个节点中 2> 叶子节点指针为null。 3> 搜索有可能在非叶子节点结束，在关键字全集内做一次查找，性能逼近二分查找算法。

B+ tree

叶子结点中包含了全部关键字的信息，非叶子节点只存储键值信息，及指向含有这些关键字记录的指针。 2> 为所有叶子节点增加了一个链指针，且叶子结点本身依关键字的大小自小而大的顺序链接。 3> 每个节点中的key个数越多，那么树的高度越小，需要 I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。非叶子结点直接在内存里面折半查找，只有叶子节点才会进行一次磁盘IO。

什么是聚集索引？都有哪些优缺点？

数据和索引设计到一个文件中。叶节点包含了所有数据记录。

优点：查询速度非常的快， B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

缺点：依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。 更新代价大 ：索引列的数据被修改时，对应的索引也将会被修改， 而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的。

什么是非聚集索引？都有哪些优缺点？

数据和索引设计到不同文件中。

优点：更新代价比聚集索引要小 。因为非聚集索引的叶子节点是不存放数据的。

缺点：跟聚集索引一样，非聚集索引也依赖于有序的数据，可能会二次查询(回表) 。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

聚集索引和非聚集索引哪个检索数据快？为什么？

聚集索引检索数据会比非聚集索引快。

原因：聚集索引只要找到索引就能一次性把索引所在行的数据一次性加载出来，因为之前已经加载到内存中了，而非聚集索引需要先在索引文件（MYI）中把磁盘文件地址找到，然后再去数据文件（MYD）中再做一次磁盘io，所以需要两次磁盘io，而聚集索引只需要一次磁盘io，所以聚集索引会快。

什么是回表？

需要扫描两遍索引树，先通过普通索引定位到主键值，再通过聚集索引定位到行记录。

什么是hash索引？

对索引的key进行一个hash运算，就可以定位出数据存储的位置。 仅能满足“=” 或者 “IN”，不支持范围查找（工作中很少用hash索引的原因）。会发生hash碰撞，但概率很低。

hash索引如何定位索引数据的？

where col3 = “tom”，只要把tom的hash值算出来，然后定位到数据存储的位置，逐个在hash链表中进行比对， 找到和Tom相同的元素，拿出他的磁盘文件地址，去磁盘里面定位这行的所有元素。

什么是覆盖索引？

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就称之为“覆盖索引”。

在 InnoDB 存储引擎中，如果不是主键索引（叶子节点存储的是主键+列值），最终还是要“回表”。 也就是要通过主键再查找一次。这样就会比较慢，覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！ （即需要查询的字段正好是索引的字段，而无需回表查询。）

什么是主键索引(Primary Key)？

一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段。 如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

什么是二级索引(辅助索引)？

二级索引又称为辅助索引，属于非聚集索引。是因为二级索引的叶子节点存储的数据是主键。通过二级索引，可以定位主键的位置。 唯一索引，普通索引，前缀索引等索引属于二级索引。

为什么建议innodb表必须建主键，并且推荐使用整型的自增主键？

（1）为什么必须建主键？innodb整个表的结构，他的设计就是必须有一个b+tree来组织整张表的数据。 如果设置了主键，主键自带索引，他就会默认用主键来组织整张表的数据，如果没有建索引，他会帮我们找一个主键，他会在整张表逐列去找，找到一列所有数据都不重复，就是那种可以添加唯一索引的列，他找到这个列之后，会用这列数据来建一个b+tree的结构来组织整张表的所有数据；如果找不到这样不重复的列，mysql会维护一个隐藏列，类似rowid，他会用这个隐藏列来组织维护整张表的所有数据。

（2）为什么用整型？和uuid对比，找索引元素的时候，会有很多次元素大小的比对，对于比较大小肯定是整型比uuid那种字符串比较要快，从存储空间来看，整型也比uuid占用的少。

（3）为什么要用自增？自增的话，永远是往后面插入，如果不是自增，插入的时候会插入到中间，插入到中间的话，如果节点满了，树就会进行分裂，分裂后还会做一下平衡。这样效率就比较低，所以自增往后面插入效率会高。

B+tree 与 B-tree区别有哪些？

（1）B-tree：叶子节点没有维护双向指针，没有指针就没有办法更好支持范围查找。 （2）B+tree：非叶子结点只放了索引元素，没有放数据，整个数据都放到叶子节点，这样可以降低树的高度，查找效率会更高。

InnoDB与MyISAM有什么区别？

（1）InnoDB支持事务，MyISAM不支持 （2）InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁 （3）InnoDB支持外键，而MyISAM不支持

（4）InnoDB是聚集索引

使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组 织的一个索引结构），必须要有主键，通过主键索引效率很高。 但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

（4）MyISAM是非聚集索引

也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。 也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值； 而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

（5）InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。 而MyISAM用一个变量保存了整个表的行数，

（6）Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高； PS：5.7以后的InnoDB支持全文索引了。