Java进阶专题(二十六) 数据库原理研究与优化

前言

在一个大数据量的系统中，这些数据的存储、处理、搜索是一个非常棘手的问题。

比如存储问题：单台服务器的存储能力及数据处理能力都是有限的, 因此需要增加服务器, 搭建集群来存储海量数据。

读写性能问题：单台数据库服务器的数据存储和数据处理能力都是有限的， 而大多数互联网业务，往往读多写少，而互联网特别是中大型的电商系统，业务都是非常繁忙的， 这个时候最容易出现的就是读性能瓶颈。

扩容问题：随着时间的推移，原有的集群中的机器不能够存储这么多的数据量时，这个时候我们就需要考虑扩容。

数据库架构设计

可用性设计

在最原始的架构中，是单一数据库，一旦数据库宕机之后，整个服务都不可用，不存在高可用。解决高可用的思路，就是冗余、复制。

主从复制：在这种主从的架构中，即使Master节点挂掉，还有Slave节点，整个数据库的数据依赖存在，但是在
这种架构中，无法保证读、写的高可用，而且会存在一致性问题；

为保证“读”的高可用，还可以对读（从）库进行冗余，但是冗余读库，也会存在副作用： 读写有延时，可能存在不一致。保证"写"高可用，就可以在上述架构的基础上,再冗余写库，采用双主双从模式。而在这种架构下，两个主库，都会执行写请求，而且互相同步。

读性能设计

在数据库中，我们为了提高读的性能，最常用的做法就是建立索引，但是如果索引过多，又会存在其副作用：
降低了增删改性能；索引占内存多了，放在内存中的数据减少，数据命中率降低，IO次数增多；

解决方案：

不同库建立不同的索引：主库只提供写操作， 不建立索引；从库提供读操作，在从库上建立适当的索引 ；

增加从库，负载均衡：这种做法上面已经提到，会存在主从不一致的问题，从库数量越多，主从延时越长，不一致问题越严重。

增加缓存层：①. 发生写请求时，先淘汰缓存，再写数据库②. 发生读请求时，先读缓存，缓存命中则直接返回，没有命中，则查询数据库，并将查询的结果缓存在redis中（而此时旧数据可能入缓存）。

一致性设计：

引入中间件：通过中间件将key写操作路由到主， 在一定时间范围内，该key上的读也路由到主，当主从同步完成后再将读操作路由到从。

读写都到主：读写都到主，不做读写分离，也就不存在主从不一致的情况。

缓存两次淘汰：异常的读写时序，或导致旧数据入缓存，一次淘汰不够，要进行二次淘汰

a. 发生写请求时，先淘汰缓存，再写数据库，额外增加一个timer，一定时间（主从同步完成的经验时间）后再次淘汰
b. 发生读请求时，先读缓存，hit则返回，miss则读数据库并将数据入缓存（此时可能旧数据入缓存，但会被二次淘汰淘汰掉，最终不会引发不一致）

扩展性设置

在上述的架构中，针对于单库的可用性、读性能、一致性进行了分析，在电商系统的数据库中，数据量是特别大的，而单台服务器的容量、性能都是有限的，如果来完成扩容，则我们需要考虑到拓展性的设计。

垂直拆分：根据业务划分，将不同的数据库表切分到不同的数据库上，以实现扩容的目的；

水平拆分：将同一块业务的数据库表，进行拆分，将一张表的数据根据一定的规则（取模，hash等）切分到不同的数据库上。

平滑、高效扩容：随着业务系统的扩张，数据库中的数据量会不断增加，如果实现扩容，最为直接了当的办法就是直接增加服务器，从而实现更多数据的存储；

如何来完成高效、平滑的扩容呢， 可以按照以下架构进行

Mysql体系结构

索引的使用

索引概述

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
优势：
1） 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
2） 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
劣势：
1） 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
2） 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

索引结构

MySQL数据库中默认的存储引擎InnoDB的索引结构为B+树，而根据叶子节点的内存存储不同，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存储的是整行数据，在InnoDB中主键索引页被称为聚簇索引。其结构如下：

而非主键索引的叶子节点内容存储时的主键的值，在InnoDB中，非主键索引也被称为二级索引或辅助索引。其结构如下：

索引使用规则

创建索引:

--为user表的name, age, sex三个字段创建联合索引,索引名为:idx_user_name_age_sex
CREATE INDEX idx_user_name_age_sex ON USER(NAME, age, sex);

1、全值匹配 ，对索引中所有列都指定具体值。

该情况下，索引生效，执行效率高。

2、最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

3、范围查询右边的列，不能使用索引 。

4、不要在索引列上进行运算操作， 索引将失效。

5、字符串不加单引号，造成索引失效。

6、用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

7、以%开头的Like模糊查询，索引失效。

8、如果MySQL评估使用索引比全表更慢，则不使用索引。

9、is NULL ， is NOT NULL 有时索引失效。

10、in ， not in 有时索引失效。

**11、尽量使用覆盖索引，避免select ***

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。

12、如果查询列，超出索引列，也会降低性能。

TIP :
using index ：使用覆盖索引的时候就会出现
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：查找使用了索引，但是需要回表查询数据
using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要
回表查询数据

索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

  • 对查询频次较高，且数据量比较大的表建立索引。

  • 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

  • 使用唯一索引，区分度越高，使用索引的效率越高。

  • 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。

  • 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。

  • 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

存储引擎

MySQL体系架构

整个MySQL Server由以下组成
Connection Pool : 连接池组件
Management Services & Utilities : 管理服务和工具组件
SQL Interface : SQL接口组件
Parser : 查询分析器组件
Optimizer : 优化器组件
Caches & Buffers : 缓冲池组件
Pluggable Storage Engines : 存储引擎
File System : 文件系统

1） 连接层

最上层是一些客户端和链接服务，包含本地socket 通信和大多数基于客户端/服务端工具实现的类似于
TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程
池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务
器也会为安全接入的每个客户端验证它所具有的操作权限。
2） 服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部
分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解
析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，
最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，
这样在解决大量读操作的环境中能够很好的提升系统的性能。
3） 引擎层

存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通
信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
4）存储层

数据存储层， 主要是将数据存储在文件系统之上，并完成与存储引擎的交互。
和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要
体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。
这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

存储引擎介绍

MySQL中支持的存储引擎比较多,我们这里重点讲解两种, InnoDB 与 MyISAM

特点 InnoDB MyISAM
存储限制 64TB 256TB
事务安全 支持 -
锁机制 行锁(适合高并发) 表锁
B+树索引 支持 支持
哈希索引 -（具有自适应哈希索引功能） -
全文索引 支持(5.6版本之后) 支持
集群索引 支持 -
数据索引 支持 -
索引缓存 支持 支持
数据可压缩 支持 支持
空间使用 高 低
内存使用 高 低
批量插入速度 低 高
支持外键 支持 -

InnoDB存储引擎深度剖析

InnoDB体系结构

缓冲池模块
1). 介绍
InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间的I/O效率的差值，就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘I/O。
在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等。

2). 读取
在数据库中进行读取页的操作时， 首先将磁盘中读取到的页数据存放在缓冲池中， 下一次再读相同的页时， 首先判断缓冲池中是否存在，如果缓冲池被命中，则直接读取数据， 如果没有，则读取磁盘中的页数据。

3). 更新
而对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上，从而保证缓冲池中的数据与磁盘中的数据一致。页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时，都需要触发，出于整体的性能考虑，而是通过checkpoint机制刷新回磁盘。

4). 参数配置
在专用服务器上，通常将多达80％的物理内存分配给缓冲池。参数设置：

在InnoDB引擎中，允许有多个缓冲池实例，根据页的哈希值分配到不同的缓冲池实例中，从而减少数据库内部的资源竞争， 提升并发处理能力。 参数配置：

参数配置:
vi /etc/my.conf

innodb_buffer_pool_size=268435456

后台线程模块

1). Master Thread

主要负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性， 还包括脏页的刷新、合并插入缓存、undo页的回收 。

2). IO Thread

在InnoDB存储引擎中大量使用了AIO来处理IO请求, 这样可以极大地提高数据库的性能，而IO Thread主要负责这些IO请求的回调。

Thread 线程数 参数配置
read thread 4 innodb_read_io_threads
write thread 4 innodb_write_io_threads
insert buffer thread 1 -
log thread 1 -

3). Purge Thread
主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收。

4). Pager Cleaner Thread

