Mysql 数据库优化

Mysql优化思路及落地方案

优化思路

公司项目添加新功能，上线后发现有些功能的列表查询时间很久，需要仔细排查到底什么原因导致的，不一定是sql语句的问题

导致数据查询慢的原因有多种，如：缓存失效，由于高并发访问导致 MySQL 服务器崩溃；SQL 语句编写问题；MySQL 服务器参数问题；硬件配置限制 MySQL 服务性能问题，网络服务质量差，内核参数优化不够健壮等。

1.查看 MySQL 服务器运行的状态值

show status

我们主要关注 “Queries”、“Threadsconnected” 和 “Threadsrunning” 的值，即查询次数、线程连接数和线程运行数。

我们可以通过执行如下脚本监控 MySQL 服务器运行的状态值

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

#!/bin/bash
while true
do
mysqladmin -uroot -p"密码" ext | awk ‘/Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf("%d %d %d\n",q,c,r)}’ >> status.txt
sleep 1
done

2 获取需要优化的 SQL 语句

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 #排序

3 开启慢查询日志

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

slow_query_log = 1
slow_query_log_file=/var/lib/mysql/slow-query.log
long_query_time = 2

log_queries_not_using_indexes = 1

其中，slowquerylog = 1 表示开启慢查询；slowquerylogfile 表示慢查询日志存放的位置；longquerytime = 2 表示查询 >=2 秒才记录日志；logqueriesnotusing_indexes = 1 记录没有使用索引的 SQL 语句。

4 查询优化

1. 避免 SELECT *，需要什么数据，就查询对应的字段。

2. 小表驱动大表，即小的数据集驱动大的数据集。如：以 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)

3. 一些情况下，可以使用连接代替子查询，因为使用 join，MySQL 不会在内存中创建临时表。

4. 适当添加冗余字段，减少表关联。

5. 合理使用索引（下文介绍）。如：为排序、分组字段建立索引，避免 filesort 的出现。

5 索引使用

5.1 适合使用索引的场景

1. 主键自动创建唯一索引

2. 频繁作为查询条件的字段

3. 查询中与其他表关联的字段

5.2 不适合使用索引的场景

1. 频繁更新的字段

2. where 条件中用不到的字段

3. 表记录太少

4. 经常增删改的表

5. 字段的值的差异性不大或重复性高

6. 索引字段是字符串类型，查询条件的值要加’'单引号,避免底层类型自动转换

6. 选择合适的数据类型及数据库表结构设计

1. 使用可以存下数据最小的数据类型

2. 使用简单的数据类型。int 要比 varchar 类型在mysql处理简单

3. 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int

4. 尽可能使用 not null 定义字段，因为 null 占用4字节空间

5. 单表不要有太多字段，建议在 20 以内

7.读写分离

一般情况下对数据库而言都是“读多写少”。换言之，数据库的压力多数是因为大量的读取数据的操作造成的。我们可以采用数据库集群的方案，使用一个库作为主库，负责写入数据；其他库为从库，负责读取数据。这样可以缓解对数据库的访问压力。

8.进行内核优化

进行内核优化，优化磁盘i/o及各类连接参数。详情请看内核参数文档

9. 硬件参数调优

在bios开启cpu高性能模式

选择 Performance Per Watt Optimized（DAPC）模式，发挥 CPU 最大性能