mysql数据结构以及常见sql优化

mysql 数据结构

mysql 数据结构有四种

二叉树

这种数据结构树的高度太大，查找数据的时候不方便，且因为速度左右不平横，容易出现极端情况，就是一侧高度很高，但是一侧高度很低，两边高度不同导致最终执行时间相差太大

红黑树

这种数据结构克服了上面高度极端情况的问题，但是数据一多就会使得树的高度太高，这个问题依然存在
而且引入了新的问题，就是由于要维护两侧树的深度相近，需要进行频繁的节点变动，所以这种方案也不行

Hash

那么hash,行不行呢？我们来看一下hash 结构的优点和缺点
- 优点
  1. 取值的时候很方便，只需要hash 一下就可以了，这种算法比其他三种方式都要快
  2. 没有二叉树和红黑树那样的树结构，需要大量的查找才能找到节点，或者频繁的进行树的结构变动
- 缺点
  1. 由于是hash算法，不能模糊匹配，也不能范围查找
  2. 如果hash冲突，处理起来比较麻烦（参考hashMap 数据结构）

B-tree

让我们来看B-tree 的优点和缺点
- 优点
  1. 树的高度一致，不会出现极端情况
  2. 节点中的数据索引从左到右递增排列
- 缺点
  1. 子节点和非子节点都存储数据，所以树的高度还是偏高
  2. 节点与节点之间分离，所以遍历起来不方便，在范围查询的时候效率低
- B-tree图解
这个时候我们对B-tree 的结构做一点调整，引入了B+tree，我们来看B+tree 的有点和缺点
- 优点
  1. 非叶子节点不存储data，只存储索引，所以树的高度不会太高（一个节点可以储存16kb 数据，如果按照一个主键索引占用4个字节来算，那么一个节点可以存放4k个主键索引，大大降低了树的高度）
  2. 树的高度一致，不会出现极端情况（B+tree 是一种平衡树，节点左右高度一致）
  3. 叶子节点节点之前用指针连接，范围查询，或者遍历的时候效率很高
- 缺点
  1. 由于是树的结构，还是会出现树结构变动的情况，且高度一致，维护高度需要额外的时间
- 图解
以上的数据结构使得我们mysql 在选择底层数据结构的时候默认选择了B+tree 作为底层的数据结构，而B+tree 的数据结构特点，要求我们在存储数据的时候，必须指定一个主键ID(如果不指定，则会选择唯一索引，如果主键和唯一索引都没有，则会在当前行记录里面，插入一个隐藏列，用来存放mysql 自动生成的一个索引ID，这个机制在后面内容会讲到)
mysql在存储数据的时候，你插入一条记录，这条记录会插入mysql 的聚簇索引里面，什么是聚簇索引呢？就是存放所有一行记录里面所有字段的索引，也成为聚集索引，如下图

如上图，绿色部分是索引节点，蓝色部分是我们存储的数据，第一行是字段1 ，第二行是字段2，以此类推。聚集索引-叶节点包含了完整的数据记录
为什么是最左前缀法则？
1. 在我们使用mysql的sql 优化的时候，我们常常听别人说说起最左前缀法则，那么是么是最左前缀法则呢？
  - 在mysql 中，如果你要使用联合索引(什么是联合索引下面会讲到)来进行条件筛选，那么你必须遵守
    1. 你字段顺序必须按照你建立联合索引的字段顺序（这一点在高版本的mysql 已经可以自动优化了）
    2. 你的条件筛选必须按照建立索引时候的字段顺序依次使用，不可以跳过中间某一个字段
      - 比如 index(a,b,c) ,你做条件查询的时候只用了a和c 字段，这样是违反最左前缀法则的
    3. 在最左侧的条件或者中间条件筛选里面，不能使用模糊匹配或者范围查找
      - 比如 index(a,b,c),条件查询 a>111 and b= 'value1' and c='value2', 因为最左侧 a 字段使用范围查找，所以导致联合索引 index(a,b,c) 失效
