07-mysql-SQL优化

一、MySQL的逻辑架构

1.连接层
 最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
 
2.服务层
  第二层架构主要完成大多少的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
 
3.引擎层
  存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB
 
4.存储层
  数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

存储引擎：MyISAM 和 InnoDB

通过这个命令可以查看当前数据库支持哪些存储引擎：

这两个引擎，MyISAM 注重性能，读取数据的速度非常的快，InnoDB注重事务，注重安全，读取速度比较慢。
我们企业中，一般会做一个MySQL主从复制   可以将主的MySQL服务器，配置为InnoDB, 从服务器可以配置为MyISAM ，因为它负责读。
可以做到读写分离。

 二、MySQL语句的执行顺序是什么？

以上是书写顺序，交给mysql数据库执行的时候，不一定 按照这个顺序执行：

先找到  A  表和  B 表， 将两个表的数据关联起来，通过where 条件过滤掉不需要的数据。
    对剩余的数据分组，分组完成后，过滤掉不需要的组数据。剩下的就是我们想要的数据了。
查找需要的字段，滤重。排序 ，展示多少条数据即可。

 通过鱼骨图表示如下：

三、数据库的索引数据结构是什么？

索引的数据结构是： B+ Tree

探讨一下，为什么不是Hash 呢 ？ 为什么不是二叉树？ 为什么不是平衡二叉树？ 为什么不是红黑树？ 为什么不是B Tree ?

偏偏是B+ Tree ? B Tree和 B+ Tree有何区别？

可以使用数据结构的可视化工具演示一下：

Data Structure Visualization

1、Hash 数据结构

Hash数据结构，如果针对相等的数据来讲，速度是非常快的，一次就搞定了，但是没办法进行范围查找。

比如 select * from user where id > 10;

2、二叉树

二叉树的特点是：一个节点，左边存放比它小的数，右边存放比它大的数。

 乍一看好像挺好，实际上，我们这个里面存储的是索引的值，索引的值一般又使用了数字。

要查找ID = 6 的数据，也需要查询六次才能获取到数据 

3、平衡二叉树

平衡二叉树，因为可以自动的平衡，树的高度比之前小了很多，但是数据量上去以后，高度也会增加，查找到速度也会变慢。 

4、红黑二叉树

也是平衡二叉树的一种，只是比之前好了一点

5、B Tree 

B Tree的一个特点：

节点上不仅保存指针，也保存数据data

mysql查找数据的时候是按照页查找的，相当于查找一次，就查找一页的数据，或者说mysql查找数据的时候一次获取16K的数据（一页数据）。
相当于16K如果获取的数据越多越好。
假如数据中不仅有指针数据还有真实的数据的话，那么获取到的指针数据就少了。
就是说：本身16KB可以都存  指针数据和Key数据，可以拿1000个左右，但是加上data数据后，每一次只能拿 100个左右了。
还有一个缺点：
B-Tree 在进行 回旋查找比较费劲。

1、BTree上的每一个节点都存放的有数据，我每一次从mysql中获取数据的时候是一次16KB,存放的节点比较少，没有B+Tree多
2、BTree 排序功能比较弱，在进行范围查找的时候，回旋查询次数比较多。效率低

 6、B+Tree

 跟B Tree相比有两方面的改进：

1、有序的，想查找某个范围速度非常的快
2、数据节点和指针分开了，一次获取到的数据比之前多很多，这样的设计，同样是三层架构，可以存储的数据比较多。

mysql也使用了hash算法。当你进行等值查询时，使用hash算法，假如使用范围查找时，使用b+tree数据结构。

四、缓冲池 Buffer Pool

mysql它的数据也存储在磁盘上，为什么可以达到毫秒级的响应呢？或者为什么速度这么快呢？

Mysql 的底层是文件系统，查询数据是需要 IO 的，但是我们每次查询数据感觉没有那么慢，原因是数据库中有一层 Buffer Pool。
缓冲池，简单来说就是一块内存区域。它存在的原因之一是为了避免每次都去访问磁盘，把最常访问的数据放在缓存里，提高数据的访问速度。

还有就是得益于预读机制：

作用就是尽可能减少磁盘的 IO，它是 Innodb 通过在缓冲池中提前读取多个数据页来优化 I/O 的一种方式。因为磁盘读写的时候，是按照页的方式来读取的（你可以理解为固定大小的数据，例如一页数据为 16K），每次至少读入一页的数据，如果下次读取的数据就在页中，就不用再去磁盘上读取了，从而减少了磁盘 I/O
 
比如： select * from user where  id = 10;
这个时候用户只看到了一条数据，但是mysql其实读取了一页数据，这一页数据有多少条呢？取决于这一条数据多大，每次读取一页数据16KB。
虽然只展示了一条数据，但是 id = 11   id =12   id =9 等等这附近的数据都已经读取出来了。
将不展示的数据放入到缓冲池里面去（其实就是一块内存），当下一次再读取的时候先去缓冲池中查找是否有该数据。如果有就直接获取即可。减少IO操作。

