教小白：如何完成一次快速的SQL查询

柔情只为你懂 2022-11-05 05:18 145阅读 0赞

**点击关注公众号，回复****面试****获取优质资料**![format_png][]

天下功夫，唯快不破！![format_png 1][]

## 1. MySQL查询慢是什么体验？ ##

谢邀，利益相关。

大多数互联网应用场景都是读多写少，业务逻辑更多分布在写上。对读的要求大概就是要快。那么都有什么原因会导致我们完成一次出色的慢查询呢？

### 1.1 索引 ###

在数据量不是很大时，大多慢查询可以用索引解决，大多慢查询也因为索引不合理而产生。

MySQL 索引基于 B+ 树，这句话相信面试都背烂了，接着就可以问最左前缀索引、 B+ 树和各种树了。

说到最左前缀，实际就是组合索引的使用规则，使用合理组合索引可以有效的提高查询速度，为什么呢？

因为索引下推。如果查询条件包含在了组合索引中，比如存在组合索引（a,b)，查询到满足 a 的记录后会直接在索引内部判断 b 是否满足，减少回表次数。同时，如果查询的列恰好包含在组合索引中，即为覆盖索引，无需回表。索引规则估计都知道，实际开发中也会创建和使用。[金三银四，如何准备][Link 1]

#### 1.1.1 什么原因导致索引失效 ####

建了索引还慢，多半是索引失效（未使用），可用 explain 分析。索引失效常见原因有 :

1.  where 中使用 != 或 <> 或 or 或表达式或函数（左侧）
2.  like 语句 % 开头
3.  字符串未加’’
4.  索引字段区分度过低，如性别
5.  未匹配最左前缀

(一张嘴就知道老面试题了) 为什么这些做法会导致失效，成熟的 MySQL 也有自己的想法。

#### 1.1.2 这些原因为什么导致索引失效 ####

如果要 MySQL 给一个理由，还是那棵 B+ 树。

##### 函数操作 #####

当在 查询 where = 左侧使用表达式或函数时，如字段 A 为字符串型且有索引, 有 `where length(a) = 6`查询，这时传递一个 6 到 A 的索引树，不难想象在树的第一层就迷路了。

##### 隐式转换 #####

隐式类型转换和隐式字符编码转换也会导致这个问题。

*  隐式类型转换对于 JOOQ 这种框架来说一般倒不会出现。
 *  隐式字符编码转换在连表查询时倒可能出现，即连表字段的类型相同但字符编码不同。

##### 破坏了有序性 #####

至于 Like 语句 % 开头、字符串未加 ’’ 原因基本一致，MySQL 认为对索引字段的操作可能会破坏索引有序性就机智的优化掉了。

不过，对于如性别这种区分度过低的字段，索引失效就不是因为这个原因。

#### 1.1.3 性别字段为什么不要加索引 ####

> 为什么索引区分度低的字段不要加索引。盲猜效率低，效率的确低，有时甚至会等于没加。

对于非聚簇索引，是要回表的。假如有 100 条数据，在 sex 字段建立索引，扫描到 51 个 male，需要再回表扫描 51 行。还不如直接来一次全表扫描呢。

所以，InnoDB 引擎对于这种场景就会放弃使用索引，至于区分度多低多少会放弃，大致是某类型的数据占到总的 30% 左右时，就会放弃使用该字段的索引，有兴趣可以试一下。

#### 1.1.4 有什么好用且简单的索引方法 ####

前面说到大多慢查询都源于索引，怎么建立并用好索引。这里有一些简单的规则。

*  索引下推：性别字段不适合建索引，但确实存在查询场景怎么办？如果是多条件查询，可以建立联合索引利用该特性优化。
 *  覆盖索引：也是联合索引，查询需要的信息在索引里已经包含了，就不会再回表了。
 *  前缀索引：对于字符串，可以只在前 N 位添加索引，避免不必要的开支。假如的确需要如关键字查询，那交给更合适的如 ES 或许更好。
 *  不要对索引字段做函数操作
 *  对于确定的、写多读少的表或者频繁更新的字段都应该考虑索引的维护成本。

#### 1.1.5 如何评价 MySQL 选错了索引 ####

有时，建立了猛一看挺正确的索引，但事情却没按计划发展。就像“为啥 XXX 有索引，根据它查询还是慢查询”。

此刻没准要自信点：我的代码不可能有 BUG，肯定是 MySQL 出了问题。MySQL 的确可能有点问题。

这种情况常见于建了一大堆索引，查询条件一大堆。没使用你想让它用的那一个，而是选了个区分度低的，导致过多的扫描。造成的原因基本有两个：

*  信息统计不准确：可以使用 `analyze table x`重新分析。
 *  优化器误判：可以 `force index`强制指定。或修改语句引导优化器，增加或删除索引绕过。

