MySQL索引实现原理

1. 什么是索引

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在表数据十分庞大时，索引可以大大提高查询速度。因为，使用索引后不再遍历整张表，而是先通过索引表找到该行数据的物理地址，然后访问相应的数据。（数据库索引好比是一本书的目录，能加快数据库的查询速度）

2. 索引的类型

2.1 普通索引（Index）

最基本的索引，没有任何限制。

1、创建索引
CREATE INDEX index_name ON table(column(length))
2、修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
3、创建表的时候同时创建索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)
4、删除索引
DROP INDEX index_name ON table;

2.2 主键索引（Primary Key）

一个表只能有一个主键，不允许有空值，一般是在建表的时候同时创建主键索引。

1、创建表时同时创建索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

2.3 全文索引（FullText Index）

主要用来查找文本中的关键字，MySQL允许在char、varchar、text类型上建立全文索引。对于较大的数据集，把数据添加到没有全文索引的表中，然后建立全文索引，往往比有全文索引的表中添加数据的速度快。MySQL5.6版本之后InnoDB存储引擎开始支持全文索引，支持英文，对中文不支持。5.7版本之后通过使用ngram插件开始支持中文。

1、创建表的适合添加全文索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);
2、修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content);
3、直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content);

2.4 组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则。

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

在使用组合索引时可能因为列名长度过长导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字段作为索引。

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3));

3. 索引的优缺点

3.1 索引优点

• 可以快速检索，减少I/O次数（将随机I/O变为顺序I/O）。
• 根据索引分组和排序，可以加快分组和排序。

3.3 索引缺点

• 创建索引和维护索引需要时间成本，时间会随着数据增大而增大。
• 当对表进行删除、添加和修改的时候，索引也要动态维护，降低了数据的维护速度。