MySQL数据优化总结-查询备忘录
source link: http://database.51cto.com/art/201806/576608.htm?amp%3Butm_medium=referral
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
一、优化分类
二、测试数据样例
参考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 表的水平拆分
前台用拆分后的表,后台用汇总表
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK