chihiro-notes

千尋簡筆記

v0.1 內(nèi)測版

📔 筆記介紹

大家好，千尋簡筆記是一套全部開源的企業(yè)開發(fā)問題記錄，毫無保留給個人及企業(yè)免費使用，我是作者星辰，筆記內(nèi)容整理并發(fā)布，內(nèi)容有誤請指出，筆記源碼已開源，前往Gitee搜索《chihiro-notes》，感謝您的閱讀和關(guān)注。

作者各大平臺直鏈： GitHub | Gitee | CSDN｜

初級篇

SQL DML 和 DDL

• 可以把 SQL 分為兩個部分：數(shù)據(jù)操作語言 (DML) 和 數(shù)據(jù)定義語言 (DDL)。
• SQL (結(jié)構(gòu)化查詢語言)是用于執(zhí)行查詢的語法。
• 但是 SQL 語言也包含用于更新、插入和刪除記錄的語法。

應(yīng)用題

表操作

• 創(chuàng)建表

創(chuàng)建一個表

DROP TABLE IF EXISTS key_value;
CREATE TABLE key_value(_key VARCHAR(255)    COMMENT '鍵' ,_value VARCHAR(255)    COMMENT '值' 
)  COMMENT = '鍵值對';

增加表字段

ALTER TABLE

給表條件一個字段

ALTER TABLE 表名 ADD `字段名` VARCHAR ( 128 )   COMMENT '備注';

ALTER TABLE t_user ADD `user_name` VARCHAR ( 128 )   COMMENT '用戶名稱';

增刪改

查詢和更新指令構(gòu)成了 SQL 的 DML 部分：

SELECT - 從數(shù)據(jù)庫表中獲取數(shù)據(jù)
UPDATE - 更新數(shù)據(jù)庫表中的數(shù)據(jù)
DELETE - 從數(shù)據(jù)庫表中刪除數(shù)據(jù)
INSERT INTO - 向數(shù)據(jù)庫表中插入數(shù)據(jù)

插入一條數(shù)據(jù)

插入一條數(shù)據(jù)

INSERT INTO 語句

INSERT INTO 語句用于向表格中插入新的行。

//語法：
INSERT INTO 表名稱 VALUES (值1, 值2,....)
//我們也可以指定所要插入數(shù)據(jù)的列：
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

INSERT INTO key_value VALUES ("1","2222");
INSERT INTO key_value (_key,_value) VALUES ("2","鍵值對");

刪除一條數(shù)據(jù)

DELETE 語句

DELETE 語句用于刪除表中的行。

//語法:
DELETE FROM 表名稱 WHERE 列名稱 = 值

DELETE FROM key_value WHERE _key = "2";
SELECT * FROM key_value;

更新一條數(shù)據(jù)庫

Update 語句

Update 語句用于修改表中的數(shù)據(jù)。

語法：
UPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值

UPDATE key_value set _key = "我不想做主鍵" WHERE _key= "1";
SELECT * from key_value;

查詢篇

查詢所有數(shù)據(jù)

現(xiàn)在我們希望從 “Persons” 表中選取所有的列。 請使用符號 * 取代列的名稱，就像這樣：

SELECT * FROM Persons

條件查詢： user_id = 123 的數(shù)據(jù)

SELECTtu.id,tu.user_name
FROMtu.t_user AS tu 
WHEREtu.user_id = 123;

條件查詢：查詢 user_id = 123 或 456 的數(shù)據(jù)

SELECTtu.id,tu.user_name
FROMt_user AS tu 
WHEREtu.user_id = 123OR tu.user_id = 456;

---

應(yīng)用題

問：你怎么快速找出兩條相同的數(shù)據(jù)？字段為id

SELECTcid.id,cid.id ,cid.name
FROMchihiro_id  AS cid
GROUP BYcid.id  HAVING COUNT(cid.id )>1;

驗證是否正確：

