in关键字和exists关键字
in
尽量用在内表小的地方,exists
关键字用在外表小的地方。- 如果用not in,则内外表都全表扫描,无索引,效率低,可使用not exists,也可用连接优化。
- in关键字是把
外表和内表做hash连接
,先查询内表,再把内表的结果和外表匹配,对外表使用索引(外表效率高,可以用大表),而内表都需要查询,使用in关键字可以加快效率。 - exists关键字是对
外表做loop循环
,每次循环对内表进行查询(对内表可以使用索引,查询效率高,可以用大表),而外表有多大都需要遍历,使用exists关键字可以加快效率。
select * from A where A.id
in
(select id from B);对外表A
使用索引效率高,建议A为大表
。
select * from A whereexists
(select * from B where A.id=B.id);对内表B
使用索引效率高,建议B为大表
。因为外表A要全表,且要循环
。
COUNT
COUNT
是用来进行不为NULL
的行数统计的,有三种用法分别是:COUNT(列名)
、COUNT(常量)
和COUNT(*)
。- 区别:
(1)常量是一个固定值,不为NULL。
(2)*可以理解为查询整行,不为NULL。
(3)列名的查询结果有可能是NULL。
(4)所以COUNT(常量) 和 COUNT(**)表示直接查询符合条件
的数据表的行数。而COUNT(列名)表示查询符合条件的列的值不为NUL
L的行数。
(5)COUNT(※)是SQL92定义的标准统计行数
的语法,因为他是标准语法,所以MySQL数据库对他进行过很多优化。
SQL92,是数据库的一个ANSI/ISO标准。它定义了一种语言(SQL)以及数据库的行为(事务、隔离级别等)。
优化count
- 要区分不同的
执行引擎
,MySQL中比较常用的执行引擎是InnoDB和MyISAM。 MyISAM
不支持事务,支持表级锁,而InnoDB
支持事务,支持行级锁。- 因为
**MyISAM**
是表级锁,所以在一张表上的操作是串行
执行的。MyISAM可以把表的总行数单独记录
下来。如果从一张表中使用COUNT(*)进行查询,直接返回这个记录下来的数值就可以了,前提
是不能有where条件。(为什么MyISAM可以这样做呢?因为它是表级锁,不会有并发的数据库修改记录的行为,查询的行数是准确的
)。 InnoDB
则不适合这种缓存操作,它支持事务和行级锁,表的行数可能会被并发修改
,那么缓存记录下来的行数就不准确。那么InnoDB则要进行扫表,从MySQL 8.0.13开始SELECT COUNT(※)
在扫表过程中做了一些优化,前提
是查询语句中不包含WHERE或GROUP BY等条件。(我们的目的只是为了统计总行数,并不关心查到的具体值,所以可以选择一个成本较低的索引进行,节省时间
)。InnoD
B中索引分为聚簇索引(主键索引)和非聚簇索引(非主键索引),聚簇索引
的叶子节点中保存的是整行记录
,而非聚簇索引
的叶子节点中保存的是该行记录的主键的值
。相比之下,非聚簇索引要比聚簇索引小很多,所以MySQL会优先选择最小的非聚簇索引来扫表。所以,当我们建表的时候,除了主键索引以外,创建一个非主键索引还是有必要的。
引擎的区别
- MyISAM不支持事务,表级锁,而InnoDB支持事务,支持行级锁。
- InnoDB 支持外键,而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败;
- InnoDB 是聚集索引,MyISAM 是非聚集索引。
字段为什么要求定义为not null
- null值会占用更多的字节,且会在程序中造成很多与预期不符的情况。