5 mysql常见性能优化方案

5.1 mysq服务器运行状态值

show status; 查看关键参数
Queries 查询次数
Threads_connected 线程连接数
Threads_running 线程运行数

5.2 获取需要优化的 SQL 语句

5.2.1 查看运行的线程

show processlist;
±—±-----±----------±-----±--------±-----±------±-----------------+
| Id | User | Host | db | Command | Time | State | Info |
±—±-----±----------±-----±--------±-----±------±-----------------+
| 11 | root | localhost | NULL | Query | 0 | init | show processlist |
±—±-----±----------±-----±--------±-----±------±-----------------+
State 的值是我们判断性能好坏的关键

5.2.2 开启慢查询日志

my.cnf 配置
slow_query_log = 1 #开启慢查询
slow_query_log_file=/var/lib/mysql/slow-query.log #慢查询日志存放的位置
long_query_time = 2 #查询 >=2 秒才记录日志
log_queries_not_using_indexes = 1 #没有使用索引的 SQL 语句

mysqldumpslow 工具对日志进行分析
mysqldumpslow -s r -t 10 /var/lib/mysql/slow-query.log

5.3 分析 SQL 语句

5.3.1 explain

mysql> explain select * from students where name=‘王五’\G
*************************** 1. row ***************************
id: 1 //select 查询序列号,id值越大优先级越高，越先被执行
select_type: SIMPLE //查询数据的操作类型
table: students //显示该行数据是关于哪张表
partitions: NULL //匹配的分区
type: ALL //表的连接类型
possible_keys: NULL //指出 MySQL 使用哪个索引在该表找到行记录
key: NULL //显示 MySQL 实际使用的索引
key_len: NULL //表示索引中使用的字节数,长度越短越好
ref: NULL //显示该表的索引字段关联了哪张表的哪个字段
rows: 2 //大致估算出找到所需的记录或所需读取的行数，数值越小越好
filtered: 50.00 //返回结果的行数占读取行数的百分比，值越大越好
Extra: Using where //包含不合适在其他列中显示但十分重要的额外信息

5.3.2 profiling

1 select @@profiling; #查看 profile 开启情况 0 表示关闭状态,1 表示开启
set profiling = 1;

2 select * from students where name=‘王五’;

3 查看执行的 SQL 列表
show profiles;

4 查询指定 ID 的执行详细信息
show profile for query 6;

5 获取 CPU、 Block IO 等信息
show profile block io,cpu for query 6;

show profile cpu,block io,memory,swaps,context switches,source for 6;

show profile all for query 6;

5.4 优化手段

5.4.1 查询优化

1 避免 SELECT *，需要什么数据，就查询对应的字段。
2 小表驱动大表，即小的数据集驱动大的数据集
当 B 表的数据集小于 A 表时 in 代替 exist 先查 小 表，再查 大 表
select * from A where id in (select id from B)
3 连接代替子查询，因为使用 join，MySQL 不会在内存中创建临时表
4 适当添加冗余字段

5.4.2 索引使用

5.4.2.1 适合使用索引的场景

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

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

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

4. 查询中排序的字段

5. 查询中统计或分组字段

5.4.2.2 不适合使用索引的场景

1. 频繁更新的字段

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

3. 表记录太少

4. 经常增删改的表

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

5.4.2.3 索引创建和使用原则

1. 单表查询：哪个列作查询条件，就在该列创建索引
2. 多表查询：left join 时，索引添加到右表关联字段；right join 时，索引添加到左表关联字段
3. 不要对索引列进行任何操作（计算、函数、类型转换）
4. 索引列中不要使用 !=，<> 非等于
5. 索引列不要为空，且不要使用 is null 或 is not null 判断
6. 索引字段是字符串类型，查询条件的值要加’'单引号,避免底层类型自动转换

5.4.2.4 索引失效

1. 模糊查询时，以 % 开头
2. 使用 or 时，如：字段1（非索引）or 字段2（索引）会导致索引失效
3. 使用复合索引时，不使用第一个索引列

ALTER TABLE students ADD COLUMN school VARCHAR(20) DEFAULT NULL COMMENT ‘学校’ after id;
ALTER TABLE students ADD COLUMN city VARCHAR(20) DEFAULT NULL COMMENT ‘城市’ after id;
ALTER TABLE students drop COLUMN school;

5.4.3 数据库表结构设计

5.4.3.1 选择合适的数据类型

1. 使用可以存下数据最小的数据类型
2. 使用简单的数据类型。int 要比 varchar 类型在mysql处理简单
3. 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int
4. 尽可能使用 not null 定义字段，因为 null 占用4字节空间
5. 尽量少用 text 类型,非用不可时最好考虑分表
6. 尽量使用 timestamp 而非 datetime
7. 单表不要有太多字段，建议在 20 以内

5.4.3.2 分表

1. 垂直拆分：将表中多个列分开放到不同的表中。插入数据时，使用事务确保两张表的数据一致性。
   10个字段放一张表 其余字段放另一张表
2. 水平拆分：按照行进行拆分。

5.4.3.2 分库

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