一、MySQL強制使用索引的兩種方式
1、使用 FORCE INDEX 語句：
explain
select
*
from
tbl_test force index (index_item_code)
where
(item_code between 1 and 1000) and (random between 50000 and 1000000)
order by
random
limit 1;使用 FORCE?INDEX（索引名稱）走索引：
2、使用 USE INDEX 語句：
explain
select
*
from
tbl_test USE index (index_item_code)
where
(item_code between 1 and 1000) and (random between 50000 and 1000000)
order by
random
limit 1;使用 USE INDEX（索引名稱）走索引：
FORCE INDEX 或 USE INDEX?的區(qū)別？
- FORCE INDEX ：這個語句指示MySQL強制查詢使用特定的索引。它會忽略優(yōu)化器的選擇,無論索引的選擇性如何，都會使用指定的索引。這意味著即使使用了不太適合的索引，MySQL也會強制使用它。這可能會導(dǎo)致性能下降，因為不適合的索引可能會導(dǎo)致查詢變慢。
- USE INDEX ：這個語句也允許你指定要使用的索引，但它與"FORCE INDEX"不同的是，它只是暗示MySQL在可能的情況下使用指定的索引。如果MySQL認為其他索引更適合查詢，它仍然可以選擇其他索引。這樣可以保留一定的靈活性，讓MySQL根據(jù)實際情況選擇最佳的索引。
總的來說，"FORCE INDEX"是強制使用指定索引，而"USE INDEX"是暗示使用指定索引，但MySQL仍然可以根據(jù)優(yōu)化器的判斷選擇其他索引。實際使用時，應(yīng)根據(jù)具體情況進行評估選擇。
二、具體實現(xiàn)數(shù)據(jù)如下
1、創(chuàng)建一張數(shù)據(jù)表及索引：
CREATE TABLE `tbl_test` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
`name` varchar(100) NOT NULL COMMENT '姓名',
`item_code` bigint NOT NULL COMMENT '子項編號',
`order_code` varchar(100) NOT NULL COMMENT '訂單編號',
`id_card` varchar(30) NOT NULL COMMENT '身份證',
`goods_number` bigint NOT NULL COMMENT '商品數(shù)量',
`amount` decimal(6,2) NOT NULL COMMENT '金額',
`create_time` datetime NOT NULL COMMENT '創(chuàng)建時間',
`random` bigint NOT NULL COMMENT '數(shù)據(jù)數(shù)',
PRIMARY KEY (`id`),
KEY `index_item_code` (`item_code`),
KEY `index_id_card` (`id_card`),
KEY `index_random` (`random`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;注：表創(chuàng)建完成后，使用如下命令新增索引：
-- 查看tbl_test表中全部的索引信息
show index from tbl_test;
添加索引：
-- 在tbl_test表中，goods_number列上創(chuàng)建索引
CREATE INDEX index_goods_number ON tbl_test (goods_number);
刪除索引：
-- 在tbl_test表中，刪除名稱為 index_goods_number 的索引
ALTER TABLE tbl_test DROP INDEX index_goods_number;
2、創(chuàng)建存儲過程：
-- 創(chuàng)建存儲過程
create procedure insert_data() begin declare i INT default 1;
while i <= 100000 DO
insert into test.tbl_test (
name,
item_code,
order_code,
id_card,
goods_number,
amount,
create_time,
random)
values (
CONCAT("test", i),
i,
CONCAT("order", i),
FLOOR(RAND() * 10000000000000),
i,
ROUND(RAND() * 100, 2),
NOW(),
FLOOR(RAND() * 1000000)
);
set
i = i + 1;
end while;
end
-- 結(jié)束執(zhí)行完成后，可在此處查看：
然后，調(diào)用存儲過程：
-- 調(diào)用儲存過程
CALL insert_data();執(zhí)行完后，數(shù)據(jù)信息如下：
這里，可以通過存儲過程的執(zhí)行時間，看看慢SQL的定位方式
三、慢SQL的發(fā)現(xiàn)
1、執(zhí)行show variables like '%general%'; 命令，查看日志功能是否開啟
主要使用命令如下：
- set global general_log=on; 這個語句將全局變量 general_log 的值設(shè)置為 "on"，表示啟用了全局查詢?nèi)罩尽⒂煤?，MySQL服務(wù)器將記錄所有的查詢語句到查詢?nèi)罩疚募?，包?SELECT、INSERT、UPDATE、DELETE 等操作。
- set global general_log=off; 這個語句將全局變量 general_log 的值設(shè)置為 "off"，表示禁用了全局查詢?nèi)罩?。禁用后，MySQL服務(wù)器將停止記錄查詢?nèi)罩?，不再將查詢語句寫入查詢?nèi)罩疚募?/span>
通過修改全局變量 general_log 的值，可以控制全局查詢?nèi)罩镜拈_啟和關(guān)閉。
2、查看當(dāng)前慢查詢?nèi)罩镜拈_啟情況
-- 查看當(dāng)前慢查詢?nèi)罩镜拈_啟情況
show variables like '%quer%';執(zhí)行信息如下：
設(shè)置信息解析：
- binlog_rows_query_log_events：該屬性設(shè)置為"OFF"，表示不記錄二進制日志中的查詢事件。
- ft_query_expansion_limit：該屬性設(shè)置為20，表示在全文搜索查詢中，擴展查詢的限制為最多20個詞。
- have_query_cache：該屬性設(shè)置為"NO"，表示當(dāng)前MySQL服務(wù)器未啟用查詢緩存功能。
- log_queries_not_using_indexes：該屬性設(shè)置為"OFF"，表示不記錄未使用索引的查詢語句。
- log_throttle_queries_not_using_indexes：該屬性設(shè)置為0，表示未使用索引的查詢語句不會被限制。
- long_query_time：該屬性設(shè)置為10.000000，表示執(zhí)行時間超過10秒的查詢將被認為是慢查詢。
- query_alloc_block_size：該屬性設(shè)置為8192，表示分配給查詢內(nèi)存塊的大小為8KB。
- query_prealloc_size：該屬性設(shè)置為8192，表示預(yù)分配給查詢的內(nèi)存大小為8KB。
- slow_query_log：該屬性設(shè)置為"ON"，表示慢查詢?nèi)罩竟δ芤褑⒂谩?/span>
- slow_query_log_file：該屬性設(shè)置為"DESKTOP-0R9IERO-slow.log"，表示慢查詢?nèi)罩疚募拿Q為"DESKTOP-0R9IERO-slow.log"。
我們通過解析，還是默認設(shè)置慢日志閥值為10秒 （設(shè)置命令：set global long_query_time = 10）
通過slow_query_log_file的值，我們找到慢SQL文件DESKTOP-0R9IERO-slow.log，我這里在本地C盤：C:\ProgramData\MySQL\MySQL Server 8.0\Data 目錄下：
慢SQL日志信息，查看存儲過程的執(zhí)行情況：
四、索引的優(yōu)化
1、EXPLAIN 是一個在 MySQL 中用于查詢執(zhí)行計劃的命令。它可以幫助您了解查詢語句的執(zhí)行方式、優(yōu)化和性能。
EXPLAIN SELECT * FROM table_name WHERE column = 'value';以下是 EXPLAIN 命令的一些關(guān)鍵信息：
-
id：表示查詢的標識符，如果查詢包含子查詢，每個子查詢都有一個唯一的標識符。 -
select_type：表示查詢的類型，常見的類型包括 SIMPLE（簡單查詢）、PRIMARY（主查詢）、SUBQUERY（子查詢）等。 -
table：表示查詢涉及的表名。 -
type：表示訪問表的方式，常見的類型有 ALL（全表掃描）、INDEX（索引掃描）、RANGE（范圍掃描）等。 -
possible_keys：表示可能應(yīng)用到查詢中的索引。 -
key：表示實際使用的索引。 -
key_len：表示索引字段的長度。 -
ref：表示與索引比較的列或常數(shù)。 -
rows：表示估計需要掃描的行數(shù)。 -
Extra：提供其他額外的信息，如是否使用了臨時表、排序方式等。
通過分析 EXPLAIN 的輸出，您可以獲得以下信息：
- 查詢是否有效利用了索引。
- 查詢的執(zhí)行順序和方式。
- 哪些表被訪問以及訪問方式。
- 估計掃描的行數(shù)和數(shù)據(jù)訪問的成本。
這些信息可以幫助您優(yōu)化查詢語句、調(diào)整索引和改進性能。
2、常見索引優(yōu)化1：條件字段函數(shù)操作
當(dāng)前表中已創(chuàng)建索引：
函數(shù)作用在條件列上，索引失效：
修改后：
2、常見索引優(yōu)化2：隱式類型轉(zhuǎn)換
當(dāng)前id_card字段在數(shù)據(jù)庫中是 varchar 類型，直接以數(shù)值類型查詢，導(dǎo)致索引失效：
修改后，如下：
-- 使用如下寫法：
explain select * from tbl_test where id_card = '2674841548013'
-- 或者：
-- CAST(267484154801 AS CHAR) 將數(shù)值類型的 2674841548013 轉(zhuǎn)換為與 id_card 列的數(shù)據(jù)類型（ varchar ）匹配的字符類型。
-- 通過這樣做，確保了 id_card 和值之間的比較使用匹配的數(shù)據(jù)類型，使索引能夠有效使用。
explain select * from tbl_test where id_card = CAST(2674841548013 AS CHAR)
失效的原因跟案例一類型，數(shù)據(jù)類型隱式轉(zhuǎn)換，對于優(yōu)化器來說，這個語句相當(dāng)于：
select * from tbl_test where CAST(id_card AS signed int) = 66778899;這樣，在WHERE 子句中使用函數(shù)、表達式或算術(shù)，索引列錯誤使用，導(dǎo)致索引失效。
3、常見索引優(yōu)化3：隱式字符編碼轉(zhuǎn)換
當(dāng)使用不同的字符集進行隱式編碼轉(zhuǎn)換時，可能會導(dǎo)致索引失效。這是因為MySQL在進行索引查找時，會使用字符集的排序規(guī)則進行比較。如果字符集不同，排序規(guī)則也會不同，從而導(dǎo)致索引無法正確使用。
例如下面的例子，因字符集utf8mb4和utf8隱式字符編碼轉(zhuǎn)換而導(dǎo)致索引失效的情況：
假設(shè)有一個表my_table，其中有一個名為column的列，該列使用utf8mb4字符集，并且創(chuàng)建了索引。
CREATE TABLE my_table (
id INT PRIMARY KEY,
column VARCHAR(255) CHARACTER SET utf8mb4
) ENGINE=InnoDB;
CREATE INDEX idx_column ON my_table (column);
然后，我們向表中插入一些數(shù)據(jù)：
INSERT INTO my_table (id, column) VALUES (1, 'abc');
INSERT INTO my_table (id, column) VALUES (2, 'def');
現(xiàn)在，如果我們使用不同字符集的查詢語句進行隱式編碼轉(zhuǎn)換，可能會導(dǎo)致索引失效。例如，以下查詢使用了utf8字符集的字符串進行查詢，這與表中的utf8mb4字符集不同：
SELECT * FROM my_table WHERE column = 'ghi';
在這個情況下，由于字符集不同，MySQL無法正確使用索引，從而進行全表掃描。這會導(dǎo)致查詢性能下降，因為全表掃描比使用索引更耗時。要避免這種情況，可以確保查詢語句中的字符集與表中的字符集一致，或者顯式地進行字符編碼轉(zhuǎn)換。
五、MySQL索引失效原因的大致匯總文章來源：http://www.zghlxwxcb.cn/news/detail-771088.html
1、前導(dǎo)模糊查詢不能利用索引，比如查詢語句是LIKE '%XX'或LIKE '%XX%'，而'A%'就可以正常使用索引。
2、如果MySQL估計使用全表掃描要比使用索引快，則不使用索引。
3、OR前后存在非索引的列，索引失效。如果想使用OR，又想讓索引生效，只能將OR條件中的每個列都加上索引。
4、普通索引的不等于不會走索引，如果是主鍵，則還是會走索引；如果是主鍵或索引是整數(shù)類型，則還是會走索引。
5、is null可以使用索引，is not null無法使用索引。
6、在設(shè)計表時設(shè)置NOT NULL約束最好，比如將INT類型的默認值設(shè)為0，將字符串默認值設(shè)為''。
7、如果在查詢條件中對索引列使用了任何操作（計算，函數(shù)），或者進行了類型轉(zhuǎn)換，可能會導(dǎo)致索引失效。
8、如果在復(fù)合索引中，查詢條件沒有遵循最左匹配原則，那么索引可能也不會生效。
9、如果MySQL優(yōu)化器認為全表掃描的速度快于使用索引，它可能會選擇全表掃描而不使用索引。文章來源地址http://www.zghlxwxcb.cn/news/detail-771088.html
到了這里，關(guān)于MySQL強制使用索引的兩種方式及優(yōu)化索引，使用MySQL存儲過程創(chuàng)建測試數(shù)據(jù)。的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
