mysql优化基础一(基础理论篇)_mysql优化器理论基础-CSDN博客

一方面复习基础，一方面注意事项

一.配置部分
二.修改字符集
三.索引
四.explain

安装问题：mysql版本必须匹配相应位数(64或32)的电脑，否则会报如下错误：

warning: MySQL-server-5.5.48-1.linux2.6.i386.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
	libaio.so.1 is needed by MySQL-server-5.5.48-1.linux2.6.i386
	libaio.so.1(LIBAIO_0.1) is needed by MySQL-server-5.5.48-1.linux2.6.i386
	libaio.so.1(LIBAIO_0.4) is needed by MySQL-server-5.5.48-1.linux2.6.i386

一.配置部分

路径须知：
准备工作：备份mysql配置文件my-huge.cnf(/usr/share/mysql/)为my.cnf(/etc/)，即保留原来的配置文件，把它复制一份用来进行修改操作，这样可以防出错。(my-huge.cnf是5.5版本，5.6是my-default.cnf)

二.修改字符集

原因：默认字符集会导致中文乱码，如下：
检查字符集设置：由图可知出现的两处默认的latin1便是出现乱码的原因。
执行修改操作：打开一开始备份的配置文件my.cnf，加入以下图中的设置保存后，重启mysql：

[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
[mysql]
default-character-set=utf8

注意点：虽然修改了配置文件，但如果是往修改之前的数据库中加中文数据，则还是会乱码。需要重新建一个库才能生效。

三.索引

1.什么是索引

定义：帮助mysql高效获取数据的数据结构
存储：由于索引本身很大，故不是存储在内存，而是以索引文件的形式存储在磁盘上。

2.优势和劣势

优势：
1.通过建立索引，提高检索效率，降低数据库IO成本；
2.通过索引列对数据进行排序，降低数据排序成本，从而降低了CPU消耗。
劣势：
1.如果建立过多索引列会占用较大空间。
2.降低更新表的速度。如对表进行插入，更新或删除操作。因为更新表时，mysql不仅要保存数据，还要保存索引文件每次更新添加的索引列的字段，即在改数据的同时会更改索引。
3.索引不是一成不变的，需要更加实际情况不断优化和重建。

3.索引分类

单值索引：一个索引只包含单个列，一个表可以有多个单值索引。(假设一个表user 有 id，name，email，address字段，给name建立索引)
唯一索引：索引列的值必须唯一，但允许有空值。(例如身份证号)
复合索引：一个索引包含多个列。(假设一个表user 有 id，name，email，address字段，给name和email建立索引)
索引基础sql语句

四.explain

1.是什么

简介：使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道mysql是如何处理你的sql语句的。分析你的查询语句或是表结构的性能瓶颈。即查询执行计划。
官网介绍

2.怎么使用

语法：EXPLAIN + SQL语句
执行计划包含信息如下图：

3.explain信息字段解释

1.id字段

说明：select查询的序列号包含一组数字，表示查询中的执行select子句或操作表的顺序。
三种情况：
1.id相同，执行顺序由上至下。图片描述如下：

2.id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。(类似于加法运算中先算括号里的)

3.既有id相同，又有id不同，规律还是以上两种。（下图表中的derived2的单词是衍生的意思，2指的id=2，即说明括号中的表是由id=2操作得出的）

2.常见select_type字段

SIMPLE：简单的select查询，不包括子查询或UNION。
PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为此字段。(具体可参照以上几张图)
SUBQUERY：在SELECT或WHERE列表中包含了子查询。
DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)，mysql会递归执行这些子查询，把结果防在临时表中。(可参照以上第三张图)
UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED。
UNION RESULT：UNION查询结果，如下图：

3.常见type字段

system：表只有一行记录(等于系统表)，这是const类型的特例，平时不会出现，可以忽略不计。
const：通过索引一次就找到了，const用于比较primary key或unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，mysql就能将该查询转换为一个常量，如下图(括号中的where条件指定的就是一个写死的常量)：
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。图示如下：
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，但它可能会找到多个符合条件的行，所以应该属于查找和扫描的混合体。
range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between，<>，in等的查询。
index：full index scan，index与All区别为index只遍历索引树。这通常比All快，因为索引文件通常比数据文件小。(由下图可知，查询的id是主键索引，即id在索引上，则类型为index)
all：full table scan，遍历全表以找到匹配行。

==注意：==以上类型的查询效率是有优劣之分的，如下：

一般来说，得保证至少到达range级别，最好是ref，毕竟理想与现实存在差距。
推荐相关博客连接

4.possible_keys与key，key_len字段

possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key：实际使用的索引。如果为null，则没有使用索引。查询中如果使用了覆盖索引，则该索引出现在key列表中。

如上图，possible_keys显示了两个索引：primary和idx_t1，但实际用到的是key显示的idx_t1索引。

通过这张图，说明一下覆盖索引。由select可知查询的是col1，col2字段，而由创建索引可以看到t2(col1,col2），同样是这两个字段，且个数和顺序都匹配，则虽然查询这两个字段本来不会用到索引而进行全表扫描(possible_keys = null)，但既然有这个匹配的索引，那实际就会用的它(key = idx_col1_col2)。
key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好(但由下图解析说明了精确性与长度的矛盾)。其显示的值为索引字段的最大可能长度，并非实际使用长度。

由图可知，第二次查询在第一次查询基础上多加了一个col2='ac’的条件，发现key_len也变大了。说明随着增加条件来达到更加精确地查询，使用的长度会增加。

5.ref字段

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

由key_len可知他t1表的idx_col1_col2索引被充分使用，即col1匹配了t2表的col1,col2匹配了一个常量值，而ref负责显示col1这一列和’ac’这个常量值const。

6.rows字段

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

由上图可知第一次查询没有建索引表，则会以t2表为驱动表全表查询，而t1查询的是唯一索引(主键id)，可以在rows看出查询两表的行数。再经过建立idx_col1_col2索引后，查询t2表实际用的是这个索引来查询，故而相比之前全表查询的行数少了很多。

7.Extra字段

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

由以上图两次查询比较，不难看出差别在第二个比一个排序处多了一个col2条件。但就是这一个小差别导致第一次的Extra出现using filesort，而第二次没有，即第一次虽然实际使用了idx_col1_col2_col3进行查询，但是在排序时没有用到它。相比之下，第二次按照我们自己建立的索引进行查询并排序更好，而像第一次那样是mysql自己内部进行排序是多此一举。
Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

由图可知，第一次查询与第二次查询的区别在与排序条件的不同，第一次比第二次少了col1，从而导致了extra中的using temporary。因为这相当于上楼，第一次排序直接就上二楼，把一楼断了，显然这是不允许的。即对于排序操作，需要保证其条件索引的个数和顺序要与建立的索引表一致。
Using index：表示相应的操作中使用了覆盖索引，避免访问表的数据行，效率不错；如果同时还出现了using where，表明索引被用来执行索引键值的查找，即需要的数据都在索引列中能找到，不需要再回表查询数据；如果没有同时出现using where，表明索引用来读取数据而非执行查找工作。
Using where：使用了where过滤
Impossible where：where子句的值总是false，不能用来获取任何元组。如下图，查询的员工名字同时有两个，则这个where条件是获取不到数据的，因为名字不可能同时有两个，