原创

分享:MySQL 学习笔记(一)

引用至:

链接:https://gper.club/articles/7e7e7f7ff7g55gcag69




分享一篇非常系统,内容丰富的学习笔记,篇幅很长,请耐心阅读哦。


一, MySQL 的发展历史

1.1 Mysql 版本分支

时间里程碑
1996 年MySQL 1.0 发布。 它的历史可以追溯到 1979 年, 作者 Monty 用 BASIC 设计 的一个报表工具。
1996 年 10 月3.11.1 发布。 MySQL 没有 2.x 版本。
2000 年ISAM 升级成 MyISAM 引擎。 MySQL 开源。
2003 年MySQL 4.0 发布, 集成 InnoDB 存储引擎。
2005 年MySQL 5.0 版本发布, 提供了视图、 存储过程等功能。
2008 年MySQL AB 公司被 Sun 公司收购, 进入 Sun MySQL 时代。
2009 年Oracle 收购 Sun 公司, 进入 Oracle MySQL 时代。
2010 年MySQL 5.5 发布, InnoDB 成为默认的存储引擎。
2016 年MySQL 发布 8.0.0 版本。 为什么没有 6、 7? 5.6 可以当成 6.x, 5.7 可以当 成 7.x。

Mysql 源码开源(也有收费版本)

  • MariaDB

  • Percona Server

  • InnoSQL

  • ArkDB

二,一条查询SQL语句是如何执行的?

  

2.1 通信协议

操作数据库,首先得与数据库建立连接

Mysql 是支持多种通信协议的,可以使用同步/异步的方式,支持长连接/短连接。

2.1.1 通信类型:同步或异步

  • 同步通信的特点:

1) 同步通信依赖于被调用方,受限于被调用方的性能。也就是说,应用操作数据库,线程会阻塞,等待数据库的返回。
2)一般只能做到一对一,很难做到一对多的通信
  • 异步跟同步相反:

1)异步可以避免应用阻塞等待,但是不能节省SQL执行的时间。
2)如果异步存在并发,每一个SQL的执行都要单独建立一个连接,避免数据混乱。但是这样会给服务端带来巨大的压力(一连接就会创建一个线程,线程间切换会占用大量CPU资源)。另外异步通信还带来了编码复杂度,所以一般不建议使用。如果要异步,必须使用连接池,排队从连接池获取连接而不是创建新连接。一般来说我们连接数据库都是同步连接。

2.1.2 连接方式:长连接或者短连接

Mysql 既支持短连接,也支持长连接。

  • 短连接

1)短连接就是操作完毕以后,马上close掉。
  • 长连接

1)长连接可以保持打开,减少服务端创建和释放连接的消耗,后面的程序访问的时候还可以使用这个连接。一般我们会在连接池中使用长连接。
2)保持长连接会消耗内存。长时间不活动的连接,Mysql 服务器会断开。
3)默认超时都是 28800 s,8h
show global variables like 'wait_timeout'; -- 非交互式超时时间, 如 JDBC 程序show global variables like 'interactive_timeout'; -- 交互式超时时间, 如数据库工具
  • show status 命令

show global status like 'Thread%';----------------------------------Threads_cached 0 -- 缓存中的线程连接数Threads_connected 10 -- 当前打开的连接数。Threads_created 10 -- 为处理连接创建的线程数。Threads_running 1 -- 非睡眠状态的连接数,通常指并发连接数。
  • show [full] processlist命令

-- 可以使用 show processlist,(root用户)查看SQL的执行状态

一些常见的状态:

状态含义
Sleep线程正在等待客户端, 以向它发送一个新语句
Query线程正在执行查询或往客户端发送数据
Locked该查询被其它查询锁定
Copying to tmp table on disk临时结果集合大于 tmp_table_size。 线程把临时表从存储器内部格式改 变为磁盘模式, 以节约存储器
Sending data线程正在为 SELECT 语句处理行, 同时正在向客户端发送数据
Sorting for group线程正在进行分类, 以满足 GROUP BY 要求
Sorting for order线程正在进行分类, 以满足 ORDER BY 要求
  • 连接数

在5.7版本中默认是151个, 最大可以设置成16384(2^14)。

show variables like 'max_connections';--------------------------------------max_connections 151

show 的参数说明:

1)级别:会话session级别(默认);全局global级别

2)动态修改:set, 重启后失效;永久生效,修改配置文件/etc/my.cnf

set global max_connections=152;

2.1.3 通信协议

  • Unix Socket

1)比如我们在Linux 服务上,如果没有指定-h参数,它就是用socket方式登录 (省略了 -S /var/lib/mysql/mysql.sock)。

2)它不用通过网络协议,也可以连接到MySQL 服务器,它需要用到服务器上的一个物理文件(/var/lib/mysql/mysql.sock)。

select @@socket;-----------------/var/lib/mysql/mysql.sock
  • TCP/IP 协议

1)如果指定-h参数,就会用第二种方式,TCP/IP协议。

2)我们的编程语言的连接模块都是用TCP协议连接到MySQL服务器,比如 mysql-connector-java-x.x.x.xx.jar

  

  • Named Pipes(管道)&& Share Memory(内存共享)

1)这两种通信方式只能在Windows上使用,一般用得比较少。

2.1.4 通信方式

MySQL 使用了半双工的通信方式?

1)要么是客户端向服务端发送数据,要么是服务端向客户端发送数据,这两个动作不能同时发生,所以客户端发送SQL语句给服务端的时候,(在一次连接是非里面)数据是不能分成小块发送的,不管你的SQL语句有多大,都是一次性发送。

2)比如我们用MyBatis动态SQL生成一个批量插入的语句,插入10万条数据,values后面跟了一长串的内容,或者where条件in里面的值太多,会出现问题。这个时候我们必须要调整MySQL服务配置 max_allowed_packet参数的值(默认4M),把它调大,否则就会报错。

3)另一方面,对于服务端来说,也是一次性发送所有的数据,不能因为你已经取到了想要的数据就中断操作,这个时候会对网络和内存产生大量消耗。所以,我们一定要在程序里面避免不带limit的这种操作,比如一次把所有满足条件的数据全部查出来,一定要先count一下,如果数据量的话,可以分批查询。

show variables like 'max_allowed_packet';----------------------------------------max_allowed_packet 4194304

  

  • 单工

1)在两台计算机通信的时候,数据的传输是单向的。比如:遥控器。

  • 半双工

1)在两台计算机之间,数据传输是双向的,你可以给我发送,我也可以给你发送,但是在这个通讯连接里面,同一时间只能有一台服务器在发送数据,也就是你要给我发的话,也必须等我发给你完了之后才能给我发。比如:对讲机。

  • 全双工

1)数据的传输是双向的,并且可以同时传输。比如:打电话。

2.2 查询缓存

1)MySQL 内部自带了一个缓存模块,可以把数据以KV的形式放到内存里面,可以加快数据的读取速度,也可以减少服务器处理的时间。但是MySQL的缓存我们好像比较陌生,从来没有去配置过,也不知道它什么时候生效?

