索引
主要目的:提高查询效率,对需要经常查询的字段添加索引,可有效提高查询效率。
简介
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
索引分为聚簇索引和非聚簇索引两种,聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。
最多用到的字段要放在组合聚簇索引的前面。Index seek 比 Index scan好,因为索引扫描也就是遍历B树,而seek是B树查找直接定位。
合理的索引设计要建立在对各种查询的分析和预测上。有大量重复值、且经常有范围查询( > ,<,>=,< =)和order by、group by发生的列,可考虑建立群集索引; 经常同时存取多列,且每列都含有重复值可考虑建立组合索引;组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。
优化原则:只要能满足你的需求,应尽可能使用更小的数据类型:例如使用MEDIUMINT代替INT;尽量把所有的列设置为NOT NULL,如果你要保存NULL,手动去设置它,而不是把它设为默认值。尽量少用VARCHAR、TEXT、BLOB类型 ;如果你的数据只有你所知的少量的几个。最好使用ENUM类型(注释:ENUM表示枚举类型)。
类型
普通索引:最基本的索引,它没有任何限制,MyIASM中默认的BTREE类型的索引。如: create index i_sname on t_student(sname);alter table t_student add index i_sname(sname);drop index i_sname on t_student。
唯一索引:索引列的值必须唯一,但允许有空值(注意和主键不同)。如果是组合索引,则列值的组合必须唯一,创建方法和普通索引类似。如: create unique index i_sno on t_stduent(sno);alter table t_stduent add unique i_sno on(sno)。
全文索引(FULLTEXT):FULLTEXT索引仅可用于 MyISAM 表。全文索引创建速度慢,而且对有全文索引的各种数据修改操作也慢。如:ALTER TABLE article ADD FULLTEXT index_content(content);CREATE FULLTEXT INDEX index_content ON article(content)。
索引优化
索引在提高查询速度的同时,也会降低更新表的速度,还要保存索引文件,建立索引会占用磁盘空间的索引文件。
复合索引失效的情况:
索引不会包含有NULL值的列,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的,所以在数据库设计时不要让字段的默认值为NULL。
使用短索引
一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
不要在列上进行运算。将导致索引失效而进行全表扫描。select * from users where YEAR(adddate)<2007,改成:select * from users where adddate<’2007-01-01′。关于这一点可以围观:一个单引号引发的MYSQL性能损失。
关于MySQL索引实现机制和原理(B树、B+树),参考MySQL索引背后的数据结构及算法原理。