MySQL--索引

索引的概念

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

索引的建立是基于字段的（存储引擎的建立是基于表的）。

一、基于两种存储引擎的索引

1. 基于MyISAM的索引

底层实现为B+树；

数据和索引分离开，是非聚集索引；

叶子节点上存放数据的地址，依靠地址查找数据；

主索引：key值不能重复，一般为主键或唯一键；

辅助索引：key值可以重复，

                  叶子节点上也存储数据的地址，查询数据时只查询一次即可

2. 基于InnoDB的索引

底层实现为B+树；

索引当成数据的一部分来存储，是聚集索引；

叶子节点上存放真实的数据；

主索引：key不能重复；

辅助索引：不存放真实的数据，而是存放主索引的索引值，

                  查询数据时需查询两次（辅助索引和主索引都要查询）

---

【扩展】

索引分为聚簇索引和非聚簇索引两种：

聚簇索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

                  聚簇索引能提高多行检索的速度

非聚簇索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

                 非聚簇索引对于单行的检索很快。

---

二、索引的分类

1. 普通索引：是最基本的索引，它没有任何限制

   索引的创建：

（1）直接创建索引：

CREATE INDEX index_name ON table(column(length))

（2）修改表结构的方式添加索引：

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

 （3）创建表的时候同时创建索引：

06	CREATE TABLE `table` (
07	`id` int(11) NOT NULL AUTO_INCREMENT ,
08	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
11	PRIMARY KEY (`id`),
12	INDEX index_name (title(length))
13	)

2. 主键索引：

简称为主索引，数据库表中一列或列组合（字段）的值唯一标识表中的每一行，该列称为表的主键。

在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型；

该索引要求主键中的每个值都唯一，当在查询中使用主键索引时，它还允许对数据的快速访问。

3. 唯一索引：

与普通索引类似，不同的是，索引列的值必须唯一，但允许有空值（注意和主键不同）；

创建方法和普通索引类似，参考普通索引的创建。

4. 全文索引（FULLTEXT）：

MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；

缺点：对于大容量的数据表，生成全文索引是一个非常消耗时间、硬盘空间的做法。

5. 组合索引：

平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步提高MySQL的效率，就要考虑建立组合索引。

组合主键：多个字段组成一个主键

最左前缀原则：从左到右依次包含

三、索引的优化

问：为什么要优化？

答：索引的建立是以空间换时间，虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

1. 什么时候该建立索引？

（1）查询频繁的字段

（2）按范围查询

（3）经常用于连接的字段，多表查询时起连接的作用的字段，主要是一些外键

2. 什么时候不该建立索引？

（1）查询很少的字段

（2）很少的数据值的字段

（3）text，image和 bit 数据类型的字段

（4）修改性能远远大于检索性能，即数据经常被修改

四、索引的注意事项

1. 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

2. 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度；

例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是维一的，那么就不要对整个列进行索引；

短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3. 索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的；

因此数据库默认排序可以符合要求的情况下不要使用排序操作；

尽量不要包含多个列的排序，如果需要，最好给这些列创建复合索引。

4. like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题；

例如，like “%aaa%” 不会使用索引而 like “aaa%” 可以使用索引。

5. 不要在列上进行运算

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。