Mysql查询和索引

mysql查询

1、逻辑架构图

2、mysql查询执行路径

1、客户端发送一条查询给服务器

2、 服务器先检查查询缓存，如果命中了，则立刻返回存储在缓存中的结果

3、服务端进行sql解析、预处理、再由优化器生成对应的执行计划

4、mysql根据优化器生成的执行计划，调用存储引擎的api来执行查询

5、将结果返回客户端

3、多列索引

注意：多列所以不是每个列创建一个索引，而是多个列在一个索引上。

 

简介

类似于书的目录，其先在索引中找到对应的值，然后根据匹配的索引记录找到对应的行。索引减少服务器扫描的数据量，避免排序和临时表，将随机IO变为顺序IO.

1、mysql只能高效使用索引的最左列前缀

2、索引是在存储引擎层而不是服务器层实现的

3、InnoDB使用B+tree数据结构，根据主键引用被索引的行

3、page页大小默认为16K，最多填充因子是页大小的15/16

索引匹配

1、 key(lastname,firstname,age) ,根据lastname排列顺序，姓名一样则根据age来排列顺序。

2、如何使用索引查找：全值匹配、匹配列前缀（第一列）、匹配范围值（第一列的范围值）、第一列全匹配第二列范围匹配、只访问索引的查询（覆盖索引）

3、不能使用lastname的后缀，不能直接使用firstname或age索引查找

4、lastname="lai" and firstname like "wen%" and age = "18" ,此时之能使用前两个索引，age索引不管用，因为第二个索引是范围查询。in()也属于范围。

hash索引 

1、利用哈希表实现，只有精确匹配索引所有列的查询才有效

2、mysql中只有memory引擎显示支持哈希索引 key using hash(fname)

3、innodb有自适应hash索引功能，某些索引使用非常频繁时，innodb会在内存中基于b+tree索引之上再创建一个hash索引，可以选择关闭该功能

如何使用高性能索引

1、独立的索引列不能加入计算。如：index_name + 1 = 2;max(index_name) - min(index_name) =3等。

2、索引的选择性越高则查询效率越高。

3、索引太长必须使用前缀索引。add key (city(7));

4、mysql无法使用前缀索引做order by 和 group by,也无法使用前缀索引做覆盖扫描。

5、索引合并，如index_1 = 32 or index_2 = 54时，查询会同时使用其中一个索引并进行合并，但是优化器这样做可能会消耗更多的cpu和内存资源，可以使用参数关闭索引合并功能。

6、将选择性高的索引放在索引最前列（也不是绝对的，没有避免随机IO和排序重要）

聚簇索引

简介

1、并不是单独的索引类型，而是一种数据存储方式。

2、Innodb的聚簇索引实际上在同一个结构中保存了B+tree索引和数据行。

3、当表有聚簇索引时，它的数据行和相邻的键值紧凑的存储在一起

4、因为无法把数据行存放在两个不同的地方，所以一个表只有一个聚簇索引。

5、叶子节点包含了行的全部数据，但是节点页只包含了索引列。

6、Innodb被索引的列就是主键，没有主键则会选择唯一的非空索引。

7、聚簇索引每个叶子节点都包含了主键值、事务ID、用于事务和mvcc的回滚指针和剩余的列。

优点

1、把相关数据保存在一起，如通过用户ID来聚集数据获得用户邮箱，只需从磁盘读取少数数据页就能获取某个用户的全部邮件，否则每个邮件可能导致一次磁盘IO

2、索引和数据保存在同一个b+tree中，查询数据快。

3、覆盖索引扫描的查询可以直接使用节点中的主键值。

缺点

1、更新聚簇索引列的代价很高，更新行移动可能会发生页分裂

2、二级索引（非聚簇索引）：页包含了引用行的主键列

3、二级索引访问需要两次索引查找，因为二级索引保存的不是指向行的物理位置的指针，而是行的主键，所以会先找到叶子节点获得对于的主键值，然后根据这个主键值去聚簇索引中找到对于的行。

注意点

主键要是随机增大的数字，如果是uuid，可能导致页分裂，从而也变得稀疏，数据会有碎片。被写满且已经刷到磁盘上的页可能会从新读取出来，从而插入新值。

