此篇主要整理基础篇中的疑问
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、列名为主键,count(列名)
和count(1)
和count(*)
执行效率是一样的:因为 explain 中 type 类型都为 index
2、列名不为主键,而且列名没有创建索引但是其他字段创建了索引:count(1) = count(*) > count(列名)
;因
为expalin 中的 type 类型count(1)
和count(*)
类型都为 index 而count(列名)
的 type 类型为 all
3、列名不为主键,但是列名创建索引:count(1) = count(*)= count(列名)
;因为 explain 中 type 类型都为 index
4、如果表多个列并且没有主键,则count(1) = count(*)
5、如果表只有一个字段,则select count(*)
和select count(1)
和select count(列名)
执行效率一样。
2、在MySQL中,LIMIT
关键词是最后执行的,如果确认结果集就只有一条,为何还需加上LIMIT 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,实际上这是一种分组的聚合方式,已经把一组的数据聚合成为了一条记录,再进行排序的时候,相当于对分的组进行了排序。
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。