本文正在参加「技能专题19期 漫谈数据库技能」活动

0. 目录

1）MySQL整体架构介绍

2）MySQL存储引擎调优

3）常用慢查询剖析东西

4）怎么定位不合理的SQL

5）SQL优化的一些主张

1 MySQL整体架构介绍

1.1 MySQL整体架构介绍

MySQL是一个联系型数据库

• 运用十分广泛
• 在学习任何一门知识之前
• 对其架构有一个概括性的了解是非常重要的
• 比方索引、sql是在哪个当地履行的
• 流程是什么样的
• 今日咱们就先来学习一下MySQL的整体架构

总的来说：MySQL架构是一个客户端-服务器体系。

MySQL首要包括以下几部分:

Server 层：首要包括衔接器、查询缓存、剖析器、优化器、履行器等，一切跨存储引擎的功用都在这一层完结，比方存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。

存储引擎： 首要负责数据的存储和读取，选用能够替换的插件式架构，支撑 InnoDB、MyISAM、Memory 等多个存储引擎，其间 InnoDB 引擎有自己的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版别开端就被当做默许存储引擎了

衔接器： 身份认证和权限相关(登录 MySQL 的时分)。

查询缓存: 履行查询句子的时分，会先查询缓存（MySQL 8.0 版别后移除，因为这个功用不太实用）mysql的server层添加一层缓存模块，相似一个内存的kv层，k是sql，value是成果

剖析器: 没有命中缓存的话，SQL 句子就会经过剖析器，剖析器说白了便是要先看你的 SQL 句子要干嘛，再检查你的 SQL 句子语法是否正确。

优化器： 依照 MySQL 以为最优的计划去履行。

履行器: 履行句子，然后从存储引擎回来数据。

1.2 MySQL存储引擎介绍

• 和大多数的数据库不同, MySQL中有一个存储引擎的概念
• 针对不同的存储需求能够挑选最优的存储引擎。
• 存储引擎便是存储数据，树立索引，更新查询数据等等技能的完结办法 。
• 存储引擎是依据表的，而不是依据库的,所以存储引擎也可被称为表类型。

MySQL供给了插件式的存储引擎架构。所以MySQL存在多种存储引擎，能够依据需求运用相应引擎，或许编写存储引擎。

MySQL5.0支撑的存储引擎包括 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等

能够经过指定 show engines ， 来查询当时数据库支撑的存储引擎 ：

SHOW ENGINES;

表意义:
  - support : 指服务器是否支撑该存储引擎
  - transactions : 指存储引擎是否支撑业务
  - XA : 指存储引擎是否支撑分布式业务处理
  - Savepoints : 指存储引擎是否支撑保存点（完结回滚到指定保存点）

• 检查MySQL数据库存储引擎装备

  SHOW VARIABLES LIKE '%storage_engine%';
  

1.2.1 怎么更改数据库表引擎

• 建表句子后边参加引擎赋值即可 ,指令举例如下 ,

CREATE TABLE t1(
	id INT ,
    name VARCHAR(20)
) ENGINE = MyISAM;

• 修正已有的表引擎 , 指令举例如下 ,

ALTER TABLE t1 ENGINE = InnoDB;

1.2.2 常用引擎及其特性比照

• 常见的存储引擎 :

  MyISAM存储引擎 : 拜访快,不支撑业务和外键。表结构保存在.frm文件中，表数据保存在.MYD文件中，索引保存在.MYI文件中。

  [root@linux-141 itcast]# ll
  -rw-r-----. 1 mysql mysql       8630 9月  10 16:01 t_account_myisam.frm
  -rw-r-----. 1 mysql mysql         52 9月  10 16:06 t_account_myisam.MYD
  -rw-r-----. 1 mysql mysql       2048 9月  10 17:56 t_account_myisam.MYI
  [root@linux-141 itcast]#
  

**innoDB存储引擎(**5.5版别开端默许) : 支撑业务 ,占用磁盘空间大 ,支撑并发操控。表结构保存在.frm文件中，假如是共享表空间，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可所以多个文件。假如是多表空间存储，每个表的数据和索引独自保存在 .ibd 中。

[root@linux-141 itcast]# ll
-rw-r-----. 1 mysql mysql       8630 9月  10 16:02 t_account_innodb.frm
-rw-r-----. 1 mysql mysql      98304 9月  14 15:50 t_account_innodb.ibd
[root@linux-141 itcast]#

MEMORY存储引擎 : 内存存储 , 速度快 ,不安全 ,适合小量快速拜访的数据。表结构保存在.frm中。

特性比照 :

特点	InnoDB	MyISAM	MEMORY	MERGE	NDB
存储束缚	64TB	有	有	没有	有
业务安全	==支撑==
锁机制	==行锁(适合高并发)==	==表锁==	表锁	表锁	行锁
B树索引	支撑	支撑	支撑	支撑	支撑
哈希索引	支撑
全文索引	支撑(5.6版别之后)	支撑
集群索引	支撑
数据索引	支撑	支撑	支撑
索引缓存	支撑	支撑	支撑	支撑	支撑
数据可压缩	支撑
空间运用	高	低	N/A	低	低
内存运用	高	低	中等	低	高
批量刺进速度	低	高	高	高	高
支撑外键	==支撑==

1.2.3 怎么挑选不同类型的引擎

在挑选存储引擎时，应该依据运用体系的特点挑选适宜的存储引擎。关于杂乱的运用体系，还能够依据实际状况挑选多种存储引擎进行组合。

以下是几种常用的存储引擎的运用环境。

• InnoDB : 是Mysql的默许存储引擎，用于业务处理运用程序，支撑外键。假如运用对业务的完好性有比较高的要求，在并发条件下要求数据的共同性，数据操作除了刺进和查询以外，还包括更新、删去操作，那么InnoDB存储引擎是比较适宜的挑选。InnoDB存储引擎除了有用的降低因为删去和更新导致的确定， 还能够确保业务的完好提交和回滚，关于相似于计费体系或许财务体系等对数据准确性要求比较高的体系，InnoDB是最适宜的挑选。
• MyISAM ： 假如运用是以读操作和刺进操作为主，只需很少的更新和删去操作，而且对业务的完好性、并发性要求不是很高，那么挑选这个存储引擎是非常适宜的。
• MEMORY：将一切数据保存在RAM中，在需求快速定位记载和其他相似数据环境下，能够供给极快的拜访。MEMORY的缺陷便是对表的巨细有束缚，太大的表无法缓存在内存中，其次是要确保表的数据能够康复，数据库异常终止后表中的数据是能够康复的。MEMORY表一般用于更新不太频频的小表，用以快速得到拜访成果。

1.3 SQL的履行流程是什么样的

• 客户端发送一条查询给服务器。
• 服务器先检查查询缓存，假如命中了缓存，则立刻回来存储在缓存中的成果。不然进入下一阶段。
• 服务器端进行SQL解析、预处理，再由优化器生成对应的履行计划。
• MySQL依据优化器生成的履行计划，再调用存储引擎的API来履行查询。
• 将成果回来给客户端。

