索引
在关系数据库中,索引是一种与表有关的数据库结构,它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录,可以
根据目录中的页码快速找到所需的内容。当表中有大量记录时,若要对表进行查询,第一种搜索信息方式是全表搜索,是将所有记录一一取出,和查询条件进行一一
对比,然后返回满足条件的记录,这样做会消耗大量数据库系统时间,并造成大量磁盘I/O操作;第二种就是在表中建立索引,然后在索引中找到符合查询条件的
索引值,最后通过保存在索引中的ROWID(相当于页码)快速找到表中对应的记录。索引是一个单独的、物理的数据库结构,它是某个表中一列或若干列值的集
合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引提供指向存储在表的指定列中的数据值的指针,然后根据您指定的排序顺序对这些指针排序。数
据库使用索引的方式与您使用书籍中的索引的方式很相似:它搜索索引以找到特定值,然后顺指针找到包含该值的行。
1、索引优点
- 加快数据的查询速度;
- 创建唯一性索引,保证数据库表中每一行数据的唯一性;
- 加速表和表之间的连接;
- 在使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间。
2、索引缺点
- 索引需要占物理空间。
- 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,增大的数据库维护的难度。
二、索引种类
索引有普通索引、唯一索引、逐渐索引和复合索引,这四种。如下将做详细介绍。
表结构如下:
DROP TABLE if EXISTS users;
CREATE TABLE users(
id INT(11) NOT NULL AUTO_INCREMENT,
-- id INT(11) NOT NULL,
username VARCHAR(255) NOT NULL,
nickname VARCHAR(255) NOT NULL,
age INT(11) NOT NULL DEFAULT 0,
gender INT(11) NOT NULL DEFAULT 0, -- 0、男,1、女
email VARCHAR(255) NOT NULL -- ,
PRIMARY KEY (id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
1、普通索引
主要目的是为了加快查询速度,所以那些最经常出现的查询条件或排序条件中的列就该创建索引。只要有可能,就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。
语法:
CREATE INDEX 索引名称 ON 表名称(列名称(长度)|列名称);
字符串类型设置成索引的情况比较多,数值类型也可以设置成索引,这里只是实例。
2、唯一索引
普通索引允许被索引的数据列包含重复的值。users表中nickname可以重复,而username一般是不允许重复的。如果能确定某个数 据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处是:一是简化了MySQL 对这个索引的管理工作,这个索引也因此而变得更有效率;二是MySQL每次有新记录插入数据表时,会自动检查新记录的这个字段的值是否已经在某个记录的这 个字段里出现过了;如果是,MySQL将拒绝插入那条新记录。唯一索引可以保证某个数据字段的唯一性,所以唯一索引往往不是为了查询速度,而是为了保证数 据的不重复。
语法:
CREATE UNIQUE INDEX 索引名称 ON 表名称(列名称(长度)|列名称);
它与普通索引类似,但不同的是,其索引列的值必须唯一,但允许有空值。
3、主键索引
主键是一种唯一性索引,但是不能为空。主键的唯一性可以加快查询速度。主键一般在创建表的时候指定,也可以通过修改表的方式加入主键。
添加主键索引的方式有如下两种:
1)、在创建表的时候就指定
CREATE TABLE users(
id INT(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
2)、修改表的结构设置主键的时候指定
ALTER TABLE users MODIFY id INT(10) NOT NULL auto_increment ,ADD PRIMARY KEY (id); -- 设置成主键自增,主键索引
或
ALTER TABLE users ADD PRIMARY KEY (id); -- 设置主键索引
4、组合索引
组合索会使用到表中的多个数据列,这样查询的速度会进一步加快。当我需要同时使用多个条件查询的时候,可以建立组合索引。因为组合索引的查询速 度要远远大于组合索引中每个字读的单个索引的查询速度。假如user表有3个单列索引,查询时这三个列的组合索引会比这是哪个单列索引效率高很多。
创建语法:
CREATE INDEX 索引名称 ON 表名称(列名称1,列名称2,…,列名称n);
DROP INDEX idx_username_gender ON users;
CREATE INDEX idx_username_gender on users(username(11),gender);
SHOW INDEX FROM users; -- 查看所有索引
使用索引尽量注意如下事项:
- 索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
- 使用短索引
对于字符类型的字段创建索引,尽量指定长度,这样不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。
- 索引列排序
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
- like语句操作
一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。因为索引是从前往后匹配的,不会跳过前面的内容而去匹配后面的内容。
- 不要在列上进行运算
select * from users where YEAR(adddate)<2007; -- 在每个行上进行运算,将导致索引失效而进行全表扫描
select * from users where adddate<‘2007-01-01’; -- 使用到索引
- 不使用NOT IN和<>操作
MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。
- 组合索引
必须要从左到右依次精确匹配索引,能匹配多少匹配多少,直到最后一个可以匹配范围索引,只要用了某列范围索引,后面的列的索引就无效了。
匹配值的范围查询(Match a range of values):仅仅使用索引中第1列。即第一列 可以用大于 小于 X>0 and X<1。
匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part):可以利用索引查找last name为Allen,而first name以字母K开始的人。即,第一列 精确匹配,后面一列 范围匹配。