学习Mysql优化总结

EXPLAIN查看优化

id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

type（优化结果）

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

创建数据预优化

• 尽量用数字类型储存 整形（日期）通常比字符串更快（字符串对比慢）
• ip利用inet_aton()和inet_ntoa()函数储存
• 尽量避免使用null（null在数据库内比较特殊的存在，对比null不并等于null，索引遇到null也会有问题）
• 尽量满足需求的最小单位储存（就算不占用，空间已被分配，其他字段也无法拥有）
• 类型固定的字符串可以用枚举代替

 create table enum_test(e enum('fish','apple','dog') not null);
 insert into enum_test(e) values('fish'),('dog'),('apple');
 select e+0 from enum_test;

• 小于三张关系表的遵守三范式

字段不可再分;
有主键，非主键字段必须依赖主键;
非主键字段不能相互依赖;

• join大于三张关系表的可以反三范式（空间换取时间）

大于三张关系表，数据量大会大大影响查询效率
建议适当的冗余字段，减少join关联

• 查询语句尽量带上索引字段（不带索引字段，索引可能会失效）
• where条件中（都有索引的情况下）对比索引字段要放在范围索引字段前面（因为范围索引字段后面都不会使用索引）
• 前缀索引尽量优化到重复前缀较少（索引重复度越大效率越低）
• 尽量给where条件的字段建立索引
• 每张表索引不建议超过5个
• 组合索引组合不建议超过5个
• 在分组或排序情况下建立索引并且两个索引一致asc或desc效率比较高
• log_bin建议打开，用于数据丢失恢复数据
• join_buffer_size建议自己根据实际情况设置，默认2M（join数据大于join_buffer_size时，不会在内存中join，会极大降低join效率）
• log_slow_admin_statements（慢查询日志建议打开，可以从日志里看出哪些查询语句比较慢）

索引分类

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