2 MySQL存储引擎调优

2.1 MySQL服务器硬件优化

tips

硬件（cpu、内存等）相关

了解即可

关于进步硬件设备功能：

例如挑选尽量高频率的**内存（**频率不能高于主板的支撑）、进步网络带宽、运用SSD高速磁盘、进步CPU功能等。

CPU的挑选:

• 关于数据库并发比较高的场景，CPU的数量比频率重要。
• 关于CPU密集型场景和频频履行杂乱SQL的场景，CPU的频率越高越好

磁盘的挑选

影响数据库最大的功能问题便是磁盘I/O 为进步数据库的IOPS功能，可运用SSD或PCIE-SSD高速磁盘设备

磁盘IO的优化

能够用RAID来进行优化

常用RAID（磁盘阵列）等级：

RAID0：也称为条带，便是把多个磁盘链接成一个硬盘运用，这个等级IO最好 RAID1：也称为镜像，要求至少有两个磁盘，每组磁盘存储的数据相同 RAID5：也是把多个（最少3个）硬盘合并成一个逻辑盘运用，数据读写时会树立奇偶校验信息，而且奇偶校验信息和相对应的数据别离存储在不同的磁盘上。当RAID5的一个磁盘数据发生损坏后，运用剩余的数据和呼应的奇偶校验信息去康复被损坏的数据

RAID1+0（主张运用）：便是RAID0和RAID1的组合。一起具备两个等级的优缺点，一般主张数据库运用这个等级。

2.2 MySQL数据库装备优化

tips：

以下为出产环境中最常用的DB参数装备

• 表明缓冲池字节巨细，大的缓冲池能够削减磁盘IO次数。 innodb_buffer_pool_size = 推荐值为物理内存的50%~80%。

• 用来操控redo log buffer改写到磁盘的策略。 innodb_flush_log_at_trx_commit=1

  select @@innodb_flush_log_at_trx_commit;
  

  0 : 提交业务的时分，不当即把 redo log buffer 里的数据刷入磁盘文件中，而是依靠 InnoDB 的主线程每秒履行一次改写到磁盘。此刻或许你提交业务了，成果 mysql 宕机了，然后此刻内存里的数据悉数丢失。
  1 : 提交业务的时分，当即把 redo log buffer 里的数据刷入磁盘文件中，只需业务提交成功，那么数据就必然在磁盘里了。
  2 : 提交业务的时分，把 redo log buffer日志写入磁盘文件对应的体系缓存，而不是直接进入磁盘文件，这时或许1秒后才会把体系缓存里的数据写入到磁盘文件。
  

• 每提交1次业务就同步写到磁盘中，能够设置为1。 sync_binlog=1

  0：默许值。业务提交后，将二进制日志从缓冲写入操作体系缓冲，可是不进行改写操作（fsync()），此刻仅仅写入了操作体系缓冲而没有改写到磁盘，若操作体系宕机则会丢失部分二进制日志。
  1：业务提交后，将二进制文件写入磁盘并当即履行改写操作，恰当所以同步写入磁盘，不经过操作体系的缓存。
  N：每写N次操作体系缓冲就履行一次改写操作。
  

• 脏页占innodb_buffer_pool_size的比例，触发刷脏页到磁盘。 推荐值为25%~50%。 innodb_max_dirty_pages_pct=30

  脏页：内存数据页和磁盘数据页上的内容不共同
  

• 后台进程最大IO功能指标。 默许200，假如SSD，调整为5000~20000

  PCIE-SSD可调整为5w左右

  默许：innodb_io_capacity=200

• 指定innodb共享表空间文件的巨细。 innodb_data_file_path ＝ ibdata:1G:autoextend：默许10M，

  一般设置为1GB

• 慢查询日志的阈值设置，单位秒。 long_query_time=0.3

  合理设置区间0.1s~0.5s，

• mysql仿制的办法，row为MySQL8.0的默许办法。 binlog_format=row

  主张binlog的记载格局为row形式

  STATEMENT形式：每一条会修正数据的sql句子都会记载到binlog中。
  ROW形式：不记载每条sql句子的上下文信息，仅需记载哪条数据被修正了，修正成什么样了。
  MIXED形式：以上两种形式的混合运用。
  

• 降低interactive_timeout、wait_timeout的值。

  交互等待时刻和非交互等待时刻，值共同，主张300~500s，默许8小时

  在用mysql客户端对数据库进行操作时，翻开终端窗口，假如一段时刻（8小时）没有操作，再次操作时，会报错：当时的衔接已经断开，需求从头树立衔接
  

• 数据库最大衔接数max_connections=200

• 过大，实例康复时刻长；过小，形成日志切换频频。 innodb_log_file_size=默许

  redo log空间巨细

• 全量日志主张封闭。 默许封闭general_log=0

  敞开 general log 将一切到达MySQL Server的SQL句子记载下来，general_Log文件就会发生很大的文件，主张封闭
  

2.3 Mysql中查询缓存优化

tips：

在MySQL 8.0之后废弃这个功用

原理：杂乱、实用性不高

作为了解即可

1） 查询缓存概述

敞开Mysql的查询缓存，当履行彻底相同的SQL句子的时分，服务器就会直接从缓存中读取成果，当数据被修正，之前的缓存会失效，修正比较频频的表不适合做查询缓存。

2） 操作流程

回顾

1. 客户端发送一条查询给服务器；
2. 服务器先会检查查询缓存，假如命中了缓存，则当即回来存储在缓存中的成果。不然进入下一阶段；
3. 服务器端进行SQL解析、预处理，再由优化器生成对应的履行计划；
4. MySQL依据优化器生成的履行计划，调用存储引擎的API来履行查询；
5. 将成果回来给客户端。

3） 查询缓存装备

1. 检查当时的MySQL数据库是否支撑查询缓存：

   SHOW VARIABLES LIKE 'have_query_cache';
   

2. 检查当时MySQL是否敞开了查询缓存 ：

SHOW VARIABLES LIKE 'query_cache_type';

3. 检查查询缓存的占用巨细 ：

SHOW VARIABLES LIKE 'query_cache_size';

4. 检查查询缓存的状况变量：

SHOW STATUS LIKE 'Qcache%';

各个变量的意义如下：

参数	意义
Qcache_free_blocks	查询缓存中的可用内存块数
Qcache_free_memory	查询缓存的可用内存量
Qcache_hits	查询缓存命中数
Qcache_inserts	添加到查询缓存的查询数
Qcache_lowmen_prunes	因为内存不足而从查询缓存中删去的查询数
Qcache_not_cached	非缓存查询的数量（因为 query_cache_type 设置而无法缓存或未缓存）
Qcache_queries_in_cache	查询缓存中注册的查询数
Qcache_total_blocks	查询缓存中的块总数

