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、索引列上参与计算

MYSQL性能调优

我们可以从四个方面进行优化：架构、硬件、DB、SQL。位置越靠前优化越明显，对数据库的性能提升越高。我们常说的SQL优化反而是对性能提高最小的优化。

架构优化

一般来说对于高并发场景下对架构优化其效果最明显。如：分布式缓存、读写分离、分库分表。

分布式缓存

性能不好，缓存来凑。在中间加一个redis。
当接收到查询时，先去缓存中查询一遍如果没命中，就查询数据库，否则加载数据库并同步到redis。这样就大大减少了对数据库的访问次数，自然而然也提高了数据库性能。

读写分离

一主多从，读写分离，主动同步，是一种常见的数据库架构优化手段。
一般来说当你的应用是读多写少，数据库扛不住读压力的时候，采用读写分离，通过增加从库数量可以线性提升系统读性能。

主库，提供数据库写服务；从库，提供数据库读能力；主从之间，通过binlog同步数据。

当准备实施读写分离时，为了保证高可用，需要实现故障的自动转移，主从架构会有潜在主从不一致性问题。

水平切分

水平切分，也是一种常见的数据库架构优化手段。
当你的应用业务数据量很大，单库容量成为性能瓶颈后，采用水平切分，可以降低数据库单库容量，提升数据库写性能。

TIPS

1. 读写分离主要是用于解决 “数据库读性能问题”
2. 水平切分主要是用于解决“数据库数据量大的问题”
3. 分布式缓存架构可能比读写分离更适用于高并发、大数据量大场景。

DB优化

QL执行慢有时候不一定完全是SQL问题，手动安装一台数据库而不做任何参数调整，再怎么优化SQL都无法让其性能最大化。要让一台数据库实例完全发挥其性能，首先我们就得先优化数据库的实例参数。

数据库实例参数优化遵循三句口诀：日志不能小、缓存足够大、连接要够用。

数据库事务提交后需要将事务对数据页的修改刷（ fsync）到磁盘上，才能保证数据的持久性。这个刷盘，是一个随机写，性能较低，如果每次事务提交都要刷盘，会极大影响数据库的性能。数据库在架构设计中都会采用如下两个优化手法：

1. 先将事务写到日志文件RedoLog（WAL），将随机写优化成顺序写
2. 加一层缓存结构Buffer，将单次写优化成顺序写

所以日志跟缓存对数据库实例尤其重要。而连接如果不够用，数据库会直接抛出异常，系统无法访问。

SQL优化

1、合理使用索引索引

少了查询慢；索引多了占用空间大，执行增删改语句的时候需要动态维护索引，影响性能 选择率高（重复值少）且被where频繁引用需要建立B树索引；一般join列需要建立索引；复杂文档类型查询采用全文索引效率更好；索引的建立要在查询和DML性能之间取得平衡；复合索引创建时要注意基于非前导列查询的情况

2、使用UNION ALL替代UNION

UNION ALL的执行效率比UNION高，因为UNION执行时需要排重；

3、避免select * 写法

执行SQL时优化器需要将 * 转成具体的列；每次查询都要回表，不能走覆盖索引。

4、JOIN字段建议建立索引
5、避免复杂SQL语句

提升可阅读性；避免慢查询的概率；可以转换成多个短查询，用业务端处理