分享3個(gè)MySQL查詢?nèi)菀撞鹊目?/h1>
更新時(shí)間:2022年11月07日 09:03:50 作者:言淦
無論你是技術(shù)大佬,還是剛?cè)胄械男“?時(shí)不時(shí)都會(huì)踩到Mysql數(shù)據(jù)庫(kù)的坑這篇文章主要給大家介紹了關(guān)于3個(gè)MySQL查詢?nèi)菀撞鹊目?文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下
前言
在使用MySQL的過程中,相比大家都踩過不少坑,下面我將列舉日常生活中比較高頻的三個(gè)”坑“,也歡迎大家踴躍分享自己的心路歷程 ??!
1、無 Order by 的隨機(jī)問題
簡(jiǎn)單來說,就是在沒有 Order By 的情況下,如果SELECT的字段不同,返回的記錄順序是隨機(jī)的,不一定一樣。
因?yàn)榉祷氐挠涗涰樞蚋?strong>插入順序、主鍵順序、以及字段上是否建立了索引等因素都有關(guān)聯(lián),可以通過下面的例子進(jìn)行驗(yàn)證:
假設(shè)有這樣的一張用戶點(diǎn)擊表:
create table t_user_click
(
id int(11) auto_increment primary key,
obj varchar(64) default '' not null comment '點(diǎn)擊對(duì)象',
click smallint default 0 not null comment '點(diǎn)擊數(shù)',
remark varchar(64) default '' not null comment '備注',
created_at timestamp default CURRENT_TIMESTAMP not null comment '創(chuàng)建時(shí)間',
updated_at timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新時(shí)間'
)
comment '用戶點(diǎn)擊表';
插入幾條測(cè)試數(shù)據(jù):
INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (1, 'aaa', 10, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');
INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (2, 'bbb', 20, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');
INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (3, 'ccc', 30, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');
INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (4, 'ddd', 40, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');
INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (5, 'eee', 50, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');
INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (6, 'fff', 60, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');
首先來看插入順序帶來的影響,在順序插入幾條順序后,不管查詢多少次,獲取的結(jié)果都與插入順序一致:
# 全部字段
mysql> select * from t_user_click where click > 0;
+----+-----+-------+--------+---------------------+---------------------+
| id | obj | click | remark | created_at | updated_at |
+----+-----+-------+--------+---------------------+---------------------+
| 1 | aaa | 10 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 2 | bbb | 20 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 3 | ccc | 30 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 4 | ddd | 40 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
...
# 與上述獲取全部字段的SQL結(jié)果一致
mysql> select id, click from t_user_click where click > 0;
...
其次來看主鍵順序這個(gè)因素,當(dāng)該表進(jìn)行過 DELETE/REPLACE/UPDATE
操作時(shí),不會(huì)再按照插入順序排序了,而是會(huì)按照主鍵ID進(jìn)行排序。
# 進(jìn)行update操作
UPDATE t_user_click SET id=7 WHERE id=3;
# 此時(shí)按照主鍵排序,obj=ccc 變成最后一條,而不是第3條
mysql> select * from t_user_click where click > 0;
+----+-----+-------+--------+---------------------+---------------------+
| id | obj | click | remark | created_at | updated_at |
+----+-----+-------+--------+---------------------+---------------------+
| 1 | aaa | 10 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 2 | bbb | 20 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 4 | ddd | 40 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 5 | eee | 50 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 6 | fff | 60 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 7 | ccc | 30 | | 2022-10-18 14:44:57 | 2022-10-18 14:46:20 |
+----+-----+-------+--------+---------------------+---------------------+
# 與上述獲取全部字段的SQL結(jié)果一致
mysql> select id, click from t_user_click where click > 0;
...
最后來看有無索引這個(gè)因素,當(dāng)我們?cè)?click
字段上建立索引時(shí),結(jié)果又不一樣了:
# 建索引
create index t_user_click_click_index on t_user_click (click);
# id=7 在最后一條
mysql> select * from t_user_click where click > 0;
+----+-----+-------+--------+---------------------+---------------------+
| id | obj | click | remark | created_at | updated_at |
+----+-----+-------+--------+---------------------+---------------------+
| 1 | aaa | 10 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 2 | bbb | 20 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 4 | ddd | 40 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 5 | eee | 50 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 6 | fff | 60 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 7 | ccc | 30 | | 2022-10-18 14:44:57 | 2022-10-18 14:46:20 |
+----+-----+-------+--------+---------------------+---------------------+
6 rows in set (0.00 sec)
# id=7 在第3條
mysql> select id, click from t_user_click where click > 0;
+----+-------+
| id | click |
+----+-------+
| 1 | 10 |
| 2 | 20 |
| 7 | 30 |
| 4 | 40 |
| 5 | 50 |
| 6 | 60 |
+----+-------+
# 我們可以通過 explain 命令來看兩種查詢方式的不同:
# 兩種方式一個(gè)沒走索引,一個(gè)走了索引,從而導(dǎo)致返回結(jié)果的不同
mysql> explain select * from t_user_click where click > 0;
type:ALL
possible_keys:t_user_click_click_index
key:NULL
Extra:Using where
mysql> explain select id, click from t_user_click where click > 0;
type:index
possible_keys:t_user_click_click_index
key:t_user_click_click_index
Extra:Using where; Using index
2、Order by + Limit 的隨機(jī)問題
簡(jiǎn)單來說就是進(jìn)行**Order by
的字段如果不唯一,則MySQL返回的記錄是隨機(jī)的,常見的表現(xiàn)就是數(shù)據(jù)分頁(yè)后出現(xiàn)重復(fù)**。
不過,這種隨機(jī)也不是隨機(jī)算法那種打亂的隨機(jī),它跟數(shù)據(jù)的插入順序,以及索引的建立也有一定關(guān)系,可以看看下面的例子。
假設(shè)有一張這樣的用戶表:
create table t_user_list
(
id int(11) auto_increment primary key,
name varchar(64) default '' not null comment '名稱',
age smallint default 0 not null comment '年齡',
created_at timestamp default CURRENT_TIMESTAMP not null comment '創(chuàng)建時(shí)間',
updated_at timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新時(shí)間'
)
comment '用戶表';
當(dāng)你按順序插入測(cè)試數(shù)據(jù),然后再去分頁(yè)查詢,你可能會(huì)發(fā)現(xiàn)每次返回的結(jié)果都是固定的,也不會(huì)出現(xiàn)隨機(jī)的情況(這時(shí)候跟插入順序有關(guān))。
# 插入測(cè)試數(shù)據(jù)
INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (1, 'aaa', 1, '2022-10-18 12:55:19', '2022-10-18 12:55:18');
INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (2, 'bbb', 2, '2022-10-18 12:55:18', '2022-10-18 12:55:18');
INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (3, 'ccc', 3, '2022-10-18 12:55:18', '2022-10-18 12:55:18');
INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (4, 'ddd', 4, '2022-10-18 12:55:18', '2022-10-18 12:55:18');
INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (5, 'eee', 5, '2022-10-18 12:55:18', '2022-10-18 12:55:18');
INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (6, 'ggg', 6, '2022-10-18 12:55:19', '2022-10-18 12:55:19');
INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (7, 'iii', 7, '2022-10-18 12:55:17', '2022-10-18 12:55:19');
# 分頁(yè)查詢
mysql> select * from t_user_list order by created_at limit 0, 3;
+----+------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+------+-----+---------------------+---------------------+
| 7 | iii | 7 | 2022-10-18 12:55:17 | 2022-10-18 12:55:19 |
| 2 | bbb | 2 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
| 3 | ccc | 3 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
+----+------+-----+---------------------+---------------------+
3 rows in set (0.00 sec)
mysql> select * from t_user_list order by created_at limit 3, 3;
+----+------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+------+-----+---------------------+---------------------+
| 4 | ddd | 4 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
| 5 | eee | 5 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
| 1 | aaa | 1 | 2022-10-18 12:55:19 | 2022-10-18 12:55:18 |
+----+------+-----+---------------------+---------------------+
3 rows in set (0.00 sec)
而當(dāng)你這時(shí)候新建一個(gè) created_at
索引,并重新插入新的數(shù)據(jù)時(shí),如果你再次查詢,你會(huì)驚奇地發(fā)現(xiàn)數(shù)據(jù)重復(fù)了。
# 新建索引
create index t_user_list_created_at_index on t_user_list (created_at);
# 插入新的測(cè)試數(shù)據(jù)
INSERT INTO db_article.t_user_list (id, name, age, created_at, updated_at) VALUES (8, 'jjj', 8, '2022-10-18 12:55:18', '2022-10-18 12:55:19');
INSERT INTO db_article.t_user_list (id, name, age, created_at, updated_at) VALUES (9, 'kkk', 9, '2022-10-18 12:55:18', '2022-10-18 12:55:19');
INSERT INTO db_article.t_user_list (id, name, age, created_at, updated_at) VALUES (10, 'mmm', 10, '2022-10-18 12:55:18', '2022-10-18 12:55:19');
# 再次分頁(yè)查詢
mysql> select * from t_user_list order by created_at limit 0, 3;
+----+------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+------+-----+---------------------+---------------------+
| 7 | iii | 7 | 2022-10-18 12:55:17 | 2022-10-18 12:55:19 |
| 2 | bbb | 2 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
| 3 | ccc | 3 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
+----+------+-----+---------------------+---------------------+
3 rows in set (0.00 sec)
# id=3 的記錄重復(fù)了
mysql> select * from t_user_list order by created_at limit 3, 3;
+----+------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+------+-----+---------------------+---------------------+
| 3 | ccc | 3 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
| 4 | ddd | 4 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
| 5 | eee | 5 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
+----+------+-----+---------------------+---------------------+
關(guān)于記錄重復(fù)的問題,個(gè)人的猜想是一開始沒有 created_at
索引,此時(shí)磁盤的數(shù)據(jù)塊的順序與插入順序一致,所以返回的結(jié)果一直是固定的;但是加了索引后,并且增加了 created_at
一致的幾條數(shù)據(jù),導(dǎo)致索引重建數(shù)據(jù)塊順序發(fā)生變化,從而記錄重復(fù)(瞎猜的,大家可以一起討論下)
3、聚合函數(shù) + Limit的不準(zhǔn)確問題
簡(jiǎn)單來說,就是在對(duì)數(shù)據(jù)進(jìn)行 Limit分頁(yè)時(shí),同時(shí)使用聚合函數(shù)(比如SUM、COUNT等)對(duì)當(dāng)前分頁(yè)的結(jié)果進(jìn)行聚合,則最終得到的聚合結(jié)果是不準(zhǔn)確的。
還是以第一點(diǎn)的例子和數(shù)據(jù)為例:
mysql> select * from t_user_click;
+----+-----+-------+--------+---------------------+---------------------+
| id | obj | click | remark | created_at | updated_at |
+----+-----+-------+--------+---------------------+---------------------+
| 1 | aaa | 10 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 2 | bbb | 20 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 4 | ddd | 40 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 5 | eee | 50 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 6 | fff | 60 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
| 7 | ccc | 30 | | 2022-10-18 14:44:57 | 2022-10-18 14:46:20 |
+----+-----+-------+--------+---------------------+---------------------+
# 沒分頁(yè)的總數(shù)
mysql> select sum(click) from t_user_click;
+------------+
| sum(click) |
+------------+
| 210 |
+------------+
1 row in set (0.00 sec)
# 分頁(yè)后的總數(shù)
mysql> select sum(click) from t_user_click limit 3;
+------------+
| sum(click) |
+------------+
| 210 |
+------------+
# 可以通過子查詢解決
select sum(tmp.click) from (select click from t_user_click limit 3) as tmp;
可以看到,在有Limit的情況下,得到的聚合結(jié)果其實(shí)是所有記錄的總和,并不是三條記錄的總和。原因就是SELECT語句執(zhí)行時(shí)有一定順序,分別是 FROM、ON、JOIN、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、ORDER BY和LIMIT。
在上述SQL中,SELECT優(yōu)先執(zhí)行,即先執(zhí)行 select sum(click) from t_user_click
,這時(shí)已經(jīng)得到結(jié)果為210,最終再執(zhí)行 limit 3
剔除不符合要求的記錄。
總結(jié)
最后小結(jié)一下,本文主要介紹了MySQL查詢中三個(gè)比較容易踩的坑,從中我們可以得到:
1、SELECT返回的順序跟多種因素有關(guān),如插入順序、主鍵ID順序、索引順序等,如果你返回的結(jié)果要求有順序,則記得加上 order by
。
2、加上了 order by
也不要高興地太早,還需要檢查 order by
的字段是否唯一,如果不唯一,返回的結(jié)果也有可能是隨機(jī)的。
3、在進(jìn)行聚合查詢時(shí),切記看有無 LIMIT 子句,有的話記得加上子查詢避免查詢的結(jié)果不準(zhǔn)確。
到此這篇關(guān)于3個(gè)MySQL查詢?nèi)菀撞瓤拥奈恼戮徒榻B到這了,更多相關(guān)MySQL查詢易踩的坑內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
-
MySQL動(dòng)態(tài)SQL拼接實(shí)例詳解
動(dòng)態(tài)SQL呢?首先是SQL語句,是根據(jù)條件來拼接SQL,下面這篇文章主要給大家介紹了關(guān)于MySQL動(dòng)態(tài)SQL拼接的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下 2022-12-12
-
mysql間隙鎖加鎖11個(gè)規(guī)則(案例分析)
這篇文章主要介紹了mysql間隙鎖加鎖11個(gè)規(guī)則?,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下 2023-03-03
-
Mysql計(jì)算字段長(zhǎng)度函數(shù)之CHAR_LENGTH函數(shù)
mysql中計(jì)算字符串長(zhǎng)度有兩個(gè)函數(shù)分別為char_length和length,char_length函數(shù)可以計(jì)算unicode字符,下面這篇文章主要給大家介紹了關(guān)于Mysql計(jì)算字段長(zhǎng)度函數(shù)之CHAR_LENGTH函數(shù)的相關(guān)資料,需要的朋友可以參考下 2023-05-05
-
MySQL 8.0.18 Hash Join不支持left/right join左右連接問題
在MySQL 8.0.18中,增加了Hash Join新功能,它適用于未創(chuàng)建索引的字段,做等值關(guān)聯(lián)查詢。這篇文章給大家介紹MySQL 8.0.18 Hash Join不支持left/right join左右連接,感興趣的朋友一起看看吧 2019-11-11
-
詳解MySQL日期 字符串 時(shí)間戳互轉(zhuǎn)
本篇文章主要介紹了詳解MySQL日期 字符串 時(shí)間戳互轉(zhuǎn),詳解date轉(zhuǎn)字符串、date轉(zhuǎn)時(shí)間戳、字符串轉(zhuǎn)date、字符串轉(zhuǎn)時(shí)間戳、時(shí)間戳轉(zhuǎn)date,時(shí)間戳轉(zhuǎn)字符串,有興趣的可以了解一下。
2017-01-01
最新評(píng)論
前言
在使用MySQL的過程中,相比大家都踩過不少坑,下面我將列舉日常生活中比較高頻的三個(gè)”坑“,也歡迎大家踴躍分享自己的心路歷程 ??!
1、無 Order by 的隨機(jī)問題
簡(jiǎn)單來說,就是在沒有 Order By 的情況下,如果SELECT的字段不同,返回的記錄順序是隨機(jī)的,不一定一樣。
因?yàn)榉祷氐挠涗涰樞蚋?strong>插入順序、主鍵順序、以及字段上是否建立了索引等因素都有關(guān)聯(lián),可以通過下面的例子進(jìn)行驗(yàn)證:
假設(shè)有這樣的一張用戶點(diǎn)擊表:
create table t_user_click ( id int(11) auto_increment primary key, obj varchar(64) default '' not null comment '點(diǎn)擊對(duì)象', click smallint default 0 not null comment '點(diǎn)擊數(shù)', remark varchar(64) default '' not null comment '備注', created_at timestamp default CURRENT_TIMESTAMP not null comment '創(chuàng)建時(shí)間', updated_at timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新時(shí)間' ) comment '用戶點(diǎn)擊表';
插入幾條測(cè)試數(shù)據(jù):
INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (1, 'aaa', 10, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57'); INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (2, 'bbb', 20, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57'); INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (3, 'ccc', 30, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57'); INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (4, 'ddd', 40, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57'); INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (5, 'eee', 50, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57'); INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (6, 'fff', 60, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');
首先來看插入順序帶來的影響,在順序插入幾條順序后,不管查詢多少次,獲取的結(jié)果都與插入順序一致:
# 全部字段 mysql> select * from t_user_click where click > 0; +----+-----+-------+--------+---------------------+---------------------+ | id | obj | click | remark | created_at | updated_at | +----+-----+-------+--------+---------------------+---------------------+ | 1 | aaa | 10 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 2 | bbb | 20 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 3 | ccc | 30 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 4 | ddd | 40 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | ... # 與上述獲取全部字段的SQL結(jié)果一致 mysql> select id, click from t_user_click where click > 0; ...
其次來看主鍵順序這個(gè)因素,當(dāng)該表進(jìn)行過 DELETE/REPLACE/UPDATE
操作時(shí),不會(huì)再按照插入順序排序了,而是會(huì)按照主鍵ID進(jìn)行排序。
# 進(jìn)行update操作 UPDATE t_user_click SET id=7 WHERE id=3; # 此時(shí)按照主鍵排序,obj=ccc 變成最后一條,而不是第3條 mysql> select * from t_user_click where click > 0; +----+-----+-------+--------+---------------------+---------------------+ | id | obj | click | remark | created_at | updated_at | +----+-----+-------+--------+---------------------+---------------------+ | 1 | aaa | 10 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 2 | bbb | 20 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 4 | ddd | 40 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 5 | eee | 50 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 6 | fff | 60 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 7 | ccc | 30 | | 2022-10-18 14:44:57 | 2022-10-18 14:46:20 | +----+-----+-------+--------+---------------------+---------------------+ # 與上述獲取全部字段的SQL結(jié)果一致 mysql> select id, click from t_user_click where click > 0; ...
最后來看有無索引這個(gè)因素,當(dāng)我們?cè)?click
字段上建立索引時(shí),結(jié)果又不一樣了:
# 建索引 create index t_user_click_click_index on t_user_click (click); # id=7 在最后一條 mysql> select * from t_user_click where click > 0; +----+-----+-------+--------+---------------------+---------------------+ | id | obj | click | remark | created_at | updated_at | +----+-----+-------+--------+---------------------+---------------------+ | 1 | aaa | 10 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 2 | bbb | 20 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 4 | ddd | 40 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 5 | eee | 50 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 6 | fff | 60 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 7 | ccc | 30 | | 2022-10-18 14:44:57 | 2022-10-18 14:46:20 | +----+-----+-------+--------+---------------------+---------------------+ 6 rows in set (0.00 sec) # id=7 在第3條 mysql> select id, click from t_user_click where click > 0; +----+-------+ | id | click | +----+-------+ | 1 | 10 | | 2 | 20 | | 7 | 30 | | 4 | 40 | | 5 | 50 | | 6 | 60 | +----+-------+ # 我們可以通過 explain 命令來看兩種查詢方式的不同: # 兩種方式一個(gè)沒走索引,一個(gè)走了索引,從而導(dǎo)致返回結(jié)果的不同 mysql> explain select * from t_user_click where click > 0; type:ALL possible_keys:t_user_click_click_index key:NULL Extra:Using where mysql> explain select id, click from t_user_click where click > 0; type:index possible_keys:t_user_click_click_index key:t_user_click_click_index Extra:Using where; Using index
2、Order by + Limit 的隨機(jī)問題
簡(jiǎn)單來說就是進(jìn)行**Order by
的字段如果不唯一,則MySQL返回的記錄是隨機(jī)的,常見的表現(xiàn)就是數(shù)據(jù)分頁(yè)后出現(xiàn)重復(fù)**。
不過,這種隨機(jī)也不是隨機(jī)算法那種打亂的隨機(jī),它跟數(shù)據(jù)的插入順序,以及索引的建立也有一定關(guān)系,可以看看下面的例子。
假設(shè)有一張這樣的用戶表:
create table t_user_list ( id int(11) auto_increment primary key, name varchar(64) default '' not null comment '名稱', age smallint default 0 not null comment '年齡', created_at timestamp default CURRENT_TIMESTAMP not null comment '創(chuàng)建時(shí)間', updated_at timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新時(shí)間' ) comment '用戶表';
當(dāng)你按順序插入測(cè)試數(shù)據(jù),然后再去分頁(yè)查詢,你可能會(huì)發(fā)現(xiàn)每次返回的結(jié)果都是固定的,也不會(huì)出現(xiàn)隨機(jī)的情況(這時(shí)候跟插入順序有關(guān))。
# 插入測(cè)試數(shù)據(jù) INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (1, 'aaa', 1, '2022-10-18 12:55:19', '2022-10-18 12:55:18'); INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (2, 'bbb', 2, '2022-10-18 12:55:18', '2022-10-18 12:55:18'); INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (3, 'ccc', 3, '2022-10-18 12:55:18', '2022-10-18 12:55:18'); INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (4, 'ddd', 4, '2022-10-18 12:55:18', '2022-10-18 12:55:18'); INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (5, 'eee', 5, '2022-10-18 12:55:18', '2022-10-18 12:55:18'); INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (6, 'ggg', 6, '2022-10-18 12:55:19', '2022-10-18 12:55:19'); INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (7, 'iii', 7, '2022-10-18 12:55:17', '2022-10-18 12:55:19'); # 分頁(yè)查詢 mysql> select * from t_user_list order by created_at limit 0, 3; +----+------+-----+---------------------+---------------------+ | id | name | age | created_at | updated_at | +----+------+-----+---------------------+---------------------+ | 7 | iii | 7 | 2022-10-18 12:55:17 | 2022-10-18 12:55:19 | | 2 | bbb | 2 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | | 3 | ccc | 3 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | +----+------+-----+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> select * from t_user_list order by created_at limit 3, 3; +----+------+-----+---------------------+---------------------+ | id | name | age | created_at | updated_at | +----+------+-----+---------------------+---------------------+ | 4 | ddd | 4 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | | 5 | eee | 5 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | | 1 | aaa | 1 | 2022-10-18 12:55:19 | 2022-10-18 12:55:18 | +----+------+-----+---------------------+---------------------+ 3 rows in set (0.00 sec)
而當(dāng)你這時(shí)候新建一個(gè) created_at
索引,并重新插入新的數(shù)據(jù)時(shí),如果你再次查詢,你會(huì)驚奇地發(fā)現(xiàn)數(shù)據(jù)重復(fù)了。
# 新建索引 create index t_user_list_created_at_index on t_user_list (created_at); # 插入新的測(cè)試數(shù)據(jù) INSERT INTO db_article.t_user_list (id, name, age, created_at, updated_at) VALUES (8, 'jjj', 8, '2022-10-18 12:55:18', '2022-10-18 12:55:19'); INSERT INTO db_article.t_user_list (id, name, age, created_at, updated_at) VALUES (9, 'kkk', 9, '2022-10-18 12:55:18', '2022-10-18 12:55:19'); INSERT INTO db_article.t_user_list (id, name, age, created_at, updated_at) VALUES (10, 'mmm', 10, '2022-10-18 12:55:18', '2022-10-18 12:55:19'); # 再次分頁(yè)查詢 mysql> select * from t_user_list order by created_at limit 0, 3; +----+------+-----+---------------------+---------------------+ | id | name | age | created_at | updated_at | +----+------+-----+---------------------+---------------------+ | 7 | iii | 7 | 2022-10-18 12:55:17 | 2022-10-18 12:55:19 | | 2 | bbb | 2 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | | 3 | ccc | 3 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | +----+------+-----+---------------------+---------------------+ 3 rows in set (0.00 sec) # id=3 的記錄重復(fù)了 mysql> select * from t_user_list order by created_at limit 3, 3; +----+------+-----+---------------------+---------------------+ | id | name | age | created_at | updated_at | +----+------+-----+---------------------+---------------------+ | 3 | ccc | 3 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | | 4 | ddd | 4 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | | 5 | eee | 5 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | +----+------+-----+---------------------+---------------------+
關(guān)于記錄重復(fù)的問題,個(gè)人的猜想是一開始沒有 created_at
索引,此時(shí)磁盤的數(shù)據(jù)塊的順序與插入順序一致,所以返回的結(jié)果一直是固定的;但是加了索引后,并且增加了 created_at
一致的幾條數(shù)據(jù),導(dǎo)致索引重建數(shù)據(jù)塊順序發(fā)生變化,從而記錄重復(fù)(瞎猜的,大家可以一起討論下)
3、聚合函數(shù) + Limit的不準(zhǔn)確問題
簡(jiǎn)單來說,就是在對(duì)數(shù)據(jù)進(jìn)行 Limit分頁(yè)時(shí),同時(shí)使用聚合函數(shù)(比如SUM、COUNT等)對(duì)當(dāng)前分頁(yè)的結(jié)果進(jìn)行聚合,則最終得到的聚合結(jié)果是不準(zhǔn)確的。
還是以第一點(diǎn)的例子和數(shù)據(jù)為例:
mysql> select * from t_user_click; +----+-----+-------+--------+---------------------+---------------------+ | id | obj | click | remark | created_at | updated_at | +----+-----+-------+--------+---------------------+---------------------+ | 1 | aaa | 10 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 2 | bbb | 20 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 4 | ddd | 40 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 5 | eee | 50 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 6 | fff | 60 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 7 | ccc | 30 | | 2022-10-18 14:44:57 | 2022-10-18 14:46:20 | +----+-----+-------+--------+---------------------+---------------------+ # 沒分頁(yè)的總數(shù) mysql> select sum(click) from t_user_click; +------------+ | sum(click) | +------------+ | 210 | +------------+ 1 row in set (0.00 sec) # 分頁(yè)后的總數(shù) mysql> select sum(click) from t_user_click limit 3; +------------+ | sum(click) | +------------+ | 210 | +------------+ # 可以通過子查詢解決 select sum(tmp.click) from (select click from t_user_click limit 3) as tmp;
可以看到,在有Limit的情況下,得到的聚合結(jié)果其實(shí)是所有記錄的總和,并不是三條記錄的總和。原因就是SELECT語句執(zhí)行時(shí)有一定順序,分別是 FROM、ON、JOIN、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、ORDER BY和LIMIT。
在上述SQL中,SELECT優(yōu)先執(zhí)行,即先執(zhí)行 select sum(click) from t_user_click
,這時(shí)已經(jīng)得到結(jié)果為210,最終再執(zhí)行 limit 3
剔除不符合要求的記錄。
總結(jié)
最后小結(jié)一下,本文主要介紹了MySQL查詢中三個(gè)比較容易踩的坑,從中我們可以得到:
1、SELECT返回的順序跟多種因素有關(guān),如插入順序、主鍵ID順序、索引順序等,如果你返回的結(jié)果要求有順序,則記得加上 order by
。
2、加上了 order by
也不要高興地太早,還需要檢查 order by
的字段是否唯一,如果不唯一,返回的結(jié)果也有可能是隨機(jī)的。
3、在進(jìn)行聚合查詢時(shí),切記看有無 LIMIT 子句,有的話記得加上子查詢避免查詢的結(jié)果不準(zhǔn)確。
到此這篇關(guān)于3個(gè)MySQL查詢?nèi)菀撞瓤拥奈恼戮徒榻B到這了,更多相關(guān)MySQL查詢易踩的坑內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL動(dòng)態(tài)SQL拼接實(shí)例詳解
動(dòng)態(tài)SQL呢?首先是SQL語句,是根據(jù)條件來拼接SQL,下面這篇文章主要給大家介紹了關(guān)于MySQL動(dòng)態(tài)SQL拼接的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-12-12mysql間隙鎖加鎖11個(gè)規(guī)則(案例分析)
這篇文章主要介紹了mysql間隙鎖加鎖11個(gè)規(guī)則?,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2023-03-03Mysql計(jì)算字段長(zhǎng)度函數(shù)之CHAR_LENGTH函數(shù)
mysql中計(jì)算字符串長(zhǎng)度有兩個(gè)函數(shù)分別為char_length和length,char_length函數(shù)可以計(jì)算unicode字符,下面這篇文章主要給大家介紹了關(guān)于Mysql計(jì)算字段長(zhǎng)度函數(shù)之CHAR_LENGTH函數(shù)的相關(guān)資料,需要的朋友可以參考下2023-05-05MySQL 8.0.18 Hash Join不支持left/right join左右連接問題
在MySQL 8.0.18中,增加了Hash Join新功能,它適用于未創(chuàng)建索引的字段,做等值關(guān)聯(lián)查詢。這篇文章給大家介紹MySQL 8.0.18 Hash Join不支持left/right join左右連接,感興趣的朋友一起看看吧2019-11-11詳解MySQL日期 字符串 時(shí)間戳互轉(zhuǎn)
本篇文章主要介紹了詳解MySQL日期 字符串 時(shí)間戳互轉(zhuǎn),詳解date轉(zhuǎn)字符串、date轉(zhuǎn)時(shí)間戳、字符串轉(zhuǎn)date、字符串轉(zhuǎn)時(shí)間戳、時(shí)間戳轉(zhuǎn)date,時(shí)間戳轉(zhuǎn)字符串,有興趣的可以了解一下。2017-01-01