mysql千万级数据如何优化？

环境：win10 + mysql5.5 + phpStudy2016

问题：某一个或多个表中数据超过千万，查询缓慢，页面卡顿，想要做数据库优化

解决方案：

1.使用如下命令：查看mysql设置

show variables like 'slow_query_log'

如果是：`slow_query_log off` ，执行如下：

set global log_queries_not_using_indexes=on; # 是否记录不使用索引的查询
set global long_query_time=10; # 默认10s，执行时间超过10秒(时间根据自己需求)的查询才能记录到日志
set global slow_query_log=on; # 开启慢查询日志

2.开启慢查询后1天(时间看自己需求)后，关闭慢查询。

set global slow_query_log=off; # 关闭慢查询

查看日志位置：

show variables like 'slow%'; # 查看慢日志相关设置

查看 slow_query_log_file 的值，即是日志文件的物理位置。

3.打开日志，对日志进行分析，查找其中出现频率较高的sql语句。

日志格式如下：

# Time: 201215 16:08:54  # 执行的时间点
# User@Host: root[root] @ localhost [127.0.0.1] # 执行主机
# Query_time: 0.084947  Lock_time: 0.000000 Rows_sent: 10000  Rows_examined: 10000 # 执行情况
use lantian10000; # 使用数据库
SET timestamp=1608019734; # 执行的时间戳
select * from income order by id desc limit 10000; # 具体的sql语句

日志数据量很大的情况下，使用工具进行日志分析，工具有两个：mysqldumpslow 和 pt-query-digest

这两个工具都需要环境支持 perl 运行（到处是坑），两个办法：

(1)百度下载一个名为 cmder 的工具，集成有 perl ，这个工具可以看作是 cmd + power shell + git + linux bash 的合体，支持大部分的linux命令。

官网 https://cmder.en.softonic.com/ 下载后解压即可使用，可能会提示需要升级！若不能打开官网，就自己百度下载了。笔者使用是这种方式。

(2)下载名为 ActivePerl 的工具，安装。

任选其一，然后命令行输入：perl -h ，能显示 perl 命令参数介绍，即可。若是 linux系统 ，那上边这步就可以省了。

回到日志分析工具：

(1)完整版的mysql自带的分析工具：mysqldumpslow

可惜笔者使用的不是完整的mysql，没有这个东西。百度下载 mysqldumpslow.pl (单个文件)，解压放入 phpStudy目录/mysql/bin 。

打开 cmder ，切换到  phpStudy目录/mysql/bin ，执行命令：

perl mysqldumpslow -h

查看 mysqldumpslow 命令详细的参数

案例1：查看耗时长前三的记录：perl mysqldumpslow -t 3 f:/php/phpstudy/mysql/data/pc-master-slow.log | more

案例2：得到返回记录最多的20个sql 并记录bb.log 中：perl mysqldumpslow -s r -t 20 f:/php/phpstudy/mysql/data/pc-master-slow.log > f:/bb.log

案例3：得到平均访问次数最多的20条sql：perl mysqldumpslow-s ar -t 20  f:/php/phpstudy/mysql/data/pc-master-slow.log

如果遇到如下错误，Died at f:/pc-master-slow.log line 161, <> chunk 405659.说明你要分析的sql日志太大了，请拆分后再分析

拆分的命令为：tail -100000 mysql-slow.log>mysql-slow.20180725.log

(2) pt-query-digest 需要单独下载，非mysql自带工具。

打开 cmder ，切换到 phpStudy目录/mysql/bin， 执行：curl -o pt-query-digest https://www.percona.com/get/pt-query-digest

下载成功后，可以在目录下看到 pt-query-digest 文件，无后缀，517K大小（笔者版本）。

先看看怎么使用这个工具了：perl pt-query-digest --help 查看参数介绍。

实用示例：

a) pt-query-digest分析慢查询日志

perl pt-query-digest --report f:/php/phpstudy/mysql/data/pc-master-slow.log

b) 报告最近半个小时的慢查询：

