一、MySQL优化

目录

1.1 慢查询

1.1.1 如何定位慢查询？

慢查询表象：页面加载过慢

解决方案一 开源工具

调试工具：Arthas
运维工具：Prometheus、Skywalking

解决方案二 MySQL自带慢日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认10秒)的所有SQL语句的日志如果要开启慢查询日志，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:

答：

  1. 介绍当时问题场景（压力测试时 某个接口响应时间很慢）
  2. 在mysql中开启了慢日志查询，设置2秒，一旦sql执行超过2秒就会被记录在日志中（调试阶段）

1.1.2 sql语句执行很慢 如何分析呢？

  1. 聚合查询（新增临时表）
  2. 多表查询（优化sql语句结构）
  3. 表数据量过大查询（添加索引）
  4. 深度分页查询

解决方案一 采用EXPLAIN 或者 DESC命令获取sql执行计划

  1. EXPLAIN 命令


    type 越往左效率越高

答：

  • 通过key和key len检查是否命中了索引(索引本身存在是否有失效的情况)
  • 通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描
  • 通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

1.2 索引

1.2.1 什么是索引?

  1. 索引(index)是帮助MSQL高效获取数据的数据结构(有序)。
  2. 提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）
  3. 通过索引对数据进行排序，降低数据排序的成本，降低了CPU的使用

1.2.2 索引的底层数据结构

  1. 二叉树
  2. 红黑树
  3. B树

    灰色是指针，蓝色是键值，绿色是数据
  4. B+树

    1.非叶子节点只存储指针，叶子节点存储数据
    2.B+树磁盘读写代价更低 查询效率B+树更稳定
    3.B+树便于扫库和区间查询

答：

MySQL的InnoDB存储引擎是B+树结构

  • 阶数更多，路径更短
  • 磁盘读写代价B+树更低，非叶子节点只存储指针，叶子节点存储数据
  • B+树便于扫库和区间查询

1.2.3 什么是聚簇索引（聚集索引）什么是非聚簇索引（二级索引）/什么是回表查询

聚集索引选取规则

  • 如果存在主键 主键索引就是聚集索引
  • 如果没有主键，则使用唯一索引
  • 如果没有唯一索引，则使用普通索引 InnoDB会自动生成一个rowid作为隐藏聚集索引

答：

  1. 聚簇索引：数据和索引存储在同一个地方，索引的叶子节点存储了整行数据，有且只有一个。
  2. 非聚簇索引：数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个
  3. 回表查询：查询数据时，通过二级索引找到对应主键值，到聚集索引中查找整行数据

1.2.4 什么是覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到

  • 使用id查询 直接走聚集索引查询 一次索引扫描 直接返回数据 性能高
  • 尽量避免使用select *

简单来说就是查询过程中不涉及回表查询

1.2.5 MySQL超大分页处理

数据量较大时，如果进行limit分页查询，越往后分页查询效率越低

优化思路：

一般分页查询时，通过创建覆盖索引能够比较好的提高性能，通过覆盖索引加子查询提高性能

先从分页的表中查询到表中需要的id，然后对表的id进行排序，就能筛选出分页后的id集合，因为id是覆盖索引，所以查询效率相对较高

1.2.6 创建索引的原则

  1. *数据量大，查询比较频繁的表建立索引 （单表超过10w）
  2. *对于常作为：查询条件，排序，分组操作的字段建立索引
  3. 选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引效率越高
  4. 如果是字符串类型的字段，可以针对字段特点建立前缀索引
  5. *尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，避免回表
  6. *控制索引数量
  7. 索引不能存储null值，创建表时用NOT NULL约束

答：

  1. 先陈述自己工作业务
  2. 超过10万条数据，建议建立索引
  3. 作为查询条件，排序字段或分组的字段
  4. 使用联合索引，返回值使用覆盖索引
  5. 字符串类型字段使用前缀索引
  6. 控制索引数量

1.2.7 什么时候索引会失效

带头大哥不能死，中间兄弟不能断，范围后面全失效，最佳左前缀，%放到最右边

答：

  1. 违反最左前缀法则
  2. 范围查询右边的列，不能使用索引
  3. 在索引上进行运算操作
  4. 字符串不加单引号（类型转换）
  5. %开头的模糊插叙导致索引失效

1.3 sql优化的经验