2)MySQL 的缓存默认是关闭的。比如 user_innodb 有 500 万行数据,没有索引,我们在没有索引的字段上执行同样的查询,速度并没有变快。A-主要是因为MySQL 自带的缓存的应用场景有限,第一个是它要求SQL语句必须一模一样,中间多一个空格,字母大小写不同都被认为是不同的SQL, B-表里面任何一条数据发生变化的时候,这张表所有缓存都会失效,所以对于有大量数据更新的应用,也不适合,C-缓存还是交给ORM框架(MyBatis)或者独立的缓存服务(Redis)来处理更合适。

3)MySQL 8.0 中,查询缓存已经被移除了。

show variables like 'query_cache%';

2.3 语法解析和预处理(Parser & Preprocessor)

MySQL 的 Parser 解析器和 Preprocessor 预处理模块 , 就是对语句基于 SQL 语法进行词法和语法分析和语义的解析

2.3.1 词法解析

词法分析就是把一个完整的SQL语句打碎成一个个的单词。

比如一个简单的SQL语句:

select name from user where id = 1;

它会打碎成8个符号,每个符号是什么类型,从哪里开始到哪里结束。

2.3.2 语法解析

第二步就是语法分析,语法分析会对SQL做一些语法检查,比如单引号有没有闭合,然后根据MySQL定义的语法规则,根据SQL语句生成一个数据结构,这个数据结构我们把它叫做解析树(select_lex)。

  

任何数据库的中间件,比如MyCat, Sharding-JDBC(用到了Druid Parser), 都必须要有词法和语法分析功能,在市面上也有很多的开源的词法解析的工具(比如LEX, Yacc)。

2.3.3 预处理器

问题:如果我写了一个词法和语法都正确的SQL,但是表名或者字段不存在,会在哪里报错?是在数据库的执行层还是解析器?比如:

select * from gper;

解析SQL的环节里面有个预处理器,它会检查生成的解析树,解决解析器无法解析的语义,比如,它会检查表和列名是否存在,检查名字和别名,保证没有歧义。预处理之后得到一个新的解析树

2.4 查询优化(Query Optimizer)与查询执行计划

2.4.1 什么是优化器

一条SQL语句并不是只有一种执行方式?一条SQL语句可以有很多种执行方式的,最终返回相同的结果,他们是等价的,但是他们会通过MySQL的查询优化器的模块(Optimizer)[查询优化器的目的就是解析树生成不同的执行计划]选择一种最优的执行计划,MySQL里面使用的是基于开销(cost)的优化器,那种执行计划开销最小,就用哪种。

可以使用以下命令查询的开销

show status like ‘Last_query_cost’;

2.4.2 优化器可以做什么?

MySQL的优化器能处理哪些优化类型呢?

  • 当我们对多张表进行关联查询的时候,以哪个表的数据 作为基准表。

  • 有多个索引可以使用的时候,选择哪个索引。

优化器也不是万能的,并不是再垃圾的SQL语句都能自动优化,也不是每次都能选择到最优的执行计划。

  • 《数据库查询优化器的艺术-原理解析与SQL性能优化》

2.4.3 优化器是怎么得到执行计划的?

启用优化器的追踪(默认是关闭的)

SHOW VARIABLES LIKE 'optimizer_trace';set optimizer_trace='enabled=on';

注意开启这开关是会消耗性能的,因为它要把优化分析的结果写到表里面,所以不要轻易开启,或者查处完之后关闭它(改成off)。

注意:参数分为session和global级别。

-- 执行一个 SQL 语句,优化器会生成执行计划select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;-- 优化器分析的过程已经记录到系统表里面了,我们可以查询select * from information_schema.optimizer_trace;

它是一个JSON类型的数据,主要分成三部分,准备阶段,优化阶段和执行阶段。

  

expanded_query 是优化后的 SQL 语句。

considered_execution_plans 里面列出了所有的执行计划

-- 关闭优化器追踪set optimizer_trace="enabled=off";SHOW VARIABLES LIKE 'optimizer_trace';

2.4.4 优化器得到的结果

1)优化器最终会把解析树变成一个查询执行计划,查询执行计划是一个数据结构。

2)这个执行计划可能并不是最优的执行计划,MySQL也有可能覆盖不到所有的执行计划。

3)MySQL提供一个执行计划的工具,在SQL语句前面加上EXPLAIN,就可以看到执行计划的信息。

注意Explain的结果也不一定是最终执行方式。

EXPLAIN select name from user where id=1;

2.5 存储引擎

得到执行计划,SQL语句是如何执行的?谁去执行的?数据是放在哪里的,数据结构是怎样的?

2.5.1 存储引擎基本介绍

1)数据放在表(Table)结构

2)表在存储数据的同时,还要组织数据的存储结构,这个存储结构就是由我们的存储引擎决定的,所以我们也可以把存储引擎叫做表类型。

2.5.2 查看存储引擎

1)使用命令查看一个数据库已经存在表的存储引擎或者DDL建表语句来查看

2)在MySQL里面,我们创建的每一张表都可以指定它的存储引擎,而不是一个数据库只能使用一个存储引擎,存储引擎是以表为单位的,而且,创建表之后还可以修改存储引擎。

-- 查询gupao数据库中各个表使用的存储引擎show table status from `gupao`;-- 查询数据数据库放数据的路径show variables like 'datadir';------------------------------datadir /var/lib/mysql/

3)默认情况下,每个数据库有一个自己文件夹,以gupao数据库为例,任何一个存储引擎都有一个frm文件,这个是表结构定义文件。

4)不同的存储引擎存放数据的方式不一样,产生的文件也不一样,innodb是1个,memory没有,myisam是两个。

2.5.3 存储引擎比较

  • 常见存储引擎

1)MyISAM(Indexed Squential Access Method: 利用索引,顺序,存取数据的方法) 和 InnoDB 是我们用得最多的两个存储引擎,在MySQL5.5版本之前,默认的存储引擎是MyISAM(MySQL自带),创建表时如不指定会自动使用MyISAM作为存储引擎。

2)5.5版本之后默认的存储引擎改成了InnoDB,它是第三方公怀为MySQL开发的,为什么要改呢?最主要的原因还是InnoDB支持事务,支持行级别的锁,对于业务一致性要求场景更适合。

  • 数据库支持的存储引擎

1)使用命令查看数据库对存储引擎的支持情况

show engines;

  

其中有存储引擎的描述和对事务,XA协议和Savepoints的支持。

XA 协议用来实现分布式事务(分为本地资源管理器,事务管理器)。 Savepoints 用来实现子事务(嵌套事务)。创建了一个 Savepoints 之后,事务就可以回滚到这个点,不会影响到创建 Savepoints 之前的操作。

MyISAM(3个文件)

1)应用范围比较小。表级锁定限制了读/写的性能,因此在Web和数据仓库配置中,它通常用于只读或以读为主的工件。

特点:

支持表级别的锁(插入和更新会锁表),不支持事务。

拥有较高的插入(insert)和查询(select)速度。

查询表的行数(count速度更快)。

应用场景:

怎么快速向数据库插入100万条数据?我们有一种先用MyISAM存储引擎插入数据,然后修改存储引擎为InnoDB的操作。

适用:

只读之类的数据分析的项目。

InnoDB(2个文件)

1)mysql 5.7 中的默认存储引擎。InnoDB是一个事务安全(与ACID兼容)的MySQL存储引擎,它具有提交,回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁(不升级为更粗粒度的锁)和Oracle 风格的一致非锁读提高了多用户并发性和性能。InnoDB将用户数据存储在聚集索引中,以减少基于主键的常见查询的I/O。为了保持数据完整性,InnoDB还支持外键引用完整性约束。

