一、前言
??????索引是為了高效查詢(xún)排好序的數(shù)據(jù)結(jié)構(gòu)，當(dāng)表數(shù)據(jù)量到達(dá)一個(gè)量級(jí)沒(méi)有對(duì)應(yīng)索引幫助查詢(xún)耗時(shí)會(huì)很長(zhǎng)，MySQL資源開(kāi)銷(xiāo)也會(huì)非常大，當(dāng)然索引也不能隨意創(chuàng)建，要做到盡量少的索引解決盡量多的問(wèn)題，這里會(huì)對(duì)一些業(yè)務(wù)場(chǎng)景做索引優(yōu)化演示，這篇文中只介紹單表索引優(yōu)化，如果單表問(wèn)題能解決多表關(guān)聯(lián)查詢(xún)優(yōu)化就簡(jiǎn)單多了。
如果對(duì)MySQL索引原理還有explain SQL分析工具不是很熟悉的可以看看幾篇文章：
- MySQL 索引底層 B+Tree 原理解析
- MySQL explain SQL分析工具詳解與最佳實(shí)踐
- MySQL 索引介紹和最佳實(shí)踐
二、表數(shù)據(jù)準(zhǔn)備
這里要準(zhǔn)備100w數(shù)據(jù)左右的表，表數(shù)據(jù)盡量多一些或者列多一些，如果數(shù)據(jù)太少，測(cè)試的時(shí)候可能看不到效果。
創(chuàng)建訂單信息表
DROP TABLE IF EXISTS `order_info`;
CREATE TABLE `order_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '訂單ID',
`order_no` varchar(100) NOT NULL COMMENT '訂單編號(hào)',
`customer_id` bigint(20) NOT NULL COMMENT '客戶(hù)編號(hào)',
`goods_id` bigint(20) NOT NULL COMMENT '商品ID',
`goods_title` varchar(100) DEFAULT NULL COMMENT '商品標(biāo)題',
`order_status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '訂單狀態(tài) 1：待支付 2：已支付 3：已發(fā)貨 4、已收貨',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='訂單信息表';
使用存儲(chǔ)過(guò)程插入100w條數(shù)據(jù)
## 創(chuàng)建一個(gè)插入數(shù)據(jù)的存儲(chǔ)過(guò)程
DROP PROCEDURE IF EXISTS insert_procedure;
delimiter;;
CREATE PROCEDURE insert_procedure ()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE goods_id BIGINT DEFAULT CEIL(RAND() * 100);
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
START TRANSACTION;
WHILE ( i <= 1000000 ) DO
INSERT INTO `order_info`(`order_no`,`customer_id`, `goods_id`, `goods_title`, `order_status`, `create_time`) VALUES (CONCAT('ON00000',i), CEIL(RAND() * 100000), goods_id, CONCAT('筆記本電腦',goods_id), MOD(i, 4)+1, NOW());
SET i = i + 1;
SET goods_id = CEIL(RAND() * 100);
END WHILE;
IF t_error=1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END;;
delimiter;
# 調(diào)用存儲(chǔ)過(guò)程插入數(shù)據(jù) 我本地插入100w條數(shù)據(jù)耗時(shí)200s
CALL insert_procedure ();
三、常見(jiàn)業(yè)務(wù)無(wú)索引查詢(xún)耗時(shí)測(cè)試
我電腦的配置32G內(nèi)存500G固態(tài)，MySQL配置全用默認(rèn)，自己測(cè)試先看看自己MySQL配置的innodb_buffer_pool_size設(shè)置的是多少，默認(rèn)是128MB，查看命令SHOW VARIABLES LIKE 'innodb_buffer_pool_size';配置里的單位是字節(jié)，InnoDB使用一個(gè)緩沖池來(lái)保存索引和原始數(shù)據(jù)，innodb_buffer_pool_size就是控制這個(gè)緩沖池的大小，這個(gè)緩沖池在一些情況下對(duì)查詢(xún)性能影響非常大，線上建議設(shè)置成MySQL能使用內(nèi)存的80%，這里不深入。
3.1、通過(guò)訂單ID / 訂單編號(hào) 查詢(xún)指定訂單
- 通過(guò)訂單ID查詢(xún)訂單
SELECT * FROM order_info WHERE id = 955;
- 通過(guò)訂單編號(hào)查詢(xún)訂單
SELECT * FROM order_info WHERE order_no = 'ON000009999';
這里已經(jīng)可以看到查詢(xún)耗時(shí)明顯的差距，我們這里的ID是主鍵，MySQL InnoDB存儲(chǔ)引擎會(huì)自動(dòng)將表中的主鍵設(shè)置為主鍵索引，同時(shí)也是一個(gè)聚簇索引葉子節(jié)點(diǎn)攜帶數(shù)據(jù)，而訂單編號(hào)是沒(méi)有索引的會(huì)進(jìn)行全表掃描，會(huì)將ON000009999和這個(gè)表中每行數(shù)據(jù)的訂單編號(hào)都進(jìn)行比對(duì)然后取出滿足條件的數(shù)據(jù)行，100w數(shù)據(jù)查詢(xún)一個(gè)訂單信息耗時(shí)已經(jīng)到了0.6秒左右。
3.2、查詢(xún)訂單列表
查詢(xún)訂單列表一般查詢(xún)條件比較多，如訂單編號(hào)、客戶(hù)編號(hào)、訂單狀態(tài)、創(chuàng)建時(shí)間、創(chuàng)建時(shí)間倒序、是否分頁(yè)做幾個(gè)演示，查詢(xún)條件的內(nèi)容自己看看存儲(chǔ)過(guò)程插入的數(shù)據(jù)長(zhǎng)什么樣。
- 1、查詢(xún)客戶(hù)編號(hào)為111的訂單列表不分頁(yè)，不根據(jù)創(chuàng)建時(shí)間倒序
SELECT * FROM order_info WHERE customer_id = 111 AND create_time >= '2023-10-02 09:57:24' AND create_time <= '2023-10-02 10:00:46';
- 2、查詢(xún)客戶(hù)編號(hào)為111訂單列表分頁(yè)，不根據(jù)創(chuàng)建時(shí)間倒序
SELECT * FROM order_info WHERE customer_id = 111 AND create_time >= '2023-10-02 09:57:24' AND create_time <= '2023-10-02 10:00:46' LIMIT 3;
- 3、查詢(xún)客戶(hù)編號(hào)為111訂單列表分頁(yè)，根據(jù)創(chuàng)建時(shí)間倒序
SELECT * FROM order_info WHERE customer_id = 111 AND create_time >= '2023-10-02 09:57:24' AND create_time <= '2023-10-02 10:00:46' ORDER BY create_time DESC LIMIT 3;
這里三個(gè)查詢(xún)只有第2個(gè)查詢(xún)相對(duì)較快一點(diǎn)，耗時(shí)110毫秒，其它兩個(gè)查詢(xún)耗時(shí)基本上都接近500毫秒，從我們給定的條件來(lái)看區(qū)別就在分頁(yè)或者不分頁(yè)，排序或者不排序，一般認(rèn)為分頁(yè)肯定比不分頁(yè)查詢(xún)要快，但是我們看1和2查詢(xún)分頁(yè)比不分頁(yè)耗時(shí)相差接近5倍相差可以說(shuō)是巨大，然后在查詢(xún)3中分頁(yè)但是根據(jù)創(chuàng)建時(shí)間倒序，這里耗時(shí)和查詢(xún)1相近和查詢(xún)2耗時(shí)相差接近5倍，這其中原理挺有趣的會(huì)在下面索引優(yōu)化實(shí)踐中舉例說(shuō)明這一些問(wèn)題。
四、訂單常見(jiàn)業(yè)務(wù)索引優(yōu)化實(shí)踐
這里會(huì)對(duì)一些業(yè)務(wù)場(chǎng)景舉例說(shuō)明，也會(huì)對(duì)索引的一些特性做講解。
4.1、通過(guò)唯一索引和普通索引優(yōu)化通過(guò)訂單編號(hào)查詢(xún)訂單信息
類(lèi)似通過(guò)訂單編號(hào)查詢(xún)訂單信息的業(yè)務(wù)有很多，都是通過(guò)一個(gè)編號(hào)信息如客戶(hù)編號(hào)配送員編號(hào)查詢(xún)一個(gè)一對(duì)一的詳情數(shù)據(jù)，這一類(lèi)查詢(xún)都有一個(gè)特性編號(hào)唯一，并且編號(hào)類(lèi)的數(shù)據(jù)大多都是字符串類(lèi)型，這里優(yōu)化可以考慮唯一索引和普通索引，一般我們會(huì)給這一類(lèi)編號(hào)數(shù)據(jù)設(shè)置一個(gè)唯一索引，既保證了數(shù)據(jù)的唯一性也保證了通過(guò)編號(hào)查詢(xún)的性能問(wèn)題。
- 1、添加唯一索引
ALTER TABLE `order_info` ADD UNIQUE INDEX `idx_orderNo`(`order_no`);
- 2、添加索引后查詢(xún)
SELECT * FROM order_info WHERE order_no = 'ON000009999';
無(wú)索引測(cè)試的時(shí)候耗時(shí)0.538s，添加索引后查詢(xún)性能提升十幾倍，數(shù)據(jù)量越大提升比例越高。
4.2、通過(guò)普通聯(lián)合索引優(yōu)化訂單列表查詢(xún)
??????在上面無(wú)索引查詢(xún)我們列舉了查詢(xún)訂單列表的三個(gè)例子，查詢(xún)耗時(shí)除了第2個(gè)都差不多耗時(shí)0.5s，不算太慢但是對(duì)MySQL性能開(kāi)銷(xiāo)其實(shí)是很大的，如果數(shù)據(jù)量在大一些到500w 1000w，查詢(xún)時(shí)間也會(huì)增加到接受不了數(shù)值，所以必須要優(yōu)化。
4.2.1、分析查詢(xún)字段的查詢(xún)場(chǎng)景
??????優(yōu)化前第一要考慮的就是需要一些什么字段，如我們例子中會(huì)使用訂單編號(hào)、客戶(hù)編號(hào)、訂單狀態(tài)、創(chuàng)建時(shí)間。
分析通過(guò)這四個(gè)字段查詢(xún)的場(chǎng)景：
- 1、需要根據(jù)訂單編號(hào)查詢(xún)
- 2、需要根據(jù)客戶(hù)編號(hào)查詢(xún)
- 3、需要根據(jù)創(chuàng)建時(shí)間查詢(xún)
- 4、需要根據(jù)訂單狀態(tài)查詢(xún)
這里我給這四個(gè)字段分了四個(gè)查詢(xún)場(chǎng)景，為什么這么分我在下面會(huì)詳細(xì)說(shuō)明。
4.2.2、優(yōu)化各場(chǎng)景查詢(xún)和原因分析
對(duì)于這種列表查詢(xún)使用索引一定要知道索引的一個(gè)特性就是最左前綴原則，索引的匹配一定是從最左邊第一個(gè)字段開(kāi)始匹配的，不能跳過(guò)中間字段匹配，在索引優(yōu)化實(shí)踐中會(huì)詳細(xì)說(shuō)明。
4.2.2.1、需要根據(jù)訂單編號(hào)查詢(xún)
??????在一個(gè)訂單列表如果需要根據(jù)訂單編號(hào)查詢(xún)，那么一定是要查詢(xún)一個(gè)唯一的訂單，如果我們有索引那么我們可以通過(guò)一個(gè)訂單編號(hào)快速定位到一條數(shù)據(jù)不用進(jìn)行全表掃描，竟然能快速定位到一條數(shù)據(jù)那么就算還攜帶別的條件那直接回表取出行數(shù)據(jù)再去判斷即可。
??????所以這里只需要?jiǎng)?chuàng)建一個(gè)訂單編號(hào)的索引來(lái)適配所有帶訂單編號(hào)的查詢(xún)，我們?cè)诒疚牡?.1的優(yōu)化通過(guò)訂單編號(hào)查詢(xún)訂單信息中創(chuàng)建過(guò)一個(gè)訂單編號(hào)的唯一索引，我們這里就用這個(gè)唯一索引就行。
- 1、需要根據(jù)訂單編號(hào)查詢(xún)測(cè)試
SELECT * FROM order_info WHERE order_no = 'ON000009999' AND order_status=4;
執(zhí)行計(jì)劃 EXPLAIN
這里可以看到查詢(xún)耗時(shí)為0.037s，執(zhí)行計(jì)劃中使用到了訂單編號(hào)的索引，掃描估計(jì)行數(shù)為1。
4.2.2.2、需要根據(jù)客戶(hù)編號(hào)查詢(xún)
??????在需要根據(jù)客戶(hù)編號(hào)查詢(xún)的業(yè)務(wù)中一定是以客戶(hù)編號(hào)為主要條件的，還有可能會(huì)攜帶訂單狀態(tài)，創(chuàng)建時(shí)間等，一個(gè)客戶(hù)可能會(huì)下很多單，想想自己這些年網(wǎng)購(gòu)和點(diǎn)外面應(yīng)該也有個(gè)100單以上了把，那么這里就不能像訂單編號(hào)只用一個(gè)單字段索引了，我們需要把訂單狀態(tài)和創(chuàng)建時(shí)間也加上，其實(shí)就算不加性能也不會(huì)差太多因?yàn)橐粋€(gè)客戶(hù)訂單本來(lái)也不會(huì)太多，單表幾十條數(shù)據(jù)和幾百條數(shù)據(jù)查詢(xún)差距不會(huì)很大。
- 1、創(chuàng)建以客戶(hù)編號(hào)起頭的普通聯(lián)合索引
ALTER TABLE `order_info` ADD INDEX `idx_customerId_orderStatus_createTime`(`customer_id`, `order_status`, `create_time`);
我這里會(huì)把訂單狀態(tài)和創(chuàng)建時(shí)間也帶上，某購(gòu)物APP查詢(xún)自己訂單是不是都有狀態(tài)選擇，時(shí)間字段可以用作排序和檢索。
- 2、需要根據(jù)客戶(hù)編號(hào)查詢(xún)測(cè)試
SELECT * FROM order_info WHERE customer_id = 111 AND order_status=4 AND create_time >= '2023-10-02 09:57:24' AND create_time <= '2023-10-02 10:00:46';
執(zhí)行計(jì)劃 EXPLAIN
這里可以看到使用到了我們創(chuàng)建的聯(lián)合索引，并且三個(gè)字段全用到了，客戶(hù)編號(hào)bigint類(lèi)型不能為空占用8字節(jié)，訂單狀態(tài)tinyint不能為空占用1字節(jié)，創(chuàng)建時(shí)間datetime類(lèi)型占用5個(gè)字節(jié)因?yàn)閯?chuàng)建時(shí)間可為空所以多加一個(gè)字節(jié)，創(chuàng)建時(shí)間占用6個(gè)字節(jié)，合集15個(gè)字節(jié)和key_len相等。
4.2.2.3、需要根據(jù)創(chuàng)建時(shí)間查詢(xún) 和 需要根據(jù)訂單狀態(tài)查詢(xún)
??????在查詢(xún)訂單列表時(shí)經(jīng)常會(huì)查詢(xún)某個(gè)狀態(tài)某個(gè)時(shí)間有多少訂單，狀態(tài)值只有4個(gè)如果要通過(guò)狀態(tài)值建立索引的話顯然是不可行的，通過(guò)狀態(tài)索引查找某個(gè)類(lèi)型數(shù)據(jù)可能得到的是幾十萬(wàn)行數(shù)據(jù)，然后還需要回表獲取聚簇索引數(shù)據(jù)，所以對(duì)于這種狀態(tài)值創(chuàng)建單獨(dú)索引時(shí)還需要帶上時(shí)間字段，在單獨(dú)查詢(xún)某個(gè)時(shí)間內(nèi)全部訂單時(shí)也可以使用這個(gè)索引，通過(guò)in查詢(xún)將狀態(tài)值全部包含滿足最左前綴原則就能使用該索引查詢(xún)指定時(shí)間段的全部訂單。
- 1、添加索引前查詢(xún)某個(gè)時(shí)間段內(nèi)全部訂單
SELECT * FROM order_info WHERE order_status IN (1,2,3,4) AND create_time >= '2023-10-02 09:57:30' AND create_time <= '2023-10-02 09:57:31';
因?yàn)槲覀兪桥坎迦氲臅r(shí)間間隔比較相近1秒有好幾千條數(shù)據(jù)，自己查詢(xún)測(cè)試的時(shí)候最好控制區(qū)間在2s的樣子。
- 2、創(chuàng)建訂單狀態(tài)和創(chuàng)建時(shí)間的普通索引
ALTER TABLE `order_info` ADD INDEX `idx_orderStatus_createTime`(`order_status`,`create_time`);
- 3、查詢(xún)某個(gè)時(shí)間段內(nèi)全部狀態(tài)訂單
SELECT * FROM order_info WHERE order_status IN (1,2,3,4) AND create_time >= '2023-10-02 09:57:30' AND create_time <= '2023-10-02 09:57:40' ;
執(zhí)行計(jì)劃 EXPLAIN
我們這里數(shù)據(jù)是批量插入的，每秒會(huì)插入幾千條數(shù)據(jù)，查詢(xún)時(shí)間間隔不能太大了最好在2s的樣子，不然索引可能是會(huì)失效的，要查詢(xún)某個(gè)狀態(tài)的訂單只有把訂單狀態(tài)的的IN查詢(xún)換成=查詢(xún)效果是一樣的，滿足最左前綴原則即可。
五、索引優(yōu)化實(shí)踐
在訂單常見(jiàn)業(yè)務(wù)索引優(yōu)化實(shí)踐中簡(jiǎn)單的介紹了一下在一些場(chǎng)景下創(chuàng)建一些什么索引能提升查詢(xún)效率，但是還有很多可變因素會(huì)影響到索引的使用，也有很多場(chǎng)景可以使用更好的索引，以及索引中很重要的左前綴原則，這里會(huì)對(duì)一些場(chǎng)景做舉例說(shuō)明。
刪除之前創(chuàng)建的索引，創(chuàng)建新的測(cè)試聯(lián)合索引
# 刪除上面創(chuàng)建的三個(gè)索引，避免測(cè)試時(shí)被其它索引干擾，要是沒(méi)有創(chuàng)建則不用刪除
ALTER TABLE `order_info` DROP INDEX `idx_orderNo`;
ALTER TABLE `order_info` DROP INDEX `idx_customerId_orderStatus_createTime`;
ALTER TABLE `order_info` DROP INDEX `idx_orderStatus_createTime`;
# 創(chuàng)建新的測(cè)試聯(lián)合索引
ALTER TABLE `order_info` ADD INDEX `idx_goodsTitle_customerId_orderStatus`(`goods_title`, `customer_id`, `order_status`);
4.1 聯(lián)合索引第一個(gè)字段用范圍查詢(xún)可能不會(huì)走索引
EXPLAIN SELECT * FROM order_info WHERE goods_title > '筆記本電腦9' AND customer_id = 9 AND order_status = 4;
- 給一個(gè)區(qū)間值
EXPLAIN SELECT * FROM order_info WHERE goods_title > '筆記本電腦9' AND goods_title < '筆記本電腦99' AND customer_id = 9 AND order_status = 4;
MySQL5.7
MySQL8.0
聯(lián)合索引第一個(gè)字段就用范圍查找可能不會(huì)走索引，對(duì)于MySQL5.7來(lái)說(shuō)只要第一個(gè)字段用范圍查找不會(huì)走索引，但是對(duì)于MySQL8.0來(lái)說(shuō)給個(gè)查詢(xún)區(qū)間還是可能會(huì)走索引的，前提是區(qū)間也不能太大不然也不會(huì)走索引，MySQL內(nèi)部可能覺(jué)得第一個(gè)字段用范圍，結(jié)果集應(yīng)該很大，回表效率不高，還不如就全表掃描，創(chuàng)建聯(lián)合索引時(shí)千萬(wàn)別把時(shí)間這類(lèi)型字段放第一個(gè)了。
4.2 強(qiáng)制走索引（聯(lián)合索引第一個(gè)字段用范圍查詢(xún)不會(huì)走索引這里強(qiáng)制使用索引）
EXPLAIN SELECT * FROM order_info FORCE INDEX(idx_goodsTitle_customerId_orderStatus) WHERE goods_title > '筆記本電腦9' AND customer_id = 9 AND order_status = 4;
這里確認(rèn)是使用了我們指定的索引，然后再來(lái)看看查詢(xún)性能怎么樣
-
不指定強(qiáng)制走索引
-
指定強(qiáng)制走索引
經(jīng)過(guò)對(duì)比發(fā)現(xiàn)強(qiáng)制走索引查詢(xún)時(shí)間能縮短近10倍，所以有時(shí)候MySQL自身并不一定能選擇到性能最高的索引使用方式，需要自己不斷的嘗試對(duì)比出最好的方式。
4.3 使用覆蓋索引優(yōu)化查詢(xún)
EXPLAIN SELECT goods_title,customer_id,order_status FROM order_info WHERE goods_title > '筆記本電腦9' AND customer_id = 9 AND order_status = 4;
查詢(xún)結(jié)果字段和條件字段都在同一個(gè)索引中，查詢(xún)可以完全使用索引中字段不用回表則稱(chēng)為覆蓋索引，覆蓋索引因?yàn)槟鼙苊饣乇砭退懵?lián)合索引第一個(gè)字段范圍查詢(xún)也能走索引。
4.4 in和or在表數(shù)據(jù)量比較大的情況會(huì)走索引，在表記錄不多的情況下會(huì)選擇全表掃描
- 100w數(shù)據(jù)表測(cè)試
EXPLAIN SELECT * FROM order_info WHERE goods_title IN('筆記本電腦8','筆記本電腦9') AND customer_id = 9 AND order_status = 4;
EXPLAIN SELECT * FROM order_info WHERE (goods_title = '筆記本電腦8' OR goods_title = '筆記本電腦9') AND customer_id = 9 AND order_status = 4;
- 根據(jù)order_info創(chuàng)建一個(gè)只有3條數(shù)據(jù)order_info_copy表進(jìn)行測(cè)試
MySQL5.7
MySQL8.0
這里可以看到MySQL5.7中會(huì)進(jìn)行全表掃描，但是MySQL8.0還是會(huì)走索引，現(xiàn)在MySQL8.0市場(chǎng)上用的已經(jīng)比較多了，它執(zhí)意要走索引肯定有它的道理，而且要通過(guò)索引優(yōu)化查詢(xún)肯定是要測(cè)試比較查詢(xún)效率的，在實(shí)際業(yè)務(wù)中多測(cè)試再看看執(zhí)行計(jì)劃，只要實(shí)踐才知道加的索引是否好用。
4.5 like KK% 一般情況也是可以走索引的
- like 筆記本電腦999
EXPLAIN SELECT * FROM order_info WHERE goods_title LIKE '筆記本電腦999%' AND customer_id = 9 AND order_status = 4;
- like 筆記本電腦9
EXPLAIN SELECT * FROM order_info WHERE goods_title LIKE '筆記本電腦9%' AND customer_id = 9 AND order_status = 4;
使用like前綴查詢(xún)?cè)诮Y(jié)果集較少的時(shí)候是會(huì)走索引的，如果MySQL認(rèn)為結(jié)果集較大還是不會(huì)走索引，結(jié)合以上幾個(gè)例子可以看出，如果通過(guò)索引查詢(xún)響應(yīng)結(jié)果集過(guò)大并且沒(méi)有滿足覆蓋索引也很有可能不會(huì)走索引，這點(diǎn)MySQL5.7 8.0都是一樣的。
4.6 分頁(yè)查詢(xún)索引使用和優(yōu)化
- 例1：在沒(méi)有索引且不分頁(yè)的請(qǐng)求下查詢(xún)指定客戶(hù)訂單列表數(shù)據(jù)
EXPLAIN SELECT * FROM order_info WHERE customer_id = 9 ORDER BY goods_title;
- 例2：在沒(méi)有索引分頁(yè)查詢(xún)指定客戶(hù)訂單列表數(shù)據(jù)
SELECT * FROM order_info WHERE customer_id = 9 LIMIT 0,3;
SELECT * FROM order_info WHERE customer_id = 9 LIMIT 80,3;
通過(guò)這兩個(gè)分頁(yè)查詢(xún)可以看到查詢(xún)從第0條開(kāi)始往后查3條數(shù)據(jù)執(zhí)行耗時(shí)很短，查詢(xún)從第80條開(kāi)始往后查3條數(shù)據(jù)耗時(shí)和不分頁(yè)差不多，因?yàn)镸ySQL分頁(yè)查詢(xún)的時(shí)候會(huì)根據(jù)我們的分頁(yè)條件找出對(duì)應(yīng)結(jié)果集的數(shù)據(jù)，比如我們分頁(yè)條件時(shí)customer_id = 9 LIMIT 0,3，MySQL會(huì)先用customer_id = 9去一條條對(duì)比數(shù)據(jù)，因?yàn)槲覀兊姆猪?yè)參數(shù)時(shí)0,3也就是說(shuō)找出3條數(shù)據(jù)即可，只要找到了3條數(shù)據(jù)就不會(huì)往后找了，同理LIMIT 80,3的時(shí)候需要找到83條數(shù)據(jù)就不會(huì)往后找了，所以這里我們LIMIT 80,3的時(shí)候和不分頁(yè)耗時(shí)差不了多少，不加分頁(yè)會(huì)掃描全表拿出全表的數(shù)據(jù)。
- 例3：在沒(méi)有索引分頁(yè)查詢(xún)指定客戶(hù)訂單列表數(shù)據(jù)并且根據(jù)創(chuàng)建時(shí)間排序
SELECT * FROM order_info WHERE customer_id = 9 ORDER BY create_time LIMIT 0,3;
這里加了一個(gè)排序條件分頁(yè)查詢(xún)后耗時(shí)也和不分頁(yè)差不多，因?yàn)槿绻恿伺判蜃侄问切枰葤呷慝@取全部符合結(jié)果的數(shù)據(jù)才能進(jìn)行分頁(yè)。
- 例4：創(chuàng)建索引分頁(yè)查詢(xún)指定客戶(hù)訂單列表數(shù)據(jù)并且根據(jù)創(chuàng)建時(shí)間排序（正序）
# 創(chuàng)建索引
ALTER TABLE `order_info` ADD INDEX `idx_customerId_createTime`(`customer_id`, `create_time`);
# 刪除索引
ALTER TABLE `order_info` DROP INDEX `idx_customerId_createTime`;
SELECT * FROM order_info WHERE customer_id = 9 ORDER BY create_time LIMIT 0,3;
執(zhí)行計(jì)劃 EXPLAIN
創(chuàng)建索引后查詢(xún)快了很多，因?yàn)檫@里是正序的索引默認(rèn)也是正序的根本不用在進(jìn)行排序，下面再試試倒序。
- 例4：創(chuàng)建索引分頁(yè)查詢(xún)指定客戶(hù)訂單列表數(shù)據(jù)并且根據(jù)創(chuàng)建時(shí)間排序（倒序）
SELECT * FROM order_info WHERE customer_id = 9 ORDER BY create_time DESC LIMIT 0,3;
MySQL 5.7
MySQL8.0
查詢(xún)時(shí)間和正序差不多，但是這里MySQL5.7和MySQL8.0的Extra有點(diǎn)區(qū)別，MySQL8.0可以反向索引掃描，MySQL5.7應(yīng)該是將獲取到的數(shù)據(jù)放入內(nèi)存中排序，也可以創(chuàng)建倒序索引這里不深入。
4.7 order by查詢(xún)索引使用和優(yōu)化
- 例1：
EXPLAIN SELECT * FROM order_info WHERE goods_title = '筆記本電腦9' AND order_status = 4 ORDER BY customer_id;
利用最左前綴原則：中間字段不能斷，因此查詢(xún)用到了goods_title索引，從key_len=403也能看出，customer_id索引列用在排序過(guò)程中，因?yàn)镋xtra字段里沒(méi)有using filesort，而且索引本來(lái)就是正序的無(wú)需在排。
- 列2：
EXPLAIN SELECT * FROM order_info WHERE goods_title = '筆記本電腦9' ORDER BY order_status;
從explain的執(zhí)行結(jié)果來(lái)看：key_len=403，查詢(xún)使用了goods_title 索引，由于用了order_status進(jìn)行排序，跳過(guò)了
customer_id，出現(xiàn)了Using filesort。
- 列3：
EXPLAIN SELECT * FROM order_info WHERE goods_title = '筆記本電腦9' ORDER BY customer_id,order_status;
查找只用到索引goods_title 字段，customer_id和order_status用于排序，無(wú)Using filesort。
- 列4：
EXPLAIN SELECT * FROM order_info WHERE goods_title = '筆記本電腦9' ORDER BY order_status,customer_id;
和例3中explain的執(zhí)行結(jié)果一樣，但是出現(xiàn)了Using filesort，因?yàn)樗饕膭?chuàng)建順序?yàn)間oods_title,customer_id,order_status，但是排序的時(shí)候customer_id和order_status顛倒位置了。
- 列5：
EXPLAIN SELECT * FROM order_info WHERE goods_title = '筆記本電腦9' AND customer_id = 9 ORDER BY order_status,customer_id;
與例4對(duì)比，在Extra中并未出現(xiàn)Using filesort，因?yàn)閏ustomer_id 為常量，在排序中被優(yōu)化，所以索引未顛倒，不會(huì)出現(xiàn)Using filesort。
- 列6：
EXPLAIN SELECT * FROM order_info WHERE goods_title = '筆記本電腦9' ORDER BY customer_id ASC,order_status DESC;
雖然排序的字段列與索引順序一樣，且order by默認(rèn)升序，這里position desc變成了降序，導(dǎo)致與索引的排序方式不同，從而產(chǎn)生Using filesort。
- 列7：
EXPLAIN SELECT * FROM order_info WHERE goods_title IN ('筆記本電腦9','筆記本電腦10') ORDER BY customer_id,order_status;
對(duì)于排序來(lái)說(shuō)，IN查詢(xún)也是范圍查詢(xún)，如果排序查詢(xún)中使用范圍查詢(xún)則索引中只能用到goods_title，后面的字段無(wú)法使用。
- 列8：
EXPLAIN SELECT * FROM order_info WHERE goods_title > '筆記本電腦9' ORDER BY goods_title;
MySQL5.7
MySQL8.0
MySQL5.7只要是聯(lián)合索引第一個(gè)字段要走范圍查詢(xún)，那么索引就沒(méi)法使用，除非覆蓋索引，但是在MySQL8.0時(shí)卻是會(huì)去使用索引的，還可以對(duì)比一下查詢(xún)性能，我這里MySQL5.7全表掃描耗時(shí)1.3s，MySQL8.0走索引耗時(shí)0.8s，兩個(gè)版本MySQL第一次執(zhí)行可能時(shí)間都需要2s的樣子，會(huì)將磁盤(pán)中的聚簇索引葉子節(jié)點(diǎn)數(shù)據(jù)加載到內(nèi)存中，多次執(zhí)行后回表可以直接查內(nèi)存不用再次讀取磁盤(pán)。
4.7 總結(jié)
- 1、MySQL支持兩種方式的排序filesort和index，Using index是指MySQL掃描索引本身完成排序。index
效率高，filesort效率低。 - 2、order by滿足兩種情況會(huì)使用Using index。
- order by語(yǔ)句使用索引最左前列。
- 使用where子句與order by子句條件列組合滿足索引最左前列。
- 3、盡量在索引列上完成排序，遵循索引建立（索引創(chuàng)建的順序）時(shí)的最左前綴法則。
- 4、如果order by的條件不在索引列上，就會(huì)產(chǎn)生Using filesort。
- 5、能用覆蓋索引盡量用覆蓋索引
- 6、group by與order by很類(lèi)似，其實(shí)質(zhì)是先排序后分組，遵照索引創(chuàng)建順序的最左前綴法則。對(duì)于group
by的優(yōu)化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能寫(xiě)在where中
的限定條件就不要去having限定了。
Using filesort文件排序原理詳解
- filesort文件排序方式
- 單路排序：是一次性取出滿足條件行的所有字段，然后在sort buffer中進(jìn)行排序；用trace工具可
以看到sort_mode信息里顯示< sort_key, additional_fields >或者< sort_key,
packed_additional_fields > - 雙路排序（又叫回表排序模式）：是首先根據(jù)相應(yīng)的條件取出相應(yīng)的排序字段和可以直接定位行
數(shù)據(jù)的行 ID，然后在 sort buffer 中進(jìn)行排序，排序完后需要再次取回其它需要的字段；用trace工具
可以看到sort_mode信息里顯示< sort_key, rowid >
- 單路排序：是一次性取出滿足條件行的所有字段，然后在sort buffer中進(jìn)行排序；用trace工具可
- MySQL 通過(guò)比較系統(tǒng)變量 max_length_for_sort_data(默認(rèn)1024字節(jié)) 的大小和需要查詢(xún)的字段總大小來(lái)
判斷使用哪種排序模式。- 如果 字段的總長(zhǎng)度小于max_length_for_sort_data ，那么使用 單路排序模式；
- 如果 字段的總長(zhǎng)度大于max_length_for_sort_data ，那么使用 雙路排序模式。
4.8 count()查詢(xún)優(yōu)化
count查詢(xún)常見(jiàn)有4種寫(xiě)法count(1)、count(字段)、count(主鍵 id)、count(*)，這里會(huì)對(duì)4種寫(xiě)法原理和性能做講解，這里MySQL5.7和MySQL8.0區(qū)別有點(diǎn)大，這里分開(kāi)講解，還需要將之前創(chuàng)建的索引全刪除避免測(cè)試被影響。
# 刪除之前創(chuàng)建的所有索引，已經(jīng)刪除的不用重復(fù)執(zhí)行
ALTER TABLE `order_info` DROP INDEX `idx_orderNo`;
ALTER TABLE `order_info` DROP INDEX `idx_customerId_orderStatus_createTime`;
ALTER TABLE `order_info` DROP INDEX `idx_orderStatus_createTime`;
ALTER TABLE `order_info` DROP INDEX `idx_goodsTitle_customerId_orderStatus`;
ALTER TABLE `order_info` DROP INDEX `idx_customerId_createTime`;
4.8.1 MySQL5.7 count()查詢(xún)優(yōu)化
先比對(duì)查詢(xún)時(shí)間后面做總結(jié)
-
MySQL5.7（goods_id字段無(wú)索引）
這里我執(zhí)行了很多次得到的執(zhí)行效率結(jié)果都是 count(*) ≈ count(1) > count(id) > count(goods_id) -
MySQL5.7（goods_id字段建立索引）
# 新增idx_goodsId索引
ALTER TABLE order_info ADD INDEX idx_goodsId(goods_id);
# 刪除idx_goodsId索引
ALTER TABLE `order_info` DROP INDEX `idx_goodsId`;
這里給goods_id字段建立索引后，我執(zhí)行了很多次得到的執(zhí)行效率結(jié)果也還是 count(*) ≈ count(1) > count(id) > count(goods_id)，反而加上索引后count(goods_id)更加慢了，而且有一點(diǎn)蛋疼的事這幾條SQL的執(zhí)行計(jì)劃都是下圖這樣的，理論上應(yīng)該會(huì)展示索引的使用。
- MySQL5.7 count()查詢(xún)總結(jié)
- 通過(guò)上面兩個(gè)查詢(xún)效率比對(duì)可以確認(rèn)count(*) ≈ count(1) > count(id) > count(goods_id)，在count查詢(xún)的時(shí)候直接使用count(
*)或者count(1)是最好的，MySQL使用count(*)并不會(huì)把全部字段取出來(lái)，而是專(zhuān)門(mén)做了優(yōu)化，不取值，按行累加，效率很高，count(1)不需要取出字段統(tǒng)計(jì)，就用常量1做統(tǒng)計(jì)，效率也很高。 - count(id)可以走主鍵索引，如果有輔助索引會(huì)選擇走輔助索引相對(duì)主鍵索引存儲(chǔ)數(shù)據(jù)更少，檢索性能應(yīng)該更高。
- 理論上count(goods_id)，goods_id沒(méi)有索引時(shí)會(huì)全表掃描拿出goods_id字段統(tǒng)計(jì)，建立索引后只用掃描索引統(tǒng)計(jì)，效率應(yīng)該更高的，這里測(cè)試好像并沒(méi)有，而且執(zhí)行計(jì)劃中也沒(méi)有顯示其它信息，我這可能是個(gè)例。
- 我們目的是找到效率最高的查詢(xún)方法，對(duì)于不同業(yè)務(wù)可能需要不同的方式，要找到最合適的方法必須靠實(shí)踐。
- 通過(guò)上面兩個(gè)查詢(xún)效率比對(duì)可以確認(rèn)count(*) ≈ count(1) > count(id) > count(goods_id)，在count查詢(xún)的時(shí)候直接使用count(
4.8.2 MySQL8.0 count()查詢(xún)優(yōu)化
-
MySQL8.0（goods_id字段無(wú)索引）
其中count(1)、count(id)、count(*) 都是使用了主鍵索引執(zhí)行計(jì)劃如下
count(goods_id)會(huì)進(jìn)行全表掃描
-
MySQL8.0（goods_id字段有索引）文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-729443.html
# 新增idx_goodsId索引
ALTER TABLE order_info ADD INDEX idx_goodsId(goods_id);
# 刪除idx_goodsId索引
ALTER TABLE `order_info` DROP INDEX `idx_goodsId`;
添加上索引后四個(gè)查詢(xún)都會(huì)走idx_goodsId索引
文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-729443.html
- MySQL8.0 count()查詢(xún)總結(jié)
- 我這里100w數(shù)據(jù)測(cè)試，我執(zhí)行了很多次得到的查詢(xún)效率都差不多，估計(jì)要加大數(shù)據(jù)測(cè)試才能行，不過(guò)和MySQL5.7理論應(yīng)該是一致的count(*) 和 count(1)一定是最穩(wěn)定效率最高的
- 在count查詢(xún)的時(shí)候直接使用count(
*)或者count(1)是最好的，MySQL使用count(*)并不會(huì)把全部字段取出來(lái)，而是專(zhuān)門(mén)做了優(yōu)化，不取值，按行累加，效率很高，count(1)不需要取出字段統(tǒng)計(jì)，就用常量1做統(tǒng)計(jì)，效率也很高。 - count(id)可以走主鍵索引，如果有輔助索引會(huì)選擇走輔助索引相對(duì)主鍵索引存儲(chǔ)數(shù)據(jù)更少，檢索性能應(yīng)該更高。
- 理論上count(goods_id)，goods_id沒(méi)有索引時(shí)會(huì)全表掃描拿出goods_id字段統(tǒng)計(jì)，建立索引后只用掃描索引統(tǒng)計(jì)，效率應(yīng)該更高的。
六、索引設(shè)計(jì)原則
- 1、代碼先行，索引后上
不知大家一般是怎么給數(shù)據(jù)表建立索引的，是建完表馬上就建立索引嗎？
這其實(shí)是不對(duì)的，一般應(yīng)該等到主體業(yè)務(wù)功能開(kāi)發(fā)完畢，把涉及到該表相關(guān)sql都要拿出來(lái)分析之后再建立
索引。 - 2、聯(lián)合索引盡量覆蓋條件
比如可以設(shè)計(jì)一個(gè)或者兩三個(gè)聯(lián)合索引(盡量少建單值索引)，讓每一個(gè)聯(lián)合索引都盡量去包含sql語(yǔ)句里的
where、order by、group by的字段，還要確保這些聯(lián)合索引的字段順序盡量滿足sql查詢(xún)的最左前綴原
則。 - 3、不要在小基數(shù)字段上建立索引
索引基數(shù)是指這個(gè)字段在表里總共有多少個(gè)不同的值，比如一張表總共100萬(wàn)行記錄，其中有個(gè)性別字段，
其值不是男就是女，那么該字段的基數(shù)就是2。
如果對(duì)這種小基數(shù)字段建立索引的話，還不如全表掃描了，因?yàn)槟愕乃饕龢?shù)里就包含男和女兩種值，根本沒(méi)
法進(jìn)行快速的二分查找，那用索引就沒(méi)有太大的意義了。
一般建立索引，盡量使用那些基數(shù)比較大的字段，就是值比較多的字段，那么才能發(fā)揮出B+樹(shù)快速二分查
找的優(yōu)勢(shì)來(lái)。 - 4、長(zhǎng)字符串我們可以采用前綴索引
盡量對(duì)字段類(lèi)型較小的列設(shè)計(jì)索引，比如說(shuō)什么tinyint之類(lèi)的，因?yàn)樽侄晤?lèi)型較小的話，占用磁盤(pán)空間也會(huì)
比較小，此時(shí)你在搜索的時(shí)候性能也會(huì)比較好一點(diǎn)。
當(dāng)然，這個(gè)所謂的字段類(lèi)型小一點(diǎn)的列，也不是絕對(duì)的，很多時(shí)候你就是要針對(duì)varchar(255)這種字段建立
索引，哪怕多占用一些磁盤(pán)空間也是有必要的。
對(duì)于這種varchar(255)的大字段可能會(huì)比較占用磁盤(pán)空間，可以稍微優(yōu)化下，比如針對(duì)這個(gè)字段的前20個(gè)
字符建立索引，就是說(shuō)，對(duì)這個(gè)字段里的每個(gè)值的前20個(gè)字符放在索引樹(shù)里，類(lèi)似于 KEY
index(goods_title(20), customer_id, order_status)。
此時(shí)你在where條件里搜索的時(shí)候，如果是根據(jù)goods_title字段來(lái)搜索，那么此時(shí)就會(huì)先到索引樹(shù)里根據(jù)goods_title
字段的前20個(gè)字符去搜索，定位到之后前20個(gè)字符的前綴匹配的部分?jǐn)?shù)據(jù)之后，再回到聚簇索引提取出來(lái)
完整的goods_title字段值進(jìn)行比對(duì)。
但是假如你要是order by goods_title，那么此時(shí)你的goods_title因?yàn)樵谒饕龢?shù)里僅僅包含了前20個(gè)字符，所以這個(gè)排
序是沒(méi)法用上索引的， group by也是同理。所以這里大家要對(duì)前綴索引有一個(gè)了解。 - 5、where與order by沖突時(shí)優(yōu)先where
在where和order by出現(xiàn)索引設(shè)計(jì)沖突時(shí)，到底是針對(duì)where去設(shè)計(jì)索引，還是針對(duì)order by設(shè)計(jì)索引？到
底是讓where去用上索引，還是讓order by用上索引?
一般這種時(shí)候往往都是讓where條件去使用索引來(lái)快速篩選出來(lái)一部分指定的數(shù)據(jù)，接著再進(jìn)行排序。
因?yàn)榇蠖鄶?shù)情況基于索引進(jìn)行where篩選往往可以最快速度篩選出你要的少部分?jǐn)?shù)據(jù)，然后做排序的成本可
能會(huì)小很多。 - 6、基于慢sql查詢(xún)做優(yōu)化
可以根據(jù)監(jiān)控后臺(tái)的一些慢sql，針對(duì)這些慢sql查詢(xún)做特定的索引優(yōu)化。
到了這里，關(guān)于MySQL 索引優(yōu)化實(shí)踐（單表）的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