覆盖索引

简介：一个索引包含（或者说覆盖）所有需要查询的字段的值。无需再回表查询。

由于Innodb的聚簇索引，Innodb的二级索引再叶子节点中保存了行的主键值，所以如果二级主键能够查询，则可以避免对主键索引的二次查询。

索引排序

1、只有当索引的列顺序和Order by 子句顺序完全一致，并且所有列的排序方向都一样时。

冗余索引和重复索引

冗余索引：索引（A,B）和索引（A）。索引扩展为（A,ID）,id为主键，对于Innodb来说主键列已经包含在二级索引中了，所以这也是冗余的。

表的索引越多，insert、update、delete会更慢。

索引和锁

1、innodb可以在服务器端过滤掉行后释放锁。

2、innodb在二级索引上使用共享（读）锁，但访问主键索引需要排他锁，使得select for update比lock in share mode或非锁定查询要慢很多。

explain

1、会返回关于执行计划中的每一步信息，而不是执行它，但是在from子句中包括的子查询会执行,结果会放入到一个临时表中。必须这可以完成外层查询优化前处理所有类似的子查询。

2、输出中的行以mysql实际查询部分的顺序出现，不一定与原sql中的相一致。

3、内存排序和临时文件都使用filesort,磁盘和内存中的临时表都显示“Using temporary”

4、不支持存储过程，不会说明mysql在查询执行中的特定优化，不会显示执行计划的所有信息.

id列

union操作会有一个id为空，因为union结果总是放在衣蛾匿名临时表中，之后mysql再讲结果读取到表外。

select_type列

显示对应行时简单还是复杂select

simple:查询不包括子查询和union

primary：如果查询有任何复杂的子部分，则最外层部分标记为primary。

subquery:包含在select列表中的子查询中的select

derived:包含在from子句中的子查询中的select

union：union中的第二个和随后的select

union result:用来从union的匿名临时表检索结果的select

dependent:select依赖于外层查询中发现的数据

uncacheable:阻止结果缓存在一个Item_cache中。（于查询缓存不是一个东西）

table列

对应正在访问的表或该表的别名

左侧深度优先树，如果把计划放倒，就能顺序读取叶子节点，直接对应于explain中的行。

from子句中的子查询，<derivedN>N是子查询中的id

type列

mysql觉定如何查找表中的行

all：全表扫描

index:和全表扫描一样，只是扫描表时按索引次序进行而不是行，优点是避免了排序，缺点是按索引次序读取整个表的开销，若按随机次序访问行，开销会比较大

range:有范围的索引扫描（有范围的index）

ref:索引访问

eq_ref:mysql知道最多只返回一条符合条件的记录

const,system:对查询的某部分进行优化并将其转换成一个常量时。如：where中条件为主键，mysql把查询转换为常量，可以高效的把表从连接执行中移除。表最多只有一行匹配，通用用于主键或者唯一索引比较时

null:mysql在优化阶段分解查询语句，甚至用不着再访问表或索引

possible_keys列

查询可以使用哪些索引

key列

mysql决定使用哪个索引来优化对该表的访问

key_len列

mysql在索引里使用的字节数

ref列

之前的表在key列记录的索引中查找值所用的列或常量

rows列

mysql估计为了找到所需的行而要读取的行数

filtered列

针对表里符合条件（where）的记录的百分比所做的一个悲观估算。

extra列

而外信息

using index：mysql将使用覆盖索引，以避免访问表

using where :mysql服务器将在存储引擎检索后再进行过滤，表示mysql服务器将存储引擎返回行以后再应用where过滤条件

using temporary：mysql在对查询结果排序时会使用一个临时表。

using filesort :mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。（排序可能在内存可以能在磁盘上完成）

range checked for each record(index map: N):没有好用的索引。

其他解释

using index ：使用覆盖索引的时候就会出现

using where：在查找使用索引的情况下，需要回表去查询所需的数据

using index condition：查找使用了索引，但是需要回表查询数据

using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

mysql版本新特性：来，看看MySQL 5.6, 5.7, 8.0的新特性 - iVictor - 博客园