特点:

支持事务,支持外键,因此数据的完整性,一致性更高。

支持行级别的锁和表级别的锁。

支持读写并发,写不阻塞读(MVCC)。

特殊的索引存放方式,可以减少IO,提升查询效率。

适合:

经常更新的表,存在并发读写或者有事务处理的业务系统。

Memory(1个文件)

1)将所有数据存储在RAM中,以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。其使用安全正在减少;InnoDB及其缓冲池内存区域提供了一种通用,持久的方法来将大部分或所有数据保存在内存中,而ndbcluster为大型分布式数据信提供了快速的键值查找。

特点:

把数据放在内存里面,读写的速度很快,但是数据库重启或者崩溃,数据会全部消失,只适合做临时表。

将表中的数据存储到内存中。

CSV(3个文件)

1)表实际上带有逗号分隔值的文本文件。csv表允许以csv格式导入或转储数据,以便与读写相同格式的脚本和应用程序交换数据。因为csv表没有索引,所以通常在正常操作期间将数据保存在innodb表中,并且只在导入或导出阶段使用csv表。

特点:

  不允许空行,不支持索引。格式通用,可以直接编辑,适合在不同数据库之间导入导出。

Archive(2个文件)

这些紧凑的未索引的表用于存储和检索大量很少引用的历史,存档或安全审计信息。

特点:

不支持索引,不支持update delete。

不同的存储引擎提供的特性都不一样,它们有不同的存储机制,索引方式,锁定水平等功能。我们在不同的业务场景中对数据操作的要求不同,就可以选择不同的存储引擎来满足我们的需求,这个就是MySQL支持这么多存储引擎的原因。

2.5.4 如何选择存储引擎?

1)如果对数据一致性要求比较高,需要事务支持,可以选择InnoDB。

2)如果数据查询多更新少,对查询性能要求比较高,可以选择MyISAM。

3)如果需要一个用于查询的临时表,可以选择Memory。

4)如果所有的存储引擎都不能满足你的需求,并且技术能力足够,可以根据官网内部手册用C语言开发一个存储引擎

2.5.5 执行引擎(Query Execution Engine)- 返回结果

1)执行引擎执行计划,操作存储引擎。

2)不同功能的存储引擎实现的API是相同的。

3)最后将数据返回给客户端,即使没有结果也要返回。

三,MySQL 体系结构总结、

  

3.1 模块详解

3.1.1 Connector

用来支持各种语言和SQL的交互,比如PHP,Python,Java的JDBC

3.1.2 Management Serveices & Utilities

系统管理和控制工具,包括备份恢复,MySQL复制,集群等等

3.1.3 Connection Pool

连接池,管理需要缓冲的资源,包括用户密码权限线程等等

3.1.4 SQL Interface

用为接收用户的SQL命令,返回用户需要的查询结果

3.1.5 Parser 用来解析SQL语句

3.1.6 Optimizer

查询优化器

3.1.7 Cache and Buffer

查询缓存,除了行记录的缓存之外,还有表缓存,Key缓存,权限缓存等等

3.1.8 Pluggable Storage Engines

插件式存储引擎,它提供API给服务层使用,跟具体的文件打交道

3.2 架构分层

MySQL 可以分成三层,客户端的连接层, 真正执行操作的服务层,硬件交流的存储引擎层

  

3.2.1 连接层

检测商品,建立连接,管理连接,验证身份和权限

3.2.2 服务层

处理SQL语句,包含缓存处理,词法解析,语法解析,优化器,执行器

3.2.3 存储引擎

数据的存储操作

四,一条更新SQL是如何执行的

基本流程与查询一致,区别在于拿符合的条件的数据之后操作。

4.1 缓冲池Buffer Pool

InnoDB的数据都是放在磁盘上的,InnoDB操作数据有一个最小逻辑单位,叫做页(索引页和数据页),对于数据的操作,不每次都直接操作磁盘,因为磁盘的速度太慢了,InnoDB使用了一种缓冲池的技术,也就是把磁盘的页放到一块内存区域里面,这个内存区域就叫Buffer Pool。下一次读取相同的页,先判断 是不是在缓冲池里面,如果是则直接读取,不再访问磁盘,修改数据的时候,先修改缓冲池里面的页,内存的数据页和磁盘数据不一致时,称为脏页,InnoDB里面有专门的后台线程把Buffer Pool 的数据写入到磁盘,每隔一段时间就一次性地把多个修改写入磁盘,这个动作就叫做刷脏

  

4.2 InnoDB 内存结构和磁盘结构

  

4.2.1 内存结构

Buffer Pool 主要分为4个部分,Buffer Pool, Change Buffer, Adaptive Hash Index, (redo) log buffer。

  • Buffer Pool

1) Buffer Poll 缓存的是页面信息,包括数据页,索引页。

2) 查看服务器状态,可以找到与Buffer Pool 相关的信息。

3) Buffer Pool 默认大小是128M(134217728字节),可以调整。

4) InnoDB用LRU算法来管理缓冲池(链表实现,不是传统的LRU, 分成了young和old),经过淘汰的数据就是热点数据。
SHOW STATUS LIKE '%innodb_buffer_pool%';

状态详情

  • Change Buffer 写缓冲

1)如果这个数据页不是唯一索引,不存在数据重复的情况,也就不需要从磁盘加载索引页判断数据是不是重复(唯一性检查), 这种情况下可以先把修改记录在内存的缓冲池中,从而提升更新语句(Insert,Delete,Update)的执行速度。

2)最后把Change Buffer 记录到数据页的操作叫做merge。几种情况下会发生 merge,a-访问这个数据时,b-通过后台线程或数据库shut down或redo log 写满时

3)如果数据库大部分索引都是非唯一索引,并且业务是写多读少,不会在写ddddddd数据后立刻读取,就可以使用Change Buffer(写缓冲)。写多读少的业务,调大这个值, Change Buffer占Buffer Pool的(默认)25%
SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';
  • Adaptive Hash Index

1)内存存储哈希索引的区域
  • (redo)Log Buffer

Redo log 分成内存和磁盘磁盘两部分。

  

1)InnoDB把所有对页面的个性操作专门写入一个日志文件,并且在数据启动时从这个文件进行恢复操作(实现crash-safe),实现事务的持久性。

2)这个文件就是磁盘的redo log(叫做重做日志),对应/var/lib/mydql目录下的ib_logfile()和ib_logfile1,每个48M

3)这种日志和磁盘配合的整个过程,其实就是MySQL里面的WAL技术(Write-Ahead Logging),它的关键点就是先写日志,再写磁盘。

4)当然redo log 也不是每一次都直接写入磁盘,在Buffer Pool里面有一块内存区域(Log Buffer)专门用来保存即将要写入日志文件的数据,默认16M,它一样可以节省磁盘IO。

5)redo log 的内容主要是用于崩溃恢复,磁盘的数据文件,数据来自buffer pool,redo log写入磁盘,不是写入数据文件
show variables like 'innodb_log%';
含义
innodb_log_file_size指定每个文件的大小,默认 48M。
innodb_log_files_in_group指定文件的数量,默认为 2。
innodb_log_group_home_dir指定文件所在路径,相对或绝对。如果不指定,则为datadir路径。
SHOW VARIABLES LIKE 'innodb_log_buffer_size';

  