缺点是：有可能会出现预读失效以及缓冲池污染的问题，这些问题都仅仅围绕 缓冲池。

什么是预读失效？

上面我们提到了缓冲池的预读机制可能会预先加载相邻的数据页。假如加载 了 20、21 相邻的两个数据页，如果只有页号为 20 的缓存页被访问了，而另 一个缓存页却没有被访问。此时两个缓存页都在链表的头部，但是为了加载这两 个缓存页却淘汰了末尾的缓存页，而被淘汰的缓存页却是经常被访问的。这种情 况就是预读失效，被预先加载进缓冲池的页，并没有被访问到，这种情况是不是 很不合理。

什么又是缓冲池污染呢？

还有一种情况是当执行一条 SQL 语句时，如果扫描了大量数据或是进行了全表扫描，此时缓冲池中就会加载大量的数据页，从而将缓冲池中已存在的所有页替换出去，这种情况同样是不合理的。这就是缓冲池污染，并且还会导致MySQL 性能急剧下降。

 如何来解决这些问题呢？-- 冷热数据分离

冷热数据分离：
也就是将 LRU 链表分为两部分，一部分为热数据区域，一部分为冷数据区域。当数据页第一次被加载到缓冲池中的时候，先将其放到冷数据区域的链表头部，1s（由 innodb_old_blocks_time 参数控制） 后该缓存页被访问了再将其移至热数据区域的链表头部。

 mysql这个数据库的并发量高吗？

mysql 的最大并发量 150
tomcat的并发量 200
mysql数据库中单表数据超过500万，就开始考虑优化了。一个表分为多个表（分表），一个数据库分为多个数据（分库）。假如你想搭建一个mysql集群，推荐使用mycat。

五、Explain 用法

在公司中，编写SQL语句，首先要先将需求写出来，不要管优化的事儿。运行一段时间后，查找出来SQL效率不高的语句，单独分析。数据库都有一个功能，叫做慢查询。可以开启慢查询，它就会在运行过程中，将执行速度比较慢的SQL语句给你展示出来，这个时候再针对这样的SQL语句进行分析，看慢在了哪里？

如何分析呢？ 使用 Explain 的关键字。

mysql> explain select * from tbl_dept  where id = 1;
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | tbl_dept | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+

我们要学习的是如何看懂这个分析的结果。

这个结果中有10个关键字。

1、id 

id:   select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序.
id如果相同，可以认为是一组，从上往下顺序执行；
在所有组中，id值越大，优先级越高，越先执行

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

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

 2、select_type 

查询的类型，主要是用于区别 普通查询、联合查询、子查询等的复杂查询
*IMPLE：简单的 select 查询,查询中不包含子查询或者UNION
*PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
*SUBQUERY：在SELECT或WHERE列表中包含了子查询
*DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询, 把结果放在临时表里。
*UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
*UNION RESULT：从UNION表获取结果的SELECT

3、table （了解）

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

如果是衍生表，一般是derivered+id作为表名。

以上面的SQL为例，video 的id 是2 , 所以 ID = 1 的 table 叫做 derived2

4、type 非常重要的指标

这个指标是说明我们这个SQL是好还是坏的一个非常重要的指标；

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

按照上面的顺序，SQL从 好变为坏。

最坏的就是 type = ALL 表示 SQL语句全表扫描了。

以上这个指标的值太多了，一般我们也见不到这么多的值，可以掌握如下几个即可：

system > const > eq_ref > ref > range > index>all 这个必须掌握。

一般来说，得保证查询至少达到range级别，最好能达到ref。 所以经常见的这几个值是 ref、range、index、all

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

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

Select * from user where id=1

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

执行的顺序是：先对t2表进行一个全表的扫描查询，然后根据t2表中的id值查找与之对应的t1的数据。

以上SQL写的是否合理，取决于t2表是大表还是小表。

最好的结果是小表先查询，再查询大表，小表驱动大表。

t2 中的 type = ALL表示进行了全表扫描，如果这个t2 表的数据特别多，这个查询就非常的烂，如果t2的数据没有t1 的多，这个SQL就是一个好的SQL。

好比是Student 和 Course ,肯定课程表是小表，Student 表是大表。

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

如果根据一个索引查找出来了一条数据，那么是eq_ref ,如果使用到了索引，但是查询出的结果可能是多条，ref.

range: 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引

一般就是在你的where语句中出现了between、<、>、in等的查询

这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

Index: 称之为覆盖索引

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