4） 敞开查询缓存

MySQL的查询缓存默许是封闭的，需求手动装备参数 query_cache_type ， 来敞开查询缓存。query_cache_type 该参数的可取值有三个 ：

值	意义
OFF 或 0	查询缓存功用封闭
ON 或 1	查询缓存功用翻开，SELECT的成果符合缓存条件即会缓存，不然，不予缓存，显式指定 SQL_NO_CACHE，不予缓存
DEMAND 或 2	查询缓存功用按需进行，显式指定 SQL_CACHE 的SELECT句子才会缓存；其它均不予缓存

在 my.cnf 装备中，添加以下装备 ：

#敞开查询缓存
query_cache_type=1

装备完毕之后，重启服务既可收效 ；

然后就能够在指令行履行SQL句子进行验证 ，履行一条比较耗时的SQL句子，然后再多履行几回，检查后边几回的履行时刻；获取经过检查查询缓存的缓存命中数，来判定是否走查询缓存。

-- 履行SQL句子进行验证 查询缓存
SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';
-- 将SELECT修正为小写，发现缓存失效
SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';

5） 查询缓存SELECT选项

能够在SELECT句子中指定两个与查询缓存相关的选项 ：

SQL_CACHE : 假如查询成果是可缓存的，而且 query_cache_type 体系变量的值为ON或 DEMAND ，则缓存查询成果 。

SQL_NO_CACHE : 服务器不运用查询缓存。它既不检查查询缓存，也不检查成果是否已缓存，也不缓存查询成果。

比如：

SELECT SQL_CACHE id, name FROM xxx;
SELECT SQL_NO_CACHE id, name FROM xxx;

​

6） 查询缓存失效的状况

tips

需求留意的问题

1） SQL 句子不共同的状况， 要想命中查询缓存，查询的SQL句子必须共同。

SQL1 : select count(*) from xxx;
SQL2 : Select count(*) from xxx;

2） 当查询句子中有一些不确定的值，则不会缓存。如 ： now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() 。

SQL1 : select * from xxx where updatetime < now() limit 1;
SQL2 : select user();
SQL3 : select database();

3） 不运用任何表查询句子。

select 'A';

4） 查询 mysql， information_schema或 performance_schema 数据库中的表时，不会走查询缓存。

select * from information_schema.engines;

5） 在存储的函数，触发器或事情的主体内履行的查询。

6） 假如表更改，则运用该表的一切高速缓存查询都将变为无效并从高速缓存中删去。这包括运用MERGE映射到已更改表的表的查询。一个表能够被许多类型的句子，如被改变 INSERT， UPDATE， DELETE， TRUNCATE TABLE， ALTER TABLE， DROP TABLE，或 DROP DATABASE 。

将查询缓存封闭，因为后边还需求进行索引的验证，所以不期望走查询缓存

[root@linux-141 itcast]# vi /etc/my.cnf
[root@linux-141 itcast]# service mysql restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!

2.4. Mysql内存管理及优化

1）内存优化准则

1） 将尽量多的内存分配给MySQL做缓存，但要给操作体系和其他程序预留满意内存。

2） MyISAM 存储引擎的数据文件读取依赖于操作体系自身的IO缓存，因而，假如有MyISAM表，就要预留更多的内存给操作体系做IO缓存。

3） 排序区、衔接区等缓存是分配给每个数据库会话（session）专用的，其默许值的设置要依据最大衔接数合理分配，假如设置太大，不但糟蹋资源，而且在并发衔接较高时会导致物理内存耗尽。

2） MyISAM 内存优化

MyISAM 存储引擎运用 key_buffer 缓存索引块，加快myisam索引的读写速度。关于myisam表的数据块，mysql没有特别的缓存机制，彻底依赖于操作体系的IO缓存。

key_buffer_size

key_buffer_size决定MyISAM索引块缓存区的巨细，直接影响到MyISAM表的存取功率。能够在MySQL参数文件中设置key_buffer_size的值，关于一般MyISAM数据库，主张至少将1/4可用内存分配给key_buffer_size。

在my.cnf 中做如下装备：

key_buffer_size=512M

read_buffer_size

假如需求常常次序扫描MyISAM 表，能够经过增大read_buffer_size的值来改善功能。但需求留意的是read_buffer_size是每个session独占的，假如默许值设置太大，就会形成内存糟蹋。

read_rnd_buffer_size

关于需求做排序的MyISAM 表的查询，如带有order by子句的sql，恰当添加 read_rnd_buffer_size 的值，能够改善此类的sql功能。

但需求留意的是 read_rnd_buffer_size 是每个session独占的，假如默许值设置太大，就会形成内存糟蹋。

3） InnoDB 内存优化

innodb用一块内存区做IO缓存池，该缓存池不仅用来缓存innodb的索引块，而且也用来缓存innodb的数据块。

innodb_buffer_pool_size

该变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区巨细。在确保操作体系及其他程序有满意内存可用的状况下，innodb_buffer_pool_size 的值越大，缓存命中率越高，拜访InnoDB表需求的磁盘I/O 就越少，功能也就越高。

innodb_buffer_pool_size=512M

innodb_log_buffer_size

决定了innodb重做日志缓存的巨细，关于或许发生许多更新记载的大业务，添加innodb_log_buffer_size的巨细，能够防止innodb在业务提交前就履行不必要的日志写入磁盘操作。

innodb_log_buffer_size=10M

2.5. Mysql并发参数调整

从完结上来说，MySQL Server 是多线程结构，包括后台线程和客户服务线程。多线程能够有用运用服务器资源，进步数据库的并发功能。在Mysql中，操控并发衔接和线程的首要参数包括 max_connections、back_log、thread_cache_size、table_open_cahce。

1） max_connections

最大可支撑的衔接数

选用max_connections 操控允许衔接到MySQL数据库的最大数量，默许值是 151。假如状况变量 connection_errors_max_connections 不为零，而且一向增长，则阐明不断有衔接恳求因数据库衔接数已到达允许最大值而失利，这时能够考虑增大max_connections 的值。

Mysql 最大可支撑的衔接数，取决于许多要素，包括给定操作体系渠道的线程库的质量、内存巨细、每个衔接的负荷、CPU的处理速度，期望的呼应时刻等。在Linux 渠道下，功能好的服务器，支撑 500-1000 个衔接不是难事，需求依据服务器功能进行评价设定。

2） back_log

积压恳求栈巨细

back_log 参数操控MySQL监听TCP端口时设置的积压恳求栈巨细。假如MySql的衔接数到达max_connections时，新来的恳求将会被存在仓库中，以等待某一衔接开释资源，该仓库的数量即back_log，假如等待衔接的数量超越back_log，将不被授予衔接资源，将会报错。5.6.6 版别之前默许值为 50 ， 之后的版别默许为 50 + （max_connections / 5）， 但最大不超越900。

