MySQL篇

一、定位慢查询

定位慢查询很简单，主要有以下方式：

1. 通过运维工具分析
2. 开启mysql慢日志

在mysql的配置文件/etc/my.cnf中配置并开启慢日志功能

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

• 分析某一条具体的sql语句

EXPLAIN/SELECT   字段列表   FROM   表名   WHERE  条件 ;

执行结果：

通过key和key_len检查是否命中了索引（索引本身存在是否有失效的情况）

通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描

通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

索引

• 索引是帮助MySQL高效查找数据的满足特定查找算法的数据结构
• 索引的底层数据结构是B+树

聚集索引和二级索引

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则：

• 当存在主键或唯一索引时，优先使用主键或第一个唯一索引作为聚集索引
• 如果主键和唯一索引都没有，InnoDB会自动生成一个rowid作为隐藏的聚集索引

回表查询：

通过二级索引查找到对应主键值，然后再根据主键值查询整行数据，这个过程叫做回表

覆盖索引:

指查询使用了索引，并且所有需返回的字段全部能在该索引中直接找到

联合索引：

指由多个字段共同构成的索引

索引失效情况

最左前缀匹配法则：

创建了联合索引(a,b,c)后，只有当前面的字段相同时，才会去比较后面的字段，优先按照联合索引最左字段进行匹配

索引失效情况：

① 如果不遵循最左匹配原则，则可能会导致索引失效

• where b='' c='': 此时索引全部失效
• where a='' c='':此时仅有索引a生效

② 范围查询右边的列，不能使用索引

• where a='' b>'': 此时c索引失效

③ 在索引字段上进行运算，索引也会失效

④ 字符串不加单引号，索引也会失效(造成类型转换)

⑤ 模糊查询%xxx模式，也会导致索引失效

SQL优化经验

1.表的设计优化，数据类型的选择

2.索引优化，索引创建原则

3.sql语句优化，避免索引失效，避免使用select * ….

4.主从复制、读写分离，不让数据的写入，影响读操作

5.分库分表

事务

• 并发事务可能出现的问题

1. 脏读
2. 不可重复读
3. 幻读

• 解决方案 —— 事务的隔离级别

1. 读未提交
2. 读已提交：只能解决脏读
3. 可重复读：只能解决脏读、不可重复读
4. 串行化：全部解决

MySQL日志

• redo log：记录的是数据页的物理变化，当服务器宕机或其他情况导致数据不一致时，可用来同步数据，保证了事务的持久性
• undo log：记录的是逻辑日志，当事务回滚时，通过逆操作来恢复数据，保证了事务的原子性和一致性

保证事务的隔离性

1. 排他锁：用于数据修改操作，确保不会同时同一资源进行多重更新
2. MVCC(多版本并发控制)：指维护一个数据的多个版本，使得读写操作没有冲突

• 隐式字段

①trx_id(事务id)，记录每一次操作的事务id，是自增的

②roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址

• undo log

①回滚日志，存储老版本数据

②版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表

• readview

  • 根据readView的匹配规则和当前的一些事务id判断该访问那个版本的数据

  • 不同的隔离级别快照读是不一样的，最终的访问的结果不一样

    RC ：每一次执行快照读时生成ReadView

    RR：仅在事务中第一次执行快照读时生成ReadView，后续复用

数据库主从同步原理

①主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。

②从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。

③从库重做中继日志中的事件，将改变反映它自己的数据

分库分表

• 水平分库
• 水平分表
• 垂直分库
• 垂直分表