超详细的MySQL数据库优化知识梳理
1 常见问题 阻塞 慢查询 2 可以从以下几个方面入手 服务器硬件优化 系统配置优化 数据库结构优化 SQL和索引 通常优化3和4 3 慢查询日志 show variables like 'slow_query_log'; # 开启慢查询日志 set global slow_query_log=on; # 查询慢查询日志文件存放的位置 show variables like 'slow_query_log_file'; # 设置慢查询日志文件存放的位置 set global slow_query_log_file= '/home/mysql/sql_log/mysql-slow.log' # 设置是否将没有使用索引的SQL记录到慢查询日志中 set global log_queries_not_using_indexes=on; # 设置是否将查询事件超过0秒的SQL记录到慢查询日志中，通产设置0.01秒 set global long_query_time=0; 4 实操 4.1 默认不开启慢查询日志 4.2 慢查询日志存储的位置 查询日志，这里我是Docker容器部署MySQL，映射在服务器上/data/mysql目录下 4.3 日志格式 第一行：执行SQL的主机信息 第二行：SQL的执行信息 第三行：SQL的执行事件 第四行：SQL的内容 5 慢查询日志分析工具 5.1 mysqldumpslow（官方工具） 5.1.1 参数解释 -s 是order的顺序 al 平均锁定时间 ar 平均返回记录时间 at 平均查询时间（默认） c 计数 l 锁定时间 r 返回记录 t 查询时间 -t 是top n的意思，即为返回前面多少条的数据 -g 后边可以写一个正则匹配模式，大小写不敏感的 5.1.2 基本使用 # 得到返回记录集最多的10个SQL。 mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log # 得到访问次数最多的10个SQL mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log # 得到按照时间排序的前10条里面含有左连接的查询语句。 mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log # 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。 mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more 5.2 pt-query-digest 5.2.1 快速安装 wget https://www.percona.com/downloads/percona-toolkit/2.2.16/RPM/percona-toolkit-2.2.16-1.noarch.rpm && yum localinstall -y percona-toolkit-2.2.16-1.noarch.rpm 5.2.2 参数解释 pt-query-digest [OPTIONS] [FILES] [DSN] --create-review-table 当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。 --create-history-table 当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。 --filter 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析 --limit 限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。 --host mysql服务器地址 --user mysql用户名 --password mysql用户密码 --history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。 --review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。 --output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。 --since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。 --until 截止时间，配合—since可以分析一段时间内的慢查询。 5.2.3 基本使用 # 输出到文件 pt-query-digest slow-log > slow_log.report # 输出到数据库 --create-reviewtable 意思是慢查询日志输出到某一张表中 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 6 SQL优化 6.1 需要优化的SQL特征 查询次数多且每次查询占用时间长的SQL IO大的SQL（SQL中扫描行数越多，IO越大） 未命中索引的SQL 6.2 使用explain查询SQL的执行计划 6.2.1 解释说明 table 显示这一行数据时关于哪张表的 type 这是重要的列，显示连接使用了那种类型。从最好到最差的连接类型为const,eq_reg,ref,range,index,ALL。const常见于主键/唯一索引查找，eq_reg常见于主键的范围查找，ref常见于连接查询,range常见于索引的范围查找，index常见于索引的扫描，ALL常见于表扫描 possible_keys 显示可能应用在这张表中的索引。如果为空，没有可能的索引。 key 实际使用的索引。如果为NULL,则没有使用索引。 key_len 使用的索引长度。在不损失精确性的情况下，长度越短越好 ref 显示索引的那一列被使用了，如果可的话，是一个常数 rows MySQL认为必须检查的用来返回请求数据的行数 6.3 SQL优化 在经常查询的字段上适当加索引 避免子查询，可优化为连接查询，注意是否存在一对多关系，可能会出现数据重复 6.4 如何选择合理的列建立索引 在where从句，group by 从句，order by 从句，on 从句中出现的列 索引字段越小越好 离散度大的列放在联合索引的前面 例如： select * from payment where staff_id = 2 and customer_id = 584; 由于customer_id的离散度更大，所以应该使用index(customer_id,staff_id) 6.4.1 如何判断列的离散程度 select count(distinct customer_id),count(distinct staff_id) from payment 列的唯一值越高，离散程度越大，可选择性越高。 6.4.2 索引的维护和优化 重复及冗余索引，重复索引是指相同的列以相同的顺序建立的同类型的索引，如下面parmary key 和ID列上的索引就是重复索引 create table test(id int not null primart key),name vachar(10) not null,title varchar(50) not null,unique(id) ) engine=innodb 使用pt-duplicate-key-checker工具检查重复及冗余索引 pt-duplicate-key-checker -uroot -p'' -h 127.0.0.1 7 数据库及表结构优化 7.1 选择合适的数据类型 使用可以存下你的数据的最小的数据类型 使用简单数据类型，int要比varchar类型在MySQL处理上简单 尽可的使用not null 定义字段 尽量少用text类型，非用不可时最好考虑分表 例如 使用bigint来存储IP地址，利用INET_ATON(),INET__NTOA()两个函数进行转换 insert into sessions(ipaddress) values (INET_ATON('127.0.0.1')); select INET__NTOA('127.0.0.1') from sessions; 7.2 范式化和反范式化 7.2.1 范式化 第一范式，强调原子性，要求属性具有原子性，不可再分解 第二范式，强调主键，要求记录有唯一标识，即实体的唯一性，级不存在部分依赖 第三范式，强调外键，要求任何字段不能由其他字段派生出来，要求字段没有冗余，即不存在依赖传递 7.2.2 反范式化 为了查询效率的考虑，把原本符合第三范式的表适当的增加冗余，以达到优化查询效率的目的，反范式化是一种以控件来换时间的操作。 7.3 数据库结构的优化 7.3.1 表的垂直拆分 把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题，通常垂直拆分可以按以下原则进行： 把不常用的字段单独存放在一个表中 把大字段独立存在在一个表中 把经常一起使用的字段放在一起 7.3.2 表的水平拆分 为了解决单表的数据量大的问题，水平拆分的表每一个表的结构都是完全一致的 8 系统配置优化 数据库是基于操作系统的，目前大多数MySQL都是安装在Linux上，所以对于操作系统的一些参数配置也会影响MySQL的性能，下面列出常用的系统配置 8.1 网络配置方面 修改/etc/sysctl.coonf文件 # 增加tcp支持的队列数 net.ip4.tcp_max_syn_backlog=65535 # 减少断开连接时，资源回收 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 65335 hard nofile 65535 除此之外最好在MySQL服务器上关闭iptables,selinux 等防火墙软件 8.2 MySQL配置文件 8.2.1 配置文件路径 Linux：/etc/my.cnf 8.2.2 参数 innodb_buffer_pool_size：非常重要的参数，用于配置innodb的缓冲池如果数据库中只有innodb表，则推荐配置量为总内存的75% 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_io_threads/innodb_write_io_threads：决定innodb读写IO的进程数，默认为4 innodb_file_per_table：关键参数，控制innodb没一个表使用独立的表空间，默认为OFF，也就是所有表都会建立在共享表空间中 innodb_stats_on_metadata：决定MySQL在什么情况下会刷新innodb表的统计信息 8.2.3 第三方配置工具 Percon Configuration Wizard 9 服务器硬件的优化 9.1 CPU 选择合适的CPU，单个频率更快的CPU MySQL一些工作只能使用到单核 MySQL对CPU的核数支持并不是越快越好，MySQL5.5使用的服务器不要超过32核 9.2 disk IO优化 常用RAID级别简介 RAID0：也成为条带，就是把多个磁盘连接成一个硬盘使用，这个级别IO最好 RAID1：也称为镜像，要求至少有两个磁盘，每组磁盘存储的数据相同 RAID5：也是把多个（最少3个）硬盘合并成一个逻辑盘使用，数据读写时会建立奇偶校验信息，并且奇偶校验信息和相对应的数据分别存储于不同的硬盘上。当RAID5的一个磁盘数据发生损坏后，就剩下的数据和相应的奇偶校验信息去恢复被损坏的数据。 RAID1+0：就是RAID1和RAID0的结合，同时具备两个级别的优缺点。一般建议数据库使用这个级别。 SNA和NAT是否适合数据库 常用于高可用的解决方案 顺序读写效率很高，但是随机读写不如人意 数据库随机读写效率很高
