速通MySQL

MySQL问题总结

优化

1.定位慢查询

开源工具：Arthas，skywalking

开启MySQL自带的慢日志：在my.cnf（Windows版是my.ini）配置一下：

# 开启MySQL慢日志查询开关
slow_query_log=1  
# 设置慢日志的时间为2秒，SQL语句查询时间超过2秒就会被计入慢查询中
long_query_time=2

配置完毕后慢SQL会记录在localhost-slow.log中。当然在生产环境中是不会开启慢日志查询的，会影响性能

2.分析慢SQL：采用在select前加上EXPLAIN关键字，例如：

possible_keys：可能会使用到的索引

key：当前SQL命中的索引

key_len：索引占用的大小（一般通过key和key_len来判断是否命中索引）一般key_len越小，索引效果越好。

type：SQL的连接类型一般为根据主键const、索引查询eq_ref,ref、范围查询range（最低要求）的性能还行。index这种索引树扫描all这种全盘扫描就需要优化了。

如何分析慢SQL：

1. 先通过key和key_len查看是否命中了索引
2. 再通过type看是不是扫了全索引或全盘

3.索引的概念以及其底层数据结构

索引（index）帮助MySQL高效获取数据的有序的数据结构。MySQL是维护了B+树算法。索引就是一种以空间换时间的数据结构，可以提高数据检索效率，降低数据库IO成本，降低CPU消耗

为什么不用二叉树或者红黑树：最坏的二叉树时间复杂度为On，相对不太稳定；红黑树虽然时间复杂度比较稳定，但当MySQL数据量过高的时候，红黑树依旧是二叉树，效率就会很低。

因此引入了B树，相对于二叉树，B树可以有多叉并且也像二叉树一样左边比右边小，并且B树引入了指针的概念。

B+树是在B树的基础上进行了优化，InnoDB就是用B+树实现其索引结构。

B+树相较于B树的优点

• 磁盘读写代价更低：B+树非叶子结点不会存储数据；在B树查询数据的时候，查询节点的同时，节点的数据也会被读出来。因此B+树查询不会有过多的数据读
• 查找效率更稳定：B+树所有的数据都存在叶子节点上
• 更便于扫库和区间查询：B+树的叶子节点之间通过双向指针连接

4.聚簇索引（聚集索引）和非聚簇索引（二级索引）

• 聚簇索引（必须有，且只有一个）：将数据和索引存储在了一起，索引结构的叶子节点保存了行数据
  • 如果有主键，那主键就是聚簇索引
  • 如果没有主键，有unique索引，那么第一个unique索引是聚簇索引
  • 如果都没有，那么innodb提供一个隐藏的rowId作为索引
• 非聚簇索引（可以存在多个）：数据与索引分开存储，索引的叶子节点对应数据的主键

5.回表查询，覆盖索引，超大分页优化

回表查询：通过二级索引(非聚簇索引)找到对应的主键值，再通过主键值在聚集索引查行数据

覆盖索引：（简单来说就是不用走回表查询）查询使用了索引，并且需要返回的列都在该索引内可以查到

超大分页查询：举例：limit 900000 10，这里我们需要查询出来前900010条记录并排序，在最后仅仅需要900000~900010的记录，而其他记录丢掉了。

优化：通过覆盖索引+子查询（即先通过子查询对ID（主键）进行排序并取出ID，再通过ID查询对应的row）

SELECT *
FROM TABLE T,
    (SELECT id FROM TABLE ORDER BY id LIMIT 600000,10) a
WHERE T.id=a.id

6.索引的创建原则

• 数据量大且查询比较频繁（单表超过10万条数据）
• 经常用于where，orderBy，groupBy的字段
• 区分度高的字段（越接近unique越好）
• 前缀索引：字符串比较长（其实这里使用ES更好）
• 联合索引：尽量使用覆盖索引，不需要回表查询
• 要控制索引的数量，别太多
• 索引列不能存null的时候记得用notnull的约束

7.索引在什么情况下会失效：

• 违反最左前缀法则（索引了多列，查询要从索引最左前列开始，并且不跳过索引中的列）例如：当查询条件为 name / name and status / name and status and address 时索引生效；而当查询条件为status / status and address时，索引会失效；当查询条件为name and address时，索引只有name会生效（通过explain查询即可得出结论）
  

  

• 范围查询的列的右边的列会不能使用索引，例如 如果 id、age、sex是联合索引，如果age使用了范围查询，那么sex的索引就失效了

  WHERE id=1 AND age>10 AND sex=1

• 索引列上进行运算操作
• 字符串类型不加单引号，会发生类型转换导致索引失效
• 以%开头的模糊查询也会导致索引失效

8.谈谈SQL优化

1. 表的设计优化：
   1. 设置合适的数据（tinyint，int,bigint）
   2. 设置合适的字符串（char，varchar）
