MySql优化

                     MySQL优化

项目运行到一定阶段，就可能出现性能问题，很多时候我们都是进行代码优化和数据库优化。

1. 代码优化

一个项目的参与人员能力都不一样，可能很多代码都有优化的空间，比如保存的时候没有使用批量，查询的时候没有使用最优的方案，最优的方案主要减少交互次数，我们要根据需求调整一个方案，比如需求合并，减少查询次数等等，我们现在主要讲MySql优化

1. 数据库优化

    MySQL整个查询执行过程，总的来说分为几个步骤：

1. 客户端向MySQL服务器发送一条查询请求
2. 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
3. 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
4. MySQL根据执行计划，调用存储引擎的API来执行查询
5. 将结果返回给客户端，同时缓存查询结果

开启MySQL缓存：

   MySQL my.ini文件设置查询缓冲，将query_cache_type设为1，在设置了这个属性后，MySQL在执行任何SELECT语句之前，都会在它的缓冲区中查询是否在相同的SELECT语句被执行过，如果有，并且执行结果没有过期，那么就直接取查询结果返回给客户端。

SQL优化，主要三点:

1. 最大化利用索引；
2. 尽可能避免全表扫描；
3. 减少无效数据的查询；

实际操作中，我们可以使用explain来处理mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

explain select * from supplier_master_data

1. select_type列

表示对应行是简单查询还是复杂查询。

1. simple：不包含子查询和union的简单查询
2. primary：复杂查询中最外层的select
3. subquery：包含在select中的子查询（不在from的子句中）
4. derived：包含在from子句中的子查询。mysql会将查询结果放入一个临时表中，此临时表也叫衍生表。
5. union：在union中的第二个和随后的select，UNION RESULT为合并的结果

1. table列

表示当前行访问的是哪张表。

1. partitions列

查询将匹配记录的分区。 对于非分区表，该值为 NULL。

1. type列

    此列表示关联类型或访问类型。也就是MySQL决定如何查找表中的行。依次从最优到最差分别为：system > const > eq_ref > ref > range > index > all。

1. NULL：MySQL能在优化阶段分解查询语句，在执行阶段不用再去访问表或者索引。
2. system、const：MySQL对查询的某部分进行优化并把其转化成一个常量（可以通过show warnings命令查看结果）。system是const的一个特例，表示表里只有一条元组匹配时为system。
3. eq_ref：主键或唯一键索引被连接使用，最多只会返回一条符合条件的记录。简单的select查询不会出现这种type。
4. ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引和某个值比较，会找到多个符合条件的行。
5. range：通常出现在范围查询中，比如in、between、大于、小于等。使用索引来检索给定范围的行。
6. index：扫描全索引拿到结果，一般是扫描某个二级索引，二级索引一般比较少，所以通常比ALL快一点。
7. ALL：全表扫描，扫描聚簇索引的所有叶子节点。

1. possible_keys列

     此列显示在查询中可能用到的索引。如果该列为NULL，则表示没有相关索引，可以通过检查where子句看是否可以添加一个适当的索引来提高性能。

1. key列

此列显示MySQL在查询时实际用到的索引。在执行计划中可能出现possible_keys列有值，而key列为null，这种情况可能是表中数据不多，MySQL认为索引对当前查询帮助不大而选择了全表查询。如果想强制MySQL使用或忽视possible_keys列中的索引，在查询时可使用force index、ignore index。

1. key_len列

        此列显示MySQL在索引里使用的字节数，通过此列可以算出具体使用了索引中的那些列。索引最大长度为768字节，当长度过大时，MySQL会做一个类似最左前缀处理，将前半部分字符提取出做索引。当字段可以为null时，还需要1个字节去记录。

      key_len计算规则：

          字符串：

              char(n)：n个数字或者字母占n个字节，汉字占3n个字节

              varchar(n)：  n个数字或者字母占n个字节，汉字占3n+2个字节。+2字节用来存储字符串长度。

          数字类型：

              tinyint：1字节      smallint：2字节               

int：4字节           bigint：8字节

          时间类型

              date：3字节        timestamp：4字节          

datetime：8字节

1. rows列

        此列是MySQL在查询中估计要读取的行数。注意这里不是结果集的行数。

1. Extra列

     此列是一些额外信息。常见的重要值如下：

1）Using index：使用覆盖索引（如果select后面查询的字段都可以从这个索引的树中获取，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值，这种情况一般可以说是用到了覆盖索引）。

     2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖。

 3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个查询的范围。

4）Using temporary：MySQL需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的。

 5）Using filesort：将使用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。

 6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时。

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

➤ 通过EXPLAIN，我们可以分析出以下结果：

1. 表的读取顺序
2. 数据读取操作的操作类型
3. 哪些索引可以使用
4. 哪些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被优化器查询

避免不走索引的场景

1、尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描

如果需求是要在前面使用模糊查询:

1）使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()，查询字符串出现的角标位置，可参阅《MySQL模糊查询用法大全（正则、通配符、内置函数等）》

2）使用FullText全文索引，用match against 检索

3）数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级

4）当表数据量较少（几千条儿那种），别整花里胡哨的，直接用like '%xx%'。

2、尽量避免使用in 和not in，会导致引擎走全表扫描。优化方式：如果是连续数值，可以用between代替，如果是子查询，可以用exists代替

3、尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描，优化方式：可以用union代替or

4、尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描，优化方式：可以给字段添加默认值0，对0值进行判断

5、尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。可以将表达式、函数操作移动到等号右侧

6、当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and

7、查询条件不能用 <> 或者 !=，使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

