你可能不知道的mysql | LinkinStar's Blog

以下是针对mysql的知识点整理，用于复习，主要以罗列为主，详细具体讲解可以参考书《高性能mysql》，你可以过一遍看看有无知识点遗漏。

执行sql过程

客户端 -> 连接器 -> 分析器 -> 优化器 -> 执行器 -> 存储引擎
连接器：连接上数据库，长连接
分析器：分析语法（包含解析器和预处理器，解析器生成解析树，预处理器判断字段存在歧义）
优化器：选择正确的索引进行优化执行
执行器：执行具体sql返回结果

mysql的两个重要日志

redo-log（重做日志）：固定大小的循环缓存，InnoDB使用，即使重启，只要记录到了redo-log就不会丢失。防止mysql意外。
bin-log：归档日志，所有sql都会记录，并且采用追加，满了之后新开，有两种方式，一种是记录sql语句（statement），一种是row，记录出现的事件。
如果只记录sql语句会导致主从同步上面存在问题，从库执行相同的sql得到效果不同，所以还有一种混合的方式，mysql会自动判断当前语句是否会造成主从不同步的情况，如果会，那么就使用row记录如果不会就是用sql记录，因为row记录会增加存储空间。

undo-log（回滚日志）：记录修改的状态和回滚信息，利用这个实现mvcc（多版本并发控制），系统会自动判断回滚日志什么时候会被删除。用于回滚操作。

两个日志记录的顺序：
更新的行如果不在内存，从磁盘取出 -> 修改内存中的值 -> 写入redo-log状态为prepare -> 写binlog -> 提交事务redo-log进行commit

数据库的隔离级别

读未提交：能读到别人未提交事务修改的数据
读已提交：能读到别人提交事务之后修改的数据
可重复读：在读已提交的基础上，当前事务读取第一次和第二次的结果相同
串行化：读会加读锁，写会加写锁，读写冲突串行化执行

隔离级别通过视图实现，读未提交没有视图，读已提交每次sql执行创建一个视图，可重复读在开始之前创建一个视图，串行化直接加锁没有视图。

事务与隔离级别：更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”；所以即使是可以重复读的隔离级别，更新数据时还是会进行当前读来保证别人已经提交的事务不被覆盖。

幻读：幻读是出现在范围查询，第二次查询之前，由于其他事务新增记录导致查询两次不同，区别于可重复读。InnoDB引入间隙锁来解决，锁住范围内的各个间隙。但是要注意间隙锁也容易导致死锁，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作，间隙锁之间都不存在冲突关系。

索引基础

常见的索引类型有：哈希、数组、搜索树
哈希用于等值查询，不适合范围查询；数组查询很快，但是更新效率低
数据库使用N叉树降低树的层级，innodb使用的是B+树

在InnoDb中，主键索引又叫聚簇索引，非主键索引又叫二级索引
主键索引可以拿到全部数据，而非主键索引只能拿到主键id通过回表查询来拿到数据
如果一个数据页满了需要新增一个数据页也叫做页分裂性能下降并且空间利用率下降，所以使用自增主键更加合理

覆盖索引：当我们查询的时候只需要查询出id字段的时候就可以直接使用单个索引来完成，不需要进行回表操作，减少搜索次数。

最左前缀原则：当我们进行一个字段查询的时候，如果这个字段没有单独做索引，但是有别的联合索引包含这个字段，且刚好以这个字段开头，那么也可以进行匹配。所以在建立联合索引的时候需要考虑字段排序，这样就可以减少维护的索引个数。

索引下堆优化：mysql5.6之后，当查询的条件中包含索引中的字段，会优先对索引中的字段做判断，而非直接回表查询。

重建索引：当删除很多数据之后，由于索引没有被删除，所以会导致数据页有空洞，而且占用资源，这个时候可以考虑再低谷期重建索引 alter table T engine=InnoDB。

唯一索引和普通索引：插入上面性能几乎没有区别，更新上面普通索引可以使用change buffer所以更加快一些，而唯一索引需要判断所以慢一些。选择还是需要根据业务出发去考虑。

合理设置前缀索引：索引可以设置只用前面几位，可以减少索引占用空间，同时设置时应保证合适的区分度。

锁相关

全局锁：用于备份的时候，锁住整个库，防止备份过程中数据修改导致问题。
表锁：有两种，一种是表锁，在引擎不支持行锁的时候使用，锁住之后不能进行增删改查；另一种是元数据锁，访问表的时候自动加上，读写锁。默认就是。

行锁：在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

间隙锁：专门用来解决幻读的问题，在可重复读的情况下才会生效。
（间隙锁和行锁合称next-key lock）
next-key lock锁的规则：
加锁范围是前开后闭区间；查找过程中访问到的对象才会加锁；当遇到索引等值查询，如果是唯一索引，那么因为只可能有一行记录那么就退化为行锁；如果索引等值查询，发现没有满足情况，就只能退化为间隙锁去锁间隙；如果是范围查询那么就会查询到第一个不满足条件的情况为止。

死锁：当对于同一个表的多行数据进行修改的时候，容易出现死锁，相互等待。死锁可以通过死锁检测或者是超时回滚来解决，但是对于性能损失巨大，最好通过业务或者客户端优化处理。

count(*)：针对这个有特殊优化，但innodb没有直接记录行数，还是需要遍历计数，实在不行可以业务实现计数。

MyISAM不支持事务
MyISAM不支持行锁
在InnoDB中，每个数据页的大小默认是16KB。

