MySQL查询基本概念

MySQL查询

MySQL索引基础知识，MySQL索引的优化，MySQL排序

索引结构

聚簇索引

聚簇索引：又称聚集索引，并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（Leaf page）中，术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引（不过，覆盖索引可以模拟多个聚簇索引的情况）。
InnoDB通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一页面中的记录。包含相邻键值的页面可能会相距甚远。

聚簇索引按照如下规则创建：

1. 当定义了主键后，innodb会利用主键来生成其聚簇索引；
2. 如果没有主键，innodb会选择一个非空的唯一索引来创建聚簇索引；
3. 如果这也没有，Innodb会隐式的创建一个自增的列来作为聚簇索引。

Note: 对于选择唯一索引的顺序是按照定义唯一索引的顺序，而非表中列的顺序, 同时选中的唯一索引字段会充当为主键，或者Innodb隐式创建的自增列也可以看做主键。
聚簇索引整体是一个b+树，非叶子节点存放的是键值，叶子节点存放的是行数据，称之为数据页，这就决定了表中的数据也是聚簇索引中的一部分，数据页之间是通过一个双向链表来链接的，上文说到B+树是一棵平衡查找树，也就是聚簇索引的数据存储是有序的，但是这个是逻辑上的有序，但是在实际在数据的物理存储上是，因为数据页之间是通过双向链表来连接，假如物理存储是顺序的话，那维护聚簇索引的成本非常的高。
详细引用来源

二级索引

二级索引保存的式行的主键值

三星查询

1. 一星，索引将相关的记录放到一起；
2. 二星，索引中的数据顺序和查找中的排列顺序一致；
3. 三星，索引中的列包含了查询中需要的全部列。

CREATE TABLE `t_wms_sku` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sku_id` bigint(20) NOT NULL DEFAULT '0',
  `sku_name` varchar(50) NOT NULL DEFAULT '',
  `price` decimal(18,2) NOT NULL DEFAULT '0.00',
  `class3_id` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_sku_id` (`sku_id`) USING BTREE,
  KEY `idx_class3` (`class3_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8195 DEFAULT CHARSET=utf8

type

type意味着类型，这里的type官方全称是“join type”，意思是“连接类型”,这样很容易给人一种错觉觉得必须需要俩个表以上才有连接类型。事实上这里的连接类型并非字面那样的狭隘，它更确切的说是一种数据库引擎查找表的一种方式，在《高性能mysql》称呼它为访问类型更贴切一些。
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最差依次是：system>const>eq_ref>refindex>all

1. all :Full Table Scan，将遍历全表以找到匹配的行。
   这便是所谓的“全表扫描”，如果是展示一个数据表中的全部数据项，倒是觉得也没什么，如果是在一个查找数据项的sql中出现了all类型，那通常意味着你的sql语句处于一种最原生的状态，有很大的优化空间。
   比如：explain select * from t_wms_sku where sku_name = '油菜(大棵/新鲜|斤)' ;
2. index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从磁盘中读取的）。
   比如：explain select sku_id from t_wms_sku;
3. range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不会扫描全部索引。
   比如：explain select * from t_wms_sku where sku_id < 1249;
explain select * from t_wms_sku where id < 1249
4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引还能访问，它返回所有匹配某个单独值的行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
   通俗的解释：索引非唯一，条件用索引列=xxx
   比如：explain select * from t_wms_sku where class3_id = 555;
5. eq_ref：唯一性索引扫描，对于每个索引键，表中都只有一条记录与之匹配。常见于主键或唯一索引扫描。
   比如：

explain
select s.*, p.* from t_wms_sku s 
inner join p_sku p on s.sku_id = p.id

6. const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因此只匹配一行数据，所以很快，如将主键置于where列表中，mysql就能将该查询转换为一个常量。
   比如：explain select * from t_wms_sku where sku_id = 1238;
7. system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

出现上述情况，因为MySQL的join是通过嵌套循环实现的。

Extra

1. Distinct：MySQL is looking for distinct values, so it stops searching for more rows for the current row combination after it has found the first matching row.（MySQL正在寻找不同的值，因此在找到第一个匹配行之后，它不再为当前行组合搜索更多行。）平时不太关注。
2. Using filesort：MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause. The keys then are sorted and the rows are retrieved in sorted order。（MySQL必须做一个额外的传递来查找如何按排序顺序检索行。排序是通过根据联接类型遍历所有行，并为匹配WHERE子句的所有行存储排序键和指向该行的指针来完成的。然后对键进行排序，并按排序顺序检索行。）
   注：看到这个一般就需要优化了，总而言之，排序没有用到索引，需要文件排序，文件排序
3. Using index
   The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.
   For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY.
   （综合一句话，索引覆盖查询，查询语句只查询了索引，没有真正扫描数据）
   
4. Using where
   A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.
   Using where has no direct counterpart in JSON-formatted output; the attached_condition property contains any WHERE condition used.
   (表示优化器需要通过索引回表查询数据)
   Using index condition：在5.6版本后加入的新特性（Index Condition Pushdown）; Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。