关于java:全面-MySQL-性能优化实践

线上有些性能的列表查问工夫很久。起因是新性能用到旧性能的接口，而这些旧接口的 SQL 查问语句关联 5,6 张表且编写不够标准，导致 MySQL 在执行 SQL 语句时索引生效，进行全表扫描。

查阅网上 SQL 优化的材料后胜利解决了问题，在此从全局角度，记录和总结 MySQL 查问优化相干技巧。

数据查问慢，不代表 SQL 语句写法有问题。首先，咱们须要找到问题的源头能力“隔靴搔痒”。上面用一张流程图展现 MySQL 优化的思路：

从图中能够分明地看出，导致数据查问慢的起因有多种，如：

缓存生效，在此一段时间内因为高并发拜访导致 MySQL 服务器解体。
SQL 语句编写问题。
MySQL 服务器参数问题。
硬件配置限度 MySQL 服务性能问题等。

如果零碎的并发申请数不高，且查问速度慢，能够疏忽该步骤间接进行 SQL 语句调优步骤。

执行命令：

show status

因为返回后果太多，此处不贴出后果。其中，再返回的后果中，咱们次要关注“Queries”、“Threadsconnected”和“Threadsrunning”的值，即查问次数、线程连接数和线程运行数。

咱们能够通过执行如下脚本监控 MySQL 服务器运行的状态值

执行该脚本 24 小时，获取 status.txt 里的内容，再次通过 awk 计算 == 每秒申请 MySQL 服务的次数 ==

awk ‘{q=$1-last;last=$1}{printf(“%d %d %dn”,q,$2,$3)}’ status.txt

复制计算好的内容到 Excel 中生成图表察看数据周期性。

如果察看的数据有周期性的变动，如上图的解释，须要批改缓存生效策略。

例如：

通过随机数在 [3,6,9] 区间获取其中一个值作为缓存生效工夫，这样扩散了缓存生效工夫，从而节俭了一部分内存的耗费。

当拜访高峰期时，一部分申请分流到未生效的缓存，另一部分则拜访 MySQL 数据库，这样缩小了 MySQL 服务器的压力。

执行命令：

show processlist

返回后果：

从返回后果中咱们能够理解该线程执行了什么命令 /SQL 语句以及执行的工夫。理论利用中，查问的返回后果会有 N 条记录。

其中，返回的 State 的值是咱们判断性能好坏的要害，其值呈现如下内容，则该行记录的 SQL 语句须要优化：

Converting HEAP to MyISAM # 查问后果太大时，把后果放到磁盘，重大 Create tmp table #创立长期表，重大 Copying to tmp table on disk #把内存长期表复制到磁盘，重大 locked #被其余查问锁住，重大 loggin slow query #记录慢查问 Sorting result #排序

在配置文件 my.cnf 中的 [mysqld] 一行下边增加两个参数：

slow_query_log = 1slow_query_log_file=/var/lib/mysql/slow-query.loglong_query_time = 2log_queries_not_using_indexes = 1

其中，slowquerylog = 1 示意开启慢查问。
slowquerylogfile 示意慢查问日志寄存的地位。
longquerytime = 2 示意查问 >=2 秒才记录日志。
logqueriesnotusing_indexes = 1 记录没有应用索引的 SQL 语句。

留神：slowquerylog_file 的门路不能轻易写，否则 MySQL 服务器可能没有权限将日志文件写到指定的目录中。倡议间接复制上文的门路。

批改保留文件后，重启 MySQL 服务。在 /var/lib/mysql/ 目录下会创立 slow-query.log 日志文件。连贯 MySQL 服务端执行如下命令能够查看配置状况。

show variables like ‘slow_query%’;show variables like ‘long_query_time’;

测试慢查问日志：

mysql> select sleep(2);+———-+| sleep(2) |+———-+| 0 |+———-+1 row in set (2.00 sec)

关上慢查问日志文件

[root@localhost mysql]# vim /var/lib/mysql/slow-query.log/usr/sbin/mysqld, Version: 5.7.19-log (MySQL Community Server (GPL)). started with:Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sockTime Id Command Argument# Time: 2017-10-05T04:39:11.408964Z# User@Host: root[root] @ localhost [] Id: 3# Query_time: 2.001395 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0use test;SET timestamp=1507178351;select sleep(2);

咱们能够看到方才执行了 2 秒的 SQL 语句被记录下来了。

尽管在慢查问日志中记录查问慢的 SQL 信息，然而日志记录的内容密集且不易查阅。因而，咱们须要通过工具将 SQL 筛选进去。

MySQL 提供 mysqldumpslow 工具对日志进行剖析。咱们能够应用 mysqldumpslow --help 查看命令相干用法。

罕用参数如下：