Log Buffer什么时候写入log file?: 在我们写入数据到磁盘的时候,操作系统本身是有缓存的,flush 就是把操作系统缓冲区写入到磁盘。

-- log buffer 写入磁盘的时机,由一个参数控制,默认是1。SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

参考地址

含义
0(延迟写)log buffer 将每秒一次地写入 log file 中,并且 log file 的 flush 操作同时进行。 该模式下,在事务提交的时候,不会主动触发写入磁盘的操作。
1(默认,实时每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file,并且刷到磁盘中去。
写,实时刷)每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file。但是 flush 操

  

Redo log 特点:

1)redo log 是InnoDB存储引擎特有的。

2)不是记录数据页更新之后的状态,而是记录这个页做了什么改动,属于物理日志

3)redo log 的大小是固定的,前面的内容会被覆盖。

  

Check point 是当前覆盖的位置,如果 write pos 跟check point 重叠,说明redo log 已经写满。这时候需要同步 redo log 到磁盘中。

4.3 磁盘结构

表空间可以看做是InnoDB存储引擎逻辑结构的最高层,所有有数据都存放在表空间中。InnoDB的表空间分为5大类。

4.3.1 系统表空间 system tablespace

1)默认情况下InnoDB存储引擎有一个共享表空间(对应文件/var/lib/mysql/ibdata1),也叫系统表空间。

2)InnoDB系统表空间包含InnoDB数据字典【由内部系统表组成,存储表和索引的元数据】和双写缓冲区【InnoDB的一大特性】,Change Buffer 和 Undo Logs, 如果没有指定 file-per-table

3)InnoDB的页和操作系统的页大小不一致,InnoDB页大小一般为16k,操作系统页大小为4k,InnoDB的页写入到磁盘时,一个页需要分4次写。

  

如果存储引擎正在写入页的数据到磁盘时发生了宕机,可能出现页只写了一部分的情况,比如只写了4K,就宕机了,这种情况做部分写失效(partial page write),可能会导致数据丢失。

show variables like 'innodb_doublewrite';

如果一个页本身就已经损坏一,使用redo log 来做崩溃恢复是没有意义的,所以在对于应用redo log之前,需要一个页的副本。如果出现了写入失效,就用页的副本来还原这个页,然后再应用 redo log, 这个页的副本就是double write,InnoDB 的双写技术。通过它实现了数据页的可靠性。

double write 由两部分组成,一部分是内存的double write, 一部分是磁盘上的double write, 因为double write 是顺序写入的,不会带来很大的开销。

在默认情况下,所有的共享一个系统表空间,这个文件会越来越来大,而且它的空间不会收缩。

4.3.2 独占表空间 file-per-table tablespace

每张表独占一个表空间,这个开关通过innodb_file_per_table 设置,默认开启。

SHOW VARIABLES LIKE 'innodb_file_per_table';

开启后,则每张表会开辟一个表空间,这个文件就是数据目录下的ibd文件(例如/var/lib/mysql/gupao/user_innodb.ibd),存放表的索引和数据。

但是其他类的数据,如回滚(undo)信息,插入缓冲索引页,系统事务信息,二次写缓冲(Double write buffer)等还是存放在原来的共享表空间内。

4.3.3 通用表空间 general tablespaces

通用表空间也是一种共享的表空间。可以创建一个通用的表空间,用来存储不同数据库的表,数据路径和文件可以自定义。在创建表的时候可以指定表空间,用ALTER修改表空间可以转移表空间。不同表空间的数据是可以移动的,删除表空间需要先删除里面的所有表

-- 创建通用表空间create tablespace ts2673 add datafile '/var/lib/mysql/ts2673.ibd' file_block_size=16K engine=innodb;-- 创建表的时候可以指定表空间,用 ALTER 修改空间可以转移表空间create table t2673(id integer) tablespace ts2673;-- 删除表空间需要先删除里面的所有表drop table t2673;drop tablespace ts2673;

4.3.4 临时表空间

存储临时表的数据,包括用户创建的临时表和磁盘的内部临时表,对应数据目录下的ibtmp1文件,当数据服务器正常关闭时,该表空间被删除,下次重新产生。

  • redo log(略)

  • undo log tablespace

undo log(撤销日志或日志)记录了事务发生了之前的数据状态(不包括select),如果修改数据时出现异常,可以用undo log 来实现回滚操作(保持原子性),在执行undo 的时候,仅仅是将数据从逻辑上恢复到事务之前的状态,而不是从物理页面上操作实现的,属于逻辑格式的日志。redo Log 和undo Log 与事务密切相关,统称为事务日志,undo Log 的数据默认在系统表空间ibdata1文件中,因为共享表空间不会自动收缩,也可以单独创建一个undo表空间。

show global variables like '%undo%';

4.4 总结

update user set name = 'penyuyan' where id=1;

1)事务开始,从内存或磁盘取到这条数据,返回给Server的执行行器

2)执行器修改这一行数据的值为penyuyan

3)记录name=qingshan到undo log

4)记录name=penyuyan到redo log

5)调用存储引擎接口,在内存(Buffer Pool)中修改name=penyuyan

6)事务提交

4.5 后台线程

后台线程的主要作用是负责刷新内存池中的数据和把修改的数据页刷新到磁盘,后台线程分为 master threa, IO thread, purge thread, page cleaner thread。

  • master thread

负责刷新缓存数据到磁盘并协调调试其它后台进程。

  • IO thread

IO thread分为insert buffer, log, read, wrilte 进程,分别用来处理insert buffer, 重做日志,读写请求的IO 回调。

  • purge thread

用来回收undo页。

  • page clear thread

用来刷新脏页。

4.6 Binlog

binlog 以事件的形式记录了所有的DDL和DML语句(因为它记录的是操作而不是数据值,属于逻辑日志),可以用来做主从复制和数据恢复。与redo log 不一样,它的文件内容是可以追加的,没有固定大小限制。在开启了binlog功能的情况下,我们可以把binlog导出成SQL语句,把所有的操作重放一遍,来实现数据的恢复

4.7 整体流程

  

五,索引

5.1 索引是什么

数据库索引,是数据库管理系统(DBMS)中一个排序的数据结构,以协助快速查询、更新数据库表中数据。

  

数据是以文件的噶啊存放在磁盘上面的,每一行数据都有它的磁盘地址。如果没有索引的话,要从500万行数据里面检索一条数据,只能依次遍历这张表的全部数据,直到找到这条数据。但如果存在索引,只需要在索引里面去检索这条数据就行了,因为它是一种特殊的专门用来快速检索的数据结构,找到数据存放的磁盘地址,就可以拿到数据。

5.2 索引类型

在InnlDB里面,索引类型有三种,普通索引,唯一索引(主键索引是特殊的唯一索引),全文牵引

  • 普通索引 (Normal)

    也叫非唯一索引,是最普通的索引,没有任何的限制。

  • 唯一索引(Unique)

唯一索引要求键值不能重复。另外需要注意的是,主键索引是一
种特殊的唯一索引,它还多了一个限制条件,要求键值不能为空。主键索引用 primay key
创建。

  • 全文索引(Fulltext)

