三. 索引
3.1 索引数据结构原理
索引在底层的数据结构用的是 B+树,它的原理见数据结构篇。
3.2 聚簇索引和非聚簇索引
3.2.1 聚簇索引、非聚簇索引
聚簇索引规定了一个数据表的排序方式,一个数据表只能有一个聚簇索引,通常使用聚簇索引的是数据表的主键。
聚簇索引和数据行是存放在一起的,所以使用聚簇索引的查询效率很高。同时由于聚簇索引已经进行了排序,所以范围查找的效率很高。但是聚簇索引插入删除的代价可能会比较高,可能会引起页分裂的情况(B+Tree 的数据结构特性,因为 B+Tree 的一个节点的度通常是数据页的大小,向一个满度的节点插入数据,就会导致分页)。
非聚簇索引又称二级索引,可以有多个,它也是一个 B+Tree 结构,它的叶节点指向的是行的 key 字段和主键值。所以通过非聚簇索引搜索时,首先通过非聚簇索引获取到行的主键值(先获取到数据表的聚簇索引值),然后根据主键值获取到数据行信息,相当于比聚簇索引多了一倍的 IO。
聚簇索引和非聚簇索引不是矛盾关系。
3.2.2 聚簇索引、唯一索引、主键的关系
- 聚簇索引与唯一索引:一个表只允许有一个聚簇索引(通常主键作为聚簇索引),但允许有多个唯一索引(通常只有主键作为唯一索引);
- 唯一索引与主键:唯一索引允许有 null 值,主键不允许有 null 值;
3.3 索引设计的原则
3.3.1 键选择原则
- 键设计4 原则
- 为关联字段创建外键;
- 所有的键都必须唯一;
- 避免使用复合键;
- 外键总是关联唯一的键字段;
- 使用系统生成的主键
- 设计数据库的时候采用系统生成的键作为主键,那么实际控制了数据库的索引完整性。这样,数据库和非人工机制就有效地控制了对存储数据中每一行的访问;
- 采用系统生成键作为主键还有一个优点:当拥有一致的键结构时,找到逻辑缺陷很容易。
- 不要使用用户可以编辑的键:不让主键具有可更新性
- 在确定采用什么字段作为表的键的时候,可一定要小心用户将要编辑的字段。通常的情况下不要选择用户可编辑的字段作为键。
- 可选键有时可做主键
- 把可选键进一步用做主键,可以拥有建立强大索引的能力。
3.3.2 索引使用原则
什么时候要使用索引?
- 主键自动建立唯一索引;
- 有高的选择性和过滤性的字段,一般建议在查询数据量 10% 以下再使用索引;
- 经常作为查询条件的列要建立索引,比如 WHERE 或者 ORDER BY 语句;
- 作为排序的列要建立索引;
- 查询中与其他表关联的字段,外键关系建立索引;
- 高并发条件下倾向组合索引( 多个列组合构建的索引 );
- “最左前缀”原则:对于语句
ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col1’,‘col2’,‘col3’);
- 把最常用作为检索或排序的列放在最左,依次递减,组合索引相当于建立了[col1], [col1, col2], [col1, col3], [col1, col2, col3] 四个索引,而 col2 或者 col3 是不能使用索引的。
- “最左前缀”原则:对于语句
- 用于聚合函数的列可以建立索引;例如使用了 max(column_1) 或者 count(column_1) 时的column_1就需要建立索引
什么时候不要使用索引?
- 经常更新的字段不要建立索引;
- 数据唯一性差,有大量重复值的列不建立索引,比如性别只有 “M” “F”,不建议建立索引;
- 表记录太少不要建立索引;
- 只有当数据库里已经有了足够多的测试数据时,它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录,它们往往在执行完第一条查询命令之后就被全部加载到内存里,这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了 MySQL 服务器上的内存总量时,数据库的性能测试结果才有意义。
索引失效的情况:
- 在索引列上使用 IS NULL 或 IS NOT NULL 操作:索引是不索引空值的,所以这样的操作不能使用索引;
- 在一个 SELECT 语句中,索引只能使用一次,如果在 WHERE 中使用了,那么在 ORDER BY 中就不要用了。
- 模糊查询:LIKE 操作中,
’%aaa%’
不会使用索引,也就是索引会失效,但是‘aaa%’’
可以使用索引。 - 在索引的列上使用表达式或者函数会使索引失效;
- 例如:
select * from users where YEAR(adddate)>2007
,将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:select * from users where adddate > ’2007-01-01′
。其它通配符同样,也就是说,在查询条件中使用正则表达式时,只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
- 例如:
- 在查询条件中使用 OR 连接多个条件会导致索引失效;
- 除非 OR 链接的每个条件都加上索引,这时应该改为两次查询,然后用 UNION ALL 连接起来。
- 数据类型的转换:当查询条件存在隐式转换时,索引会失效。比如在数据库里 id 存的 number 类型,但是在查询时,却用了下面的形式:
select * from sunyang where id=‘123’;
索引的优化
- 最左前缀,见前面的解析;
- 带索引的模糊查询优化,见前面的解析;
- 为检索的条件构建全文索引,然后使用
SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);
- 使用短索引
- 对串列进行索引,如果可能应该指定一个前缀长度;
- 例如,如果有一个CHAR(255)的 列,如果在前 10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引;
- 短索引不仅可以提高查询速度,而且可以节省磁盘空间和 I/O 操作。