MySQL索引入门

介绍

索引就相当于表的目录一样，可以提高我们查询数据的效率，是一种数据结构。常见的MySQL主要有两种结构：Hash索引和B+树索引。使用哪种数据结构跟存储引擎有关，InnoDB引擎默认使用的是B+树。
补充： B+树索引和哈希索引的区别？

• 哈希索引适合等值查询，B+树索引适合范围查询。
• 哈希索引无法利用索引完成排序，B+树是一种天然有序的，对范围查询不需要全表查询。
• 哈希索引不支持联合索引的最左匹配原则。
• 如果存在大量重复键值，哈希索引效率会很低，因为存在哈希碰撞。

1、索引的选取

• 1、选择小的数据类型：越小的数据类型通常在磁盘，内存和CPU缓存中占用更少的空间，处理速度更快
• 2、选择简单的数据类型：整型数据比字符，处理开销更小，因为字符串的比较更复杂
• 3、尽量避免NULL：在MySQL中，含有空值的列很难进行查询优化，因为空值实的索引，索引的统计信息以及比较运算更加复杂

什么场景不适合使用索引：

• 1、对于那些在查询中很少使用到的或者参考到的列不适合创建索引：因为很少使用，所以有无索引并不能提高查询速度，相反还需要维护索引和占用空间，得不偿失。
• 2、对于那些只有很少数据值的列不适合创建索引：比如性别列–只有男女两种，在查询的结果中，结果集的数据行占了表中数据行的很大比例，相当于做全表查询，所以没有必要增加索引，可减少开销。
• 3、对于那些定义为text、image、和bit类型的列不适合创建索引：因为这些列的数据量要么相当大，要么取值很少。
• 4、当使用修改的操作远大于查询操作时，不适合创建索引：因为增加索引，虽然可以提高检索速度，但同时也会降低修改性能。
• 5、对于不会出现在where条件中的字段不适合创建索引：因为你查找的时候都不会用到。
• 6、对于数据量较小的表不适合创建索引：因为查询速度优化不了多少，相反还会有额外的开销。

什么场景适合使用索引：

• 1、表的主键，外键必须有索引：默认创建。
• 2、数据量超过300的表应该创建索引。
• 3、经常与其他表进行连接的表，在连接字段上应该创建索引，如左连接，右连接。
• 4、经常出现在where子句中的字段应该创建索引，and使用联合索引，or要确保两个字段都是单列索引。
• 5、经常用于排序的列应该创建索引，因为索引已经排序。
• 6、经常用于范围内搜索的列应该创建索引，因为索引已排序，取范围容易。

2、索引的优缺点

优点：

• 由数据库中的一列或多列组合而成，可以提高对表中数据的查询速度。

缺点：

• 创建和维护索引需要耗费时间。
• 索引虽然可以提高查询速度，但也会降低修改性能，因为修改的时候可能会重建索引。一个表的索引最好不要超过6个。
• 最好先插入数据，再创建索引，速度比先创建索引，再插入数据快，因为

3、索引的分类

1）普通索引：没有任何限制，仅加速查询，是最常用的基本索引。
在已存在的表上创建索引：

CREATE INDEX index_name on healerjean(name);

在创建表的同时创建索引：

create table healerjean (
  id   bigint(20)  NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT  '主键',
  name  VARCHAR(32) NOT NULL COMMENT '姓名',
  email VARCHAR(64) NOT NULL COMMENT  '邮箱',
  message text DEFAULT  NULL COMMENT '个人信息',
  INDEX index_name (name) COMMENT '索引name'
) COMMENT  = '索引测试表';

注意：如果是blob和text类型的列，必须指定length。

create index ix_ extra on healerjean(message(200));

删除索引：

drop index_name on healerjean;

alter TABLE healerjean drop index index_name;

查看索引：

show index from healerjean;

2）唯一索引：与普通索引不同的 是：加速查询和列值唯一

CREATE UNIQUE INDEX index_mail on healerjean(mail);

3）主索引：主键的索引，与唯一索引的区别就是使用primary关键字，默认创建主键时建立索引

ALTER TABLE healerjean ADD PRIMARY KEY(id);

4）外键索引：也是默认创建外键时建立索引。

5）联合索引：

ALTER TABLE table_name ADD IDNEX index_name ('column1','column2','column3');

遵循最左前缀原则：即最左优先，以最左边的column1为起点的任何连续索引都能匹配上。

• 本质：当创建联合索引(a,b,c)时，相当于创建了a单列索引，(a,b)联合索引以及(a,b,c)联合索引三个。只用使用这三种组合就可以使索引生效，当然a,c组合也会使索引生效，但其实只用到a的索引，查看key_len可知。
• 注意： 如果第一个字段是范围查询，需要单独建立一个索引。如 a>4是范围查询，a=3是单值查询。
• 注意： 联合索引只适合and的条件语句，or条件语句不起作用。
• 注意： 创建联合索引时，将where子句中使用最频繁的一列放在最左边，遵循最左前缀原则。

6）单列索引：

• 本质：单独创建三个索引：a，b，c
• 当查询语句为 where a=2 and b=4 and c=6时：只用到a索引。为什么没有用上b，c另外两个索引呢？
• 这里就涉及到MySQL优化器的优化策略：当多条件联合查询时，优化器会评估那个条件索引效率最高，就会选择最佳索引去使用，也即abc都能用，只不过优化器判断只需要查询条件的第一个就能完成本次查询，就使用第一个就可以了。
• 特殊： 当查询语句为 where a=3 or b=4 or c=6时，优化器判断这种情况需要使用到三个索引才是效率最高的，即abc三个索引都要使用。
• 注意： 无论是否使用到多个索引，多个单列索引都会在底层建立多个B+索引树，比较占用空间，浪费一定的搜索效率，索引当多条件联合查询时最好使用联合索引。

参考：链接
7）全文索引：
全文索引（FULLTEXT)仅可以适用于MyISAM引擎的数据表；作用于CHAR,VARCHAR,TEXT数据类型的列。

3、特殊的条件查询

1）对于使用like查询时，后面如果是常量并且只有%号不在第一个字符，索引才可能会被使用到：

• 不使用：explain select * from company2 where name like ‘%3’；
• 使用：explain select * from company2 where name like ‘3%’；
  2）如果列名是索引，使用column_name is null 将使用到索引：

explain select * from company2 where name is null;

3）对于大文本进行搜索，使用全文索引而不使用like。

4、存在索引，但不使用

1）MySQL如果估计使用索引比全表搜索更慢，则不使用索引。

• 如：a均匀分布在1-100，select * from table_name where a>1 and a<90;将不使用a索引。
  2）使用MEMORY/HEAP内存表时，并且where条件不使用 = 进行索引列，则不会使用到索引。heap表只有在 = 的条件下才会使用索引。
• 补充：内存表使用哈希散列索引把数据保存在内存中，因此具有极快的速度，适合缓存中小型数据库，但是使用上受到一些限制，如服务器重启，内存表数据全丢失，但表结构仍存在；复制某数据表，则复制后的所有主键，索引，自增格式不存在，需要重新添加。
• 参考：添加链接描述
  3）or条件必须前后所有列都有索引，并且是单列索引，才会被使用到。
  4）如果列是字符型，传入的是数字，则不上‘ ’不会使用索引。