针对比较大的数据,比如我们存放的是消息内容,有几 KB 的数
据的这种情况,如果要解决 like 查询效率低的问题,可以创建全文索引。只有文本类型
的字段才可以创建全文索引,比如 char、varchar、text。

5.3 索引存储模型推演

5.3.1 二分查找

二分查找,也叫折半查找,每一次把候选数据缩小一半,如果数据已经排过序,这种方式效率比较高。有序数组的等值查询和比较查询 效率非常高,但是更新数据的时候会出现一个问题,只要能要挪动大量的数据(改变index),所有只适合存储静态的数据。不适合于频繁的修改操作。

5.3.2 二叉查找树(BST Binary Search Tree)

二叉查找树的特点是左子树所有的节点都小于父节点,右子树所有的节点都大于父节点,投影到平面以后,就是一个有序的线性表。二叉查找树既能够实现快速查找,又能够实现快速插入。但二叉查找树的耗时与树的深度相关,最坏的情况下时间复杂度会退化成O(n)。

  

最坏的情况,由于左右子树深度太大,二叉查找树会变成“斜树”,这棵树的左子树根本没有节点–> 不够平衡,这种情况下不能达到加快检索速度的,和顺序查找效率没有什么区别

  

  • 在线数据结构的动态演示

5.3.3 平衡二叉树(AVL Tree)(左旋,右旋)

1)平衡二叉树 AVL Trees (Balanced binary search trees) 的定义左右子树深度差绝对不能超过1。为了保持平衡中,AVL树在插入和更新数据的时候执行了一系列的计算和调整的操作。

  

  

  • 平衡二叉树作为索引查询数据

每一个节点都是固定的单位,索引存储石块内容

1)索引的键值

2)数据的磁盘地址

3)左子节点和右子节点的引用

  

5.4 InnoDB 逻辑存储结构

MySQL的存储结构分为5级:表空间,段,簇(区),页,行。

  

  • 表空间 Table Space

  • 段 Segment

表空间是由各个段组成的,常见的段有数据段,索引段,回滚段,段是一个逻辑的概念。一个ibd文件(独立表空间文件)里面会由很多个段组成。创建一个索引会创建两个段,一个是索引段:leaf node segment, 一个是数据段:non-leaf node segment。索引段管理非叶子节点的数据。数据段管理叶子节点的数据。一个表段数,是索引的个数乘以2。

  • 簇 Extent

一个段(Segment)又有很多的簇组成,每个区的大小是1MB(64个连续的页)。每一段至少会有一个簇,一个段所管理的空间大小是无限的,可以一直扩展下去,但是扩展的最小单位就是簇。

  • 页 Page

为了高效管理物理空间,对簇进一步细分,得到页,簇就由连续的页(Page)组成的空间,一个簇中有64个连续的页。(1MB/16KB=64)。这些页面在物理上和逻辑上都是连续的。页是InnoDB存储引擎磁盘管理的最小单位,通过innodb_page_size 设置。一个表空间最多拥有2∧32个页,默认情况下一个页的大小为16KB,表空间最多存储64TB的数据

注意:文件系统中,也有页的概念

操作系统和内存打交道,最小的单位是页Page。文件系统的内存页通常是4K。

  

SHOW VARIABLES LIKE 'innodb_page_size';

假设一行数据大小是 1K,那么一个数据页可以放 16 行这样的数据。
举例:一个页放 3 行数据。

  

往表中插入数据时,如果一个页面已经写完,产生一个新的叶页面。如果一个簇的所有的页面都被用完,会从当前页面所在段新分配一个簇。如果数据不是连续的,往已经写满的页中插入数据,会导致叶页面分裂

  

  

  • 行 Row

1)InnoDB存储引擎是面向行的(row-oriented),也就是说数据的存放按行进行存放

2)Antelope是InnoDB内置的文件格式,有两种行格式: REDUNDANT Row Format, COMPACT Row Format(5.6默认)

3)Barracude(梭子鱼)是InnoDB Plugin 支持的文件格式,新增了两种行格式: DYNAMIC Row Format(5.7 默认),COMPRESSED Row Format

文件格式行格式描述
Antelope (Innodb-base)ROW_FORMAT=COMPACT ROW_FORMAT=REDUNDANTCompact 和 redumdant 的区别在就是在于首部的存 存内容区别。 compact 的存储格式为首部为一个非 NULL 的变长字 段长度列表 redundant 的存储格式为首部是一个字段长度偏移 列表(每个字段占用的字节长度及其相应的位移) 。 在 Antelope 中对于变长字段, 低于 768 字节的, 不 会进行 overflow page 存储, 某些情况下会减少结果 集 IO.
Barracuda (innodb-plugin)ROW_FORMAT=DYNAMIC ROW_FORMAT=COMPRESSED这两者主要是功能上的区别功能上的。 另外在行 里的变长字段和 Antelope 的区别是只存 20 个字节, 其它的 overflow page 存储。 另外这两都需要开启 innodb_file_per_table=1

innodb_file_format 在配置文件中指定;row_format 则在创建数据表时指定。

show variables like "%innodb_file_format%";SET GLOBAL innodb_file_format=Barracuda;

  

在创建表的时候可以指定行格式。

CREATE TABLE tf1
(c1 INT PRIMARY KEY)
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;

查看行格式:

SHOW TABLE STATUS LIKE 'student' \G;

  

5.5 AVL 树用于存储索引数据

索引的数据,是放在硬盘上的。查看数据 和索引的大小

selectCONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS data_len,CONCAT(ROUND(SUM(INDEX_LENGTH/1024/1024),2),'MB') as index_lenfrom information_schema.TABLESwhere table_schema='gupao' and table_name='user_innodb';

当我们用树的结构来存储索引的时候,访问一个节点就要跟磁盘之间发生一次 IO。InnoDB 操作磁盘的最小的单位是一页(或者叫一个磁盘块),大小是 16K(16384 字节)。 那么,一个树的节点就是 16K 的大小。如果我们一个节点只存一个键值+数据+引用,例如整形的字段,可能只用了十几个或者几十个字节,它远远达不到 16K 的容量,所以访问一个树节点,进行一次 IO 的时候,浪费了大量的空间。所以如果每个节点存储的数据太少,从索引中找到我们需要的数据,就要访问更多的节点,意味着跟磁盘交互次数就会过多。如果是机械硬盘时代,每次从磁盘读取数据需要 10ms 左右的寻址时间,交互次数越多,消耗的时间就越多。

  

比如上面这张图,我们一张表里面有 6 条数据,当我们查询 id=37 的时候,要查询两个子节点,就需要跟磁盘交互 3 次,如果我们有几百万的数据呢?这个时间更加难以估计。所以我们的解决方案是什么呢?
第一个就是让每个节点存储更多的数据。第二个,节点上的关键字的数量越多,我们的指针数也越多,也就是意味着可以有更多的分叉(我们把它叫做“路数”)。因为分叉数越多,树的深度就会减少(根节点是 0)。这样,我们的树是不是从原来的高瘦高瘦的样子,变成了矮胖矮胖的样子?这个时候,我们的树就不再是二叉了,而是多叉,或者叫做多路 。

5.6 多路平衡查找树(B Tree)(分裂,合并)

