数据库索引

一、什么是索引

索引是对数据库中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

二、索引分类

按存储方式分为两种；

1. 聚集索引
   例：根据汉字拼音查询，结果集是有序的；

2. 非聚集索引
   例：根据汉字部首查询，结果集是乱序的；

按作用功能分为三种；

1. 普通索引
   最基本的索引，无任何限制；

2. 唯一索引
   索引列的值必须唯一；

3. 全文索引
   类似搜索引擎，查找关键字，而非直接比较值，目前只能用在char、varchar、text列上；

主键索引是一种特殊的唯一索引，建表添加主键时同时自动创建；
组合索引是建立在多个字段上，遵循最左匹配；

三、索引操作

1. 创建索引

1. 创建表时

CREATE TABLE table_name (
    xxx xxx
    [UNIQUE|FULLTEXT] index_name (index_col_name, ...)
);

2. 直接创建

CREATE [UNIQUE|FULLTEXT] INDEX index_name
ON table_name (index_col_name, ...);

3. 修改表时

ALTER TABLE table_name 
ADD [UNIQUE|FULLTEXT] INDEX index_name (index_col_name, ...);

2. 删除索引

ALTER TABLE table_name
DROP INDEX index_name;

3. 修改索引

先删除，在创建；

4. 查询索引

可指定数据库，下列两种都可行；

SHOW INDEX FROM table_name FROM db_name;
SHOW INDEX FROM db_name.table_name;

四、索引方法

1. B-Tree
   适用于范围查找前缀查找，N节点的B树，查找时间复杂度为O(logN)，相当于二分查找；

2. Hash
   适用于等值比较查找，查找复杂度为O(1)；

五、失效原因

1. 索引列参与运算；
   例：SELECT * FROM menu WHERE id + 1 = 3;
SELECT * FROM menu WHERE CONCAT(id) = '1';

2. 使用like，%在左；
   例：SELECT * FROM menu WHERE id like '%1';

3. 类型匹配不符；
   索引字段类型为int，与字符串比较会走索引；
   索引字段类型为char或者vachar，与数字比较不会走索引；

4. OR操作，额外列无索引；
   例：SELECT * FROM code_set WHERE id = 1 OR code ='type';

5. 索引列存在null值；
网络资料显示如此，但实际我基于MySQL数据库试了下，存在null值也会走索引；

六、使用代价

1. 降低更新表的速度，更新表的同时，也需要更新索引；
2. 增加存储空间，索引也是磁盘文件；

七、测试佐证

测试表：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

100w条数据测试，查询语句：

SELECT SQL_NO_CACHE * FROM test WHERE `name` = 'apple_1';

耗时0.328s，然后添加索引：

CREATE UNIQUE INDEX idx_name
ON test (`name`);

再次执行查询，耗时0.001s左右

八、参考文章

1. MySQL索引最左匹配原则的理解?
2. MySQL索引类型