但根据我浅薄的经验来看，更可能是因为你建了些没必要的索引导致的。不会真有人以为 MySQL 没自己机灵吧？

除了上面这些索引原因外，还有下面这些不常见或者说不好判断的原因存在。

### 1.2 等MDL锁 ###

在 MySQL 5.5 版本中引入了 MDL，对一个表做 CRUD 操作时，自动加 MDL 读锁；对表结构做变更时，加 MDL 写锁。读写锁、写锁间互斥。

当某语句拿 MDL 写锁就会阻塞 MDL 读锁，可以使用`show processlist`命令查看处于`Waiting for table metadata lock`状态的语句。

### 1.3 等 flush ###

flush 很快，大多是因为 flush 命令被别的语句堵住，它又堵住了 select 。通过`show processlist`命令查看时会发现处于`Waiting for table flush`状态。

### 1.4 等行锁 ###

某事物持有写锁未提交。

### 1.5 当前读 ###

InnoDB 默认级别是可重复读。设想一个场景：事物 A 开始事务，事务 B 也开始执行大量更新。B 率先提交， A 是当前读，就要依次执行 undo log ，直到找到事务 B 开始前的值。

### 1.6 大表场景 ###

在未二次开发的 MYSQL 中，上亿的表肯定算大表，这种情况即使在索引、查询层面做到了较好实现，面对频繁聚合操作也可能会出现 IO 或 CPU 瓶颈，即使是单纯查询，效率也会下降。

且 Innodb 每个 B+ 树节点存储容量是 16 KB，理论上可存储 2kw 行左右，这时树高为3层。我们知道，innodb\_buffer\_pool 用来缓存表及索引，如果索引数据较大，缓存命中率就堪忧，同时 innodb\_buffer\_pool 采用 LRU 算法进行页面淘汰，如果数据量过大，对老或非热点数据的查询可能就会把热点数据给挤出去。

所以对于大表常见优化即是分库分表和读写分离了。

#### 1.6.1 分库分表 ####

##### 方案 #####

是分库还是分表呢？这要具体分析。

*  如果磁盘或网络有 IO 瓶颈，那就要分库和垂直分表。
 *  如果是 CPU 瓶颈，即查询效率偏低，水平分表。

> 水平即切分数据，分散原有数据到更多的库表中。
> 
> 垂直即按照业务对库，按字段对表切分。

工具方面有 sharding-sphere、TDDL、Mycat。动起手来需要先评估分库、表数，制定分片规则选 key，再开发和数据迁移，还要考虑扩容问题。

##### 问题 #####

实际运行中，写问题不大，主要问题在于唯一 ID 生成、非 partition key 查询、扩容。

*  唯一 ID 方法很多，DB 自增、Snowflake、号段、一大波GUID算法等。
 *  非 partition key 查询常用映射法解决，映射表用到覆盖索引的话还是很快的。或者可以和其他 DB 组合。
 *  扩容要根据分片时的策略确定，范围分片的话就很简单，而随机取模分片就要迁移数据了。也可以用范围 + 取模的模式分片，先取模再范围，可以避免一定程度的数据迁移。

当然，如果分库还会面临事务一致性和跨库 join 等问题。

#### 1.6.2 读写分离 ####

##### 为什么要读写分离 #####

分表针对大表解决 CPU 瓶颈，分库解决 IO 瓶颈，二者将存储压力解决了。但查询还不一定。

如果落到 DB 的 QPS 还是很高，且**读远大于写**，就可以考虑读写分离，基于主从模式将读的压力分摊，避免单机负载过高，同时也保证了高可用，实现了负载均衡。

##### 问题 #####

主要问题有过期读和分配机制。

*  过期读，也就是主从延时问题，这个对于。
 *  分配机制，是走主还是从库。可以直接代码中根据语句类型切换或者使用中间件。

### 1.7 小结 ###

以上列举了 MySQL 常见慢查询原因和处理方法，介绍了应对较大数据场景的常用方法。

分库分表和读写分离是针对大数据或并发场景的，同时也为了提高系统的稳定和拓展性。但也不是所有的问题都最适合这么解决。

## 2. 如何评价 ElasticSearch ##

前文有提到对于关键字查询可以使用 ES。那接着聊聊 ES 。

### 2.1 可以干什么 ###

ES 是基于 Lucene 的近实时分布式搜索引擎。使用场景有全文搜索、NoSQL Json 文档数据库、监控日志、数据采集分析等。

对非数据开发来说，常用的应该就是全文检索和日志了。ES 的使用中，常和 Logstash, Kibana 结合，也成为 ELK 。先来瞧瞧日志怎么用的。

下面是我司日志系统某检索操作：打开 Kibana 在 Discover 页面输入格式如 “xxx” 查询。

