优势：1. 提高数据检索效率，减少查找数据的磁盘I/O操作 2. 索引天然排序，根据索引顺序执行排序操作，效率更高（譬如order by）
缺点：1. 占用磁盘空间 2. 增加维护成本，增删改需要维护索引
1. 聚集索引：一张表只能有一个，叶子节点会包含该行的完整数据。
2. 非聚集索引：叶子节点不存完整的数据。
#创建聚集索引 ALTER TABLE table_name ADD PRIMARY KEY (column_name); #创建普通索引 ALTER TABLE table_name ADD INDEX index_name (column_name) ; #创建唯一索引 CREATE UNIQUE INDEX index_name ON table(column_name) ;
（https://www.cs.usfca.edu/~galles/visualization/Algorithms.html 数据结构展示）
1. 哈希key-value：查询速度快，不支持范围查找。
2. 二叉树：存在退化成链表的问题
3. 平衡二叉树：根节点的左右子树高度相差不超过1。当数据量大，则树的高度越高，查找耗时越多。不支持范围查找
4. B树：降低树的高度。每个索引节点都保存了数据（或指针），且每个索引节点只存在一个索引页中。
索引节点：一个索引节点代表表中的某一行数据。一个索引节点包含索引值和指向保存数据的指针（或数据本身）。
索引页：一个索引页在逻辑上代表一个B树节点，包含x个索引节点和x+1个指针（指向索引节点的左右子树）。访问不同的索引页产生随机I/O。
以InnoDB为例，计算B树存储的数据量。
InnoDB数据页和索引页大小均为16KB。假设一条记录大小1KB。与数据记录大小相比，指针占用内存忽略。
一个索引页存放的数据为16条，如果是非叶子节点，还包含指向下一层的17个指针。一个2层的B树存放的数据量为17*16=272条数据。一个三层的B树存储的数据量为4624。
缺点：每个索引节点都保存了指向数据的指针（或数据本身），整个页保存的索引节点数下降。且不支持范围查找。
5. B+树：只有B+树叶子节点才存储指向数据（或指针），叶子节点之间有双向指针，一个索引节点即在叶子节点中出现，也在非叶子节点中出现。
以InnoDB为例，计算B+树存储的数据量。
InnoDB数据页和索引页大小均为16KB。假设一条记录大小1KB。
一个B+树叶子节点存储的数据为16KB/1KB=16条。
一个B+树非叶子节点，假设索引值为bigint类型，即一个索引节点大小为8字节。非叶子节点存放x个索引节点和x+1个指针。8x+(x+1)*6=16*1024，计算出x约等于1169。
即一个非叶子节点存放约1169个索引节点。一个叶子节点存放16条数据。一个2层的B+树存储的数据量为(1169+1)*16=18720条数据。一个三层的B+树存储的数据量为18720*1170=21902400。
可见相比于B树，相同层数的B+树存储的数据量远远超过了B树。反过来讲，存储相同的数据量，B树的深度要远大于B+树，即需要更多的随机I/O读取索引页。
6. B树和B+树结构的区别：
1.B树一个索引节点只出现一次
2.B树每个节点都存放真实数据或指向真实数据的指针
3.B+树叶子节点之间有双向链表
索引和数据存放在两个文件，索引文件后缀.MYI，数据文件后缀.MYD。MyISAM的主键索引和辅助索引都是非聚集索引，叶子节点存储的是索引值和一个指向数据地址的指针。
索引和数据存放在同一个文件，后缀.ibd。
2.1 主键索引
主键索引是聚集索引，叶子节点存储的是数据本身。如果没有定义主键，使用第一个非NULL的唯一索引作为主键索引，如果不满足，使用隐藏字段ROW_ID作为索引。
2.2 辅助索引
除主键外其余的索引，使用非聚集索引。非聚集索引的叶子节点存储的是辅助索引值和主键值，需要根据主键值做回表（二次查询）。
2.3 组合索引
一条SQL语句通常只使用一个索引，因为使用多个单独的索引，耗费的成本比使用单个索引高，在MySQL5.0及之后的版本，引入了“索引合并”的策略，一定程度上可以使用多个单列索引定位。但更推荐使用组合索引。
组合索引的生成规则和创建索引时列的顺序有关，譬如在字段name,age,height上建立组合索引。生成索引时先按照name排序，再按照age排序，最后按照height排序。即最左匹配原则的由来。
最左匹配原则：在查询时，会根据组合索引向右匹配直到遇到范围条件(>、<、between、like)停止，所以一般将可能用作范围查询的列放在组合索引的后面。
选择索引顺序的经验法则：将选择性最高的列放到索引最前列（选择性是指，不重复的索引值/数据表的总记录数）。这样可以更快的找出指定的行。除此之外，还需要考虑where子句中的排序、分组和范围条件。譬如where a = 1 order by b。则考虑建立索引(a,b)，这样当a确定时，b已经自然排序了。
再考虑如下的sql语句，如何建立索引?
select * from test where a = 1 and b > 2 order by c;
如果b的选择性低，c的选择性高，推荐建立索引(a,c)，反之建立(a,b)。另外，如果这里b的选择性非常低，只有少数的选项，可以考虑建立索引(a,b,c)。上述的语句可以修改成
select * from test where a = 1 and b in(3,4) order by c;
这么做的原因是尽可能的重用索引，某些场景下可能需要创建(a,b)和(a,c)两个索引。如果用不到中间索引b，只需要将在中间新增条件 b in(1,2,3,4)。当然如果b的选择性很高就不适合了。
2.4 覆盖索引
在辅助索引树上获得需要的全部的查询字段。比如创建组合索引(a,b,c,d)，执行查找语句：
select a,b,c,d from test where a = 1;
在辅助索引的叶子节点上包含的索引值和主键值囊括了需要的全部查询字段，减少了去主键索引回表查询的I/O操作。
观察如下查询语句的执行计划：
mysql> explain select a from test where b = 1; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | test | NULL | index | NULL | idx_abc | 53 | NULL | 2 | 50.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
上述查询也会用到索引，其中Using index，Using where即使用覆盖索引进行全表扫描，减少了回表次数。
2.5 索引下推
MySQL5.6及之后版本，通过 SET optimizer_switch = ‘index_condition_pushdown=on‘; 开启。
如2.4，创建联合索引(a,b,c,d)，分析查询语句：
explain select a,id from test where a>2 and b=3 and d = ‘banana‘;
根据最左匹配原则，因为a是范围查找，在a之后的条件都不能使用组合索引，如果不开启索引下推：
mysql> SET optimizer_switch = ‘index_condition_pushdown=off‘; Query OK, 0 rows affected (0.06 sec) mysql> explain select a,id from test where a>2 and b=3 and d = ‘banana‘; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test | NULL | range | idx_abc | idx_abc | 5 | NULL | 1 | 50.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
MySQL的执行过程是：InnoDB先根据 a>2 找到第一条满足的记录，回表查询数据行，交给MySQL Service层使用剩余查询条件b=3 and d=‘banana‘进行判断，不符合丢弃，否则缓存到结果集。
InnoDB继续查询下一行，进行同样的操作，直到a不满足条件。
而开启索引下推：
mysql> explain select a,id from test where a>2 and b=3 and d = ‘banana‘; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------+ | 1 | SIMPLE | test | NULL | range | idx_abc | idx_abc | 5 | NULL | 1 | 50.00 | Using index condition; Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------+
MySQL的执行过程是：InnoDB根据a>2找到第一条满足的记录，紧接着按照组合索引继续判断b=3，找到满足的第一行记录，回表查询数据行，交给MySQL Service层使用剩余查询条件d=‘banana‘进行判断，不符合丢弃，否则缓存到结果集。
InnoDB继续查询下一行，进行同样的操作，直到a和b不满足条件。
索引下推将一部分MySQL Server判断的部分下推到存储引擎层判断，毕竟索引都是有序的，当然前提是初始条件是第一列，否则用不到索引。总的讲就是有关索引的判断都放在了存储引擎层，其余的判断交给MySQL Server。减少了回表查询的次数和返回服务层的记录数。
频繁作为查询/分组/排序条件。作为查询返回结果的列使用组合索引避免回表查询。关联查询关联的字段需要建立索引（是否两边都需要？）
1. 配置表等记录少的表不需要创建索引，全表扫和走索引相差不大
2. 一张表不要建立太多索引，占空间，增删改查的维护以及优化器的筛选成本
3. 频繁更新的字段不适合创建索引，造成索引页的分裂和合并，影响性能
4. 选择性低的字段不适合创建索引，没什么用，筛选出来的数据量太大，全表扫反而性能高一些
5. InnoDB的索引，主键不要太大，太大一个索引页存放的索引节点变少，索引页的数量变多，而访问索引页是需要I/O操作的。
6. 主键索引建议自增，类似雪花ID这种无序的字段作为索引，更新时会导致页的频繁分裂，并且一个页上存储的数据是稀疏的。自增索引往往是写完一个页接着写下一个页，是顺序的。
7. 创建组合索引而不是多个单独索引
1. 在索引的列上做操作，比如计算、函数、类型转换(比如字符串字段的等值条件是数字)
2. not，<>，!= 三种操作不会使用索引
3. or条件前后必须都建立了索引
4. 使用索引后筛选的数据量依然很大，则使用全表扫
5. 使用like时，不能以%开头
6. 组合索引没有以第一列作为查询条件的开头
7. 字段存在空值，查询语句包含is null 或 is not null 都可以使用索引，MySQL版本5.7.26
mysql> explain select a,b,c from test where a is null; +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | test | NULL | ref | idx_abc | idx_abc | 5 | const | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select a,b,c from test where a is not null; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | test | NULL | range | idx_abc | idx_abc | 5 | NULL | 4 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
哈希索引复杂度低，但不支持范围查询，排序，模糊查询。
原文：https://www.cnblogs.com/walker993/p/14529994.html