MySQL数据优化总结-查询备忘录

MySQL数据优化总结-查询备忘录

www.toutiao.com   2018-06-19 16:49

MySQL数据优化总结-查询备忘录

参考mysql官方的sakina数据库。

MySQL数据优化总结-查询备忘录

第一个，开启慢查询日志。第二个，慢查询日志存储位置。第三个，没有使用索引的也会记录到慢查询日志中。第四个，超过1秒之后的查询记录到慢查询日志中（通常设置100ms）。

3.1、分析慢查询日志文件

3.1.1 tail命令

tail -50 /home/mysql/sql_log/mysql_slow.log，输入文件中的尾部内容，即末尾50行数据.

我们抽出其中一条，查看，如下图所示。

MySQL数据优化总结-查询备忘录

MySQL数据优化总结-查询备忘录

query_time,查询耗时(单位秒)；lock_time，锁表时间。rows_sent,发送请求的行数；rows_examined，查询数据导致扫描表用到的行数。

3.1.2 官方mysqldumpslow工具

mysqldumpslow ，默认随mysql安装。

mysqldumpslow -h，可查询工具支持的命令。

MySQL数据优化总结-查询备忘录

mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log | more ，返回结果如下图所示。

MySQL数据优化总结-查询备忘录

3.1.3 pt-query-digest工具

比mysqldumpslow反馈的信息多。

MySQL数据优化总结-查询备忘录

pg-query-digest --help 查看帮助，查看使用命令。

pg-query-digest /var/lib/mysql/localhost-slow.log，查询结果如下。

MySQL数据优化总结-查询备忘录

第一部分

MySQL数据优化总结-查询备忘录

第二部分

MySQL数据优化总结-查询备忘录

第三部分

MySQL数据优化总结-查询备忘录

MySQL数据优化总结-查询备忘录

const常数查找，一般来说，针对主键和唯一索引；eq_reg，一般主键或是唯一索引范围查找；ref，常见于连接查询中；range,对于索引的范围查找；

index，对于索引的扫描；all，表扫描。

MySQL数据优化总结-查询备忘录

MySQL数据优化总结-查询备忘录

1、max()优化

在payment_date上建立索引

MySQL数据优化总结-查询备忘录

建索引后的查询结果

可以看出，直接通过索引结构，就能查询出最大日期。覆盖索引，是指完全可以通过索引获得查询结果。

2、count()优化

count(*)包含null值，count(id)不包含

错误写法：

MySQL数据优化总结-查询备忘录

正确写法：

MySQL数据优化总结-查询备忘录

一对多的子查询，注意dinstinct

MySQL数据优化总结-查询备忘录

MySQL数据优化总结-查询备忘录

优化前

MySQL数据优化总结-查询备忘录

优化前

MySQL数据优化总结-查询备忘录

优化后

MySQL数据优化总结-查询备忘录

优化后

MySQL数据优化总结-查询备忘录

优化后，减少io，提高效率，节省服务器资源

灵活使用子查询和连接查询

MySQL数据优化总结-查询备忘录

MySQL数据优化总结-查询备忘录

缺点：分页limit越往后，扫描行数越多，io操作越大

MySQL数据优化总结-查询备忘录

缺点：id连续。主键连续增长，分页查询更快

MySQL数据优化总结-查询备忘录

如果是覆盖索引，可直接从索引结构中获取数据，这样最快；索引字段越小，数据库数据存储以页为单位，每次io所获取的数据量就大。

通过select count（dinstinct customer_id）查看离散度。离散度大的列，可选择性越高。

索引提高查询，但是会影响inset，update，delete。

MySQL数据优化总结-查询备忘录

MySQL数据优化总结-查询备忘录

MySQL数据优化总结-查询备忘录

4.1 选择合适的数据类型

MySQL数据优化总结-查询备忘录

时间类型上，时间戳和int占用字节相同；not null需要额外字段存储，

MySQL数据优化总结-查询备忘录

MySQL数据优化总结-查询备忘录

bigint8个字节，varchar15个字节

4.2 数据库的范式化优化

MySQL数据优化总结-查询备忘录

MySQL数据优化总结-查询备忘录

4.4表的垂直拆分

MySQL数据优化总结-查询备忘录

例如，将新闻表的内容拆分到单独一个表

4.5 表的水平拆分

MySQL数据优化总结-查询备忘录

MySQL数据优化总结-查询备忘录

前台用拆分后的表，后台用汇总表

总结的很随意，纯粹方便查看知识点