2. 我们来看一个联合索引（联合索引是我们为了查询方便，对多个字段建立的一个索引，索引key 是多个字段，value 是主键id）
  - 联合索引的作用？
    1. 联合索引可以让我们在做条件筛选的时候，如果筛选字段都在联合索引内且不违反最左前缀法则，这个时候可以直接在联合索引里面进行条件筛选，而不去聚簇索引
    2. 聚簇索引通常比联合索引要大，所以联合索引的查找效率要比聚簇索引高
    3. 如果查询的字段恰好也在联合索引内，那么连回表都省了(什么是回表下面会讲)
  - 联合索引的弊端？
    - 因为联合索引是独立于聚簇索引，也就是我们插入的数据之外的，所以维护联合索引也需要耗费性能，联合索引字段越多，耗费的性能就约多
3. 最左前缀法则失效的原因？
  - 由于B+tree 结构有序性，联合索引再插入的时候，也是按照建立索引的时候的字段顺序来排列的，所以，如果当我们打破这种顺序，就会导致索引失效，看如下案列
    - sql：select * from table where name like "%value1%" and age=10 and position ="value2"
    - 这种情况下，即使是我们建立了索引，但是，mysql 在执行的时候，依然不会使用索引，而是会选择全表扫描，因为 name like “%value%” 这个条件，是一个模糊条件，可能查出很多个值，且查出值之后，会导致联合索引的第二个字段和第三个字段无序，我们根本无法判断从哪一个索引开始遍历查询
    - 同理，如果 name 是一个固定的值，比如name ="name" 但是 age>18 ，这个时候会不会用到索引呢？答案是会，但是只会用到一半。因为第二个字段是无序的，导致第三个字段无法按照索引顺序来查询
    - 如果还不理解，请参考如下案例，我有三个数字123，132，312，这个时候是按照顺序排列的，但是，如果我把中间这个数字 132 的中间的3 替换成一个未知的数字x,这个时候，你还能保证有序吗？
mysql 数据结构常见面试题
1. 为什么 B+ tree 要把 data 挪到子节点里面去
  - B+tree 非子节点不存储data，可以保证非子节点存储更多的索引
  - B-tree 节点存储data 会导致tree 高度过高
2. 常问的B树与B+树的区别是什么
  - B-tree 会在根节点存储数据，B+tree 只会在子节点存储数据，根节点存储索引冗余，提高访问速度
  - B+tree 子节点之间有指针相连接，可以提高访问速度
  - B+tree 和B -tree 都具有相同的树深度；且数据不重复的特点；且数据从左到右有序排列
3. 索引在B+树上如何快速定位
  - 从根节点利用数据结构有序性依层向子节点查找
4. 千万级数据表如何用B+树索引快速查找
  - 答案同上
5. 很少使用的索引底层结构Hash是怎样的
  - 通过对data 进行hash 运算得到 key , 然后用data作为 value, 存贮 key-value 数据
  - 如果有key 冲突，用同一key 节点，用链表作为存储的数据结构
  - hash 结构具有插入快，查找快的特点
  - 但是由于 hash 结构的特点，无法进行模糊匹配和范围查找
6. 为什么推荐使用自增整型的主键而不是UUID
  - 自增整型相比于 uuid 占用的内存更小
  - 因为B+tree 数据结构有序性，自增索引插入效率更快，uuid 插入效率慢，且有表结构变动的可能
7. 为什么非主键索引结构叶子节点存储的是主键值？
  - 一致性和节省存储空间
8. 索引最左前缀原则底层实现原理
  - 假如有三个字段 A、B、C组成一个联合索引，那么在底层数据结构存贮中则为 A-B-C 作为 key , 该行数据主键ID 作为key(inndb 必须有一个主键索引，不手动指定，存储引擎会默认指定一个)
  - 这个时候，如果我的查询条件用到了A 和 C ,没有用到 B ,或者B 是范围查找，那么使用索引查找的时候，只会用到 A （b 如果是范围查找的时候，也可能用到B）
  - 因为数据结构的有序性，在确定A 的范围只会，如果没有指定 B ，或者B 是个范围，那么C 就无法使用索引，因为无法确定B 之后，A 后面的数据结构就乱了，C 的索引自然就失效了