SELECTcid.id,cid.id ,cid.name
FROMchihiro_id  AS cid
WHEREcid.id  = 34170
OR cid.id  = 15022
;

刪除重復(fù)的id

DELETE FROM chihiro_id
WHEREid = 317021266123 OR id = 317021266123
;

中級篇

常用條件查詢

模糊查詢

select * from chihiro_area;
SELECT * FROM `chihiro_area` WHERE 1=1 and name LIKE '%北';
SELECT name,area_code FROM chihiro_area WHERE 1=1 and area_code LIKE '11%';
select * from chihiro_area where parent_code  LIKE '1100%';
select * from chihiro_area WHERE name LIKE '北京%';

聯(lián)查

SELECT * from sys_user;
SELECT * from sys_dept;select su.dept_id,su.user_name,sd.dept_name,sd.email 
from sys_user AS su 
INNER JOIN sys_dept AS sd ON su.dept_id = sd.dept_id;

關(guān)鍵字：UNION ALL

多字段查詢

-- 用于多字段查詢
SELECTlc.id,lc.first_hearing_address AS hearingAddress
FROMt_layer_case AS lc 
WHERElc.first_hearing_address != '' 
UNION ALL
SELECTlc.id,lc.second_hearing_address AS hearingAddress
FROMt_layer_case AS lc 
WHERElc.second_hearing_address != '' 
UNION ALL
SELECTlc.id,lc.executive_court AS hearingAddress
FROMt_layer_case AS lc 
WHERElc.executive_court != ''

Union all 查詢完統(tǒng)計

select a,b,c from (select a, b, c from aaunion all select a1 as a, b1 as b, c1 as c from bb
) a group by c

關(guān)鍵字：DISTINCT

-- 去重手機號
SELECT DISTINCT first_economics_officer_contact AS "economicsOfficerContact",first_economics_officer AS "economicsOfficer"
FROMt_layer_case
WHEREfirst_economics_officer_contact is not null

【Java代碼】xml 循環(huán)set數(shù)組

<if test="caseTypeSet != null">AND lc.case_type IN<foreach collection="caseTypeSet" item="item" open="(" separator="," close=")">#{item}</foreach>
</if>

關(guān)鍵字：EXISTS

實際場景：查詢表a中a.id,在表b中是否存在車輛；

AND EXISTS(SELECT tpc.types_of_property_clues FROM t_property_clues AS tpc WHERE tpc.types_of_property_clues = '車輛' AND tpc.case_id = lc.id)
-- 判斷
SELECTCOUNT(*) AS number,hearingAddress 
FROM(SELECTlc.id,lc.first_hearing_address AS hearingAddress FROMt_layer_case AS lcLEFT JOIN t_entrusted_client AS ec ON ec.id = lc.entrusted_client_idLEFT JOIN t_upcoming AS tu ON tu.case_id = lc.idWHERE1 = 1 AND lc.first_hearing_address != '' AND EXISTS(SELECT tpc.types_of_property_clues FROM t_property_clues AS tpc WHERE tpc.types_of_property_clues = '車輛' AND tpc.case_id = lc.id)UNION ALLSELECTlc.id,lc.second_hearing_address AS hearingAddress FROMt_layer_case AS lcLEFT JOIN t_entrusted_client AS ec ON ec.id = lc.entrusted_client_idLEFT JOIN t_upcoming AS tu ON tu.case_id = lc.idWHERE1 = 1 AND lc.second_hearing_address != '' AND EXISTS(SELECT tpc.types_of_property_clues FROM t_property_clues AS tpc WHERE tpc.types_of_property_clues = '車輛' AND tpc.case_id = lc.id)UNION ALLSELECTlc.id,lc.executive_court AS hearingAddress FROMt_layer_case AS lcLEFT JOIN t_entrusted_client AS ec ON ec.id = lc.entrusted_client_idLEFT JOIN t_upcoming AS tu ON tu.case_id = lc.idLEFT JOIN ( SELECT id, case_id, types_of_property_clues FROM t_property_clues WHERE types_of_property_clues = '車輛' GROUP BY case_id ) AS tpc ON tpc.case_id = lc.id WHERE1 = 1 AND lc.executive_court != '' AND EXISTS(SELECT tpc.types_of_property_clues FROM t_property_clues AS tpc WHERE tpc.types_of_property_clues = '車輛' AND tpc.case_id = lc.id)) table1 
GROUP BYhearingAddress 
ORDER BYCOUNT(*) DESC LIMIT 10

