很多時(shí)候我們業(yè)務(wù)系統實(shí)現分頁(yè)功能可能會(huì )用如下sql實(shí)現：
select * from employees limit 10000,10;
表示從表 employees 中取出從 10001 行開(kāi)始的 10 行記錄?？此浦徊樵?xún)了 10 條記錄，實(shí)際這條 SQL 是先讀取 10010條記錄，然后拋棄前 10000 條記錄，然后讀到后面 10 條想要的數據。因此要查詢(xún)一張大表比較靠后的數據，執行效率是非常低的。這是典型的深度分頁(yè)問(wèn)題。
首先來(lái)看一個(gè)根據自增且連續主鍵排序的分頁(yè)查詢(xún)的例子：
select * from employees limit 90000,5;
該 SQL 表示查詢(xún)從第 90001開(kāi)始的五行數據，沒(méi)添加單獨 order by，表示通過(guò)主鍵排序。我們再看表 employees ，因為主鍵是自增并且連續的，所以可以改寫(xiě)成按照主鍵去查詢(xún)從第 90001開(kāi)始的五行數據，如下：
select * from employees where id > 90000 limit 5;
查詢(xún)的結果是一致的。我們再對比一下執行計劃：
EXPLAIN select * from employees limit 90000,5;
EXPLAIN select * from employees where id > 90000 limit 5;
顯然改寫(xiě)后的 SQL 走了索引，而且掃描的行數大大減少，執行效率更高。
但是，這條改寫(xiě)的SQL 在很多場(chǎng)景并不實(shí)用，因為表中可能某些記錄被刪后，主鍵空缺，導致結果不一致，如下圖試驗所示（先刪除一條前面的記錄，然后再測試原 SQL 和優(yōu)化后的 SQL）：
兩條 SQL 的結果并不一樣，因此，如果主鍵不連續，不能使用上面描述的優(yōu)化方法。
另外如果原 SQL 是 order by 非主鍵的字段，按照上面說(shuō)的方法改寫(xiě)會(huì )導致兩條 SQL 的結果不一致。所以這種改寫(xiě)得滿(mǎn)足以下兩個(gè)條件：
再看一個(gè)根據非主鍵字段排序的分頁(yè)查詢(xún)，SQL 如下：
select * from employees ORDER BY name limit 90000,5;
EXPLAIN select * from employees ORDER BY name limit 90000,5;
發(fā)現并沒(méi)有使用 name 字段的索引（key 字段對應的值為 null），具體原因是：掃描整個(gè)索引并查找到?jīng)]索引的行(可能要遍歷多個(gè)索引樹(shù))的成本比掃描全表的成本更高，所以?xún)?yōu)化器放棄使用索引。
知道不走索引的原因，那么怎么優(yōu)化呢？
其實(shí)關(guān)鍵是讓排序時(shí)返回的字段盡可能少，即考慮使用覆蓋索引進(jìn)行優(yōu)化，所以可以讓排序和分頁(yè)操作先查出主鍵，然后根據主鍵查到對應的記錄，SQL改寫(xiě)如下:
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
需要的結果與原 SQL 一致，執行時(shí)間減少了一半以上，此時(shí)查詢(xún)和排序都是在覆蓋索引樹(shù)上進(jìn)行的，所以效率較高。我們再對比優(yōu)化前后sql的執行計劃：
原 SQL 使用的是 file sort 排序，而優(yōu)化后的 SQL 使用的是索引排序。
基于 Spring Boot + MyBatis Plus + Vue & Element 實(shí)現的后臺管理系統 + 用戶(hù)小程序，支持 RBAC 動(dòng)態(tài)權限、多租戶(hù)、數據權限、工作流、三方登錄、支付、短信、商城等功能。
項目地址：https://github.com/YunaiV/ruoyi-vue-pro
示例表：
‐‐ 示例表：
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table t2 like t1;
‐‐ 插入一些示例數據
‐‐ 往t1表插入1萬(wàn)行記錄
drop procedure if exists insert_t1;
delimiter ;;
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into t1(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t1();
‐‐ 往t2表插入100行記錄
drop procedure if exists insert_t2;
delimiter ;;
create procedure insert_t2()
begin
declare i int;
set i=1;
while(i<=100)do
insert into t2(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t2();
mysql的表關(guān)聯(lián)常見(jiàn)有兩種算法
一次一行循環(huán)地從第一張表（稱(chēng)為驅動(dòng)表）中讀取行，在這行數據中取到關(guān)聯(lián)字段，根據關(guān)聯(lián)字段在另一張表（被驅動(dòng)表）里取出滿(mǎn)足條件的行，然后取出兩張表的結果合集。
EXPLAIN select * from t1 inner join t2 on t1.a= t2.a; -- // a字段有索引
從執行計劃中可以看到這些信息：
inner join 時(shí)，排在前面的表并不一定就是驅動(dòng)表。right join時(shí)，右表是驅動(dòng)表，左表是被驅動(dòng)表，當使用join時(shí)，mysql會(huì )選擇數據量比較小的表作為驅動(dòng)表，大表作為被驅動(dòng)表。Using join buffer 則表示使用的 join 算法是 NLJ。上面sql的大致流程如下：
整個(gè)過(guò)程會(huì )讀取 t2 表的所有數據(掃描100行)，然后遍歷這每行數據中字段 a 的值，根據 t2 表中 a 的值索引掃描 t1 表中的對應行(掃描100次 t1 表的索引，1次掃描可以認為最終只掃描 t1 表一行完整數據，也就是總共 t1 表也掃描了100行)。因此整個(gè)過(guò)程掃描了 200 行。
把驅動(dòng)表的數據讀入到 join_buffer 中，然后掃描被驅動(dòng)表，把被驅動(dòng)表每一行取出來(lái)跟 join_buffer 中的數據做對比。
EXPLAIN select * from t1 inner join t2 on t1.b= t2.b; -- // b字段沒(méi)有索引
Extra 中 的Using join buffer (Block Nested Loop)說(shuō)明該關(guān)聯(lián)查詢(xún)使用的是 BNL 算法。
上面sql的大致流程如下：
join_buffer 中join_buffer 中的數據做對比整個(gè)過(guò)程對表 t1 和 t2 都做了一次全表掃描，因此掃描的總行數為10000(表 t1 的數據總量) + 100(表 t2 的數據總量) =10100。并且 join_buffer 里的數據是無(wú)序的，因此對表 t1 中的每一行，都要做 100 次判斷，所以?xún)却嬷械呐袛啻螖凳?code>100 * 10000= 100 萬(wàn)次。
這個(gè)例子里表 t2 才 100 行，要是表 t2 是一個(gè)大表，join_buffer 放不下怎么辦呢？
join_buffer 的大小是由參數 join_buffer_size 設定的，默認值是 256k。如果放不下表 t2 的所有數據話(huà)，策略很簡(jiǎn)單，就是分段放。
比如 t2 表有1000行記錄， join_buffer 一次只能放800行數據，那么執行過(guò)程就是先往 join_buffer 里放800行記錄，然后從 t1 表里取數據跟 join_buffer 中數據對比得到部分結果，然后清空 join_buffer ，再放入 t2 表剩余200行記錄，再次從 t1 表里取數據跟 join_buffer 中數據對比。所以就多掃了一次 t1 表。
被驅動(dòng)表的關(guān)聯(lián)字段沒(méi)索引為什么要選擇使用 BNL 算法而不使用 Nested-Loop Join 呢？
如果上面第二條sql使用 Nested-Loop Join，那么掃描行數為 100 * 10000 = 100萬(wàn)次，這個(gè)是磁盤(pán)掃描。
很顯然，用BNL磁盤(pán)掃描次數少很多，相比于磁盤(pán)掃描，BNL的內存計算會(huì )快得多。因此MySQL對于被驅動(dòng)表的關(guān)聯(lián)字段沒(méi)索引的關(guān)聯(lián)查詢(xún)，一般都會(huì )使用 BNL 算法。如果有索引一般選擇 NLJ 算法，有索引的情況下 NLJ 算法比 BNL算法性能更高.
straight_join寫(xiě)法固定連接驅動(dòng)方式，省去mysql優(yōu)化器自己判斷的時(shí)間straight_join解釋?zhuān)?code>straight_join功能同join類(lèi)似，但能讓左邊的表來(lái)驅動(dòng)右邊的表，能改表優(yōu)化器對于聯(lián)表查詢(xún)的執行順序。
比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql選著(zhù) t2 表作為驅動(dòng)表。
straight_join只適用于inner join，并不適用于left join，right join。（因為left join，right join已經(jīng)代表指定了表的執行順序）straight_join一定要慎重，因為部分情況下人為指定的執行順序并不一定會(huì )比優(yōu)化引擎要靠譜。在決定哪個(gè)表做驅動(dòng)表的時(shí)候，應該是兩個(gè)表按照各自的條件過(guò)濾，過(guò)濾完成之后，計算參與 join 的各個(gè)字段的總數據量，數據量小的那個(gè)表，就是“小表”，應該作為驅動(dòng)表。
基于微服務(wù)的思想，構建在 B2C 電商場(chǎng)景下的項目實(shí)戰。核心技術(shù)棧，是 Spring Boot + Dubbo 。未來(lái)，會(huì )重構成 Spring Cloud Alibaba 。
項目地址：https://github.com/YunaiV/onemall
原則：小表驅動(dòng)大表，即小的數據集驅動(dòng)大的數據集。
In是In后的表先執行（適用于B表小于A(yíng)表）：
select * from A where id in ( select id from B)
Exists是Exists前面的表先執行（適用于A(yíng)表小于B表）:
select * from A where id in ( select id from B)
select * from A where id in (select id from B)
// #等價(jià)于：
for(select id from B){
select * from A where A.id = B.id
}
將主查詢(xún)A的數據，放到子查詢(xún)B中做條件驗證，根據驗證結果（true或false）來(lái)決定主查詢(xún)的數據是否保留.
select * from A where exists (select 1 from B where B.id = A.id)
// # 等價(jià)于:
for(select * from A){
select * from B where B.id = A.id
}
// # A表與B表的ID字段應建立索引
1、EXISTS (subquery)只返回TRUE或FALSE,因此子查詢(xún)中的SELECT * 也可以用SELECT 1替換,官方說(shuō)法是實(shí)際執行時(shí)會(huì )忽略SELECT清單,因此沒(méi)有區別
2、EXISTS子查詢(xún)的實(shí)際執行過(guò)程可能經(jīng)過(guò)了優(yōu)化而不是我們理解上的逐條對比
3、EXISTS子查詢(xún)往往也可以用JOIN來(lái)代替，何種最優(yōu)需要具體問(wèn)題具體分析
-- 臨時(shí)關(guān)閉mysql查詢(xún)緩存，為了查看sql多次執行的真實(shí)時(shí)間
set global query_cache_size=0;
set global query_cache_type=0;
EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;
-- 注意：以上4條sql只有根據某個(gè)字段count不會(huì )統計字段
經(jīng)過(guò)測試發(fā)現：四個(gè)sql的執行計劃一樣，說(shuō)明這四個(gè)sql執行效率應該差不多
1、字段有索引： count(*)≈count(1)>count(字段)>count(主鍵 id)
字段有索引，count(字段)統計走二級索引，二級索引存儲數據比主鍵索引少，所以count(字段)>count(主鍵 id)
2、字段無(wú)索引: count(*)≈count(1)>count(主鍵 id)>count(字段)
字段沒(méi)有索引count(字段)統計走不了索引，count(主鍵 id)還可以走主鍵索引，所以count(主鍵 id)>count(字段)
count(1) 跟 count(字段) 執行過(guò)程類(lèi)似，不過(guò)count(1)不需要取出字段統計，就用常量1做統計，count(字段)還需要取出字段，所以理論上count(1)比count(字段)會(huì )快一點(diǎn)。
count(*) 是例外，mysql并不會(huì )把全部字段取出來(lái)，而是專(zhuān)門(mén)做了優(yōu)化(5.7版本)，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)來(lái)替代 count(*)。
為什么對于count(id)，mysql最終選擇輔助索引而不是主鍵聚集索引？因為二級索引相對主鍵索引存儲數據更少，檢索性能應該更高，mysql內部做了點(diǎn)優(yōu)化(應該是在5.7版本才優(yōu)化)。
當表中數據量非常大的時(shí)候，count這種通過(guò)計算統計的都會(huì )很慢，所以需要一些優(yōu)化手段。
對于myisam存儲引擎的表做不帶where條件的count查詢(xún)性能是很高的，因為myisam存儲引擎的表的總行數會(huì )被mysql存儲在磁盤(pán)上，查詢(xún)不需要計算.
對于innodb存儲引擎的表mysql不會(huì )存儲表的總記錄行數(因為有MVCC機制，后面會(huì )講)，查詢(xún)count需要實(shí)時(shí)計算.
如果只需要知道表總行數的估計值可以用如下sql查詢(xún)，性能很高
插入或刪除表數據行的時(shí)候同時(shí)維護redis里的表總行數key的計數值(用incr或decr命令)，但是這種方式可能不準，很難保證表操作和redis操作的事務(wù)一致性.
插入或刪除表數據行的時(shí)候同時(shí)維護計數表，讓他們在同一個(gè)事務(wù)里操作
is_xxx的方式命名，數據類(lèi)型是unsigned tinyint（1表示是，0表示否）。說(shuō)明：任何字段如果為非負數，必須是unsigned。注意：POJO類(lèi)中的任何布爾類(lèi)型的變量，都不要加is前綴，所以，需要在設置從is_xxx到Xxx的映射關(guān)系。數據庫表示是與否的值，使用tinyint類(lèi)型，堅持is_xxx的命名方式是為了明確其取值含義與取值范圍。正例：表達邏輯刪除的字段名is_deleted，1表示刪除，0表示未刪除。aliyun_admin，rdc_config，level3_name 反例：AliyunAdmin，rdcConfig，level_3_namepk_ 即primary key；uk_ 即 unique key；idx_ 即index的簡(jiǎn)稱(chēng)。gmt_create, gmt_modified。說(shuō)明：其中id必為主鍵，類(lèi)型為bigint unsigned、單表時(shí)自增、步長(cháng)為1。gmt_create, gmt_modified的類(lèi)型均為datetime類(lèi)型，前者現在時(shí)表示主動(dòng)式創(chuàng )建，后者過(guò)去分詞表示被動(dòng)式更新。alipay_task / force_project / trade_configdistinct left(列名, 索引長(cháng)度))/count(*)的區分度來(lái)確定。order by的場(chǎng)景，請注意利用索引的有序性。order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現file_sort的情況，影響查詢(xún)性能。正例：where a=? and b=? order by c; 索引：a_b_c 反例：索引如果存在范圍查詢(xún)，那么索引有序性無(wú)法利用，如：WHERE a>10 ORDER BY b; 索引a_b無(wú)法排序。using index。SELECT a.* FROM 表1 a, (select id from 表1 where 條件 LIMIT 100000,20 ) b where a.id=b.idtype=index，索引物理文件全掃描，速度非常慢，這個(gè)index級別比較range還低，與全表掃描是小巫見(jiàn)大巫。where a=? and b=?，a列的幾乎接近于唯一值，那么只需要單建idx_a索引即可。說(shuō)明：存在非等號和等號混合判斷條件時(shí)，在建索引時(shí)，請把等號條件的列前置。如：where c>? and d=? 那么即使c的區分度更高，也必須把d放在索引的最前列，即建立組合索引idx_d_c。count(列名)或count(常量)來(lái)替代count()，count()是SQL92定義的標準統計行數的語(yǔ)法，跟數據庫無(wú)關(guān)，跟NULL和非NULL無(wú)關(guān)。說(shuō)明：count(*)會(huì )統計值為NULL的行，而count(列名)不會(huì )統計此列為NULL值的行。count(distinct col) 計算該列除NULL之外的不重復行數，注意 count(distinct col1, col2) 如果其中一列全為NULL，那么即使另一列有不同的值，也返回為0。count(col)的返回結果為0，但sum(col)的返回結果為NULL，因此使用sum()時(shí)需注意NPE問(wèn)題。正例：可以使用如下方式來(lái)避免sum的NPE問(wèn)題：SELECT IFNULL(SUM(column), 0) FROM table;NULL<>NULL的返回結果是NULL，而不是false。2） NULL=NULL的返回結果是NULL，而不是true。3） NULL<>1的返回結果是NULL，而不是true。反例：在SQL語(yǔ)句中，如果在null前換行，影響可讀性。select * from table where column1 is null and column3 is not null; 而ISNULL(column)是一個(gè)整體，簡(jiǎn)潔易懂。從性能數據上分析，ISNULL(column)執行效率更快一些。student_id是主鍵，那么成績(jì)表中的student_id則為外鍵。如果更新學(xué)生表中的student_id，同時(shí)觸發(fā)成績(jì)表中的student_id更新，即為級聯(lián)更新。外鍵與級聯(lián)更新適用于單機低并發(fā)，不適合分布式、高并發(fā)集群；級聯(lián)更新是強阻塞，存在數據庫更新風(fēng)暴的風(fēng)險；外鍵影響數據庫的插入速度。select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id; 反例：在某業(yè)務(wù)中，由于多表關(guān)聯(lián)查詢(xún)語(yǔ)句沒(méi)有加表的別名（或表名）的限制，正常運行兩年后，最近在某個(gè)表中增加一個(gè)同名字段，在預發(fā)布環(huán)境做數據庫變更后，線(xiàn)上查詢(xún)語(yǔ)句出現出1052異常：Column 'name’ in field list is ambiguous。select t1.name from table_first as t1, table_second as t2 where t1.id=t2.id;SELECT LENGTH(“輕松工作”)； 返回為12 SELECT CHARACTER_LENGTH(“輕松工作”)； 返回為4 如果需要存儲表情，那么選擇utf8mb4來(lái)進(jìn)行存儲，注意它與utf8編碼的區別。TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務(wù)日志資源少，但TRUNCATE無(wú)事務(wù)且不觸發(fā)trigger，有可能造成事故，故不建議在開(kāi)發(fā)代碼中使用此語(yǔ)句。說(shuō)明：TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語(yǔ)句相同。* 作為查詢(xún)的字段列表，需要哪些字段必須明確寫(xiě)明。說(shuō)明：1）增加查詢(xún)分析器解析成本。2）增減字段容易與resultMap配置不一致。3）無(wú)用字段增加網(wǎng)絡(luò )消耗，尤其是text類(lèi)型的字段。is_，要求在resultMap中進(jìn)行字段與屬性之間的映射。說(shuō)明：參見(jiàn)定義POJO類(lèi)以及數據庫字段定義規定，在sql.xml增加映射，是必須的。#{}，#param# 不要使用${} 此種方式容易出現SQL注入。queryForList(String statementName,int start,int size)不推薦使用。說(shuō)明：其實(shí)現方式是在數據庫取到statementName對應的SQL語(yǔ)句的所有記錄，再通過(guò)subList取start,size的子集合。正例：
Map<String, Object> map = new HashMap<>();
map.put(“start”, start);
map.put(“size”, size);
gmt_modified字段值為當前時(shí)間。update table set c1=value1,c2=value2,c3=value3; 這是不對的。執行SQL時(shí)，不要更新無(wú)改動(dòng)的字段，一是易出錯；二是效率低；三是增加binlog存儲。@Transactional事務(wù)不要濫用。事務(wù)會(huì )影響數據庫的QPS，另外使用事務(wù)的地方需要考慮各方面的回滾方案，包括緩存回滾、搜索引擎回滾、消息補償、統計修正等。在MySQL中，選擇正確的數據類(lèi)型，對于性能至關(guān)重要。一般應該遵循下面兩步：
在MySQL數據類(lèi)型設置方面，盡量用更小的數據類(lèi)型，因為它們通常有更好的性能，花費更少的硬件資源。并且，盡量 把字段定義為NOT NULL，避免使用NULL.
優(yōu)化建議：
INT(10)類(lèi)似的方法指定字段顯示寬度，直接用INT。AUTO_INCREMENT。優(yōu)化建議：
CURRENT_TIMESTAMP作為默認（MySQL5.6以后），MySQL會(huì )自動(dòng)返回記錄插入的確切時(shí)間。YYYYMMDD HH:MM:SS的整數，與時(shí)區無(wú)關(guān)，你存了什么，讀出來(lái)就是什么。優(yōu)化建議:
我們經(jīng)常會(huì )使用命令來(lái)創(chuàng )建數據表，而且同時(shí)會(huì )指定一個(gè)長(cháng)度，如下。但是，這里的長(cháng)度并非是TINYINT類(lèi)型存儲的最大長(cháng)度，而是顯示的最大長(cháng)度。
CREATE TABLE `user`(
`id` TINYINT(2) UNSIGNED
);
這里表示user表的id字段的類(lèi)型是TINYINT，可以存儲的最大數值是255。所以，在存儲數據時(shí)，如果存入值小于等于255，如200，雖然超過(guò)2位，但是沒(méi)有超出TINYINT類(lèi)型長(cháng)度，所以可以正常保存；如果存入值大于255，如500，那么MySQL會(huì )自動(dòng)保存為T(mén)INYINT類(lèi)型的最大值255。
在查詢(xún)數據時(shí)，不管查詢(xún)結果為何值，都按實(shí)際輸出。這里TINYINT(2)中2的作用就是，當需要在查詢(xún)結果前填充0時(shí)，命令中加上ZEROFILL就可以實(shí)現，如：
`id` TINYINT(2) UNSIGNED ZEROFILL
這樣，查詢(xún)結果如果是5，那輸出就是05。如果指定TINYINT(5)，那輸出就是00005，其實(shí)實(shí)際存儲的值還是5，而且存 儲的數據不會(huì )超過(guò)255，只是MySQL輸出數據時(shí)在前面填充了0。
換句話(huà)說(shuō)，在MySQL命令中，字段的類(lèi)型長(cháng)度TINYINT(2)、INT(11)不會(huì )影響數據的插入，只會(huì )在使用ZEROFILL時(shí)有 用，讓查詢(xún)結果前填充0。
聯(lián)系客服
