数据库笔记5——索引优化

一、索引优化技巧

A、 复合索引中，范围查找之后的索引会失效（select id from u where a=1 and b>2 and c=3，这条sql语句中，如果有（a,b,c）的复合索引，c的索引失效）

B、 一张表最好不要创建超过5个索引，尽量用复合索引

C、 左连接将索引创建在右表上合适（如果左表也需要筛选条件，左表也要创建索引），右连接将索引创建在左表上合适

D、联表查询时，小表驱动大表

E、  全值匹配我最爱。

F、  最佳左前缀法则，如果索引是多字段的复合索引，要遵守最佳左前缀法则。指的是where语句里从索引的最左前列开始并且不跳过索引中的字段。 口诀：带头大哥不能死，中间兄弟不能断。

G、 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫 描。

H、索引中范围条件右边的字段会全部失效。 把where语句里范围的查询放最后

I、   尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致）,不要SELECT *

J、  MySQL在使用 != 或者 <> 的时候无法使用索引会导致全表扫描。 is null 、 is not null 也无法使用索引。 like 以通配符开头 %abc 索引失效会变成全表扫描，%加右边。

K、 字符串不加单引号索引失效。

L、 少用 or ，用它来连接时会索引失效

 

二、小表驱动大表

A、 In查询

　　SELECT * FROM `A` WHERE `id` IN (SELECT `id` FROM `B`)

　　IN适合B表比A表数据小的情况

B、 Exists（语法：SELECT....FROM tab WHERE EXISTS(subquery); 该语法可以理解为将主查询的数据，放到子查询中做条件验证，根据验证结果（ true 或是 false ）来决定主查询的数据结果是否得以保留）

　　SELECT * FROM `A` WHERE EXISTS (SELECT 1 FROM `B` WHERE `B`.id = `A`.id)

       此时B表应该比A表数据大

 

三、Order by、group by优化

A、 要排序或者分组的字段建有索引，切满足最左匹配原则。

B、 Group by时先使用where筛选，再用having

C、增大 sort_buffer_size 参数的设置。 增大 max_length_for_sort_data 参数的设置。