perl pt-query-digest --report --since 1800s f:/php/phpstudy/mysql/data/pc-master-slow.log

c) 报告一个时间段的慢查询：

perl pt-query-digest --report --since '2013-02-10 21:48:59' --until '2013-02-16 02:33:50' f:/php/phpstudy/mysql/data/pc-master-slow.log

d) 分析general log：

perl pt-query-digest --type=genlog localhost.log > slow_report.log

e) 输出到文件：

perl pt-query-digest f:/php/phpstudy/mysql/data/pc-master-slow.log > slow.report

通过日志查找：

(1)查询次数多且每次查询占用时间长的SQL。通常就是 pt-query-digest 分析的前几个查询。

(2)IO大的SQL。注意 pt-query-digest 分析中 rows examine 项。该项值越大，代表sql语句扫描的行数越多。

(3)未命中索引的SQL。注意 pt-query-digest 分析中 rows examine 和 rows send 的对比。rows examine 越大，rows send 越小，代表sql语句效率越低，需要优化。

找到需要优化的sql语句，然后：

explain select * from income order by id limit 10;

查看sql语句的执行计划，返回结果类似：

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | income | index | NULL          | PRIMARY | 4       | NULL |   10 |       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+

各列含义：

table ：显示这行数据是关于那张表
type：显示连接使用了哪种类型从最好到最差，类型为const，eq_reg,ref,range,index,all
possible_keys显示可能应用的索引，为空表示没有可能的索引，
key：实际使用的索引，如果为null，表示没有使用索引，
key_len：使用的索引长度，在不损失精确性的情况下，长度越短越好
ref：显示索引的那一列被使用了，
rows：mysql认为必须检查的用来返回请求数据的行数（表扫描的行数）
extra列需要注意的返回值
Using filesort：看到这个的时候，查询就需要优化，mysql需要进行额外的步骤来发现如何返回的行排序，根据连接类型以及存储的排序键值和匹配条件的全部行的行指针来排序全部行（文件排序 orde by）
Using temporary 看到这个的时候，查询需要优化了，这里，mysql需要创建一个临时表来存储结果，通常在使用order by 上

4. 根据 explain 返回结果，对sql语句进行优化。

(一)针对SQL语句优化
(1)sql语句中尽量使用索引字段，避免使用非索引字段。
(2)count()优化，使用 select count(year='2020' or null) from film 替代 select count(*) from film where year='2020'。
只有 值为null时，count才不统计，其它值比如 0 false都会被统计进最终的结果中。
(3)子查询优化成 join 查询，但是要注意使用 join 时关联键是否有一对多的关系，此时可能会存在重复数据，可以使用 distinct 去重。
(4)group by 优化，例如下边的 sql语句：

explain select actor.first_name,actor.last_name,count(*) from film_actor inner join actor using(actor_id) group by film_actor.actor_id;

查看结果后发现，执行时使用了 Using filesort 和 Using temporary ，严重影响了效率，可以改为：

explain 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);

(5)limit优化，例如：

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

查看分析结果发现，using filesort ，有效率问题，可以改为：

explain select film_id,description from film order by film_id limit 50,5; # 扫描55行
explain select film_id,description from film where film_id>55 film_id<=60 order by film_id limit 1,5; #避免了数据量大时扫描过多记录

(二)索引优化

(1)如何选择合适的列创建索引？

a) where从句，group by从句，order by从句，on 从句中出现的列。
b) 索引字段越小越好。
c) 离散度大的列要放在联合索引的前边。
例如：select * from payment where staff_id=2 and customer_id=584;
是 index(staff_id,customer_id) 好 还是  index(customer_id,staff_id) 好？由于 customer_id 离散度更大，所以 index(customer_id,staff_id) 更好！

(2)避免创建重复和冗余索引

索引可以加速查询，但是影响写入，不是越多越好，每个表最多不要超过6个索引。
重复索引指的是在相同列上以相同顺序创建的相同类型的索引。例如：
id int not null primary key，
unique(id) 即是重复索引
冗余索引指的是多个索引的前缀列相同，或者 在联合索引中包含了主键索引。例如：
id int not null primary key，
name varchar(20) not null default '',
key(name,id)   # 冗余索引
查找重复和冗余索引：两种方法，选其一
a)使用sql语句

