• 0-不开启
• 1-开启，默认缓存每条select,针对某个sq不缓存: select sql-no-cache
• 2-开启，默认都不缓存,通过select sql-cache制定缓存哪-个条

3. 客户端设置缓存大小

query_ cache .size
复制代码

4. 重蛋缓存

reset query cache
复制代码

5. 缓存失效

日对数据表的改动会导致基 于该数据表的所有缓存失效(表层面的管理)

六、分区

1. 默认情况下一张表对应一组存储文件，但当数据量较大时(通常千万条级别)需要将数据分到多组存储文件，保证单个文件的处理效率

2. partition by分区函数(分区字段)(分区逻辑)

• hash-分区字段为整型
• key-分区字段为字符串
• range-基于比较，只支持less than
• list-基于状态值

3. 分区管理

• 创建时分区：create table article0 partition by key(title) partitions 10
• 修改表结构：alter table article add partition(分区逻辑)

4. 分区字段应选择常用的检素字段，否则分区意义不大

七、水平分割和垂直分割

1. 水平

多张结构相同的表存储同一类型数据

单独一张表保证id唯一性

2. 垂直

分割字段到多张表，这些表记录是一对应关系

八、集群

1. 主从复制

①. 首先手动将slave和master同步一下

• stop slave
• master导出数据到slave执行一遍
• show master status with read lock记录File和Position
• 到slave.上change master to

②. start slave查看Slave_ IO_ Running和Slave_ SQL_ _Running,必须都为YES

③. master可读可写，但slave只能读，否则主从复制会失效需要重新手动同步

④. mysqlreplicate快速配置主从复制

2. 读写分离(基于主从复制)

①. 使用原stcConecton

WriteDatabase提供写连接

ReadDatabase提供读连接

②. 借助Sping AOP和Aspec实现数据源动态切换

• RoutingDataSourcelmpl extends AbstractRoutingDataSource,重写determineDatasource,注入到SqISessionFactory, 配置defaultTargetDatasource和targetDatasource (根据determineDatasource的返回值选择 具体数据源value-ref)

• DatasourceAspect切面组件，配置切入点@Pointcut aspect0 (所有DAO类的所有方法)，配置前置增强@Before(" aspect0") before(Joinpoint point)， 通过point.getSignature.getName获取方法名，与METHOD TYPE MAP的前缀集合比对，将write/read设置到当前线程上(也是接下来要执行DAO方法的线程，前置增强将其拦截下来了)

• DatasourceHandler,使用ThreadLocal在前置通知中将方法要使用的数据源绑定到执行该方法的线程上，执行方法要获取数据源时再根据当前线程获取

3. 负载均衡

算法

• 轮询
• 加权轮询
• 依据负载情况

4. 高可用

为单机服务提供一个冗余机

• 心跳检测
• 虚IP
• 主从复制

九、典型SQL

1. 线上DDL

为了避免长时间表级锁定

• copy策略,逐行复制，记录复制期间旧表SQL日志重新执行
• mysq|5.6 online ddl,大大缩短锁定时间

2. 批量导入

①. 先禁用索引和约束,导入之后统一建立

②. 避免逐条事务

innodb为了保证一致性，默认为每条SQL加事务(也是要耗费时间的)，批量导入前应手动建立事务，导入完毕后手动提交事务。

3. limit offset,rows

避兔较大的offset (较大页码数)

offset用来跳过数据，完全可以用过滤筛选数据，而不是查出来之后再通过offset跳过

4. select *

尽量查询所需字段,减少网络传输延时(影响不大)

5. order by rand（）

会为每条数据生成一个随机数最后根据随机数排序，可以使用应用程序生成随机主键代替

6. limit 1

如果确定了仅仅检索一条数据，建议都加上limit 1

十、慢查询日志

1. 定位查询效率较低的SQL,针对性地做优化

2. 配置项

• 开启slow_ query. log
• 临界时间long_ query. time

3. 慢查询日志会自己记录超过临界时间的SQL，并保存在datadir下的xxx-slow.log中

十一、Profile

1. 自动记录每条SQL的执行时间和具体某个SQL的详细步骤花费的时间

2. 配置项日

开启profiling

3. 查看日志信息show profiles

4. 查看具体SQL的详细步骤花费的时间日

show profiles for query Query_ ID
复制代码

十二、典型的服务器配置

1. max_ connections, 最大客户端连接数

2. table_ open_ cache, 表文件缓存句柄数，加快表文件的读写

3. key_ buffer. _size, 索引缓存大小

4. innodb_ buffer. pool size, innodb的缓冲池大小，实现innodb各种功能的前提

5. innodb_ file_ per_ table,每个表一个ibd文件， 否则innodb共享 表空间

十三、压测工具MySQLSlap

1. 自动生成sq|并执行来测试性能

myqslap -a-to-generate sql -root -root
复制代码

2. 并发测试

mysqlslap --auto-generate-sql --concurrency= 100 -uroot -proot,模拟100个客户端执行sql

3. 多轮测试，反应平均情况

mysqlslap --auto-generate-sql --concurrency= 100 --interations=3 -uroot -proot,模拟100个客户端执行sql.执行3轮

4. 存储引擎测试

• --engine=innodb：mysqlslap --auto-generate-sql --concurrency= 100 --interations=3 -- engine-innodb -uroot -proot,模拟100个客户端执行sql.执行3轮，innodb的处理性能

• -- engine= myisam：mysqlslap -- auto-generate-sql --concurrency= 100 --interations=3 --engine-innodb -uroot -proot,模拟100个客户端执行sql.执行3轮，myisam的处理性能

关注下面的标签，发现更多相似文章
作者：Java架构筑基
链接：https://juejin.im/post/5e1c2b2df265da3dec60ae7a
来源：掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

历时七天，史上最强MySQL优化总结，从此优化So Easy！

标签：jin   ping   routing   第一范式   全表扫描   结果   共享   修改表   建议