Balanced Tree 就是我们的多路平衡查找树,叫做B Tree(B 代表平衡)

1,与AVL树一样,B 树在枝节点和叶子节点存储键值,数据地址,节点引用。

2,B Tree的特点:分叉数(路数)永远比关键字数多1,如每个节点存储两个关键字,那么就会有三个指针指向三个子节点。

  

  • B Tree 的查找规则是什么样的呢?

比如我们要在这张表里面查找15。因为15小于17,走左边,因为15大于12,走右边,在磁盘块7是是里面就找到了15,只用了3次IO。

数据结构可视化

比如Max Degree(路数)是3的时候,我们插入数据1,2,3,在插入3的时候,本来应该在第一个磁盘块,但是如果一个节点有三个关键字的时候,意味着有4个指针,子节点会变成4路,所以这个时候必须进行分裂,把中间的数据 2提上去,把1和3变成2的子节点。如果删除节点,会有相反的合并的操作。

  

5.7 B+树(加强版多路平衡查找树)

  

  • 1,B+Tree的关键字的数量是跟路数相等的。

  • 2,B+Tree的根节点和枝节点中都不会存储数据,只有叶子节点才存储数据,搜索到关键字不会直接返回,会支最后一层的叶子节点。比如我们搜索id=28,虽然在第一层直接命中了,但是全部的数据在叶子节点上面,所以我还要继续往下搜索,一直到叶子节点。

假设一条记录是1K,一个叶子节点(一页)可以存储16条记录,非叶子节点可以存储多个指针?

假设索引是bigint类型,长度为8字节。指针大大泡泡糖上在InnoDB源码中设置为6字节,这样一共14字节,非叶子节点(一页)可以存储16384/14=1170个这样的单元(键值+指针),代表有1170个指针。

树深度为2的时候,有1170^2个叶子节点,可以存储的数据为1170*1170*16=21902400。

  

在查找数据时一次页的查找代表一次IO,也就是说,一张2000万左右的表,查询数据最多需要访问3次磁盘。所以在InnoDB中B+树深度一般为1-3层,它就能满足千万级的数据存储。

  • 3,B+Tree的每个叶子节点增加了一个相邻叶子节点的指针,它的最后一个数据会指向下一个叶子节点的第一个数据,形成了一个有序链表的结构。

  • 4,它是根据左闭右开的区间[ )来检索数据。

5.7.1 B+Tree 的数据搜寻过程

1)比如我们要查找 28,在根节点就找到了键值,但是因为它不是页子节点,所以 会继续往下搜寻,28 是[28,66)的左闭右开的区间的临界值,所以会走中间的子节点,然 后继续搜索,它又是[28,34)的左闭右开的区间的临界值,所以会走左边的子节点,最后 在叶子节点上找到了需要的数据。

2)第二个,如果是范围查询,比如要查询从 22 到 60 的数据,当找到 22 之后,只 需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点,这样就极大地提高了区间查询效率(不需要返回上层父节点重复遍历查找)。

5.7.2 B+Tree的特点

1)它是 B Tree 的变种,B Tree 能解决的问题,它都能解决。B Tree 解决的两大问题 是什么?(每个节点存储更多关键字;路数更多)

2)扫库、扫表能力更强(如果我们要对表进行全表扫描,只需要遍历叶子节点就可以 了,不需要遍历整棵 B+Tree 拿到所有的数据)

  1. B+Tree 的磁盘读写能力相对于 B Tree 来说更强(根节点和枝节点不保存数据区, 所以一个节点可以保存更多的关键字,一次磁盘加载的关键字更多)

4)排序能力更强(因为叶子节点上有下一个数据区的指针,数据形成了链表)

5)效率更加稳定(B+Tree 永远是在叶子节点拿到数据,所以 IO 次数是稳定的)

5.8 为什么不用红黑树?

红黑树也是 BST 树,但是不是严格平衡的。 必须满足 5 个约束:

1、节点分为红色或者黑色。

2、根节点必须是黑色的。

3、叶子节点都是黑色的 NULL 节点。

4、红色节点的两个子节点都是黑色(不允许两个相邻的红色节点)。

5、从任意节点出发,到其每个叶子节点的路径中包含相同数量的黑色节点。

  

插入:60、56、68、45、64、58、72、43、49, 基于以上规则,可以推导出:从根节点到叶子节点的最长路径(红黑相间的路径)不大于最短路径(全部是黑色 节点)的 2 倍。

  • 为什么不用红黑树?

1、只有两路。

2、不够平衡。 红黑树一般只放在内存里面用。例如 Java 的 TreeMap。

5.9 索引方式:真的是用B+Tree吗?

在 Navicat 的工具中,创建索引,索引方式有两种,Hash 和 B Tree。

5.9.1 HASH

以 KV 的形式检索数据,也就是说,它会根据索引字段生成哈希码和指针, 指针指向数据。

  

5.9.2 哈希索引有什么特点呢?

1, 它的时间复杂度是 O(1),查询速度比较快。因为哈希索引里面的数据不是 按顺序存储的,所以不能用于排序。

2, 我们在查询数据的时候要根据键值计算哈希码,所以它只能支持等值查询 (= IN),不支持范围查询(> < >= <= between and)。 另外一个就是如果字段重复值很多的时候,会出现大量的哈希冲突(采用拉链法解 决),效率会降低

5.9.3 问题: InnoDB 可以在客户端创建一个索引,使用哈希索引吗?

  • InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature【InnoDB 内部使用哈希索引来实现自适应哈希索引特性】

这句话的意思是 InnoDB 只支持显式创建 B+Tree 索引,对于一些热点数据页, InnoDB 会自动建立自适应 Hash 索引,也就是在 B+Tree 索引基础上建立 Hash 索引, 这个过程对于客户端是不可控制的,隐式的。 我们在 Navicat 工具里面选择索引方法是哈希,但是它创建的还是 B+Tree 索引,这 个不是我们可以手动控制的,buffer pool 里面有一块区域是 Adaptive Hash Index 自适应哈希 索引,就是这个。

-- 这个开关默认是 ONshow variables like 'innodb_adaptive_hash_index';-- 存储引擎的运行信息show engine innodb status

因为B Tree 和B+Tree 的特性,它们广泛地用在文件系统和数据库中,例如Windows 的 HPFS 文件系统,Oracel、MySQL、SQLServer 数据库。

5.10 B+Tree落地形式

5.10.1 MySQL架构

MySQL 是一个支持插件式存储引擎的数据库。在 MySQL 里面,每个表在创建的时候都可以指定它所使用的存储引擎,主要关注一下最常用的两个存储引擎,MyISAM 和 InnoDB 的索引的实现。

5.10.2 MySQL 数据存储文件

MySQL 的数据都是文件的形式存放在磁盘中的,我们可以找到这个数据目录 的地址。在 MySQL 中有这么一个参数。

show VARIABLES LIKE 'datadir';

每张 InnoDB 的表有两个文件(.frm 和.ibd),MyISAM 的表 有三个文件(.frm、.MYD、.MYI)。

  

有一个是相同的文件,.frm。 .frm 是 MySQL 里面表结构定义的文件,不管你建表 的时候选用任何一个存储引擎都会生成。

5.10.2.1 MyISAM
  • 在 MyISAM 里面,另外有两个文件:

