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

一、优化分类

二、测试数据样例

参考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 表的水平拆分

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

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

原文发布于微信公众号 - 程序你好(codinghello)

原文发表时间:2018-06-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Danny的专栏

【MyBatis框架点滴】——mybatis插入不显示,但是主键自增了?

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/huyuyang6688/article/...

1742
来自专栏维C果糖

史上最简单的 MySQL 教程(二十二)「数据的高级操作 之 更新 & 删除」

此外,在删除记录的过程中,如果表中存在自增长的主键,那么删除之后,自增长不会还原。执行如下 SQL 语句,进行测试:

3528
来自专栏我的博客

PHP读取excel插入mysql数据库

php读取excel在网上找了n多办法,没有合适的。但是也有一定的收获,就是尽量实用类,不用odbc或者csv格式读取——因为它可以跨平台。各自的优缺点在这里都...

3424
来自专栏武培轩的专栏

MySQL存储引擎

定义 数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定...

3634
来自专栏有趣的django

13.MySQL(一) 数据库简介mysql安装数据库操作Mysql数据类型存储引擎

数据库简介 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库 RDBMS 即关系数据库管理系统(Relational Database Ma...

3416
来自专栏散尽浮华

mysql操作命令梳理(4)-grant授权和revoke回收权限

在mysql维护工作中,做好权限管理是一个很重要的环节。下面对mysql权限操作进行梳理: mysql的权限命令是grant,权限撤销的命令时revoke; g...

2965
来自专栏GopherCoder

sqlite 的分布式实现方案:rqlite

6154
来自专栏架构之路

mysql 通过慢查询日志查写得慢的sql语句

MySQL通过慢查询日志定位那些执行效率较低的SQL 语句,用--log-slow-queries[=file_name]选项启动时,mysqld 会写一个包含...

3784
来自专栏我是攻城师

如何在Elasticsearch里面使用索引别名

5599
来自专栏「3306 Pai」社区

MySQL 8.0用户和角色管理

MySQL8.0新加了很多功能,其中在用户管理中增加了角色的管理,默认的密码加密方式也做了调整,由之前的sha1改为了sha2,同时加上5.7的禁用用户和用户过...

2810

扫码关注云+社区

领取腾讯云代金券