2. SQL语句优化：
   1. 避免select *；
   2. 避免索引失效；
   3. 避免在where子句对字段进行表达式操作；
   4. join优化：能用inner join就不用left/right join；使用的时候一定要以小表为驱动（）
   5. 尽量使用union all代替union（union会比union all多一层过滤）
3. 主从复制，读写分离
   1. 如果读场景很多，为了避免写影响读，将一个slave设置为只读；将一个master设置为只写；然后将master数据同步进slave。这样可以提高查询效率
4. 索引优化（见第6条）
5. 分库分表

事务

1.什么是事务

事务是一组操作的集合；是一个不可分割的工作单位；事务会把所有操作视为一个整体一起向系统提交或撤销；事务所控制的语句要么同时成功，要么同时失败

2.事务的特性ACID

A原子性：事务是不可分割的最小单元，要么一起成功，要么一起失败——由undo log提供

C一致性：事务完成时所有数据必须保持一致状态——由undo log提供

I隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作的影响下进行——由MVCC提供

D持久性：事务一旦提交，那么它的效果就是永久的——由redo log提供

3.并发事务问题

脏读：一个事务读到了另一个事务还未提交的数据

不可重复读：一个事务先后读取同一条记录，但结果却不一样

幻读：一个事务按条件查询时并没有查到数据，但在插入数据的时候又发现这个数据已经存在了。

4.MySQL的隔离级别（隔离级别越高，安全性越高，但是性能就越低，一般都是使用默认的可重复读）

1. 未提交读（啥都不能解决）
2. 读已提交（能解决脏读）RC
3. 可重复读（默认）RR可以解决脏读和不可重复读
4. 串行化（最高）都可以解决，但是效率很低，因为所有事务在这里都是串行进行的

5.undo-log和redo-log

redo-log前置知识：

　　缓冲池（buffer pool）主内存中的一个区域，里面缓存了磁盘上经常操作的真实数据。在执行CRUD时先操作缓冲池内数据（如果缓冲池没有数据，那需要从磁盘加载并缓存）再以一定频率刷新到磁盘，从而减少磁盘IO并加快处理速度

　　数据页（page）innodb存储引擎磁盘管理的最小单元，默认大小为16KB，页中存储的是行数据

redo-log：是一种物理日志，提交一些update/delete时，为了方便会先把数据commit进buffer pool（内存），然后由pool和page（数据页，位于磁盘，后缀为ibd）交互。但如果突然服务宕机，pool会与page无法交互，导致持久性无法保持，因此redo-log记录数据页的变化，服务宕机后可用于同步数据。实现事务的持久性

undo-log：是一种逻辑日志，回滚日志，用于记录数据被修改前的信息。一般用于事务：提供回滚和MVCC控制。可以认为他会记录和执行结果完全相反的记录，实现了事务的一致性和原子性

6.MVCC

事务如何保持隔离性：

锁：排它锁：如果事务获取了一个数据行的排它锁，其他事务就不能获取该行的排它锁

MVCC：多版本并发控制。指维护一个数据的多个版本，让读写没有冲突

实现原理：

1. 隐藏字段：
   • DB_TRX_ID：记录插入或最后一次修改这条数据的事务的ID
   • DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，配合undolog使用
   • DB_ROW_ID：隐藏主键，没有主键才会有这个字段
2. undo log：
   • 在insert，update，delete时便于回滚的日志
     • insert：产生的undo log日志只在回滚时使用，事务提交后即可删除
     • update，delete：产生的undo log日志不仅在回滚时需要，MVCC版本访问也需要，不会被立即删除
3. readview：用于解决事务查询时选择的版本问题
   • 快照读SQL执行时MVCC提供数据的依据，记录并维护系统当前活跃事务（未提交的）ID
     • 当前读：读取的是当前记录的最新版本，读取时还会对当前记录加锁使其保持版本不变
     • 快照读：读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读

主从同步原理

MySQL有二进制文件（BINLOG）记录了所有DDL和DML，但不会包括select、show语句

slave会从master的binlog中读取二进制数据，将其写入自己的relay log中，再由自身执行relay log完成数据同步

分库分表

水平分库：将一个库的数据拆分到多个库中，解决海量数据存储和高并发问题。（我实习的公司就是测试的时候是单库，生产的时候用分片技术把数据库分成多个）

水平分表：解决单表存储和性能问题。————————水平分库分表都需要涉及数据存在哪，去哪取的问题；这里一般引用mycat、sharding-sphere作为中间件

垂直分库：根据业务拆分（例如微服务），高并发环境下提高磁盘IO和网络连接数。

垂直分表：数据进行冷热分离，多表互不影响：把一个常用表的不常用字段抽出来，增加查询效率。