mysql优化

数据采集分析工具

profile

set profiling=1;
show profiles;

performance_scheme

show processlist

执行计划

命令

/* explain sql语句*/
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c2;

输出内容

列	含义
id	语句标识
select_type	语句类型
table	输出行引用的表
partitions	匹配的分区
type	join类型
possible_keys	候选索引
key	实际使用的索引
key_len	实际使用的索引键值长度
ref	与索引列比较的列或常量
rows	估计行数
filtered	通过条件筛选行的占比
Extra	额外信息

type

类型	含义
system	表只有一条记录
const	最多只有一条记录，一般为基于主键的查询
eq_ref	对于每个行连接，只有一行记录读取，一般为主键索引或唯一非空索引，并且索引全部列被使用
ref	与索引值匹配的所有行都被读取
fulltext	执行全文索引
ref_or_null	同ref,但包括NULL的查找
index_merge	索引合并优化,合并多个针对单表的range查询
unique_subquery	使用索引查找函数替换in子查询,value in(select primary_key from t where condition)
index_subquery	同unique_subquery，但是索引是非唯一索引
range	范围查询
index	全索引树扫描
all	全表扫描

Extra

可以重点关注下面这些值：

• Using filesort 使用了文件排序
• Using temporary 使用了临时表
• Using index condition 索引下推
• Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access), Using join buffer (hash join) 使用了join buffer，关注join buffer大小配置

优化

设计优化

合理设计表，使表的数据尽可能小，不仅可以占用更少的磁盘空间和更小的内存，还能减少读写次数，提高处理速度.这些设计考虑包括表的存储引擎、列的数据类型、行的格式以及索引方法等

• 在满足业务的情况下，尽量使用小的数据类型
• 如果可以，不允许列为NULL，但是如果需要则用，不建议为NULL值列设置默认值
• 设置合理的ROW_FORMAT，Innodb默认为DYNAMIC
• 主键列尽可能小，在Innodb 中，会自动对非聚族索引进行扩展（每个索引项都会存储主键列的值），小的主键列能更节省空间
• 只在需要的情况下创建索引
• 如果一个长字符串列前缀具有唯一性，如果需要可以使用该前缀建立索引
• 需要join的列使用相同的列定义
• 合理使用范式和反范式

数据类型

整型

• 能使用numeric类型，尽量使用数字类型

字符串

• 如果不需要特定语言的比较或排序，尽量使用二进制类型，包括比较或排序
• 如果不同列需要比较，尽量使用相同的字符集和排序类型
• 如果列的数据比较大（text/varchar/blob），但是又不经常使用，尽量把该列放在单独的表中
• 合理的选择字符集,比如数据类容只有拉丁字符，选择latin1就可以了
• InnoDB存储引擎中，如果使用随机值作为主键，建议使用日期时间作为前缀，从而使连续的主键值相邻存储，插入和检索速度更快
• 如果字符串是可变长度，建议使用varchar，而不是char

日期时间

• 不要使用字符串存储日期时间类型，通常日期时间类型比字符串节省空间，并且可以利用日期函数进行计算
• datetime具有时区无关性，timestamp时区相关性

枚举类型

使用枚举类型代替字符串，枚举比使用字符串占用更少的空间

主键选择

代理主键是与业务无关的实体唯一标识，而自然主键是实体的属性，可以唯一标识实体，比如身份证号，推荐使用代理主键

数据引擎选择

合理的选择数据存储引擎

	MyISAM	InnoDB
事务	不支持	支持
索引	非聚族索引	聚族索引
锁	表锁	表锁、行锁
全文索引	支持	>5.6
适用场景	读	读写

适当冗余

被频繁引用的字段，只能通过join 2张及以上的大表才能获得

索引优化

索引数据结构

Hash（用于memory存储引擎）

• 只能用于等值比较（=、<=、>=）
• 不会提升排序的速度
• 只能整个hash key进行匹配，不能部分匹配

B+tree

• 可用于 =, >, >=, <, <=, or BETWEEN操作
• like 参数以常量打头，也会使用索引
• 如果列定义了索引，列的IS NULL查询也会使用索引

索引存储

• 聚族索引:数据与索引键值存在一起
• 非聚族索引:数据与索引键值分开存储

索引类型

• 主键索引、唯一索引、普通索引、全文索引、组合索引

索引匹配方式

• 全值匹配
• 最左匹配
• 匹配列前缀
• 匹配范围值
• 精确匹配列及范围匹配组合
• 只访问索引查询

名词

• 覆盖索引:检索的数据直接可以从索引中取得

  create table t(id int ,index_col1 varchar(10