關(guān)鍵字：CASE WHEN

查詢結(jié)果等于0 就返回一1 ，其他返回0

SELECTtfm.id AS id,(CASE WHEN SUM(trs.repayment_amount_instalment * (lawyer_fee_proportion/100))-SUM(trs.repayment_amount* (lawyer_fee_proportion/100)) = 0 THEN 1 ELSE 0 END) AS fee_clear,
FROMt_financial_management AS tfm

存儲過程

利用生成假數(shù)據(jù)

創(chuàng)建存儲過程

delimiter //
create procedure batchInsert()
begindeclare num int; set num=1;while num<=1000000 doinsert into key_value(`username`,`password`) values(concat('測試用戶', num),'123456');set num=num+1;end while;
end
// 
delimiter ; #恢復(fù);表示結(jié)束

調(diào)用存儲過程

寫好了存儲過程就可以進行調(diào)用了，可以通過命令調(diào)用：

CALL batchInsert;

也可以在數(shù)據(jù)庫工具的中Functions的欄目下，找到剛剛創(chuàng)建的存儲過程直接執(zhí)行。

刪除存儲過程

drop procedure batchInsert; 

應(yīng)用題

有關(guān)時間的語句

--  改成日期的時間戳
SELECT NOW();
SELECT UNIX_TIMESTAMP(NOW());
SELECT UNIX_TIMESTAMP('2022-12-27');

x日期 - y日期 小于等于 40天

-- 	當(dāng)前時間大于開庭時間，代表已開庭 
SELECT tlc.first_hearing_time AS courtDate,CASE WHEN NOW()> tlc.first_hearing_time THEN "1" ELSE "0" END AS isOpenACourtSession
FROM t_layer_case  AS tlc
WHEREtlc.first_hearing_time IS NOT NULL
ANDABS(DATEDIFF(first_hearing_time,"2022-12-27 16:56:13" )) <=40;

計算兩個時間相差的天數(shù)

ABS(DATEDIFF(tpc.appeal_time_of_closure_and_registration,NOW())) AS "累計查封時間",

sql如何計算一個日期某個周期后的日期

--  查詢x日期，y年后的日期
SELECT DATE_ADD(NOW(),INTERVAL 3 YEAR);

select語句查詢近一周的數(shù)據(jù)

select * from table  where 
DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time);

SQL利用Case When Then多條件判斷

CASE
WHEN 條件1 THEN 結(jié)果1
WHEN 條件2 THEN 結(jié)果2
WHEN 條件3 THEN 結(jié)果3
WHEN 條件4 THEN 結(jié)果4
…
WHEN 條件N THEN 結(jié)果N
ELSE 結(jié)果X
END

Case具有兩種格式。簡單Case函數(shù)和Case搜索函數(shù)。
–簡單Case函數(shù)
CASE sex
WHEN ‘1’ THEN ‘男’
WHEN ‘2’ THEN ‘女’
ELSE ‘其他’ END
–Case搜索函數(shù)
CASE WHEN sex = ‘1’ THEN ‘男’
WHEN sex = ‘2’ THEN ‘女’
ELSE ‘其他’ END

CASE WHEN bn.endDay < 60 THEN 1WHEN bn.endDay < 30 THEN 2WHEN bn.endDay < 15 THEN 3ELSE"不提醒"END AS "level",

MySQL內(nèi)連接（INNER JOIN）

