前言
观看笔记：https://www.bilibili.com/video/BV1fJ41127Rj?from=search&seid=13531213796559959972
本课程作为MySQL高级课程，主要讲解了：
- MySQL中的视图/存储过程/触发器/索引等对象的使用
- 常见的SQL语句优化的技巧
- 应用优化
- 数据库优化
- 数据库日志
等方面的知识，并通过综合案例，对课程中的知识进行一个整合应用。
本课程旨在通过MySQl高级部分内容，可以在满足现有业务需求基础上，对MySQL底层的体系结构，及底层的优化有一个深入的理解，对系统的整体性能进行提升。
今日内容
Mysql高级-day02
1） MySql的体系结构概览（MySQL内部原理，内部执行流程有一个认识）
2） 存储引擎（InnoDB以及MyISAM）
3） 优化SQL步骤（从哪些方面以及参考指标）
4） 索引的使用（在SQL优化中如何使用）
5） SQL优化
体系结构
MySQL的体系结构概览
MySQL的体系结构图和在进行Web开发的三层架构是相类似的；
Web开发时的三层架构：
（1） 前端发起请求先到达controller；
（2） controller调service；service调用dao;
（3） dao再去访问数据库；
mysql的体系结构当中，首先最上面的部分Connectors实际上代表的就相当于是客户端；
在客户端连接当中Natice C API，JDBC，ODBC,.NET，PHP,Perl，Python，Ruby，Cobol这些个；
JDBC就很熟悉了；在Java程序当中可以通过JDBC来进行操作mysql；
所以最上面部分即mysql连接的客户端通过JDBC来进行操作；
然后紧接着中层即MySQL Server；
MySQL Server当中最上面一层即Connection Pool连接池；
在连接池当中有很多的组件模块Authentication、Thread Reuse、Connection Limits、Check Memory、Caches；
也就是客户端发起一个连接；
这个连接就会到MySQL Server的这个Connection Pool连接池当中来进行获取一个连接 来 执行 对应的请求；
当然，在MySQL Server的Connection Pool进行获取连接的时候还需要进行认证、连接最大数、缓存等相关操作；
此为MySQL Server的第一层连接层；
连接层主要负责的就是客户端发起一个请求，
MySQL Server接收到这个请求之后，开启一段线程进行与客户端请求相关的操作；
这是连接层；
MySQL Server当中的第二层即Management Services & Utillties、SQL Interface、Parser、Optimizer、Caches & Buffers 这一部分；
MySQL Server第二层当中有很多的组件；
其中第一个（第一个模块）即MySQL的管理服务以及一些工具；管理层Management Services当中可以进行数据的备份与恢复以及集群安全以及系统配置相关的内容（Backup & Recovery，Security，Replication，Cluster，Administrator，Configuration，Migration & Metadata）
第二层的第二个模块：SQL Interface即SQL的一个接口，在此当中可以去进行执行或者是去封装DML、DDL语句以及存储过程、存储函数、视图、触发器等等相关数据库对象（DML、DDL、Stored Procedures，Views ，Triggers ，etc.）
第二层的第三个模块即：Parser，Parser就是一个解析器，这个解析器解析的是客户端发起的一个请求，客户端发起的这个请求就是SQL语句，SQL语句需要经过这个解析器Parser的解析，解析之后并且在这一块进行相关的过滤（Query Translation,Object Privilege）
解析完成之后，在MySQL的内部还需要几个过程；
第二层的第四个模块：Optimizer，这个过程实际上称作优化器；即在解析完成了前端请求过来的SQL语句并进行相关的过滤之后，MySQL内部还须要通过其内部的一个优化器，对前端请求来的SQL语句解析并过滤后进行一个优化处理；MySQL内部会按照其内部自己的一个优化体系来进行优化（Access Paths，Statistics）
当优化完成之后，接下来还需要进行缓存的一个操作：
第二程的第五个模块：Caches & Buffers，即查询在缓存当中有没有要查询的数据；如果存在有对应的树据信息，那么这个时候可能就直接返回给前端客户端了；（Global and Engine ，Specific Caches & Buffers）
所以第二层当中的这五个模块就是MySQL的服务层操作；即将前端请求的SQL语句进行封装起来并且进行解析以及相关过滤，以及对其进行优化，最终还需要操作MySQL当中的缓存区的信息；
MySQL Server的第三层：Pluggable Storage Engines(Memory，Index & Storage Management)，此处实际指的是存储引擎；
Pluggable插件式的存储引擎；
以下是MySQL当中的存储引擎:
（MyISAM、InnoDB、NDB、Archive、Federated、Memory、Merge、Partner、Community、Custom …）
MySQL版本5.5之后默认的存储引擎即为InnoDB；
引擎：发动机；
存储引擎是什么？（与飞机上的引擎比较类似，比如客机则有客机的引擎，客机即运送乘客、旅客；但是还有对应的货机，那么货机即对应的即运输货物的；还有直升机等等；不同的飞机有不同的飞机引擎；不同的飞机引擎在相对应的机型当中都发挥着不同的作用；在操作的时候需要考虑到，用户是什么，然后去考虑更适合哪一种引擎，对应的发动机；同样对于mysql的存储引擎是一个道理；存储引擎则需要根据存储的数据不同或者是需求不同而选择不同的存储引擎来进行存储不同的数据；）
MySQL Server的第三层为存储引擎层；
MySQL Server的第四层即存储层：File system（NTFS，ufs，ext2/3，NFS，SAN，NAS）
Files & Logs（Redo，Undo，Data，Index，Binary，Error，Query and Slow）
在操作MySQL数据库中表当中的数据的时候，不管是插入还是查询或者修改删除等操作；实际上最终都是操作的File system即文件系统；最终操作的都是文件系统当中的文件；而这个文件系统即就处于磁盘当中；所以最下面一层就文件系统；最重要操作的也就是文件系统；除了在表结构当中的数据，还有对应的索引信息、二进制错误、查询、慢查询日志等；
此为MySQL体系结构当中的四层；
第一层连接层；负责接收客户端的发送的请求；然后MySQL Server开启一段线程进行相关的认证授权之后再来进行请求的处理；
第二层服务层：服务层在主要起到备份恢复、SQL封装、解析过滤、SQL语句优化、缓存查询等相关操作；
第三层存储引擎层，选择合适的存储引擎对相关数据进行相关的处理；
第四层文件系统：当第三层的数据进行相关的处理完成之后，则需要将数据最终写入文件系统当中，即实际的物理磁盘当中；
整个MySQL由以下组成：
- Connection Pool：连接池组件
- Management Service & Utillties: 管理服务和工具组件
- SQL Interface: SQL接口组件
- Parser: 查询分析器组件
- Optimizer: 优化器组件
- Caches & Buffers: 缓冲池组件
- Pluggable Storage Engines: 存储引擎
- File System: 文件系统
- 连接层
最上层是一些客户和连接服务，包含本地socket通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。
主要完成一些类似于连接处理、授权认证、以及相关的安全方案。
在该层上引入了线程池的概念，未通过认证安全接入的客户端提供线程。
同样在该层上可以实现基于SSL的安全连接，服务器也会为安全接入的每个客户端验证它所具有的的操作权限； - 服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。
所有跨存储引擎的功能也在这一层实现，如过程、函数等。
在该层，服务器会解析查询并创建相应的内部解析树，并对其完成响应的优化如确定表的查询顺序，是否利用索引等，最后生成响应的执行操作。
如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。 - 引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。
不同的存储引擎具有不同的功能，这样可以根据自己的需要，来选取合适的存储引擎； - 存储层
数据存储层，主要是将数据存储在文件系统之上，并完成与存储引擎的交互；
和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。
主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。
这种架构可以根据业务的需求和实际需要选择合适的存储引擎；
存储引擎 - 概述
和大多数的数据库不同，MySQL中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎；
存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。
存储引擎是基于表的，而不是基于库的。
索引存储引擎也可被称为表类型。
Oracle、SQLServer等数据库只有一种存储引擎。
MySQL提供了插件式的存储引擎架构。
索引MySQl存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。
MySQL5.0支持的存储引擎包含：InnoDB、MyISAM、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供食物安全表，其他存储引擎是非事务安全表；
可以通过指定 show engines，来进行查询当前数据库支持的存储引擎；
show engines;
创建新表时，如果不指定存储引擎，那么系统就会使用默认的存储引擎。
MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为InnoDB；
mysql -u root -p #连接mysql
show engines;
# Engine 存储引擎的名字
# Support 是否支持该存储引擎，YES/NO/DEFAULT，DEFAULT默认，默认支持的存储引擎InnoDB
# comment 注释
Comment注释
MyISAM : MyISAM storage engines
MRG_MYISAM : COLLECTION OF IDENTICAL MyISAM tables
MEMORY : Hash based,stored in memory,useful for temporary tables
BLACKHOLE : /dev/null storage engine (anything you wirte to it disappears)
CSV : CSV storage engine
PERFIRMANCE_SCHEMA : Performance Schema
ARCHIVE : Archive storage engine
FEDERATED ： Federated MySQL storage engine
InnoDB ： Support transaction,row-level locking, and foreign keys # 支持事务，支持行级锁并且支持外键
#查看数据库当中的词汇？
show variables like '%storage_engine%';
default_storage_engine InnoDB
default_tmp_storage_engine InnoDB
storage_engine InnoDB
存储引擎 - 特性
下面重点介绍几种常用的存储引擎，并对比各个存储引擎之间的区别，如下表所示：
| 特点 | InnoDB | MyISAM | MEMORY | MERGE | NDB |
|---|---|---|---|---|---|
| 存储限制 | 64TB | 有 | 有 | 没有 | 有 |
| 事务安全 | 支持 | ||||
| 锁机制 | 行锁（适合高并发） | 表锁 | 表锁 | 表锁 | 行锁 |
| B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 哈希索引 | （当前我看到的是支持的） | 支持 | |||
| 全文索引 | 支持（5.6版本之后） | 支持 | |||
| 集群索引 | 支持 | ||||
| 数据索引 | 支持 | 支持 | 支持 | ||
| 索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 数据可压缩 | 支持 | ||||
| 空间使用 | 高 | 低 | N/A | 低 | 低 |
| 内存使用 | 高 | 低 | 中等 | 低 | 高 |
| 批量插入速度 | 低 | 高 | 高 | 高 | 高 |
| 支持外键 | 支持 |
下面将重点介绍最常使用的两种存储引擎：InnoDB、MyISAM，另外两种MEMORY、MERGE，了解即可；
存储引擎 - InnoDB特性
InnoDB存储引擎是MySQL的默认存储引擎。
InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。
但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引；
InnoDB存储引擎不同于其他存储引擎的特点：
事务控制
MySQL 当中事务默认自动提交；
create database demo_02 default charset=utf8mb4;
use demo_01;
create table goods_innodb(
id int(11) not null auto_increment,
name varchar(20) not null,
primary key(id)
)ENGINE=innodb default charset=utf8;
start transaction;
insert into goods_innodb(id,name) values(null,'Meta20');
commit;# 如不提交，则其他客户端无法查看得到插入的数据；
# MySQL默认的隔离级别为可重复读
外键约束
MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建索引的时候，也会自动的创建对应的索引；
下面两张表中，
1） country_innodb是父表，country_id为主键索引；
2） city_innodb是子表，country_id字段为外键，对应于country_innodb表中的主键country_id；
create table country_innodb(
country_id int not null auto_increment,
counry_name varchar(100) not null ,
primary key (country_id)
)engine=innodb default charset=utf8;
create table city_innodb(
city_id int not null auto_increment,
city_name varchar(50) not null,
country_id int not null,
primary key (city_id),
key idx_fk_country_id(country_id),
CONSTRAINT 'fk_city_contry' FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DFLETE RESTRICT ON UPDATE CASCADE
)engine=innodb default charset=utf8;
# ON DELETE RESTRICT 在删除主表数据时，如果有关联记录则不删除
# ON UPDATE CASCADE 更新主表时，如果子表有关联记录，则更新子表记录
# 插入数据
insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);
在创建索引时，可以指定在删除、更新父表时，对子表进行的相应操作，包括RESTRICT、CASCADE、SET NULL和NO ACTION。
RESTRICT和NO ACTION相同，是指限制在子表有关联记录的情况下，父表不能更新；
CASCADE表示父表在更新或者删除时，更新或者删除子表对应的记录；
SET NULL则表示父表在更新或者删除的时候，子表对应字段被SET NULL。
针对上面创建的两个表，子表在外键指定是ON DELETE RESTRICT ON UPDATE CASCADE方式的，那么在主表删除记录的时候，如果子表有对应记录，则不允许删除。主表在更新记录的时候，如果子表有对应记录，则子表也会对应更新；
select * from country_innodb;#主表
select * from city_innodb;#子表；子表当中的外键country_id指向主表country_innodb当中的主键country_id
delete from country_innodb where country_id=2;
#此处由于外键关联，所以导致删除报错，无法更新；
update country_innodb set country_id=100 where country_id=1;
#此时子表当中关联有1；那么说则刚才的子表当中对应country_id也为1 的记录将会进行相应的更新；
查看主表以及子表：
select * from country_innodb;
select * from city_innodb;
存储方式
存储方式实际上指的是InnoDB这个存储引擎对应的表在数据库当中以数据文件的存储方式；
InnoDB存储表和索引有以下两种方式：
- 使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在 innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件；
- 使用多表空间存储，这种方式存储的表的表结构仍然存在.frm文件中，但是每个表的数据和索引单独保存在.ibd中；
存储引擎 - MyISAM特性
MyISAM不支持事务，也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表，有以下两个比较重要的特点：
不支持事务
create table goods_myisam(
id int not null auto_increment,
name varchar(20) not null,
primary key(id)
)engine=myisam default charset=utf8;
start transaction;
insert into goods_myisam values(null,'电脑3');
rollback;
通过测试可以发现，在MyISAM存储引擎中，是没有事务控制的；
文件存储方式
每个MyISAM在磁盘上存储成3个文件，其文件名都和表明相同，但拓展名称分别是：
- .frm (存储表定义)
- .MYD (MYData，存储数据)
- .MYI (MYIndex，存储索引)
存储引擎 - Memory与Merge特性
Memory（内存）存储引擎将表的数据放在内存中。
每个MEMORY表实际对应一个磁盘文件，格式是.frm，改文件中只存储表的数据结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表达效率。
MEMORY类型的表访问非常的快，因为他的数据是存放在内存中的，并且默认使用HASH索引，但是服务一旦关闭，表中的数据就会丢失；
MERGE（合并）存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有存储数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的；
对于MERGE类型表的插入操作是通过INSERT_METHOD子句定义插入的表，可以有三个不同的值，使用FIRST或者LAST值使得插入操作被相应的作用在第一或者是最后一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE表执行插入操作；
可以对MERGE表进行DROP操作，但是这个操作知识删除MERGE表的定义，对内部的表是没有任何影响的；
MERGE存储引擎与view视图有些类似；
下面是一个创建和使用MERGE表的示例：
1）创建三个测试表 payment_2006、payment_2007、payment_all，其中payment_all是前两个表的MERGE表；
create table order_1990(
order_id int,
order_money double(10,2),
order_address varchar(50),
primary key (order_id)
)engine=myisam default charset=utf8;
create table order_1991(
order_id int,
order_money double(10,2),
order_address varchar(50),
primary key (order_id)
)engine=myisam default charset=utf8;
create table order_all(
order_id int,
order_money double(10,2),
order_address varchar(50),
primary key (order_id)
)engine=merge
union = ( order_1990, order_1991 )
INSERT_METHOD=LAST
default charset=utf8;
2） 分别向两张表中插入记录
insert into order_1990 values(1,100.0,'上海');
insert into order_1990 values(2,100.0,'北京');
insert into order_1991 values(10,100.0,'北京');
insert into order_1991 values(11,100.0,'上海');
3） 查询三张表当中的数据：
order_1990表中的数据：
select * from order_1990;
order_1991表中的数据：
select * from order_1991;
order_all表中的数据：
select * from order_all;
存储引擎 - 选择原则
在选择存储引擎时，应该选择根据应用系统的特点选择合适的存储引擎。
对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合；
以下是几种常用的存储引擎的使用环境：
- InnoDB
- 是MySQL的默认存储引擎，用于事务处理应用程序，支持外键。
- 如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
- InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似于计费系统或者是财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择；
- MyISAM
- 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的；
- MEMORY
- 将所有的数据保存在内存当中RAM，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。
- MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。
- MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
- MERGE
- 用于将一些列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。
- MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率，这对于存储诸如数据仓库等VLDB环境十分合适。
有一个点：就是有没有类似emm，MERGE然后内部基层的表的存储引擎是InnoDB这种的；如果是这种的话；不是有那个什么分库分表什么的？（我不太了解分库分表，乱说的）
优化SQL步骤 - SQL执行频率
在应用的开发过程中，由于初期数据量小，开发人员写SQL语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据的急剧增长，很多SQL语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的SQL语句就成为整个系统性能的瓶颈，因此必须要对它们进行优化，本章将详细介绍MySQL中优化SQL语句的方法。
当面对一个有SQL性能问题的数据库时，应该从何处入手来进行系统的分析，使得能够尽快定位问题SQL并尽快解决问题。
查看SQL执行频率
MySQL客户端连接成功后，通过 show [ session | global ] status 命令可以提供服务器状态信息。
show [ session | global ] status 可以根据需要加上参数 “session” 或者 “global” 来显示 session级（当前连接）的统计结果和 global级（自数据库上次启动至今）的统计结果。
如果不写，默认使用参数是“session”。
下面的命令显示了当前session中所有统计参数的值：
针对数据库
show status like 'Com_______'; # _ 占位字符，匹配的模糊字符 查询级别为session会话级别
show global status like 'Com_______'; # _ 匹配的模糊字符 查询级别为global全局级别
Com_binlog
Com_commit
Com_delete #删除的次数
Com_insert #插入的次数
Com_repair
Com_select #查询的次数
Com_revoke
Com_signal
Com_update #修改的次数
Com_xa_end
#此时查询的为当前连接session的信息，并非为整个数据库全局的统计参数
#如果需要查询全局则：show global status like 'Com_______';
#通过知道当前数据库当中是以查询为主还是增删改为主；为SQL优化提供借鉴性指标
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存储引擎的，累加的算法也略有不同。
针对InnoDB存储引擎
针对于InnoDB：
show status like 'Innodb_rows_%';# 此时查询出来的为InnoDB的各个操作所影响的行，数量
Innodb_rows_deleted 2 # 该存储引擎中删去的行数
Innodb_rows_inserted 49# 该存储引擎中插入的行数
Innodb_rows_read 380# 该存储引擎中读取的行数
Innodb_rows_updated 15# 更新的行数
mysql -u root -p
use demo_01l
show tables;
select * from goods_innodb; # Innodb_rows_read此时会增加，查询出来的结果集有n条记录，则Innodb_rows_read增加n
Innodb_rows_deleted 2 # 该存储引擎中删去的行数
Innodb_rows_inserted 49# 该存储引擎中插入的行数
Innodb_rows_read 382# 该存储引擎中读取的行数
Innodb_rows_updated 15# 更新的行数
update goods_innodb set name ='Meta30'; # Innodb_rows_update此时会增加；修改影响n条记录，则Innodb_rows_update增加n;
Innodb_rows_deleted 2 # 该存储引擎中删去的行数
Innodb_rows_inserted 49# 该存储引擎中插入的行数
Innodb_rows_read 382# 该存储引擎中读取的行数
Innodb_rows_updated 17# 更新的行数
优化SQL步骤 - 定位低效SQL
可以通过以下两种方式定位执行效率较低的SQL语句。
- 慢查询日志：通过慢查询日志定位那些执行效率较低的SQL语句，用 –log-slow-queries[ =file_name ] 选项启动时，mysqld写一个包含所有执行时间超过 long_query_time 秒的SQL语句的日志文件。
- show processlist ：慢查询日志在 查询结束以后才记录，所以在应该 用反映执行效率出现问题的时候 查询慢日志 并不能定位问题，可以使用 show processlist 命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看SQL的执行情况，同时对一些 锁表操作 进行 优化。(实时)
show processlist;
#（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语句，是判断问题语句的一个重要依据。
优化SQL步骤 - explain指令介绍
通过以上步骤查询到效率低的SQL语句后，
可以通过 EXPLAIN 或者 DESC 命令 获取MySQL如何执行SELECT语句的信息，包括SELECT语句执行过程中表如何 连接 和 连接 的顺序。
查询SQL语句的执行计划：
explain select * from tb_item where id=1;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | tb_item | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
explain select * from tb_item where title='阿尔卡特（OT-979）冰川白 联通3G手机3';
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | tb_item | ALL | NULL | NULL | NULL | NULL | 9816098 | Using where |
| 字段 | 含义 |
|---|---|
| id | select查询的序列号，是一组数字，表示的是查询中select子句或者是操作表的顺序。（与表结构执行的顺序有关系） |
| select_type | 表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者是子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等 |
| table | 输出结果集的表（当前输出的数据与哪一张表有关） |
| type | 表示表的连接类型，性能由好到差的连接类型为（system---->const---->eq_ref------>ref------->ref_or_null------>index_merge------>index_subquery------->range------>index------>all） |
| possible_keys | 表示查询时，可能使用的索引。 |
| key | 表示实际使用的索引 |
| key_len | 索引字段的长度 |
| ref | 引用 |
| rows | 扫描行的数量 |
| extra | 执行情况的说明和描述，额外的内容，前面几个字段当中没有表述清楚的，在这个字段当中表述出来 |
explain 用来 查看 SQL语句 的 执行计划。
优化SQL步骤 - explain之id
环境准备
用户表、角色表、用户角色表（多对多关系）
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 'union_role_name' ('role_name')
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE 't_user'(
'id' varchar(32) NOT NULL,
'username' varchar(45) DEFAULT NULL,
'name' varchar(45) DEFAULT NULL,
'password' varchar(96) DEFAULT NULL,
PRIMARY KEY ('id'),
UNIQUE KEY 'union_user_username' ('user_name')
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE 'user_role'(
'id' int(11) NOT NULL autoincrement,
'user_id' varchar(32) DEFAULT NULL,
'role_id' varchar(32) DEFAULT NULL,
PRIMARY KEY ('id'),
KEY 'fk_ur_user_id'('user_id'),
KEY 'fk_ur_role_id'('role_id'),
CONSTRAINT 'fk_ur_role_id' FOREIGN KEY ('role_id') REFERENCES 't_role' ('id') ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT 'fk_ur_user_id' FOREIGN KEY ('user_id') REFERENCES 't_user' ('id') ON DELETE NO ACTION ON UPDATE NO ACTION
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 插入用戶数据
insert into 't_user'('id','username','password','name') values('1','老王','xxxxxyyyyyyy','超级管理员');
insert into 't_user'('id','username','password','name') values('2','老李','xxxxxyyyyyyyzzzzz','系统管理员');
insert into 't_user'('id','username','password','name') values('3','老头子','xxxxxyyyytyyyzzzzz','学生1');
insert into 't_user'('id','username','password','name') values('4','老婆子','xxxxxyywyyyyyzzzzz','学生2');
insert into 't_user'('id','username','password','name') values('5','super','xxxxxyyfyyyyyzzzzz','学生3');
insert into 't_user'('id','username','password','name') values('6','admin','xxxxxyyyyyyyzzzzze','学生4');
# 插入角色数据
....
# 插入用户角色数据
....
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;# 多表联查
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | r | PRIMARY | NULL | NULL | NULL | NULL | 5 | NULL |
| 1 | SIMPLE | ur | ref | fk_ur_user_id,fk_ur_role_id | fk_ur_role_id | 99 | db03.r.id | 1 | Using where |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 98 | db.ur.user_id | 1 | NULL |
（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'
)
)# 嵌套子查询
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | t_role | const | PRIMARY | PRIMARY | 98 | const | 1 | NULL |
| 2 | SUBQUERY | user_role | ref | fk_ur_user_id | fk_ur_user_id | 99 | const | 1 | Using where |
| 3 | SUBQUERY | t_user | const | unique_user_username | unique_user_username | 137 | const | 1 | Using index |
（3） id有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。
EXPLAIN SELECT * FROM t_role r,
(
SELECT * FROM user_role ur WHERE ur.'user_id'='2'
) a
WHERE r.id=a.role_id;
#三表查询又连接了一个子查询
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | < derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 1 | PRIMARY | r | eq_ref | PRIMARY | PRIMARY | 98 | a.role_id | 1 | NULL |
| 2 | DERIVED | ur | ref | fk_ur_user_id | fk_ur_user_id | 99 | const | 1 | Using index condition |
优化SQL步骤 - explain之select_type
explain之select_type
表示SELECT的类型，常见的取值，如下表所示（从上往下，效率越来越低）：
| 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 |
explain select * from tb_item;
# SIMPLE
explain select * from t_user where id=(select id from user_role where role_id='9');
# select * from t_user where id： PRIMARY ；
#(select id from user_role where role_id='9')：SUBQUERY
explain select a.* from (select * from t_user where id in ('1','2')) a;
# select * from t_user where id in ('1','2'): DERIVED；存储至衍生的临时表
# select a.* from ： PRIMARY derived2指的是derived指的是衍生的表a，2则指的是id，执行操作表顺序字段；
explain select * from t_user where id='1' union select * from t_user where id='2';
#select * from t_user where id='1': PRIMARY
#select * from t_user where id='2' ： UNION
# select * from t_user where id='1'的结果 && select * from t_user where id='2'的结果联合起来： UNION RESULT
优化SQL步骤 - explain之table 、type
explain之table
展示这一行的数据是关于哪一张表的；
explain之type
type显示的是访问类型，是较为重要的一个指标，可取值为：
| type | 含义 |
|---|---|
| NULL | MySQL不访问任何表，索引，直接返回结果 |
| system | 表只有一行记录（等同于系统表），这是const类型的特例，一般不会出现 |
| const | 表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，索引很快。如将主键置于where列表中，MySQL就能将该查询转换成一个常量。(根据主键或者唯一索引进行查询) |
| eq_ref | 类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描 |
| ref | 非唯一性索引扫描，返回皮诶某个单独值的所有行。本质上也是野种索引访问，返回所有匹配某个单独值的所有行（多个） |
| range | 只检索给定返回的行，使用一个索引来选择行。where之后出现between，<，>，in等操作 |
| index | index与ALL的区别为index类型只是遍历了索引树，通常比ALL快，ALL是遍历数据文件。 |
| all | 将遍历全表以找到匹配的行 |
type为NULL
explain select now(); # 查询当前时间(查询效率非常高) ： type->NULL
示例：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
type为system
explain select * from (select * from t_user where id='1') a;： type->system
示例：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | < derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | DERIVED | t_user | const | PRIMARY | PRIMARY | 98 | const | 1 | NULL |
type为const
explain select * from t_user where id='1';# 根据主键查询返回一条记录：type->const
示例：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | t_user | const | PRIMARY | PRIMARY | 98 | const | 1 | NULL |
type为const
explain select * from t_user where username='stu1';#根据唯一索引查询返回一条记录：type->const
示例：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | t_user | const | unique_user_username | unique_user_username | 137 | const | 1 | NULL |
type为eq_ref
select * from t_user;//id有1、2、3、4、5、6
select * from t_role;//id有10、5、7、8、9
explain select * from t_user u ,t_role r where u.id=r.id;#业务逻辑层面不符合，只是数据库硬性查询（强行关联）出一个type为eq_ref的示例而已
示例：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | r | ALL | PRIMARY | NULL | NULL | NULL | 5 | NULL |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 98 | demo_02.r.id | 1 | NULL |
type为ref
show index from t_user;#查询t_user表当中的索引
create index idx_user_name on t_user('name');
explain select * from t_user where name='a';#根据非唯一性索引进行查询，返回的记录只有一条
示例：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | t_user | ref | idx_user_name | idx_user_name | 137 | const | 1 | Using index condition |
type为range(范围查询)
explain .......（between and）、（>、<、>=、<=）...
type为index(指的即索引)
explain select * from t_user;//查询所有数据，进行全表扫描，即到数据文件当中进行读取；type为ALL
explain select id from t_user;//此时type为index；因为id为主键，主键则走主键索引；
结果值从最好到最坏依次是：
NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all
优化SQL步骤 - explain之key rows extra
explain之key
possible_keys:显示可能应用在这张表的索引，一个或者多个；
key:实际使用的索引，如果为NULL，则没有使用索引；
key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好（越短执行效率越高）；
explain之rows
扫描行的数量。
explain select * from t_user where name ='a';#name建了索引，所以查询rows为1行
explain select * from t_user where password='aaa';# password没有建立索引，整表扫描；rows即SQL语句查询时所查询的行数；
explain之extra
其他的额外的执行计划信息，在该列展示。
（using_filesort以及using_temporary非常耗性能；
需要优化using_filesort以及using_temporary；保持using_index）
| extra | 含义 |
|---|---|
| using_filesort | 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为“文件排序” |
| using_temporary | 使用了临时表保存中间结果，MySQL对查询结果排序时使用临时表。常见于order by 和group by |
| using_index | 表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率不错。 |
explain select * from t_user order by id;#按照id进行排序
explain select * from t_user order by password;#根据文件进行排序；即需要扫描文件当中的内容，然后再去进行排序；效率非常低；这个时候如果需要进行优化的话，可以在password字段上与name一样建立索引；
explain select * from t_user order by name; #此时依然是using_filesort根据文件排序
explain select name from t_user order by name;#此时则使用的是using_index根据索引排序
explain select * from t_user group by name;#此时extra为null，原因是根据name字段建立了索引
explain select * from t_user group by password;#此时password字段没有建立索引；extra为using temporary；using filesort效率低；
优化SQL步骤 - show profile
MySQL从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。
show profiles 能够在做SQL优化时帮助了解时间都耗费到哪里去了；
通过 have_profiling 参数，能够看到 当前MySQL 是否支持 profile；
select @@have_profiling;#系统变量；YES/NO
默认 profiling 是关闭的，可以通过set语句在Session级别开启profiling：
select @@profiling;
set profiling=1;# 开启profiling开关；0:未开启；1：开启
通过profile，能够更清楚的了解SQL执行的过程；
首先，可以执行一系列的操作，如下图所示：
show databases;
use db01;
select * from t_user;
select count(*) from tb_item;
select * from tb_item where title='abc';
show profiles;#查看之前每一个操作的耗时情况 query_id(当前查询的id)、duration（执行的耗时时间）、query（查询的sql）
# use db01;其实底层即select database();
执行完上述命令之后，再执行show profiles指令，来查看SQL语句执行的耗时：
show profiles;# query_id、duration、query
通过 show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间；
show profile for query 6;#6即通过show profiles查询出来的query_id; status、duration
| status（经历的每一个阶段） | duration（每一个阶段的耗时情况） |
|---|---|
| starting（开启） | 0.000046 |
| checking permissions（校验权限） | ... |
| opening tables（打开表） | ... |
| init（初始化） | ... |
| system lock（系统锁） | ... |
| optimizing（优化器） | ... |
| statistics（统计） | ... |
| perparing（准备） | ... |
| executing（执行） | ... |
| sending data（发送数据） | ... |
| end（结束） | ... |
| query end（查询结束） | ... |
| closing tabls（关闭表） | ... |
| freeing itmes（释放其他项） | ... |
| cleaning up（清除） | ... |
（select count(*) from tb_item；该sql语句的时间都耗费在sending data上了；）
TIP:
Sending data 状态表示MySQL线程开始访问数据行并把结果放回给客户端，而不仅仅是返回给客户端。
由于Sending data 状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。
在获取到最耗时时间的线程状态后，MySQL支持进一步选择all、cpu、block io、context switch、page faults 等明细类型等查看MySQL在使用什么资源上耗费了过高的时间。
例如，选择查看CPU的耗费时间：
select profile cpu for query 6;# status、duration、CPU_user、CPU_System查询到的是id为6的该条sql语句在各个阶段当中cpu的耗费情况；
select profile all for query 6;# status、duration、CPU_user、CPU_system、context_voluntary、context_involuntary、block_ops_in、block_ops_out、......
通过 show profile 可以 分析SQL语句 各个阶段的 耗时情况
优化SQL步骤 - trace工具
mysql的服务端server有一个步骤就是optimizer，优化sql部分；按照mysql当中具体对应的规则对SQL进行优化处理；trace即分析优化器
trace分析优化器执行计划
MySQL5.6提供了对SQL的跟踪 trace，通过trace文件能够进一步了解为什么优化器选择A计划，而不是B计划。
打开trace，设置格式为JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。
SET optimizer_trace ='enabled=on', end_markers_in_json=on;# optimizer_trace ='enabled=on'进行开启；end_markers_in_json=on 设置格式为JSON
set optimizer_trace_max_mem_size=1000000;# 日志文件占用内存大小
执行SQL语句：
select * from tb_item where id < 4;
最后，检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的：
select * from infomation_schema.optimizer_trace\G;# infomation_schema系统库；optimizer_trace系统库当中的日志表
# QUERY 查询的SQL语句；select * from tb_item where id<4;
# TRACE 跟踪信息；在跟踪信息当中最终select * 当中的* 最终解析为tb_item表当中的每一个具体的字段
索引的使用 - 验证索引提升查询效率
索引的使用
索引时候数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题；
验证索引提升查询效率
在准备的表结构tb_item中，一共存储了300万条记录；
A. 根据ID查询
show tables;
select count(*) from tb_items;
select * from tb_item where id=1888;#id字段具有主键索引
select * from tb_item where id=1999\G;
#根据title进行精确查询
select * from tb_item where title='阿尔卡特';#查询速度慢，因为title字段没有索引，查询效率低，创建主键即自动生成主键索引；
#查看SQL语句的执行计划：
explain select * from tb_item where title='阿尔卡特'\G;
处理方案，针对title字段，创建索引：
create index idx_item_title on tb_item(title);
索引创建完成之后，再次查询：
select * from tb_item where title='阿尔卡特'\G;
# 创建索引的过程中，mysql在做什么？表tb_item中有300万条记录，那么在创建索引的时候就会对这300万条记录重建索引；所以此时即数据库底层在重建索引；创建完索引之后再进行查询效率则变快；索引是优化查询最有效的方式之一；
查询速度很快，接近0s，主要的原因是因为id为主键，有索引；
explain select * from tb_item where id=1999\G;
准备环境
create table 'tb_seller'(
'sellerid' varchar(100),
'name' varchar(100),
'nickname' varchar(50),
'password' varchar(60),
'status' varchar(1),
'address' varchar(100),
'createtime' datetime,
primary key('sellerid')
)engine=innodb default charset=utf8mb4;
#插入记录 导入基本数据
insert into tb_seller values ......
# 创建联合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
创建了索引并不是一定会提高效率；创建了索引并且需要合理运用索引才能够提高查询效率；
索引的使用 - 全值匹配
避免索引失效
(1) 全值匹配，对索引中所有列都指定具体值。
该情况下，索引生效，执行效率提高。
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'\G;
索引的使用 - 最左前缀法则
避免索引失效
(2) 最左前缀法则
tb_seller创建了一个复合索引即name、status、address这三个字段；
如果索引了多列，要遵守最左前缀法则。
指的是查询从 索引的最左前列 开始，并且 不跳过 索引中的列。
匹配最左前缀法则，走索引：
explain select * from tb_seller where name ='小米科技';# key:idx_name_sta_addr； key_len：403
explain select * from tb_seller where name='小米科技' and status='1';# key:idx_name_sta_addr； key_len：410
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';# key:idx_name_sta_addr； key_len：813
explain select * from tb_seller where status='1' and address='北京市';# key:NULL key_len：NULL 此时则不再走索引；因为不再符合最左前缀法则，没有以最左边的列开始；
explain select * from tb_seller where status='1' and address='北京市' and name='小米科技';# key:idx_name_sta_addr； key_len：813 此时是走索引的；与where列表当中字段的先后顺序无关；会检查where条件列表中包不包含最左列；而是与复合索引当中的是否存在有复合索引当中的最左字段；
explain select * from tb_seller where name='小米科技' and address='北京市' ;#跳过索引列不走索引; key:idx_sellter_name_sta_addr；key_len: 403；此时索引的长度是403；也就是这是执行 explain select * from tb_seller where name ='小米科技'; 时所走的索引；即后面的 and address='北京市' 并未走索引；
违反最左前缀法则，索引失效；
需要包含 复合索引当中的最左列，并且不能跳过 复合索引当中的某一列进行查询否则复合索引失效；
索引的使用 - 索引失效情况（范围查询、字段运算）
避免索引失效
（3） 范围查询右边的列，不能使用索引；
根据前面的两个字段name、status查询是走索引的，但是最后一个条件address没有用到索引。
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';# key:idx_name_sta_addr； key_len：813；此时三个字段name、status、address三个字段都走了索引；
explain select * from tb_seller where name ='小米科技' and status >'1' and address ='北京市';# key:idx_name_sta_addr； key_len：410;此时走的索引是name和status字段的索引；而范围查询status>'1'之后的address并没有走索引；
# 范围查询之后的索引字段将失效
（4） 不要在索引列上进行运算操作，索引将失效；
select * from tb_seller;
select * from tb_seller where name='小米科技';#此时走索引
select * from tb_seller where substring(name,3,2)='科技';
explain select * from tb_seller where substring(name,3,2)='科技';#此时key为null以及key_len为null；而type为ALL则说明了进行了全表扫描；没有走索引
（5） 字符串不加单引号，会造成索引失效；
explain select * from tb_seller where name='科技' and status='0';# 此时key:idx_name_sta_addr；key_len：410；
explain select * from tb_seller where name='科技' and status=0;# 此时key：idx_name_sta_addr；key_len：403；索引长度为403；则说明走的索引为name字段的索引；而status字段的索引并没有走；造成了status=0 该索引字段的失效；当status字段取值没有加单引号时，mysql底层检测到status字段为varchar类型所以会对其没有加单引号的取值进行隐式类型转换；隐式类型转换了之后该status索引字段就失效了；因为底层对该字段进行了运算操作；
索引的使用 - 覆盖索引
避免索引失效
（6） 尽量使用覆盖索引，避免使用 select *
尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列））， 减少select * ；
如果查询列，超出索引列，也会降低性能；
explain select * from tb_seller where name ='小米科技';
# extra为using index condition指的是：在查询的时候确实用到了name这个字段的索引；
#但是用到了name这个字段的索引信息之后，在返回数据的时候还需要进行索引回调查询；
explain select name , status ,address where name='小米科技';
# 此时extra为using where ;using index；
# 此时表示的是：可以通过索引进行查询；但是此时并不需要进行回调查询了；
# 原因就在于查询的字段在索引结构当中已经被记录了；
# 所以并不需要去进行回调查询；
explain select name , status where name='小米科技'; #同上
explain select name , address where name='小米科技'; #同上
explain select name , address ,password where name='小米科技';
# 复合索引当中并未包括password字段则此时name索引字段以及address索引字段当中并没有包括password字段的信息，
# 则此时需要去回调查询password字段的信息；
# 即extra为using index_condition
TIP:
索引回调查询指的是：
using index : 使用覆盖索引的时候就会出现
using where : 在查找使用索引的情况下，需要回表去查询所需的数据
using index condition ：查找使用了索引，但是需要回表查询数据（从索引当中拿到了数据，然后再到表结构当中去拿到该数据对应的一整行数据，因为该name索引字段并没有记录所有的数据，只是记录了那么字段的数据；所以需要回表查询数据；即用了索引还需要回调查询）
using index ; using where : 查找使用了索引，但是需要的数据都在索引列中能找到，索引不需要回表查询数据
（7） in走索引，not in 索引失效
索引的使用 - or索引失效情况
避免索引失效
（7）用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到；
示例：name字段是索引列，而createtime不是索引列，中间是or进行连接是不走索引的；
select * from tb_seller;
explain select * from tb_seller where name='小米科技' and createtime='2088-01-01 12:00:00'\G;#key:idx_name_sta_addr； key_len: 403;此时走索引
explain select * from tb_seller where name='小米科技' or createtime='2088-01-01 12:00:00'\G;#key:idx_name_sta_addr； key_len: NULL
explain select * from tb_seller where name='小米科技' and nickname='小米官方旗舰店';#此时走索引；key_len:403;走的是name字段的索引
explain select * from tb_seller where name='小米科技' or nickname='小米官方旗舰店';#换成or之后，nickname字段非索引字段；所以此时将全表扫描；不会走索引；key_len:NULL；以及type为ALL
# or之后的字段如果没有索引的话；那么整个where列表当中即便有索引字段也将都失效，不再走索引；
索引的使用 - like模糊匹配
避免索引失效
（8） 以%开头的Like模糊查询，索引失效。
如果仅仅是尾部模糊查询，索引不会失效。
如果是头部模糊查询匹配，则索引失效。
explain select * from tb_seller where name like '小米科技';#key: idx_name_sta_addr； key_len: 403; 此时走索引；索引走的是name字段
explain select * from tb_seller where name like '%小米科技';#key: NULL； key_len: NULL; 此时不走索引；且type为ALL；全表扫描
explain select * from tb_seller where name like '%小米科技%';#key: NULL； key_len: NULL; 此时不走索引；且type为ALL；全表扫描
explain select * from tb_seller where name like '科技%';#key: idx_name_sta_addr； key_len: 403; 此时走索引；索引走的是name字段
类似 “%科技%” 这种情况在业务逻辑处理当中还是比较多的；但是这样效率将十分低下；所以针对这种情况的处理方式为：
覆盖索引：覆盖索引指的是，尽量不要使用select * 这种方式进行查询；而是使用复合索引当中的那些索引列，即索引字段；即复合索引为idx_name_sta_addr，当中包含的索引字段为name、status、address；所以select 查询列名称的时候尽量查询的列名称为复合索引当中的索引列，或者是其他索引的字段（例如唯一索引的索引字段、主键索引的索引字段等）；
即通过覆盖索引就可以解决 “%科技%” like模糊查询效率低下的情况（条件是select 查询的字段都需要是索引字段，如果出现了查询非索引字段则将不再走索引）；
explain select sellerid from tb_seller where name like '%科技%';# 则此时是走索引的；key:idx_seller_name_sta_addr；key_len：813；
explain select sellerid,name from tb_seller where name like '%科技%';# 则此时是走索引的；key:idx_seller_name_sta_addr；key_len：813；
explain select sellerid,name,status,address from tb_seller where name like '%科技%';# 则此时是走索引的；key:idx_seller_name_sta_addr；key_len：813；
explain select sellerid,name,status,address,password from tb_seller where name like '%科技%';# 则此时是不走索引的，由于password字段并不是索引字段，所以将造成不会走索引的局面；key:NULL；key_len：NULL；
索引的使用 - 全表扫描更快
避免索引失效
（9） 如果MySQL评估使用索引比全表更慢，则不使用索引；
如果发现走全表扫描比走索引要快，这个时候mysql就会走全表扫描；
select * from tb_seller;
explain select * from tb_seller where address='北京市';#此时key:NULL 以及key_len: NULL；由于address是复合索引当中的索引字段但是又不满足最左前缀法则；所以不走索引；
create index idx_seller_address on tb_seller(address);#在tb_seller表中的address字段上建立索引；单列索引
explain select * from tb_seller where address='北京市';# 但是此时仍然没有走索引；possible_key出现有idx_seller_address；但是key为NULL；以及key_len为NULL；type为ALL；即走的还是全表扫描
explain select * from tb_seller where address='西安市';#此时是走索引的，key为idx_address；key_len为403;索引走的是address字段；extra为using index condition
#同样一条SQL语句，只是传入的参数不同；也就导致了是否走索引的不同；一个是全表扫描没有走索引；而另外一个是走了索引idx_seller_address；
#原因就在于：这个与数据库当中的数据是存在一定的关系的；
select * from tb_seller; #该表当中一共有12条记录；这12条记录当中出现有address为“北京市”的记录行数就有11条记录；只有一条是1条是“西安市”；
#如果数据量大，且精确查询的内容又在表中占比大；此时就极有可能走的是全表扫描而非走索引了；速度更快
索引的使用 - NULL值的判定
避免索引失效
（10） is NULL，is NOT NULL 有时 索引失效；
show index from tb_seller;
select * from tb_seller where address is null;
explain select * from tb_seller where address is null;
# 此时是走索引的；key：idx_seller_address ;key_len：403 走的索引字段为address
explain select * from tb_seller where address is not null;
# 此时是没有走索引的；key：null ;key_len:null;type:all 走的是全表扫描
select * from tb_seller;
# 当前该表当中每一条记录都address字段记录取值都是 not null;
# 当前explain where address is null时走了索引，
# 即address is null这样的记录在表当中占比较少此时则走索引；
# 而explain where address is not null时没有走索引；
# 而此时 address is not null则在表当中占比较多；
# 所以就不会去走索引而是全表扫描；
# 这个与前面所说的全表扫描更快是一个道理；
# 全表扫描与走索引的这两种方式哪一种更快就选择哪一种方式；
# 也就是说mysql底层会去自动的判断如果走全表扫描更快就走全表扫描；
# 如果走索引更快就走索引的这种方式；
explain select * from tb_seller where name is null;
# 此时走索引；key:idx_name_sta_addr；key_len：403
explain select * from tb_seller where name is not null;
# 此时不走索引；key:null； key_len：null; type：ALL 走的是全表扫描
explain select * from t_user where name is null;
# 此时不走索引；key：null；key_len: null ; type:all 走的是全表扫描
explain select * from t_user where name is not null;
# 此时走了索引；key:idx_name; key_len:138;
select * from t_user;
update t_user set name =null where id <> 1;
select * from t_user;
# 则此时除了id为1的记录其余的记录的name字段就变成了null；
# 所以如果此时查询t_user表当中name字段为is null则走全表扫描；
# 而查询is not null则走索引；
show index from t_user;#此时在t_user表当中name字段是存在索引的；
explain select * from t_user where name is null;
# key:null ; key_len:null;type:all 走的是全表扫描（原因就在于此时在表当中记录name字段为null占比大于name字段is not null；
# 所以全表扫描与走索引的方式这两者相比较之下，全表扫描更快；）
explain select * from t_user where name is not null;
# 此时是走索引的；key:idx_user_name;key_len:138;
# 也就是说is null与is not null；
# 有时是会走索引的有时又不会走索引；
# 这要分情况而定；
# 这个根据数据库当中的数据量来决定的；
# 如果查询的字段在数据量当中的null值占比较多,那么这个时候is null将会走全表扫描而is not null则走索引；
# 如果查询的字段在数据量当中绝大部分都是存在有值的也就是说is not null占据较多；
# 则此时is null就会走索引；而is not null则走全表扫描；
索引的使用 - in和not in
避免索引失效
（11） 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');
select * from tb_seller;
explain select * from tb_seller where sellerid in ('oppo','xiaomi','sina');
# 此时使用了索引；key:PRIMARY；key_len:402;
explain select * from tb_seller where sellerid not in ('oppo','xiaomi','sina');
# 此时未走索引；索引失效；key:NULL；key_len:NULL；type:ALL 全表扫描
索引的使用 - 单列索引与复合索引选择
避免索引失效
（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);
当对name、status、address字段进行搜索的时候，数据库只会选择一个索引；
所以此时在这种情况下单列索引并不占优势；
数据库会选择一个最优的索引来使用，并不会使用全部索引；
show index from tb_seller;#查看tb_seller表当中的索引
# PRIMARY 主键索引
# idx_name_sta_address name
# idx_name_sta_address status
# idx_name_sta_address address
# idx_seller_address address
#删除单列索引
drop index from idx_seller_address on tb_seller;
#查看tb_seller表的索引
show index from tb_seller;
explain select * from tb_seller where name='小米科技' and status='0' and address='西安市';#此时使用复合索引；key:idx_name_sta_addr；key_len：813
#删除复合索引
drop index idx_name_sta_address on tb_seller;
#创建单列索引
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);
#查看单列索引
show index from tb_seller;
#PRIMARY 主键索引
# idx_seller_name 单列索引name字段
# idx_seller_status 单列索引status字段
# idx_seller_address 单列索引address字段
explain select * from tb_seller where name ='小米科技' and status='0' and address='西安市';
# 此时走索引但是；key:idx_seller_name; key_len:403;
# 只走了name字段的索引；
# 而status字段以及address字段的索引未走；
# 相当于白创建了status以及address字段的索引了；
# 即此时possible_key：idx_seller_name、idx_seller_status、idx_seller_address；这三个索引可能都会用到；
# 但是实际上只用到了idx_seller_name索引；即name字段的索引；
# 为什么选择了name字段来作为该SQL的索引；
# 原因在于：数据库会选择一个最优的索引来使用；并不会使用全部索引；也就意味着name该字段的索引在这三个索引当中最优；
# 为什么name该字段的索引idx_seller_name最优，
select * from tb_seller;
# 因为在此表当中，name查询的精确值为“小米科技”辨识度最高，记录行为1；
# 而status字段取值为0的记录行超过1；
# address字段取值为“西安市”记录行也只有1；
# 也应该是遵循了从左到右的一个判断；所以此时idx_seller_name索引最优；
# 从而走了idx_seller_name索引；
explain select * from tb_seller where status='0' and address='西安市';
# 此时没有name字段；
# 而address在数据表当中辨识度最高；
# 即记录行为1；所以此时走的索引自然即为idx_seller_address；
# key:idx_seller_address;key_len:403;possible_key:idx_seller_status,idx_seller_address;
explain select * from tb_seller where status='0' and address='北京市';
# 如果此时address取精确值为“北京市”；
# 而在数据库当中address取值为“北京市”的记录行辨识度并不高；
# 也就是取值为“北京市”的address字段其记录行有很多超过1，即总共12条记录，有11条记录当中的address都是“北京市”，从而其辨识度并不高；
# 而此时status字段相比较之下，取值为0的记录行数，总行数12行，而status取值为0的记录行数为3行，那么此时较之address的辨识度自然是要高一些；
# 所以将走status字段的索引，即idx_seller_status索引；
# possible_key:idx_seller_status,idx_seller_address；key:idx_seller_status;key_len:7；
也就是说：数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部的索引；
索引的使用 - 查看索引使用情况
查看索引使用情况
show status like 'Handler_read%';#查看的是当前会话中索引的使用情况
#Variable_name Value
show global status like 'Handler_read%';#查看的是全局中索引的使用情况
| Variable_name | Value |
|---|---|
| Handler_read_first | 0 |
| Handler_read_key | 2 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 22 |
Handler_read_first: 索引中第一条被读的次数。如果较高，表示服务器正在自行大量全索引扫描（这个值越低越好）；
Handler_read_key: 如果索引正在工作，这个值代表一个行被索引值读取的次数；如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）；
Handler_read_next: 按照键顺序读下一行的请求数。如果用范围约束或者如果执行索引扫描来查询索引列，该值增加；
Handler_read_prev: 按照键顺序读前一行的请求数，该读方法主要用于优化ORDER BY.....DESC；
Handler_read_rnd: 根据固定位置读一行的请求数。如果正执行大量查询并需要对结果进行排序该值较高。可能使用了大量需要MySQL扫描整个表的查询或者连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救；
Handler_read_rnd_next: 在数据文件中读下一行的请求数。如果正进行大量的表扫描，该值要求较高。通常说明表索引不正确或者是写入的查询没有利用索引；
SQL优化 - 大批量插入数据
大批量插入数据
环境准备：
CREATE TABLE 'tb_user_1'(
'id' int(11) NOT NULL AUTO_INCREMENT,
'username' varchar(45) NOT NULL,
'password' varchar(96) NOT NULL,
'name' varchar(45) NOT NULL,
'birthday' datetime DEFAULT NULL,
'sex' char(1) DEFAULT NULL,
'email' varchar(45) DEFAULT NULL,
'phone' varchar(45) DEFAULT NULL,
'qq' varchar(32) DEFAULT NULL,
'status' varchar(32) DEFAULT NULL comment '用户状态',
'create_time' datetime NOT NULL,
'update_time' datetime DEFAULT NULL,
PRIMARY KEY('id'),
UNIQUE KEY 'unique_user_username'('username')
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE 'tb_user_2'(
'id' int(11) NOT NULL AUTO_INCREMENT,
'username' varchar(45) NOT NULL,
'password' varchar(96) NOT NULL,
'name' varchar(45) NOT NULL,
'birthday' datetime DEFAULT NULL,
'sex' char(1) DEFAULT NULL,
'email' varchar(45) DEFAULT NULL,
'phone' varchar(45) DEFAULT NULL,
'qq' varchar(32) DEFAULT NULL,
'status' varchar(32) DEFAULT NULL comment '用户状态',
'create_time' datetime NOT NULL,
'update_time' datetime DEFAULT NULL,
PRIMARY KEY('id'),
UNIQUE KEY 'unique_user_username'('username')
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
当使用load命令导入数据的时候，适当的设置可以提高导入的效率。
引擎使用的是Innodb，Innodb底层使用的索引是B+树索引；而B+树索引当中的数据有顺序的；
对于Innodb类型的表，有以下集中方式可以提高导入的效率：
（1） 主键顺序插入
因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高 导入数据的 效率。
如果InnoDB表 没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。
脚本文件介绍：
sql1.log -----> 主键有序
sql2.log -----> 主键无序
插入ID顺序排列数据：
load data local infile '/root/sql1.log' into table 'tb_user_1' fields terminated by ',' lines terminated by '\n';
# 耗费时长：20.58 second
select count(*) from tb_user_1;
load data local infile '/root/sql2.log' into table 'tb_user_2' fields terminated by ',' lines terminated by '\n';
#耗费时长：1min 59.29 second
select count(*) from tb_user_2;
#sql1.log当中数据有序进行排列；sql2.log当中数据无序进行排列；
#而sql1.log当中的数据将插入到tb_user_1表；sql2.log当中的数据将插入到tb_user_2表当中；
#sql1.log与sql2.log当中数据量完全一致；只数据排列分布顺序不一致；
head sql1.log #linux 查看sql1.log前一部分的数据内容，id主键数据有序
head sql2.log #linux 查看sql2.log前一部分的数据内容，主键数据无序
load data local infile '/root/sql1.log' into table 'tb_user_1' fields terminated by ',' lines terminated by '\n';
# load data 加载数据
#local infile 从本地文件系统当中加载数据；
#'/root/sql1.log' 数据文件在linux当中的位置
# into table 'tb_user_1' 加载数据到具体表tb_user_1当中
# fields terminated by ',' 每一个域之间使用逗号,进行分隔
# lines terminated by '\n' 每一行之间使用\n进行分隔,也就是换行
# 回车之后，在导入数据的过程当中，还需要去进行维护索引，维护唯一索引；unique；
# sql1.log导入到tb_user_1表当中耗费时间为19.37 second
select count(*) from tb_user_1;# 1000000
load data local infile '/root/sql2.log' into table 'tb_user_2' fields terminated by ',' lines terminated by '\n';
#回车之后，耗费的时间为 1min 51.42 second；和sql1.log导入tb_user_1时差距很大；
#所以这样就验证了大批量导入数据时，有序数据导入表当中时耗费的时间小于无序数据导入表当中的时间；
select count(*) from tb_user_2;# 1000000
（2） 关闭唯一性校验
在导入数据前执行 SET UNIQUE_CHECKS=0 ，关闭唯一性校验，
在导入结束后执行 SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
#在tb_user_1表结构当中存在username字段的唯一性索引；如果存在唯一性索引则在插入数据的时候是需要对唯一性字段数据的取值进行校验的；
SET UNIQUE_CHECKS=0;
load data local infiles '/root/sql1.log' into table 'tb_user_1' fields terminated by ',' lines terminated by '\n';
#耗费时长为 19.39 second
SET UNIQUE_CHECKS=1;
（3） 手动提交事务
如果应用使用自动提交的方式，
建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，
导入结束后再执行 SET AUTOCOMMIT=1；打开自动提交，也可以提高导入的效率。
SET AUTOCOMMIT=0;
load data local infiles '/root/sql1.log' into table 'tb_user_1' fields terminated by ',' lines terminated by '\n';
#耗费时长为 19.58 second
select count(*) from tb_user_1;
SET AUTOCOMMIT=1;
SQL优化 - insert优化
优化insert语句
当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。
-
如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。
使得效率比分开执行的单个insert语句块。
示例，原始方式为：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 (1,'tom'),(2,'cat'),(3,'Jerry'); -
在事务中进行数据插入
start transaction; insert into tb_test values(1,'tom'); insert into tb_test values(2,'cat'); insert into tb_test values(3,'Jerry'); commit; -
数据有序插入
insert into tb_test values(4,'Tim'); insert into tb_test values(1,'tom'); insert into tb_test values(2,'cat'); insert into tb_test values(5,'Rose'); insert into tb_test values(3,'Jerry');优化后：
insert into tb_test values(1,'Tim'); insert into tb_test values(2,'tom'); insert into tb_test values(3,'cat'); insert into tb_test values(4,'Rose'); insert into tb_test values(5,'Jerry');
SQL优化 - orderby优化
优化 order by 语句
环境准备
CREATE TABLE 'emp'(
'id' int(11) NOT NULL AUTO_INCREMENT,
'name' varchar(100) NOT NULL,
'age' int(3) NOT NULL,
'salary' int(11) DEFAULT NULL,
PRIMARY KEY('id')
)ENGINE=InnoDB DEAFULT CHARSET=utf8mb4;
#基础数据插入
.....
create index idx_emp_age_salary on emp(age,salary);#建立age、salary字段的复合索引
show index from emp;
#PRIMARY 主键索引
#idx_emp_age_salary age 复合索引
#idx_emp_age_salary salary 复合索引
两种排序方式
（1） 第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫做 FileSort排序。
explain select * from emp order by age;#key:NULL；key_len:NULL；Extra：Using filesort;type:ALL 没有走索引，全表扫描（升序）
explain select * from emp order by age,salary;#key:NULL；key_len:NULL；Extra：Using filesort;type:ALL 没有走索引，全表扫描（升序）
explain select * from emp order by age desc;#key:NULL；key_len:NULL；Extra：Using filesort;type:ALL 没有走索引，全表扫描（降序）
explain select * from emp order by age desc,salary desc;#key:NULL；key_len:NULL；Extra：Using filesort;type:ALL 没有走索引，全表扫描（降序）
#通过file sort 文件系统进行排序，效率较低
explain select * from emp order by age desc;#type：ALL，key：NULL；key_len:NULL
explain select * from emp order by age asc;#type：ALL，key：NULL；key_len:NULL
（2） 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。
explain select id from emp order by age asc;#走索引；key：idx_age_salary；key_len:9；Extra：Using index 走索引
explain select id,age from emp order by age asc;#走索引；key：idx_age_salary；key_len:9；Extra：Using index 走索引
explain select id,age,salary from emp order by age asc;#走索引；key：idx_age_salary；key_len:9；Extra：Using index 走索引
#覆盖索引的用法；查询select 的字段都是覆盖索引则这个时候使用的即为using index
explain select id,age,salary,name from emp order by age asc;#走索引；key：NULL；key_len:NULL；type:ALL；Extra：Using filesort 全表扫描；由于查询的字段当中包含了非索引字段；即并未覆盖索引；所以走全表扫描的方式；id、age、salary这三个字段都有索引；而name字段没有索引；
多字段排序
explain select id ,age,salary from emp order by age,salary;#key:idx_age_salary;key_len:9;Extra:Using index
explain select id ,age,salary from emp order by age asc,salary asc;#key:idx_age_salary;key_len:9;Extra:Using index(order by 如不标明desc或者asc;默认为asc升序排序)
explain select id,age,salary from emp order by age desc,salary desc;#key:idx_age_salary;key_len:9;Extra:Using index
explain select id,age,salary from emp order by age asc,salary desc;#key:idx_age_salary;key_len:9;Extra:Using index;Using filesort此时就会出现Filesort
# 即在使用order by进行多字段排序的时候，如果想要效率较高；那么此时最好这些多字段统一排序方式；要么都是升序要么都是降序；如果出现一升一降那么就会出现Filesort；文件系统排序造成效率低；
explain select id,age,salary from emp order by salary asc,age asc;#key:idx_age_salary;key_len:9;Extra:Using index;Using filesort；此时将age asc进行了调后处理；也出现了Filesort
#原因在于：
#按照多字段进行排序，要么统一进行升序asc要么统一进行降序排序；
#如果按照多字段进行排序，那么排序的顺序需要和复合索引当中的字段顺序要保持一致；
#复合索引为idx_emp_age_salary（age，salary）复合索引当中age字段的索引顺序先于salary字段的顺序；
#而此时SQL当中order by语句多字段排序salary字段先于age字段；所以此时Extra当中就出现了using index；也出现了using filesort;
了解了MySQL的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。
where条件和Order by 使用相同的索引并且Order by 的顺序和索引顺序相同，并且Order by的字段都是升序，或者都是降序。
否则肯定需要额外的操作，这样就会出现FileSort排序；
Filesort的优化
尽可能避免Filesort的出现，但是无法完全避免Filesort的出现；
通过创建合适的索引，能够减少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';
| Variable_name | Value |
|---|---|
| max_length_for_sort_data | 1024 |
show variables like 'sort_buffer_size';
| Variable_name | Value |
|---|---|
| sort_buffer_size | 262144 |
SQL优化 - group by优化
由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY相比，GROUP BY主要只是多了排序字后的分组操作。
当然如果在分组的时候还是用了其他的一些聚合函数，那么还需要一些聚合函数的计算。
所以，在GROUP BY的实现过程中，与ORDER BY一样也可以利用到索引。
如果查询包含GROUP BY，但是用户想要避免排序结果的消耗，则可以执行order by null 禁止排序。
如下：
show index from emp;
drop index idx_emp_age_salary on emp;
explain select age,count(*) from emp group by age;#key:NULL,key_len:NULL;type:ALL;Extra:Using temporary;Using filesort; 由此可以看出GROUP BY 底层进行了排序操作；Using filesort
#Using temporary与Using Filesort都是比较耗时的操作；
优化后：
explain select age,count(*) from emp group by age order by null;#key:null;key_len:null;type:All;Extra:Using temporary;此时就没有Using filesort了
从上面的例子可以看出，第一个SQL语句需要进行“Filesort”，而第二个SQL语句由于order by null不需要进行“Filesort”，而上文提过FileSort往往非常耗费时间。
创建索引：
create index idx_emp_age_salary on emp(age,salary);
explain select age,count(*) from emp group by age order by null;#key:idx_emp_age_salary;key_len:9;extra:using index 此时走索引；
#group by语句也是可以通过索引来提高效率的；
SQL优化 - 子查询优化
优化嵌套查询
MySQL4.1 版本之后，开始支持SQL的子查询。
这个技术可以使用select语句来创建一个单列的查询结果，然后吧这个结果来作为过滤条件用在另一个查询当中。
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。
但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。(使用多表连接查询来替换子查询)
示例，查找有角色的所有用户信息：
show tables;# t_user 用户表 , t_role 角色表 , user_role 用户角色中间表 ；
explain select * from t_user where id in ( select user_id from user_role);
执行计划为：
explain select * from t_user where id in (select user_id from user_role);
| id | select_type | table | type | possible_key | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | t_user | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where |
| 1 | SIMPLE | < subquery2> | eq_ref | < auto_key> | < auto_key> | 99 | demo_02.t_user.id | 1 | NULL |
| 2 | MATERIALIZED | user_role | index | fk_ur_user_id | fk_ur_user_id | 99 | NULL | 6 | Using index |
优化后：
explain select * from t_user u,user_role ur where u.id=ur.user_id;#多表联查替代子查询
| id | select_type | table | type | possible_key | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | u | ALL | PRIMARY | NULL | NULL | NULL | 6 | NULL |
| 1 | SIMPLE | ur | ref(ref的效率比index效率高) | fk_ur_user_id | fk_ur_user_id | 99 | demo_02.u.id | 1 | NULL |
SQL优化 - or优化
对于包含 OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引，而且不能使用到复合索引；如果没有索引，则应该考虑增加索引。
获取emp表中的所有的索引；
show index from emp;
示例：
show index from emp;
select * from emp;
explain select * from emp where id=1 or name='Tom;#id为主键，即存在主键索引；而name字段没有设置索引；所以此时用or来连接两个条件时，当中包含有一个非索引字段；所以不会走索引,索引失效，采用的是全表扫描；key:NULL;key_len:NULL;type:ALL;extra:Using where；or关键字如果想要走索引则or连接的条件列字段需要是索引列；从而能够走索引否则全表扫描不走索引；
explain select * from emp where age=20 or salary=3500;#key:NULL,key_len:NULL;type;All;extra:Using where;此时未走索引；age与salary为复合索引当中的索引列；而or关键字所连接的条件列当中字段的索引不能为复合索引列；可以为主键索引列、唯一索引列、单列索引等；就是不能为复合索引列其他皆可；
explain select * from emp where id=1 or age=30;
| id | select_type | table | type | possible_key | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | t_user | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where |
| 1 | SIMPLE | emp | index_merge | PRIMARY,idx_emp_age_salary | PRIMARY,idx_emp_age_salary | 4,4 | NULL | 2 | Using sort_union(idx_emp_age_salary,PRIMARY);Using where |
explain select * from emp where id=1 or id=10 \G;
| id | select_type | table | type | possible_key | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
建议使用 union 替换 or；
优化：
explain select * from emp where id =1 union select * from emp where id =10;
| id | select_type | table | type | possible_key | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | emp | const(const效率远高于range) | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 2 | UNION | emp | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| NULL | UNION RESULT | < union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
explain select * from emp where id=1 or age=20;
#id 1
#select_type SIMPLE
#table emp
#possible_key PRIMARY,idx_emp_age_salary
#key idx_emp_age_salary,PRIMARY
#key_len 4,4
#ref NULL
#rows 2
#extra Using sort_union(idx_emp_age_salary,PRIMARY)
#type index_merge
优化：
explain select * from emp where id=1 union select * from emp where age=20;
#union即为一个求并集的一个操作；
| id | select_type | table | type | possible_key | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | emp | const(const效率远高于range) | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 2 | UNION | emp | ref(ref比index_merge效率高) | idx_emp_age_salary | idx_emp_age_salary | 4 | const | 1 | NULL |
| NULL | UNION RESULT | < union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
建议通过union替换or；
来比较下重要指标，发现主要差别是type和ref这两项；
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > uniqe_subquery > index_subquery > range > index > ALL
UNION语句的type值为ref，OR语句的type值为range，可以看到这是一个很明显的差距
UNION语句的ref值为const，OR语句的type值为NULL，const表示是常量值引用，非常快
这两项的差距就说明了UNION要优于OR；
SQL优化 - limit优化
优化分页查询
一般分页查询时，通过创建覆盖索引能够比较好的提高性能。
一个常见又非常头疼的问题就是 limit 2000000,10; 此时需要MySQL排序前2000010记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大。
explain select * from tb_item limit 2000000,10;
| id | select_type | table | type | possible_key | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | tb_item | ALL | NULL | NULL | NULL | NULL | 9816098 | NULL |
优化思路一
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
slect count(*) from tb_item;#3000000
select * from tb_item limit 10;
select * from tb_item limit 10,10;
select * from tb_item limit 20,10;
select * from tb_item limit 2000000,10;#查询效率低 此时为全表扫描没有使用得到索引；
explain select * from tb_item t, (select id from tb_item order by id limit 2000000,10) a where t.id=a.id;
| id | select_type | table | type | possible_key | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | < derived2> | ALL | NULL | NULL | NULL | NULL | 2000010 | NULL |
| 1 | PRIMARY | t | eq_ref | PRIMARY | PRIMARY | 4 | a.id | 1 | NULL |
| 2 | DERIVED | tb_item | index | NULL | PRIMARY | 4 | NULL | 2903274 | Using index |
优化思路二
该方案适用于主键自增的表(且主键自增的列不能够出现断层)，可以把limit 查询转换成某个位置的查询；
断层：即指的是当在查询数据时，id主键自增列当中可能会有出现修改或者删除的操作，那么这个时候数据就不一定准确了；
explain select * from tb_item where id > 1000000 limit 10;
| id | select_type | table | type | possible_key | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | tb_item | range | PRIMARY | PRIMARY | 4 | NULL | 4908049 | Using where |
SQL优化 - 索引提示
使用SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
USE INDEX
在查询语句中 表名称的后面 ，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。
select * from tb_seller;
show index from tb_seller;
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);
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
explain select * from tb_seller where name='小米科技';
| id | select_type | table | type | possible_key | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | tb_seller | ref | idx_seller_name_sta_address,idx_seller_name | idx_seller_name(数据库选择使用idx_seller_name索引) | 403 | const | 1 | Using index condition |
explain select * from tb_seller use index(idx_seller_name_sta_address) where name ='小米科技';
| id | select_type | table | type | possible_key | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | tb_seller | ref | idx_seller_name_sta_address | idx_seller_name_sta_address | 403 | const | 1 | Using index condition |
explain select * from tb_seller use index(idx_seller_name) where name ='小米科技';
| id | select_type | table | type | possible_key | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | tb_seller | ref | idx_seller_name | idx_seller_name | 403 | const | 1 | Using index condition |
IGNORE INDEX
如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint；
explain select * from tb_seller ignore index(idx_seller_name) where name ='小米科技';
| id | select_type | table | type | possible_key | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | tb_seller | ref | idx_seller_name_sta_address | idx_seller_name_sta_address | 403 | const | 1 | Using index condition |
explain select * from tb_seller ignore index(idx_seller_name_sta_address) where name='小米科技';
| id | select_type | table | type | possible_key | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | tb_seller | ref | idx_seller_name | idx_seller_name | 403 | const | 1 | Using index condition |
FORCE INDEX
为了强制MySQL使用一个特定的索引，可在查询中使用 force index 来作为 hint;
create index idx_seller_address on tb_seller(address);
explain select * from tb_seller where address='北京市';#实际没有使用到索引；type:all;key:null;key_len:null;extra:using where使用的是全表扫描；原因在于address为北京市时在表当中占比较多所以由mysql底层判断决定不走索引即全表扫描会快于走索引；所以此处没有使用得到索引；
explain select * from tb_seller use index(idx_seller_address) where address='北京市';#此时则走索引
| id | select_type | table | type | possible_key | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | tb_seller | ALL | idx_seller_address | idx_seller_address | NULL | NULL | 12 | Using where |
1091
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
