一、查询避坑
1.使用like关键字且使用了"左模糊"或者"全模糊"导致索引失效
例如:
SELECT * FROM user WHERE username like '%三%'
2. 组合索引(多列索引)不满足最左匹配原则导致索引失效
组合索引最多包含16个字段
创建组合索引 (a,b,c)
CREATE INDEX idx_a_b_c ON user(a,b,c);
SELECT * FROM user WHERE b=1 and c=2;
最左匹配原则即(a,b)(a,b,c) (a,c) (a)这几种情况都会走索引,但是(b,c)(b) (c) 都不会走索引
3.使用OR关键字导致索引失效
OR关键字只有在 OR前后的两个条件都是索引列时才生效,否则不使用索引
二、表优化
1.使用JOIN连接查询替换子查询
MySQL需要为内层查询语句的结果建立临时表存储,因此查询速度会有一定影响,使用JOIN连接会更有效率(也不是所有的子查询都会建立临时表,比如自查询,编译器会做优化)
2.垂直拆分
分离低频字段,减少数据读取的代价
3.建立中间表
经常连表查询合并结果的可以建立中间表如用户信息和分组,一次查询需要知道每个用户的分组和分组名,这样我们建立中间表存储用户ID,名称 分组ID,分组名,减少连接查询的次数
4.增加冗余字段
按照范式来说,尽可能减少冗余,减少重复数据的存储,但是实际开发过程中发现冗余一部分字段会加快开发速度,减少连接查询也更加快捷
三、插入优化
批量插入时,去掉唯一索引,插入速度会大幅提升
批量插入时,尽量在一个事务中提交(一般100条一提交),速度可提升
批量插入100w条数据时,先采用MyISAM引擎插入数据,改后改引擎为INNODB,设置INNODB主键速度会比直接INNODB保存快很多