SELECTtpc.id,tpc.case_id,tpc.entrusted_client_id,tpc.types_of_property_clues,tpc.property_clue_information,CASE WHEN bn.endDay < 60 THEN 1WHEN bn.endDay < 30 THEN 2WHEN bn.endDay < 15 THEN 3ELSE"不提醒"END AS "level",tlc.defendant_name,tlc.first_case_number,tlc.second_case_number,tlc.execution_case_number
FROMt_property_clues AS tpc
INNER JOIN(SELECTtpcc.id AS id,DATE_ADD(tpcc.appeal_time_of_closure_and_registration,INTERVAL tpcc.appeal_legal_time_of_closure YEAR) AS endTime,ABS(DATEDIFF(DATE_ADD(tpcc.appeal_time_of_closure_and_registration,INTERVAL tpcc.appeal_legal_time_of_closure YEAR),NOW())) AS endDayFROMt_property_clues AS tpcc
)AS bn ON bn.id = tpc.id
LEFT JOINt_layer_case AS tlc ON tlc.id = tpc.case_id
WHERE1=1
AND ABS(DATEDIFF(bn.endTime,NOW())) < 60
;

between

between value1 and value2 (篩選出的條件中包括value1,但是不包括vaule2，也就是說

索引-理論篇

存儲方式區(qū)分

• MySQL 索引可以從存儲方式、邏輯角度和實際使用的角度來進行分類。
• 根據(jù)存儲方式的不同，MySQL 中常用的索引在物理上分為 B-樹索引和HASH索引兩類，兩種不同類型的索引各有其不同的適用范圍。

B-樹索引：BTREE

• B-樹索引又稱為 BTREE 索引，目前大部分的索引都是采用 B-樹索引來存儲的。

• B-樹索引是一個典型的數(shù)據(jù)結(jié)構(gòu)，其包含的組件主要有以下幾個。

葉子節(jié)點：包含的條目直接指向表里的數(shù)據(jù)行。葉子節(jié)點之間彼此相連，一個葉子節(jié)點有一個指向下一個葉子節(jié)點的指針。

分支節(jié)點：包含的條目指向索引里其他的分支節(jié)點或者葉子節(jié)點。

根節(jié)點：一個 B-樹索引只有一個根節(jié)點，實際上就是位于樹的最頂端的分支節(jié)點。

基于這種樹形數(shù)據(jù)結(jié)構(gòu)，表中的每一行都會在索引上有一個對應(yīng)值。因此，在表中進行數(shù)據(jù)查詢時，可以根據(jù)索引值一步一步定位到數(shù)據(jù)所在的行。

B-樹索引可以進行全鍵值、鍵值范圍和鍵值前綴查詢，也可以對查詢結(jié)果進行 ORDER BY 排序。但 B-樹索引必須遵循左邊前綴原則，要考慮以下幾點約束：

1. 查詢必須從索引的最左邊的列開始。
2. 查詢不能跳過某一索引列，必須按照從左到右的順序進行匹配。
3. 存儲引擎不能使用索引中范圍條件右邊的列。

哈希索引：Hash

• 哈希（Hash）一般翻譯為“散列”，也有直接音譯成“哈?！钡?#xff0c;就是把任意長度的輸入（又叫作預(yù)映射，pre-image）通過散列算法變換成固定長度的輸出，該輸出就是散列值。

• 哈希索引也稱為散列索引或 HASH 索引。MySQL 目前僅有 MEMORY 存儲引擎和 HEAP 存儲引擎支持這類索引。其中，MEMORY 存儲引擎可以支持 B-樹索引和 HASH 索引，且將 HASH 當(dāng)成默認(rèn)索引。

• HASH 索引不是基于樹形的數(shù)據(jù)結(jié)構(gòu)查找數(shù)據(jù)，而是根據(jù)索引列對應(yīng)的哈希值的方法獲取表的記錄行。哈希索引的最大特點是訪問速度快，但也存在下面的一些缺點：

1. MySQL 需要讀取表中索引列的值來參與散列計算，散列計算是一個比較耗時的操作。也就是說，相對于 B-樹索引來說，建立哈希索引會耗費更多的時間。
2. 不能使用 HASH 索引排序。
3. HASH 索引只支持等值比較，如“=”“IN()”或“<=>”。
4. HASH 索引不支持鍵的部分匹配，因為在計算 HASH 值的時候是通過整個索引值來計算的。

邏輯區(qū)分

根據(jù)索引的具體用途，MySQL 中的索引在邏輯上分為以下五類

• 普通索引：INDEX
• 唯一索引：UNIQUE
• 主鍵索引：PRIMARY KEY
• 空間索引：SPATIAL
• 全文索引：FULLTEXT

普通索引：INDEX

1. 普通索引是 MySQL 中最基本的索引類型，它沒有任何限制，唯一任務(wù)就是加快系統(tǒng)對數(shù)據(jù)的訪問速度。
2. 普通索引允許在定義索引的列中插入重復(fù)值和空值。
3. 創(chuàng)建普通索引時，通常使用的關(guān)鍵字是 INDEX 或 KEY。

基本語法如下：

CREATE INDEX index_id 
ON my_chihiro(id);

唯一索引：UNIQUE

1. 唯一索引與普通索引類似，不同的是唯一索引不僅用于提高性能，而且還用于數(shù)據(jù)完整性，唯一索引不允許將任何重復(fù)的值插入表中

2. 唯一索引列的值必須唯一，允許有空值。

3. 如果是組合索引，則列值的組合必須唯一。

4. 創(chuàng)建唯一索引通常使用 UNIQUE 關(guān)鍵字。

基本語法如下：

CREATE UNIQUE INDEX index_id 
ON my_chihiro(id);

主鍵索引：PRIMARY KEY

1. 主鍵索引就是專門為主鍵字段創(chuàng)建的索引，也屬于索引的一種。
2. 主鍵索引是一種特殊的唯一索引，不允許值重復(fù)或者值為空。
3. 創(chuàng)建主鍵索引通常使用 PRIMARY KEY 關(guān)鍵字。不能使用 CREATE INDEX 語句創(chuàng)建主鍵索引。

空間索引：SPATIAL

1. 空間索引是對空間數(shù)據(jù)類型的字段建立的索引，使用 SPATIAL 關(guān)鍵字進行擴展。
2. 創(chuàng)建空間索引的列必須將其聲明為 NOT NULL，空間索引只能在存儲引擎為 MyISAM 的表中創(chuàng)建。
3. 空間索引主要用于地理空間數(shù)據(jù)類型 GEOMETRY。

基本語法如下：my_chihiro 表的存儲引擎必須是 MyISAM，line 字段必須為空間數(shù)據(jù)類型，而且是非空的。

CREATE SPATIAL INDEX index_line
ON my_chihiro(line);

全文索引：FULLTEXT

1. 全文索引主要用來查找文本中的關(guān)鍵字，只能在 CHAR、VARCHAR 或 TEXT 類型的列上創(chuàng)建。在 MySQL 中只有 MyISAM 存儲引擎支持全文索引。
2. 全文索引允許在索引列中插入重復(fù)值和空值。
3. 不過對于大容量的數(shù)據(jù)表，生成全文索引非常消耗時間和硬盤空間。

基本語法如下：index_info 的存儲引擎必須是 MyISAM，info 字段必須是 CHAR、VARCHAR 和 TEXT。

CREATE FULLTEXT INDEX index_info
ON my_chihiro(info);

實際使用

在實際應(yīng)用中，索引通常分為

• 單列索引
• 復(fù)合索引/多列索引/組合索引

單列索引

1. 單列索引就是索引只包含原表的一個列。在表中的單個字段上創(chuàng)建索引，單列索引只根據(jù)該字段進行索引。
2. 單列索引可以是普通索引，也可以是唯一性索引，還可以是全文索引。只要保證該索引只對應(yīng)一個字段即可。

基本語法如下：address 字段的數(shù)據(jù)類型為 VARCHAR(20)，索引的數(shù)據(jù)類型為 CHAR(6)，查詢時可以只查詢 address 字段的前 6 個字符，而不需要全部查詢。

CREATE INDEX index_addr 
ON my_chihiro(address(6));

多列索引/復(fù)合索引

1. 組合索引也稱為復(fù)合索引或多列索引。
2. 相對于單列索引來說，組合索引是將原表的多個列共同組成一個索引。
3. 多列索引是在表的多個字段上創(chuàng)建一個索引。該索引指向創(chuàng)建時對應(yīng)的多個字段，可以通過這幾個字段進行查詢。
4. 注意只有查詢條件中使用了這些字段中第一個字段時，索引才會被使用。

基本語法如下：索引創(chuàng)建好了以后，查詢條件中必須有 name 字段才能使用索引

CREATE INDEX index_na 
ON tb_student(name,address);

? 無論是創(chuàng)建單列索引還是復(fù)合索引，都應(yīng)考慮在查詢的WHERE子句中可能經(jīng)常使用的列作為過濾條件。
? 如果僅使用一列，則應(yīng)選擇單列索引，如果在WHERE子句中經(jīng)常使用兩個或多個列作為過濾器，則復(fù)合索引將是最佳選擇。

? 一個表可以有多個單列索引，但這些索引不是組合索引。

? 一個組合索引實質(zhì)上為表的查詢提供了多個索引，以此來加快查詢速度。比如，在一個表中創(chuàng)建了一個組合索引(c1，c2，c3)，在實際查詢中，系統(tǒng)用來實際加速的索引有三個：單個索引(c1)、雙列索引(c1，c2)和多列索引(c1，c2，c3)。

刪除索引

DROP INDEX命令， 可以使用SQL DROP 命令刪除索引，刪除索引時應(yīng)小心，因為性能可能會降低或提高。

基本語法如下：

DROP INDEX index_name;

---

索引-實踐篇

增刪查

添加索引

alter table chihiro_member_info add index idx_name (name);

查看索引

SHOW INDEX FROM chihiro_member_info;

刪除索引

DROP INDEX <索引名> ON <表名>
DROP INDEX idx_name ON chihiro_member_info;

索引失效

有時候我們明明加了索引了，但是索引卻不生效。在哪些場景，索引會不生效呢？主要有以下十大經(jīng)典場景：

一、隱式的類型轉(zhuǎn)換，索引失效

我們有一個索引，字段（name）類型為varchar字符串類型，如果查詢條件傳了一個數(shù)字去，會導(dǎo)致索引失效。

EXPLAIN SELECT *	FROM chihiro_member_info WHERE name = 1;

如果給數(shù)字加上’'，也就是說，傳的是一個字符串，就正常走索引。

EXPLAIN SELECT *	FROM chihiro_member_info WHERE name = 1;

分析：為什么第一條語句未加單引號就不走索引了呢？這是因為不加單引號時，是字符串跟數(shù)字的比較，它們類型不匹配，MySQL會做隱式的類型轉(zhuǎn)換，把它們轉(zhuǎn)換為浮點數(shù)再做比較。隱式的類型轉(zhuǎn)換，索引會失效。

二、查詢條件包含or，可能導(dǎo)致索引失效

我們在來看一條sql語句，name添加了索引，但是openid沒有添加索引。我們使用or，下面的sql是不走索引的。

EXPLAIN SELECT *	FROM chihiro_member_info WHERE name = "123" or openid = "123";

分析：對于 or+沒有索引的openid這種情況，假設(shè)它走 name的索引，但是走到 openid查詢條件時，它還得全表掃描，也就是需要三步過程： 全表掃描+索引掃描+合并。如果它一開始就走全表掃描，直接一遍掃描就完事。Mysql優(yōu)化器處于效率與成本考慮，遇到 or條件，讓索引失效。

當(dāng) name和role都是索引時，使用一張表中的多個索引時，mysql會將多個索引合并在一起。

EXPLAIN SELECT * FROM chihiro_member_info WHERE name = "123" or role = "123";

注意：如果or條件的列都加了索引，**索引可能會走也可能不走，**大家可以自己試一試哈。但是平時大家使用的時候，還是要注意一下這個or，學(xué)會用explain分析。遇到不走索引的時候，考慮拆開兩條SQL。

三、like通配符可能導(dǎo)致索引失效

并不是用了 like通配符索引一定會失效，而是 like查詢是以 %開頭，才會導(dǎo)致索引失效。

EXPLAIN SELECT * FROM chihiro_member_info WHERE name LIKE '%陳';

把 %放到后面，索引還是正常走的。

EXPLAIN SELECT * FROM chihiro_member_info WHERE name LIKE '陳%';

分析：既然 like查詢以 %開頭，會導(dǎo)致索引失效。我們?nèi)绾蝺?yōu)化？