该操作可以在 Dev Tools 的控制台替换为：

GET yourIndex/_search  
    {    
     "from" : 0, "size" : 10,  
     "query" : {  
            "match_phrase" : {  
                "log" : "xxx"       
            }  
        }  
    }

什么意思？Discover 中加上 “” 和 console 中的 match\_phrase 都代表这是一个短语匹配，意味着只保留那些包含全部搜索词项，且位置与搜索词项相同的文档。

### 2.2 ES 的结构 ###

在 ES 7.0 之前存储结构是 Index -> Type -> Document，按 MySQL 对比就是 database - table - id(实际这种对比不那么合理)。7.0 之后 Type 被废弃了，就暂把 index 当做 table 吧。

在 Dev Tools 的 Console 可以通过以下命令查看一些基本信息。也可以替换为 crul 命令。

1.  GET /\_cat/health?v&pretty：查看集群健康状态
2.  GET /\_cat/shards?v ：查看分片状态
3.  GET yourindex/\_mapping   :index mapping结构
4.  GET yourindex/\_settings   :index setting结构
5.  GET /\_cat/indices?v   :查看当前节点所有索引信息

重点是 mapping 和 setting ，mapping 可以理解为 MySQL 中表的结构定义，setting 负责控制如分片数量、副本数量。

以下是截取了某日志 index 下的部分 mapping 结构，ES 对字符串类型会默认定义成 text ，同时为它定义一个叫做 keyword 的子字段。这两的区别是：text 类型会进行分词， keyword 类型不会进行分词。

