mySQL—索引

————————————————————————————————

什么是索引？

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。
索引的作用就相当于目录的作用。 打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

为什么要使用索引？

1、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

2、可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。

3、帮助服务器避免排序和临时表。

4、将随机IO变为顺序IO

5、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

索引的优缺点

索引的优点

• 可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
• 毕竟大部分系统的读请求总是大于写请求的。
• 另外，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

索引的缺点

• 创建索引和维护索引需要耗费许多时间
  • 当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
• 占用物理存储空间
  • 索引需要使用物理文件存储，也会耗费一定空间。

索引这么多优点，为什么不对表中的每一个列创建一个索引呢？

1、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

2、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

3、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

使用索引的注意事项？

1. 在经常需要搜索的列上，可以加快搜索的速度；
2. 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
3. 在经常需要排序的列上创 建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
4. 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
5. 在经常用在连接的列上，这 些列主要是一些外键，可以加快连接的速度；
6. 避免 where 子句中对字段施加函数，这会造成无法命中索引。
7. 在使用InnoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
8. 将某一列设置为default null，where 是可以走索引，另外索引列是否设置 null 是不影响性能的。 但是，还是不建议列上允许为空。最好限制not null，因为null需要更多的存储空间并且null值无法参与某些运算。
9. 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 chema_unused_indexes 视图来查询哪些索引从未被使用
10. 在使用 limit offset 查询缓慢时，可以借助索引来提高性能

哪些操作会引起索引失效?

1）隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误。

• 由于表的字段 S_id 定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.
• 错误的例子：select * from test where S_id=111;
• 正确的例子：select * from test where S_id=‘111’;

2） 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)

• 错误的例子：select * from test where id-1=9;
• 正确的例子：select * from test where id=10;

3）使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引。

• 错误的例子：select * from test where round(id)=10;
• 说明，此时id的索引已经不起作用了

4）以下使用会使索引失效，应避