假如有一个表  user    name 和 phone 组合成了一个联合索引。
select * from  user ; // 这个肯定是全表扫描了，效果非常差。
select name,phone from user; // 这个时候使用到了索引的。  name和phone 都在索引库中，通过索引库就可以查找到所有的数据，不需要去 文件中查找数据，这个就称之为覆盖索引。
 
index类型指的是你要查找的数据刚好在索引库中存在，你不需要区数据库中查找了，这种sql就是index类型的。所以也给我们一个启发：写sql,用到什么字段就写什么字段，不要使用*。

 ALL: Full Table Scan，将遍历全表以找到匹配的行,没有使用到索引。

        总结：通过explain 进行SQL语句分析的时候，如果查看到 type 是ALL就说明这个SQL语句进行了全表扫描，需要优化（数据量比较大）。

                eq_ref > ref > range > index > ALL

5、possible_keys 

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

6、key

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

7、key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好（char类型就比varchar短两位，null比not null长1位）。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

长度的计算是一个非常复杂的过程，可以作为了解：

长度的计算是一个复杂的算法：跟字符集有关系，跟数据类型，跟是否为varchar类型有关系，跟是否为null有关系。
以上面这个算法为例：
    name 列是索引列，  name="zhangsan"  那肯定用到了这个索引。
    这个长度是 78
因为name 是字符 ，由于我们的数据库采用的是utf-8  一个字符需要三个字节
    24 * 3 = 72
由于存放字符的是varchar 需要再加上2  ，如果 是char(24) 不需要+2
    72 + 2 = 74
再由于我们的这个name 不允许为null   ,所以不需要  + 1  ，如果这个字段允许为null   ,就需要长度 再加 +1

 8、ref

索引被使用的具体情况，和key不太一样，key说明有没有用到索引，ref指的是使用索引的具体情况。
此处的ref 是一个字段，跟前面讲的ref 不一样，前面的type字段的一个值。
ref 表示使用到了哪些索引。key 表示使用到了索引，索引的具体情况没有说，需要查看ref 才能够知道。

9、rows 

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
简单来讲，就是查询出来这一条数据，查询了多少条。

10、extra 

包含不适合在其他列中显示但十分重要的额外信息

1）Using filesort （出现这个说明非常不好）

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
MySQL中无法利用索引完成的排序操作称为“文件排序”
说白了就是你在进行order by 的时候没有使用到索引建立的梯子，中间有断层
    
首先我新建一张表t1,创建了一个复合索引：col1,col2,col3

2）Using temporary （出现这个说明非常不好） 

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

3）USING index 说明还可以，还不错 

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

 覆盖索引的复习：

关于覆盖索引：
覆盖索引（Covering Index）,一说为索引覆盖。
理解方式一:就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
 
理解方式二:索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。
 
注意：
如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，
因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。 

4）Using where

表明使用了where过滤

5）using join buffer 使用了连接缓存

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

比如：select * from user where name=“张三” and name=”李四”

通过explain 进行sql分析的时候，其实就是查看这个大的SQL拆分以后，每一个小的SQL语句是如何执行的。
并且查看每一个小的SQL语句它的type类型 和 Extra 这两个字段的值
查看type类型是否为ALL
查看Extra 这个字段是否出现 filesort temporary

六、SQL的优化方案

sql优化呢，其实就是添加索引，添加索引就害怕索引失效，哪些写法可能出现索引失效呢？

1、全字段匹配是最棒的

假如一个Staffs 这个表，将 name,age ,pos 组合成了一个联合索引，在where条件下，能够使用到的索引越多越好。
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

2、最佳左前缀法则 

创建索引的时候， name --> age --> pos

在使用索引的时候，也必须按照这个顺序来，不能少，顺序可以乱。

3、索引列上不计算 

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

4、不能使用索引中范围条件右边的列

Explain select * from staffs where name='' and age >25 and pos='';

age > 25 这个 age 这个索引也用到了，但是 pos这个字段的索引没有起作用，失效了。

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

以上的SQL语句，一样的where条件，因为 select 后面的字段不一样，sql的执行效率就不一样，因为第二个语句将 * 改为了三个索引字段，使用到了覆盖索引。

6、使用不等于(!= 或者<>)的时候无法使用索引

7、注意字段的null值和 not null 对sql的影响

第一种情况，name 是一个索引，name 字段允许为null

第二种情况：name 是一个索引，name 字段不允许为null

总结就是：索引字段，这个字段是否为null 对于sql的好坏有一定的影响，需要警惕。

8、like以通配符开头('%abc...')mysql索引失效

9、字符串不加单引号索引失效 （底层使用数据类型转换）

10、少用or,用它来连接时会索引失效

可以使用union 替代 or

索引的练习题： 

以上这些就是sql的优化的一部分。

总结了一个口诀：

全职匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引无所谓；
不等空值还有OR，索引影响要注意；