MySQL 优化笔记

优化方向

SQL 优化

1. sql 优化分析
2. 索引优化

优化数据库对象

1. 优化表的数据类型
2. 表拆分（水平、垂直）
3. 反范式
4. 使用中间表

优化 mysql server

1. mysql 内存管理优化
2. log 机制及优化
3. 调整 mysql 并发参数

应用优化

1. 数据库连接池
2. 使用缓存减少压力
3. 负载均衡建立集群
4. 主主同步、主从复制

Mysql 优化问题分析定位

分析 SQL 执行频率

show status

例如：分析读为主，还是写为主

定位执行效率低的 SQL

慢查询日志定位
-log-slow-queries = xxx（指定文件名）

SHOW PROCESSLIST
查看当前正在进行的线程，包括线程状态、是否锁表

分析 SQL 执行计划

explain "your sql"

desc "your sql"

- 部分参数分析
select_type: 
SIMPLE 简单表，不使用表连接或子查询
PRIMARY 主查询，即外层的查询
UNION 
SUBQUER 子查询的第一个select

type: 
ALL 全表扫描
index 索引全扫描
range 索引范围扫描
ref 使用非唯一索引或唯一索引的前缀扫描
eq_ref 类似ref，使用的索引是唯一索引
const/system 单表中最多有一个匹配行
NULL 不用访问表或者索引，直接得到结果

show profile 分析 SQL

select @@have_profiling 是否支持
select @@profiling 是否开启

执行 "your sql"
show profiles 
show profile block io for QUERY 17

索引优化

索引的存储分类

B-TREE索引：常见，大部分都支持
HASH索引：只有memory引擎支持
R-TREE索引：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型
full-text索引：全文索引，MyISAM的一个特殊索引类型，innodb从5.6开始支持

索引的创建与删除

添加索引
ALTER Table `table_name` ADD PRIMARY KEY（`column`）
ALTER Table `table_name` ADD UNIQUE（`column`）
ALTER Table `table_name` ADD INDEX（`column`）
ALTER Table `table_name` ADD FULLTEXT（`column`）

删除
ALTER Table `table_name` drop index index_name

Mysql 中能使用索引的情况

匹配全值
匹配值范围查询
匹配最左前缀
仅仅对索引进行查询（覆盖查询）
匹配列前缀 （添加前缀索引）
部分精确+部分范围

不能使用索引的场景

以%开关的like查询
数据类型出现隐式转换
复合索引查询条件不包含最左部分
使用索引仍比全表扫描慢
用or分割开的条件

mysql 语句优化

定期优化表

optimize table table_name 合并表空间碎片，对MyISAM、BDB、INNODB有效

如果提示不支持，可以用 mysql --skip-new 或者 mysql --safe-mode 来重启，以便让其他引擎支持

常用优化

尽量避免全表扫描，对where及orderby的列建立索引
尽量避免where使用 != 或 <>
尽量避免where子句用 or 连接条件
乱用%导致全表扫描
尽量避免where子句对字段进行表达式操作
尽量避免where子句对字段进行函数操作
覆盖查询，返回需要的字段
优化嵌套查询，关联查询优于子查询
组合索引或复合索引，最左索引原则
用exist代替in
当索引列有大量重复数据时，SQL查询可能不会去利用索引

优化数据库对象

优化表数据类型

PROCEDURE ANALYSE (16,256) 排除多于16个，大于256字节的ENUM建议

"your sql" PROCEDURE ANALYSE () 

表拆分

垂直拆分
针对某些列常用、不常用

水平拆分
表很大
表中的数据有独立性，能简单分类
需要在表存放多种介质

反范式

增加冗余列、增加派生列、重新组表和分割表

使用中间表

数据查询量大
数据统计、分析场景

Mysql 引擎比较

mysql 有什么引擎？

关于表引擎的命令

show engines; 查看myql所支持的存储引擎
show variables like '%storage_engine'; 查看mysql默认的存储引擎
show create table table_name 查看具体表使用的存储引擎

关于 innodb

1. 提供事务、回滚、系统奔溃修复能力、多版本并发控制事务
2. 支持自增列
3. 支持外键
4. 支持事务以及事务相关联功能
5. 支持mvcc的行级锁

关于 MyISAM

1. 不支持事务、不支持行级锁，只支持并发插入的表锁，主要用于高负载的select
2. 支持三种不同的存储结构：静态、动态、压缩

调整参数优化 mysql 后台服务

MyISAM 内存优化

#修改相应服务器位置的配置文件 my.cnf

key_buffer_size
决定myisam索引块缓存区的大小，直接影响表的存取效率，建议1/4可用内存

read_buffer 读缓存

write_buffer 写缓存

InnoDB 内存优化

innodb_buffer_pool_size 存储引擎表数据和索引数据的最大缓存区大小

innodb_old_blocks_pct LRU算法 决定old sublist的比例

innodb_old_blocks_time LRU算法 数据转移间隔时间

mysql 并发参数

max_connections 最大连接数，默认151

back_log 短时间内处理大量连接，可适当增大

table_open_cache 控制所有SQL执行线程可打开表缓存的数量，受其他参数制约

thread_cache_size 控制缓存客户服务线程数量，加快数据库连接速度，根据threads_created/connections来衡量是否合适

innodb_lock_wait_timeout 控制事务等待行锁时间，默认50ms

Mysql 应用优化介绍

为什么要做应用优化

• 数据的重要性
• mysql 服务及自身性能瓶颈
• 保证大型系统稳定可靠运行

应用优化方法

使用连接池

减少对 mysql 的真实连接
a. 避免相同数据重复执行（查询缓存）
b. 使用 mysql 缓存（sql 缓存）

负载均衡
a. LVS 分布式
b. 读写分离（主主复制、主从复制保证数据一致性）

数据库连接池

php-cp 扩展，仅记录一下，这种方案可能已过时

主从备份及读写分离

主主备份

负载均衡