-s：排序形式，后边接着如下参数 c：拜访次数 l：锁定工夫 r：返回记录 t：查问工夫 al：均匀锁定工夫 ar：均匀返回记录书 at：均匀查问工夫 -t：返回后面多少条的数据 -g：翻遍搭配一个正则表达式，大小写不敏感

案例：

获取返回记录集最多的 10 个 sql

mysqldumpslow -s r -t 10 /var/lib/mysql/slow-query.log

获取拜访次数最多的 10 个 sql

mysqldumpslow -s c -t 10 /var/lib/mysql/slow-query.log

获取依照工夫排序的前 10 条外面含有左连贯的查问语句

mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/slow-query.log

筛选出有问题的 SQL，咱们能够应用 MySQL 提供的 explain 查看 SQL 执行打算状况（关联表，表查问程序、索引应用状况等）。

用法：

explain select * from category;

返回后果：

字段名称

字段解释

select 查问序列号。id 雷同，执行程序由上至下；id 不同，id 值越大优先级越高，越先被执行

select_type

查问数据的操作类型，其值如下：1、simple：简略查问，不蕴含子查问或 union；2、primary: 蕴含简单的子查问，最外层查问标记为该值；3、subquery：在 select 或 where 蕴含子查问被标记为该值；4、derived：在 from 列表中蕴含的子查问被标记为该值，MySQL 会递归执行这些子查问，把后果放在长期表；5、union：若第二个 select 呈现在 union 之后，则被标记为该值。若 union 蕴含在 from 的子查问中，外层 select 被标记为 derived；6、union result：从 union 表获取后果的 select；

table

显示该行数据是对于哪张表

partitions

匹配的分区

type

表的连贯类型，其值，性能由高到底排列如下：1、system：表只有一行记录，相当于零碎表；2、const：通过索引一次就找到，只匹配一行数据；3、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。罕用于主键或惟一索引扫描；4、ref：非唯一性索引扫描，返回匹配某个独自值的所有行。用于 =、< 或 > 操作符带索引的列；5、range：只检索给定范畴的行，应用一个索引来抉择行。个别应用 between、>、< 状况；6、index：只遍历索引树；7、ALL：全表扫描，性能最差；注：前 5 种状况都是现实状况的索引应用状况。通常优化至多到 range 级别，最好能优化到 ref

possible_keys

指出 MySQL 应用哪个索引在该表找到行记录。如果该值为 NULL，阐明没有应用索引，能够建设索引进步性能

key

显示 MySQL 理论应用的索引。如果为 NULL，则没有应用索引查问

key_len

示意索引中应用的字节数，通过该列计算查问中应用的索引的长度。在不损失精确性的状况下，长度越短越好显示的是索引字段的最大长度，并非理论应用长度

ref

显示该表的索引字段关联了哪张表的哪个字段

rows

依据表统计信息及选用状况，大抵估算出找到所需的记录或所需读取的行数，数值越小越好

filtered

返回后果的行数占读取行数的百分比，值越大越好

extra

蕴含不适合在其余列中显示但非常重要的额定信息，常见的值如下：1、using filesort：阐明 MySQL 会对数据应用一个内部的索引排序，而不是依照表内的索引程序进行读取。呈现该值，应该优化 SQL；2、using temporary：应用了长期表保留两头后果，MySQL 在对查问后果排序时应用长期表。常见于排序 order by 和分组查问 group by。呈现该值，应该优化 SQL；4、using index：示意相应的 select 操作应用了笼罩索引，防止了拜访表的数据行，效率不错；5、using where：where 子句用于限度哪一行；6、using join buffer：应用连贯缓存；distinct：发现第一个匹配后，进行为以后的行组合搜寻更多的行；留神：呈现前 2 个值，SQL 语句必须要优化。

应用 profiling 命令能够理解 SQL 语句耗费资源的详细信息（每个执行步骤的开销）。

4.2.1 查看 profile 开启状况

select @@profiling;

返回后果：

mysql> select @@profiling;+————-+| @@profiling |+————-+| 0 |+————-+1 row in set, 1 warning (0.00 sec)

0 示意敞开状态,1 示意开启

4.2.2 启用 profile

set profiling = 1;

返回后果：

mysql> set profiling = 1;Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@profiling;+————-+| @@profiling |+————-+| 1 |+————-+1 row in set, 1 warning (0.00 sec)

在连贯敞开后，profiling 状态主动设置为敞开状态。

4.2.3 查看执行的 SQL 列表

show profiles;

返回后果：

