MySQL优化
2019-09-09 11:01:26 5 举报AI智能生成
登录查看完整内容
MYSQL优化
作者其他创作
大纲/内容
MySQL优化
1、概述
为什么要优化
一个应用吞吐量瓶颈往往出现在数据库的处理速度上
随着应用程序的使用，数据库数据逐渐增多，数据库处理压力逐渐增大
关系型数据库的数据是存放在磁盘上的，读写速度较慢（与内存中的数据相比）
如何优化
表、字段的设计阶段，考量更优的存储和计算
数据库自身提供的优化功能，如索引
横向扩展，主从复制、读写分离、负载均衡和高可用
典型SQL语句优化（收效甚微）
2、字段设计
典型方案
对精度有要求
decimal
小数转整数
尽量使用整数表示字符串
IP
inet_aton(\"ip\")
inet_ntoa(num)
尽可能使用not null
null数值的计算逻辑比较复杂
定长和非定长的选择
较长的数字数据可以使用decimal
char为定长（超过长度的内容被截掉），varchar为非定长，text对内容长度的保存额外保存，而varchar对长度的保存占用数据空间
字段数不要过多、字段注释是必要的、字段命名见名思意、可以预留字段以备扩展
范式
第一范式
字段原子性（关系型数据库有列的概念，默认就符合了）
第二范式
消除对主键的部分依赖（因为主键可能不止一个）
使用一个与业务无关的字段作为主键
第三范式
消除对主键的传递依赖
高内聚，如商品表可分为商品简略信息表和商品详情表
3、存储引擎的选择（MyISAM和Innodb）
功能差异
Innodb支持事务、行级锁定、外键
存储差异
存储方式
表可移动性
可以通过移动表对应的MYI和MYD能够实现表的移动，而Innodb还有额外的关联文件
碎片控件
MyISAM删除数据时会产生碎片空间（占用表文件空间），需要定期通过optimizetable table-name手动优化，而Innodb不会。
有序存储
Innodb插入数据时按照主键有序来插入，因此表中数据默认按主键有序（耗费写入时间，因为需要在b+tree中查找插入点，但查找效率高）
选择依据
读多写少用MyISAM
新闻、博客网站
读多写也多用Innodb
支持事务/外键，保证数据一致性、完整性
并发能力强（行锁）
4、索引
什么是索引
从数据中提取的具有标识性的关键字，并且有到对应数据的映射关系
类型
主键索引 primary key
要求关键字唯一且不为null
普通索引 key
符合索引仅按照第一字段有序
唯一索引 unique key
要求关键字唯一
全文索引 fulltext key(不支持中文)
索引管理语法
查看索引
show create table student
desc student
建立索引
更改表结构
删除索引
alter table student drop key key_name
如果删除的是主键索引，并且主键自增长，则需要alter_modify 先取消自增长再删除
执行计划explain
分析SQL执行是否用到了索引，用到了什么索引
索引的使用场景
where
如果查找字段都建立了索引，则会索引覆盖
order by
如果排序字段建立了索引，而索引又是有序排列的，直接根据索引拿到对应数据即可，与读取查询出来的所有数据再排序相比效率很高
join
如果用join on的条件字段简历了索引，查找会变得高效
索引覆盖
直接对索引查找，而不去读取数据
语法细节
对数据表的改动会导致基于该数据表的所有缓存失效（表管理的层面）
即使建立了索引，有些场景也不一定使用
like 语句不要再关键字前模糊匹配，即'%keyword%'不会使用索引，而'keyword%'会使用索引
or关键两边条件字段都建立索引时才会使用索引，只要有一边不是就会做全表扫描
状态值，像性别这样的状态值，一个关键字对应很多条数据，会认为使用索引比全表扫描效率还低
索引的存储结构
btree
b+tree
由btree升级而来，数据和关键字存在一块空间，省去了由关键字到数据的映射找数据存放地的时间
5、查询缓存
将select查询结果缓存起来，key为SQL语句，value为查询结果
如果SQL功能一样，但只是多个空格或略微改动都会导致key的不匹配
客户端开启
query_cacha_type
0-不开启
2-开启，默认都不缓存，通过select sql-cache制定缓存哪一个
客户端设置缓存大小
query_cache_size
重置缓存
reset_query_cache
缓存失效
6、分区
默认情况下一张表对应一组存储文件，但当数据量较大时（通常千万条级别）需要将数据分到多组存储文件，保证单个文件的处理效率
partition by 分区函数（分区字段）（分区逻辑）
hash-分区字段为整型
key-分区字段为字符串
range-基于比较，只支持iess than
list-基于状态值
分区管理
创建时分区
create table article() partition by key(title) partitions 10
修改表结构
alter table article add partition(分区逻辑)
分区字段应选择常用的检索字段，否则区分意义不大
7、水平分隔和垂直分隔
水平
多张结构相同的表存储同一类型数据
单独一张表保证id唯一性
垂直
分割字段到多张表，这些表记录是一一对应关系
8、集群
主从复制
首先手动将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快速配置住处复制
读写分离（基于主从复制）
使用原生javax.sql.Connection
WriteDatabase提供写连接
ReadDatabase提供读连接
借助Spring_AOP和Aspect实现数据源动态切换
DatasourceAspect切面组件，配置切入点@Pointcut aspect()(所有DAO类的所有方法），配置前置增强@Before(\"aspect()\
DatasourceHandler，使用ThreadLoacal在前置通知中将方法要使用到的数据源绑定到执行该方法的线程上，执行方法要获取数据源时再根据当前线程获取
负载均衡
算法
轮询
加权轮询
依据负载情况
高可用
为单机服务提供一个冗余机
心跳监测
虚拟IP
9、典型SQL
线上DDL
为了避免长时间表级锁定
copy 策略，逐行复制，记录复制期间日表SQL日志重新执行
mysql5.6 online ddl ，大大缩短锁定时间
批量导入
先禁用索引和约束，导入之后统一建立
避免逐条事务
innodb为了保证一致性，默认为每条SQL加事务（也是要耗费时间的），批量导入前应手动建立事务，导入完毕后手动提交事务，
limit offset rows
避免较大的offset(较大页码数)
offset用来跳过数据，完全可以用过滤筛选数据，而不是查出来之后再通过offset跳过
select *
尽量查阅所需字段，减少网络传输延时（影响不大）
order by rand()
会为每条数据生成一个随机数量最后根据随机数排序，可以使用应用程序生成随机主键代替
limit 1
如果确定了仅仅检索一条数据，建议都加上limit 1
10、慢查询日志
定位查阅效率较低的SQL，针对性地做优化
配置项
开启slow_query_log
临界时间long_query_time
慢查询日志会自己记录超时临界时间的SQL，并保存在datadir下的xxx-slow.log中
11、profile
自动记录每条SQL的执行时间和具体某个SQL的详细步骤花费的时间
开启profiling
查看日志信息show profiles
查看具体SQL的详细步骤花费的时间
show profiles for query Query_ID
12、典型的服务器配置
max_connections，最大客户端连接数
table_open_cache，表文件缓存句柄数，加快表文件的读写
innodb_file_per_table，每个表一个ibd文件，否则innodb共享表空间
13、压测工具mysqlslap
自动生成sql并执行来测试性能
mysqlslap --auto-generate-sql -uroot -proot
并发测试
多轮测试，反应平均情况
存储引擎测试
--engine=innodb
--engine=myisam
0 条评论
回复 删除
下一页