新引入的一个用于协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞。

文件模块

1). frm文件
该文件是用来保存每个表的元数据信息的， 主要包含表结构定义 。

2). 系统表空间
系统表空间是InnoDB数据字典，二次写缓冲区，更改缓冲区和撤消日志的存储区 。系统表空间可以具有一个或多个数据文件， 默认情况下会在数据存放目录中创建一个名为 ibdata1 表空间数据文件。该文件名称可以通过参数 innodb_data_file_path 指定。

3). 独占表空间
innodb中设置了参数 innodb_file_per_table 为 1/ON，则会将存储的数据、索引等信息单独存储在一个独占表空间，因此也会产生一个独占表空间文件（ibd）

4). redo log
重做日志， 用于恢复提交事务修改的页操作 ， 用来保证事务的原子性和持久性。主要是解决 提交的事务没有执行完成但是数据库崩溃了，当数据库恢复之后，可以完整的恢复数据。在执行操作时，InnoDB存储引擎会首先将重做日志信息放到这个缓冲区 redo log buffer,然后按照不同的策略和频率将buffer中的数据刷新到重做日志中。redo log在磁盘中保存的名称为 ib_logfile0，ib_logfile1。

5). bin log
二进制日志，其中记录表结构中的数据变更，包含DDL与DML。

6). 其他
错误日志、查询日志、慢查询日志等。

InnoDB逻辑存储结构

1). 表空间
表空间是InnoDB存储引擎逻辑结构的最高层， 大部分数据都存在于共享表空间ibdata1中。如果用户启用了参数 innodb_file_per_table ，则每张表都会有一个表空间（xxx.ibd）,里面存放表中的数据、索引和插入缓存Bitmap页。其他的数据如undo log、插入缓存索引页、系统事务信息、二次写缓存都是在共享表空间中。

2). 段
表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB存储引擎是基于索引组织的，因此数据即是索引，索引即数据。数据段就是B+树的叶子节点， 索引段即为B+树的非叶子节点。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制。

3). 区
区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。

4). 页
页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。

5). 行
InnoDB 存储引擎是面向行的（row-oriented)，也就是说数据是按行进行存放的，每个页存放的行记录也是有硬性定义的，最多允许存放 16KB/2-200 行，即 7992 行记录。

checkpoint

1). 介绍
由于日常的DML语句操作时，首先操作的是缓冲池，并没有直接写入到磁盘，这有可能会导致内存中的数据与磁盘中的数据产生不一致的情况，而与磁盘中数据不一致的页我们成为"脏页"。 而checkpoint的工作，就是将内存中的脏页，在一定条件下刷新到磁盘。

如果在从缓冲池将页数据刷新到磁盘的过程中发生宕机，那么数据就无法恢复了；为了避免这种情况的发生，采用了Write Ahead Log(WAL)策略，即当事务提交时，先写重做日志(redo log)，再修改缓冲池数据页，最后通过Checkpoint刷新到磁盘（事务提交会触发checkpoint）。这样正在执行的事务，因为存在日志都可以被恢复，没有日志的事务还没有执行也不会丢失数据。

2). 作用
A. 缩短数据恢复时间
当数据库发生宕机时，数据库不用重做所有的日志，因为Checkpoint之前的页都已经刷新会磁盘了，故数据库只需要重做Checkpoint之后的日志就好，这样就大大缩短了恢复时间。

B. 缓冲池不够用时，需要先将脏页数据刷新到磁盘中；
当缓冲池不够用时, 根据LRU算法溢出最近最少使用的页, 如果此页是脏页,则强制执行Checkpoint, 刷新脏页到磁盘。

C. 重做日志不可用时，刷新脏页到磁盘；
redo log大小是固定的, 当前的InnoDB引擎中， 重做日志的设计都是循环使用的，并不是无限增大的。重做日志可以被重用的部分是已经不再需要的， 数据库发生宕机也不需要这部分的重做日志，因此可以被覆盖使用， 如果此时重做日志还需要使用，那么必须强制执行Checkpoint，将缓冲池中的页至少刷新磁盘, checkpoint移动到当前重做日志的位置。

3). 分类
A. Sharp Checkpoint
Sharp Checkpoint 发生在数据库关闭时，将所有的脏页都刷新回磁盘，这是默认的工作方式，参数：innodb_fast_shutdown=1。

