数据库性能定位-慢sql定位、sql优化（docker容器实战）

安装好mysql数据之后，创建库的时候，要注意选择 字符集编码。如果没有选择好，你的库表存中文的时候，会字符集乱码。选择utf8mb4.

建表的时候，存储引擎

InnoDB、MyISAM

mysql5.7及以后数据库，表的默认存储引擎 InnoDB

从磁盘的读写速度而言，MyISAM的表  速度要快一些

InnoDB存储引擎，存储追求事务的准确性，准确率要比MyISAM要高。

数据库自身有很多配置参数，参数可以改变数据库的性能。

如何获取慢sql？

1、数据库的慢sql的阈值设置一下。 一般设置为1， 1秒钟。

数据库的配置文件中。 /etc/my.cnf 文件中

long_query_time=1

需要重启数据库

2、修改数据库慢sql的开关，开关打开

set global slow_query_log=ON

不能重启数据库

3、进行性能测试。

测试中，如果，接口的平均响应时间（接口的最大响应时间）超过1秒，这个时候，才可能有慢sql。

有慢sql了，就会自动记录到 slow_query_log_file这个参数配置的日志文件中。就从这个日志文件中，去获取sql就可以了。

4、 测试完成之后， 记得 关闭开关。

set global slow_query_log=OFF

SQL优化

• 在写on语句时，将数据量小的表放左边，小表写左边，大表写右边 
• where后面的条件尽可能用索引字段，复合索引时，最好按复合索引顺序写where条件 
• where后面有in语句，in字段的索引，最好放复合索引的后面，因为in的字段索引可能会失效 
• 模糊查询时，尽量用 常量开头，不要用%开头，用%开头查询索引将失效 
• 尽量不要使用 or， 否则索引失效 尽量不要使用类型转换(显式、隐式),否则索引失效 
• 如果主查询数据量大，则使用in；如果子查询数据量大，则使用exists 
• 查询哪些列，就根据哪些列group by，不然会产生一个临时表 

实战

第一种获取慢sql：传统的方法

设置数据库慢sql查询参数

1、先连接数据库查看慢sql开关是否开启

show variables like "show%";

2、查看慢sql的阈值是否设置

show variables like "long%";

查看数据库容器信息

docker ps

进入容器

docker exec -it centos7_mysql57 /bin/bash

查看数据库配置信息

cd etc
cd mysql
cat mysql.cnf 

从图中我们没有修改任何节点所以不用修改，但是下方有两个地址

进入mysql.conf.d/

cd mysql.conf.d/

查看mysql.cnf 文件内容

cat mysql.cnf 

修改配置文件就在这个里面修改

先将容器中的文件拷贝当前目录进行修改（容器中无法使用vi命令）

 docker cp centos7_mysql57:/etc/mysql/mysql.conf.d/mysqld.cnf $PWD

修改配置文件（需要备份下文件cp ）

cp mysqld.cnf mysqld01.cnf
vim mysqld.cnf

在文件末尾加上

long_query_time = 1

再将修改好的胚子文件拷贝会原地址

 docker cp mysqld.cnf centos7_mysql57:/etc/mysql/mysql.conf.d/mysqld.cnf 

再进入容器查看配置文件是否修改成功

cat mysqld.cnf 

退出容器

exit

重启数据库，配置才会生效

docker restart  centos7_mysql57

产看配置修改是否生效

show variables like "long%";

查看日志文件和慢日志开关

show variables like "slow%";

开启慢查询开关（重启数据库后设置失效）

set global slow_query_log = ON;

再查看设置是否生效

show variables like "slow%";

拷贝日志文件到本地查看

 docker cp centos7_mysql57:/var/lib/mysql/e2d0077fb342-slow.log $PWD

查看文件可以看出文件中无任何内容

cat e2d0077fb342-slow.log 

启动项目和性能脚本开始测试

从图中我们可以看出平均时间大于1s了，那么就怀疑有慢sql问题了

再次将数据库日志拷贝出来进行查看

 docker cp centos7_mysql57:/var/lib/mysql/e2d0077fb342-slow.log $PWD