9. 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
  1. B+tree 数据结构必须有一个主键
  2. 整型比字符类型节约跟多空间
  3. 整型比字符类型插入更快（插入有序，字符类型需要转码）
  4. 整型自增可以避免因为插入导致节点结构变化

msql 索引分类

聚集索引

叶子节点包含完整的记录（查询的时候不需要回表）

非聚集索引

节点不包含完整记录，索引和数据是分离的（查询的时候需要回表）

稠密索引

每个索引键值都对应有一个索引项

稀疏索引

相对于稠密索引，稀疏索引只为某些搜索码值建立索引记录

ps:什么是回表操作？

如下 sql1 select a,b,d from table1 where a=1 and b= 2 and c=3，如果当前表有联合索引且包括 a,b,c 三个字段，那么在进行条件筛选的时候，只需要查询联合索引，但是，查询完联合索引之后我们需要得到 a,b,d 三个字段的值，这个时候，其实就需要根据联合索引拿到主键索引ID然后回到聚簇索引得到 a,b,d 三个字段的值

mysql 的执行引擎

mysql 执行引擎主要有两种（其实不止两种，只不过其他的现在用的很少了，所以这里不做讨论）

mysiam

myisam 索引文件和数据文件是分开的（非聚集的）
存储文件，mysiam 存储文件在mysql 的data目录下，目录主要有三个文件
1. tableName.myi 索引数据
2. tableName.myd myisam索引数据（这两个文件是mysiam引擎特有的）
3. tableName.frm 表结构（这个文件是所有的执行引擎都有的）

innodb

5.5.5 开始 innodb 成为默认引擎
表数据文件本身就是按照B+tree 组织的一个索引结构文件
聚集索引-叶节点包含了完整的数据结构
存储文件，innodb存储文件在mysql 的data目录下，目录主要有三个文件
1. tableName.frm 表结构
2. tableName.ibd 索引文件和数据文件一起（这个文件是innodb引擎特有的）

mysql 优化

expain 详解

expain 是什么？ expain mysql提供的一种分析sql 的工具，可以帮助程序员分析 sql 执行的效率

expain 使用？ 直接在要执行的sql 前面加上 expain 关键字就可以了，如下

expain select * from table1

expain 各字段详解

1. id列

id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

2. select_type列

select_type 表示对应行是简单还是复杂的查询。

simple：简单查询。查询不包含子查询和union
primary：复杂查询中最外层的 select select （select * from xxx） from (select * from xxx) 最外边的就是primary
subquery：子查询，select 语句里面的
derived：衍生表，from 后面的

3. table列

这一列表示 explain 的一行正在访问哪个表。
当 from 子句中有子查询时，table列是格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id。

4. type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。

依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL

一般来说，得保证查询达到range级别，最好达到ref

NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表
const：可以直接命中主键索引或者唯一索引
system：查询的行数只有一行，这是 const 查询的特殊情况
eq_ref：primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。
- 说人话就是，关联查询的时候，使用主键索引或者唯一索引
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
- 使用普通索引来查询值
- 用到了唯一索引的部分前缀
- 可能查找出多个值
range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
- 用了索引，但是用索引的时候是一个范围查找
index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。
- 假设我要查询 table1 中的两个字段，但是这两个字段刚好都有索引（即，刚好有主键索引和普通索引，或者有覆盖索引），这种情况下，即使是全表扫描，但是依然走了索引，属于index 级别
  - 注意,如果在这种情况下，扫描主键索引和扫描二级索引都可以得到结果，那么会优先扫描二级索引，因为二级索引更小
  - 这种情况，虽然用了索引，但是查询效率不高，因为数据在磁盘上，io操作还是非常消耗时间
all：全表扫描
- 扫描的聚簇索引的子节点

5. possible_keys列

这一列显示查询可能使用哪些索引来查找。

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

如果该列有值，则说明mysql 分析计划觉得会走索引
如果该列有值，但是key 没有值，则说明分析计划有值，但是key 没有值，说明执行计划没有没有走索引

