关于数据库索引的基本知识

索引
在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的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开始的人。即，第一列精确匹配，后面一列范围匹配。

posted on 2016-02-20 08:54 爱漂泊人生阅读(952) 评论(0) 编辑收藏举报

刷新页面返回顶部

关于数据库索引的基本知识

导航

公告