MySQL篇

如何定位慢查询

出现的表象：页面加载太慢，接口压测响应时间超过1s

出现的本质：

查表时出现聚合查询
多表查询
表数据量过大的查询
深度分页查询

解决方案：

开源工具：Arthas，Prometheus
MySQL自带的工具：慢日志查询
1
2
slow_query_log = 1 # 开启慢日志查询
long_query_time = 2 # 设置慢日志查询时间阈值为2s
配置完成之后，查询启动MySQL服务器进行测试，在/var/lib/mysql/localhost-slow.log中查看结果

注意：一般只在测试阶段才会做，因为正常使用的时候打开慢日志查询会降低性能。

这个SQL语句执行很慢，如何分析？

通过SQL执行计划，找到慢的原因。

科研采用EXPLAIN或者DESC命令获得MySQL如何执行SELECT语句的信息
possible_keys :当前sql可能会使用到的索引

key：当前sql实际命中的索引

key_len: 索引占用的大小，结合key字段可以判断是否命中了索引（索引本身是否失效）

Extra: 额外优化建议：

Using where；Using Index：查找使用了索引，但是需要的数据都在索引列中能找到，不需要回表查询数据

Using index condition：查找使用了索引，但是需要回表查询数据(通过添加索引或者修改返回字段修复)

type：表示这条sql的连接类型：性能好到差：Null，system，const，eq_ref，ref，range，index，all（开发中最低到range）

什么是索引？

索引是帮助MySQL高效获取数据的数据结构（有序）。除了数据之外，数据库还维护着特定的数据交换（B+树），这些结构指向存储的数据，使用高级查找算法高效获取数据。

索引的底层： B+树。MySQL的InnoDB采用B+树的数据结构来存储索引

B+树与B树相比，优势在于：

读写磁盘代价更低：B+树内部节点没有具体的内容指针，只记录儿子地址，故IO读取时可以读取更多关键字
更高效：查询路径都相同（都必须走到叶子才知道结果）。
更有利于数据库扫描：叶子之间相连（方便区间查询），支持顺序查询和根查询。

聚簇索引与非聚簇索引

聚簇索引（聚集索引）：数据和索引放到一起，B+树的叶子节点保存了整行数据，有且只有一个

聚簇索引选取规则：存在主键，主键为聚簇索引；无主键唯一索引为聚簇索引，都无，InnDB自动生成rowid作为隐藏索引

非聚簇索引：数据与索引分开存储，B+树的叶子节点保存了对应的主键。可以有多个

图中Id为主键，为name添加索引后，形成的非聚簇索引B+树。

回表查询： Eg：select * from user where name = ‘Arm’;

由于name添加了索引，所以会走下面的B+树，但查询到的是主键地址，而结果却要*，这样查到了主键地址也没用，还得再走一遍聚簇索引。

回表操作就是：通过二级查询找到了对应的主键值，再到聚簇索引中查找了整行数据，这个过程就称为回表。

覆盖索引，超大分页优化

覆盖索引：查询使用了索引，返回的列必须再索引中全部找到。可以说索引成功了（不用回表查询）就是覆盖索引，而失败了（没有在结果中找到全部的结果）就要回表查询。

启示：尽量不要使用select *，如果返回的列中没有创建索引，就可能触发回表查询。

超大分页优化：

1	SELECT * FROM tb_sku ORDER BY id LIMIT 9000000, 10;

需要先扫描前 9000000+10 条数据，然后丢弃前 9000000 条 （关键点在这里，需要优化）
即便 id 有索引，也需要回表 9000010 次（每次都要读取磁盘数据）
当 offset 极大时，性能急剧下降（本质是 O(n) 复杂度）

优化分页：让id作主键或者唯一索引

SELECT t.* 
FROM tb_sku t, 
     (SELECT id FROM tb_sku ORDER BY id LIMIT 9000000, 10) a 
WHERE t.id = a.id;

只要扫描索引树的9000010 个节点（不需要回表查询），而是是O(logn),最后只要与主查询关联，再额外查10个就可以结束。

索引创建的原则

常见索引：主键索引，唯一索引，复合索引

原则：

数据量大且查询比较频繁的表建立索引
字段：常作为where，order by,group by操作的字段，应该建立索引
区分度搞的列，尽量建立唯一索引，区分度越高，使用索引效率就高
字段为字符串，字段很长则建立前缀索引
鼓励使用联合索引，而不是单列索引。联合索引很多时候可以覆盖索引，不用回表。提高查询效率
索引并不是越多越高，维护索引也有额外开销
索引列不存NULL，则创建表时使用NOT NULL约束

索引失效的情况

通过explain，结合key和key_len字段可以判断索引是否失效

违反最左前缀法则：

索引多列的时候，要从查询条件要从最左端开始，并且不能跳过索引中的列。

失败情况：

2.范围查询时，右边的列不能使用索引。

此时status还是走索引，但是address没有命中索引了
1. 不要在索引列上进行运算操作，否则索引失效
2. 字符串不加引号，造成索引失效。
3. like关键字匹配的%开头的模糊匹配。 like “%abc%”,like “%abc”,like “abc%”只有最后一个生效

sql优化经验

