MySQL索引

如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

4.5、全文索引

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。

CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

和常用的like模糊查询不同，全文索引有自己的语法格式，使用match和against关键字，比如：

select * from user where match(name) against('aaa');

全文索引使用注意事项：

全文索引必须在字符串、文本字段上建立；
全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）；

字段值大小必须在范围内，才会创建全文索引。

比如：select * from user where match(name) against('a'); 值‘a’长度为1，不会创建全文索引，所以查不到数据。

全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa；

Syntax字符如下：

全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*；

select * from user where match(name) against('a*' in boolean mode);

5、最左前缀原则

对于组合索引（col1,col2,col3）,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高; select * from table1 where col1=A AND col2=B AND col3=D

如果使用 where col2=B AND col1=A 或者 where col2=B 将不会使用索引

组合索引中字段的顺序，选择性越高的字段排在最前面
组合索引的字段不要过多，如果超过4个字段，一般需要考虑拆分成多个单列索引或更为简单的组合索引
如果where条件中是OR关系，加索引不起作用
避免使用“OR”，否定查询，模糊查询，not in，<>等操作
选择 where,on,group by,order by 中出现的列

举例说明

比如，联合索引为(a，b，c，d)

select * from user where a=xx and b=xx 索引有效

select * from user where b=xx and a=xx 索引有效，但查询引擎会优化顺序为联合索引的顺序，增加额外的时间开销

~~~~select * from user where a=xx and~~ c~~~~=xx~~ ~~索引无效~~

~~~~select * from user where~~ b~~~~=xx~~ ~~索引无效~~

向右匹配直到遇到范围查询(>、<、between、like)就停止匹配

联合索引为(a，b，c，d)

select * from user where a=xx and b=xx and c>3 ~~and d=4~~ d索引无效

联合索引为(a，b，d，c) “=”查询时，索引列的顺序可以调整

select * from user where a=xx and b=xx and c>3 and d=4 索引有效

6、最左前缀失效

回表

对于普通索引查询时，需要根据索引的索引树(非聚簇索引)找到叶子节点对应的主键，再通过主键去主键索引树查询一遍，才能得到真实的数据，此过程就叫做回表。

索引覆盖

在用索引查询时，使它的索引树，查询到的叶子节点上的数据可以覆盖到你查询的所有字段，这样就可以避免回表。

举例说明

现有组合索引index（a，b，c）

查询语句为select a,b,c from table1 where c = 'xxx'。根据最左匹配原则，该SQL查询不会走组合索引index，但实际执行计划如下(走了组合索引)。

这是因为查询字段为a,b,c，三个字段的值均可在组合索引index中找到，通过组合索引查询，可避免回表，所以虽然查询条件不符合组合索引的最左匹配原则，但是MySQL执行器还是走了index组合索引。

7、创建索引时需要考虑哪些因素

慢查询->explain->执行计划->全表扫描->对查询条件建立索引。

8、为什么不能乱用索引

创建和维护索引需要耗费额外的时间，且随着数据量的增加而增加
索引需要占用额外的物理空间
当堆表中的数据进行新增、修改、删除操作时，需要消耗额外的时间进行索引的维护，降低了数据的更新效率

9、B树和B+树

BTree

索引值和data数据分布在整棵树结构中
每个节点可以存放多个索引值及对应的data数据
树节点中的多个索引值从左到右升序排列
所有节点都带有指向记录的指针，在内部节点出现的索引项不会出现在叶子节点中；

B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。

B+Tree

非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
叶子节点包含了所有的索引值和data数据
叶子节点用指针连接，提高区间的访问性能
非叶子节点不存放数据，所以相同磁盘块能存放的节点数更多，能存放更多元素，从而减少磁盘访问次数。

B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

10、MySQL为什么使用B+树，而不是B-树、Hash、二叉树、红黑树

IO：将索引从硬盘加载到内存。

Hash：可快速定位，但是没有顺序，IO复杂度高；

二叉树：树高不均匀，查找效率和数据有关(树高)；

红黑树：树的高度随着数据量的增加而增加，IO代价高；

B-树：每一层都会存储数据

B+树查询时，从根节点开始进行二分查找，无需进行全表扫描。
B+树的数据集中在叶子节点，分支节点只存放索引，B+树的层高小于B树，平均IO更小。
B+树的数据都存放在叶子节点，查询效率稳定。
B+树的数据在叶子节点有序存放，更适合范围查询。

相对于B树，B+树非叶子节点不存储数据，占用空间小，所以树结构更加矮胖，层级更低，IO次数更少。

而B树，虽然提高了磁盘IO性能，但并没有解决元素遍历低下(中序遍历)的问题。

11、B+树3层最多存多少数据

页(Page)是InnoDb的最小存储单元，一个页的大小是16k。

数据表中的数据都是存储在页中的，假设1行数据的大小是1k，则1页就有16行。

指针在InnoDb中的大小为6字节，非叶子节点为指针，叶子节点为数据。

非叶子节点的指针数据，有指针(6字节)和主键(8字节)。

