数据库技术知识点总结之三——索引相关内容

接上篇《数据库技术知识点总结之二——Mybatis》

三. 索引

3.1 索引数据结构原理

索引在底层的数据结构用的是 B+树，它的原理见数据结构篇。

3.2 聚簇索引和非聚簇索引

注：参考地址 《和刚入门的菜鸟们聊聊–什么是聚簇索引与非聚簇索引》 《MYSQL索引：对聚簇索引和非聚簇索引的认识》 《通俗易懂 索引、单列索引、复合索引、主键、唯一索引、聚簇索引、非聚簇索引、唯一聚簇索引 的区别与联系》

3.2.1 聚簇索引、非聚簇索引

聚簇索引规定了一个数据表的排序方式，一个数据表只能有一个聚簇索引，通常使用聚簇索引的是数据表的主键。

聚簇索引和数据行是存放在一起的，所以使用聚簇索引的查询效率很高。同时由于聚簇索引已经进行了排序，所以范围查找的效率很高。但是聚簇索引插入删除的代价可能会比较高，可能会引起页分裂的情况（B+Tree 的数据结构特性，因为 B+Tree 的一个节点的度通常是数据页的大小，向一个满度的节点插入数据，就会导致分页）。

非聚簇索引又称二级索引，可以有多个，它也是一个 B+Tree 结构，它的叶节点指向的是行的 key 字段和主键值。所以通过非聚簇索引搜索时，首先通过非聚簇索引获取到行的主键值（先获取到数据表的聚簇索引值），然后根据主键值获取到数据行信息，相当于比聚簇索引多了一倍的 IO。

聚簇索引和非聚簇索引不是矛盾关系。

3.2.2 聚簇索引、唯一索引、主键的关系

• 聚簇索引与唯一索引：一个表只允许有一个聚簇索引（通常主键作为聚簇索引），但允许有多个唯一索引（通常只有主键作为唯一索引）；
• 唯一索引与主键：唯一索引允许有 null 值，主键不允许有 null 值；

3.3 索引设计的原则

3.3.1 键选择原则

1. 键设计4 原则
   • 为关联字段创建外键；
   • 所有的键都必须唯一；
   • 避免使用复合键；
   • 外键总是关联唯一的键字段；
2. 使用系统生成的主键
   • 设计数据库的时候采用系统生成的键作为主键，那么实际控制了数据库的索引完整性。这样，数据库和非人工机制就有效地控制了对存储数据中每一行的访问；
   • 采用系统生成键作为主键还有一个优点：当拥有一致的键结构时，找到逻辑缺陷很容易。
3. 不要使用用户可以编辑的键：不让主键具有可更新性
   • 在确定采用什么字段作为表的键的时候，可一定要小心用户将要编辑的字段。通常的情况下不要选择用户可编辑的字段作为键。
4. 可选键有时可做主键
   • 把可选键进一步用做主键，可以拥有建立强大索引的能力。

3.3.2 索引使用原则

什么时候要使用索引？

1. 主键自动建立唯一索引；
2. 有高的选择性和过滤性的字段，一般建议在查询数据量 10% 以下再使用索引；
3. 经常作为查询条件的列要建立索引，比如 WHERE 或者 ORDER BY 语句；
4. 作为排序的列要建立索引；
5. 查询中与其他表关联的字段，外键关系建立索引；
6. 高并发条件下倾向组合索引（ 多个列组合构建的索引 ）；
   • “最左前缀”原则：对于语句 ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col1’,‘col2’,‘col3’)；
   • 把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1, col1, col2, col1, col3, col1, col2, col3 四个索引，而 col2 或者 col3 是不能使用索引的。
7. 用于聚合函数的列可以建立索引；例如使用了 max(column_1) 或者 count(column_1) 时的column_1就需要建立索引

什么时候不要使用索引？

1. 经常更新的字段不要建立索引；
2. 数据唯一性差，有大量重复值的列不建立索引，比如性别只有 “M” “F”，不建议建立索引；
3. 表记录太少不要建立索引；
   • 只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了 MySQL 服务器上的内存总量时，数据库的性能测试结果才有意义。

索引失效的情况：

1. 在索引列上使用 IS NULL 或 IS NOT NULL 操作：索引是不索引空值的，所以这样的操作不能使用索引；
2. 在一个 SELECT 语句中，索引只能使用一次，如果在 WHERE 中使用了，那么在 ORDER BY 中就不要用了。
3. 模糊查询：LIKE 操作中，’%aaa%’ 不会使用索引，也就是索引会失效，但是 ‘aaa%’’ 可以使用索引。
4. 在索引的列上使用表达式或者函数会使索引失效；
   • 例如：select * from users where YEAR(adddate)>2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate > ’2007-01-01′。其它通配符同样，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
5. 在查询条件中使用 OR 连接多个条件会导致索引失效；
   • 除非 OR 链接的每个条件都加上索引，这时应该改为两次查询，然后用 UNION ALL 连接起来。
6. 数据类型的转换：当查询条件存在隐式转换时，索引会失效。比如在数据库里 id 存的 number 类型，但是在查询时，却用了下面的形式：
   • select * from sunyang where id=‘123’;

索引的优化

1. 最左前缀，见前面的解析；
2. 带索引的模糊查询优化，见前面的解析；
3. 为检索的条件构建全文索引，然后使用
   • SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);
4. 使用短索引
   • 对串列进行索引，如果可能应该指定一个前缀长度；
   • 例如，如果有一个CHAR(255)的 列，如果在前 10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引；
   • 短索引不仅可以提高查询速度，而且可以节省磁盘空间和 I/O 操作。