MySQL知识整理9—索引

数据库的组成部分，表，视图，索引，存储过程，触发器，这一部分内容详细介绍索引。

一、索引是什么？

索引是一种提高数据库查询速度的机制，避免了整表扫描，索引就相当于书签。索引是对数据库表或视图中一列或多列的值进行排序的一种结构，通过减少检索查询数据需要的I/O活动量来提高查询的性能。

例如：有一张person表，其中有2W条记录，记录着2W个人的信息。有一个Phone的字段记录每个人的电话号码，现在想要查询出电话号码为xxxx的人的信息。

二、索引的优点和缺点和使用原则

优点：

1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引那么需要的空间就会更大。

3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

因为索引非常占内存，所以索引也需要谨慎添加，那些字段需要索引。

使用原则：

通过上面说的优点和缺点MySQL 索引，我们应该可以知道，并不是每个字段度设置索引就好，也不是索引越多越好，而是需要自己合理的使用。

1. 对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引，

2. 数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。

3. 在一同值少的列上(字段上)不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。

使用索引查询一定能提高查询的性能吗？

不一定。通常,通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价。索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O。因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况：

三、索引的分类

1. 普通索引

这是最基本的索引，它没有任何限制，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

2. 唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

3. 全文索引（FULLTEXT）

MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。

对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

4. 单列索引、多列索引

多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

5. 组合索引（最左前缀）

通俗的说，组合索引就是一个表中一个索引包括多个字段，一个表中多个单列索引并不是组合索引。

四、创建索引

(1)创建表的时候创建索引

1.创建普通索引

这是最基本的索引，它没有任何限制（唯一性之类），作用只是加快数据的访问速度。MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

use study;
/*创建普通索引*/
CREATE TABLE book(
bookid 	int NOT NULL,
bookname VARCHAR(25) NOT NULL,
authors VARCHAR(25) NOT NULL,
info VARCHAR(25) NULL,
comment VARCHAR(25) NULL,
year_publication year not null,
index(year_publication)
);
/*show CREATE TABLE查看表结构*/
show CREATE TABLE book \G
/*explain语句查看索引是否正再使用*/
explain SELECT * from book where year_publication=1990 \G

2.创建唯一索引

唯一索引：唯一索引和普通索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许存在空值（只允许存在一条空值）。组合索引，列值的组合必须唯一。

/*创建唯一索引*/
CREATE TABLE t1(
id int not null,
name char(25) not null,
unique index uniqidx(id)
);
show CREATE table t1 \G

3.创建组合索引

组合索引是在多个字段上创建的一个索引。组合索引遵守“最左前缀”原则，即在查询条件中使用了组合索引的第一个字段，索引才会被使用。因此，在组合索引中索引列的顺序至关重要。

最左原则:假设组合索引为:a,b,c的话;那么当SQL中对应有:a或a,b或a,b,c的时候，可称为完全满足最左原则;当SQL中对应只有a,c的时候，可称为部分满足最左原则;当SQL中没有a的时候，可称为不满足最左原则。

提示：对索引字段进行>,=,

（编辑：92站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!