数据库索引

前言
索引作用和类型
如何利用索引优化查询
优化索引
索引的缺点

前言

最近做的一个项目，由于包含比较大的数据量，mysql的查询速度出现了明显的瓶颈，只能研究一下如何优化数据库的查询性能了，因为我比较熟悉的也是mysql，所以针对mysql的优化查了一些资料，总觉得得整理出来。当然，数据库的优化还是一个比较大的话题，设计的方面也很多，一个一个来吧，本文先对索引做一些总结。

索引作用和类型

为了方便说明，我们假设创建了一个数据库表：

CREATE TABLE student(
    ID INT NOT NULL,
    name VARCHAR(16) NOT NULL,
    age INT NOT NULL,
    sex CHAR(1) NOT NULL
);

假设我们往student表里插了10000条记录，当我们查找名字为Tom的学生的时候，使用如下sql:

SELECT * FROM student WHERE name='Tom'

如果在name上已经建立了索引，MySQL无须任何扫描，即准确可找到该记录。相反，MySQL会扫描所有记录，即要查询10000条记录。

数据库的索引是有类型的，不同的数据库可能有不同的索引类型，mysql目前只有四种索引类型：

普通索引
唯一索引
主键索引
组合索引

普通索引

普通索引是最普遍的索引，理论上所有数据库都会支持这类索引，没有任何限制，创建的方式如下：

-- 方式1：创建索引
CREATE INDEX indexName ON tableName(columnName(length));
-- 方式2:修改表
ALTER TABLE tableName ADD INDEX indexName (columnName(length)) ;
-- 方式3
CREATE TABLE tableName(
    ID INT NOT NULL,
    field1 VARCHAR(16) NOT NULL,
    field2 INT NOT NULL,
    field3 CHAR NOT NULL,
    INDEX indexName (field1(length))
);

如：

-- 方式1
CREATE INDEX nameIndex ON student(name(10));
-- 方式2
ALTER TABLE student ADD INDEX nameIndex (name(10)) ;
-- 方式3
CREATE TABLE student(
    ID INT NOT NULL,
    name VARCHAR(16) NOT NULL,
    age INT NOT NULL,
    sex CHAR(1) NOT NULL,
    INDEX nameIndex (name(10))
);

对于CHAR和VARCHAR类型，length可以小于字段实际长度（大于实际长度是没有意义的）；如果是BLOB和TEXT类型，必须指定 length。

唯一索引

它与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建方式如下：

-- 方式1：创建索引
CREATE UNIQUE INDEX indexName ON tableName(columnName(length));
-- 方式2:修改表
ALTER TABLE tableName ADD UNIQUE INDEX indexName (columnName(length)) ;
-- 方式3:创建表时一起创建索引
CREATE TABLE tableName(
    ID INT NOT NULL,
    field1 VARCHAR(16) NOT NULL,
    field2 INT NOT NULL,
    field3 CHAR NOT NULL,
    UNIQUE INDEX indexName (field1(length))
);

如：

-- 方式1
CREATE UNIQUE INDEX nameIndex ON student(name(10));
-- 方式2
ALTER TABLE student ADD UNIQUE INDEX name (name(10)) ;
-- 方式3
CREATE TABLE student(
    ID INT NOT NULL,
    name VARCHAR(16) NOT NULL,
    age INT NOT NULL,
    sex CHAR(1) NOT NULL,
    UNIQUE INDEX nameIndex (name(10))
);

对于索引长度的限制和普通索引是一样的。

主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

-- 方式1
CREATE TABLE tableName(
    ID INT NOT NULL,
    field1 VARCHAR(16) NOT NULL,
    field2 INT NOT NULL,
    field3 CHAR NOT NULL,
    PRIMARY KEY (ID)
);
-- 方式2
ALTER TABLE tableName ADD PRIMARY KEY(ID) ;

如：

-- 方式1
CREATE TABLE student(
    ID INT NOT NULL,
    name VARCHAR(16) NOT NULL,
    age INT NOT NULL,
    sex CHAR NOT NULL,
    PRIMARY KEY (ID)
);
-- 方式2
ALTER TABLE student ADD PRIMARY KEY(ID) ;

组合索引

组合索引指的是以多个字段为单位创建索引，当查询的过滤条件使用了这几个字段的时候，查询性能会更加出色，创建方式如下：

-- 方式1
CREATE INDEX indexName ON tableName(field1(length),filed2(length),field3(length));
-- 方式2
ALTER TABLE tableName ADD INDEX indexName (field1(length),filed2(length),field3(length));
-- 方式3
CREATE TABLE student(
    ID INT NOT NULL,
    name VARCHAR(16) NOT NULL,
    age INT NOT NULL,
    sex CHAR(1) NOT NULL,
    INDEX groupIndex (name(10),age,sex)
);

如：

-- 方式1
CREATE INDEX groupIndex ON student(name(10),age,sex);
-- 方式2
ALTER TABLE student ADD INDEX groupIndex (name(10),age,sex);
-- 方式3
CREATE TABLE student(
    ID INT NOT NULL,
    NAME VARCHAR(16) NOT NULL,
    age INT NOT NULL,
    sex CHAR(1) NOT NULL,
    INDEX groupIndex (name(10),age,sex)
);

注意，这里创建的是普通的组合索引，实际上也能创建唯一索引，创建方式和单个字段的唯一索引方式相似，只要在INDEX改为UNIQUE INDEX即可。

我们这里对三个字段创建了一个组合索引，一般情况下，会比对三个字段创建三个单独索引性能更好一些，因为mysql在查询时，只能用到其中的那个它认为是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

name,age,sex
name,age
name

这里没有age,sex，这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。因此，并不是包含这三个列的查询就一定会用到这个组合索引，比如：

SELECT * FROM student WHREE age=20 AND sex='0'
SELECT * FROM student WHREE sex='1'

这两个查询是不会用到这个组合索引的，因为我们知道，组合索引并没有针对age,sex和sex的索引。

如何利用索引优化查询

如何建立索引才能得到最好的性能呢？一般来说这跟业务相关，根据我们的业务需求，对最常使用的查询字段建立索引可以极大提高系统性能。

建立索引前，我们需要想了解数据库在什么情况下会使用索引：

MySQL只对<，<=，=，>，>=，BETWEEN，IN使用索引
MySQL对LIKE ‘%name%’不使用索引，对LIKE ‘name%’使用索引

比如：

-- 会使用索引的sql
SELECT * FROM student WHERE name='Tom';
SELECT * FROM student WHERE age>2;
SELECT * FROM student WHERE name LIKE 'T%';
-- 不会使用索引的sql
SELECT * FROM student WHERE name LIKE '%T%';

因此，在使用LIKE的时候要特别注意。

优化索引

为了让索引能发挥最大的作用，我们建立索引的时候就需要根据业务进行特定的设计，一般会有如下几个考虑的点：

索引不会包含有NULL值的列

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

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
不要在列上进行运算
```
  SELECT * FROM student WHERE YEAR(adddate)<2007;
```
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:
```
  SELECT * FROM student WHERE adddate<'2007-01-01';
```
不使用NOT IN和<>操作

这个很简单，因为这两个操作不会使用索引。

索引的缺点

索引虽然能提高查询性能，但是泛滥使用也会导致一些问题：

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。