假如需求数据库在较短的时刻内处理许多衔接恳求， 能够考虑恰当增大back_log 的值。

3） table_open_cache

履行线程可翻开表缓存个数

该参数用来操控一切SQL句子履行线程可翻开表缓存的数量， 而在履行SQL句子时，每一个SQL履行线程至少要翻开 1 个表缓存。该参数的值应该依据设置的最大衔接数 max_connections 以及每个衔接履行相关查询中触及的表的最大数量来设定 ：

max_connections x N ；

4） thread_cache_size

缓存客户服务线程的数量

为了加快衔接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用，经过参数 thread_cache_size 可操控 MySQL 缓存客户服务线程的数量。

5）lock_wait_timeout

innodb_lock_wait_timeout

业务等待行锁的时刻

该参数是用来设置InnoDB 业务等待行锁的时刻，默许值是50ms ， 能够依据需求进行动态设置。关于需求快速反馈的业务体系来说，能够将行锁的等待时刻调小，以防止业务长时刻挂起； 关于后台运转的批量处理程序来说， 能够将行锁的等待时刻调大， 以防止发生大的回滚操作。

3 常用慢查询剖析东西

导言
在日常的业务开发中
MySQL 呈现慢查询是很常见的
大部分状况下会分为两种状况
1、业务增长太快
2、要么便是SQL 写的太xx了
所以
对慢查询 SQL 进行剖析和优化很重要
其间 mysqldumpslow 是 MySQL 服务自带的一款很好的剖析调优东西

3.1 调优东西mysqldumpslow

3.1.1 调优东西常用设置

1、什么是MySQL 慢查询日志

MySQL供给的一种慢查询日志记载，用来记载在MySQL查询中呼应时刻超越阀值的记载 具体指运转时刻超越long_query_time值的SQL，则会被记载到慢查询日志中

2、怎么检查慢查询设置状况

慢查询的时刻阈值设置

show variables like '%slow_query_log%';

解说

• slow_query_log //是否敞开，默许封闭，主张调优时才敞开
• slow_query_log_file //慢查询日志存放途径

3、怎么敞开慢查询日志记载

1) 指令敞开

set global slow_query_log =1; //只对当时会话收效，重启失效

履行成功

再次履行

show variables like '%slow_query_log%';

先封闭客户端衔接，再进行从头衔接，即可看到设置收效

发现敞开了mysqldumpslow调优东西

mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------------+
| Variable_name       | Value                                     |
+---------------------+-------------------------------------------+
| slow_query_log      | ON                                        |
| slow_query_log_file | /opt/mysql-5.7.28/data/linux-141-slow.log |
+---------------------+-------------------------------------------+
2 rows in set (0.02 sec)
mysql> 

2）装备文件敞开

vim my.cnf
在[mysqld]下添加：
slow_query_log = 1
slow_query_log_file = /opt/mysql-5.7.28/data/linux-141-slow.log
重启MySQL服务

修正而且重启后

发现敞开了mysqldumpslow调优东西

mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------------+
| Variable_name       | Value                                     |
+---------------------+-------------------------------------------+
| slow_query_log      | ON                                        |
| slow_query_log_file | /opt/mysql-5.7.28/data/linux-141-slow.log |
+---------------------+-------------------------------------------+
2 rows in set (0.02 sec)
mysql> 

3）哪些 SQL 会记载到慢查询日志

-- 检查阀值（大于），默许10s
show variables like 'long_query_time%';

默许值是10秒

4）怎么设置查询阀值

• 指令设置

-- 设置慢查询阀值
set global long_query_time = 1;

补白：另外开一个session或从头衔接 ，才会看到改变

履行成功发发现慢sql的时刻变成了1秒

装备文件设置

vim my.cnf
[mysqld]
long_query_time = 1
log_output = FILE
重启MySQL服务

履行成功发发现慢sql的时刻变成了1秒

5）怎么把未运用索引的 SQL 记载写入慢查询日志

-- 检查设置，默许封闭
show variables like 'log_queries_not_using_indexes';

咱们发现，未运用索引的sql默许是不记载到慢查询日志的

敞开装备

set global log_queries_not_using_indexes = on;

履行如下

6）模拟数据

-- 睡觉2s再履行
select sleep(2);
-- 检查慢查询条数
show global status like '%Slow_queries%';

咱们发现，每履行一次select sleep(2)，之后，再经过show global status …指令，他的值就会+1

3.1.2 调优东西常用指令

语法格局

mysqldumpslow [ OPTS... ] [ LOGS... ] //指令行格局

常用到的格局组合

-s 表明依照何种办法排序
    c 拜访次数
    l 确定时刻
    r 回来记载
    t 查询时刻
    al 均匀确定时刻
    ar 均匀回来记载数
    at  均匀查询时刻
-t 回来前面多少条数据
-g 后边调配一个正则匹配形式，巨细写不灵敏

1、拿到慢日志途径

show variables like '%slow_query_log%';

日志途径为：/opt/mysql-5.7.28/data/linux-141-slow.log

检查日志

[root@linux-141 mysql-5.7.28]# cat /opt/mysql-5.7.28/data/linux-141-slow.log
/opt/mysql-5.7.28/bin/mysqld, Version: 5.7.28-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2021-09-15T01:40:31.342430Z
# User@Host: root[root] @  [192.168.36.1]  Id:     2
# Query_time: 2.000863  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use itcast;
SET timestamp=1631670031;
-- 睡觉2s再履行
select sleep(2);
[root@linux-141 mysql-5.7.28]#

2、得到拜访次数最多的10条SQL

[root@linux-141 mysql-5.7.28]# ./bin/mysqldumpslow -s r -t  10 /opt/mysql-5.7.28/data/linux-141-slow.log
-bash: ./bin/mysqldumpslow: /usr/bin/perl: 坏的解说器: 没有那个文件或目录
[root@linux-141 mysql-5.7.28]# yum -y install perl perl-devel
[root@linux-141 mysql-5.7.28]# ./bin/mysqldumpslow -s r -t  10 /opt/mysql-5.7.28/data/linux-141-slow.log

3、依照时刻排序的前10条里面含有左衔接的SQL

[root@linux-141 mysql-5.7.28]# ./bin/mysqldumpslow -s t -t 10 -g "left join"  /opt/mysql-5.7.28/data/linux-141-slow.log
Reading mysql slow query log from /opt/mysql-5.7.28/data/linux-141-slow.log
Died at ./bin/mysqldumpslow line 167, <> chunk 28.
[root@linux-141 mysql-5.7.28]#

3.1.3 慢日志文件剖析

1、检查慢查询日志

