欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

学习Mysql优化总结

程序员文章站 2022-07-08 14:23:53
...

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(慢查询日志建议打开,可以从日志里看出哪些查询语句比较慢)

索引分类

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