1.索引
1.1.定义
MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。
我们平常所说的索引，如何没有特别的指明，都是B树(多路搜索树，并不一定是二叉的)结构组织索引，其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引，当然除了B+数这种类型的索引外还有(hash索引等)
1.2.优劣势
索引
优势：
类似大学图书馆建书目索引，提高数据检索的效率，降低数据IO成本
通过索引列对数据排序，降低数据排序的成本，降低CPU的消耗
劣势：
实际上，索引也是表，它保存主键和索引字段并指向实体表的记录，因此索引也会占用空间。
[En]
In fact, an index is also a table, which holds the primary key and index fields and points to the records of the entity table, so the index also takes up space.
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅 要保存数据，还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询
1.3.索引分类
单值索引：一个索引只包含一列，一个表可以有多个单列索引
[En]
Single-valued index: an index contains only a single column, and a table can have multiple single-column indexes
唯一索引：索引列的值必须是唯一的，但允许空值
[En]
Unique index: the value of the index column must be unique, but null values are allowed
复合索引：一个索引包含多个列
[En]
Composite index: an index contains multiple columns
基本语法：
创建：
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length))
ALTER mytable add[unique] index [indexname] on (columnname(length))
删除：
DROP INDEX[INDEXNAME] ON Mytable
查看：
show index from table_name\G
使用ALTER命令：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) #该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) #这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
ALTER TABLE tbl_name ADD INDEX index_name (column_list) #添加普通索引，索引值可出现多次
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) #该语句指定了索引为FULLTEXT，用于全文索引
1.4.索引使用场景
哪些情况需要创建索引：
1.主键自动建立唯一索引；
2.频繁作为查询条件的字段应该创建索引；
3.查询中与其它表关联的字段，外键关系建立索引；
4.频繁更新的字段不适合创建索引-因为每次更新不单单是更新了记录还会更新索引；
- Where条件里用不到的字段不创建索引；
6.单键/组合索引的选择问题，who? (在高并发下倾向创建组合索引)；
7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度；
8.查询中统计或者分组字段
哪些情况不要创建索引：
1.表记录太少；
2.经常增删改的表；
3.数据重复且分布平均的表字段，应该只为最经常查询和最经常排序的数据列建立索引。
请注意，如果数据列包含大量重复内容，则对其进行索引不会有太多实际效果。
[En]
Note that if a data column contains a lot of duplicate content, indexing it does not have much practical effect.
（比如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建立索引一般不会提高数据库的查询速度。
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。
索引的选择性越接近1，该索引的效率就越高。)
[En]
The closer the selectivity of an index is to 1, the more efficient the index is. )
2.Explain性能分析
使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句，分析你的查询语句或是表结构的性能瓶颈。
具体的分析结果包括表的读取顺序、数据读取操作的操作类型、可以使用哪些索引、实际使用了哪些索引、表之间的引用以及优化器查询每个表中的多少行。
[En]
The specific analysis results include the reading order of the table, the type of operation of the data read operation, which indexes can be used, which indexes are actually used, the references between tables, and how many rows in each table are queried by the optimizer.
2.1.id（执行顺序）
explain + sql 执行之后会显示如下信息列，表示mysql执行当前sql的执行信息。
id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
id显示的执行顺序一共有三种情况：
①id相同，执行顺序由上至下；
②id不同，如果是子查询，id序号会递增，id值越大优先级越高，越优先被执行
③id相同与不同，同时存在
2.2.select_type（查询类型）
select_type：表示查询的类型，主要是用于区别该条sql语句是普通查询、联合查询、子查询等的复杂查询
select_type的类型有以下几种：
SIMPLE：简单的select查询，查询中不包含子查询或者union；
PRIMARY：查询中若包含任何复杂的子部分，最外层则被标记为；
SUBQUERY：在select或where列表中包含子查询；
DERIVED：在FROM列表中包含子查询被标记为DERIVED (衍生) MYSQL会递归执行这些子查询，把结果放在临时表中
UNION：若第二个SELECT出现在UNION之后，则被标记为UNION，若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED
UNION RESULT：从UNION表获取结果的SELECT
2.3.Table
table用于显示这一行数据是来自哪一张表的
2.4.Type
type显示的是访问类型，是较为重要的一个指标，主要的类型有：
一般来说，在实际开发中，结果值从最好到最差：
[En]
In general, in actual development, the result values are from the best to the worst:
system > const > eq_ref > ref > range > index > ALL
一般来说，sql优化得保证查询至少达到range级别，最好能达到ref。
system：表中只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计；
const：表示通过索引一次就找到了，const用于比较primary key 或者unique索引，因为只匹配一行数据，所以很快将主键置于where列表中，MySQL就能将该查询转换为一个常量
eq_ref：唯一索引扫描，对每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
ref：非唯一性索引扫描，返回匹配的某个单独值所有行，本质上也是一种索引访问，
它返回与单个值匹配的所有行，但他可能会找到满足条件的多行，因此他应该是查找和扫描的混合体。
[En]
It returns all rows that match a single value, but he may find multiple rows that meet the criteria, so he should be a mixture of lookup and scanning.
range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在where语句中出现了between、<>、in等查询，这种范围扫描比
全表扫描更好，因为它从索引的一个点开始，在另一个点结束，而不扫描所有索引
[En]
A full table scan is better because it starts at one point of the index and ends at another point, without scanning all the indexes
index：index与ALL区别为index类型值遍历索引，通常比ALL快，因为索引文件通常比数据文件小
（all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
all：全表扫描，将遍历全表以找到匹配的行
2.5.possible_keys/key
possible_keys：显示可能应用在这张表中的索引，一个或多个查询设计到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
key：实际使用的索引，如果为NULL，则没有使用索引
注意：查询中若使用了覆盖索引，则该索引仅出现在key列表中
2.6.key_len
key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引长度，在不损失精确性的情况下，长度越短越好
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，及key_len是根据表定义计算而得，而不是通过表内检索出的
原则：同一检索条件下，key_len长度越短越好，消耗小、性能高。
2.7.ref
ref：显示索引的那一列被使用了，如果可能的话，是一个常数，那些列或常量被用于查找所有列上的值
2.8.rows
rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要的读取行数
2.9.Extra
extra：包含不适合在其它列中显示但十分重要的额外信息，主要类型有：
Using filesort：说明mysql会对数据使用一个外部索引排序，而不是按照表内的索引顺序进行读取，MySQL中无法利用索引完成的排序操作称为”文件排序” （性能较差）
Using temporary：使用临时表保存了中间结果，MySQL在对查询结果排序时时使用临时表，常见于排序order by 和分组查询group by （性能最差）
Using index：表示相应的select操作中使用了覆盖索引(Covering Index) 避免访问了表的数据行，效率不错,如果同时出现using where 表明索引被用来执行索引键值的查找。如果没有出现using where 表明该索引用来读取数据而非执行查找动作 （性能不错）
覆盖索引：又名索引覆盖，就是select的数据列只用从索引中就能获取，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
简单了解覆盖索引：且查询的字段在数量和顺序上与已建立的索引一致，或包含在索引查询的范围内(数量小于，依次包含)。
[En]
Simple understanding of the overlay index: and the fields queried are consistent with the established index in number and order, or are included in the scope of the index query (the number is less than, sequentially included).
注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致文件过大，查询性能下降。
Using where：表示where连接
Using join buffer：表示使用了连接缓存
Original: https://www.cnblogs.com/yif0118/p/15596357.html
Author: 人无名，则可专心练剑
Title: 【数据库】– MySQL SQL调优笔记(1)
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/507990/
转载文章受原作者版权保护。转载请注明原作者出处！