mysql 怎么算第二天_MySQL 第二天

mysql 第二天

优化分析(Explain)：是什么？

能干嘛？表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

表之间的引用

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

怎么用？explain + SQL； explain select * from user;

各字段解释id:select查询的序列号包含一组数字，表示查询中执行select子句或操作表的顺序。

三种情况：id相同的情况：执行顺序由上到下。

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

id相同与不同：越大越先执行，之后同级的由上往下执行。 衍生= DERIVED,后面跟随的数据，就表示是衍生的某个id。 id影响到表的加载和读取速度。

select_type有哪些:

查询的类型，主要用于区别：普通查询、联合查询、子查询的复杂查询：SIMPLE简单的select查询，查询中不包含子查询或者UNION

PRIMARY查询中若包含任何复杂的子查询，最外层的查询则为PRIMARY

SUBQUERY在SELECT或WHERE 列表中包含了子查询，括号内的。

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

UNION若第二个SELECT出现UNION之后，则标记为UNION；

若UNION包含在FROM子句子查询中，外层的SELECT 将标记为：DERIVED

UNION REULT从UNION表获取SELECT

table显示这一行的数据是关于哪张表的。

type

访问类型排序显示查询使用了何种类型，从最好到最差依次是：system > const > eq_ref > ref > range > index > ALL,一般来说，的保证查询至少达到range级别，最好能达到ref。

system:表只有一行记录(等于系统表)，这是const类型的特征，平时不会出现，这个也可以忽略不计。

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

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

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

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

index：Full Index Scan，index 与All的区别为index只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。也就是说，虽然all和index都是读全表，但是index是从索引中读取，而all是从硬盘中读取。

all：Full Table Scan，将遍历全表以找到匹配的行。

备注：一般来说得保证查询至少达到range级别，最好能达到ref。

possible_keys:显示可能应用在这张表中的索引，一个或多个。查询设计到的字段上若存在索引，则改索引将被列出，但不一定被查询实际使用。

key:实际使用的索引，如果为NULL，则没有使用索引(索引失效)，或者根本没有创建索引。

查询若使用了覆盖索引，则该索引仅出现在key列表中。

key_len:表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算面得，不是通过表内检索出的。

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

rows:根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

Extra：包含不适合在其他列显示，但是又十分重要的信息Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。

Mysql中无法利用索引完成的排序操作成为“文件排序”。mysql自己进行了一次排序。

常见于复合索引没有按照顺序走。

Using temporary使用了临时表保存中间结果，mysql在对查询结果排序的时候使用临时表，常见于排序order by 和分组查询group by； 常见于复合索引没有按照顺序走。

特别注意，如果需要用到索引字段的排序，要么别创建复合索引，要么按照顺序给我都用上！。

Using index表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效果不错！

如果同时出现using where，表示所有被用来执行索引键值的查找；

如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。覆盖索引：理解方式：复合索引或者说普通索引，创建了几个，什么顺序，你select后面跟随的就是指定的列名，和顺序，正好和我一致，我直接通过索引去查找，不用逐行检索数据了，索引把查询字段覆盖，就叫覆盖索引。

Using where：使用了where过滤。

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

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

热身case

索引优化

索引分析单表建立SQL

出现了Using filesort，外部文件排序，影响未来数据多的响应速度

结论：很显然，type是All全表扫描。Extra还出现了Using filesort。都是最坏的情况，当前表没有创建索引。

开始优化：新建索引 + 删除索引

type达到较为优秀的状态，范围查询，但是using filesort仍然存在，rows也减少了。

SQL不一样，查询的结果和性能完全不同。当前索引建立不是最合适的。range查询索引字段后面的索引失效。

个人想法：删除全部索引，新增一个复合索引和一个普通索引。

作者想法：新增一个CV复合索引，单独一个C普通索引，排除范围索引。

达到最优状态，ref条件常量查询条件，索引排序，排除了range索引导致的后续索引失效。

两表案例分析

两张表创建和插入语句

select from class; select from book;

结果：

结论：type有ALL，并且两张表的全表检索，直接double kill，爽！！

开始优化：

结果，book表直接达到最优解，个人猜测：left 右边建立，righe左边建立：理由：因为左表一条会去检索另外一个方向的全部数据，可以直接让全表检索变成索引1对1检索。 如果直接两个都建立呢？？= =

开始换左表进行优化尝试

结果：

index全表查询，仍然没有改善。

三表在原来的基础上添加phone表

SQL：

最终phone被检索的次数最多，其次是book，可不可以book建立一个，phone建立一个？个人猜测，没有测试= =

结果：

直接全表扫描，ALL,ALL,ALL，直接三杀，爽死你了。

自己的猜测与作者一致：

最优解完毕，频繁检索的进行索引创建，phone = 20 20 20， book = 20 * 20;

索引失效(应该避免)

建表SQL：

案例：

好家伙，数据量有点大= =全局匹配我最爱

复合索引，即使使用一个也会生效。

失效了？ 难道是因为断层了？复合索引中的name断层没有用到??? 违背了最佳左前缀法则

最佳左前缀法则如果索引了多列，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列，

如果出现断层那么后面的索引字段就会失效。

不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描

存储引擎不能使用索引中范围条件右边的列范围索引之后索引列都失效。

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

即使使用了范围检索，范围之后的索引失效也被range 快 ref > range

mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

is null,is not null 也无法使用索引

要尽量避免数据库中字段存在空值(NULL)

like以通配符开头(%abc...)mysql索引失效会变成全表扫描的操作

百分号模糊查询，like放右边。

但是问题，就要用百分号。问题：解决like %字符串% 时索引不被使用的方法？？

创建索引：

利用覆盖索引避免了，LIKE 失效。

利用覆盖索引解决%%百分号左右匹配，但是查询字段不能含有*或者索引不存在的字段。

遇到这样的情况要加快查询个人思路：先利用覆盖索引，通过索引表查询全部的id，之后利用in进行查询，虽然也会失效= =但是个人觉得会快点把= =

字符串不加单引号索引失效会被项目经理骂死= =划重点。小失误导致的失效最不应该。

少用or，用它来连接时会索引失效

小总结题目：

a,ab,abc,0,a,ab,abc,a,a,abc.

面试分析

查询优化器，会把mysql的命令调节优化，我们创建了1234的索引，即使使用索引的数据乱了，但是最终调优之后仍然会进行优化。

因为优化器会将使用到的进行排序，1234，随后4之后没有所以全用，不会造成失效。

直接特码的灭绝师太，group by的前提是排序，所以出现了两个，一个内置排序，一个创建了临时存储表。

一般建议对于单键索引，尽量选择针对当前query国旅行更好的索引。

在选择组合索引的时候，当前Query中过滤性能最好的字段在索引顺序中，位置越靠前越好。

在选择组合索引的时候，尽量选择恶意能够包含当前query中的where子句中更多字段的索引。

尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。