mysql语句中 圈a,学习猿地-MySQL 优化笔记

优化方向

SQL优化

sql优化分析

索引优化

优化数据库对象

优化表的数据类型

表拆分(水平、垂直)

反范式

使用中间表

优化 mysql server

mysql内存管理优化

log机制及优化

调整mysql并发参数

应用优化

数据库连接池

使用缓存减少压力

负载均衡建立集群

主主同步、主从复制

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 扩展，仅记录一下，这种方案可能已过时

消息队列

结合高并发设计的 消息队列篇

为什么需要消息队列

双十一、抢购、秒杀等等，共同点：短时间内，写入请求量激增

消息队列的好处

削峰填谷

异步处理

解耦合

redis 处理队列

ZADD

ZRANGE

ZCARD(ZSIZE)

ZDELETE

主从备份及读写分离

主主备份

负载均衡