Mysql调优

金字塔

image.png

慢查询日志

image.png image.png image.png image.png

索引

长字段调优

• 额外创建一个字段，存储长字段的hash值, 并创建索引
  INSERT INTO {tableName} (name, hash_name) VALUES('阿巴巴巴巴巴.........', CRC32('阿巴巴巴巴巴.........'))
  hash字段应该具备以下要求：

  1. 字段长度应该比较小， SHA1/MD5是不合适的，可以用CRC32
  2. 应当尽量避免hash冲突，目前来说，流行的hash函数包括CRC32，或者FNV64
  3. 查询语句可以变为：SELECT * FROM {tableName} WHERE hash_name = CRC32('张三') AND name = '张三'

• 前缀索引
  ALTER TABLE {tableName} ADD KEY (name(5)) 取name的前5位作为索引值， 索引长度为5

  1. 这里索引长度怎么选择呢？
     索引的选择性 = 不重复的索引值/数据表的总记录数。
     数值越大，选择性越高，性能越好
  2. 计算选择性
     字段最大选择性： SELECT COUNT(DISTINCT name) / COUNT(*) FROM {tableName}
     计算取前5位的选择性： SELECT COUNT(DISTINCT LEFT(name, 5)) / COUNT(*) FROM {tableName}
  3. 局限性：无法order by、group by， 无法使用覆盖索引

• 后缀索引
  mysql不支持后缀索引，但可以通过创建翻转字段的方式实现。
  比如：对于name列，在存储的时候，把name列翻转过来再存储。在针对翻转字段做前缀索引。 比如：张三啊， 存为：啊三张；

单列索引 vs 组合索引

• SQL存在多个条件，多个单列索引，会用到索引合并
  假如存在索引：index(name)、index(sex)
  SELECT * FROM {tableName} WHERE name = 'zhangsan' AND sex = 1
• 如果出现了索引合并，往往说明索引不够合理
• 如果SQL暂时没有性能问题，暂时可以不用管
• 如果SQL存在性能问题，可以考虑使用组合索引

覆盖索引

• 对于索引X，SELECT 的字段只需从索引就能获得，而无需到表数据里获取，这样的索引就叫做覆盖索引
• 使用覆盖索引时，extra：using index
  image.png

重复索引、冗余索引、未使用的索引

• 索引是有开销的！：新增、更新、删除时

重复索引

• 在相同的列上按照相同顺序创建了相同的索引

冗余索引

• 如果已经存在索引index(A, B), 又创建了index（A），那么index（A）就是index（A，B）的冗余索引
• 一般避免，但有特例，比如以下例子使用索引index（A）时可以利用索引排序，但使用index（A、B）时则会使用using filesort。

SELECT * FROM {table_name} 
WHERE A = 111
ORDER BY 主键

这是因为index（A）在某种意义上可以理解为index（A、主键）。而index（A、B）可以理解为index（A、B、主键）。

join语句优化-JOIN种类、算法与原理1

关联查询

inner join

使用inner join查询时，使用小表作为主表，大表作为关联表