[root@linux-141 mysql-5.7.28]# cat /opt/mysql-5.7.28/data/linux-141-slow.log
/opt/mysql-5.7.28/bin/mysqld, Version: 5.7.28-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2021-09-15T01:40:31.342430Z
# User@Host: root[root] @  [192.168.36.1]  Id:     2
# Query_time: 2.000863  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use itcast;
SET timestamp=1631670031;
-- 睡觉2s再履行
select sleep(2);
# Time: 2021-09-15T01:50:32.130305Z
# User@Host: root[root] @  [192.168.36.1]  Id:     2
# Query_time: 3.001904  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1631670632;
select sleep(3);
# Time: 2021-09-15T01:50:55.064372Z
# User@Host: root[root] @  [192.168.36.1]  Id:     2
# Query_time: 4.008082  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1631670655;
select sleep(4);
# Time: 2021-09-15T01:51:01.343463Z
# User@Host: root[root] @  [192.168.36.1]  Id:     2
# Query_time: 5.007035  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1631670661;
select sleep(5);
# Time: 2021-09-15T01:51:07.737834Z                                     	###### 履行SQL时刻
# User@Host: root[root] @  [192.168.36.1]  Id:     2						###### 履行SQL的主机信息
# Query_time: 6.009129  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0	###### SQL的履行信息
SET timestamp=1631670667;													###### SQL履行时刻
select sleep(6);															###### SQL内容
[root@linux-141 mysql-5.7.28]#

特点解说

# Time: 2021-09-15T01:51:07.737834Z                                     	###### 履行SQL时刻
# User@Host: root[root] @  [192.168.36.1]  Id:     2						###### 履行SQL的主机信息
# Query_time: 6.009129  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0	###### SQL的履行信息
SET timestamp=1631670667;													###### SQL履行时刻
select sleep(6);															###### SQL内容

3.2 调优东西show profile

tips：

show profile，它也是调优东西

也是MySQL服务自带的剖析调优东西

不过这款更高档

比较挨近底层硬件参数的调优。

简介：

show profile是MySQL服务自带更高档的剖析调优东西

比较挨近底层硬件参数的调优

1、检查show profile设置

-- 默许封闭，保存近15次的运转成果
show variables like 'profiling%';

经过上面咱们发现，show profile东西默许是封闭状况，15表明保存了近15次的运转成果。

2、敞开调优东西

履行下面的指令敞开

SET profiling = ON;

再次检查状况

show variables like 'profiling%';

3、检查最近15次的运转成果

-- 检查最近15次的运转成果
show profiles;
-- 能够显现警告和报错的信息
show warnings;
-- 慢查询句子
SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';

显现最近15次的运转成果

4、确诊运转的SQL

接下来，咱们一起确诊一下query id为23的慢查询

-- 语法
SHOW PROFILE cpu,block io FOR QUERY query id;
-- 示例
SHOW PROFILE cpu,block io FOR QUERY 129;

开端履行

解说：
经过Status一列，能够看到整条SQL的运转过程
1. starting //开端
2. checking permissions //检查权限
3. Opening tables //翻开数据表
4. init //初始化
5. System lock //锁机制
6. optimizing //优化器
7. statistics //剖析语法树
8. prepareing //预预备
9. executing //引擎履行开端
10. end //引擎履行完毕
11. query end //查询完毕
12. closing tables //开释数据表
13. freeing items //开释内存
14. cleaning up //彻底清理

检查类型选项
SHOW PROFILE...后边的列，即：SHOW PROFILE ALL, BLOCK IO, ... FOR QUERY 209;
ALL //显现索引的开支信息
BLOCK IO //显现块IO相关开支
CONTEXT SWITCHES  //上下文切换相关开支
CPU //显现CPU相关开支信息
IPC //显现发送和接收相关开支信息
MEMORY //显现内存相关开支信息
PAGE FAULTS //显现页面错误相关开支信息
SOURCE //显现和source_function,source_file,source_line相关的开支信息
SWAPS //显现交换次数相关开支的信息

重要提示

如呈现以下一种或许几种状况，阐明SQL履行功能极端低下，亟需优化
* converting HEAP to MyISAM  //查询成果太大，内存都不行用了往磁盘上搬了
* Creating tmp table //创立暂时表：复制数据到暂时表，用完再删
* Copying to tmp table on disk //把内存中暂时表仿制到磁盘，风险
* locked //呈现死锁

4 怎么定位不合理的SQL

导言
在运用的开发过程中，因为初期数据量小，开发人员写 SQL 句子时更注重功用上的完结，可是当运用体系正式上线后，跟着出产数据量的急剧增长，许多SQL句子开端逐渐显露出功能问题，对出产的影响也越来越大，此刻这些有问题的SQL句子就成为整个体系功能的瓶颈，因而咱们必须要对它们进行优化，本章将详细介绍在MySQL中优化SQL句子的办法。
当面对一个有SQL功能问题的数据库时，咱们应该从何处下手来进行体系的剖析，使得能够赶快定位问题SQL并赶快处理问题。

4.1 怎么检查SQL履行频率

MySQL 客户端衔接成功后，经过

-- 服务器状况信息
show [session|global] status;

指令能够供给服务器状况信息。show [session|global] status 能够依据需求加上参数“session”或许“global”来显现 session 级（当时衔接）的核算成果和 global 级（自数据库前次启动至今）的核算成果。

假如不写，默许运用参数是“session”。

下面的指令显现了当时 session 中一切核算参数的值：

show status like 'Com_______';

show status like 'Innodb_rows_%';

Com_xxx 表明每个 xxx 句子履行的次数，咱们一般比较关心的是以下几个核算参数。

参数	意义
Com_select	履行 select 操作的次数，一次查询只累加 1。
Com_insert	履行 INSERT 操作的次数，关于批量刺进的 INSERT 操作，只累加一次。
Com_update	履行 UPDATE 操作的次数。
Com_delete	履行 DELETE 操作的次数。
Innodb_rows_read	select 查询回来的行数。
Innodb_rows_inserted	履行 INSERT 操作刺进的行数。
Innodb_rows_updated	履行 UPDATE 操作更新的行数。
Innodb_rows_deleted	履行 DELETE 操作删去的行数。
Connections	企图衔接 MySQL 服务器的次数。
Uptime	服务器作业时刻。
Slow_queries	慢查询的次数。

Com_*** : 这些参数关于一切存储引擎的表操作都会进行累计。

Innodb_*** : 这几个参数仅仅针对InnoDB 存储引擎的，累加的算法也略有不同。

4.2 怎么定位低功率SQL

以下两种办法：

• 慢查询日志（重要） : 经过慢查询日志定位那些履行功率较低的 SQL 句子，用–log-slow-queries[=file_name]选项启动时，mysqld 写一个包括一切履行时刻超越 long_query_time 秒的 SQL 句子的日志文件。

  tips：

  关于慢查询SQL怎么获取

  参看上个章节

• show processlist （重要） :

  慢查询日志在查询完毕以后才记载，所以在运用反映履行功率呈现问题的时分查询慢查询日志并不能定位问题。

  能够运用show processlist指令检查当时MySQL在进行的线程，包括线程的状况、是否锁表等，能够实时地检查 SQL 的履行状况，一起对一些锁表操作进行优化。