6. key列

这一列显示mysql实际采用哪个索引来优化对该表的访问。

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

7. key_len列

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

key_len计算规则如下：

字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节
- char(n)：如果存汉字长度就是 3n 字节
- varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串
数值类型
- tinyint：1字节
- smallint：2字节
- int：4字节
- bigint：8字节　　
时间类型　
- date：3字节
- timestamp：4字节
- datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

8. ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

如果用到的是常量，显示const
如果用到的是字段，显示字段名称
可能显示多个

9. rows列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

这是一个预估算法，不是确定值

10. Extra列

这一列展示的是额外信息。常见的重要值如下：

Using index：使用覆盖索引
覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值
- 说人话就是，你要查找的值都在同一颗索引树里面，不需要回表
Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖
- select 列里面没有覆盖索引，且 where 条件字段没有索引
Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围（即where 条件用到了联合索引最左字段）；
Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
- 比如 distinct
  - 如果没有索引，需要先创建一张临时表，然后把结果放到临时表里面，再去重
  - 如果有索引，可以直接在从索引里面把结果拿出来
Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
- 如果有索引，直接(覆盖)索引排序
- 如果没有索引，而且数据量大的话，需要用到磁盘排序
Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是

mysql 单表sql 优化

影响sql 执行计划的因素

sql 执行计划主要由三个方面决定：数据量，查询条件，查询字段/或者说索引（注意，这是基本原则，所有的sql优化都是在这个基础上的，这也是搞懂sql 优化的基础，你如果不懂这个，直接去看sql优化的方法，有时候会出现你意想不到的结果）

数据量：在同一条sql中，比如 select a,b,d from table1 where a like 'a%' and b= 'b' and c='c' ，数据量是100 和数据量是10000 即使其他条件一模一样，但是执行计划是有差异的
查询条件：对比两条sql： sql1(select a,b,d from table1 where a like 'a%' and b= 'b' and c='c') sql2(select a,b,d from table1 where a like 'zzz%' and b= 'b' and c='c') 发现了吗，只有 a的查询条件是不一样的，在其他条件相同情况下，sql1 可能走全表扫描，sql2 可能走索引，这是因为，sql1 判定 a>'a' 可能需要扫描很多行记录，而 sql2 判定 a like 'zzz%' 可能只要扫描少数几行记录，sql 执行查询条件之后，还需要回表查出 a,b,d 三个字段，所以，执行计划可能sql2 走索引，sql1 走全表扫描
字段/索引：这个主要就是和覆盖索引有关了，如果 sql 查询的字段刚好都在联合索引里面，其他条件不变，sql 可能走索引(为什么说可能？因为可能查询条件不在索引里面，索引这个时候可能还得走聚集索引来进行条件筛选)

sql 优化