1. 使用覆蓋索。
2. 把 %放后面。

四、查詢條件不滿足聯(lián)合索引的最左匹配原則

Mysql建立聯(lián)合索引時，會遵循左前綴匹配原則，既最左優(yōu)先。如果你建立一個（a,b,c）的聯(lián)合索引，相當(dāng)于簡歷了(a)、(a,b)、(a,b,c)。

我們先添加一個聯(lián)合索引：

alter table chihiro_member_info add index idx_name_role_openid (name,role,openid);

查看表的索引：

SHOW INDEX FROM chihiro_member_info;

有一個聯(lián)合索引idx_name_role_openid，我們執(zhí)行這個SQL，查詢條件是role，索引是無效：

EXPLAIN SELECT * FROM chihiro_member_info WHERE role = 0;

在聯(lián)合索引中，查詢條件滿足最左匹配原則時，索引才正常生效。

EXPLAIN SELECT * FROM chihiro_member_info WHERE name = "劉";

五、在索引列上使用mysql的內(nèi)置函數(shù)

我們先給創(chuàng)建時間添加一個索引。

ALTER TABLE chihiro_member_info ADD INDEX idx_create_time(create_time);

雖然create_time加了索引，但是因為使用了mysql的內(nèi)置函數(shù)DATE_ADD()，導(dǎo)致直接全表掃描了。

