数据库调优

Explain有哪些字段,分别有什么含义

id: 表示查询中执行select子句或者操作表的顺序，id的值越大，代表优先级越高，越先执行
select_type: 表示 select 查询的类型，主要是用于区分各种复杂的查询，例如：普通查询、联合查询、子查询等
table: 输出行所引用的表
partitions: 使用的哪个分区，需要结合表分区才可以看到
type: 查询使用了何种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
possible_keys: 表示在MySQL中通过哪些索引，能让我们在表中找到想要的记录，一旦查询涉及到的某个字段上存在索引，则索引将被列出，但这个索引并不定一会是最终查询数据时所被用到的索引
key: 区别于possible_keys，key是查询中实际使用到的索引，若没有使用索引，显示为NULL
key_len: 表示查询用到的索引长度（字节数），原则上长度越短越好
ref: 显示索引的哪一列被使用了，如果可能的话，是一个常数
rows: 以表的统计信息和索引使用情况，估算要找到我们所需的记录，需要读取的行数
filtered: 这个是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例
Extra: 不适合在其他列中显示的信息,都显示在这，例如：using file sort ，using where， using join buffer，using index等

索引

覆盖索引

简单理解就是【索引的字段就是我们查的字段】,可以直接获取，减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

联合索引

1. 适合的场景是由A即B的场景。将A和B建立联合索引，较少回表的操作
   最佳实践:一般都是设计联合索引，很少用单个字段做索引，因为还是要尽可能让索引数量少，避免磁盘占用太多，影响增删改性能

2. 【最】左匹配原则,例如:

   1. like 'upupor%',可以走到索引

   2. 如有索引 (a,b,c,d)，查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中a、b、c，无法命中d。(c已经是范围查询了，d肯定是排不了序了)

索引下推(index condition pushdown)

简单理解就是先使用确定的索引字段减少范围,直接过滤掉不满足条件的记录,减少回表次数

主键索引

属于聚集索引。聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引

聚集索引的优点和缺点:
优点：

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

缺点:

2. 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。

3. 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚集索引

非聚集索引即索引结构和数据分开存放的索引

优点：

1. 更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

缺点:

1. 跟聚集索引一样，非聚集索引也依赖于有序的数据

2. 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

唯一索引

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

普通索引

只有普通索引可以使用

前缀索引

定义字符串的一部分作为索引。会回表查完整的信息

总结

1. 唯一索引、普通索引、前缀索引属于二级索引。二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置

2. 唯一索引、普通索引会使用change buffer

   1. 什么是change buffer?
      写缓存（Change Buffer） 是一种特殊的数据结构，用于在对数据变更时，如果数据所在的数据页没有在 buffer pool 中的话，在不影响数据一致性的前提下，
      InnoDB 引擎会将对数据的操作缓存在 Change Buffer 中，这样就省去了从磁盘中读入这个数据页。

   2. 使用场景
      写多读少的业务，例如账单类、日志类

不走索引的场景

1. 对字段做了函数计算，就用不上索引了，这是MySQL的规定。因为对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

2. 隐式类型转换不会走索引，因为mysql在隐式转换时会使用转换函数，而使用了函数就不会走索引

3. 隐式字符编码转换不会走索引

4. 在联合索引的场景下，查询条件不满足最左匹配原则。

5. 在联合索引下，使用了select * 会导致索引失效。解决办法: 在联合索引下，尽量使用明确的查询列来趋向于走覆盖索引； 就是select具体的字段

6. 模糊查询时（like语句），模糊匹配的占位符位于条件的首部

7. 查询条件使用or关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效； or两边为“>”和“<”范围查询时，索引失效

8. 两列数据做比较，即便两列都创建了索引，索引也会失效。例如: id > age

9. 查询条件使用is null时正常走索引，使用is not null时，不走索引。

索引相关文章

MySQL如何设计索引更高效？
MySQL是如何优化模糊匹配like的SQL？

索引,进一步深入

有哪些数据结构?

1. Hash(无序)

   1. 可能会出现Hash冲突

   2. 可以快速的精确查询，但是不支持范围查询。

      • 适合等值查询的场景:例如Redis、Memcached这些NoSQL的中间件。

      • 如果是有序的数据结构，比如【有序数组】,支持范围和等值查询了。但是有序数据适合静态的数据，对于CUD的业务就不适合了，成本高

2. B+数

   1. MySQL为什么要选择B+

      • 同样的元素，B+树的表示要比B树要“胖”，原因在于B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。把非叶子节点冗余的原因是为了提高范围查找的效率

      • 优势:

        1. 有序

        2. 高查询索引时的磁盘IO效率高(结构整体高度低)

        3. 范围查询的效率高(有指针)

其他算法比对

1. 二叉树

   • 有序,所以支持范围查询

   • 时间复杂度是O(log(N)) 为了维持这个时间复杂度，更新的时间复杂度也得是O(log(N))，那就得保持这棵树是完全平衡二叉树了

2. 平衡二叉树

   • 是否可以使用平衡二叉树做索引?不行! 索引也不只是在内存里面存储的，还是要落盘持久化的，可以看到图中才这么一点数据，如果数据多了，树高会很高，查询的成本就会随着树高的增加而增加。

3. B树

   • B树的表示要比完全平衡二叉树要“矮”，原因在于B树中的一个节点可以存储多个元素

创建索引注意事项

1. 选择合适的字段创建索引

   1. 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。

   2. 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。

   3. 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。

   4. 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

   5. 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

2. 被频繁更新的字段应该慎重建立索引

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

4. 注意避免冗余索引

5. 考虑在字符串类型的字段上使用前缀索引代替普通索引。

