【Mysql篇】了解Mysql(一)

73 阅读3分钟
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 where exists (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(列名)表示查询符合条件的列的值不为NULL的行数。
    (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等条件。(我们的目的只是为了统计总行数,并不关心查到的具体值,所以可以选择一个成本较低的索引进行,节省时间)。
  • InnoDB中索引分为聚簇索引(主键索引)和非聚簇索引(非主键索引),聚簇索引的叶子节点中保存的是整行记录,而非聚簇索引的叶子节点中保存的是该行记录的主键的值。相比之下,非聚簇索引要比聚簇索引小很多,所以MySQL会优先选择最小的非聚簇索引来扫表。所以,当我们建表的时候,除了主键索引以外,创建一个非主键索引还是有必要的。

引擎的区别

  • MyISAM不支持事务,表级锁,而InnoDB支持事务,支持行级锁。
  • InnoDB 支持外键,而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败;
  • InnoDB 是聚集索引,MyISAM 是非聚集索引。
字段为什么要求定义为not null
  • null值会占用更多的字节,且会在程序中造成很多与预期不符的情况。