索引类型
索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。mysql索引大致可以分为普通索引、唯一索引、主键索引、组合索引、全文索引，下面我们就来具体了解下各个索引的区别：
普通索引
基本的索引类型，值可以为空，没有唯一性的限制。
- 直接创建索引
CREATE INDEX index_name ON table_name(col_name);
-- 修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name(col_name);
-- 创建表的时候同时创建索引
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(255) NOT NULL ,
`age` int(4) NULL ,
PRIMARY KEY (`id`),
INDEX index_name (age(4))
)
-- 删除索引
DROP INDEX index_name ON table_name;
-- 或
alter table `表名` drop index 索引名;
唯一索引
索引列中的值必须是唯一的，但是允许为空值（只允许存在一条空值）。
-- 创建单个索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
-- 创建多个索引
CREATE UNIQUE INDEX index_name on table_name(col_name,...);
-- 修改表结构
-- 单个
ALTER TABLE table_name ADD UNIQUE index index_name(col_name);
-- 多个
ALTER TABLE table_name ADD UNIQUE index index_name(col_name,...);
主键索引
主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键。
-- 主键索引(创建表时添加)
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(255) NOT NULL ,
`age` int(4) NULL ,
PRIMARY KEY (`id`)
)
-- 主键索引(创建表后添加)
CREATE TABLE `order` (
`orderId` varchar(36) NOT NULL,
`productId` varchar(36) NOT NULL ,
`time` varchar(20) NULL DEFAULT NULL
)
alter table `order` add primary key(`orderId`);
组合索引
在多个字段上创建的索引。组合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。
-- 创建一个复合索引
create index index_name on table_name(col_name1,col_name2,...);
-- 修改表结构的方式添加索引
alter table table_name add index index_name(col_name,col_name2,...);
全文索引
全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建。可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。MyISAM支持全文索引，InnoDB在mysql5.6之后支持了全文索引。 全文索引不支持中文需要借sphinx(coreseek)或迅搜<、code>技术处理中文。
-- 创建表的适合添加全文索引
CREATE TABLE `news` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` varchar(255) NOT NULL ,
`content` text NOT NULL ,
`time` varchar(20) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
)
-- 修改表结构添加全文索引
ALTER TABLE table_name ADD FULLTEXT index_fulltext_content(col_name);
索引实现
下面我们来具体看下载不同的数据库引擎下上述索引的实现原理：
innodb索引
主键索引
每个InnoDB表都有一个主键索引(也叫聚簇索引) ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。。InnoDB创建索引的具体规则如下：
在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引
如果表没有定义主键，且存在非空unique列，则选择第一个非空unique列为主键，构建聚簇索引；
如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增
除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。 主键索引的叶子节点会存储数据行（所以一般进行的非主键索引检索至少需要经历两次磁盘IO），辅助索引只会存储主键值。具体主键索引存储结构如下：
等值查询数据
先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）
将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）
检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于28的索引项，直接可以获取整行数据。将改记录返回给客户端。（1次磁盘IO）
磁盘IO数量：3次。
辅助索引
除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。以表user的age列为例，age索引的索引结果如下图：
底层叶子节点的按照（age，id）的顺序排序，先按照age列从小到大排序，age列相同时按照id列从小到大排序。使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录。
场景一：等值查询的情况
select * from t_user_innodb where age=19;
根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。
磁盘IO数：辅助索引3次+获取记录回表3次
组合索引
还是以自己创建的一个表为例：表 abc_innodb，id为主键索引，创建了一个联合索引idx_abc(a,b,c)。
CREATE TABLE `abc_innodb`
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` varchar(10) DEFAULT NULL,
`d` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_abc` (`a`, `b`, `c`)
) ENGINE = InnoDB;
组合索引的数据结构：
组合索引的查询过程：
select * from abc_innodb where a = 13 and b = 16 and c = 4;
最左匹配原则
最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。
在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。
就像上面的查询，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起。
可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引。、
组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。
覆盖索引
覆盖索引并不是说是索引结构，覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况，在上面abc_innodb表中的组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。可以看一下执行计划：
MyIsam索引
MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。
主键索引
表user的索引存储在索引文件user.MYI中，数据文件存储在数据文件 user.MYD中。简单分析下查询时的磁盘IO情况：
场景一：根据主键等值查询数据
先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）
将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）
检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于30的索引项。（1次磁盘IO）
从索引项中获取磁盘地址，然后到数据文件user.MYD中获取对应整行记录。（1次磁盘IO）
将记录返给客户端。
磁盘IO次数：3次索引检索+记录数据检索。
场景二：根据主键范围查询数据
先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）
将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）
-
检索到叶节点，将节点加载到内存中遍历比较16<28，18<28，28=28<47。查找到值等于28的索引项。
根据磁盘地址从数据文件中获取行记录缓存到结果集中。（1次磁盘IO）
我们的查询语句时范围查找，需要向后遍历底层叶子链表，直至到达最后一个不满足筛选条件。
向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，28<47=47，根据磁盘地址从数据文件中获取行记录缓存到结果集中。（1次磁盘IO）
最后得到两条符合筛选条件，将查询结果集返给客户端。
磁盘IO次数：4次索引检索+记录数据检索。
备注：以上分析仅供参考，MyISAM在查询时，会将索引节点缓存在MySQL缓存中，而数据缓存依赖于操作系统自身的缓存，所以并不是每次都是走的磁盘，这里只是为了分析索引的使用过程。
辅助索引
在 MyISAM 中，辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。
索引失效情况
情况一：where语句中包含or时，可能会导致索引失效
使用or并不是一定会使索引失效，你需要看or左右两边的查询列是否命中相同的索引。
-- 假设user表中的user_id列有索引，age列没有索引
-- 能命中索引
select * from user where user_id = 1 or user_id = 2;
-- 无法命中索引
select * from user where user_id = 1 or age = 20;
-- 假设age列也有索引的话，依然是无法命中索引的
select * from user where user_id = 1 or age = 20;
可以根据情况尽量使用union all或者in来代替，这两个语句的执行效率也比or好些。
情况二：where语句中索引列使用了负向查询，可能会导致索引失效
负向查询包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等。其实负向查询并不绝对会索引失效，这要看MySQL优化器的判断，全表扫描或者走索引哪个成本低了。
情况三：索引字段可以为null，使用is null或is not null时，可能会导致索引失效
其实单个索引字段，使用is null或is not null时，是可以命中索引的。
情况四：在索引列上使用内置函数，一定会导致索引失效
比如下面语句中索引列login_time上使用了函数，会索引失效：
select * from user where DATE_ADD(login_time, INTERVAL 1 DAY) = 7;
情况五：隐式类型转换导致的索引失效
如下面语句中索引列user_id为varchar类型，不会命中索引：
select * from user where user_id = 12;
情况六：对索引列进行运算，一定会导致索引失效
运算如+，-，*，/等，如下：
select * from user where age - 1 = 10;
优化的话，要把运算放在值上，或者在应用程序中直接算好，比如：
select * from user where age = 10 - 1;
情况七：like通配符可能会导致索引失效
like查询以%开头时，会导致索引失效。解决办法有两种：
- 将%移到后面，如：
select * from user where `name` like '李%';
- 利用覆盖索引来命中索引：
select name from user where `name` like '%李%';
情况八：MySQL优化器的最终选择，不走索引
上面有提到，即使完全符合索引生效的场景，考虑到实际数据量等原因，最终是否使用索引还要看MySQL优化器的判断。当然你也可以在sql语句中写明强制走某个索引。