use information_schema;
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_schename=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 \G

b) 使用 pt-duplicate-key-checker 工具检查
pt-duplicate-key-checker -uroot -p '123456' -h 127.0.0.1

(3)索引的维护及优化

删除不用的索引：通过慢查询日志配合 pt-index-usage 工具来进行索引使用情况分析。
pt-index-usage -uroot -p '123456' mysql-slow.log

注意：sql语句优化和索引优化是优化的重点。
percona-toolkit 工具集可以去 https://www.percona.com/downloads/percona-toolkit 查看下载。

5. 数据库结构优化

(1) 选择 合适 的数据类型

a) 选择可以存下数据的最小的数据类型。
b) 使用简单的数据类型。int 要比 varchar 在mysql处理上更简单。
c) 尽可能使用 not null 定义字段。
d) 尽量少用 text 类型，非用不可时最好考虑分表。

使用 int 来存储日期时间，利用 from_unixtime()  unix_timestamp() 两个函数进行转换。
使用 bigint 存储ip地址，利用 inet_aton() inet_ntoa() (linux)两个函数进行转换。

(2) 表的范式化和反范式化

表的范式化设计一般是指第三设计范式，即表中不存在非关键字段对任何候选关键字段的传递函数依赖，符合这个规则就是符合范式化设计。
反范式化就是为了提高查询效率，把原本符合第三范式的表适合增加冗余，以达到优化查询效率的目标，即拿空间换取时间的操作。例如：

select b.用户名,b.电话,b.地址,a.订单ID,sum(c.商品价格*c.商品数量) as 订单价格
from `订单表` a  
    join `用户表` b on a.用户ID=b.用户ID 
    join `订单商品表` c on c.订单ID=b.订单ID  
group by b.用户名,b.电话,b.地址,a.订单ID

可以在订单表中增加 用户名,电话,地址,订单ID 四个字段，然后上边的sql语句就转变成 

select a.用户名,a.电话,a.地址,a.订单ID,a.订单价格
from `订单表` a
   

(3)表的垂直拆分

就是把一个有很多列的表拆分成多个表，解决了表宽度问题。拆分时原则如下：
a) 把不常用的字段单独存放到一个表。
b) 把大字段独立存放到一个表。
c) 把经常一起使用的字段存放到一个表。

(4)表的水平拆分

水平拆分指的是将数据分别存储在字段相同名称相似的一系列的表中。例如：
顾客表，对 customer_id 进行 hash 运算，如果要拆分成5个表，使用 mod(customer_id,5) 取出0-4个值；针对不同的 hashID 把数据存到不同的表中。
可能会遇到的挑战性问题：
a) 跨分区表进行数据查询
b) 统计及后台报表操作

6.系统配置优化

(1)操作系统配置优化

操作系统的配置会影响到mysql的性能，大多数mysql都是运行于linux系统。linux下常用的优化配置如下：
a) 网络方面的配置，修改 /etc/sysctl.conf文件
# 增加tcp支持的队列数
net.ipv4.tcp_max_syn_backlog = 65535
# 系统同时保持TIME_WAIT套接字的最大数量，连接数过多导致服务器运行缓慢
net.ipv4.tcp_max_tw_buckets = 8000
# 允许将TIME-WAIT sockets重新用于新的TCP连接，默认为0，表示关闭
net.ipv4.tcp_tw_reuse = 1
# 开启TCP连接中TIME-WAIT sockets的快速回收，默认为0，表示关闭
net.ipv4.tcp_tw_recycle = 1
# 表示如果套接字由本端要求关闭，这个参数决定了它保持在FIN-WAIT-2状态的时间
net.ipv4.tcp_fin_timeout = 10
b) 打开文件数的限制，mysql数据是以文件方式存储，操作系统的这个配置会影响mysql的性能。
可以使用 ulimit -a 显示当前所有的资源限制。
修改 /etc/security/limits.conf ，重设打开文件数量的限制，改善mysql性能，增加如下内容：
soft nofile 65535
hard nofile 65535
初次之外，最好关闭mysql服务器上的iptables,selinux等防火墙软件，尽量使用硬件防火墙。