使用索引优化
- 主键索引
  - 主键索引不必说，基本使我们创建表的时候肯定会创建的一个索引，这也是mysql 储存数据的时候必须有的一个索引（没有mysql 自动生成一个）这个索引字段类型一般为 int 或者 bigInt ,然后设置为自增
  - ```
  使用：primary index(ID字段)
```
- 唯一索引
  - 唯一索引在一张表缺失主键索引的情况下 msql 会默认将他充当主键索引来使用
  - 当业务要求某一个字段在表中要具有唯一性的时候，阿里操作手册明确推荐使用数据库唯一索引来实现业务要求，而不是用java来实现约束
  - 当然，使用这个索引，你的数据库在插入数据时候，唯一索引校验需要额外的开销，这个使用情况看业务要求决定
  - ```
  使用：UNIQUE INDEX (唯一索引字段)
```
- 普通索引
  - 如果我们某一个字段查询非常频繁（一般是条件筛选的时候经常用到），这个时候我们可以给他设置一个普通索引，这样可以加快sql执行速度
  - ```
  使用：index(字段1)
```
- 联合索引
  - 这个索引就是对上面普通索引的升级版，可以指定多个字段作为索引
  - 用起来也很方便，这个一般在经常使用的查询sql 里面查询的字段和条件筛选都可以用到
    - 举例 sql：select d,e,f from table1 where a='value1' and b='value2' and c='value3'，这个时候使用使用联合索引就非常合适了，有两种方案
      - 第一种：index(a,b,c)
        
        优点：
        
        这种方案优势很明显，由于我们的sql 条件筛选字段都包含在了联合索引里面，所以，在条件筛选的时候大概率就会走联合索引，而不是全表扫描
        
        而且，索引的字段少，在维护索引开销的资源消耗小
        
        缺点：
        
        其实缺点也很明显，就是我们条件筛选之后，需要回表来查询 d,e,f 三个字段
        
        而且，如果条件筛选是范围查询或者模糊匹配的话（就是违反最左前缀法则），索引会失效，还是会走全表扫描
      - 第二种：index(a,b,c,d,e,f) ,
        
        优点
        
        这种方案不管是条件筛选还是查询字段都会在索引内，不会走全表扫描，也不用回表，十分快捷
        
        支持范围查询和模糊匹配，即使你的sql 违法最左前缀法则，也依然会走索引
        
        缺点
        
        索引字段过多，导致索引维护需要很大的开销
        
        索引字段太多，占用磁盘空间。这相当于把表重新拷贝一份了
  - ```
  使用：index(字段1,字段2,字段3)
```
关于索引下推
- 首先来讲一下什么是索引下推，我们来举个例子，如下sql ，select * from table a=111 and b=222 and c=333 ,这条sql 如果没有索引下推的话，那么sql 在mysql 执行引擎中是这样的
  1. 先查询 table 表中的 a 符合条件的
  2. 根据第一步结果筛选 b 符合条件的
  3. 根据第二部结果筛选 c 符合条件的
- 发现了没有，如果 a 字段筛选之后的结果非常大，那么这样是非常损耗性能的
- 那么引入了索引下推之后的执行引擎执行sql 会是怎么样子的呢
  1. 先查询 table 表中的 a 符合条件的
  2. 找到一个符合 a条件的，然后直接判断 b 是否也符合条件，然后判断c 是否符合条件
  3. 重复以上步骤，直到所有的行记录都被扫描
- 索引下推在mysql 5.6 之后推出，且只能用于二级索引，对一级索引（聚簇索引无效）
- 索引下推可以大大减少回表次数，用的好的话可以极大提升sql 执行性能
模糊查询
- 关于 like "%value1%" 情况
  - select a,b,c,d from table where a like "%value1%" ，这种情况其实我们都知道，因为违反最左前缀原则，这回导致索引失效，是的执行引擎最终在执行sql的时候走全表扫描
  - 优化：尽量通过其他手段把 like "%value1%" 改成 like "value%" 这样至少不违法最左前缀法则，有索引情况下还可以走索引
- 关于 select a,b,c,d from table where a like "%value1%" and b =‘value2’ and c='value3'
  - 这种情况同上，即使你设置了联合索引 index (a,b,c) 但是由于违背了最左前缀原则，联合索引会失效，导致最终执行引擎会走全表扫描
  - 优化：这种除了上面把 like "%value1%" 改成 like "value%" 这种方式之外，可以设置联合索引index(a,b,c,d)，让查询字段和条件筛选字段全部被联合索引覆盖，当然，这种适用于数据量不是特别多的情况下，如果你的数据量大几十万或者已经上百万了，你还是想办法用缓存吧，联合索引毕竟维护成本还是挺高的
关于 order by 和 group by排序
- 这里补充两个概念，filesort 和index
  - filesort 如果你的排序字段没有用到索引，那么 sql 在执行计划 ext 字段里面就会出现这个值，这代表，mysql 在执行完条件查询之后，还需要单独使用一块内存空间来进行排序，这种效率很低，一般建议使用索引来优化掉
  - index 如果你的排序字段用到了索引，那么你的 sql 在执行计划里面出现这个值，这种执行效率较高