EXPLAIN SELECT * FROM chihiro_member_info WHERE DATE_ADD(create_time,INTERVAL 1 DAY) = '2022-10-10 00:00:00';

分析：一般這種情況怎么優(yōu)化呢？可以把**內(nèi)置函數(shù)的邏輯轉(zhuǎn)移到右邊，**如下：

EXPLAIN SELECT * FROM chihiro_member_info WHERE create_time = DATE_ADD('2022-10-10 00:00:00',INTERVAL -1 DAY);

六、對索引進行列運算（如，+、-、*、/）,索引不生效

給 role字段（tinyint）添加一個索引。

-- 添加索引
ALTER TABLE chihiro_member_info ADD INDEX idex_role(role);

雖然role加了索引，但是因為它進行運算，索引直接迷路了。如圖：

EXPLAIN SELECT * FROM chihiro_member_info WHERE role+1 = 1;

分析：不可以對索引列進行運算，可以在代碼處理好，再傳參進去。

七、索引字段上使用（！= 或者 < >），索引可能失效

給 role字段（tinyint）添加一個索引。

-- 添加索引
ALTER TABLE chihiro_member_info ADD INDEX idex_role(role);

注意：我在mysql 5.7.26測試，測試結(jié)果有所不同，可以根據(jù)mysql版本去測試。

