MySQL高级（二）

MySQL高级（二）

索引（Index）是帮助MySQL高效获取数据的数据结构。可以简单理解为排好序的快速查找数据结构。

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

1、索引的分类

（1）单值索引：一个索引只包含单个列，一个表可以有多个单列索引。

（2）唯一索引：索引列的值必须唯一，但允许有空值。

（3）复合索引：一个索引包含多个列。

2、索引的语法

#创建
CREATE [UNIQUE] INDEX [indexName] ON table_name(colume);

#删除
DROP INDEX [indexName] ON table_name;

#查看
SHOW INDEX FROM table_name\G



#ALTER命令
#有四种方式来添加数据表的索引

##该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

##这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)

##添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD INDEX index_name (column_list)
 
##该语句指定了索引为 FULLTEXT ，用于全文索引。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

3、BTree索引

一颗b树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），
如磁盘块1包含数据项17和35，包含指针P1、P2、P3，
P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

【查找过程】

如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

4、EXPLAIN

4.1、简介

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

4.2、作用

（1）表的读取顺序

（2）数据读取操作的操作类型

（3）哪些索引可以使用

（4）哪些索引被实际使用

（5）表之间的使用

（6）每张表有多少行被优化器查询

4.3、基本语法

#explain sql语句
explain select * from t_dept;

4.4、expain各字段的解释

4.4.1、expalin之id

select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序。

三种情况：

（1）id相同，执行顺序由上至下

（2）id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

（3）id相同和不同，同时存在

id如果相同，可以认为是一组，从上往下顺序执行；
在所有组中，id值越大，优先级越高，越先执行。

4.4.2、explain之select_type（常见）

（1）SIMPLE：简单的select查询，查询中不包含子查询或者UNION

（2）PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为Primary

（3）DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询, 把结果放在临时表里。

（4）SUBQUERY：在SELECT或WHERE列表中包含了子查询

（5）UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED

（6）UNION RESULT：从UNION表获取结果的SELECT

4.4.3、explain之type

访问类型排列：从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL

（1）system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

（2）const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

（3）eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

（4）ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

（5）range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

（6）index：全索引扫描，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型是扫描全部的索引，主要优点是避免了排序，但是开销仍然非常大。

（7）ALL：全表扫描

4.4.4、explain之possible_keys和key

possible：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key：实际使用的索引。如果为NULL，则没有使用索引；查询中若使用了覆盖索引，则该索引和查询的select字段重叠

覆盖索引：就是select的数据列只用从索引中就能够获取到，不必读取数据行，换句话说查询列要被所使用的索引覆盖。

4.4.5、explain之key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

key_len显示的值为索引字段的最大可能长度，并非实际使用长度。

4.4.6、explain之ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

4.4.7、explain之rows

rows列显示MySQL认为它执行查询时必须检查的行数，越少越好。

4.4.8、explain之extra（重要）

（1）Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
MySQL中无法利用索引完成的排序操作称为“文件排序”。

（2）Using temporary：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

（3）Using index：表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

（4）Using where：表明使用了where过滤。

（5）Using join buffer：使用了连接缓存。

（6）impossible where：where子句的值总是false，不能用来获取任何元组

#例如
explain select * from staffs where name = 'July' and name = 'zhangsan';