- 补充一个知识点单路排序和双路排序
  - 单路排序，sql 在查询完之后，进行排序的时候，将所有的数据（包括排序字段和非排序字段，只要是select 后面的都放）放到内存（mysql 内有一块内存专门用来进行排序的）里面进行排序，然后返回给客户端
  - 双路排序，sql 在查询完之后，将主键id 和排序字段放入内存里面进行排序（这种是因为结果集太大了，内存不够），排序完成之后再回表，所有步骤完成之后，再返回客户端。这种很慢，效率很低
  - 参考到以上两种排序模式，我们尽量在排序的时候让我们的结果集小一点
关于count这个字段
- 如下四个个 count 使用场景，你觉得哪个执行最快？
  - count(*)，count(1)，count(字段)，count(id)
- 其实要分两种情况来讨论，字段1 有索引，和字段1 没有索引
  - 在字段1 有索引情况下，执行速度 coumt(*)=count(1)>count(字段1)>count(id)
    - 为什么？
      - count(*)， mysql做了特殊处理，其实这玩意执行很快的，别被网上那些文章骗了
      - count(1)，其实和count (*) 差不多，这个不用查询字段，只要不断得累加行记录就可以了
      - 来，我们重点说一下 count(字段1)，count(id),这两个，为什么有索引情况下，count(字段) 比 count(id) 快? 因为，count(字段) 这里会走二级索引，count(id) 要走一级索引，二级索引比一级索引小，所以二级索引比一级索引快
  - 在字段1 没有索引情况下，执行速度 coumt(*)=count(1)>count(id)>count(字段1)
    - 为什么？
      - 前面两个上面解释过了，这里不重复
      - 后面两个，由于没有二级索引，所以这里count(id)和 count(字段1) 都要走全表扫描，但是！！！主键索引具有唯一性，不可重复，所以直接累加就可以了，普通索引你还要判断它是否重复，不重复才能累加，所以 count(id) 比 count(字段快)
关于 or 和 in
- 这两个其实没什么好说的，但是有一点要注意，就是 or 和 in 在有索引情况下，如果数据量小，会走全表扫表，而不会走索引，因为回表，如果数据量大，会走索引
关于 in 和 exsits
- 基本原则，小表驱动大表如下 sql：select * from A where id in/exsits (select id from B)
  - in：当B表的数据集小于A表的数据集时，in优于exists
  - exsits：当A表的数据集小于B表的数据集时，exists优于in
    - 这里可能很多同学没用过 exsits，我解释一下：将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

mysql 多表查询

表连接查询方式

首先，我们把连接方式分为有索引连接和无索引连接，有索引连接又可以分为，主键索引/唯一键索引连接和普通索引连接这几种情况
1. 嵌套循环连接 Nested-Loop Join(NLJ) 算法
  - 这种连接方式一般是有索引情况下，这里基本原则是小表驱动大表（但是注意，这里必须是主键索引或者唯一键索引，普通索引会失效）
  - 假设 table1 有1w条数据，table2 有100 条数据，这个时候mysql 会怎么做？
    1. 从 table2 去一条数据根据 id 去 table 1 找出这条数据，然后拼接
    2. 重复第一步
  - 这个时候我们来看，mysql 的io次数是100+100=200 次，这种效率还是很高的，所以我们做表连接查询的时候，尽量使用主键索引来连接，但是切记，连接的表别太多，最多三张（阿里手册推荐）
2. 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
  - 这种连接方式通常在没有索引情况下（或者索引失效），这里基本原则也是小表驱动大表，但是不一定有效，因为没有主键索引和唯一索引，mysql 无法判断哪一张表示大表
  - 假设 table1 1w 条数据，table 2 100 条数据，那么mysql 会怎么做？
    - mysql 会把这10100 条数据全部加载到内存中（mysql 专门有这么一块内存做表连接，上面的nlj 也是用的这个内存如果内存不够，那么就分端加载），然后在内存中进行逐一比对
    - 这个时候，io次数是10100 次，比对次数是100*10000=100w 次，这个就比较吓人了，而且效率很低

posted @ 2023-01-13 23:35 刘阿泽阅读(170) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

璃天沫

mysql数据结构以及常见sql优化

mysql数据结构以及常见sql优化