Mysql性能调优

一、sql慢原因

1 、无索引，索引失效
2 、锁等待
3 、不恰当的sql

select * 、对大数据表limit m n 、对非索引字段进行排序

二、sql优化

1 、 优化步骤

1 、 explain分析sql执行计划
2 、 Show Profile分析SQL执行性能(执行线程的状态跟时间，type不同显示不同系统资源上所消耗的时间

2 、 常见优化方式

1 、子查询优化分页查询
优化前 ：select * from test order by code limit 10000,20
优化后 ：select * from test where id >( select id from test order by code limit 10000,1) limit 20
2 、优化select count(*)
① 、使用explain对表进行估算
② 、添加汇总统计表或者缓存字段来统计
3 、优化select * ，避免回表操作
主键为聚簇索引(基于B+数，叶子节点存行数据)，二级索引为非聚簇索引(叶子节点存主键id)
优化前 ：select * from test
优化后 ：select id from test

三 、优化高并发事务

1 、结合场景使用事务隔离级别

innodb的rc跟rr基于多版本并发控制 (MVCC)，对普通的select不加锁，读取快照的缓存数据

2 、 避免行锁升级为表锁
3 、 控制事务大小，减少锁定的资源量和锁定时间长度(新建订单再删除库存)

四 、建立使用索引优化

1 、覆盖索引优化查询

不需要查询出包含整行记录的信息减少了I/O操作

2 、 自增字段作主键优化查询

新数据按顺序添加到当前索引节点位置，不需要移动已有数据

3 、前缀索引优化

索引是存储在磁盘中的，以页为单位(16k)，数据大造成遍历大量页，效率低；
缺点 ：order by 无法使用，也不能作为覆盖索引

4 、防止索引失效

1 、 Memory引擎的hash索引，只有在=的情况下才生效
2 、 %开头的like无法利用节点查询
3 、 复合索引时需要用最左列进行查询-最左匹配原则
4 、 or前后条件中有一个列没有索引，则涉及的索引都不会被使用到

5 、force index(索引名) 可以强制使用索引，直接放到from表名的后边使用
6 、mysql判断查询超过整个表20%的数据时，就会考虑使用聚簇索引来查询数据

五 、避免死锁

必要条件 ：互斥、占有且等待、不可强占有、循环等待

1 、当两个事务相互等待时，当一个事务等待时间超过设置的某一阈值，就对该事务进行回滚

innodb_lock_wait_timeout设置

2 、 将字段设为唯一索引

不能防止幻读，但可以保证数据唯一性
缺点 ：当数据重复就会抛出异常

3 、 其他方式实现幂等性校验

redis(setnx方法分布式锁)
zookeeper(临时有序节点，watch机制实现分布式锁)等

4 、 更新数据尽量使用主键方式

聚簇索引与辅助索引之间的相互等待

5 、 避免长事务，尽量将长事务拆解，降低与其他事务的冲突概率
6 、mysql默认开启死锁检测机制

检测到死锁之后会选择最小的(占用资源最少)的事务进行回滚
使用 wait_for graph 算法检测
innodb_deadlock_detect=on 设置打开检测

六 、分库分表问题

1 、分布式事务问题

两阶事务提交(2pc)
补偿事务提交(tcc)
spring实现的JTA、阿里分布式事务中间件seata

2 、跨节点join查询问题

主表与详表不在同一个库，用冗余表或冗余字段避免跨库join查询

3 、 跨节点分页查询问题

建议两套数据处理 ：
一套基于分库分表的用户单条或多条数据
另一套基于elasticsearch、solr存储的数据根据字段进行分页查询
异步消息实现新增，修改数据同步

4 、全局注解id问题

uuid、
redis分布式锁递增id、
雪花算法(时间、机器标识、顺序技术long类型主键id，每秒上万id生成)

5 、 扩容问题

根据用户id hash取模分表，尽量使用2的倍数来设置表数量，扩容也按照2的倍数扩容，减少数据迁移量

七 、数据库参数设置

数据库就是用来存数据的，存数据就涉及磁盘I/O操作
Mysql 数据库为了减少磁盘I/O读写操作，应用了大量内存管理来优化DB操作，包括内存优化查询、排序以及写入操作

• innodb中数据和索引缓存如果设置过大，就会引发swap页交换

swap页交换 ：swap分区在系统的物理内存不够用的时候，就会把物理内存中的一部分空间释放出来，以供当前运行程序使用；
这些释放的空间的数据被临时保存到swap分区中，等到程序要运行时，再从分区中恢复保存的数据到内存中。

1 、 mysql体系结构

第一层客户端连接器
db连接 、授权认证 、 安全管理等，引用线程池提高线程处理效率
第二层server层
实现sql一些基础功能 sql解析、优化、执行、缓存等
第三层各种存储引擎
负责数据存取，涉及buffer缓存
第四层db存储层
负责将数据存储在文件系统中，并完成存储引擎的交互

mysql体系结构.jpg

• 查询语句步骤

通过第一层连接与授权认证；
sql请求发送到sql接口，接口接收到后，会检查查询sql是否命中cache缓存中的数据，命中直接返回；
否则进入解析器，解析器对sql进行语法以及词法分析，之后进入优化器中，优化器会生成多种执行计划方案，并选择最优方案执行；
确认执行计划方案后，执行器会检查连接用户是否有该表的执行权限，有则查询buffer中是否有该缓存，存在获取锁，查询表数据；
否则重新打开表文件，通过接口调用相应存储引擎处理，这时存储引擎就会进入到存储文件系统中获取相应的数据，并返回结果集

• 更新语句步骤

更新 sql 的执行流程跟查询 sql 差不多，更新操作多了记录日志
在执行更新操作时 mysql 会将操作的日志记录到 binlog（归档日志）中，这个步骤所有的存储引擎都有。
而 InnoDB 除了要记录 binlog 之外，还需要多记录一个 redo log（重做日志）。
redo log 主要是为了解决 crash-safe 问题而引入的

• crash-safe : 保证存储的数据要么存储成功，要么存储失败
  内容源于极客时间app《java性能调优实战》，侵删。

执行更新操作时，先查询相关的数据，之后通过执行器执行更新操作，并将执行结果写入到内存中，同时记录更新操作到 redo log 的缓存中，此时 redo log 中的记录状态为 prepare，并通知执行器更新完成，随时可以提交事务。
执行器收到通知后会执行 binlog 的写入操作，此时的 binlog 是记录在缓存中的，写入成功后会调用引擎的提交事务接口，更新记录状态为 commit。
之后，内存中的 redo log 以及 binlog 都会刷新到磁盘文件中。

2 、内存调优

第一个query cache 缓存的参数设置

• 以查询sql的hash值为key，返回结果集为value
  判断一条sql是否命中缓存，是通过匹配查询sql的hash值来实现的

querycache参数.jpg

第二个 buffer 缓存参数配置
1、 myisam存储引擎
使用key buffer 缓存索引块 ，表的数据块没有缓存，直接存在磁盘文件中
可通过 key_uffer_size设置大小
一般为服务器内存中可用内存的1/4分配给你key buffer
2 、innodb存储引擎
使用innodb buffer pool 缓冲池，存储表索引块及表数据，默认内存大小128M
可通过 innodb_buffer_pool_size 一般为服务器物理内存80%

内容源于极客时间app -《java性能调优实战》，侵删。