MySQL优化总结

建表原则

• 定长字段与变长字段分离，使存储空间整齐，加速查询；
• 常用字段与不常用字段分离
• 适当增加冗余字段，如一对多查询需要关联统计

列类型选择

• 字段类型选择优先级：int,bigint > date,time > enum,char > varchar > blob, text；
• 字段长度够用就行，不要过大
• 尽量不要使用NULL，不利于索引

索引（btree、hash）

• 常用多列（联合）索引，索引命中顺序从左向右；
• 聚簇索引（InnoDB）会发生索引覆盖，非聚簇索引（MyISAM）无影响；
• 理想索引：查询频繁、区分度高、长度小、索引列尽量覆盖常用查询字段；
• 索引列不能使用表达式或函数

2. 对一列的前n个字符使用前缀索引：create index 索引名 on table(列名(n))
3. 区分度低的列尽量不要建立索引
4. 使用联合索引时经常出现在where语句中的列放在的前面
5. 使用索引优化排序的3个条件：
   索引列顺序与order by子句顺序一致；
   索引列的方向（asc、desc）与order by子句一致；
   多表关联时，order by中的字段需要在关联表中的第一个表。
6. InnoDB存储引擎的表，数据的逻辑顺序与主键顺序一致，可以使用主键排序
7. 索引可以在存储引擎过滤不必要的锁，减少锁定的行数，进而提升并发性

查询优化

• limit分页查询随着select ...limit x,y;中的x增大而降低效率，可使用
  select ... from 表 join (select id from 表 limit x,y) as a on 表.id = a.id;进行优化，先从索引查找缩小范围

其它

• 长时间数据变化后，索引文件和数据文件都将产生空洞，形成碎片，可以使用optimize table 表名;来优化；