MYSQL数据库优化

使用MySQL慢查询日志对有效率问题的SQL进行监控

• show variables like 'slow_query_log'; #查询慢日志是否开启
• show variables like '%log%';
• set global slow_query_log_file = '/home/mysql/sql_log/mysql-slow.log; #设置慢日志文件
• set global log_queries_not_using_indexes = on ; 设置未使用索引的sql记录慢日志中
• set global long_query_time = 1;查询大于1秒钟的sql记录在慢日志中

慢查日志的分析工具--mysqldumpslow输出

•  mysqldumpslow -t 3 /路径

慢查日志的分析工具

• 输出到文件 pt-query-digest slow-log > slow_log.report
• 输出到数据库表 pt-query-digest slow.log -review h=127.0.0.1,D=test,p=root,P=3306,u=root,t=query_review   --create-reviewtable   --review-history t=hostname_slow

如何通过慢查日志发现有问题的SQL？

1. 查询次数多且每次查询占用时间长的SQL（通常为pt-query-digest分析的前几个查询）
2. IO大的SQL（注意pt-query-digest）分析中的Rows examine项
3. 未命中索引的SQL（注意pt-query-digest分析中Rows examine 和Rows Send的对比）

使用explain查询SQL的执行计划

explain返回各列的含义

• table：显示这一行的数据是关于哪张表的
• type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL
• possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。
• key：实际使用的索引。如果为NULL，则没有使用索引。
• key_len：使用的索引长度。在不损失精确性的情况下、长度越短越好
• ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
• rows：MYSQL认为必要检查的用来返回请求数据的行数
• extra：列需要注意的返回值

1. Using filesort：看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的指针来排序全部行
2. Using temporary：看到这个时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Count()和Max()的优化方法

• 在一条SQL同时查出2006年2007年电影的数量(count函数某一列时，不会统计null值)
• select count(release_year = '2006' or null) as '2006年电影数据量', count(release_year='2007' or null) as '2007年电影数量' from film;

子查询的优化

• 通常情况下，需要把子查询优化为join查询，但在优化时要注意关联键是否有一对多的关系，要注意重复数据。

优化group by查询

• select actor.first_name, actor.last_name, count(*) from film_actor inner join actor using(acotr_id) group by film_actor.actor_id;
• 优化后：select actor.first_name, actor.last_name, c.cnt from actor inner join (select actor_id,count(*) as cnt from film_actor group by actor_id) as c using (actor_id);

优化limit查询

limit常用语分页处理，时常会伴随order by从句使用，因此大多数时候会使用Filesorts这样会造成大量的IO问题

select film_id, description from film order gy title limit 50, 5;

1. 使用有索引的列或主键进行order by操作
2. 记录上次返回的主键，在下次查询时使用主键过滤

  如何选择合适的列建立索引?

1. 在where从句，group by从句，order by从句，on从句中出现的列
2. 索引字段越小越好
3. 离散度大的列放到联合索引的前面

索引的维护及优化--重复及冗余索引

• 重复索引是指相同的列以相同的顺序建立得同类型的索引，如下表中primarykey和id列上的索引就是重复索引  create table test(id int not null primay key, name varchar(10) not null, unique(id)) engine=innodb;
• 冗余索引是指多个索引的前缀相同，或是在联合索引中包含了主键的索引，下面这个例子中key(name,id)就是冗余索引 create table test(id int not null primay key, name varchar(10) not null, key(name,id)) engine=innodb;

索引的维护及优化--查找重复及冗余索引

• SELECT a.table_schema AS '数据名', a.table_name AS '表名', a.index_name AS '索引1', b.index_name AS '索引2', a.column_name AS '重复列名' FROM statistics a JOIN statistics b ON a.table_schema = b.TABLE_SCHEMA AND a.`TABLE_NAME`= b.`TABLE_NAME` AND a.`SEQ_IN_INDEX` = b.`SEQ_IN_INDEX` AND  a.`COLUMN_NAME` = b.`COLUMN_NAME` WHERE a.`SEQ_IN_INDEX` = 1 AND a.`INDEX_NAME` <> b.`INDEX_NAME`
• 使用 pt-duplicate-key-checker工具检查重复及冗余索引 pt-duplicate-key-checker -uroot -p '' -h 127.0.0.1

索引的维护及优化--删除不用索引

• 目前MySQL中还没有记录索引的使用的情况，但是在PerconMySQL和MariaDB中可以通过INDEX_STATISTICS表来查看哪些索引未使用，但在MySQL中目前只能通过慢查日志配合pt-index-usage工具来进行索引使用情况的分析
• pt-index-usage -uroot -p''   mysql-slow.log