8、where条件仅包含复合索引非前置列，如下：复合（联合）索引包含key_part1，key_part2，key_part3三列，但SQL语句没有包含索引前置列"key_part1"，按照MySQL联合索引的最左匹配原则，不会走联合索引。详情参考《联合索引的使用原理》。

9、隐式类型转换造成不使用索引，如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

10、 order by 条件要与where中条件一致，否则order by不会利用索引进行排序

对于上面的语句，数据库的处理顺序是：

第一步：根据where条件和统计信息生成执行计划，得到数据。

第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。

第三步：返回排序后的数据。

当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。

这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

11、正确使用hint优化语句

MySQL中可以使用hint指定优化器在执行时选择或忽略特定的索引。

一般而言，处于版本变更带来的表结构索引变化，更建议避免使用hint，而是通过Analyze table多收集统计信息。但在特定场合下，指定hint可以排除其他索引干扰而指定更优的执行计划。

USE INDEX 在你查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。例子: SELECT col1 FROM table USE INDEX (mod_time, name)...

IGNORE INDEX 如果只是单纯的想让 MySQL 忽略一个或者多个索引，可以使用 IGNORE INDEX 作为 Hint。例子: SELECT col1 FROM table IGNORE INDEX (priority) ...

FORCE INDEX 为强制 MySQL 使用一个特定的索引，可在查询中使用FORCE INDEX 作为Hint。例子: SELECT col1 FROM table FORCE INDEX (mod_time) ...

在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。

SELECT语句其他优化

1. 避免出现select *

使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。按需显示

2. 避免出现不确定结果的函数

特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。

3.多表关联查询时，小表在前，大表在后。

在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。

例如：表1有50条数据，表2有30亿条数据；如果全表扫描表2，你品，那就先去吃个饭再说吧是吧。

4. 使用表的别名

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。

5. 用where字句替换HAVING字句

避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。

where和having的区别：where后面不能使用组函数

6.调整Where字句中的连接顺序

MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

增删改 DML语句优化

1. 大批量插入数据

如果同时执行大量的插入，建议使用多个值的INSERT语句(方法二)。这比使用分开INSERT语句快（方法一），一般情况下批量插入效率有几倍的差别。

2. 适当使用commit

适当使用commit可以释放事务占用的资源而减少消耗，commit后能释放的资源如下：

事务占用的undo数据块；

事务在redo log中记录的数据块；

释放事务施加的，减少锁争用影响性能。特别是在需要使用delete删除大量数据的时候，必须分解删除量并定期commit。

3. 避免重复查询更新的数据

针对业务中经常出现的更新行同时又希望获得改行信息的需求，MySQL并不支持PostgreSQL那样的UPDATE RETURNING语法，在MySQL中可以通过变量实现。

4.查询优先还是更新（insert、update、delete）优先

MySQL 的默认的调度策略可用总结如下：

写入操作优先于读取操作。

对某张数据表的写入操作某一时刻只能发生一次，写入请求按照它们到达的次序来处理。

对某张数据表的多个读取操作可以同时地进行。MySQL 提供了几个语句调节符，允许你修改它的调度策略：

LOW_PRIORITY关键字应用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE；

HIGH_PRIORITY关键字应用于SELECT和INSERT语句；

DELAYED关键字应用于INSERT和REPLACE语句。

查询条件优化

1. 对于复杂的查询，可以使用中间临时表 暂存数据；

2. 优化group by语句

默认情况下，MySQL 会对GROUP BY分组的所有值进行排序，如 “GROUP BY col1，col2，....;” 查询的方法如同在查询中指定 “ORDER BY col1，col2，...;” 如果显式包括一个包含相同的列的 ORDER BY子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。

因此，如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL禁止排序。

3. 优化join语句

MySQL中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN)..替代。

4. 优化union查询

MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。

5.拆分复杂SQL为多个小SQL，避免大事务

简单的SQL容易使用到MySQL的QUERY CACHE；

减少锁表时间特别是使用MyISAM存储引擎的表；

可以使用多核CPU。

6. 使用truncate代替delete

当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。

使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位，使自增字段值归零。

7. 使用合理的分页方式以提高分页效率

使用合理的分页方式以提高分页效率 针对展现等分页需求，合适的分页方式能够提高分页的效率。

建表优化

1、在表中建立索引，优先考虑where、order by使用到的字段。

2、尽量使用数字型字段（如性别，男：1 女：2），若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

3、查询数据量大的表 会造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段分页进行查询，循环遍历，将结果合并处理进行展示。要查询100000到100050的数据，如下：

用varchar/nvarchar 代替 char/nchar，尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

show processlist

show processlist 是显示用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。除非单独个这个用户赋予了PROCESS 权限。

通过 show processlist 查询当前mysql有些线程正在运行,然后分析其中的参数,找出那些有问题的线程,该kill的kill,该优化的优化!

1. sql语句时间执行过长导致CPU高解决方案

1)根据Time这列 找出Time 时间过长的语句
2)查看当前这条线程sql State所处的动作
3)如果是Sending data 说明正在执行 将info中的语句复制 通过 explain进行分析 可以到当前语句是否建有索引 如果没建添加对应的索引即可

2、大量的睡眠线程导致CPU过高解决方案

运行 show full processlist

1）根据Command 这一列发现大量的Sleep

2） 再根据Time这一列 查看当前Sleep线程的sql 所用的耗时

3）根据sql睡眠线程耗时时间 配置msyql

打开mysql的配置文件— my.cnf文件

配置如下：

# vim /etc/my.cnf

[mysqld]

wait_timeout=20 ## 大于20秒自动断开 (根据实际情况设置)