MySQL高级学习(二)-MySQL体系结构、SQL优化、索引使用
一、MySQL的体系结构概览
整个MySQL Server由以下组成
Connection Pool : 连接池组件
Management Services & Utilities : 管理服务和工具组件
SQL Interface : SQL接口组件
Parser : 查询分析器组件
Optimizer : 优化器组件
Caches & Buffers : 缓冲池组件
Pluggable Storage Engines : 存储引擎
File System : 文件系统
1.1、连接层
最上层是一些客户端和链接服务,包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。
主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
1.2、服务层
第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询(Caches & Buffers),SQL的分析和优化(Optimizer),部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如 过程、函数等。在该层,服务器会解析查询并创建相应的内部解析树,并对其完成相应的优化如确定表的查询的顺序,是否利用索引等, 最后生成相应的执行操作。如果是
select语句,服务器还会查询内部的缓存,如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统的性能。
1.3、引擎层
存储引擎层, 存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能,这样我们可以根据自己的需要,来选取合适的存储引擎。
1.4、存储层
数据存储层,主要是将数据存储在文件系统之上,并完成与存储引擎的交互。
和其他数据库相比,MySQL有点与众不同,它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上,插件式的存储引擎架构,将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
二、存储引擎
2.1、存储引擎概述
和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。
存储引擎就是存储数据,建立索引,更新查询数据等等技术的实现方式 。
存储引擎是基于表的,而不是基于库的。所以存储引擎也可被称为表类型。
存储引擎负责数据的存储和提取,它是负责与文件打交道的子系统。
在使用数据库可视化工具创建库时,不会要求你选择存储引擎,而在创建表时,会让你选择存储引擎。
Oracle,SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎,可以根据需要使用相应引擎,或者编写存储引擎。
MySQL5.0支持的存储引擎包含 : InnoDB(5.5之后,MySQL默认的存储引擎) 、MyISAM(5.5之前,MySQL默认的存储引擎) 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等,其中InnoDB和BDB提供事务安全表,其他存储引擎是非事务安全表。
1、查看当前数据库支持的存储引擎
使用指令
show engines
来查看
1 | show engines; |
从上面搜索到的结果可以看到,MySQL默认支持的存储引擎是 InnoDB,在建表时如果不加以指定,那么默认使用 InnoDB 作为存储引擎。
InnoDB 支持 事务、行级锁 和 外键。
MySQL5.5之前的默认存储引擎是MyISAM,5.5之后就改为了InnoDB。
2、查看默认的存储引擎
使用指令
show variables like '%storage_engine%'
查看
1 | show variables like '%storage_engine%'; |
3、常见存储引擎介绍
下面介绍几种常用的存储引擎,和各引擎间区别,如下表
特点 | InnoDB | MyISAM | MEMORY | MERGE | NDB |
---|---|---|---|---|---|
存储限制 | 64TB | 有 | 有 | 没有 | 有 |
事务安全 | 支持 | ||||
锁机制 | 行锁(适合高并发) | 表锁 | 表锁 | 表锁 | 行锁 |
B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
哈希索引 | 支持 | 支持 | |||
全文索引 | 支持(5.6 版本之后) | 支持 | |||
集群索引 | 支持 | ||||
数据索引 | 支持 | 支持 | 支持 | ||
索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
数据可压缩 | 支持 | ||||
空间使用 | 高 | 低 | N/A | 低 | 低 |
内存使用 | 高 | 低 | 中等 | 低 | 高 |
批量插入速度 | 低 | 高 | 高 | 高 | 高 |
支持外键 | 支持 |
2.2、InnoDB 存储引擎
1、概述
在 MySQL 5.5 之后, InnoDB 替代了原来的 MyISAM 成为了 MySQL 默认的存储引擎,它具有以下几个特点
- 支持事务
InnoDB 存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全
写效率较低,同时占用的空间较大(相比 MyISAM)
支持行级锁
支持外键
2、InnoDB 支持事务
- 创建一张表用于演示 InnoDB 存储引擎的特性–事务控制。
1 | create table goods_innodb |
手动开启事务,打开两个cmd窗口,链接上数据库
1 | mysql -u root -p你的密码 |
在第一个窗口中输入以下命令
1 | start transaction; |
此时使用第二个窗口查看表数据,发现没有
id
为2的数据
在第一个窗口中提交事务,再次查看第二个窗口中数据库表数据
1 | commit; |
3、InnoDB 支持外键
- 创建两张表用于演示 InnoDB 存储引擎的特性–支持外键。
MySQL支持外键的存储引擎只有InnoDB , 在创建外键的时候, 要求父表必须有对应的索引 , 子表在创建外键的时候, 也会自动的创建对应的索引。
下面两张表中 , country_innodb是父表 , country_id为主键索引,city_innodb表是子表,country_id字段为外键,对应于country_innodb表的主键country_id 。
1 | create table country_innodb |
在删除主表信息时,如果子表中有和待删除数据关联的数据,那么主表不能删除
试着删除主表中
id
为2的数据
1 | delete from country_innodb where country_id = 2; |
控制台结果为:
在创建索引时, 可以指定在删除、更新父表时,对子表进行的相应操作,包括 RESTRICT、CASCADE、SET NULL和 NO ACTION。
RESTRICT和NO ACTION相同, 是指限制在子表有关联记录的情况下, 父表不能更新;
CASCADE表示父表在更新或者删除时,更新或者删除子表对应的记录;
SET NULL 则表示父表在更新或者删除的时候,子表的对应字段被SET NULL 。
针对上面创建的两个表, 子表的外键指定是 ON DELETE RESTRICT ON UPDATE CASCADE 方式的, 那么在主表删除记录的时候, 如果子表有对应记录, 则不允许删除, 主表在更新记录的时候, 如果子表有对应记录, 则子表对应更新 。
- 下面演示 UPDATE CASCADE ,将
id
为1的记录的id
设置为100
1 | update country_innodb set country_id = 100 where country_id = 1; |
此时再次查看两张表中的数据,发现子表与父表关联的数据也被修改了。
4、InnoDB 的存储方式
InnoDB 存储表和索引有以下两种方式:
- 使用共享表空间存储, 这种方式创建的表的表结构保存在.frm文件中, 数据和索引保存innodb_data_home_dir 和 innodb_data_file_path定义的表空间中,可以是多个文件。
- 使用多表空间存储, 这种方式创建的表的表结构仍然存在 .frm 文件中,但是每个表的数据和索引单独保存在.ibd 中。
5、什么时候使用 InnoDB?
- InnoDB 是 MySQL 的默认存储引擎,用于事务处理应用程序,支持外键和行级锁。
如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询意外,还包含很多的更新、删除操作,那么InnoDB 存储引擎是比较合适的选择。
- InnoDB 支持事务
InnoDB 存储引擎除了有效的降低由于删除和更新导致的锁定, 还可以确保事务的完整提交和回滚,对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统,InnoDB是最合适的选择。
- 开发中绝大部分使用的都是InnoDB
2.3、MyISAM 存储引擎
1、概述
与 InnoDB 相比,MyISAM 的优势是访问的速度快,一些对事务完整性没有要求或者以
SELECT
、INSERT
为主的应用基本上都可以使用 MyISAM 引擎来创建表。它有两个比较重要的特点
- 不支持事务
- 文件存储方式特殊
- 不支持行级锁,只支持表锁
2、不支持事务
创建一张表,在建表语句中指定存储引擎为MyISAM
1 | create table goods_myisam |
同上,使用两个cmd窗口,在第一个窗口中使用
start transaction
开启事务,插入一条数据
在第二个窗口查看表数据,发现已经查询到了未提交的数据,故
myisam
不支持事务。
1 | select * from `index_study`.goods_myisam; |
3、文件存储方式
每个MyISAM在磁盘上存储成3个文件,其文件名都和表名相同,但拓展名分别是:
- .frm (存储表定义);
- .MYD(MYData , 存储数据);
- .MYI(MYIndex , 存储索引);
4、什么时候使用 MyISAM
- 如果应用以读操作和插入操作为主,且只有少量的更新和删除操作,那么可以考虑使用 MyISAM
- 如果应用对 事务的完整性、并发性要求不是很高,那么可以考虑 MyISAM
2.4、Memory 存储引擎
Memory 存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件,格式是.frm ,该文件中只存储表的结构,而其数据文件,都是存储在内存中,这样有利于数据的快速处理,提高整个表的效率。
Memory 类型的表访问非常地快,因为他的数据是存放在内存中的,并且默认使用 HASH 索引 ,但是服务一旦关闭,表中的数据就会丢失。
三、SQL优化(一)
在应用的的开发过程中,由于初期数据量小,开发人员写 SQL 语句时更重视功能上的实现,但是当应用系统正式上线后,随着生产数据量的急剧增长,很多 SQL 语句开始逐渐显露出性能问题,对生产的影响也越来越大,此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈,因此我们必须要对它们进行优化。
当面对一个有 SQL 性能问题的数据库时,我们应该从何处入手来进行系统的分析,使得能够尽快定位问题 SQL 并尽快解决问题。
3.1、查看SQL执行频率
MySQL 客户端连接成功后,通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级(当前连接)的计结果和
global 级(自数据库上次启动至今)的统计结果。如果不写,默认使用参数是“session”。
1、查询当前会话中所有统计参数值
下面的命令显示了当前 session 中所有统计参数的值:
1 | show status like 'Com_______'; |
2、查询全局统计参数值
上面的命令查询的只是当前链接的统计参数,如果要统计全局参数,我们可以在 status 前添加一个
global
参数
1 | show global status like 'Com_______'; |
3、查询InnoDB存储引擎的操作数量
1 | show status like 'Innodb_rows_%'; |
输出结果如下
InnoDB
插入的行:8
InnoDB
查询的行:20
InnoDB
更新的行:3
InnoDB
删除的行:0
通过上面的几条指令,就可以大致确定当前数据库到底是以读为主还是以写为主。
4、参数说明
参数 | 含义 |
---|---|
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 存储引擎的,累加的算法也略有不同。
3.2、定位低效率执行SQL
可以通过以下两种方式定位执行效率较低的 SQL 语句。
- 慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句,用–log-slow-queries[=file_name]选项启动时,mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。
- show processlist : 慢查询日志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题,可以使用show processlist命令查看当前MySQL在进行的线程,包括线程的状态、是否锁表等,可以实时地查看 SQL 的执行情况,同时对一些锁表操作进行优化。
1、使用show processlist 指令连接和执行操作
1 | show processlist; |
查看执行效果
查看
Info
列信息
1 | /* ApplicationName=DataGrip 2020.2.2 */ 操作数据库的应用,我这里用的是`DATAGRIP` |
上述参数说明
- id列,用户登录mysql时,系统分配的”connection_id”,可以使用函数connection_id()查看
- user列,显示当前用户。如果不是root,这个命令就只显示用户权限范围的sql语句
- host列,显示这个语句是从哪个ip的哪个端口上发的,可以用来跟踪出现问题语句的用户
- db列,显示这个进程目前连接的是哪个数据库
- command列,显示当前连接的执行的命令,一般取值为休眠(sleep),查询(query),连接
(connect)等 - time列,显示这个状态持续的时间,单位是秒
- state列,显示使用当前连接的sql语句的状态,很重要的列。state描述的是语句执行中的某一个状态。一
个sql语句,以查询为例,可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成 - info列,显示这个sql语句,是判断问题语句的一个重要依据
2、使用show processlist实时监控慢查询sql
在一张大数据表中,执行一条慢查询sql,然后快速使用
show processlist
查看,结果如下
3.3、Explain分析执行计划
通过以上步骤查询到效率低的 SQL 语句后,可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
1、使用 explain 查询SQL语句的执行计划
explain 要查询的SQL语句;
这里以视频给的数据库表为例
1 | explain select * from tb_item; |
结果
说明
字段 | 含义 |
---|---|
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 | 索引字段的长度 |
rows | 扫描行的数量 |
extra | 执行情况的说明和描述 |
2、环境准备
准备三张表,表与表之间的关系如下
1 | CREATE TABLE `t_role` |
3、explain 之 id
id 字段是 select查询的序列号,是一组数字,表示的是查询中执行select子句或者是操作表的顺序。
id 情况有三种:
- id 相同表示加载表的顺序是从上到下
这里以一条多表查询语句为例
1 | EXPLAIN SELECT * |
查看结果,可以看到
ur
表的加载顺序高于tr
表和u
表,第一列不是id
。。。
- id 不同id值越大,优先级越高
越先被执行,这里以一条子查询语句为例
1 | EXPLAIN SELECT * |
执行语句查看结果,最内层的子查询的执行优先级最高。
- id 有相同,也有不同,同时存在。
id相同的可以认为是一组,从上往下顺序执行;在所有的组中,id的值越大,优先级越高,越先执行。
这里以一条带子查询和连接查询的sql为例
1 | EXPLAIN SELECT * |
查看结果,发现先执行子查询,后执行连接查询。
4、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 |
5、explain 之 table
展示这一行的数据是关于哪一张表的
6、explain 之 type
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 | 将遍历全表以找到匹配的行 |
- type 为
null
的情况,例如使用select now()
访问当前时间
1 | explain select now(); |
查看结果
- type 为
system
的情况,数据源中只有一条数据时出现 - type 为
const
的情况,使用t_user
表中的 主键索引 和 唯一索引 查看
1 | explain select * from t_user where username = 'super'; |
type为
eq_ref
的情况,使用主键的关联查询,关联查询出的记录只有一条;type为
index
的情况,查询整个索引树,效率通常不高type为
all
的情况,遍历整个数据文件。
一般来说,我们需要保证查询至少达到
range
级别,最好达到ref
。
7、explain 之 key
- possible_keys : 显示可能应用在这张表的索引, 一个或多个。
- key : 实际使用的索引, 如果为NULL, 则没有使用索引。
- key_len : 表示索引中使用的字节数, 该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下, 长度越短越好 。
- possible_keys : 显示可能应用在这张表的索引, 一个或多个。
- key : 实际使用的索引, 如果为NULL, 则没有使用索引。
8、explain 之 rows
扫描行的数量。
9、explain 之 extra
其他的额外的执行计划信息,在该列展示 。
优化前两个,保持第三个。
extra | 含义 |
---|---|
using filesort | 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取, 称为“文件排序”, 效率低。 |
using temporary | 使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于 order by 和group by; 效率低 |
using index | 表示相应的select操作使用了覆盖索引, 避免访问表的数据行, 效率不错。索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了。 |
Using where | 表明使用了where过滤 |
using join buffer | 使用了连接缓存: |
impossible where | where子句的值总是false,不能用来获取任何元组 |
10、explain之case
执行顺序:4-3-2-1-NULL
- 执行顺序4
3.4、show profile分析SQL
Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
1、查看当前MySQL是否支持profile
通过 have_profiling 参数,能够看到当前MySQL是否支持profile:
1 | select @@have_profiling; |
默认profiling是关闭的,可以通过set语句在Session级别开启profiling:
1 | set profiling = 1; |
此时查看
profiling
的值。
1 | select @@profiling; |
2、使用 show profiles 指令查看sql语句和耗时
其中
Query
为执行的SQL语句,而Duration
为该SQL执行耗费的时间。
在上面查询出来的结果之上,可以通过
show profile for query id值
指令来查看某条sql语句的详细信息,例如查看id
为5的查询语句的详细信息。
1 | show profile for query 5; |
发现
Sending data
阶段花费了最多时间;Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端,而不仅仅是返回个客户端。
由于在Sending data状态下,MySQL线程往往需要做大量的磁盘读取操作,所以经常是整个查询中耗时最长的状态。
3.5、trace分析优化器执行计划
MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。
1、打开trace 并设置格式和内存大小
打开trace , 设置格式为 JSON,并设置trace最大能够使用的内存大小,避免解析过程中因为默认内存过小而不能够完整展示。
1 | SET optimizer_trace="enabled=on",end_markers_in_json=on; |
2、执行SQL语句
1 | select * from where id < 4; |
3、查看Mysql如何执行上述的SQL语句
检查
infomation_schema
.optimizer_trace
就可以知道MySQL是如何执行SQL的
1 | select * from information_schema.optimizer_trace\G; |
四、索引的使用
索引是数据库优化最常用也是最重要的手段之一,通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。
4.1、验证索引提升查询效率
在我们准备的表结构tb_item 中, 一共存储了 300 万记录;
1、根据ID查询
1 | select * from tb_item where id = 1999\G; |
查询速度很快, 接近0s , 主要的原因是因为id为主键, 有索引;
2、根据title进行精确查询
1 | select * from tb_item where title = 'iphoneX 移动3G 32G941'\G; |
执行sql语句,发现返回时间长达9s
使用
explain
查看SQL语句的执行计划 :
3、优化查询,为title添加索引
1 | create index idx_item_title on tb_item(title); |
创建索引可能会花费一些时间,这是因为MySQL底层正在为3000000条数据创建索引。
索引创建完成之后,再次进行查询
再次通过explain查看SQL语句的执行计划,发现执行SQL时使用了刚才创建的索引
4.2、索引的使用
1、准备环境
1 | use index_study; |
2、避免索引失效–全值匹配
全值匹配,对索引中的所有列都指定具体值。
该情况下,索引生效,执行效率高。
1 | explain select from tb_seller where name='小米科技' and status='1' and address='北京市'\G; |
使用
explain
查看上述SQL的执行计划:
3、避免索引失效–最左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始,并且不跳过中间的列,
如果跳过中间列,那么只会使用最左列的索引。
上述的索引加在了(name, status, address)中,那么查询就必须从
name
开始且不能跳过status
直接跳到address
- 匹配最左前缀法则,走索引:
1 | explain select * from tb_seller where name='小米科技' and status='1'; |
- 违背最左前缀法则,索引失效
跳过name直接查询status/address,索引失效
1 | explain select * from tb_seller where status = '1'; |
跳过索引列中间的status直接到address,此时只有最左边的索引列(name)生效
1 | explain select * from tb_seller where name = '小米科技' and address = '西安市'; |
不使用索引的最左列进行查询,索引失效
1 | explain select * from tb_seller where status = '1' and address = '西安市'; |
索引失效与否与
where
子句的组装顺序无关。
3、避免索引失效–范围查询右边的列,不能使用索引
范围查询右边的列将不走索引,例如
status
使用了范围查询,那么只走name
和status
的索引,不走address
的索引。
1 | explain select * from tb_seller where name = '小米科技' and status > '1' and address = '西安市'; |
4、避免索引失效–不要在索引列上进行运算操作
如果在索引列上进行运算操作,那么索引将失效
1 | explain select * from tb_seller where substring(name,3,2) = '科技'; |
在status列上进行运算
1 | explain select * from tb_seller where name = '小米科技' and status + 1 = '2'; |
5、避免索引失效–字符串必须加单引号
字符串不加单引号,造成索引失效。
1 | explain select * from tb_seller where name = '小米科技' and status = '1'; |
演示不加单引号的情况
1 | explain select * from tb_seller where name = '小米科技' and status = 1; |
由于,在查询时没有对字符串加单引号,MySQL的查询优化器会自动的进行类型转换,造成索引失效。
6、尽量使用覆盖索引,避免select *
尽量使用覆盖索引(只访问索引的查询(索引列完全包含查询列)),减少select *
如果查询列,超出索引列,也会降低性能。(例如多查询了一个password会导致性能下降)
TIPS:
- using index :使用覆盖索引的时候就会出现
- using where:在查找使用索引的情况下,需要回表去查询所需的数据
- using index condition:查找使用了索引,但是需要回表查询数据
- using index ; using where:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据
7、or分隔的条件
or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。
示例,name字段是索引列 , 而
createtime
不是索引列,中间是or进行连接是不走索引的 :
1 | explain select from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'\G; |
8、以%开头的Like模糊查询,索引失效。
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
1 | explain select * from tb_seller where name like '%黑马'; |
但右%不会导致索引失效
1 | explain select * from tb_seller where name like '黑马%'; |
- 解决方法
通过覆盖索引(尽量不要使用select * ,使用索引中的那些列)来解决
1 | explain select name,status,address from tb_seller where name like '%黑马%'; |
如果查询的列中有不含索引的属性,那么索引将失效。
9、如果MySQL评估使用索引比全表更慢,则不使用索引。
为
address
创建一个单列索引
1 | create index `idx_address` on tb_seller(address); |
执行以下的 sql 语句
1 | explain select * from tb_seller where address = '北京市'; |
将上述的条件修改为
西安市
,再次执行
1 | explain select * from tb_seller where address = '西安市'; |
- 为什么?
因为在tb_seller表中
address
为 北京市 的数据占比非常大,使用索引不如进行全表扫描。
10、is NULL、is NOT NULL 有时
索引失效。
由于上面我们为
address
添加了单列索引,所以我们以address
为例。
- IS NULL
1 | explain select * from tb_seller where address is null; |
发现此时走了索引。
- IS NOT NULL
1 | explain select * from tb_seller where address is not null; |
发现此时不走索引。
- 原因
这是因为在 tb_seller 表中绝大部分数据的
address
属性都不为空,那么当使用 IS NOT NULL 的时候,由于非空的数据太多,还不如直接扫描全表。而使用 is null 时,由于为空的数据不多,所以使用索引速度快。
11、In、not In 有时
不使用索引
使用
in
进行测试
1 | explain select * from tb_seller where sellerid in ('oppo','xiaomi','sina'); |
使用
not in
进行测试,发现使用索引。
1 | explain select * from tb_seller where sellerid not in ('oppo','xiaomi','sina'); |
In、not in 使不使用索引不是一刀切的。
12、单列索引和复合索引
尽量使用复合索引,而少使用单列索引。
创建复合索引:
1 | create index idx_name_sta_address on tb_seller(name, status, address); |
相当于创建了三个索引:
name
name + status
name + status + address
- 创建单列索引,在我们使用多个带索引的字段进行搜索时,不会使用全部索引
1 | create index idx_seller_name on tb_seller(name); |
数据库会选择一个最优索引来使用。
- 演示单列索引和复合索引的区别
删除上面为
address
创建的单列索引
1 | drop index `idx_address` on tb_seller; |
此时使用复合索引进行数据查找
1 | explain select * from tb_seller where name = '小米科技' and status = '1' and address = '西安市'; |
删除表中的复合索引
1 | drop index idx_seller_name_sta_addr on tb_seller; |
创建三个单列索引
1 | create index idx_seller_name on tb_seller(name); |
再次执行上面的查询语句
1 | explain select * from tb_seller where name = '小米科技' and status = '1' and address = '西安市'; |
由于
name
列中的数据辨识度最高。
4.3、查看索引使用情况
1、查看当前连接(会话)的索引情况
1 | show status like 'Handler_read%'; |
2、查看全局索引使用情况
1 | show global status like 'Handler_read%'; |
3、属性说明
Handler_read_first:索引中第一条被读的次数。如果较高,表示服务器正执行大量全索引扫描(这个值越低
越好)。Handler_read_key:如果索引正在工作,这个值代表一个行被索引值读的次数,如果值越低,表示索引得到的
性能改善不高,因为索引不经常使用(这个值越高越好)。Handler_read_next :按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列,
该值增加。Handler_read_prev:按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY … DESC。
Handler_read_rnd :根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高,意味着运行效率低,应
该建立索引来补救。Handler_read_rnd_next:在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。
4.4、综合案例
1、环境准备
1 | create table test03 |
2、案例1
分析以下案例的索引使用情况
1 | explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3'; |
优化器会自动优化顺序
3、案例2
分析以下案例的索引使用情况
1 | explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3'; |
这里仍然用到了4个索引,因为MySQL优化器会优化顺序,将其变为a1,a2,a3,a4,虽然范围比较后的列不使用索引,但范围索引的列是使用索引的,所以使用了4个索引。
可以看到,此处的
key_len
与上面一致,都是164,所以使用了4个索引
4、案例3
分析以下案例的索引使用情况
1 | explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3; |
这里其实也使用了c3这个索引,只不过c3作用在排序而不是查找,所以没有统计进key中。
没有使用C4索引
5、案例4
1 | explain select * from test03 where c1='a1' and c2='a2' order by c4; |
使用了索引,但出现了
filesort
,因为此时没有c3,c4索引无法使用了。
6、案例5
1 | explain select * from test03 where c1='a1' and c4 = 'a4' order by c3,c2; |
这里使用了c1索引,但由于c3,c2顺序没有按建索引时的顺序排序,所以c3,c2所以失效,出现了
filesort
7、案例6
1 | explain select * from test03 where c1='a1' and c2 = 'a2' and c5 = 'a5' order by c3,c2; |
这里使用了c1,c2索引进行排序,由于c2已经有值了(已经是一个常量了),所以此时c3依然生效,此时c3索引用于排序
4.5、聚簇索引
这两者都是使用 B+ 树这种索引结构
1、聚簇索引
将数据和索引放在一起,而且是按照一定顺序组织的,找到索引就等于找到了数据。聚簇索引中,数据的物理存放顺序和索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。
2、非聚簇索引
叶子节点不存放数据,而是存放数据地址,这意味着在找到数据索引后还需要根据索引上的地址再去磁盘上取数据,类似书的目录。
3、优势
- 查询通过聚簇索引可以直接获取数据,因为聚簇索引的数据与索引存放在一起,相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率更高
- 聚簇索引对于范围查找的效率很高,因为其数据是按大小排序的
- 聚簇索引适合用在排序的场合,而非聚簇索引不适合
4、劣势
- 维护索引很昂贵,尤其是插入新行或者主键被更新导致要分页的时候。建议在大量插入数据后,选择负载降低的时间段通过
OPTIMIZE TABLE
优化表。 - 如果表使用 UUID(随机ID) 作为主键,使数据存储稀疏,这就会出现聚簇索引可能比全表扫描更慢,所以建议使用自增主键或者雪花算法生成的唯一ID作为主键
- 如果主键比较大的话,那么辅助索引将会变得更大,因为辅助索引的叶子节点存储的是主键值,过长的主键会导致叶子节点占用更多空间。
5、补充
InnoB
中一定有主键,主键一定是聚族索引,不手动设置、则会使用 unique素引,没有 unique素引,则会使用数据库内部的一个行的隐藏id来当作主键索引。在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找,非聚簇索引都是铺助索引,像复合索引、前缀索引、唯一索引,辅助索引叶子节点存储的不再是行的物理位置,而是主键值。MyISAM
使用的是非聚簇索引,没有聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。
五、SQL优化(二)
5.1、大批量插入数据
1、环境准备
创建两张表,两张表结构相同,名字不同;
tb_user_1
和tb_user_2
1 | CREATE TABLE `tb_user_2` |
对于
InnoDB
类型的表,有以下几种方式可以提高导入的效率:
- 主键顺序插入
- 关闭唯一性校验
- 手动提交事务
2、InnoDB引擎表插入优化 - 主键顺序插入
因为InnoDB类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以有效的提高导入数据的效率。如果InnoDB表没有主键,那么系统会自动默认创建一个内部列作为主键,所以如果可以给表创建一个主键,将可以利用这点,来提高导入数据的效率。
脚本文件介绍(大小相同) :
sql1.log —-> 主键有序
sql2.log —-> 主键无序
使用
load data local infile 文件名 into 表名
进行数据加载,查看结果
- 插入ID顺序排列数据:
- 插入ID无序排列数据:
2、InnoDB引擎表插入优化-关闭唯一性校验
在导入数据前执行
SET UNIQUE_CHECKS=0
,关闭唯一性校验,在导入结束后执行SET UNIQUE_CHECKS=1
,恢复唯一性校验,可以提高导入的效率。
3、InnoDB引擎表插入优化-手动提交事务
如果应用使用自动提交的方式,建议在导入前执行
SET AUTOCOMMIT=0
,关闭自动提交,导入结束后再执行SET AUTOCOMMIT=1
,打开自动提交,也可以提高导入的效率。
5.2、优化insert语句
当进行数据的insert操作的时候,可以考虑采用以下几种优化方案。
1、使用多个值表的insert语句
如果需要同时对一张表插入很多行数据时,应该尽量使用多个值表的insert语句,这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。
- 原始方案为:
1 | insert into tb_test values(1,'Tom'); |
- 优化后的方案为:
1 | insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); |
2、在执行大量的insert语句前使用事务的手动提交
如果数据量实在太大,建议分段提交。
每一万条数据
insert
一次。
1 | start transaction; |
3、数据有序插入
按照主键顺序插入
- 优化前
1 | insert into tb_test values(4,'Tim'); |
- 优化后
1 | insert into tb_test values(1,'Tom'); |
5.3、优化order by语句
1、准备环境
1 | use index_study; |
2、MySQL支持的两种排序方式
- 第一种是通过对返回数据进行排序,也就是通常说的
filesort
排序,所有不是通过索引直接返回排序结果的排序都叫FileSort
排序。 - 第二种通过有序索引顺序扫描直接返回有序数据,这种情况即为
using index
,不需要额外排序,操作效率高。
3、查询所有字段进行的排序
- 查询所有字段,根据
age
进行升序/降序排序。
1 | explain select * from emp order by age; |
和根据
salary
进行排序一样,这里进行的都是文件排序filesort
- 查询所有字段,根据
age
和salary
进行排序
1 | explain select * from emp order by age,salary; |
此时仍然为
filesort
4、查询索引字段进行的排序
如果使用 覆盖索引 ,那么就会使用索引排序。
5、查询字段中有非索引字段进行的排序
如果查询的字段中有非索引字段,例如
name
,那么将会使用filesort
6、优化目标
了解了MySQL的排序方式,优化目标就清晰了:
- 尽量减少额外的排序,通过索引直接返回有序数据。
- where 条件和Order by 使用相同的索引,并且Order By 的顺序和索引顺序相同, 并且Order by 的字段都是升序,或者都是降序。否则肯定需要额外的操作,这样就会出现
FileSort
。
7、举例
假设现有一复合索引
idx_a_b_c
(a,b,c),则以下情况可以避免filesort
order by 能使用索引最左前缀
- order by a;
- order by a,b;
- order by a,b,c;
- order by a desc,b desc,c desc;
如果where使用索引的最左前缀定义为常量,则order by可以使用索引
- where a = const order by b,c;
- where a = const and b = const order by c;
- where a = const and b > const order by c;
不能使用索引进行排序的情况
- order by a asc,b desc,c desc
上述语句排序不一致,必须要么全为desc,要么全为asc
- where g = const order by b,c
上述语句丢失a索引
- where a = const order by c
上述语句丢失b索引
- where a = const order by a,d
上述语句中,d不是索引的一部分
8、FileSort
的优化
通过创建合适的索引,能够减少
Filesort
的出现,但是在某些情况下,条件限制不能让Filesort
消失,那就需要加快Filesort
的排序操作。对于Filesort
, MySQL 有两种排序算法:
两次扫描算法 :MySQL4.1 之前,使用该方式排序。首先根据条件取出排序字段和行指针信息,然后在排序区 sort buffer 中排序,如果sort buffer不够,则在临时表 temporary table 中存储排序结果。完成排序之后,再根据行指针回表读取记录,该操作可能会导致大量随机I/O操作。
一次扫描算法:一次性取出满足条件的所有字段,然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大,但是排序效率比两次扫描算法要高。
MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小, 来判定是否那种排序算法,如果max_length_for_sort_data 更大,那么使用第二种优化之后的算法;否则使用第一种。
可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量,来增大排序区的大小,提高排序的效率。
5.4、优化group by 语句
由于GROUP BY 实际上也同样会进行排序操作,而且与ORDER BY 相比,GROUP BY 主要只是多了排序之后的分组操作。当然,如果在分组的时候还使用了其他的一些聚合函数,那么还需要一些聚合函数的计算。
所以,在 GROUP BY 的实现过程中,与 ORDER BY 一样也可以利用到索引。
如果查询包含 group by 但是用户想要避免排序结果的消耗, 则可以执行order by null禁止排序。如下 :
1、删除复合索引,查看group by是否包含排序
1 | explain select age,count(*) from emp group by age; |
2、使用order by null优化group by避免排序
1 | explain select age,count(*) from emp group by age order by null; |
从上面的例子可以看出,第一个SQL语句需要进行”filesort”,而第二个SQL由于order by null 不需要进行
“filesort”, 而上文提过Filesort往往非常耗费时间。
3、使用索引优化Group by
1 | create index idx_emp_age_salary on emp(age,salary); |
5.5、优化嵌套查询(子查询)
Mysql4.1版本之后,开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询是可以被更高效的连接(JOIN)替代。
示例 ,查找有角色的所有的用户信息 :
1、优化前
1 | explain select * from t_user where id in (select user_id from user_role ); |
2、优化后
1 | explain select * from t_user u , user_role ur where u.id = ur.user_id; |
连接( Join)查询之所以更有效率一些 ,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
5.6、优化OR条件
包含OR的查询子句,如果要利用索引,则OR之间的每个条件列都必须用到索引 , 而且不能使用到复合索引; 如果没有索引,则应该考虑增加索引。
1、获取 emp 表中的所有的索引
1 | show index from emp; |
2、使用union替换or
1 | explain select * from emp where id = '1' or id = '10'; |
1 | explain select * from emp where id = '1' union select * from emp where id = '10'; |
- 我们来比较下重要指标,发现主要差别是 type 和 ref 这两项
type 显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
UNION 语句的 type 值为 ref,OR 语句的 type 值为 range,可以看到这是一个很明显的差距
UNION 语句的 ref 值为 const,OR 语句的 type 值为 null,const 表示是常量值引用,非常快这两项的差距就说明了 UNION 要优于 OR 。
5.7、优化分页查询
一般分页查询时,通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ,此时需要MySQL排序前2000010 记录,仅仅返回2000000 - 2000010 的记录,其他记录丢弃,查询排序的代价非常大 。
1、优化思路一
在索引上完成排序分页操作,最后根据主键关联回原表查询其他所需要的列的内容
- 常规写法
1 | select * from tb_item limit 200000,10; |
- 优化后写法
1 | select t.* from tb_item as t join (select id from tb_seller order by id limit 200000,10) as a on a.id = t.id; |
- 查看优化后写法的执行计划
2、优化思路二
该方案适用于主键自增的表,可以把Limit 查询转换成某个位置的查询 。(自增的主键还不能出现断层。)
- 优化后写法
1 | explain select * from tb_item where id > 200000 limit 10; |
由于使用了索引,所以非常快
5.8、使用SQL提示
SQL提示,是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
1、USE INDEX
在查询语句中表名的后面,添加 use index 来提供希望MySQL去参考的索引列表,就可以让MySQL不再考虑其他可用的索引。
- 查看表中的索引
假设现在有如下SQL
1 | select * from tb_seller where name = '小米科技'; |
那么可能会使用两个索引,即
idx_seller_name
和idx_seller_name_sta_adds
- 查看上述SQL的执行计划,发现其使用了
idx_seller_name
索引
1 | explain select * from tb_seller where name = '小米科技'; |
- 使用 use index 人为干预
1 | explain select * from tb_seller use index(idx_seller_name_sta_adds) where name = '小米科技'; |
2、IGNORE INDEX
如果用户只是单纯的想让MySQL忽略一个或者多个索引,则可以使用 ignore index 作为 提示
- 从上面可知,如果不加以干预,Mysql使用的是
idx_seller_name
索引,我们可以使用IGNORE INDEX
让MySQL忽略idx_seller_name
索引
1 | explain select * from tb_seller ignore index(idx_seller_name) where name = '小米科技'; |
3、FORCE INDEX
为强制MySQL使用一个特定的索引,可在查询中使用 force index 作为提示 。
- 为
address
属性添加一个单列索引
1 | create index idx_address on tb_seller(address); |
- 在
tb_seller
表中执行以下语句,MySQL不会使用索引
这是因为表中
address
属性为北京市的数据占绝大多数,使用索引不如全表扫描
- 这里我们可以使用
force index
强制MySQL使用索引
1 | explain select * from tb_seller force index(idx_address) where address = '北京市'; |
4、force 和 use的区别
use index 更多的是建议,而force index是强制使用索引。
如果MySQL觉得你使用
use index
建议的索引效率比全表扫描慢,那么仍然不会走索引。如果你使用
force index
强制MySQL使用你给的索引,那么即使你给的索引执行效率低,那么MySQL也会使用你给的索引。
5.9、分解关联查询
许多高性能的应用都会对关联查询进行分解。
即可以对每一个表进行一次 单表查询 ,然后将结果在应用程序中进行关联。
例如,下面这个查询中
1 | select |
可以分解为下列这些查询来代替
1 | select * from tag where tag = 'mysql'; |
这样做地原因?
- 让缓存的效率更高。
许多应用程序可以方便地缓存单表查询对应的结果对象。
例如,上面查询中的
tag
已经被缓存了,那么应用就可以跳过第一个对象。再例如,应用中已经缓存了
post.id
为 123、456 的内容,那么第三个查询的in
就可以少几个ID
.另外,对于MySQL的查询缓存来说,如果关联的中的每个表发生了变化,那么就无法是哟查询缓存了,而拆分后,如果某个表很少改变,那么基于此表的查询就可以重复利用查询缓存。
- 将查询分解后,执行单个查询可以减少锁的竞争。
- 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可拓展。
- 查询本身效率可能有所提升
这个例子中,使用
in
代替 关联查询,可以让MySQL按照ID
顺序进行查询,这比随机的关联要更高效。
- 可以减少冗余记录的查询。
在应用层做关联查询,意味着对于某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复地访问一部分数据。从这点看,这样的重构还可能会减少网络和内存的消耗。
六、补充-尚硅谷索引优化
6.1、索引单表查询优化案例
1、环境搭建
1 | CREATE TABLE IF NOT EXISTS `article` |
2、案例
查询 category_id 为1 且 comments 大于 1 的情况下, views 最多的
article_id
。
1 | SELECT id, |
使用explain 分析以上
sql
的执行计划,发现type
为 ALL,即最坏的情况,Extra
中还出现了Using FileSort
的情况,这个时候必须进行优化。
开始优化
- 为
category_id
、comments
和views
新建一个复合索引
1 | alter table `article` add index idx_cate_comm_views (`category_id`,`comments`,`views`); |
- 再次使用 explain 分析上面的
sql
,发现使用了索引,但using filesort
依然存在
由于 范围比较 后的列索引会失效,所以
sql
的执行计划中出现了 文件排序,我们需要删除上面这个不怎么合适的索引,重新再建一个。
- 删除上面的索引。
1 | drop index idx_cate_comm_views on `article`; |
- 绕过
comments
字段,为category_id
和views
字段建一个复合索引。
1 | alter table `article` add index `idx_article_cate_views`(`category_id`,`views`); |
- 再次运行SQL语句,查看执行计划
1 | explain SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1; |
6.2、索引关联查询优化案例
1、环境准备
1 | CREATE TABLE IF NOT EXISTS `class` |
2、两表案例
下面开始explain分析
1 | EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card; |
- 给book表的card字段创建一个索引
1 | alter table book add index `idx_book_card` (`card`); |
- 此时再次进行explain分析
左连接在右表中创建索引
- 删除原有的book表中的索引,为class表的card字段创建一个索引
1 | drop index `idx_book_card` on book; |
- 再次进行explain分析
可以看到,此时的type为index,index的效率比ref低。
左连接以左表作为主表,左边数据是一定都有的,左连接从右表中选择数据添加到左表中,右边是我们的关键点,所以必须对右表建索引。
右连接同理需要对左表的字段建索引。
3、三表案例
添加新表,插入数据
1 | CREATE TABLE IF NOT EXISTS `phone` |
- 先删去上面为class创建的索引
1 | drop index `idx_book_card` on class; |
- 编写三表查询SQL
1 | SELECT * |
- 使用explain分析执行计划
- 给
phone
表和book
表各自建一个索引
1 | alter table `phone` add index `idx_phone_card`(`card`); |
- 再次使用explain分析执行计划
4、结论
- 永远用小表(结果集)驱动大表(结果集)
先了解在join连接时,哪个表是驱动表,哪个是被驱动表
- 当使用left join时,左表是驱动表,右表是被驱动表
- 当使用right join时,右表时驱动表,左表是驱动表
- 当使用join时,
mysql
会选择数据量比较小的表作为驱动表,大表作为被驱动表
- 子查询尽量不要放在被驱动表,有可能使用不到索引。
join查询在有索引条件下
驱动表有索引不会使用到索引
被驱动表建立索引会使用到索引
在以小表驱动大表的情况下,再给大表建立索引会大大提高执行速度
- 在无法保证被驱动表的Join条件字段被索引且内存资源充足的情况下,不要太吝惜
JoinButter
的设置