"******": {  
        "mappings": {  
          "doc": {  
            "properties": {  
              "appname": {  
                "type": "text",  
                "fields": {  
                  "keyword": {  
                    "type": "keyword",  
                    "ignore_above": 256  
                  }  
                }

### 2.3 ES 查询为什么快？ ###

分词是什么意思？看完 ES 的索引原理你就 get 了。

ES 基于倒排索引。嘛意思？传统索引一般是以文档 ID 作索引，以内容作为记录。倒排索引相反，根据已有属性值，去找到相应的行所在的位置，也就是将单词或内容作为索引，将文档 ID 作为记录。

下图是 ES 倒排索引的示意图，由 Term index，Team Dictionary 和 Posting List 组成。

![format_png 2][]

图中的 Ada、Sara 被称作 term，其实就是分词后的词了。如果把图中的 Term Index 去掉，是不是有点像 MySQL 了？Term Dictionary 就像二级索引，但 MySQL 是保存在磁盘上的，检索一个 term 需要若干次的 random access 磁盘操作。

而 ES 在 Term Dictionary 基础上多了层 Term Index ，它以 FST 形式保存在内存中，保存着 term 的前缀，借此可以快速的定位到 Term dictionary 的本 term 的 offset 。而且 FST 形式和 Term dictionary 的 block 存储方式都很节省内存和磁盘空间。

到这就知道为啥快了，就是因为有了内存中的 Term Index , 它为 term 的索引 Term Dictionary 又做了一层索引。

不过，也不是说 ES 什么查询都比 MySQL 快。检索大致分为两类。

#### 2.3.1 分词后检索 ####

ES 的索引存储的就是分词排序后的结果。比如图中的 Ada，在 MySQL 中 %da% 就扫全表了，但对 ES 来说可以快速定位。

#### 2.3.2 精确检索 ####

该情况其实相差是不大的，因为 Term Index 的优势没了，却还要借此找到在 term dictionary 中的位置。也许由于 MySQL 覆盖索引无需回表会更快一点。

### 2.4 什么时候用 ES ###

如前所述，对于业务中的查询场景什么时候适合使用 ES ？我觉得有两种。

#### 2.4.1 全文检索 ####

在 MySQL 中字符串类型根据关键字模糊查询就是一场灾难，对 ES 来说却是小菜一碟。具体场景，比如消息表对消息内容的模糊查询，即聊天记录查询。

但要注意，如果需要的是类似广大搜索引擎的关键字查询而非日志的短语匹配查询，就需要对中文进行分词处理，最广泛使用的是 ik 。Ik 分词器的安装这里不再细说。

什么意思呢？

##### 分词 #####

开头对日志的查询，键入 “我可真是个机灵鬼” 时，只会得到完全匹配的信息。

而倘若去掉 “”，又会得到按照 “我”、“可”，“真”….分词匹配到的所有信息，这明显会返回很多信息，也是不符合中文语义的。实际期望的分词效果大概是“我”、“可”、“真是”，“机灵鬼”，之后再按照这种分词结果去匹配查询。

这是 ES 默认的分词策略对中文的支持不友善导致的，按照英语单词字母来了，可英语单词间是带有空格的。这也是不少国外软件中文搜索效果不 nice 的原因之一。

对于该问题，你可以在 console 使用下方命令，测试当前 index 的分词效果。

POST yourindex/_analyze  
     {  
       "field":"yourfield",  
       "text":"我可真是个机灵鬼"   
    }

#### 2.4.2 组合查询 ####

如果数据量够大，表字段又够多。把所有字段信息丢到 ES 里创建索引是不合理的。使用 MySQL 的话那就只能按前文提到的分库分表、读写分离来了。何不组合下。

##### 1. ES + MySQL #####

将要参与查询的字段信息加上 id，放入 ES，做好分词。将全量信息放入 MySQL，通过 id 快速检索。

##### 2. ES + HBASE #####

如果要省去分库分表什么的，或许可以抛弃 MySQL ，选择分布式数据库，比如 HBASE , 对于这种 NOSQL 来说，存储能力海量，扩容 easy ，根据 rowkey 查询也很快。

以上思路都是经典的**索引与数据存储隔离的方案**了。

当然，摊子越大越容易出事，也会面临更多的问题。使用 ES 作索引层，数据同步、时序性、mapping 设计、高可用等都需要考虑。

毕竟和单纯做日志系统对比，日志可以等待，用户不能。

### 2.5 小结 ###

本节简单介绍了 ES 为啥快，和这个快能用在哪。现在你可以打开 Kibana 的控制台试一试了。

如果想在 Java 项目中接入的话，有 SpringBoot 加持，在 ES 环境 OK 的前提下，完全是开箱即用，就差一个依赖了。基本的 CRUD 支持都是完全 OK 的。

## 3. HBASE ##

前面有提到 HBASE , 什么是 HBASE ，鉴于篇幅这里简单说说。

#### 3.1 存储结构 ####

关系型数据库如 MySQL 是按行来的。

HBASE 是按列的（实际是列族）。列式存储上表就会变成：

下图是一个 HBASE 实际的表模型结构。![format_png 3][]

Row key 是主键，按照字典序排序。TimeStamp 是版本号。info 和 area 都是列簇（column Family），列簇将表进行横向切割。name、age 叫做列，属于某一个列簇，可进行动态添加。Cell 是具体的 Value 。

#### 3.2 OLTP 和 OLAP ####

数据处理大致可分成两大类：联机事务处理OLTP（on-line transaction processing）、联机分析处理OLAP（On-Line Analytical Processing）。

*  OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理。
 *  OLAP是数据仓库系统的主要应用，支持复杂分析，侧重决策支持，提供直观易懂的查询结果。

面向列的适合做 OLAP，面向行的适用于联机事务处理(OLTP)。不过 HBASE 并不是 OLAP ，他没有 transaction，实际上也是面向 CF 的。一般也没多少人用 HBASE 做 OLAP 。

#### 3.3 RowKey ####

HBASE 表设计的好不好，就看 RowKey 设计。这是因为 HBASE 只支持三种查询方式

1、基于 Rowkey 的单行查询 2、基于 Rowkey 的范围扫描 3、全表扫描

可见 HBASE 并不支持复杂查询。

#### 3.4 使用场景 ####

HBASE 并非适用于实时快速查询。它更适合写密集型场景，它拥用快速写入能力，而查询对于单条或小面积查询是 OK 的，当然也只能根据 rowkey。但它的性能和可靠性非常高，不存在单点故障。

## 4. 总结 ##

个人觉得软件开发是循序渐进的，技术服务于项目，合适比新颖复杂更重要。

如何完成一次快速的查询？最该做的还是先找找自己的 Bug，解决了当前问题再创造新问题。

本文列举到的部分方案对于具体实现大多一笔带过，实际无论是 MySQL 的分表还是 ES 的业务融合都会面临很多细节和困难的问题，搞工程的总要绝知此事要躬行。

来源：http://7li49.cn/PYcfy

推荐阅读 
    一个简单通用的Java后台管理系统，附项目地址
    
    徒手撸一个Spring Boot中的starter，解密自动化配置
    
    写给小白看的线程池，还有10道面试题
    
    面试诀窍
    
    点赞越多，bug越少

[format_png]: /images/20221023/e83feac91d3240cf88bd2d54f2d30325.png
[format_png 1]: /images/20221023/516b716738d1402e8ba86892da25c118.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzU4MDM3MDgyMA%3D%3D&chksm=fd555034ca22d9227a63f380444a6cbed806d8096af39e478a0bf1b7a9d674f3a72928844f6c&idx=1&mid=2247497951&scene=21&sn=070d021f91f3c202b8f7ad4d9ec38c9d#wechat_redirect
[format_png 2]: /images/20221023/1c380d1adce24925af73560198ba589e.png
[format_png 3]: /images/20221023/ec44fe027018435fa4eb22a3d75618de.png