特点字段解说
1） id列，用户登录mysql时，体系分配的"connection_id"，能够运用函数connection_id()检查
2） user列，显现当时用户。假如不是root，这个指令就只显现用户权限规模的sql句子
3） host列，显现这个句子是从哪个ip的哪个端口上发的，能够用来跟踪呈现问题句子的用户
4） db列，显现这个进程现在衔接的是哪个数据库
5） command列，显现当时衔接的履行的指令，一般取值为休眠（sleep），查询（query），衔接（connect）等
6） time列，显现这个状况持续的时刻，单位是秒
7） state列，显现运用当时衔接的sql句子的状况，很重要的列。
	state描绘的是句子履行中的某一个状况。一个sql句子，以查询为例，或许需求经过copying to tmp table、sorting result、sending data等状况才干够完结
8） info列，显现这个sql句子，是判断问题句子的一个重要依据

4.3 运用explain剖析履行计划

-- explain 剖析履行计划
explain SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';

字段	意义
id	select查询的序列号，是一组数字，表明的是查询中履行select子句或许是操作表的次序。
select_type	表明 SELECT 的类型，常见的取值有 SIMPLE（简略表，即不运用表衔接或许子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或许后边的查询句子）、SUBQUERY（子查询中的第一个 SELECT）等
table	输出成果集的表
partitions	匹配的分区
type	表明表的衔接类型，功能由好到差的衔接类型为( system —> const —–> eq_ref ——> ref ——-> ref_or_null—-> index_merge —> index_subquery —–> range —–> index ——> all )
possible_keys	表明查询时，或许运用的索引
key	表明实际运用的索引
key_len	索引字段的长度
rows	扫描行的数量
filtered	按表条件过滤的行百分比
extra	履行状况的阐明和描绘

4.3.1 环境预备

CREATE TABLE `t_role` (
  `id` varchar(32) NOT NULL,
  `role_name` varchar(255) DEFAULT NULL,
  `role_code` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_user` (
  `id` varchar(32) NOT NULL,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `user_role` (
  `id` int(11) NOT NULL auto_increment ,
  `user_id` varchar(32) DEFAULT NULL,
  `role_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_role_user` (`role_id`,`user_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超级管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','体系管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into `t_user` (`id`, `username`, `password`, `name`) values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学生1');
insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学生2');
insert into `t_user` (`id`, `username`, `password`, `name`) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','教师1');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','学生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','教师','teacher','教师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教育管理员','teachmanager','教育管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超级管理员','super','超级管理员');
INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;

4.3.2 explain 之 id

id 字段是 select查询的序列号，是一组数字，表明的是查询中履行select子句或许是操作表的次序。

id 状况有三种 ：

1） id 相同表明加载表的次序是从上到下。

explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;

2） id 不同id值越大，优先级越高，越先被履行。

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))

3） id 有相同，也有不同，一起存在。id相同的能够以为是一组，从上往下次序履行；在一切的组中，id的值越大，优先级越高，越先履行。

EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = (select role.id from t_user, user_role role where role.id = 10) ;

4.3.3 explain 之 select_type

表明 SELECT 的类型，常见的取值，如下表所示：

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'));

select_type	意义
SIMPLE	简略的select查询，查询中不包括子查询或许UNION
PRIMARY	查询中若包括任何杂乱的子查询，最外层查询标记为该标识
SUBQUERY	在SELECT 或 WHERE 列表中包括了子查询
DERIVED	在FROM 列表中包括的子查询，被标记为 DERIVED（衍生） MYSQL会递归履行这些子查询，把成果放在暂时表中
UNION	若第二个SELECT呈现在UNION之后，则标记为UNION ； 若UNION包括在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED
UNION RESULT	从UNION表获取成果的SELECT

4.3.4 explain 之 table

展现这一行的数据是关于哪一张表的

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'));

4.3.5 explain 之 type

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'));

type 显现的是拜访类型，是较为重要的一个指标，可取值为：

type	意义
NULL	MySQL不拜访任何表，索引，直接回来成果
system	表只需一行记载(等于体系表)，这是const类型的特例，一般不会呈现
const	表明经过索引一次就找到了，const 用于比较primary key 或许 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转化为一个常量。const会将 “主键” 或 “仅有” 索引的一切部分与常量值进行比较
eq_ref	相似ref，差异在于运用的是仅有索引，运用主键的相关查询，相关查询出的记载只需一条。常见于主键或仅有索引扫描
ref	非仅有性索引扫描，回来匹配某个独自值的一切行。本质上也是一种索引拜访，回来一切匹配某个独自值的一切行（多个）
range	只检索给定回来的行，运用一个索引来挑选行。 where 之后呈现 between ， < , > , in 等操作。
index	index 与 ALL的差异为 index 类型仅仅遍历了索引树， 一般比ALL 快， ALL 是遍历数据文件。
all	将遍历全表以找到匹配的行

成果值从最好到最坏依次是：

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

一般来说， 咱们需求确保查询至少到达 range 等级， 最好到达ref 。

4.3.6 explain 之 key

possible_keys : 显现或许运用在这张表的索引， 一个或多个。
key ： 实际运用的索引， 假如为NULL， 则没有运用索引。
key_len : 表明索引中运用的字节数。len=3*n+2（n为索引字段的长度）
EXPLAIN select * from t_role where role_name = '超级管理员'; 
select 255 * 3 + 2; -- role_name VARCHAR(255)

4.3.7 explain 之 rows

扫描行的数量。

4.3.8 explain 之 extra

其他的额定的履行计划信息，在该列展现 。

EXPLAIN select u.username from t_user u order by u.username desc;

extra	意义
using filesort	阐明mysql会对数据运用一个外部的索引排序，而不是依照表内的索引次序进行读取， 称为 “文件排序”, 功率低。
using temporary	运用了暂时表保存中心成果，MySQL在对查询成果排序时运用暂时表。常见于 order by 和 group by； 功率低
using index	表明相应的select操作运用了掩盖索引， 防止拜访表的数据行， 功率不错。

5 怎么合理运用索引加快

tips：

500万条建表sql参照网盘sql脚本

[root@linux-141 bin]# ./mysql -u root -p itcast < product_list-5072825.sql

索引是数据库优化最常用也是最重要的手法之一, 经过索引一般能够协助用户处理大多数的MySQL的功能优化问题。

5.1 验证索引进步查询功率

在咱们预备的表结构product_list 中， 一共存储了 500多万记载；

mysql> select count(1) from product_list;
+----------+
| count(1) |
+----------+
|  5072825 |
+----------+
1 row in set (1.71 sec)
mysql> 

1） 依据ID查询

SELECT * FROM product_list WHERE id = 121926;

查询速度很快， 挨近0s ， 首要的原因是因为id为主键， 有索引；

