浅谈数据库之索引

在数据库中，索引可以

• 帮助引擎减少扫描的数据行数，提高效率
• 帮助服务器对数据进行排序和分组，因为索引的底层数据结构B+树是有序的
• 将随机I/O变为顺序I/O（这个什么意思呢～～～我目前也说不出来 ：)）

索引类型

• 按底层数据结构分
  • 哈希索引：使用哈希表做索引，可以做到快速精确查找，但是不能实现区间查找和排序，适合单条查找。
  • B+树索引：使用B+树做索引，因其树形结构，可以实现排序和区间查找，用得比较多。
• 按物理结构划分
  • 聚簇索引：在B+树的叶子节点，存储的是行数据（即记录），InnoDB使用的是这个。
  • 非聚簇索引：在B+树的叶子节点，存储的是行数据所在位置的指针，MyISAM使用的是这个。
• 按逻辑层面划分
  • 主键索引：一种特殊索引，索引中的键值不能为空。
  • 普通索引：最基本的所以，没有任何限制。
  • 唯一索引：索引值必须唯一，允许有空值。
  • 全文索引：全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
  • 组合索引：为了提高数据库的查询效率可以使用组合索引，如（col1, col2, col3)，查询时遵循“最左前缀”原则。

最左前缀–>传送门

---

并不是对某字段建立了索引，查询时若涉及该字段就一定会使用索引

索引使用注意事项

1. 考虑在where子句和order by 设计的列上建立索引。
2. 避免在where子句中对字段进行 null 判断，会导致不使用索引改用全表扫描。
3. 避免使用 <>、NOT IN、！=，会导致全表扫描
   • 使用NOT EXISTS 代替 NOT IN
   • 使用 or 代替 <>，如 id <> 3 改为 id < 3 or id > 3
4. 避免在where子句中使用or，如果其中一个字段没有建立索引，将会导致引擎使用全表扫描。可以使用 union来减少查询次数

   # 假设num没有建立索引
   select id from table_a where num = 20 or name = 'user_name'
   # 改为
   select id from table_a where num = 20
   union
   # 使用索引
   select id from table_a where name = 'user_name'
   

5. 避免使用 IN 和 NOT IN。
   1. 对于连续的值，可以使用 between，如

   select id from table_a where num in (1,2,3,4)
   # 改为
   select id from table_a where num between 1 and 4
   

   2. 使用 exsits 代替 in

   select id from table_a where num in (select num from table_b)
   # 改为
   select id from table_a where exits (select table_b.num from table_b where num = table_b.num)
   

6. mysql like 查询（模糊查询时，%在最左边出现时，不使用索引。
   1. like ‘%str%’ 不使用索引。
   2. like ’%str‘ 不使用索引。
   3. like ’str%‘ 使用索引。
   4. 对于 1、 2 可以先使用前匹配的方法查找数据，再对结果集进行like匹配
7. 避免在where 子句中使用参数，会导致全表扫描。

   select id from table_a where num = @num
   # 改为
   select id from table_a with(index(索引名)) where num = @num
   

8. 避免在where子句中对字段进行表达式运算，即不要在 = 左边进行函数、算术或其他形式的表达式运算。

   # 表达式运算
   select id from table_a where num / 2 = 100
   # 改为
   select id from table_a where num = 100 * 2
   
   # 函数操作
   select id from table_a where substring(name, 1, 3) = 'abc'
   select id from table_a where datediff(day, createdate, '2019-5-12'
   # 改为
   select id from table_a where name like 'abc%'
   select id from table_a where createdate >= '2019-5-12' and createdate < '2019-5-12'
   

9. 使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且尽可能的让字段顺序与索引顺序一致。
10. 使用 join 查询
11. 使用union来代替手动创建临时表，因为union本身会创建临时表，并会在查询结束后将临时表删除。使用union时，各条select语句的字段数目要相同

select name, phone from client union
select name, birthday from author union
select naem, supplier from product

---

主键与索引的比较

• 概念
  • 主键唯一地标记一行，作为一个可以被外键有效引用的对象
  • 索引是一种特殊的文件，里面包含着 对数据表里所有记录的引用指针
• 区别
  • 主键是逻辑键，索引是物理键。主键是不实际存在的，而索引是实际存在于数据库中的
  • 主键是为了标志数据库记录的唯一性，不允许记录重复，且键值不为空值
  • 索引是为了提高数据查询的效率，相当于字典的目录。对于不同的索引，有些索引键值可以为空，可以不唯一
  • 一个表中，主键只有一个，但是索引却可以多个
  • 使用主键会使数据库自动创建主键索引，也可以对非主键建立索引
  • 主键索引外的索引键值可以为空，主键索引也是唯一索引

---

何时建立索引

1. 适合
   1. 表的主键（这个会自动创建）
   2. 数据量大的表应该建立
   3. 经常与其他表连接的表，在连接的字段上建立索引
   4. 经常出现在where子句的字段，特别是数据量大的表，应该建立
   5. 索引应该建立在选择性高的字段上
2. 不适合
   1. 对大的文本字段，甚至超长字段，不建立
   2. 数据库频繁进行增删改操作的表， 不要建立太多的索引（涉及索引的维护时间）
   3. 小型表不建立建立索引
   4. 亿级数据级以上，建议使用非关系型数据库
   5. 特殊字段，如性别，不适合创建索引

---

索引的设计

1. 选择唯一性的字段作为索引：唯一索引查询更快速
2. 为经常需要排序、分组和联合的字段建立索引（ORDER BY，GROUP BY， DISTINCT、UNION）
3. 为常作为查询条件的字段建立索引
4. 限制索引数目，避免数据更新时带来的索引维护开销
5. 尽量使用字段值短的字段作为索引，如对 char(100) 和 char(10) 的字段建立索引，检索时间差别很多
6. 删除不再使用或者很少使用的索引（考虑到维护开销）
7. 小表不应建立索引

---