MySQL基础知识整理笔记(一)

MySQL 基础知识整理笔记

此篇主要整理基础篇中的疑问
PS: 文章整理的知识内容及资料均来自极客时间《SQL 必知必会》专栏

1、在 MySQL 统计行数中,SELECT COUNT(*),SELECT COUNT(1) 和 SELECT COUNT(具体字段) 的查询效率具体如何?

在 MySQL InnoDB 存储引擎中,COUNT(*)COUNT(1) 都是对所有结果进行 COUNT。如果有 WHERE 子句,则是对数据表的数据行数进行统计。因此 COUNT(*)COUNT(1) 本质上没有区别,执行的复杂度都是 O(N),也就是采用全表扫描,进行循环 + 计数的方式进行统计。

在 MySQL MyISAM 存储引擎,统计数据表的行数只需要 O(1) 的时间复杂度,这是因为每张 MyISAM 的数据表都有一个 meta 信息存储了 row_count 值,而一致性则由表级锁来保证。因为 InnoDB 支持事务,采用行级锁和 MVCC 机制,所有无法像 MyISAM 一样,只维护一个 row_count 变量,因此需要采用扫描全表,进行循环 + 计数的方式来统计。

另外在 InnoDB 引擎中,如果采用 COUNT(*) 和 COUNT(1) 来统计数据行数,要尽量采用二级索引。因为主键采用的索引是聚簇索引,聚簇索引包含的信息多,明显会大于二级索引(非聚簇索引)【也就是说,统计起来自然就慢】。对于 COUNT(*) 和 COUNT(1) 来说,它们不需要查找具体的行,只是统计行数,系统会自动采用占用空间更小的二级索引来进行统计【也就是说,占用空间更小,速度更快】。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
总结:
1、列名为主键时,count(列名) 和 count(1) 和 count(*) 执行效率是一样的:
因为 explaintype 类型都为 index

2、列名不为主键,而且列名没有创建索引但是其他字段创建了索引:
count(1) = count(*) > count(列名):
因为 explaintype 类型 count(1)和count(*)都为indexcount(列名)的type 类型为 all

3、列名不为主键,但是列名创建索引:
count(1) = count(*) = count(列名):
因为 explaintype 类型 都为index

4、如果表多有个列并且没有主键,则
count(1) = count(*)

5、如果表只有一个字段,则
select count(*) 和 select count(1) 和 select count(列名) 执行效率一样。

2、在 MySQL 中,LIMIT 关键词是最后执行的,如果确认结果集就只有一条,为何还需要加上 LIMIT 1 进行优化?

如果你可以确定结果集只有1条,那么如果加上 LIMIT 1 的时候,当找到一条结果的时候就不会继续扫描,这样会加快查询速度。 如果数据表已经对字段建立了唯一索引,那么可以通过索引进行查询,不会全表扫描的话,就不需要加上 LIMIT 1了。

3、在 WHERE 子句中加索引可以快速定位数据,那为什么需要在 ORDER BY 字段也加上索引?

在 MySQL 中,支持两种排序方式,分别是 FileSort 和 Index 排序。在 Index 排序中,索引可以保证数据的有序性,不需要再进行排序,效率更高。而 FileSort 排序则一般在内存中进行排序,占用 CPU 较多。如果待排结果较大,会产生临时文件 I/O 到磁盘进行排序的情况,效率较低。所以使用 ORDER BY 子句时,应该尽量使用 Index 排序,避免使用 FileSort 排序。当然你可以使用 explain 来查看执行计划,看下优化器是否采用索引进行排序。

4、ORDER BY 是对分的组排序还是对分组中的记录排序呢?

这个要看情况,一般来说 ORDER BY 就是对记录进行排序。如果你在 ORDER BY 前面用到了 GROUP BY,实际上这是一种分组的聚合方式,已经把一组的数据聚合成为一条记录,再进行排序的时候,相当于对分的组进行排序。

文章目录
  1. 1. MySQL 基础知识整理笔记
    1. 1.1. 1、在 MySQL 统计行数中,SELECT COUNT(*),SELECT COUNT(1) 和 SELECT COUNT(具体字段) 的查询效率具体如何?
    2. 1.2. 2、在 MySQL 中,LIMIT 关键词是最后执行的,如果确认结果集就只有一条,为何还需要加上 LIMIT 1 进行优化?
    3. 1.3. 3、在 WHERE 子句中加索引可以快速定位数据,那为什么需要在 ORDER BY 字段也加上索引?
    4. 1.4. 4、ORDER BY 是对分的组排序还是对分组中的记录排序呢?
本站总访问量 | 本页面被访问 | 您是第位小伙伴

© XueSi博客 版权所有 备案号 : 赣ICP备19008485号-1