Mysql的一些日常整理（查询，以及查询的大致过程以及个人分享的一点干货）

查询性能变慢的几个原因

我们可以把对于数据库的查询看作是一个任务，这个任务可以分成几个子任务，正如同我们平时进行连接查询或者嵌套查询一样，可以把不同的执行过程看作是多个子任务，那么，此时我们如果要进行查询优化，实际上就是优化其子任务（过程），要么消除子任务的个数，再者，要么减少子任务的执行次数。查询的生命周期可以笼统的按照这样的顺序，即：从客户端到服务器，然后在服务器上进行解析，生成执行计划，执行，最后将执行的结果反馈给客户端。执行应该是整个周期中最为重要的一环，原因在于其执行过程中包含了大量为了检索数据到存储引擎的调用以及调用后的数据处理，（举个例子来说，假如我们查询一个用户表，返回所有人的名字，然后这些人按照年龄进行排序）
所以，查询变慢的原因可以总结为如下几点（当然，这个不完整，这是就个人通过学习而列举的。）：
（1）向底层存储引擎检索数据的调用操作，可能会消耗大量的时间。例如：内存不足的时候导致的I/O操作消耗时间。
（2）查询过程中，可能在网络传输上消耗大量的时间，或者产生互斥等待的时候，消耗大量的时间，这都会造成查询性能变慢。
（3）查询性能低下的根本原因就是访问的数据太多，导致在进行筛选数据的时候消耗大量的时间。

查询执行的基础

mysql执行一个查询的过程：（1）客户端发送一条查询给服务器（2）服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入（3）。 （3）服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划。（4）mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询（5）将结果返回给客户端
注意客户端和服务器端的通信协议是半双工的，即要么服务器端发送数据，要么客户端向服务端发送请求，这两个动作不能同时发生。
这里面的具体操作比较复杂，本人能力有限，不能一一讲解。这里解释一下什么叫做命中缓存。
MySQL Query Cache是用来缓存我们所执行的SELECT语句以及该语句的结果集，MySql在实现Query Cache的具体技术细节上类似典型的KV存储，就是将SELECT语句和该查询语句的结果集做了一个哈希映射并保存在一定的内存区域中。当客户端发起SQL查询时，Query Cache的查找逻辑是，先对SQL进行相应的权限验证，接着就通过Query Cache来查找结果（注意必须是完全相同，即使多一个空格或者大小写不 同都认为不同，即使完全相同的SQL，如果使用不同的字符集、不同的协议等也会被认为是不同的查询而分别进行缓存）。它不需要经过Optimizer模块进行执行计划的分析优化，更不需要发生同任何存储引擎的交互，减少了大量的磁盘IO和CPU运 算，所以有时候效率非常高。缓存命中的条件如下：
（1）缓存存在一个hash表中,通过查询SQL,查询数据库,客户端协议等作为key.在判断是否命中前,MySQL不会解析SQL,而是直接使用SQL去查询缓存,SQL任何字符上的不同,如空格,注释,都会导致缓存不命中.
（2）如果查询中有不确定数据,例如CURRENT_DATE()和NOW()函数,那么查询完毕后则不会被缓存.所以,包含不确定数据的查询是肯定不会找到可用缓存的
如果小伙伴们想把缓存这部分内容更详细的了解，可以去看看这篇博文：
https://www.cnblogs.com/Alight/p/3981999.html

最后，分享一点干货： 优化数据库几种操作的方式（个人在面试中碰见的问题）

作为一只菜鸟，在前段时间的面试中，总是被问到这样一个问题：熟悉mysql吗，有没有考虑过mysql优化的方式。就本人整理而来的几种常见优化方式分享给需要的人。如下：
(1)在数据库设计方面，尽量占用小的磁盘空间，尽可能使用更小的数据类型，尽可能的定义字段为not null,如果没有变长字段，尽量使用char，只需要创建确实需要的索引，索引的第一部分必须使最常使用的字段。所有数据在保存到数据库之前进行处理。所有字段都得有默认值。

（2）在系统用途方面：（1）尽量使用连接。（2）通过使用explain查看复杂SQL的执行方式，并执行优化。（2）如果两个表要做比较，那么做比较的字段必须类型和长度一致。（4）limit语句尽量要跟order by 或者DISTINCT 搭配使用，这样可以避免做一次full table scan。（5）如果想要清空表的所有记录，建议使用truncate table tablename而不是delete from tablename。（6）在一条insert语句中采用多重记录插入格式，而且使用load data infile 来导入大量数据。（7）如果date类型的数据需要频繁的做比较，那么尽量保存为unsigned int 类型，可以加快比较的速度。
（3）数据库参数优化
（4）SQL语句优化：（1）尽量避免使用子查询，因为字查询先查外表，再查内表，如果外表数据量过大，则会很浪费资源。采用join关联方式（select id from t1 join t2 on t1.id=t2.id）对其进行优化。该优化只对select有效，对update或delete子查询无效。
（2）避免函数索引，即用select * from T where D >=’2016-10-01’代替select * from T where year(D)>=2016（注：之所以要避免函数索引，是因为mysql不能自动解析这个方程式，这完全是用户行为，其不能解析函数的参数，这是一个常见的错误。）（3）用in来替换or（in和or的效率，取决目标条件列是否有索引或者是否是主键，如果有索引或者主键性能没啥差别，如果没有索引，in的性能要远远优于or.or的效率为O(n)，而in的效率为O(log2n)，当基数越大时，in的效率就能凸显出来了。
如果你想更好的了解，参考这位博主的博文https://blog.csdn.net/weixin_40609759/article/details/79998911
）（4）在like中双百分号无法使用到索引（5）读取适当的记录limit M,N（6）避免数据类型不一致。（7）分组统计可以禁止排序。（8）避免随机取记录。（9）禁止不必要的order by排序（10）尽量使用批量的INSERT插入（这是因为可以避免重复的与数据库进行连接，降低资源的消耗）。
其他的SQL语句优化的原因，有兴趣的可以自己研究研究。作为菜鸟的我还没有研究过为什么。

8、如何分析一条SQL语句的执行性能？需要关注哪些信息？
使用explain命令，观察type列，可以知道是否是全表扫描，可以知道索引的使用方式，可以观察key知道用了哪个索引，观察key_len知道索引是否使用完成。观察rows可以知道扫描的行数是否过多，观察extra可以知道是否使用了临时表和进行了额外的排序操作。
9、优化SQL语句执行效率的方法
（1）尽量选择较小的列（2）将where中用的比较频繁的字段建立索引（3）select子句中避免使用*（3）避免在索引列上使用计算，NOT、IN和<>操作。（4）针对查询较慢的语句可以使用explain分析该语句的具体执行情况。
10、如何提高insert的性能
（1）合并多条insert为一条，即批量插入（因为使用一次插入一条语句的方式，就得多次调用insert语句，这就意味着多次与数据库建立连接，增加服务器的负荷。执行每一次SQL服务器都要同样对SQL进行分析、优化等操作）（2）修改参数bulk_insert_buffer_size,调大批量插入的缓存。（3）设置innodb_flush_log_at_trx_commit=0

后续:下一篇，涉及一下数据库索引…