表的设计优化
索引优化：参考索引创建原则与索引失效情况
SQL语句优化：
1. SELECT语句，务必指明具体字段，不要使用*
2. SQL要避免索引失效的情况
3. 尽量用union all 去代替 union，union会多一次过滤，对获取的结果进行排序操作。union all只是合并查询结果，并不会进行去重和排序操作，在没有去重的前提下，使用union all的执行效率要比union高
4. 避免在where子句中对字段进行表达式操作
5. 能用innerjoin就不用left join/right join，必须使用时，以小表为驱动。内连接会对两个表进行优化，优先把小表放在外面，大表放在里面。而外连接不会。
写入操作很多时，采用主从复制，读写分离的架构：主DB复制写，从DB复制读，主DB会根据规则把数据同步到从DB中。

事务的特性

ACID.

A:原子性，事务是最小的操作单元，要么全部成功，要么全部失败。

C:一致性，事务完成时，必须使所有数据都保持一致的状态。

I:隔离性，依托DB的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

D:持久性，事务一旦提交或回滚，它对数据库的改变就是永久的。

并发事务问题

脏读：一个事务读到另外一个事务还没提交的数据。

不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同。

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是插入数据时，又发现这行数据已经存在。

** 丢失修改：两个事务同时对一个数据继续操作，比如A=20，但事务A,B同时对A-1，最后A=19。称为丢失修改。

不可重复读与幻读的区别：

不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改；
幻读的重点在于记录新增比如多次执行同一条查询语句（DQL）时，发现查到的记录增加了。

幻读其实可以看作是不可重复读的一种特殊情况，单独把幻读区分出来的原因主要是解决幻读和不可重复读的方案不一样。

解决方案：对事物进行隔离：从上到下隔离级别依次提高，但是性能越来越低。

undo log与redo log区别

缓冲池：主存中的一个区域，可以缓存磁盘上经常操作的真实数据。执行CRUD时，先操作缓冲池中的数据，随后再将池中数据写入磁盘。

数据页：InnoDB存储引擎磁盘管理的最小单元。

redo log：

内存中加入了Redolog buffer，主要记录数据页的变化并上传到磁盘中的redolog中。磁盘中也加入了ib_logfile0/1(redolog磁盘数据)，用于接收内存中的redolog buffer中的数据。当内存中的脏页同步失败时，就用该数据完成更新。日志文件中的读写操作都是顺序的磁盘IO。当脏页正常写入磁盘时，redolog磁盘数据就没有用了，会循环使用这篇磁盘空间，定时清理。

undo log：

回滚日志，用于记录数据被修改前的信息。作用包括：提供回滚，MVCC。undo log主要记录逻辑日志。

当delete一条数据时，undo log会对应记录一条insert记录，反之亦然

当update一条记录时，它会记录一条对应相反的update数据，当执行rollback时，可以通过undolog进行回滚。

区别： redo log是记录数据页的物理变化，当发生宕机时可以同步数据。undo log记录逻辑日志，回滚时提供逆操作的数据。redolog保证了持久性，而undolog保证了原子性和一致性

什么是MVCC

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

MVCC主要依赖于：隐藏字段，undolog，readview

undo log：

insert时，产生的undolog只在回滚时需要，当在事务提交后，可被立即删除。

update和delete时，产生的undo log日志不仅在回滚时需要，mvcc版本控制也需要，不会立即被删除。

undo log版本链：

不同的事务或者相同的事务对同一记录进行修改，会导致该记录的undolog生成一条记录版本的链表，头部是最新的旧纪录，尾部是最早的旧纪录

readview（读视图）：

是快照读SQL执行时MVCC提供数据的依据，记录并维护系统当前活跃的事务。

当前读：读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录。

快照读：普通的select语句（不加锁）。读取的是记录数据的可见版本，也可能是历史版本

RC: 每次select，都生成一个快照读

RR：开启事务后的第一个select语句才是快照读的地方。

readview中包含了四个核心字段：

m_ids : 当前活跃事务ID集合

min_trx_id: 最小活跃事务ID

max_trx_id:最大事务ID+1

creator_trx_id : ReadView创建者的事务ID

比较规则：

并发事务控制方式二：锁

除了MVCC，还有锁方案。锁可以看作是悲观控制的模式，多版本并发控制可以看作是乐观控制的模式。

锁控制方式下会通过锁来显式控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制。

共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。

排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。

表级锁与行级锁的区别？

表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。
行级锁： MySQL 中锁定粒度最小的一种锁，是 针对索引字段加的锁 ，只针对当前操作的行记录进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。

行级锁使用的注意事项

InnoDB中行级锁针对索引字段加的锁，而表级锁针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。

MySQL隔离级别基于锁实现吗？

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

SERIALIZABLE 隔离级别是通过锁来实现的，READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。

MySQL主从同步原理

主从复制核心：二进制日志(BINLOG):记录了所有的DDL语句和DML语句，但不包括查询语句

主库提交事务时会写入binlog
从库有一个IO线程专门读取binlog，并写入自己的Relay log
从库有一个SQL线程写自己的Relay log中记录的事件

分库分表

单表数据量很大（1000W / 20G）
优化解决不了性能问题
IO瓶颈

分库分表缓解了访问压力和存储压力

拆分方法：垂直拆分，水平拆分

垂直分库：以表为依据，根据业务不同拆分到不同的库中。

垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。（不常用的字段单独放一个表/大字段单独拆出来）

水平分库：将一个库的数据分到多个库中。

水平分表：将一个表中数据拆分到多个表中。（可以在一个库中）

水平分法：可以按照mod方式去拆分。但会产生其他的问题，如分布式事务一致性问题，跨节点关联查询等，解决办法：有一些中间件，如mycat等