则一个非叶子节点，可存储16*1024/(6+8) = 1170个指针数据。

则三层B+树结构：

根节点1170，第二层1170*1170 = 1368900个指针数据。

每个指针指向一个page，每个page可存储16行数据：

则第三层可存储数据 = 1368900 * 16 = 21902400行数据。

一次页的查找即为一次IO，那么B+树一般只需要1-3次IO即可查询到数据。

12、聚簇索引和非聚簇索引

聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引。
主键索引和辅助索引：B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引（二级索引）。
在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储。

12.1、聚簇索引

InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。

数据存储和索引放在一块，在B+Tree结构中体现在叶子节点为索引和数据。

InnoDB的表要求必须要有聚簇索引：

如果表定义了主键，则主键索引就是聚簇索引
如果表没有定义主键，则第一个非空unique列作为聚簇索引
否则InnoDB会从建一个隐藏的row-id作为聚簇索引

12.2、辅助索引

InnoDB辅助索引，也叫作二级索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多，通常创建辅助索引就是为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。

索引叶节点data域存储相应记录主键的值而不是地址。(查询时需要回表)

12.3、非聚簇索引

非聚簇索引都是辅助索引，数据存储和索引分开存放，在B+Tree结构中体现在叶子节点为索引和指向数据的指针。

MyISAM引擎使用B+Tree作为索引结构，为非聚簇索引，索引和数据存放在不同文件中，索引叶节点的data域存放的是数据记录的地址。

MyISAM将索引缓存到内存中，访问数据时，直接在内存中搜索索引，找到索引后再根据数据指针找到磁盘上相应的数据。

InnoDB引擎使用B+Tree作为索引结构，为聚簇索引，索引和数据存放在同一个文件中，索引叶节点的data域存放的是行数据。由于聚簇索引是将数据和索引放到一块，所以一个表只有一个聚簇索引。

而在聚簇索引之上的是辅助索引(普通索引)，辅助索引的子节点的data域存放的是主键值。查询时，首先根据辅助索引找到主键值，然后利用主键值执行第二次查找找到行数据。

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

13、索引失效场景

条件中包含OR运算符
like查询以%开头
条件中含有函数或者表达式
索引列上使用内置函数
对索引列进行运算
如果MySQL执行器认定索引比全表扫描慢，则不会使用索引
组合索引不满足最左匹配原则

14、索引分析和优化

14.1、Explain

14.1.1、Select_type

查询类型：

SIMPLE ：表示查询语句不包含子查询或union
PRIMARY：表示此查询是最外层的查询
UNION：表示此查询是UNION的第二个或后续的查询
DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
UNION RESULT：UNION的结果
SUBQUERY：SELECT子查询语句
DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果

14.1.2、Type

存储引擎查询数据时采用的方式：

ALL：表示全表扫描，性能最差。
index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
ref：表示使用非唯一索引进行单值查询。
eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
const：表示使用主键或唯一索引做等值查询，常量查询。
NULL：表示不用访问表，速度最快。

14.1.3、Possible_keys

表示查询时可能使用到的索引。

14.1.4、Key

查询时真正使用到的索引。

14.1.5、Key_len

表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。

字符串类型

字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4

char(n)：n*字符集长度

varchar(n)：n * 字符集长度 + 2字节

数值类型

TINYINT：1个字节 SMALLINT：2个字节 MEDIUMINT：3个字节

INT、FLOAT：4个字节 BIGINT、DOUBLE：8个字节

时间类型

DATE：3个字节 TIMESTAMP：4个字节 DATETIME：8个字节

字段属性

NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项

14.1.6、Rows

SQL查询扫描的行数，行数越小越好。MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。

14.1.7、Extra

额外信息。

Using where：表示查询需要通过索引回表查询数据。
Using index：表示查询需要通过索引，索引就可以满足所需数据。
Using filesort：表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化。
Using temprorary：查询使用到了临时表，一般出现于去重、分组等操作。

14.2、回表

聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。

辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。

通过索引查询主键值，然后再去聚簇索引查询记录信息。

14.3、覆盖索引

explain的输出结果Extra字段为Using index时，能够触发索引覆盖。
只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。
实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

14.4、最左前缀

最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

14.5、MySQL在使用like模糊查询时，索引能不能起作用？

MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。

14.6、如果MySQL表的某一列含有NULL值，那么包含该列的索引是否有效？

有效。

空值不占空间，NULL值占空间。当字段不为NULL时，也可以插入空值
当使用 IS NOT NULL 或者 IS NULL 时，只能查出字段中没有不为NULL的或者为 NULL 的，不能查出空值。
判断NULL 用IS NULL 或者 is not null,SQL 语句函数中可以使用IFNULL()函数来进行处理，判断空字符用 =’‘或者<>’'来进行处理。
在进行count()统计某列的记录数的时候，如果采用的NULL值，会别系统自动忽略掉，但是空值是会进行统计到其中的。

在对某列设置索引时，最好将该列设置为NOT NULL。