清水泥沙

MySql（查询优化）

  1. mysql数据类型

(1) 整数类型

① tinyint

② smallint

③ mediumint

④ int

⑤ bigint

(2) 实数类型

① float

② double

③ decimal 可以存储比bigint还大的整数，可以用于存储精确的小数点

(3) 字符串类型

① varchar 可变长度的字符串类型，对于经常变更的数据char比varchar更好，char不容易产生碎片

② char 定长字符串类型，对于较短的数据varchar存储空间更有效率

③ blob

④ text 查询回使用临时表导致严重的性能开销

(4) 枚举

① 有时可以把常用的字符串替换成枚举类型

② 把不重复的集合存储成一个预定义的集合

③ 尽量避免使用数字作为enum作为常量，容易混乱

(5) 日期类型

① timestamp 存储的是整形，相对空间效率更高

② Datetime

(6) 列属性

① auto_increment自增

② default 默认值

③ not null 非空

④ zerofill 无符号填充

  1. 索引

(1) 索引对性能的影响

① 减少数据检索数量

② 帮助服务器减少排序和临时表

③ 将随机IO变成顺序IO

(2) 查询优化

① 开启慢SQL日志

② 使用show profile查看SQL耗时

③ 使用 show state 计数器

④ 使用 show processlist查看线程正常状态

⑤ 使用 explian分析单个SQL

(3) 优化查询过程中的数据访问

① 确定是否在查询大量数据导致性能下降

② 确认是否检索过多的列和行

③ 确认是否在分析大量不必要的数据行

④ 避免使用如下SQL

  1. 查询不必要的行数，使用limit
  2. 多表关联返回所有的列，指定需要的列
  3. 总是取出全部的列，select *会无法完成索引覆盖，需要进行回表查询

⑤ 重复查询相同的数据使用缓存

⑥ 是否扫描额外的记录

  1. 使用explain进行分析，如果发现扫码大量数据但是只返回少数的行

a. 使用索引覆盖，创建适合索引的索引

b. 修改表结构，可以使用冗余的字段避免连接查询。

c. 重写SQL语句，让优化器可以更优的方式执行查询

(4) 优化长难的查询语句

① 一个复杂查询还是多个简单查询

  1. MYSQL内部能每秒扫码大量的数据，但响应数据要慢得多，使用尽量少的查询是好的，但有时候可以根据业务需要将一个大的查询分为多个小查询也是必要的

② 切分查询

  1. 将一个大的查询分为多个小的查询

a. 一次性删除1000万条数据比一次删除一万条数据，开销更大

③ 分解关联查询

  1. 将一条关联语句分解成多条的SQL来执行，缓存效率更高，也可以减少所的竞争

(5) 优化特定类型的查询语句

① 优化count（）查询

  1. count(*)会忽略所有的列，直接统计所有列数。尽量不使用count(列名)
  2. 增加数据统计汇总表
  3. 为汇总表做缓存

② 优化关联查询

  1. 确定on或者using子句中的列有索引
  2. 确保group by order by 只有一个列，否则可能不使用索引

③ 优化子查询

  1. 使用关联查询替代

④ 优化group by 和distinct

  1. 使用索引来进行优化
  2. group by 不需要排序的时候加一个order by null ，MySQL不会再进行文件排序
  3. 可以使用程序处理代替group by

⑤ Limit 分页

  1. limit偏移量大，查询效率低

a. 记录上一次查询的最大ID，使用where大于ID来进行查询

⑥ 优化union查询

  1. 使用union all优化