mysql> show profiles;+———-+————+——————————+| Query_ID | Duration | Query |+———-+————+——————————+| 1 | 0.00062925 | select @@profiling || 2 | 0.00094150 | show tables || 3 | 0.00119125 | show databases || 4 | 0.00029750 | SELECT DATABASE() || 5 | 0.00025975 | show databases || 6 | 0.00023050 | show tables || 7 | 0.00042000 | show tables || 8 | 0.00260675 | desc role || 9 | 0.00074900 | select name,is_key from role |+———-+————+——————————+9 rows in set, 1 warning (0.00 sec)

该命令执行之前，须要执行其余 SQL 语句才有记录。

4.2.4 查问指定 ID 的执行详细信息

show profile for query Query_ID;

返回后果：

mysql> show profile for query 9;+———————-+———-+| Status | Duration |+———————-+———-+| starting | 0.000207 || checking permissions | 0.000010 || Opening tables | 0.000042 || init | 0.000050 || System lock | 0.000012 || optimizing | 0.000003 || statistics | 0.000011 || preparing | 0.000011 || executing | 0.000002 || Sending data | 0.000362 || end | 0.000006 || query end | 0.000006 || closing tables | 0.000006 || freeing items | 0.000011 || cleaning up | 0.000013 |+———————-+———-+15 rows in set, 1 warning (0.00 sec)

每行都是状态变动的过程以及它们继续的工夫。Status 这一列和 show processlist 的 State 是统一的。因而，须要优化的留神点与上文形容的一样。

4.2.5 获取 CPU、Block IO 等信息

show profile block io,cpu for query Query_ID;show profile cpu,block io,memory,swaps,context switches,source for query Query_ID;show profile all for query Query_ID;

次要以查问优化、索引应用和表结构设计方面进行解说。

防止 SELECT *，须要什么数据，就查问对应的字段。

小表驱动大表，即小的数据集驱动大的数据集。如：以 A，B 两表为例，两表通过 id 字段进行关联。

当 B 表的数据集小于 A 表时，用 in 优化 exist；应用 in，两表执行程序是先查 B 表，再查 A 表

select * from A where id in (select id from B)

当 A 表的数据集小于 B 表时，用 exist 优化 in；应用 exists，两表执行程序是先查 A 表，再查 B 表

select * from A where exists (select 1 from B where B.id = A.id)

一些状况下，能够应用连贯代替子查问，因为应用 join，MySQL 不会在内存中创立长期表。

适当增加冗余字段，缩小表关联。

正当应用索引（下文介绍）。如：为排序、分组字段建设索引，防止 filesort 的呈现。更多：来一份 MySQL 索引数据结构及优化清单

5.2.1 适宜应用索引的场景

1、主键主动创立惟一索引 2、频繁作为查问条件的字段 3、查问中与其余表关联的字段 4、查问中排序的字段 5、查问中统计或分组字段

5.2.2 不适宜应用索引的场景

1、频繁更新的字段 2、where 条件中用不到的字段 3、表记录太少 4、常常增删改的表 5、字段的值的差异性不大或重复性高

5.2.3 索引创立和应用准则

单表查问：哪个列作查问条件，就在该列创立索引
多表查问：left join 时，索引增加到右表关联字段；right join 时，索引增加到左表关联字段
不要对索引列进行任何操作（计算、函数、类型转换）
索引列中不要应用 !=，<> 非等于
索引列不要为空，且不要应用 is null 或 is not null 判断
索引字段是字符串类型，查问条件的值要加’’单引号, 防止底层类型主动转换
违反上述准则可能会导致索引生效，具体情况须要应用 explain 命令进行查看

5.2.4 索引生效状况

除了违反索引创立和应用准则外，如下状况也会导致索引生效：

含糊查问时，以 % 结尾
应用 or 时，如：字段 1（非索引）or 字段 2（索引）会导致索引生效。
应用复合索引时，不应用第一个索引列。
index(a,b,c)，以字段 a,b,c 作为复合索引为例：

5.3.1 抉择适合的数据类型 6.3 数据库表结构设计

应用能够存下数据最小的数据类型

应用简略的数据类型。int 要比 varchar 类型在 mysql 解决简略
尽量应用 tinyint、smallint、mediumint 作为整数类型而非 int
尽可能应用 not null 定义字段，因为 null 占用 4 字节空间
尽量少用 text 类型, 非用不可时最好思考分表
尽量应用 timestamp 而非 datetime
单表不要有太多字段，倡议在 20 以内

5.3.2 表的拆分

当数据库中的数据十分大时，查问优化计划也不能解决查问速度慢的问题时，咱们能够思考拆分表，让每张表的数据质变小，从而进步查问效率。

垂直拆分：将表中多个列离开放到不同的表中。例如用户表中一些字段常常被拜访，将这些字段放在一张表中，另外一些不罕用的字段放在另一张表中。插入数据时，应用事务确保两张表的数据一致性。