查看mysql版本

SELECT VERSION() FROM DUAL;

!=：正常走的索引。

EXPLAIN SELECT * FROM chihiro_member_info WHERE role != 2;

<>：正常走的索引。

EXPLAIN SELECT * FROM chihiro_member_info WHERE role <> 2;

分析：其實這個也是跟mySQL優(yōu)化器有關(guān)，如果優(yōu)化器覺得即使走了索引，還是需要掃描很多很多行的哈，它覺得不劃算，**不如直接不走索引。**平時我們用！= 或者< >，not in的時候，可以先使用 EXPLAIN去看看索引是否生效。

八、索引字段上使用is null， is not null，索引可能失效

給 role字段（tinyint）添加一個索引和 name字段（varchar）添加索引。

-- 添加索引
ALTER TABLE chihiro_member_info ADD INDEX idex_role(role);
ALTER TABLE chihiro_member_info ADD INDEX idex_name(name);

單個字段 role字段加上索引，查詢 role 為空的語句，會走索引：

EXPLAIN SELECT * FROM chihiro_member_info WHERE role is not null;

兩字字段用 or鏈接起來，索引就失效了。

分析：很多時候，也是因為數(shù)據(jù)量問題，導(dǎo)致了MySQL優(yōu)化器放棄走索引。同時，平時我們用explain分析SQL的時候，如果type=range，需要注意一下，因為這個可能因為數(shù)據(jù)量問題，導(dǎo)致索引無效。

