MySQL性能分析及SQL优化

一、性能分析

1.查看执行频率

        MySQL客户端连接成功后，通过SQL命令可以提供服务器状态信息。通过以下指令，可以查看当前数据库的insert，update，select、delete的访问频次：

#session当前会话
#global服务器
#七个下划线  原因：通过七个下划线匹配insert、update、delete、select，这样方便查看数据
show [session/global] status like 'com_______';

2.慢查询日志

        慢查询日志记录了所有执行时间超过指定参数（long_query_time,单位：秒，默认10秒）的所有SQL语句的日志。

#查看是否开启慢查询日志
show variables like 'slow_query_log';

        MySQL的慢查询日志默认没有开启,需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息（注意：配置需写在 [mysqld] 下面，否则会失效）：

#开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志的时间为4秒，SQL语句执行时间超过4秒，就会被视为慢查询，记录慢查询日志
long_query_time=2

        重启MySQL：

 service mysqld restart

        如果出现 '[Warning] World-writable config file '/etc/my.cnf' is ignored.'警告则为my.cnf权限过高，修改my.cnf权限为644之后重启MySQL即可。

        修改语句如下：

#修改my.cnf权限为644
chmod  0644 /etc/mysql/my.cnf

        配置完毕后，通过指令重启MySQL服务器进行测试，查看慢日志文件中记录的信息

/var/lib/mysql/loacal-slow.log

3.profile详情

        show profile 能够在做SQL优化时帮助我们了解时间都耗费到哪了。需要通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

select @@have_profiling;

        默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：

#查看profiling
select @@profiling;
#打开profiling
set profiling=1;

        执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：

#查看每一条SQL的耗时基本情况
show profiles;

#查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

#查看指定定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

4.explain执行计划（重要）

        explain或者desc命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接顺序。

#直接在select语句前加上关键字explain或者desc即可
explain select 字段列表 from 表名 where 条件;

• id: select 查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）。
• select_type: 表示select的类型，常见的值有simple（简单表，即不使用表连接或者子查询）、primary（主查询，即外层的查询）、union（union中的第二个或者后面的查询语句）、subquery（select/where之后包含了子查询）等。
• type:表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。通过主键或者唯一索引访问时会出现const，使用非唯一性索引时会出现ref，all为全表扫描。
• possible_key:显示可能应用在这张表上的索引，一个或者多个。
• key:实际用到的索引，如果为null，则没有使用索引。
• key_len：索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
• row：MySQL认为必须要执行查询的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确。
• filtered:表示返回结果的行数占需读取行数的百分比。

二、SQL优化

1.插入数据

• 批量插入

insert into tb_test values(1,'Tom'),(2,'Jerry')...;

• 手动提交事务

start transaction;
insert into tb_test values(1,'Tom'),(2,'Jerry'),...;
insert into tb_test values(4,'Tom'),(5,'Jerry'),...;
insert into tb_test values(71,'Tom'),(72,'Jerry'),...;
commit;

• 主键顺序插入
• 大批量插入数据：如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下（左侧为本地磁盘文件）：

#客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p
#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
#执行load指令将准备好的数据，加载到表结构中
load data local infile '路径/xxx.sql' into table '表名' fields terminated by '文件中的分隔符' lines terminated  by '\n';

注意：主键顺序插入性能高于乱序插入

2.主键优化

• 数据组织方式：

        在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（IOT）。

• 主键设计原则：

        满足业务需求的情况下，尽量降低主键长度；

        插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键；

        尽量不要使用UUID做主键或者其他自然主键（乱序插入可能会引起页分裂），如身份证号；

        业务操作时，避免对主键的修改；

3.order by优化

        （1）Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后再排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫filesort排序。

        （2）Using index：通过有序索引顺序排序扫描直接返回有序数据，这种情况为using index，不需要额外排序，操作效率高。

        优化方式：

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则；
• 尽量使用覆盖索引；
• 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）；
• 如果不可避免的出现filesort，大数据量排序时，可以适当增加排序缓冲区大小sort_buffer_size (默认256k)；

4.group by优化

• 在分组操作时，可以通过索引来提高效率；
• 分组操作时，索引的使用也满足最左前缀法则；

5.limit优化

        limit的问题在于资源的浪费，例如：limit 2000000，10时，此时MySQL需要排序前2000010条记录，但是又仅仅需要返回10条记录，其余记录则抛弃，查询排序的代价非常大。

        优化思路：

        一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

6.count优化

• MyISAM引擎把一个表的总行数存放在了磁盘中，因此执行count（*）的时候（注：不执行where）会直接返回这个数，效率很高。
• InnoDB引擎执行count（*）的时候，需要把数据一行行地从引擎里面读出来，然后累积计数。

        优化思路：

        自己计数，使用Redis，在插入或删除时，对值进行维护。比较麻烦。

         按照效率排序：count（字段）<count（主键）<count(1)<count（*）

7.update优化

        InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。在update时，尽量通过判断索引字段进行更新，并且该索引不能失效，否则会从行锁升级为表锁。