2). 依据store_name进行精确查询

履行用时4分钟

SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';

检查SQL句子的履行计划 ：

explain SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';

处理计划 ， 针对store_name字段， 创立索引 ：

create index product_list_stname on product_list(store_name);

索引创立完结之后，再次进行查询 ：

SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';

经过explain ， 检查履行计划，履行SQL时运用了刚才创立的索引

-- 检查SQL句子的履行计划
explain SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';

5.2 索引的运用

5.2.1 预备环境

create table `tb_seller` (
	`sellerid` varchar (100),
	`name`  varchar (100) not null,
	`nickname` varchar (50),
	`password` varchar (60),
	`status`  varchar (1) not null,
	`address`  varchar (100) not null,
	`createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8; 
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

5.2.2 防止索引失效

组合索引(name,status,address)

1) 全值匹配

对索引中一切列都指定具体值。

-- 全值匹配
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
ken_len = 3 * N + 2;
-- name varchar(100)  	==302
-- status varchar(1)  	==5
-- address varchar(100) ==302

2) 最左前缀规律

假如索引了多列，要恪守最左前缀规律。指的是查询从索引的最左前列开端，而且不跳过索引中的列。

匹配最左前缀规律，走索引：

explain select * from tb_seller  where name='小米科技';

违背最左前缀规律 ， 索引失效：

explain select * from tb_seller  where status='1';
explain select * from tb_seller  where status='1'  and  address='北京市';

假如符合最左规律，可是呈现跳跃某一列，只需最左列索引收效：

explain select * from tb_seller  where name='小米科技'  and  address='北京市';

3) 规模查询右边的列

-- 运用规模查询的状况，右边的列失效
explain select * from tb_seller  where name='小米科技' and status='1'  and  address='北京市';
explain select * from tb_seller  where name='小米科技' and status>'1'  and  address='北京市';

依据前面的两个字段name ， status 查询是走索引的， 可是最终一个条件address 没有用到索引。

4) 制止列运算

-- 不要在索引列上进行运算操作， 索引将失效。
explain select * from tb_seller  where substring(name,3,2) ='科技';

5) 字符串不加单引号

形成索引失效。

-- 字符串不加单引号,形成索引失效。
explain select * from tb_seller  where name='科技' and status='0';
explain select * from tb_seller  where name='科技' and status=0;

因为，在查询时，没有对字符串加单引号，MySQL的查询优化器，会主动的进行类型转化，形成索引失效。

6) 尽量运用掩盖索引

防止select *

尽量运用掩盖索引（只拜访索引的查询（索引列彻底包括查询列）），削减select * 。

-- 尽量运用掩盖索引
explain select * from tb_seller  where name='科技' and status='0'  and  address='西安市';
explain select name from tb_seller  where name='科技' and status='0'  and  address='西安市';
explain select name ,status  from tb_seller  where name='科技' and status='0'  and  address='西安市';
explain select name ,status,address  from tb_seller  where name='科技' and status='0'  and  address='西安市';

假如查询列，超出索引列，也会降低功能。

explain select status,address ,password  from tb_seller  where name='科技' and status='0'  and  address='西安市';

TIP :
    using index ：运用掩盖索引的时分就会呈现
    using where：在查找运用索引的状况下，需求回表去查询所需的数据
    using index condition：查找运用了索引，可是需求回表查询数据
    using index ; using where：查找运用了索引，可是需求的数据都在索引列中能找到，所以不需求回表查询数据

7) 合理运用or条件

用or分割开的条件， 假如or前的条件中的列有索引，而后边的列中没有索引，那么触及的索引都不会被用到。

示例，name字段是索引列 ， 而createtime不是索引列，中心是or进行衔接是不走索引的 ：

explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00';

8) 合理运用like查询

以%开头的Like含糊查询，索引失效。

-- 假如仅仅是尾部含糊匹配，索引不会失效。假如是头部含糊匹配，索引失效。
explain select * from tb_seller  where name like '黑马程序员%';
explain select * from tb_seller  where name like '%黑马程序';
explain select * from tb_seller  where name like '%黑马程序员%';

处理计划 ： 经过掩盖索引来处理

explain select sellerid from tb_seller  where name like '%科技%';
explain select sellerid,name from tb_seller  where name like '%科技%';
explain select sellerid,name,status,address  from tb_seller  where name like '%科技%';

9) 合理评价索引履行

假如MySQL评价运用索引比全表更慢，则不运用索引。

-- 假如MySQL评价运用索引比全表更慢，则不运用索引。
create index idx_seller_addr on tb_seller(address);
explain select * from tb_seller  where address='北京市';
explain select * from tb_seller  where address='西安市';

10) is NULL和 is NOT NULL

有时索引失效。

-- is  NULL和 is NOT NULL 
explain select * from tb_seller  where name  is null;
explain select * from tb_seller  where name  is not null;

处理计划：把null值设置一个默许值

11) in和not in

in 走索引， not in 索引失效。

-- in 走索引， not in 索引失效。
explain select * from tb_seller  where sellerid in('oppo','xiaomi','sina');
explain select * from tb_seller  where sellerid not in  ('oppo','xiaomi','sina');

12) 单列索引和复合索引

尽量运用复合索引，而少运用单列索引 。

创立复合索引

create index idx_name_sta_address on tb_seller(name, status, address);
就恰当于创立了三个索引 ： 
	name
	name + status
	name + status + address

创立单列索引

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

数据库会挑选一个最优的索引（辨识度最高索引）来运用，并不会运用悉数索引 。

5.3 检查索引运用状况

show status like 'Handler_read%';
show global status like 'Handler_read%';	

mysql> show status like 'Handler_read%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 18      |
| Handler_read_key      | 19      |
| Handler_read_last     | 0       |
| Handler_read_next     | 5072825 |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 269     |
+-----------------------+---------+
7 rows in set (0.02 sec)
mysql> 

Handler_read_first：索引中第一条被读的次数。假如较高，表明服务器正履行许多全索引扫描（这个值越低越好）。
Handler_read_key：假如索引正在作业，这个值代表一个行被索引值读的次数，假如值越低，表明索引得到的功能改善不高，因为索引不常常运用（这个值越高越好）。
Handler_read_next ：依照键次序读下一行的恳求数。假如你用规模束缚或假如履行索引扫描来查询索引列，该值添加。
Handler_read_prev：依照键次序读前一行的恳求数。该读办法首要用于优化ORDER BY ... DESC。
Handler_read_rnd ：依据固定方位读一行的恳求数。假如你正履行许多查询并需求对成果进行排序该值较高。你或许运用了许多需求MySQL扫描整个表的查询或你的衔接没有正确运用键。这个值较高，意味着运转功率低，应该树立索引来补救。
Handler_read_rnd_next：在数据文件中读下一行的恳求数。假如你正进行许多的表扫描，该值较高。一般阐明你的表索引不正确或写入的查询没有运用索引。

6 SQL优化的一些主张

6.1 优化insert句子

当进行数据的insert操作的时分，能够考虑选用以下几种优化计划。

• 假如需求一起对一张表刺进许多行数据时，应该尽量运用多个值表的insert句子

  这种办法将大大的减缩客户端与数据库之间的衔接、封闭等消耗。

  使得功率比分开履行的单个insert句子快。

  示例， 原始办法为：

  insert into xxx values(1,'Tom');
  insert into xxx values(2,'Cat');
  insert into xxx values(3,'Jerry');
  

  优化后的计划为 ：

  insert into xxx values(1,'Tom'),(2,'Cat')，(3,'Jerry');
  

• 数据有序刺进

  insert into tb_test values(4,'Tim');
  insert into tb_test values(1,'Tom');
  insert into tb_test values(3,'Jerry');
  insert into tb_test values(5,'Rose');
  insert into tb_test values(2,'Cat');
  

  优化后

  insert into tb_test values(1,'Tom');
  insert into tb_test values(2,'Cat');
  insert into tb_test values(3,'Jerry');
  insert into tb_test values(4,'Tim');
  insert into tb_test values(5,'Rose');
  

6.2 优化order by句子

6.2.1 环境预备

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(3) NOT NULL,
  `salary` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');
create index idx_emp_age_salary on emp(age,salary);

6.2.2 两种排序办法

1). 第一种是经过对回来数据进行排序，也便是一般说的 filesort 排序

tips

不是经过索引直接回来排序成果的排序都叫 FileSort 排序。

explain  select  *  from emp  order by  age  desc;
explain  select  *  from emp  order by  age  asc;

2). 第二种经过有序索引次序扫描直接回来有序数据，这种状况即为 using index，

不需求额定排序，操作功率高。

explain  select  id from emp  order by  age  asc;
explain  select  id,age  from emp  order by  age  asc;
explain  select  id,age,salary  from emp  order by  age  asc;

多字段排序

-- 多字段排序
explain  select  id,age,salary  from emp  order by  age, salary;
explain  select  id,age,salary  from emp  order by  age desc, salary desc;
explain  select  id,age,salary  from emp  order by  salary desc, age desc;
explain  select  id,age,salary  from emp  order by  age desc, salary asc ;

总结：

了解了MySQL的排序办法，优化方针就明晰了：

尽量削减额定的排序，经过索引直接回来有序数据。

where 条件和Order by 运用相同的索引，而且Order By 的次序和索引次序相同， 而且Order by 的字段都是升序，或许都是降序。不然必定需求额定的操作，这样就会呈现FileSort。

6.2.3 Filesort 的优化原理

tips

了解即可

经过创立适宜的索引，能够削减 Filesort 的呈现，可是在某些状况下，条件束缚不能让Filesort消失，那就需求加快 Filesort的排序操作。关于Filesort ， MySQL 有两种排序算法：

1） 两次扫描算法 ：MySQL4.1 之前，运用该办法排序。首先依据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，假如sort buffer不行，则在暂时表 temporary table 中存储排序成果。完结排序之后，再依据行指针回表读取记载，该操作或许会导致许多随机I/O操作。

2）一次扫描算法：一次性取出满意条件的一切字段，然后在排序区 sort buffer 中排序后直接输出成果集。排序时内存开支较大，可是排序功率比两次扫描算法要高。

MySQL 经过比较体系变量 max_length_for_sort_data 的巨细和Query句子取出的字段总巨细， 来判定运用哪种排序算法，假如max_length_for_sort_data 更大，那么运用第二种优化之后的算法；不然运用第一种。

能够恰当进步 sort_buffer_size 和 max_length_for_sort_data 体系变量，来增大排序区的巨细，进步排序的功率。

show  variables like 'max_length_for_sort_data';
show  variables  like 'sort_buffer_size';

6.3 优化group by 句子

tips

连续运用6.2 优化order by句子中创立的表emp

创立索引 （预备作业）：

create index idx_emp_age_salary on emp(age,salary);

因为GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 首要仅仅多了排序之后的分组操作。

当然，假如在分组的时分还运用了其他的一些聚合函数，那么还需求一些聚合函数的核算。所以，在GROUP BY 的完结过程中，与 ORDER BY 一样也能够运用到索引。

假如查询包括 group by 可是用户想要防止排序成果的消耗， 则能够履行order by null 制止排序。如下 ：

drop index idx_emp_age_salary on emp;
explain select age,count(*) from emp group by age;

优化后

explain select age,count(*) from emp group by age order by null;

从上面的比如能够看出，第一个SQL句子需求进行”filesort”，而第二个SQL因为order by null 不需求进行 “filesort”， 而上文提过Filesort往往非常消耗时刻。

6.4 优化嵌套查询

tips

连续运用4.3 运用explain剖析履行计划中创立的表

Mysql4.1版别之后，开端支撑SQL的子查询。这个技能能够运用SELECT句子来创立一个单列的查询成果，然后把这个成果作为过滤条件用在另一个查询中。运用子查询能够一次性的完结许多逻辑上需求多个步骤才干完结的SQL操作，一起也能够防止业务或许表锁死，而且写起来也很容易。可是，有些状况下，子查询是能够被更高效的衔接（JOIN）代替。

示例 ，查找有角色的一切的用户信息 :

explain select * from t_user where id in (select user_id from user_role );

履行计划为 :

优化后 :

explain select * from t_user u , user_role ur where u.id = ur.user_id;

衔接(Join)查询之所以更有用率一些 ，是因为MySQL不需求在内存中创立暂时表来完结这个逻辑上需求两个步骤的查询作业。

6.5 运用SQL提示

tips：

连续运用5.2.1 预备环境中创立的表

SQL提示，是优化数据库的一个重要手法，简略来说，便是在SQL句子中参加一些人为的提示来到达优化操作的目的。

6.5.1 USE INDEX

在查询句子中表名的后边，添加 use index 来供给期望MySQL去参考的索引列表，就能够让MySQL不再考虑其他可用的索引。

create index idx_seller_name on tb_seller(name);
explain select  *  from tb_seller  where name='小米科技'
explain select  *  from tb_seller use index(idx_seller_name)  where name='小米科技'

6.5.2 IGNORE INDEX

假如用户仅仅单纯的想让MySQL忽略一个或许多个索引，则能够运用 ignore index 作为 hint 。

explain select * from tb_seller ignore index(idx_seller_name_sta_addr) where name = '小米科技';

6.5.3 FORCE INDEX

为强制MySQL运用一个特定的索引，可在查询中运用 force index 作为hint 。

create index idx_seller_address on tb_seller(address);
explain select * from tb_seller force index(idx_seller_address) where address = '北京市';

至此，MySQL数据库调优就干完了，假如大家喜爱的话，欢迎给我点个赞，支撑一会儿~~~，感谢~~