认识索引
认识索引是什么东西非常关键，一个非常恰当的比喻就是书的目录页与书的正文内容之间的关系，为了方便查找书中的内容，通过对内容建立索引形成目录。因此，首先你要明白的一点就是，索引它也是一个文件，它是要占据物理空间的。
比如对于MyISAM存储引擎来说:
.frm后缀的文件存储的是表结构。
.myd后缀的文件存储的是表数据。
.myi后缀的文件存储的就是索引文件。
如下图所示:
对于InnoDB存储引擎来说:
.frm
.ibd后缀的文件存放索引文件和数据(需要开启innodb_file_per_table参数)
如下图所示:
因此，当你对一张表建立索引时，索引文件的大小也会改变，当你数据表中的数据因为增删改变化时，索引文件也会变化的，只不过MySQL会自动维护索引，这个过程不需要你介入，这也是为什么不恰当的索引会影响MySQL性能的原因。
总结:
1. 索引是按照特定的数据结构把数据表中的数据放在索引文件中，以便于快速查找；
2. 索引存在于磁盘中，会占据物理空间。
索引的类型
B-Tree 索引
以 B-Tree 为结构的索引是最常见的索引类型，比如 InnoDB 和 MyISAM 都是以 B-Tree 为索引结构的索引，事实上是以 B+ Tree 为索引结构，B-Tree 和 B+Tree 区别在于，B+ Tree 在叶子节点上增加了顺序访问指针，方便叶子节点的范围遍历。这里主要介绍一下 InnoDB 和 MyISAM。
InnoDB
InnoDB 支持聚簇索引，聚簇索引和非聚簇索引严格来说不是一种索引，而是一种数据存储方式，这个名字跟它本身的存储方式有关系，“聚簇“表示数据行和相邻的键值存储在一起，简单的说，就是叶子节点中存储的实际是真实的数据。InnoDB 通过主键聚集数据，所以一个表只能有一个聚簇索引，且必须有主键，如果没有定义主键，且不存在非空索引可以代替，InnoDB 会隐式定义一个主键作为聚簇索引。
聚簇索引的二级索引存储的不是指向行的物理位置的指针，而是行的主键值，所以如果通过二级索引查找行，需要找到二级索引的叶子结点获得对应的主键值，然后再去查找对应的行。对于 InnoDB，自适应哈希索引可以减少这样的重复工作。
锁
InnoDB 使用的是行锁，所以支持事务，而 MyISAM 使用的是表锁，不支持事务。
适用范围
B-Tree 索引适用于区间查询，因为 B-Tree 存储后的叶子节点本身就是有序的，并且 B+ Tree 结构还增加了叶子节点的连续顺序指针，对于区间查询来说就更加方便了。
哈希索引
哈希索引是基于哈希表实现的，只有精确匹配索引所有列的查询才有效。方法是，对所有的索引列计算一个 hash code，hash code 作为索引，在哈希表中保存指向每个数据行的指针。
优点
索引本身只存储 hash code，所以结构很紧凑，并且查找速度很快
限制
索引中的 hash code 是顺序存储的，但是 hash code 对应的数据并不是顺序的，所以无法用于排序
不支持部分索引列匹配查找，因为哈希索引是使用索引列的全部内容来计算 hash code
只支持等值比较，不支持范围查询
如果哈希冲突严重时，必须遍历链表中所有行指针
哈希冲突严重的话，索引维护操作的代价也很高
InnoDB 的自适应哈希索引
首先，请注意，自适应哈希索引对于用户来说是无感知的，这是一个完全自动、内部的行为，用户无法控制或者配置，但是可以关闭。
当 InnoDB 注意到某个索引值被使用的非常频繁时，它会在内存中基于 B-Tree 索引之上再创建一个哈希索引，这样 B-Tree 也可以具有哈希索引的一些优点，比如快速的哈希查找。
当然如果存储引擎不支持哈希索引，用户也可以自定义哈希索引，这样性能会比较高，缺陷是需要自己维护哈希值，如果采用这种方法，不要使用 SHA1() 和 MD5() 作为哈希函数，因为这两个是强加密函数，设计目标是最大限度消除冲突，生成的 hash code 是一个非常长的字符串，浪费大量的空间，哈希索引中对于索引的冲突要求没有那么高。
索引的优点
使用索引可以减少服务器需要扫描的数据量
使用索引可以帮助服务器避免排序和临时表
使用索引可以将随机 I/O 变为顺序 I/O
但是不是所有情况下，索引都是最好的解决方案，对于非常小的表来说，大部分情况下简单的全表扫描更高效，对于中到大型表，索引就比较有效，对于特大型的表来说，分区会更加有效。
常见优化方法
联合索引最左前缀原则
复合索引遵守「最左前缀」原则，查询条件中，使用了复合索引前面的字段，索引才会被使用，如果不是按照索引的最左列开始查找，则无法使用索引。
比如在(a,b,c)三个字段上建立联合索引，那么它能够加快a|(a,b)|(a,b,c)三组查询的速度，而不能加快b|(b,a)这种查询顺序。
另外，建联合索引的时候，区分度最高的字段在最左边。
不要在列上使用函数和进行运算
不要在列上使用函数，这将导致索引失效而进行全表扫描。
例如下面的 SQL 语句：
select * from artile where YEAR(create_time) <= '2018'; 复制代码
即使 date 上建立了索引，也会全表扫描，可以把计算放到业务层，这样做不仅可以节省数据库的 CPU，还可以起到查询缓存优化效果。
负向条件查询不能使用索引
负向条件有：!=、<>、not in、not exists、not like 等。
select * from artile where status != 1 and status != 2; 复制代码
可以使用in进行优化：
select * from artile where status in (0,3) 复制代码
使用覆盖索引
所谓覆盖索引，是指被查询的列，数据能从索引中取得，而不用通过行定位符再到数据表上获取，能够极大的提高性能。
可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的。
避免强制类型转换
当查询条件左右两侧类型不匹配的时候会发生强制转换，强制转换可能导致索引失效而进行全表扫描。
如果phone字段是varchar类型，则下面的SQL不能命中索引：
select * from user where phone=12345678901; 复制代码
可以优化为：
select * from user where phone='12345678901'; 复制代码
范围列可以用到索引
范围条件有：<、<=、>、>=、between等。
范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。
更新频繁、数据区分度不高的字段上不宜建立索引
更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能。
「性别」这种区分度不大的属性，建立索引没有意义，不能有效过滤数据，性能与全表扫描类似。
区分度可以使用 count(distinct(列名))/count(*) 来计算，在80%以上的时候就可以建立索引。
索引列不允许为null
单列索引不存null值，复合索引不存全为null的值，如果列允许为 null，可能会得到不符合预期的结果集。
避免使用or来连接条件
应该尽量避免在 where 子句中使用 or 来连接条件，因为这会导致索引失效而进行全表扫描，虽然新版的MySQL能够命中索引，但查询优化耗费的 CPU比in多。
模糊查询
前导模糊查询不能使用索引，非前导查询可以。
需要学习更多java相关知识请关注我，或者查看我的往期发布哦
网友评论