1.3.1 表的设计优化

  1. 主要参考阿里开发手册（嵩山版）
    1. 设置合适的数据（tinyint int bigint）
    2. 设置合适的字符串类型（char varchar）定长效率高
  2. SQL语句优化
    1. 避免直接使用select *
    2. sql语句要避免索引失效的写法
    3. 尽量使用union all代替union union会多一次过滤 效率低 （两条sql的并集）
    4. 避免在where上对字段进行表达式操作
    5. Join优化 能用innnerjoin就不用左连接/右连接，如必须使用一定要以小表为驱动 因为内连接会对两个表进行优化，优先把小表放在外面，大表放在里面。外连接不会重新调整顺序。
  3. 主从复制，读写分离
    1. 主库负责写，从库负责读
    2. 主从复制延迟问题
    3. 主从复制数据不一致问题
      数据库读多写少，为避免写操作造成的性能影响，采用读写分离架构
  4. 索引优化（创建索引原则，索引失效的注意事项）
  5. 分库分表（详见1.6）数据量十分巨大时

1.4 事务相关 ACID

事务特性是什么：事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，目即这些操作要么同时成功，要么同时失败。

（结合转账案例）

  1. 原子性（Atomicity）要么全部成功 要么全部失败
  2. 一致性（Consistency）事务完成时，所有数据保持一致（数据守恒）
  3. 隔离性（Isolation）不受外部影响 在独立环境下运行
  4. 持久性（Durability）事务一旦提交或回滚，对数据库中数据的改变是永久的

1.5 并发事务带来哪些问题？ 怎么解决？ Mysql默认隔离级别？

脏读，不可重复读，幻读

  1. 脏读：一个事务读取了另一个事务未提交的数据
  2. 不可重复读：一个事务读取了另一个事务已提交的数据，导致两次读取结果不一致
  3. 幻读：一个事务两次查询中得到了不同的结果集

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

项目 不可重复读 幻读
侧重 同一条记录的值发生变化 查询结果集中出现了新记录或消失
原因 其他事务更新或删除了已有记录 其他事务插入了新记录或删除记录
解决方式 可重复读隔离级别 串行化隔离级别

隔离级别：

越往下安全性越高 性能越低

  1. 读未提交（Read Uncommitted）
  2. 读已提交（Read Committed）
  3. 可重复读（Repeatable Read） 默认
  4. 串行化（Serializable）

解决办法：

  1. 脏读：使用读已提交隔离级别
  2. 不可重复读：使用可重复读隔离级别
  3. 幻读：使用串行化隔离级别

1.6 undo log 和 redo log的区别

  • 缓冲池(buffer pool):主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改査操作时，先操作缓冲池中的数据(若缓冲池没有数据，则从磁盘加载并缓存)，以一定频率刷新到磁盘，从而减少磁盘10，加快处理速度。
  • 数据页(page):是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。页中存储的是行数据

redo log (重做日志): 记录物理日志 当事务提交之后会把所有修改的信息都存到该日志文件里，在用于刷新脏页到磁盘，发生错误时，进行数据恢复使用

undo log (回滚日志): 存储逻辑日志

  • redo log：记录数据页的物理变化，服务宕机可以用来同步数据

  • undo log：记录数据页的逻辑变化，事务回滚时，通过逆操作来恢复原来的数据，比如 我们delete一条数据 undo log就会记录一条insert语句

  • redo log 保证了事务的持久性，undo log 保证了事务的原子性和一致性

1.7 事务隔离性怎么保证的？（解释mvcc）

  • 锁：排他锁
  • mvcc：多版本并发控制 指的是维护一个数据的多个版本，使读写操作没有冲突

mvcc实现原理

  1. 隐式字段

  2. undo log

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

  3. readview （读视图）：解决的是一个事务查询选择版本的问题

    1. 当前读：读取的是记录的最新版本 读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
    2. 快照读：读取的是记录的快照 有可能是历史数据，不加锁
      1. 读已提交：每次select都会生成一个快照读
      2. 可重复读：开启事务后第一个select语句是快照读的地方，之后的select语句都是当前读
    3. 四个核心字段：

1.8 MySQL主从同步原理

主从复制核心是二进制日志

BINLOG日志，记录了DDL 和 DML （不包括 select show）

答：

  1. 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
  2. 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log。
  3. 从库重做中继日志中的事件，将改变反映它自己的数据。

1.9 分库分表

  • 垂直分库：（微服务项目 高并发下提高性能）
    • 以表为依据，依据业务将不同的表拆分到不同库中。
    • 提高磁盘IO和数据量连接数
  • 垂直分表：
    • 以字段为依据，依据字段属性将不同的字段拆分到不同表中。
    • 把不常用字段单独放在一张表（冷热数据分离）
    • text blob等大字段拆分出来放在附表中
  • 水平分库：（解决海量数据存储 高并发）
    • 将一个库的数据库拆分到多个数据库中
    • 根据id节点取模或者根据id区间进行路由
  • 水平分表：
    • 将一个表拆分到多个表中
    • 根据id节点取模或者根据id区间进行路由

分库之后的问题

  • 分布式事务一致性问题
  • 跨节点关联查询
  • 跨节点分页、排序函数
  • 主键避重

解决方式：中间件：MyCat