程度拆分：依照行进行拆分。例如用户表中，应用用户 ID，对用户 ID 取 10 的余数，将用户数据平均的调配到 0~9 的 10 个用户表中。查找时也依照这个规定查问数据。

5.3.3 读写拆散

个别状况下对数据库而言都是“读多写少”。换言之，数据库的压力少数是因为大量的读取数据的操作造成的。咱们能够采纳数据库集群的计划，应用一个库作为主库，负责写入数据；其余库为从库，负责读取数据。这样能够缓解对数据库的拜访压力。

sortbuffersize 排序缓冲区内存大小
joinbuffersize 应用连贯缓冲区大小
readbuffersize 全表扫描时调配的缓冲区大小

1、Innodblogfile_size 事务日志大小 2、Innodblogfilesingroup 事务日志个数 3、Innodblogbuffer_size 事务日志缓冲区大小 4、Innodbflushlogattrx_commit 事务日志刷新策略，其值如下：

0：每秒进行一次 log 写入 cache，并 flush log 到磁盘
1：在每次事务提交执行 log 写入 cache，并 flush log 到磁盘
2：每次事务提交，执行 log 数据写到 cache，每秒执行一次 flush log 到磁盘

expirelogsdays 指定主动清理 binlog 的天数
maxallowedpacket 管制 MySQL 能够接管的包的大小
skipnameresolve 禁用 DNS 查找
read_only 禁止非 super 权限用户写权限
skipslavestart 级你用 slave 主动复原

max_connections 管制容许的最大连接数
tmptablesize 长期表大小
maxheaptable_size 最大内存表大小

笔者并没有应用这些参数对 MySQL 服务器进行调优，具体详情介绍和性能成果请参考文章开端的材料或另行百度。

硬件的性能间接决定 MySQL 数据库的性能。硬件的性能瓶颈，间接决定 MySQL 数据库的运行数据和效率。

作为软件开发程序员，咱们次要关注软件方面的优化内容，以下硬件方面的优化作为理解即可

内存的 IO 比硬盘的速度快很多，能够减少零碎的缓冲区容量，使数据在内存停留的工夫更长，以缩小磁盘的 IO

应用 SSD 或 PCle SSD 设施，至多取得数百倍甚至万倍的 IOPS 晋升

购买阵列卡同时装备 CACHE 及 BBU 模块，能够显著晋升 IOPS

尽可能选用 RAID-10，而非 RAID-5

在服务器的 BIOS 设置中，调整如下配置：

1、抉择 Performance Per Watt Optimized（DAPC）模式，施展 CPU 最大性能 2、敞开 C1E 和 C States 等选项，晋升 CPU 效率 3、Memory Frequency（内存频率）抉择 Maximum Performance

1、https://dev.mysql.com/doc/refman/5.7/en/show-status.html show status 语法 2、https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html show processlist 语法 3、https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html 线程状态 4、https://dev.mysql.com/doc/refman/5.7/en/explain-output.html explain 语法 5、https://dev.mysql.com/doc/refman/5.7/en/show-profile.html show profile 语法 6、http://blog.csdn.net/nightelve/article/details/17393631 MySQL 服务器参数调优 7、http://blog.csdn.net/qq_22929803/article/details/51237056 MySQL 服务器参数调优 8、http://blog.chinaunix.net/uid-11640640-id-3426908.html 9、https://segmentfault.com/a/1190000006158186 10、http://blog.csdn.net/gzh0222/article/details/7976127

关于java:全面-MySQL-性能优化实践

全面 MySQL 性能优化实际

一、优化思路

二、查看 MySQL 运行状态的值

!/bin/bashwhile truedomysqladmin -uroot -p” 明码 ” ext | awk ‘/Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf(“%d %d %dn”,q,c,r)}’ >> status.txtsleep 1done

三、获取须要优化的 SQL 语句

3.1 形式一：查看运行的线程

4.2 形式二：开启慢查问日志

四、剖析 SQL 语句

4.1 形式一：explain

4.2 形式二：profiling

4.2.1 查看 profile 开启状况

4.2.2 启用 profile

4.2.3 查看执行的 SQL 列表

4.2.4 查问指定 ID 的执行详细信息

4.2.5 获取 CPU、Block IO 等信息

五、优化伎俩

5.1 查问优化

5.2 索引应用

5.2.1 适宜应用索引的场景

5.2.2 不适宜应用索引的场景

5.2.3 索引创立和应用准则

5.2.4 索引生效状况

5.3.1 抉择适合的数据类型 6.3 数据库表结构设计

5.3.2 表的拆分

5.3.3 读写拆散

六、服务器参数调优

6.1 内存相干

6.2 IO 相干

6.3 平安相干

6.4 其余

七、硬件选购和参数优化

7.1 内存相干

7.2 磁盘 I/O 相干

7.3 配置 CUP 相干

八、参考资料