一个是.MYD 文件,D 代表 Data,是 MyISAM 的数据文件,存放数据记录,比如我 们的 user_myisam 表的所有的表数据。

一个是.MYI 文件,I 代表 Index,是 MyISAM 的索引文件,存放索引,比如我们在 id 字段上面创建了一个主键索引,那么主键索引就是在这个索引文件里面。 也就是说,在 MyISAM 里面,索引和数据是两个独立的文件。

  • 那我们怎么根据索引找到数据呢?

MyISAM 的 B+Tree 里面,叶子节点存储的是数据文件对应的磁盘地址。所以从索 引文件.MYI 中找到键值后,会到数据文件.MYD 中获取相应的数据记录。

  

  • 这里是主键索引,如果是辅助索引,有什么不一样呢?

在 MyISAM 里面,辅助索引也在这个.MYI 文件里面。 辅助索引跟主键索引存储和检索数据的方式是没有任何区别的,一样是在索引文件 里面找到磁盘地址,然后到数据文件里面获取数据。

  

5.10.2.2 InnoDB
  • InnoDB 只有一个文件(.ibd 文件),那索引放在哪里呢?

在 InnoDB 里面,它是以主键为索引来组织数据的存储的,所以索引文件和数据文 件是同一个文件,都在.ibd 文件里面。 在 InnoDB 的主键索引的叶子节点上,它直接存储了我们的数据。

  

  • 什么叫做聚集索引(聚簇索引)?

就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。(比如字典的目录 是按拼音排序的,内容也是按拼音排序的,按拼音排序的这种目录就叫聚集索引)。 在 InnoDB 里面,它组织数据的方式叫做叫做(聚集)索引组织表(clustered index organize table),所以主键索引是聚集索引,非主键都是非聚集索引。 如果 InnoDB 里面主键是这样存储的,那主键之外的索引,比如我们在 name 字段 上面建的普通索引,又是怎么存储和检索数据的呢?

  

  • InnoDB 中,主键索引和辅助索引是有一个主次之分的。

辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询,会根据主键值在主键索引中查询,最终取得数据。 比如我们用 name 索引查询 name= ‘青山’,它会在叶子节点找到主键值,也就是 id=1,然后再到主键索引的叶子节点拿到数据。

  • 为什么在辅助索引里面存储的是主键值而不是主键的磁盘地址呢?如果主键的数据 类型比较大,是不是比存地址更消耗空间呢? 我们前面说到 B Tree 是怎么实现一个节点存储多个关键字,还保持平衡的呢?

是因为有分叉和合并的操作,这个时候键值的地址会发生变化,所以在辅助索引里面不能存储地址。

  • 另一个问题,如果一张表没有主键怎么办?

1、如果我们定义了主键(PRIMARY KEY),那么 InnoDB 会选择主键作为聚集索引。

2、如果没有显式定义主键,则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引。

3、如果也没有这样的唯一索引,则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引,它会随着行记录的写入而主键递增。

select _rowid name from t2;

5.11 索引使用原则

我们容易有以一个误区,就是在经常使用的查询条件上都建立索引,索引越多越好, 那到底是不是这样呢?

5.11.1 列的离散度

  • 第一个叫做列的离散度,我们先来看一下列的离散度的公式:

count(distinct(column_name)) : count()*

列的全部不同值和所有数据行的比例。 数据行数相同的情况下,分子越大,列的离散度就越高。

简单来说,如果列的重复值越多,离散度就越低,重复值越少,离散度就越高。 了解了离散度的概念之后,我们再来思考一个问题,我们在 name 上面建立索引和 在 gender 上面建立索引有什么区别。

ALTER TABLE user_innodb DROP INDEX idx_user_gender;

ALTER TABLE user_innodb ADD INDEX idx_user_gender (gender); -- 耗时比较久 EXPLAIN SELECT * FROM `user_innodb` WHERE gender = 0;show indexes from user_innodb;

而 name 的离散度更高,比如“青山”的这名字,只需要扫描一行。

ALTER TABLE user_innodb DROP INDEX idx_user_name;

ALTER TABLE user_innodb ADD INDEX idx_user_name (name); EXPLAIN SELECT * FROM `user_innodb` WHERE name = '青山';

查看表上的索引,Cardinality代表基数,代表预估的不重复的值的数量。索引的基数与表总行数越接近,列的离散度就越高。

如果在 B+Tree 里面的重复值太多,MySQL 的优化器发现走索引跟使用全表扫描差不了多少的时候,就算建了索引,也不一定会走索引。

  

  • 这个给我们的启发是什么?

建立索引,要使用离散度(选择度)更高的字段。

5.11.2 联合索引最左匹配

前面我们说的都是针对单列创建的索引,但有的时候我们的多条件查询的时候,也 会建立联合索引。单列索引可以看成是特殊的联合索引。

  • 比如我们在 user 表上面,给 name 和 phone 建立了一个联合索引。

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;

ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

  

联合索引在 B+Tree 中是复合的数据结构,它是按照从左到右的顺序来建立搜索树的(name 在左边,phone 在右边)。

  • 从这张图可以看出来,name 是有序的,phone 是无序的。当 name 相等的时候,phone 才是有序的。

这个时候我们使用 where name= ‘青山’ and phone = '136xx '去查询数据的时候,B+Tree 会优先比较 name 来确定下一步应该搜索的方向,往左还是往右。如果 name相同的时候再比较 phone。但是如果查询条件没有 name,就不知道第一步应该查哪个节点,因为建立搜索树的时候 name 是第一个比较因子,所以用不到索引

5.11.2.1 什么时候用到联合索引

所以,我们在建立联合索引的时候,一定要把最常用的列放在最左边。

  • 比如下面的三条语句,能用到联合索引吗?

1)使用两个字段,可以用到联合索引

EXPLAIN SELECT * FROM user_innodb WHERE name= '权亮' AND phone = '15204661800';

2)使用左边的 name 字段,可以用到联合索引

EXPLAIN SELECT * FROM user_innodb WHERE name= '权亮'

3)使用右边的 phone 字段,无法使用索引,全表扫描

EXPLAIN SELECT * FROM user_innodb WHERE phone = '15204661800'
5.11.2.2 如何创建联合索引
  • 有一天我们的 DBA 找到我,说我们的项目里面有两个查询很慢。

SELECT * FROM user_innodb WHERE name= ? AND phone = ?;

SELECT * FROM user_innodb WHERE name= ?;
  • 按照我们的想法,一个查询创建一个索引,所以我们针对这两条 SQL 创建了两个索 引,这种做法觉得正确吗?

CREATE INDEX idx_name on user_innodb(name);

CREATE INDEX idx_name_phone on user_innodb(name,phone);

当我们创建一个联合索引的时候,按照最左匹配原则,用左边的字段 name 去查询 的时候,也能用到索引,所以第一个索引完全没必要。 相当于建立了两个联合索引(name),(name,phone)。

  • 如果我们创建三个字段的索引 index(a,b,c),相当于创建三个索引:

index(a)

index(a,b)

index(a,b,c)

用 where b=? 和 where b=? and c=? 和 where a=? and c=?是不能使用到索引 的。不能不用第一个字段,不能中断。 这里就是 MySQL 联合索引的最左匹配原则。

5.11.3 覆盖索引

  • 回表