B. Fuzzy Checkpoint
在InnoDB存储引擎运行时，使用Fuzzy Checkpoint进行页刷新，只刷新一部分脏页。

InnoDB主要特性

插入缓存

插入缓冲是InnoDB存储引擎关键特性中最令人激动的。
主键是行唯一的标识符，在应用程序中行记录的插入顺序一般是按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取。因此，在这样的情况下，插入操作一般很快就能完成。

但是，不可能每张表上只有一个聚集索引，在更多的情况下，一张表上有多个非聚集的辅助索引（secondary index）。比如，我们还需要按照name这个字段进行查找，并且name这个字段不是唯一的, 这样的情况下产生了一个非聚集的并且不是唯一的索引。在进行插入操作时，数据页的存放还是按主键id的执行顺序存放，但是对于非聚集索引，叶子节点的插入不再是顺序的了。这时就需要离散地访问非聚集索引页，插入性能在这里变低了。然而这并不是这个name字段上索引的错误，因为B+树的特性决定了非聚集索引插入的离散性。

InnoDB存储引擎开创性地设计了插入缓冲，对于非聚集索引的插入或更新操作，不是每一次直接插入索引页中，而是先判断插入的非聚集索引页是否在缓冲池中。如果在，则直接插入；如果不在，则先放入一个插入缓冲区中，好似欺骗数据库这个非聚集的索引已经插到叶子节点了，然后再以一定的频率执行插入缓冲和非聚集索引叶子节点的合并操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对非聚集索引执行插入和修改操作的性能。

两次写

当数据库写物理页时，如果宕机了，那么可能会导致物理页的一致性被破坏。
可能有人会说，重做日志不是可以恢复物理页吗？实际上是的，但是要求是在物理页一致的情况下。
也就是说，如果物理页完全是未写之前的状态，则可以用重做日志恢复。如果物理页已经完全写完了，那么也可以用重做日志恢复。但是如果物理页前面2K写了新的数据，但是后面2K还是旧的数据，则种情况下就无法使用重做日志恢复了。

这里的两次写就是保证了物理页的一致性，使得即使宕机，也可以用重做日志恢复。
在写物理页时，并不是直接写到真正的物理页上去，而是先写到一个临时页上去，临时页写完后，再写物理页。这样一来：
A. 如果写临时页时宕机了，物理页还是完全未写之前的状态，可以用重做日志恢复
B. 如果写物理页时宕机了，则可以使用临时页来恢复物理页
InnoDB中共享表空间中划了2M的空间，叫做double write，专门存放临时页。
InnoDB还从内存中划出了2M的缓存空间，叫做double write buffer，专门缓存临时页。

每次写物理页时，先写到double write buffer中，然后从double write buffer写到double write上去。最后再从double write buffer写到物理页上去。

自适应哈希索引

在InnoDB中默认支持的索引结构为 B+ 树，B+ 树索引可以使用到范围查找，同时是按照顺序的方式对数据进行存储，因此很容易对数据进行排序操作，在联合索引中也可以利用部分索引键进行查询 。
而对于Hash索引则只能满足 =，<>,in查询，不能使用范围查询， 而且数据的存储是没有顺序的。MySQL 默认使用 B+ 树作为索引，因为 B+ 树有着 Hash 索引没有的优点，那么为什么还需要自适应 Hash 索引呢？

这是因为B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般为3-4层，故需要3-4次查询。而 Hash 索引在进行数据检索的时候效率非常高，通常只需要 O(1) 的复杂度，也就是一次就可以完成数据的检索。虽然 Hash 索引的使用场景有很多限制，但是优点也很明显。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引（Adaptive Hash Index，AHI）。
注意，这里的自适应指的是不需要人工来指定，系统会根据情况自动完成。

什么情况下才会使用自适应 Hash 索引呢？如果某个数据经常被访问，当满足一定条件的时候，就会将这个数据页的地址存放到 Hash 表中。这样下次查询的时候，就可以直接找到这个页面的所在位置。值得注意的是，hash索引只能用于= ，in的查询，对于其他的查询类型，如范围匹配等是不能使用hash索引的。而且自适应 Hash 索引只保存热数据（经常被使用到的数据），并非全表数据。因此数据量并不会很大，因此自适应 Hash 也是存放到缓冲池中，这样也进一步提升了查找效率。

