sql索引原理及应用

絮絮叨叨

昨晚公司培训sql优化的时候，讲到索引可以提高查询速度，然后怎么创建索引啥啥啥的。我觉得要对一个东西够了解，我们才能够知道在哪些情况下使用他最合适。所以我们先讨论下索引的原理。

原理解释

索引就是类似我们书本目录的东西，我们通过翻目录，查询要读的章节的页码，我们就可以翻到这个页。

可是为什么呢？

我们想，在数据库中，我们单个字段也是要比对整个数据库这个字段，我们的这个目录页必须包含这个字段的所有才能进行查询，你凭什么就能说用索引就能比不用索引要快？

猜想

猜想提出

我们都知道，I/O读取是很耗时间的。如果我们读更少的磁盘可以节省时间。关系数据库表中数据是按链表或是顺序结果进行存储的，如果我们把数据库每一行数据作一个数据块，我们要查找某个用户名为“XXX”的用户详细信息，要读取全部的块来查找。

可是，索引存在另外的块，比起全部数据，读取的块较少，而且存储结构是B树（B+树），树形结构更利于查找，所以，在查询上用索引就效率较高了。

以上是我的猜想，然后我查询了下资料，翻了相关的书。

猜想验证

1. 节省磁盘读取时间。可能原理类似，但我表述有点不专业（等以后回去更加详细的读相关书籍再进行改正），截图（摘自数据库原理、编程与性能）如下：
   

2. B树可以提高查询效率。且不论是不是B树，排序好的值，我们进行查找可以采用二分法等高级查找方法，所以索引可以提高查找效率应该是有依据的。

优缺点综述

截图源博客

使用场景

不要用

1. 建立索引的目的就是帮助查询,如果查寻用不到则索引就没有必要建立。

2. 如果表是经常需要更新的也不适合做索引 。频繁更新会导致索引也会频繁更新，降低写的效率。

3. 唯一性差的字段不适合创建索引。

4. 当给一个字段创建了索引的话，而这个字段要进行like模糊查询的话，那么这个值左边不可以有%，因为索引查询是要从左到右的，你如果给它加上%后，左边的值不是确定的话，它会找不到这个索引。所以在使用like模糊查询的时候，值得左边不可以有%。

5. order by 不会使用索引

6. or 当前后2个字段都有索引时才可以索引出来 否则不可以。

7. 如果数据表过大(5w以上)则有些字段(字符型长度超过(40))不适合作为索引。查询大量数据时,索引有效,但是慢

8. 不会出现在where条件中的字段不该建立索引。

推荐用

1. 当数据多且字段值有相同的值得时候用普通索引。

2. 当字段多且字段值没有重复的时候用唯一索引。

3. 当有多个字段名都经常被查询的话用复合索引。

4. 普通索引不支持空值，唯一索引支持空值。

语句

创建

CREATE [UNIQUE|CLUSTERED] INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)；

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

其中UNIQUE和CLUSTERED为可选项，分别是建立唯一索引和聚簇索引，具体解释为：

UNIQUE:表示此索引的每一个索引值只对应唯一的数据。

CLUSTERED:表示要建立的索引时聚簇索引，即索引项的顺序与表中记录的物理顺序一致的索引组织

删除

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

更多

开始的时候，觉得就一点点东西，一会儿就OK，结果随着写猜想，然后去找验证猜想的材料，发现后面的知识体系大得有点吓人。

期间觉得自己写的这东西太烂了，想放弃了，但还是勉强写完了，不过这东西确实看起来确实也不太理想，周末继续学习，理解更深之后再进行润色。学海无涯啊~~

大学用的书籍大多是讲数据库语句的使用，类似于《数据库原理》 ，可以快速入门使用SQL工具，想要更加深入一点学，个人推荐《数据库原理、编程与性能》