如何定位慢查询？

  • 开源工具
    • 调试工具：Arthas
    • 运维工具：Promethus、Skywalking
  • MySQL自带慢日志
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志时间为2s
long_query_time=2
# 慢日志位置
# /var/lib/mysql/localhost-slow.log

一条SQL语句执行很慢，如何分析？

可以使用EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句信息

  • possible_keys 当前sql可能会使用到的索引

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

  • key_len 索引占用的大小

  • Extra 额外的优化建议

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

    Using index condition查找使用了索引，但是需要回表查询数据

  • type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、index、all

    • system 查询系统的表
    • const 根据主键查询
    • eq_ref 根据主键或者唯一索引
    • ref 索引查询
    • range 范围查询
    • index 索引树扫描
    • all 全表扫描

什么是索引？

  • 索引（index）是帮助MySQL高效获取数据的数据结构（有序）
  • 提高数据的检索效率，减低数据库的IO成本（不需要全表扫描）
  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

B树与B+树对比？

MySQL的InnoDB引擎采用的B+树的数据结构来存储索引

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

什么是聚簇索引什么是非聚簇索引？

  • 聚簇索引（聚集索引）：数据与索引放到一起，B+树的叶子节点保存了整行数据，有且只有一个
  • 非聚簇索引（二级索引）：数据与索引分开存储，B+树的叶子节点保存的是对应的主键，可以有多个

什么是回表查询？

通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表

什么是覆盖索引？

覆盖索引是指查询使用了索引，返回的列，必须在索引中全部找到

  • 使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高
  • 如果返回的列中没有创建索引，有可能出发回表查询，尽量避免使用select *

MySQL超大分页怎么处理？

问题：在数据量较大时，使用limit分页查询，需要对数据进行排序，效率低

解决方案：覆盖索引+子查询

索引创建的原则有哪些？

  • 数据量较大，且查询比较频繁的表（10w+）
  • 常作为查询、排序、分组的字段
  • 字段内容区分度高
  • 内容较长，使用前缀索引
  • 尽量使用联合索引（覆盖索引）
  • 要控制索引的数量
  • 如果索引列不能存储NULL值，在创建表的时候用NOT NULL约束

什么情况下索引会失效？

  • 违反最左前缀法则
  • 范围查询右边的列，不能使用索引
  • 不能在索引列进行运算操作
  • 字符串条件不佳单引号（类型转换）
  • 以%开头的Like模糊查询

SQL优化方法？

  • 表设计优化（参考阿里开发手册《嵩山版》）
    • 设置合适的数值（tinyint、int、bigint）
    • 设置合适的字符串类型（char、varchar），char定长效率高，varchar可变长、效率低
  • SQL语句优化
    • SELECT 语句必须指明字段名称
    • SQL避免索引失效写法
    • 尽量使用union all 代替 union ，union会多一次过滤，效率低
    • 避免在where子句中进行表达式操作
    • join能用inner join 就不用left join，right join，如必须使用，以小表驱动大表，内连接会动两个表进行优化，优先把小表放到外面，把大表放到里面。left join或right join，不会重新调整顺序
  • 主从复制、读写分离
  • 分库分表

事务的特性是什么？

  • 原子性（Atomicity）事务是不可分割的最小操作单元，要么全部成功，要么全部失败
  • 一致性（Consistency）事务完成时，必须使的所有的数据都保持一致的状态
  • 隔离性（Isolation）数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
  • 持久性（Durability）事务一旦提交或者回滚，它对数据库的数据改变是永久的

并发事务带来什么问题？

  • 脏读：一个事务读取到另外一个事务还没有提交的数据
  • 不可重复读：一个事务先后读取同一条数据，但两次读取的结果不同
  • 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入时，又发现这行数据已经存在，好像出现了“幻觉”

MySQL的隔离级别？

默认为REPEATABLE READ 可重复读取 （无法解决 幻读）

  • READ UNCOMMITTED 未提交读 （无法解决 脏读、不可重复读、幻读）
  • READ COMMITTED 读已提交 （无法解决 不可重复 幻读）
  • REPEATABLE READ 可重复读取 （无法解决 幻读）
  • SERIALIZABLE 串行化

undo log 和 redo log的区别？

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

事务的隔离性是如何保证的？

锁机制：

  • 数据库系统使用锁来控制并发访问，确保事务在读取和修改数据时的一致性。
  • 行级锁和表级锁是常见的锁类型，用于控制对数据行或表的访问。

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

  • 隐藏字段：
    • trx_id（事务id）,记录每一次操作的事务id，是自增的
    • roll_pointer（回滚指针），指向上一个版本的事务版本记录地址
  • undo log:
    • 回滚日志，存储老版日志
    • 版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表
  • readView 解决的是一个事务查询选择版本的问题
    • 根据readView匹配规则和当前事务id判断应该访问哪个版本的数据
    • 不同的隔离级别快照读是不一样的，最终访问的结果也不同
      • RC：每一次执行快照读时生成ReadView
      • RR：仅在事务中第一次执行快照读时生成ReadView，后续复用

MySQL 主从同步原理

MySQL主从复制的核心就是二进制日志binlog（DDL（数据定义语言）语句和DML（数据操纵）语句）

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

MySQL分库分表

拆分前提：

  • 请求数多或者业务累积大
  • 单表1000w+或者20G+

拆分策略：

  • 水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发问题
  • 水平分表，解决单表存储和性能问题
  • 垂直分库，根据业务进行拆分，高并发下提高磁盘IO和网络连接数
  • 垂直分表，冷热数据分离，多表互不影响

水平拆分需要用到 sharding-sphere或者mycat等中间件，用于解决分布式事务一致性问题、跨节点关联查询、挂节点分页、主键避重等问题。