order by的实现：在不用索引的时候，如果内存够用，那么会将查询全部查出来然后放到内存中快排，如果内部不够，使用磁盘进行排序后归并。更好的情况是去使用索引，因为存储的时候默认就是有顺序的，这样能减少排序从而加速。

无法使用索引的情况

1. 如果对字段做了函数计算，就用不上索引了
2. 如果触发隐式转换也用不上索引了
3. 字符集不同触发转换也无法使用索引

查看相关命令

show processlist命令查看Waiting for table metadata lock
查看各个线程锁的情况

select from information_schema.innodb_trx\G
selectfrom t sys.innodb_lock_waits where locked_table= 'test'.'t'\G
可以查看具体是被那个线程锁住了

一些小的

for update和lock in share mode
lock in share mode是意向共享锁，其他session可以读取相关记录，也可以继续加IS，但是无法修改
for update是意向排他锁，其他session无法进行select…for update操作，也就是排除别的想要加排它锁的情况。
两者都不会阻塞别的session进行的快照读。
用法，lock in share mode用于两个表之间要保证一致性，a表的操作时要保证b表中的某条数据不能被修改；
for update用于同一个表中的数据，a事务操作时不允许b事务进行修改。

在删除数据的时候尽量加limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。

不要一次性地用delete语句删除太多数据。其实，这就是一个典型的大事务场景。

sql慢的原因

索引设计不合理
sql设计不合理
mysql索引自动选择错误

运维上的一些

双主的时候，通过binlog上面的serverid记录来判断是否与自己相同，如果不同才会更新，避免循环复制

主备延迟的来源：
备库机器性能差，备库查询压力大，大事务一直正在处理。
mysql5.7采用并行复制的策略减少主备延迟

因为主备同步会存在延迟，所以在开发的时候一定要注意读取从库的时候不一定是最新的值。
1、读取的时候读主库，最常用
2、读取之前进行睡眠一段时间保证同步
保证seconds_behind_master一定为0的时候才执行查询
或者可以使用semi-sync replication，当从库收到binlog之后会返回主库一个ack，主库只有收到这个ack之后才认为事务完成

如何进行主备切换？？？？过程是怎么样的？

对比位点
Master_Log_File和Read_Master_Log_Pos，表示的是读到的主库的最新位点；
Relay_Master_Log_File和Exec_Master_Log_Pos，表示的是备库执行的最新位点。
如果位点相同可以认为已经同步

对比GTID集合确保主备无延迟：

如何判断一个数据库正常
1、使用select进行查询，查询一个创建在mysql库中的表；容易实现，但是因为只是查询所以会漏掉一些错误条件，比如当磁盘满了，binlog写不进去了，但是可以读不能写。那么可以使用update来进行优化一下下。尝试去修改一个值来实现。

当出现误删除（delete）的时候，这个时候要指望binlog存放了数据，然后逆执行去恢复（Flashback），但是需要确保binlog_format=row 和 binlog_row_image=FULL。

预防才是关键：
把sql_safe_updates参数设置为on。这样一来，如果我们忘记在delete或者update语句中写where条件，或者where条件里面没有包含索引字段的话，这条语句的执行就会报错。

如果是直接执行的drop的话，binlog也无能为力，因为log中没有存放删除的数据，这个时候只能依赖备份了，利用最近一次备份的数据进行恢复，然后进行binlog重放。

故意延迟复制的从库，弄一个故意延迟一个小时复制的从库，这样无论什么时候都能快速拿到一个小时前的数据。

账号权限很关键，没有权限去执行对应操作的sql就可以了

kill query +线程id，可以终止一个线程正在执行的sql语句

mysql采用的是边查边给的，查到就会发给客户端，而不是全部查到全部结果之后再发

使用join的时候一定要注意，使用是有条件的：
当使用join的时候被驱动表能使用索引，那么是可以的，同时也需要注意，使用小表作为驱动表，这样能让扫描行数更加少一些，大表去走索引去。
当使用join的时候如果不能走索引的情况，那么mysql会使用BNL算法，将驱动表的数据和被驱动表的数据加载到内存中，并且使用join_buffer来进行合并操作，但是这样扫描行会变的非常的巨大，所以这个时候如果表的数据太多就不适合使用。

mysql面试问题

主从复制的原理与流程？

1. 主库将修改写入本地binlog中
2. 从库将拉取主库binlog写入本地relay log中
3. 从库读取relay log并执行（这里是单线程执行，不能并发，所以慢）

innodb和myisam与区别

innodb支持事务，myisam不支持
innodb支持行锁，myisam支持表锁
innodb支持mvcc，myisam不支持
innodb支持外键，myisam不支持
myisam不支持崩溃后安全恢复

innodb引擎的4大特性

1. 插入缓冲insert buffer，change buffer；将一系列的操作缓存，然后一次性写到磁盘，目的还是为了减少随机IO带来性能损耗。
2. 二次写：从innodb buffer pool中flush写文件之前存doublewrite buffer写到物理磁盘上共享表空间。
3. 自适应哈希索引：当二级索引访问频繁的时候，会自动建立哈希索引来加速
4. 预读

mysql索引方法有哪些

B-Tree索引：利用二叉树的特性，同时优化磁盘io，然后查询更快，同时优化索引查询和排序
Hash索引：基于hash实现，在hash冲突不高的情况下，速度快，但是对于范围查询和排序都不支持

mysql索引类型有哪些

主键索引，普通索引（组合索引），唯一索引，全文索引，空间索引