一、优化分类
二、测试数据样例
参考mysql官方的sakina数据库。
三、使用mysql慢查询日志对有效率问题的sql进行监控
第一个,开启慢查询日志。第二个,慢查询日志存储位置。第三个,没有使用索引的也会记录到慢查询日志中。第四个,超过1秒之后的查询记录到慢查询日志中(通常设置100ms)。
3.1、分析慢查询日志文件
3.1.1 tail命令
tail -50 /home/mysql/sql_log/mysql_slow.log,输入文件中的尾部内容,即末尾50行数据.
我们抽出其中一条,查看,如下图所示。
query_time,查询耗时(单位秒);lock_time,锁表时间。rows_sent,发送请求的行数;rows_examined,查询数据导致扫描表用到的行数。
3.1.2 官方mysqldumpslow工具
mysqldumpslow ,默认随mysql安装。
mysqldumpslow -h,可查询工具支持的命令。
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log | more ,返回结果如下图所示。
3.1.3 pt-query-digest工具
比mysqldumpslow反馈的信息多。
pg-query-digest --help 查看帮助,查看使用命令。
pg-query-digest /var/lib/mysql/localhost-slow.log,查询结果如下。
第一部分
第二部分
第三部分
四、如何通过慢查日志发现有问题的sql
五、通过explain查询和分析sql的执行计划
const常数查找,一般来说,针对主键和唯一索引;eq_reg,一般主键或是唯一索引范围查找;ref,常见于连接查询中;range,对于索引的范围查找;
index,对于索引的扫描;all,表扫描。
六、count()和max()的优化
1、max()优化
在payment_date上建立索引
建索引后的查询结果
可以看出,直接通过索引结构,就能查询出最大日期。覆盖索引,是指完全可以通过索引获得查询结果。
2、count()优化
count(*)包含null值,count(id)不包含
错误写法:
正确写法:
七、子查询的优化
一对多的子查询,注意dinstinct
八、group by的优化
优化前
优化前
优化后
优化后
优化后,减少io,提高效率,节省服务器资源
灵活使用子查询和连接查询
九、limit查询的优化
缺点:分页limit越往后,扫描行数越多,io操作越大
缺点:id连续。主键连续增长,分页查询更快
十、如何选择合适的列建立索引
如果是覆盖索引,可直接从索引结构中获取数据,这样最快;索引字段越小,数据库数据存储以页为单位,每次io所获取的数据量就大。
通过select count(dinstinct customer_id)查看离散度。离散度大的列,可选择性越高。
十一、索引优化SQL的方法
索引提高查询,但是会影响inset,update,delete。
4、数据库表结构优化
4.1 选择合适的数据类型
时间类型上,时间戳和int占用字节相同;not null需要额外字段存储,
bigint8个字节,varchar15个字节
4.2 数据库的范式化优化
4.4表的垂直拆分
例如,将新闻表的内容拆分到单独一个表
4.5 表的水平拆分
前台用拆分后的表,后台用汇总表
总结的很随意,纯粹方便查看知识点
领取专属 10元无门槛券
私享最新 技术干货