MySQL调优、索引是什么、如何创建索引、索引的作用、索引失效场景

索引

索引是一种数据结构。用于加快对数据库的查询速度以及性能。
MySQL 索引类似于书籍的索引，通过存储指向数据行的指针，可以快速定位和访问表中的特定数据。

打个比方，如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是一个人力三轮车。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引的类型以及使用

普通索引

一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复、NULL值插入。

CREATE INDEX IndexName ON `TableName`(`字段名`(length));
# 或者
ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length));

主键索引

一张表只能有一个主键索引，不允许重复、不允许为 NULL；

 ALTER TABLE TableName ADD PRIMARY KEY(column_list);

唯一索引

数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length));
# 或者
ALTER TABLE TableName ADD UNIQUE (column_list);

全文索引

它查找的是文本中的关键词，主要用于全文检索。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

组合索引

一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即使用 where 、order by时条件要按照建立索引的时候字段的排列方式放置索引才会生效。

CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

显示索引信息

SHOW INDEX FROM table_name\G
-- \G: 格式化输出信息

在这里插入图片描述

解读表中字段的含义

1、Non_unique：如果索引不能包括重复值则为0，如果可以则为1。也就是平时所说的唯一索引。
2、Key_name：索引名称，如果名字相同则表明是同一个索引，而并不是重复，比如上图中的第二、三条数据，索引名称都是nick_sex_index，其实是一个联合索引。
3、Seq_in_index：索引中的列序列号，从1开始。上图中的二、三条数据，Seq_in_index一个是1一个是2，就是表明在联合索引中的顺序，我们就能推断出联合索引中索引的前后顺序。
4、Column_name：索引的列名。
5、Collation：指的是列以什么方式存储在索引中，大概意思就是字符序。
6、Cardinality：是基数的意思，表示索引中唯一值的数目的估计值。我们知道某个字段的重复值越少越适合建索引，所以我们一般都是根据Cardinality来判断索引是否具有高选择性，如果这个值非常小，那就需要重新评估这个字段是否适合建立索引。
7、Sub_part：前置索引的意思，如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
8、Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案。
9、Null 如果列含有NULL，则含有YES。
10、Index_type表示索引类型，Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

删除索引

DROP INDEX index_name ON table_name;
-- 或者
ALTER TABLE table_name
DROP INDEX index_name;

什么情况下需要使用索引

1、表的主外键都必须有索引；
2、记录数多的表应该有索引；（一般数据量达到300万-500万时考虑建立索引。）
3、常与其他表进行连接的表，在连接字段上应建立索引；
4、常出现在where后面的字段需要建立索引；
5、索引应当建立在小字段上，对于大的文本字段甚至超长的字段不建；
6、查询中的统计、分组字段；

不合适使用索引的场景

1、频繁更新的字段、表；
2、数据重复且分布平均的字段（由于大量的重复，索引对性能的提升很有限，比如年龄、性别…

Explain

在sql前面加explain的作用是分析当前sql的执行计划信息。
在这里插入图片描述

字段含义

1、select_type

每个select对应一个select_type，表示select的复杂度

SIMPLE：简单查询。查询不包含子查询和union；

PRIMARY：对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的（select * from user where id = 1）那个查询的select_type值就是PRIMARY；

SUBQUERY：包含在 select 中的子查询(不在 from 子句中)；

DERIVED：对于包含‘派生表’的查询；

UNION：在 union 中的第二个和随后的 select(select * from user where id =30)

2、type

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL一般来说，得保证查询达到range级别，最好达到ref

null：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

system：表中只有一行数据。属于 const 的特例。如果物理表中就一行数据为 ALL

const ：查询结果最多有一个匹配行。因为只有一行，所以可以被视为常量。const 查询速度非常快，因为只读一次。一般情况下把主键或唯一索引作为唯一条件的查询都是 const

eq_ref：查询时查询外键表全部数据。且只能查询主键列或关联列。且外键表中外键列中数据不能有重复数据，且这些数据都必须在主键表中有对应数据（主键表中数据可以有没有用到的）

ref：比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
1、简单查询，nick_name是普通索引不是唯一索引

range：把这个列当作条件只检索其中一个范围。常见 where 从句中出现 between、<、>、>=、in 等。主要应用在具有索引的列中

index：只遍历索引树，通常比All快。因为，索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。

ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

3、possible_keys

这个字段表示在查询时当前sql可能用到的索引，但是不一定真的使用，只是一种可能。

分析某条sql时，如果key列字段为null，但是possible_keys有值。说明mysql觉得可能会使用sql，但因为表中数据少，使用索引反而没有全表扫描效率高，那么mysql就不会使用索引查找，这种情况是可能发生的。

tips：如果出现以上情况，我们也可通过where后面的条件来适当建立索引提高性能。

4、key

sql真正使用到的索引字段。

5、key_len

用到的索引字段的长度。

6、ref

表示那些列或常量被用于查找索引列上的值，
ref列展示的就是与索引列作等值匹配的值什么，比如只是一个常数或者是某个列。
在这里插入图片描述

7、rows

表示在查询中检索了多少列，但是并不一定是返回那么多数据。

8、Extra

一些额外信息。

失效场景

1、不满足最左原则：在联合索引中，最左侧的字段优先匹配。因此，在创建联合索引时，where子句中使用最频繁的字段放在组合索引的最左侧。
而在查询时，要想让查询条件走索引，则需满足：最左边的字段要出现在查询条件中。
在这里插入图片描述

2、查询条件中有or即使部分条件带索引也会失效

type=ALL，进行了全表扫描未命中索引。
总结：查询条件中带or的查询，除非所有的条件都有索引否则不会走索引。
3、like查询使用%开头的
非模糊查询：

使用模糊查询后：
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/2c5fcf26c195423fbae
4、如果列类型是字符串，那在查询条件中需要将数据用引号引用起来，否则不走索引

5、索引列上参与计算