非主键索引,我们先通过索引找到主键索引的键值,再通过主键值查出索引里面没有的数据,它比基于主键索引的查询多扫描了一棵索引树,这个过程就叫回表。

例如:select * from user_innodb where name = ‘青山’;

  

在辅助索引里面,不管是单列索引还是联合索引,如果 select 的数据列只用从索引 中就能够取得,不必从数据区中读取,这时候使用的索引就叫做覆盖索引,这样就避免了回表。

创建一个联合索引

-- 创建联合索引 ALTER TABLE user_innodb DROP INDEX comixd_name_phone;

ALTER TABLE user_innodb add INDEX `comixd_name_phone` (`name`,`phone`)

这三个查询语句都用到了覆盖索引

EXPLAIN SELECT name,phone FROM user_innodb WHERE name= '青山' AND phone = ' 13666666666';

EXPLAIN SELECT name FROM user_innodb WHERE name= '青山' AND phone = ' 13666666666';

EXPLAIN SELECT phone FROM user_innodb WHERE name= '青山' AND phone = ' 13666666666';

Extra 里面值为“Using index”代表使用了覆盖索引

  

select * ,用不到覆盖索引

如果改成只用 where phone = 查询呢?动手试试?

很明显,因为覆盖索引减少了 IO 次数,减少了数据的访问量,可以大大地提升查询 效率。

5.11.4 索引条件下推(ICP)

再来看这么一张表,在 last_name 和 first_name 上面创建联合索引。

drop table employees;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NULL,
`first_name` varchar(14) NOT NULL,varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

alter table employees add index idx_lastname_firstname(last_name,first_name);

INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (1, NULL, '698', 'liu', 'F', NULL);

INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (2, NULL, 'd99', 'zheng', 'F', NULL);

INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (3, NULL, 'e08', 'huang', 'F', NULL);

INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (4, NULL, '59d', 'lu', 'F', NULL);

INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (5, NULL, '0dc', 'yu', 'F', NULL);

INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (6, NULL, '989', 'wang', 'F', NULL);

INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (7, NULL, 'e38', 'wang', 'F', NULL);

INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (8, NULL, '0zi', 'wang', 'F', NULL);

INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (9, NULL, 'dc9', 'xie', 'F', NULL);

INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10, NULL, '5ba', 'zhou', 'F', NULL);

关闭 ICP:

set optimizer_switch='index_condition_pushdown=off';

查看参数:

show variables like 'optimizer_switch';

现在我们要查询所有姓 wang,并且名字最后一个字是 zi 的员工,比如王胖子,王 瘦子。查询的 SQL:

select * from employees where last_name='wang' and first_name LIKE '%zi' ;
  • 这条 SQL 有两种执行方式:

1、根据联合索引查出所有姓 wang 的二级索引数据,然后回表,到主键索引上查询 全部符合条件的数据(3 条数据)。然后返回给 Server 层,在 Server 层过滤出名字以 zi 结尾的员工。

2、根据联合索引查出所有姓 wang 的二级索引数据(3 个索引),然后从二级索引 中筛选出 first_name 以 zi 结尾的索引(1 个索引),然后再回表,到主键索引上查询全 部符合条件的数据(1 条数据),返回给 Server 层。

  

很明显,第二种方式到主键索引上查询的数据更少。

注意,索引的比较是在存储引擎进行的,数据记录的比较,是在 Server 层进行的。 而当 first_name 的条件不能用于索引过滤时,Server 层不会把 first_name 的条件传递 给存储引擎,所以读取了两条没有必要的记录。 这时候,如果满足 last_name='wang’的记录有 100000 条,就会有 99999 条没有 必要读取的记录

执行以下 SQL,Using where:

explain select * from employees where last_name='wang' and first_name LIKE '%zi' ;

Using Where 代表从存储引擎取回的数据不全部满足条件,需要在 Server 层过滤。 先用 last_name 条件进行索引范围扫描,读取数据表记录,然后进行比较,检查是 否符合 first_name LIKE ‘%zi’ 的条件。此时 3 条中只有 1 条符合条件。

开启 ICP:

set optimizer_switch='index_condition_pushdown=on';

此时的执行计划,Using index condition:

  

把 first_name LIKE '%zi’下推给存储引擎后,只会从数据表读取所需的 1 条记录。

索引条件下推(Index Condition Pushdown),5.6 以后完善的功能。只适用于二 级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。

5.12 索引的创建与使用

因为索引对于改善查询性能的作用是巨大的,所以我们的目标是尽量使用索引。

5.12.1 索引的创建

1、在用于 where 判断 order 排序和 join 的(on)字段上创建索引

2、索引的个数不要过多。 ——浪费空间,更新变慢。

3、区分度低的字段,例如性别,不要建索引。 ——离散度太低,导致扫描行数过多。

4、频繁更新的值,不要作为主键或者索引。 ——页分裂

5、组合索引把散列性高(区分度高)的值放在前面。

6、创建复合索引,而不是修改单列索引。

7、过长的字段,怎么建立索引?

8、为什么不建议用无序的值(例如身份证、UUID )作为索引?

5.12.3 索引的创建

1、索引列上使用函数(replace\SUBSTR\CONCAT\sum count avg)、表达式、 计算(+ - * /):

explain SELECT * FROM `t2` where id+1 = 4;

2、字符串不加引号,出现隐式转换

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone)
explain SELECT * FROM `user_innodb` where name = 136;

explain SELECT * FROM `user_innodb` where name = '136';

3、like 条件中前面带%

where 条件中 like abc%,like %2673%,like %888 都用不到索引吗?为什么?

explain select *from user_innodb where name like 'wang%';

explain select *from user_innodb where name like '%wang';

过滤的开销太大,所以无法使用索引。这个时候可以用全文索引

4、负向查询

NOT LIKE 不能:

explain select *from employees where last_name not like 'wang

!= (<>)和 NOT IN 在某些情况下可以:

explain select *from employees where emp_no not in (1)

explain select *from employees where emp_no <> 1

注意一个 SQL 语句是否使用索引,跟数据库版本、数据量、数据选择度都有关系。 其实,用不用索引,最终都是优化器说了算。

  • 优化器是基于什么的优化器?

基于 cost 开销(Cost Base Optimizer),它不是基于规则(Rule-Based Optimizer),也不是基于语义。怎么样开销小就怎么来。

5.11 前缀索引

当字段值比较长的时候,建立索引会消耗很多的空间,搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引,这个就叫前缀索引。创建一张商户表,因为地址字段比较长,在地址字段上建立前缀索引:

create table shop(address varchar(120) not null); alter table shop add key (address(12));

问题是,截取多少呢?截取得多了,达不到节省索引存储空间的目的,截取得少了,重复内容太多,字段的散列度(选择性)会降低。

怎么计算不同的长度的选择性呢?

先看一下字段在全部数据中的选择度:

select count(distinct address) / count(*) from shop;

通过不同长度去计算,与全表的选择性对比:

select count(distinct left(address,10))/count(*) as sub10, count(distinct left(address,11))/count(*) as sub11, count(distinct left(address,12))/count(*) as sub12, count(distinct left(address,13))/count(*) as sub13 from shop

只要截取前 13 个字段,就已经有比较高的选择性了(这里的数据只是举例)

正文到此结束
本文目录