flush

将redo log更新到磁盘

概念

• 脏页： 内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”

• 干净页: 内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页“

什么时候会flush?

1. InnoDB的redo log写满了，这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。

2. 系统内存不足，当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。

   • 如果刷【脏页】一定会写盘，就保证了每个数据页有两种状态：

     1. 一种是内存里存在，内存里就肯定是正确的结果，直接返回；

     2. 另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。 这样的效率最高。

3. MySQL认为系统“空闲”的时候，只要有机会就刷一点“脏页”。

4. MySQL正常关闭，这时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

我们怎么做才能把握flush的时机呢?

1. innodb_io_capacity参数设置。 它会告诉InnoDB你的磁盘能力，这个值建议设置成磁盘的IOPS，磁盘的IOPS可以通过fio这个工具来测试

2. 刷脏页的时候，旁边如果也是脏页，会一起刷掉的，并且如果周围还有脏页，这个连带责任制会一直蔓延，这种情况其实在机械硬盘时代比较好，一次IO就解决了所有问题

并发事务带来的问题(多个用户对统一数据进行操作)

1. 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

2. 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

3. 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

4. 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读的重点是修改，幻读的重点在于新增或者删除

事务隔离级别(SQL 标准定义了四个隔离级别)

1. READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

2. READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生

3. REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

4. SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL日志

MySQL 日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类

二进制日志 binlog（归档日志）

1. binlog 日志有三种格式，可以通过binlog_format参数指定

   1. 指定statement，记录的内容是SQL语句原文。同步数据时，会执行记录的SQL语句，但是有个问题，update_time=now()这里会获取当前系统时间，直接执行会导致与原库的数据不一致。

   2. row，记录的内容不再是简单的SQL语句了，还包含操作的具体数据。这样就能保证同步数据的一致性，通常情况下都是指定为row，这样可以为数据库的恢复与同步带来更好的可靠性。

   3. mixed，记录的内容是前两者的混合。
      MySQL会判断这条SQL语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement格式。

2. 写入机制
   binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中

事务日志 redo log（重做日志）

让MySQL拥有了崩溃恢复能力

undo log（回滚日志）

记录undo操作日志

redo log与binlog事务不一致解决方法

背景:
redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。在binlog提交事务准备写入时宕机了，在恢复的时候就会导致数据不一致

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。

原理很简单，将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

MySQL锁

1. 行锁

2. 表锁

   • InnoDB只有在通过索引条件检索数据时使用行级锁，否则使用表锁

   • 当索引失效的时候，行锁会升级成表锁

3. 间隙锁

   • 什么是间隙锁?
     当我们采用范围条件查询数据时，InnoDB 会对这个范围内的数据进行加锁。比如有 id 为：1、3、5、7 的 4 条数据，我们查找 1-7 范围的数据。那么 1-7 都会被加上锁。2、4、6 也在 1-7 的范围中，但是不存在这些数据记录，这些 2、4、6 就被称为间隙。

   • 间隙锁的危害
     范围查找时，会把整个范围的数据全部锁定住，即便这个范围内不存在的一些数据，也会被无辜的锁定住，比如我要在 1、3、5、7 中插入 2，这个时候 1-7 都被锁定住了，根本无法插入 2。在某些场景下会对性能产生很大的影响

MySQL主从同步

1. MySQL 本身就自带有一个主从复制的功能，可以帮助我们实现负载均衡和读写分离

2. 通过binlog来实现

缺点:
当用户写数据主服务器必须和从服务器同步了才告诉用户写入成功，等待时间比较长

主从复制原理[](https://www.cnblogs.com/rickiyang/p/13856388.html)

relay log

这里又引申出一个新的日志概念。MySQL 进行主主复制或主从复制的时候会在要复制的服务器下面产生相应的 relay log。

relay log 是怎么产生的呢？

从服务器 I/O 线程将主服务器的 Binlog 日志读取过来，解析到各类 Events 之后记录到从服务器本地文件，这个文件就被称为 relay log。然后 SQL 线程会读取 relay log 日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。中继日志充当缓冲区，这样 master 就不必等待 slave 执行完成才发送下一个事件。

主从复制可以分为

1. 主从同步：当用户写数据主服务器必须和从服务器同步了才告诉用户写入成功，等待时间比较长。

2. 主从异步：只要用户访问写数据主服务器，立即返回给用户。

3. 主从半同步：当用户访问写数据主服务器写入并同步其中一个从服务器就返回给用户成功。

canal实现原理 [](https://juejin.cn/post/7055294014630674463)

基础存储结构

页 [](https://juejin.cn/post/6844904084886011911)

深分页

limit 10000,10

MySQL引擎

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎

MyISAM 不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。

5.5 版本之后，MySQL 引入了 InnoDB（事务性数据库引擎）

MyISAM和InnoDB的区别

1. MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

2. InnoDB 引擎中，其数据文件本身就是索引文件。其 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方

面试相关的问题

很长的字段，想做索引我们怎么去优化它呢

可以冗余字段hash,转换为hash或者类似的特殊编码字符

MySQL主从复制什么原因会造成不一致，如何预防及解决

1. 人为原因导致从库与主库数据不一致（从库写入）

2. binlog非row格式

3. 主从复制过程中，主库异常宕机

4. 设置了ignore/do/rewrite等replication等规则

5. 从库中断很久，binlog应用不连续，监控并及时修复主从

解决方案:

1. 主库binlog采用ROW格式。

2. 主从实例数据库版本保持一致

3. 主库做好账号权限把控，不可以执行set sql_log_bin=0

4. 从库开启只读，不允许人为写入。

5. 定期进行主从一致性检验。