九、左右連接，關(guān)聯(lián)的字段編碼格式不一樣

新建兩個表，一個user，一個user_job：

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,`age` int(11) NOT NULL,PRIMARY KEY (`id`),KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;CREATE TABLE `user_job` (`id` int(11) NOT NULL,`userId` int(11) NOT NULL,`job` varchar(255) DEFAULT NULL,`name` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

user表的name字段編碼是utf8mb4，而user_job表的name字段編碼為utf8。

執(zhí)行左外連接查詢，user_job表還是走全表掃描。

如果把它們的name字段改為編碼一致，相同的SQL，還是會走索引。

分析：所以大家在做表關(guān)聯(lián)時，注意一下關(guān)聯(lián)字段的編碼問題。

十、優(yōu)化器選錯了索引

MySQL 中一張表是可以支持多個索引的。你寫SQL語句的時候，沒有主動指定使用哪個索引的話，用哪個索引是由MySQL來確定的。

我們?nèi)粘ｉ_發(fā)中，不斷地刪除歷史數(shù)據(jù)和新增數(shù)據(jù)的場景，有可能會導(dǎo)致MySQL選錯索引。那么有哪些解決方案呢？

• 使用force index 強行選擇某個索引；
• 修改你的SQl，引導(dǎo)它使用我們期望的索引；
• 優(yōu)化你的業(yè)務(wù)邏輯；
• 優(yōu)化你的索引，新建一個更合適的索引，或者刪除誤用的索引。

3.3 索引速度對比

測試數(shù)據(jù)量量400萬，字段包含：id、username、password

-- 數(shù)據(jù)量量400萬，字段包含：id、username、password-- 沒有索引下查詢
SELECT * FROM key_value;select * from key_value WHERE username = '測試用戶388888'
-- > OK
-- > 時間: 1.496sselect * from key_value WHERE username = '測試用戶388888'
-- > OK
-- > 時間: 1.503sselect * from key_value WHERE username = '測試用戶388888'
-- > OK
-- > 時間: 1.475s-- 創(chuàng)建索引后：
SELECT * from key_value WHERE username = '測試用戶388888';SELECT * from key_value WHERE username = '測試用戶388888'
-- > OK
-- > 時間: 0.005sSELECT * from key_value WHERE username = '測試用戶3588828';
-- > OK
-- > 時間: 0.005s-- 測試查找主鍵id 
-- 主鍵也是有索引的是，所以非?？?SELECT * from key_value WHERE id = 123333;
-- > OK
-- > 時間: 0.004s