count(列名)、count(id)、count(1)、count(*)到底谁的性能高
count(普通字段)<count(id)<count(1)=count(*)
如果没有辅助索引,count(*)和coun(字段)
都需要扫描所有数据,但count(字段)
还需要判空,所以会慢一些
如果有辅助索引,就会选择比较小的辅助索引树来扫描,因为辅助索引叶子节点上没有存全量数据,所以扫描辅助索引要比扫描主键索引io次数少,也就更快
有辅助索引的情况下,还要看count的字段是不是索引字段,才能判断会不会扫描辅助索引树
执行效果:
1. count(1) and count(*)
当表的数据量大些时,对表作分析之后,使用count(1)还要比使用count(*)用时多了!
从执行计划来看,count(1)和count(*)的效果是一样的。 但是在表做过分析之后,count(1)会比count(*)
的用时少些(1w以内数据量),不过差不了多少。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(*)
,自动会优化指定到那一个字段。所以没必要去count(1),用count(*)
,sql会帮你完成优化的 因此: count(1)和count(*)基本没有差别!
2. count(1) and count(字段)
两者的主要区别是
(1) count(1) 会统计表中的所有的记录数, 包含字段为null 的记录。
(2) count(字段) 会统计该字段在表中出现的次数,忽略字段为null 的情况。即 不统计字段为null 的记录。
count(*) 和 count(1)和count(列名)区别
执行效果上 :
count(*)包括了所有的列,相当于行数,在统计结果的时候, 不会忽略列值为NULL
count(1)包括了忽略所有列,用1代表代码行,在统计结果的时候, 不会忽略列值为NULL
count(列名)只包括列名那一列,在统计结果的时候,会忽略列值为空(这里的空不是只空字符串或者0,而是表示null)的计数, 即某个字段值为NULL时,不统计。
执行效率上:
列名为主键,count(列名)会比count(1)快
列名不为主键,count(1)会比count(列名)快
如果表多个列并且没有主键,则 count(1) 的执行效率优于count(*)
如果有主键,则 select count(主键)的执行效率是最优的
如果表只有一个字段,则 select count(*)最优。
where 和on
用on
用where
on和where条件的区别如下:
(1) on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
(2) where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
如果有where条件,顺序是on 后where,
on是连接的条件 因为and在on后面 所以and也是连接的条件
where是连接后筛选条件 and是连接的条件
where和on的区别
where是对已经搜索出的结果的过滤条件
on是两者join形成新表时用到的匹配条件
连接过程用where
mysql 连表查询使用join 和 where 区别大么? 两个表用=和join连接什么区别?
如果是inner join
,没有区别,因为优化后都是一样的
如果是left join ,有区别,除非优化器可以通过逻辑优化判断两个表的结果是一样的,否则两个sql的执行是不一样的
他们的过程
1,from语句把student表 和 student_subject表从数据库文件加载到内存中。
2,join语句相当于对两张表做了乘法运算,把student表中的每一行记录按照顺序和student_subject表中记录依次匹配。
3,匹配完成后,我们得到了一张有 (student中记录数 × student_subject表中记录数)条的临时表。 在内存中形成的临时表如表1.0所示。我们又把内存中表1.0所示的表称为‘笛卡尔积表’。
针对以上的理论,我们提出一个问题,难道表连接的时候都要先形成一张笛卡尔积表吗,如果两张表的数据量都比较大的话,那样就会占用很大的内存空间这显然是不合理的。所以,我们在进行表连接查询的时候一般都会使用JOIN xxx ON xxx的语法,ON语句的执行是在JOIN语句之前的,也就是说两张表数据行之间进行匹配的时候,会先判断数据行是否符合ON语句后面的条件,再决定是否JOIN。
因此,有一个显而易见的SQL优化的方案是,当两张表的数据量比较大,又需要连接查询时,应该使用 FROM table1 JOIN table2 ON xxx的语法,避免使用 FROM table1,table2 WHERE xxx 的语法,因为后者会在内存中先生成一张数据量比较大的笛卡尔积表,增加了内存的开销。
日期与时间类型
MySQL有多种表示日期和时间的数据类型,不同的版本可能有所差异,MySQL8.0版本支持的日期和时间
类型主要有:YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型。
YEAR
类型通常用来表示年DATE
类型通常用来表示年、月、日TIME
类型通常用来表示时、分、秒DATETIME
类型通常用来表示年、月、日、时、分、秒TIMESTAMP
类型通常用来表示带时区的年、月、日、时、分、秒
YEAR | 年 | 1 | YYYY或YY | 1901 | 2155 |
TIME | 时间 | 3 | HH:MM:SS | -838:59:59 | 838:59:59 |
DATE | 日期 | 3 | YYYY-MM-DD | 1000-01-01 | 9999-12-03 |
DATETIME | 日期时间 | 8 | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
TIMESTAMP | 日期 时间 | 4 | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:00 UTC | 2038-01-19 03:14:07UTC |
可以看到,不同数据类型表示的时间内容不同、取值范围不同,而且占用的字节数也不一样,你要根据实际需要灵活选取。
为什么时间类型 TIME 的取值范围不是 -23:59:59~23:59:59 呢?原因是 MySQL 设计的 TIME 类型,不光表示一天之内的时间,而且可以用来表示一个时间间隔,这个时间间隔可以超过 24 小时。
DATE类型
DATE类型表示日期,没有时间部分,格式为YYYY-MM-DD
,其中,YYYY表示年份,MM表示月份,DD表示日期。需要3个字节的存储空间。在向DATE类型的字段插入数据时,同样需要满足一定的格式条件。
-
以
YYYY-MM-DD
格式或者YYYYMMDD
格式表示的字符串日期,其最小取值为1000-01-01,最大取值为9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式。例如:
INSERT INTO test_date1 VALUES ('2020-10-01'), ('20201001'),(20201001);
-
以
YY-MM-DD
格式或者YYMMDD
格式表示的字符串日期,此格式中,年份为两位数值或字符串满足YEAR类型的格式条件为:当年份取值为00到69时,会被转化为2000到2069;当年份取值为70到99时,会被转化为1970到1999。例如:
INSERT INTO test_date1 VALUES ('00-01-01'), ('000101'), ('69-10-01'), ('691001'), ('70-01-01'), ('700101'), ('99-01-01'), ('990101');
-
使用CURRENT_DATE() 或者NOW() 函数,会插入当前系统的日期。
例如:
INSERT INTO test_date1 VALUES (CURRENT_DATE()), (NOW());
TIME类型
TIME类型用来表示时间,不包含日期部分。在MySQL中,需要3个字节的存储空间来存储TIME类型的数
据,可以使用“HH:MM:SS”格式来表示TIME类型,其中,HH表示小时,MM表示分钟,SS表示秒。
在MySQL中,向TIME类型的字段插入数据时,也可以使用几种不同的格式。
(1)可以使用带有冒号的
字符串,比如' D HH:MM:SS' 、' HH:MM:SS '、' HH:MM '、' D HH:MM '、' D HH '或' SS '格式
,都能被正确地插入TIME类型的字段中。其中D表示天,其最小值为0,最大值为34。如果使用带有D格式的字符串
插入TIME类型的字段时,D会被转化为小时,计算格式为D*24+HH。当使用带有冒号并且不带D的字符串
表示时间时,表示当天的时间,比如12:10表示12:10:00,而不是00:12:10。
例如:INSERT INTO test_time1 VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45');
(2)可以使用不带有冒号的
字符串或者数字,格式为’ HHMMSS '或者HHMMSS 。如果插入一个不合法的字符串或者数字,MySQL在存
储数据时,会将其自动转化为00:00:00进行存储。比如1210,MySQL会将最右边的两位解析成秒,表示
00:12:10,而不是12:10:00。
例如:INSERT INTO test_time1 VALUES ('123520'), (124011),(1210);
(3)使用CURRENT_TIME() 或者NOW() ,会插入当前系统的时间。
例如:INSERT INTO test_time1 VALUES (NOW()), (CURRENT_TIME());
DATETIME类型
DATETIME类型在所有的日期时间类型中占用的存储空间最大,总共需要8 个字节的存储空间。在格式上
为DATE类型和TIME类型的组合,可以表示为YYYY-MM-DD HH:MM:SS
,其中YYYY表示年份,MM表示月
份,DD表示日期,HH表示小时,MM表示分钟,SS表示秒。
在向DATETIME类型的字段插入数据时,同样需要满足一定的格式条件。
-
以
YYYY-MM-DD HH:MM:SS
格式或者YYYYMMDDHHMMSS
格式的字符串插入DATETIME类型的字段时,- 最小值为1000-01-01 00:00:00,最大值为9999-12-03 23:59:59。
以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时,会被转化为YYYY-MM-DD
HH:MM:SS格式。
- 最小值为1000-01-01 00:00:00,最大值为9999-12-03 23:59:59。
-
使用函数CURRENT_TIMESTAMP() 和NOW() ,可以向DATETIME类型的字段插入系统的当前日期和
时间。
例如
INSERT INTO test_datetime1
VALUES ('2021-01-01 06:50:30'), ('20210101065030');
INSERT INTO test_datetime1
VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'),
('200101000000');
INSERT INTO test_datetime1
VALUES (20200101000000), (200101000000), (19990101000000), (990101000000);
INSERT INTO test_datetime1
VALUES (CURRENT_TIMESTAMP()), (NOW());
TIMESTAMP类型
TIMESTAMP类型也可以表示日期时间,其显示格式与DATETIME类型相同,都是YYYY-MM-DD HH:MM:SS
,需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多,只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中,UTC表示世界统一时间,也叫
作世界标准时间。
- 存储数据的时候需要对当前时间所在的时区进行转换,查询数据的时候再将时间转换回当前的时区。因此,使用TIMESTAMP存储的同一个时间值,在不同的时区查询时会显示不同的时间。
向TIMESTAMP类型的字段插入数据时,当插入的数据格式满足YY-MM-DD HH:MM:SS和YYMMDDHHMMSS
时,两位数值的年份同样符合YEAR类型的规则条件,只不过表示的时间范围要小很多。
如果向TIMESTAMP类型的字段插入的时间超出了TIMESTAMP类型的范围,则MySQL会抛出错误信息。
TIMESTAMP和DATETIME的区别:
TIMESTAMP
存储空间比较小,表示的日期时间范围也比较小- 底层存储方式不同,TIMESTAMP底层存储的是毫秒值,距离1970-1-1 0:0:0 0毫秒的毫秒值。
- 两个日期比较大小或日期计算时,TIMESTAMP更方便、更快。
- TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同,显示不同的结果。而DATETIME则只能
反映出插入时当地的时区,其他时区的人查看数据必然会有误差的。
开发经验
用得最多的日期时间类型,就是 DATETIME 。虽然 MySQL 也支持 YEAR(年)、 TIME(时间)、
DATE(日期),以及 TIMESTAMP 类型,但是在实际项目中,尽量用 DATETIME 类型。因为这个数据类型
包括了完整的日期和时间信息,取值范围也最大,使用起来比较方便。毕竟,如果日期时间信息分散在
好几个字段,很不容易记,而且查询的时候,SQL 语句也会更加复杂。
此外,一般存注册时间、商品发布时间等,不建议使用DATETIME
存储,而是使用时间戳
,因为
DATETIME虽然直观,但不便于计算。
SELECT UNIX_TIMESTAMP();
“由于 DECIMAL 数据类型的精准性,在我们的项目中,除了极少数(比如商品编号)用到整数类型外,其他的数值都用的是 DECIMAL,原因就是这个项目所处的零售行业,要求精准,一分钱也不能差。 ” ——来自某项目经理
CHAR与VARCHAR类型
字符串(文本) | 类型 | 特点长度 | 长度范围 | 占用的存储空间 |
---|---|---|---|---|
CHAR(M) | 固定长度 | M | 0 <= M <= 255 | M个字节 |
VARCHAR(M) | 可变长度 | M | 0 <= M <= 65535 | (实际长度 + 1) 个字节 |
CHAR类型:
CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M),则表示长度默认是1个字符。
如果保存时,数据的实际长度比CHAR类型声明的长度小,则会在右侧填充空格以达到指定的长
度。当MySQL检索CHAR类型的数据时,CHAR类型的字段会去除尾部的空格。
定义CHAR类型字段时,声明的字段长度即为CHAR类型字段所占的存储空间的字节数。
VARCHAR类型:
VARCHAR(M) 定义时, 必须指定长度M,否则报错。
MySQL4.0版本以下,varchar(20):指的是20字节,如果存放UTF8汉字时,只能存6个(每个汉字3字
节) ;MySQL5.0版本以上,varchar(20):指的是20字符
。
检索VARCHAR类型的字段数据时,会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间
为字符串实际长度加1个字节。
哪些情况使用 CHAR 或 VARCHAR 更好
类型 | 特点 | 空间上 | 时间上 | 适用场景 |
---|---|---|---|---|
CHAR(M) | 固定长度 | 浪费存储空间 | 效率高 | 存储不大,速度要求高 |
VARCHAR(M) | 可变长度 | 节省存储空间 | 效率低 | 非CHAR的情况 |
情况1:存储很短的信息。比如门牌号码101,201……这样很短的信息应该用char,因为varchar还要占个
byte用于存储信息长度,本来打算节约存储的,结果得不偿失。
情况3:十分频繁改变的column。因为varchar每次存储都要有额外的计算,得到长度等工作,如果一个
非常频繁改变的,那就要有很多的精力用于计算,而这些对于char来说是不需要的。
情况4:具体存储引擎中的情况:
MyISAM
数据存储引擎和数据列:MyISAM数据表,最好使用固定长度(CHAR)的数据列代替可变长
度(VARCHAR)的数据列。这样使得整个表静态化,从而使数据检索更快,用空间换时间。MEMORY
存储引擎和数据列:MEMORY数据表目前都使用固定长度的数据行存储,因此无论使用
CHAR或VARCHAR列都没有关系,两者都是作为CHAR类型处理的。InnoDB
存储引擎,建议使用VARCHAR类型。因为对于InnoDB数据表,内部的行存储格式并没有区
分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),而且主要影响性能的因素
是数据行使用的存储总量,由于char平均占用的空间多于varchar,所以除了简短并且固定长度的,
其他考虑varchar。这样节省空间,对磁盘I/O和数据存储总量比较好。
BLOB
BLOB是一个二进制大对象,可以容纳可变数量的数据。
MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型,它们可容纳值的最大
长度不同。可以存储一个二进制的大对象,比如图片、音频和视频等。
需要注意的是,在实际工作中,往往不会在MySQL数据库中使用BLOB类型存储大对象数据,通常会将图
片、音频和视频文件存储到服务器的磁盘上
,并将图片、音频和视频的访问路径存储到MySQL中。
TEXT和BLOB的使用注意事项:
在使用text和blob字段类型时要注意以下几点,以便更好的发挥数据库的性能。
① BLOB和TEXT值也会引起自己的一些问题,特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的" 空洞",以后填入这些"空洞"的记录可能长度不同。为了提高性能,建议定期使用 OPTIMIZE TABLE
功能对这类表进行碎片整理
。
② 如果需要对大文本字段进行模糊查询,MySQL 提供了前缀索引
。但是仍然要在不必要的时候避免检索大型的BLOB或TEXT值。例如,SELECT * 查询就不是很好的想法,除非你能够确定作为约束条件的WHERE
子句只会找到所需要的数据行。否则,你可能毫无目的地在网络上传输大量的值。
③ 把BLOB或TEXT列分离到单独的表中
。在某些环境中,如果把这些数据列移动到第二张数据表中,可以让你把原数据表中的数据列转换为固定长度的数据行格式,那么它就是有意义的。这会减少主表中的碎片
,使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT *
查询的时候不会通过网络传输大量的BLOB或TEXT值。
JSON 类型
JSON(JavaScript Object Notation)是一种轻量级的数据交换格式
。简洁和清晰的层次结构使得 JSON 成
为理想的数据交换语言。它易于人阅读和编写,同时也易于机器解析和生成,并有效地提升网络传输效
率。JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串,然后就可以在网络或者程序之间轻
松地传递这个字符串,并在需要的时候将它还原为各编程语言所支持的数据格式。
在MySQL 5.7中,就已经支持JSON数据类型。在MySQL 8.x版本中,JSON类型提供了可以进行自动验证的JSON文档和优化的存储结构,使得在MySQL中存储和读取JSON类型的数据更加方便和高效。 创建数据表,表中包含一个JSON类型的字段 js 。
字段设计规范
1、INT:如无特殊需要,存放整型数字使用UNSIGNED INT型,整型字段后的数字代表显示长度。比如 id
int(11) NOT NULL
2、DATETIME:所有需要精确到时间(时分秒)的字段均使用DATETIME,不要使用TIMESTAMP类型。
对于TIMESTAMP,它把写入的时间从当前时区转化为UTC(世界标准时间)进行存储。查询时,将其又转化为客户端当前时区进行返回。而对于DATETIME,不做任何改变,基本上是原样输入和输出。
另外DATETIME存储的范围也比较大:
timestamp所能存储的时间范围为:‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’。
datetime所能存储的时间范围为:‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。
但是特殊情况,对于跨时区的业务,TIMESTAMP更为合适。
3、VARCHAR:所有动态长度字符串 全部使用VARCHAR类型,类似于状态等有限类别的字段,也使用可以比较明显表示出实际意义的字符串,而不应该使用INT之类的数字来代替;VARCHAR(N),
N表示的是字符数而不是字节数。比如VARCHAR(255),可以最大可存储255个字符(字符包括英文字母,汉字,特殊字符等)。但N应尽可能小,因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节,且存储字符个数由所选字符集决定。
如UTF8存储一个字符最大要3个字节,那么varchar在存放占用3个字节长度的字符时不应超过21845个字符。同时,在进行排序和创建临时表一类的内存操作时,会使用N的长度申请内存。(如无特殊需要,原则上单个varchar型字段不允许超过255个字符)
4、TEXT:仅仅当字符数量可能超过20000个的时候,才可以使用TEXT类型来存放字符类数据,因为所有MySQL数据库都会使用UTF8字符集。
所有使用TEXT类型的字段必须和原表进行分拆,与原表主键单独组成另外一个表进行存放,与大文本字段的隔离,目的是。如无特殊需要,不使用MEDIUMTEXT、TEXT、LONGTEXT类型
5、对于精确浮点型数据存储,需要使用DECIMAL,严禁使用FLOAT和DOUBLE。
6、如无特殊需要,尽量不使用BLOB类型
7、如无特殊需要,字段建议使用NOT NULL属性,可用默认值代替NULL
8、自增字段类型必须是整型且必须为UNSIGNED,推荐类型为INT或BIGINT,并且自增字段必须是主键或者主键的一部分。
小结
阿里巴巴《Java开发手册》之MySQL数据库:
-
任何字段如果为非负数,必须是 UNSIGNED
【强制】小数类型为 DECIMAL,禁止使用 FLOAT 和 DOUBLE。- 说明:在存储的时候,FLOAT 和 DOUBLE 都存在精度损失的问题,很可能在比较值的时候,得
到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围,建议将数据拆成整数和小数并
分开存储。
- 说明:在存储的时候,FLOAT 和 DOUBLE 都存在精度损失的问题,很可能在比较值的时候,得
-
【强制】如果存储的字符串长度几乎相等,使用 CHAR 定长字符串类型
-
【强制】VARCHAR 是可变长字符串,不预先分配存储空间,长度不要超过
5000
。如果存储长度大
于此值,定义字段类型为TEXT
,独立出来一张表,用主键来对应,避免影响其它字段索引效率。