优化定位慢查询聚合查询多表查询表数据量过大查询深度分页查询表象页面加载过慢、接口压测响应时间过长超过1s1.使用开源的工具定位 接口-对应代码2.使用mysql自带的慢日志开启查询后可直接查看慢查询分析知道了sql很慢如何分析了可以采用EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息可以采用MySQL自带的分析工具 EXPLAIN通过key和key_len检查是否命中了索引索引本身存在是否有失效的情况通过type字段查看sql是否有进一步的优化空间是否存在全索引扫描或全盘扫描通过extra建议判断是否出现了回表的情况如果出现了可以尝试添加索引或修改返回字段来修复Mysql存储引擎存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的而不是基于库的所以存储引擎也可被称为表类型。INNodb支持事务支持行级锁 支持外键索引是一种数据结构帮助mysql查询优化的结构。提高数据检索的效率降低数据库的IO成本不需要全表扫描数据结构B树这些数据结构以某种方式引用指向数据 这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。二叉搜索树容易有最坏的情况出现退化为链表b树底部存储着所有的数据适合区间 范围扫描效率更高什么是聚簇索引什么是非聚簇索引 ?聚簇索引聚集索引数据与索引放到一块B树的叶子节点保存了整行数据有且只有一个 非聚簇索引二级索引数据与索引分开存储B树的叶子节点保存对应的主键可以有多个聚集索引一般是主键知道什么是回表查询吗覆盖索引覆盖索引是指查询使用了索引并且需要返回的列在该索引中已经全部能够找到 。聚集索引Clustered Index也译作 “聚簇索引”不是覆盖索引Covering Index二者是从完全不同维度定义的索引类型 —— 前者描述索引与数据的物理存储关系后者描述索引能否满足查询的全部需求Mysql超大分页处理、当在进行分页查询时如果执行 limit 9000000,10 此时需要MySQL排序前9000010 记录仅仅返回 9000000 - 9000010 的记录其他记录丢弃查询排序的代价非常大 。拆分 “定位主键” 和 “查询全字段” 两步索引创建原则索引失效1 如果索引了多列要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。匹配最左前缀法则2.范围查询右边的列不能使用索引会导致范围查询后面的失效3.不要在索引列上进行运算操作 索引将失效。4.字符串不加单引号造成索引失效。由于在查询是没有对字符串加单引号 MySQL的查询优化器会自动的进行类型转换造成索引失效。5).以%开头的Like模糊查询索引失效。如果仅仅是尾部模糊匹配索引不会失效。如果是头部模糊匹配索引失效Sql优化sql优化表的设计优化参考阿里开发手册《嵩山版》 比如设置合适的数值tinyint int bigint要根据实际情况选择 比如设置合适的字符串类型char和varcharchar定长效率高varchar可变长度效率稍低索引优化分库分表读写分离select指明字段尽量部回表查询尽量走覆盖索引事务事务的特性原子性Atomicity事务是不可分割的最小操作单元要么全部成功要么全部失败。一致性Consistency事务完成时必须使所有的数据都保持一致状态。隔离性Isolation数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行。持久性Durability事务一旦提交或回滚它对数据库中的数据的改变就是永久的。事务是一组操作的集合它是一个不可分割的工作单位事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失败。事务的并发问题杂言undo log和redo log的区别‘redo log: 记录的是数据页的物理变化服务宕机可用来同步数据 undo log 记录的是逻辑日志当事务回滚时通过逆操作恢复原来的数据 redo log保证了事务的持久性undo log保证了事务的原子性和一致性事务中的隔离性是如何保证的呢锁排他锁如一个事务获取了一个数据行的排他锁其他事务就不能再获取该行的其他锁 mvcc : 多版本并发控制 你解释一下MVCC?解释一下MVCC全称 Multi-Version Concurrency Control多版本并发控制。指维护一个数据的多个版本使得读写操作没有冲突 MVCC的具体实现主要依赖于数据库记录中的隐式字段、undo log日志、readView。Mysql主从同步原理MySQL主从复制的核心就是二进制日志binlog(DDL数据定义语言语句和 DML数据操纵语言语句) 主库在事务提交时会把数据变更记录在二进制日志文件 Binlog 中。 从库读取主库的二进制日志文件 Binlog 写入到从库的中继日志 Relay Log 。 从库重做中继日志中的事件将改变反映它自己的数据分库分表分库分表的时机1前提项目业务数据逐渐增多或业务发展比较迅速 单表的数据量达1000W或20G以后2优化已解决不了性能问题主从读写分离、查询索引…3IO瓶颈磁盘IO、网络IO、CPU瓶颈聚合查询、连接数太多库为载体表为载体把同一个表的数据按指定规则如用户 ID、时间分散存储到多个独立的 MySQL 数据库实例中而非同一个库的不同表。比如校园应用中将用户表user按学号尾号拆到 db1、db2、db3 三个库每个库都有完整的user表结构但只存部分用户数据。ShardingSphere分库分表框架集成了分布式事务能力是解决基于 MySQL 的分库分表场景下分布式事务的主流方案。
Mysql八股
优化定位慢查询聚合查询多表查询表数据量过大查询深度分页查询表象页面加载过慢、接口压测响应时间过长超过1s1.使用开源的工具定位 接口-对应代码2.使用mysql自带的慢日志开启查询后可直接查看慢查询分析知道了sql很慢如何分析了可以采用EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息可以采用MySQL自带的分析工具 EXPLAIN通过key和key_len检查是否命中了索引索引本身存在是否有失效的情况通过type字段查看sql是否有进一步的优化空间是否存在全索引扫描或全盘扫描通过extra建议判断是否出现了回表的情况如果出现了可以尝试添加索引或修改返回字段来修复Mysql存储引擎存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的而不是基于库的所以存储引擎也可被称为表类型。INNodb支持事务支持行级锁 支持外键索引是一种数据结构帮助mysql查询优化的结构。提高数据检索的效率降低数据库的IO成本不需要全表扫描数据结构B树这些数据结构以某种方式引用指向数据 这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。二叉搜索树容易有最坏的情况出现退化为链表b树底部存储着所有的数据适合区间 范围扫描效率更高什么是聚簇索引什么是非聚簇索引 ?聚簇索引聚集索引数据与索引放到一块B树的叶子节点保存了整行数据有且只有一个 非聚簇索引二级索引数据与索引分开存储B树的叶子节点保存对应的主键可以有多个聚集索引一般是主键知道什么是回表查询吗覆盖索引覆盖索引是指查询使用了索引并且需要返回的列在该索引中已经全部能够找到 。聚集索引Clustered Index也译作 “聚簇索引”不是覆盖索引Covering Index二者是从完全不同维度定义的索引类型 —— 前者描述索引与数据的物理存储关系后者描述索引能否满足查询的全部需求Mysql超大分页处理、当在进行分页查询时如果执行 limit 9000000,10 此时需要MySQL排序前9000010 记录仅仅返回 9000000 - 9000010 的记录其他记录丢弃查询排序的代价非常大 。拆分 “定位主键” 和 “查询全字段” 两步索引创建原则索引失效1 如果索引了多列要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。匹配最左前缀法则2.范围查询右边的列不能使用索引会导致范围查询后面的失效3.不要在索引列上进行运算操作 索引将失效。4.字符串不加单引号造成索引失效。由于在查询是没有对字符串加单引号 MySQL的查询优化器会自动的进行类型转换造成索引失效。5).以%开头的Like模糊查询索引失效。如果仅仅是尾部模糊匹配索引不会失效。如果是头部模糊匹配索引失效Sql优化sql优化表的设计优化参考阿里开发手册《嵩山版》 比如设置合适的数值tinyint int bigint要根据实际情况选择 比如设置合适的字符串类型char和varcharchar定长效率高varchar可变长度效率稍低索引优化分库分表读写分离select指明字段尽量部回表查询尽量走覆盖索引事务事务的特性原子性Atomicity事务是不可分割的最小操作单元要么全部成功要么全部失败。一致性Consistency事务完成时必须使所有的数据都保持一致状态。隔离性Isolation数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行。持久性Durability事务一旦提交或回滚它对数据库中的数据的改变就是永久的。事务是一组操作的集合它是一个不可分割的工作单位事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失败。事务的并发问题杂言undo log和redo log的区别‘redo log: 记录的是数据页的物理变化服务宕机可用来同步数据 undo log 记录的是逻辑日志当事务回滚时通过逆操作恢复原来的数据 redo log保证了事务的持久性undo log保证了事务的原子性和一致性事务中的隔离性是如何保证的呢锁排他锁如一个事务获取了一个数据行的排他锁其他事务就不能再获取该行的其他锁 mvcc : 多版本并发控制 你解释一下MVCC?解释一下MVCC全称 Multi-Version Concurrency Control多版本并发控制。指维护一个数据的多个版本使得读写操作没有冲突 MVCC的具体实现主要依赖于数据库记录中的隐式字段、undo log日志、readView。Mysql主从同步原理MySQL主从复制的核心就是二进制日志binlog(DDL数据定义语言语句和 DML数据操纵语言语句) 主库在事务提交时会把数据变更记录在二进制日志文件 Binlog 中。 从库读取主库的二进制日志文件 Binlog 写入到从库的中继日志 Relay Log 。 从库重做中继日志中的事件将改变反映它自己的数据分库分表分库分表的时机1前提项目业务数据逐渐增多或业务发展比较迅速 单表的数据量达1000W或20G以后2优化已解决不了性能问题主从读写分离、查询索引…3IO瓶颈磁盘IO、网络IO、CPU瓶颈聚合查询、连接数太多库为载体表为载体把同一个表的数据按指定规则如用户 ID、时间分散存储到多个独立的 MySQL 数据库实例中而非同一个库的不同表。比如校园应用中将用户表user按学号尾号拆到 db1、db2、db3 三个库每个库都有完整的user表结构但只存部分用户数据。ShardingSphere分库分表框架集成了分布式事务能力是解决基于 MySQL 的分库分表场景下分布式事务的主流方案。