cat e2d0077fb342-slow.log 

从日志中可以看出sql语句基本都一致，再将sql拷贝出来

select account0_.id as id1_0_, account0_.age as age2_0_, account0_.createtime as createti3_0_, account0_.email as email4_0_, account0_.gqid as gqid5_0_, account0_.identity as identity6_0_, account0_.lasttime as lasttime7_0_, account0_.mobile as mobile8_0_, account0_.money as money9_0_, account0_.password as passwor10_0_, account0_.pay_pwd as pay11_0_, account0_.platform as platfor12_0_, account0_.pmoney as pmoney13_0_, account0_.sex as sex14_0_, account0_.token as token15_0_, account0_.username as usernam16_0_ from kyj.cb_account account0_ where account0_.mobile='18797823533';

分析慢sql

将sql单独拿出来执行时，执行时没有并发，执行时间就很短，从表现来看就不是慢sql

SELECTaccount0_.id AS id1_0_,account0_.age AS age2_0_,account0_.createtime AS createti3_0_,account0_.email AS email4_0_,account0_.gqid AS gqid5_0_,account0_.identity AS identity6_0_,account0_.lasttime AS lasttime7_0_,account0_.mobile AS mobile8_0_,account0_.money AS money9_0_,account0_.PASSWORD AS passwor10_0_,account0_.pay_pwd AS pay11_0_,account0_.platform AS platfor12_0_,account0_.pmoney AS pmoney13_0_,account0_.sex AS sex14_0_,account0_.token AS token15_0_,account0_.username AS usernam16_0_ 
FROMkyj.cb_account account0_ 
WHEREaccount0_.mobile = '18797823533';

使用explain来进行解析

explain
SELECTaccount0_.id AS id1_0_,account0_.age AS age2_0_,account0_.createtime AS createti3_0_,account0_.email AS email4_0_,account0_.gqid AS gqid5_0_,account0_.identity AS identity6_0_,account0_.lasttime AS lasttime7_0_,account0_.mobile AS mobile8_0_,account0_.money AS money9_0_,account0_.PASSWORD AS passwor10_0_,account0_.pay_pwd AS pay11_0_,account0_.platform AS platfor12_0_,account0_.pmoney AS pmoney13_0_,account0_.sex AS sex14_0_,account0_.token AS token15_0_,account0_.username AS usernam16_0_ 
FROMkyj.cb_account account0_ 
WHEREaccount0_.mobile = '18797823533';

id是表示数据库从上往下开始执行

首先看  type值：

        ALL 是 全表扫描，  我们期望这个值为  ref或 eq_ref    配合rows值看。

                为ALL，第1反应，就是 是不是没有用 索引。

                        看key的值。 如果没有，肯定就没有用索引，还可以看 extra这个的值， Using where  使用了where条件来查询，但是where条件不是索引。

                where条件只有  mobile这个字段。 说明这个字段不是索引。

如何优化

建立索引

因为是注册接口，该sql语句需要查询手机号是否在数据库中存在所以可以把手机号mobile设置为唯一索引

或者通过sql添加索引

ALTER TABLE `kyj`.`cb_account` 
ADD INDEX `index_mobile` USING BTREE (`mobile`);

然后再运行刚刚的sql,可以通过key看出我们运用了新的索引，而且时间比以前的还短

再次运行性能脚本，可以明显看出平均响应时间降低，吞吐量增加了。

如果索引字段的顺序和查询的字段的顺序不一致，那么这个索引就没有使用成功

索引如下：

那么查询结果如下，通过key可以明显看出未使用索引

如何解决这种问题呢？

添加一个新的索引，字段的位置需要和查询语句的字段顺序一致。

查询结果引用索引如下：

详细的sql优化见上方sql优化内容

第二种获取慢sql的方法：企业的监控平台，日志分析平台

日志分析平台中，就会记录，每个事件的 时长  通过这个时长的倒序，就能找到，哪些事件时间是最长，找到这个事件用sql