选择合适的数据类型

• 使用可以存下你的数据的最小的数据类型
• 使用简单的数据类型。Int要比varchar类型在mysql处理上简单

1. 使用int来存储日期时间，利用FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数来进行转换 create table test(id int auto_increment not null, timestr int, primary key(id)); inerst into test(timestr) values(UNIX_TIMESTAMP('2020-08-30 12:20:00)); select FROM_UNIXTIME(timestr) test;
2. 使用bigint来存储ip地址，利用INET_ATON(), INET_NTOA()两个函数来进行转换 CREATE TABLE sessions (id int auto_increment not null, ipaddress bigint, primary key(id)); inerst into sessions(ipaddress) values(INET_ATON('192,168.0.1)); select INET_NTOA(ipaddress) sessions;

• 尽可能的使用not null定义字段
• 尽量少用text类型，非用不可的时最好考虑分表

表的范式化和反范式化

• 不符合第三范式要求的表存在下面问题

1. 数据冗余
2. 数据的插入异常
3. 数据的更行异常
4. 数据的删除异常

• 反范式化是指为了查询效率的考虑把原本符合第三范式的表适当的增加冗余，以达到优化查询效率的目的，反范式化是一种以空间来换取时间的操作。

表的垂直拆分：所谓的垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。通常垂直拆分可以按一下原则：

• 把不常用的字段单独存放到一个表中。
• 把大字段独立存放到一个表中。
• 把经常一起使用的字段放到一起。

表的水平拆分：表的水平拆分是为了解决单表数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的。

常用的水平拆分方法为：

1. 对表主键进行hash运算，如果要拆分5个表则使用mod(id, 5)取出0-4个值
2. 针对不同hashID把数据存到不同的表中

操作系统配置优化->数据库是基于操作系统的，目前大多数MySQL都是安装在Linux系统之上，所以对于操作系统的一些参数配置也会影响到MySQL的性能。

网络方面的配置，要修改/etc/sysctl.conf文件

• #增加tcp支持的队列数
• net.ipv4.tcp_max_syn_backlog = 65635
• #减少断开连接时，资源回收
• net.ipv4.tcp_max_tw_buckets = 8000
• net.ipv4.tcp_tw_reuse = 1
• net.ipv4.tcp_tw_recycle = 1
• net.ipv4.tcp_fin_timeout = 10

打开文件数的限制，可以使用ulimit -a查看目录的限制，可以修改/etc/security/limits.conf文件，增加以下内容以吸怪打开文件数量的限制

• soft nofile 65535
• hard nofile 65535
• 除此之外最好在MYSQL服务器上关闭iptables,selinux等防火墙软件

MYSQL可以通过启动时指定配置参数和使用配置文件两种法法进行配置，在大数情况下配置文件位于/etc/my.cnf或是/etc/mysql/my.cnf在windows系统配置文件可以是C:/windows/my.ini文件，MYSQL查找配置文件顺序可以通过以下方法获取：$ /usr/sbin/mysqld --verbose --help |grep -A 1 'Default options '

注意：如果存在多个位置存在配置文件，则后面的会覆盖前面的

mysql配置文件--常用参数说明

• innodb_buffer_pool_size 非常重要的一个参数，用于配置Innodb的缓冲池如果数据库中只有Innodb表，则推荐配置总量为总内存的75%

1. SELECT ENGINE, ROUND(SUM(data_length + index_length)/1024/1024, 1) AS 'Total MB' FROM  information_schema.`TABLES` WHERE table_schema NOT IN ('information_schema', 'performance_schema') GROUP BY ENGINE;
2. innodb_buffer_pool_size >= Total MB

• innodb_buffer_pool_instances mysql5.5新增加参数，可以控制缓冲池的个数，默认情况下只有一个缓冲池。
• innodb_log_buffer_size innodb log 缓冲的大小，由于日志最长每秒就会刷新所以一般不用太大
• innodb_flush_log_at_trx_commit 关键参数，对innodb的IO效率影响很大。默认为1，可以取0,1,2三个值，一般建议设为2，但如果数据安全性要求比较高则使用默认值1.
• innodb_read/write_io_threads  以上两个参数决定innodb读写的IO进程数，默认为4
• innodb_file_per_table 关键参数，控制innodb每一表使用独立的表空间，默认为OFF，也就是所有的表都会建立一个共享的表空间中
• innodb_stats_on_metadata 决定了MYSQL在什么情况下回刷新innodb表的统计信息

第三方配置工具

Percon Configuration Wizard  https://tools.percona.com/wizard