异步IO

为了提高磁盘的操作性能，在InnoDB存储引擎中使用异步非阻塞AIO的方式来操作磁盘。
与AIO对应的是Sync IO，如果是同步IO操作，则每进行一次IO操作，需要等待此次操作结束后才可以进行接下来的操作。但是如果用户发出的是一条索引扫描的查询，那么这条SQL查询语句可能需要扫描多个索引页，也就是需要进行多次的IO操作。每扫描一个页并等待其完成之后，再进行下一次扫描，这是没有必要的。
用户可以在发出一个IO请求后立即再发出另一个IO请求，当全部的IO请求发送完毕后，等待所有的IO操作完成，这就是AIO。

InnoDB事务

redo log

redo log叫做重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时,发生错误时, 进行数据恢复使用。

start transaction;
select balance from bank where name="Tom";
-- 生成 重做日志 balance=8000
update bank set balance = balance - 2000;
-- 生成 重做日志 account=2000
update finance set account = account + 2000;
commit;

mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Buffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程将缓存池刷新到磁盘。
当在执行刷新时，宕机或者断电，可能会丢失部分数据。所以引入了redo log来记录已成功提交事务的修改信息，并且在事务提交时会把redo log持久化到磁盘，系统重启之后在读取redo log恢复最新数据。
简单来说 ， redo log是用来恢复数据的 用于保障，已提交事务的持久化特性 ;

undo log

undo log 叫做回滚日志，用于记录数据被修改前的信息。他正好跟前面所说的重做日志所记录的相反，重做日志记录数据被修改后的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

ndo log 记录事务修改之前版本的数据信息，因此假如由于系统错误或者rollback操作而回滚的话可以根据undo log的信息来进行回滚到没被修改前的状态。

常见的SQL优化

数据准备:

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  DEFAULT CHARSET=utf8mb4;
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('1','Tom','25','2300');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('2','Jerry','30','3500');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('3','Luci','25','2800');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('4','Jay','36','3500');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('5','Tom2','21','2200');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('6','Jerry2','31','3300');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('7','Luci2','26','2700');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('8','Jay2','33','3500');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('9','Tom3','23','2400');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('10','Jerry3','32','3100');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('11','Luci3','26','2900');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('12','Jay3','37','4500');
CREATE INDEX idx_emp_age_salary ON emp(age,salary);

Order By优化

第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

多字段排序

了解了MySQL的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。where 条件和Order by 使用相同的索引，并且Order By 的顺序和索引顺序相同， 并且Orderby 的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现FileSort。

Filesort 的优化
通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快 Filesort的排序操作。对于Filesort ， MySQL 现在采用的是一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。
MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。
可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

Group by优化

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。如下 ：

优化后

从上面的例子可以看出，第一个SQL语句需要进行"filesort"，而第二个SQL由于order by null 不需要进行 "filesort"， 而上文提过Filesort往往非常耗费时间。

limit优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 5000000,10 ，此时需要MySQL排序前5000010 记录，仅仅返回5000000 - 5000010 的记录，其他记录丢弃，查询排序的代价非常大 。
limit分页操作, 越往后, 性能越低

优化方案：

--可以通过只查主键,再通过关联主键查询结果再查询出具体行数据
select * from tb_sku t , (select id from tb_sku order by id limit 9000000,1) a where t.id = a.id;

count优化

在很多的业务系统中，都需要考虑进行分页操作，但是当我们执行分页操作时，都需要进行一次count操作，求取总记录数，如果数据库表的数据量大，在InnoDB引擎中，执行count操作的性能是比较低的，需要遍历全表数据，对计数进行累加。
优化方案：
①. 在大数据量的查询中，只查询数据， 而不展示总记录数 ；
②. 通过缓存redis维护一个表的计数，来记录数据库表的总记录数，在执行插入/删除时，需要动态更新；但是这种带where条件的就没法子了。
③. 在数据库表中定义一个大数据量的计数表，在执行插入/删除时，需要动态更新。同上一样无法带where条件。

posted @ 2021-02-22 19:36  有梦想的老王  阅读(735)  评论(0)  编辑  收藏  举报