历时七天,史上最强MySQL优化总结,从此优化So Easy!
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了历时七天,史上最强MySQL优化总结,从此优化So Easy!,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4615字,纯文字阅读大概需要7分钟。
内容图文
![历时七天,史上最强MySQL优化总结,从此优化So Easy!](/upload/InfoBanner/zyjiaocheng/525/ec4546d45cf94ba5afcd39572862eab3.jpg)
- 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 第一范式 全表扫描 结果 共享 修改表 建议
本文系统来源:https://www.cnblogs.com/seasonzone/p/13086562.html
内容总结
以上是互联网集市为您收集整理的历时七天,史上最强MySQL优化总结,从此优化So Easy!全部内容,希望文章能够帮你解决历时七天,史上最强MySQL优化总结,从此优化So Easy!所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。