SQL索引

索引是什么？

• 定义:索引是一种数据结构，可以快速的查找数据。

• 索引本身实现了高效的查找算法。

• 索引本质上也是一张表，添加索引会导致数据库更新效率变低，更新字段时也会更新索引。

那些情况需要创建索引：

1. 主键自动建立唯一索引。

2. 频繁作为查询条件的字段应该创建索引（where 后面的语句）

3. 查询中与其他表关联的字段，外键关系建立索引。

4. 多字段查询下倾向创建组合索引。

5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

6. 查询中统计或者分组字段。

下列情况不推荐索引：

1. 表记录太少。

2. 经常增删改的表。

3. where条件里用不到的字段不建立索引。

索引分类：

主键索引

• 表中的列表设定为主键后，数据库会自动建立主键索引。

• 单独创建和删除主键索引语法：

  1. 创建主键索引语法：alter table 表名 add primary key (字段)

  2. 创建主键索引语法：alter table 表名 drop primary key;

唯一索引：

• 表中的列创建了唯一约束时，数据库会自动创建唯一索引。

• 单独创建和删除唯一索引语法：

  1. 创建唯一索引语法：alter table 表名 add unique 索引名（字段）或者create unique index 索引名 on 表名(字段)；

  2. 删除唯一索引语法：drop index 索引名 on 表名；

单值索引：

• 一个索引只包含单个列，一个表可以有多个单值索引。

• 单独创建和删除单值索引语法：

  1. 创建单值索引语法：alter table 表名 add index 索引名（字段）或者create  index 索引名 on 表名(字段)；

  2. 删除唯一索引语法：drop index 索引名 on 表名；

组合索引：

• 一个索引只包含单个列，一个表可以有多个单值索引。

• 单独创建和删除组合索引语法：

  1. 创建组合索引语法：alter table 表名 add index 索引名（字段1，字段2...）或者create  index 索引名 on 表名(字段1，字段2...)；

  2. 删除唯一索引语法：drop index 索引名 on 表名；

索引失效：

1. 最佳左前缀法则：如果索引了多列，要遵循最左前缀发着，指的是从索引的最左列开始并且不跳过索引中的列。

   • 左前缀顺序无要求， where a=1 and b=2 和 where b=2 and a=1 效果相同。

2. 不再索引上做任何计算，函数操作，会导致索引失效而转向全表扫描。

3. 存储引擎不能适用索引中范围条件右边的列。

   • explain select * from user where name = "小明" and age > 20 and score = 100

   • 上面的语句中score列的索引会失效。

4. Mysql在使用不等于时无法适用索引会导致全表扫描。

5. is null 可以使用·索引，但是 is not null 无法使用索引。

6. like以通配符开头会使索引试下导致全表扫描

   • explain select * from user where name like "%明"：索引会失效。

   • explain select * from user where name like "明%"：索引可以正常使用 。

7. 字符串不加单引号索引会失效。

8. 使用 or 链接会失效。

建议：

1.对于单值索引，尽量选择针对当前查询字段过滤性更好索引。

2.对于组合索引，当前where查询中过滤性更好的字段在索引字段顺序中位置越靠前越好。

3.对于组合索引，尽量选择能够包含在当前查询中 where子句中更多字段的索引。

4.尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。