(2)mysql配置文件优化

mysql可以通过启动时指定配置参数、使用配置文件两种方式进行配置，linux系统大多数情况下配置文件位于 /etc/my.cnf 或者 /etc/mysql/my.cnf ；
windows系统下单独安装的mysql配置文件位于 c:/windows/my.ini 。linux下mysql查找配置文件的顺序可以通过一下方法获得：

$ /usr/sbin/mysqld --verbose --help | grep -A l 'Default options'

如果多个位置存在配置文件，则后面的会覆盖前面的。
几个重要mysql配置：
a) innodb_buffer_pool_size  用于配置innodb的缓冲池，很重要的一个参数。若数据库中只有innodb表，则推荐配置值为总内存的75%。若内存太小，就尽可能的大点。
可以通过如下sql语句查看innodb表数据+索引的大小：

select engine,round(sum(data_length + index_length)/1024/1024,1) as 'Total MB' 
from information_schema.talbes where table_schema not in ("information_schema","performance_schema") group by engine;

根据显示结果，innodb_buffer_pool_size >= Total MB 设置这个参数的值。
b) innodb_buffer_pool_instance 这个参数是mysql5.5以后新增的参数，用于设置缓冲池的个数，默认情况下只有一个缓冲池。
c) innodb_log_buffer_size 指的是innodb log缓冲的大小，由于日志最长每秒钟就会刷新所以一般不用设置太大。
d) innodb_flush_log_at_trx_commit 指innodb的redo日志写入磁盘的方式，重要配置项。三个值：0，1，2，默认值是1。0代表不立即把redo写入磁盘，而依靠innodb的主线程每秒执行一次刷新到磁盘。1代表事务提交成功，立即写入磁盘。2代表redo日志写入到操作系统缓存区，而不是直接写入磁盘。一般建议该配置项设置为2，若安全性要求比较高则设置为1。
e) innodb_read_io_threads 和 innodb_write_io_threads ，指innodb读写的线程数，默认都是4，根据主机cpu资源、数据库读概率大还是写概率大来设置。
f) innodb_file_per_table 关键参数，设置innodb每个表是否使用独立的空间，默认off，即所有的表会建立在共享表空间，顺序读写，建议设置为on，加快innodb表的读写、收缩。
g) innodb_stats_on_metadata 指mysql在什么情况下会刷新innodb表的统计信息。统计信息多用来做数据库优化，但是也有很多不必要的sql语句执行时出发这个统计刷新，建议设置为off。

(3) 第三方优化配置参考
https://tools.percona.com/wizard 
提交自己服务器的状况，能到注册的邮箱中收到最优配置的邮件。

7.硬件优化
代价比较高，见效甚微，简单略过即可。
1) cpu的选择，mysql部分工作，比如Replicate、sql语句执行等，需要的是单核高频cpu，不是核心越多越好，mysql5.5以后使用cpu不要超过32核。
2) 磁盘IO，一般使用raid，三个级别：
a) RAID0：也称为条带，就是把多个磁盘连城一个磁盘使用，这个级别 IO 最好。
b) RAID1：也称为镜像，要求至少有两个磁盘，每组数据存储的数据相同。
c) RAID5：把至少3个硬盘合成一个磁盘使用，数据读写时会建立奇偶校验信息，并且奇偶校验信息和相对应的数据分别存储在不同的磁盘上。当其中一个磁盘数据损坏时，利用剩下的数据和奇偶校验信息去恢复被损坏的数据。
建议使用 RAID0 + 1 方案，就是 RAID0 和 RAID1 的结合，同时具备两个级别的优缺点。其特点如下：
a) 常用于高可用解决方案。
b) 顺序读写效率很高，但是随机读写不如人意。
c) 数据库随机读写比率很高。