记录学习笔记,持续更新。
优化方向
SQL优化
- sql优化分析
- 索引优化
优化数据库对象
- 优化表的数据类型
- 表拆分(水平、垂直)
- 反范式
- 使用中间表
优化 mysql server
- mysql内存管理优化
- log机制及优化
- 调整mysql并发参数
应用优化
- 数据库连接池
- 使用缓存减少压力
- 负载均衡建立集群
- 主主同步、主从复制
Mysql优化问题分析定位
分析SQL执行频率
1 2 3
| show status
例如:分析读为主,还是写为主
|
定位执行效率低的SQl
1 2 3 4 5
| 慢查询日志定位 -log-slow-queries = xxx(指定文件名)
SHOW PROCESSLIST 查看当前正在进行的线程,包括线程状态、是否锁表
|
分析SQL执行计划
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| 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
1 2 3 4 5 6
| select @@have_profiling 是否支持 select @@profiling 是否开启
执行 "your sql" show profiles show profile block io for QUERY 17
|
索引优化
索引的存储分类
1 2 3 4
| B-TREE索引:常见,大部分都支持 HASH索引:只有memory引擎支持 R-TREE索引:空间索引是MyISAM的一个特殊索引类型,主要用于地理空间数据类型 full-text索引:全文索引,MyISAM的一个特殊索引类型,innodb从5.6开始支持
|
索引的创建与删除
1 2 3 4 5 6 7 8
| 添加索引 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中能使用索引的情况
1 2 3 4 5 6
| 匹配全值 匹配值范围查询 匹配最左前缀 仅仅对索引进行查询(覆盖查询) 匹配列前缀 (添加前缀索引) 部分精确+部分范围
|
不能使用索引的场景
1 2 3 4 5
| 以%开关的like查询 数据类型出现隐式转换 复合索引查询条件不包含最左部分 使用索引仍比全表扫描慢 用or分割开的条件
|
mysql语句优化
定期优化表
1 2 3
| optimize table table_name 合并表空间碎片,对MyISAM、BDB、INNODB有效
如果提示不支持,可以用 mysql --skip-new 或者 mysql --safe-mode 来重启,以便让其他引擎支持
|
常用优化
1 2 3 4 5 6 7 8 9 10 11
| 尽量避免全表扫描,对where及orderby的列建立索引 尽量避免where使用 != 或 <> 尽量避免where子句用 or 连接条件 乱用%导致全表扫描 尽量避免where子句对字段进行表达式操作 尽量避免where子句对字段进行函数操作 覆盖查询,返回需要的字段 优化嵌套查询,关联查询优于子查询 组合索引或复合索引,最左索引原则 用exist代替in 当索引列有大量重复数据时,SQL查询可能不会去利用索引
|
优化数据库对象
优化表数据类型
1 2 3
| PROCEDURE ANALYSE (16,256) 排除多于16个,大于256字节的ENUM建议
"your sql" PROCEDURE ANALYSE ()
|
表拆分
1 2 3 4 5 6 7
| 垂直拆分 针对某些列常用、不常用
水平拆分 表很大 表中的数据有独立性,能简单分类 需要在表存放多种介质
|
反范式
使用中间表
Mysql引擎比较
mysql有什么引擎?
关于表引擎的命令
1 2 3
| show engines; 查看myql所支持的存储引擎 show variables like '%storage_engine'; 查看mysql默认的存储引擎 show create table table_name 查看具体表使用的存储引擎
|
关于innodb
1 2 3 4 5
| 1. 提供事务、回滚、系统奔溃修复能力、多版本并发控制事务 2. 支持自增列 3. 支持外键 4. 支持事务以及事务相关联功能 5. 支持mvcc的行级锁
|
关于MyISAM
1 2
| 1. 不支持事务、不支持行级锁,只支持并发插入的表锁,主要用于高负载的select 2. 支持三种不同的存储结构:静态、动态、压缩
|
调整参数优化mysql后台服务
MyISAM内存优化
1 2 3 4 5 6 7 8
| #修改相应服务器位置的配置文件 my.cnf
key_buffer_size 决定myisam索引块缓存区的大小,直接影响表的存取效率,建议1/4可用内存
read_buffer 读缓存
write_buffer 写缓存
|
InnoDB内存优化
1 2 3 4 5
| innodb_buffer_pool_size 存储引擎表数据和索引数据的最大缓存区大小
innodb_old_blocks_pct LRU算法 决定old sublist的比例
innodb_old_blocks_time LRU算法 数据转移间隔时间
|
mysql并发参数
1 2 3 4 5 6 7 8 9
| 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 扩展,仅记录一下,这种方案可能已过时
主从备份及读写分离
主主备份
负载均衡