問題發現

我認為一條很簡單的 SQL 然后跑了很久，明明我已經都建立相應的索引，邏輯也不需要優化。

SELECTa.custid,b.score,b.xcreditscore,b.lrscore
FROM(
SELECTDISTINCTcustid
FROMsync.`credit_apply`
WHERESUBSTR(createtime,1,10)>='2019-12-15'
ANDrejectrule='xxxx'
)a
LEFTJOIN(
SELECT*
FROMsync.`credit_creditchannel`
)b
ONa.custid=b.custid;

查看索引狀態：credit_apply表

mysql>showindexfromsync.`credit_apply`;

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|credit_apply|0|PRIMARY|1|applyId|A|1468496|NULL|NULL||BTREE|||
|credit_apply|1|index2|1|custId|A|666338|NULL|NULL||BTREE|||
|credit_apply|1|index2|2|createTime|A|1518231|NULL|NULL||BTREE|||
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

或者

CREATETABLE`credit_apply`(
`applyId`bigint(20)NOTNULLAUTO_INCREMENT,
`custId`varchar(128)COLLATEutf8mb4_unicode_ciNOTNULL,
`ruleVersion`int(11)NOTNULLDEFAULT'1',
`rejectRule`varchar(128)COLLATEutf8mb4_unicode_ciDEFAULT'DP0000',
`status`tinyint(4)NOTNULLDEFAULT'0',
`extra`textCOLLATEutf8mb4_unicode_ci,
`createTime`timestampNOTNULLDEFAULTCURRENT_TIMESTAMP,
`updateTime`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
`mobile`varchar(128)COLLATEutf8mb4_unicode_ciDEFAULT'',
PRIMARYKEY(`applyId`)USINGBTREE,
KEY`index2`(`custId`,`createTime`)
)ENGINE=InnoDBAUTO_INCREMENT=1567035DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ci

sync.`credit_creditchannel`表

mysql>showindexfromsync.`credit_creditchannel`;
+----------------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|
+----------------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|credit_creditchannel|0|PRIMARY|1|recId|A|450671|NULL|NULL||BTREE|||
|credit_creditchannel|1|nationalId_custid|1|nationalId|A|450770|NULL|NULL||BTREE|||
|credit_creditchannel|1|nationalId_custid|2|custId|A|450770|NULL|NULL|YES|BTREE|||
|credit_creditchannel|1|credit_creditchannel_custId|1|custId|A|450770|10|NULL|YES|BTREE|||
+----------------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

或者

CREATETABLE`credit_creditchannel`(
`recId`bigint(20)NOTNULLAUTO_INCREMENT,
`nationalId`varchar(128)NOTNULLDEFAULT'',
`identityType`varchar(3)NOTNULLDEFAULT'',
`brief`mediumtext,
`score`decimal(10,4)NOTNULLDEFAULT'0.0000',
`npaCode`varchar(128)NOTNULLDEFAULT'',
`basic`mediumtext,
`createTime`timestampNOTNULLDEFAULTCURRENT_TIMESTAMP,
`updateTime`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
`request`mediumtext,
`custId`varchar(128)DEFAULT'',
`xcreditScore`decimal(10,4)DEFAULT'0.0000',
`queryTime`varchar(24)DEFAULT'',
`lrScore`decimal(10,4)DEFAULT'0.0000',
PRIMARYKEY(`recId`)USINGBTREE,
KEY`nationalId_custid`(`nationalId`,`custId`),
KEY`credit_creditchannel_custId`(`custId`(10))
)ENGINE=InnoDBAUTO_INCREMENT=586557DEFAULTCHARSET=utf8

我們都可以看到相應的索引。以現在簡單的sql邏輯理論上走custid這個索引就好了

解釋函數explain

mysql>explainSELECTa.custid,b.score,b.xcreditscore,b.lrscoreFROM(
SELECTDISTINCTcustidFROMsync.`credit_apply`WHERESUBSTR(createtime,1,10)>='2019-12-15'ANDrejectrule='xxx')a
LEFTJOIN
(select*fromsync.`credit_creditchannel`)b
ONa.custid=b.custid;
+----+-------------+----------------------+------------+-------+---------------+--------+---------+------+---------+----------+----------------------------------------------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+----------------------+------------+-------+---------------+--------+---------+------+---------+----------+----------------------------------------------------+
|1|PRIMARY||NULL|ALL|NULL|NULL|NULL|NULL|158107|100.00|NULL|
|1|PRIMARY|credit_creditchannel|NULL|ALL|NULL|NULL|NULL|NULL|450770|100.00|Usingwhere;Usingjoinbuffer(BlockNestedLoop)|
|2|DERIVED|credit_apply|NULL|index|index2|index2|518|NULL|1581075|10.00|Usingwhere|
+----+-------------+----------------------+------------+-------+---------------+--------+---------+------+---------+----------+----------------------------------------------------+
3rowsinset(0.06sec)

如何去看我們的SQL是否走索引？我們只需要注意一個最重要的type 的信息很明顯的提現是否用到索引：

type結果type結果值從好到壞依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般來說，得保證查詢至少達到range級別，最好能達到ref，否則就可能會出現性能問題。possible_keys：sql所用到的索引key：顯示MySQL實際決定使用的鍵（索引）。如果沒有選擇索引，鍵是NULLrows: 顯示MySQL認為它執行查詢時必須檢查的行數。

分析：我們的credit_creditchannel是ALL，而possible_keys是NULL索引在查詢該表的時候并沒有用到索引怪不得這么慢！！！！！！！！！

分析和搜索解決辦法

換著法的改sql也沒用；換著群問大神也沒用；各種搜索引擎搜才總算有點思路。**索引用不上的原因可能是字符集和排序規則不相同。于是看了了兩張表的字符集和兩張表這個字段的字符集以及排序規則:

**修改數據庫和表的字符集

alterdatabasesyncdefaultcharactersetutf8mb4;//修改數據庫的字符集
altertablesync.credit_creditchanneldefaultcharactersetutf8mb4;//修改表的字符集

****修改表排序規則

altertablesync.`credit_creditchannel`converttocharactersetutf8mb4COLLATEutf8mb4_unicode_ci;

由于數據庫中的數據表和表字段的字符集和排序規則不統一，批量修改腳本如下：1. 修改指定數據庫中所有varchar類型的表字段的字符集為ut8mb4，并將排序規則修改為utf8_unicode_ci

SELECTCONCAT('ALTERTABLE`',table_name,'`MODIFY`',column_name,'`',DATA_TYPE,'(',CHARACTER_MAXIMUM_LENGTH,')CHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci',CASE
WHENIS_NULLABLE='NO'THEN'NOTNULL'
ELSE''
END,';')
FROMinformation_schema.COLUMNS
WHERE(TABLE_SCHEMA='databaseName'
ANDDATA_TYPE='varchar'
AND(CHARACTER_SET_NAME!='utf8mb4'
ORCOLLATION_NAME!='utf8mb4_unicode_ci'));

**2.修改指定數據庫中所有數據表的字符集為UTF8，并將排序規則修改為utf8_general_ci**

SELECTCONCAT('ALTERTABLE',table_name,'CONVERTTOCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;')
FROMinformation_schema.TABLES
WHERETABLE_SCHEMA='sync_rs'

explain 查看是否用到了索引

mysql>explainSELECTa.custid,b.score,b.xcreditscore,b.lrscoreFROM(
SELECTDISTINCTcustidFROMsync.`credit_apply`WHERESUBSTR(createtime,1,10)>='2019-12-15'ANDrejectrule='xxx')a
LEFTJOIN
(select*fromsync.`credit_creditchannel`)b
ONa.custid=b.custid;
+----+-------------+----------------------+------------+-------+-----------------------------+-----------------------------+---------+----------+---------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+----------------------+------------+-------+-----------------------------+-----------------------------+---------+----------+---------+----------+-------------+
|1|PRIMARY||NULL|ALL|NULL|NULL|NULL|NULL|146864|100.00|NULL|
|1|PRIMARY|credit_creditchannel|NULL|ref|credit_creditchannel_custId|credit_creditchannel_custId|43|a.custid|1|100.00|Usingwhere|
|2|DERIVED|credit_apply|NULL|index|index2|index2|518|NULL|1468644|10.00|Usingwhere|
+----+-------------+----------------------+------------+-------+-----------------------------+-----------------------------+---------+----------+---------+----------+-------------+

就是這樣！！！！

補充大全：

可以看到結果中包含10列信息，分別為

id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

對應的簡單描述如下：

• id: select查詢的序列號，包含一組數字，表示查詢中執行select子句或操作表的順序===id如果相同，可以認為是一組，從上往下順序執行；在所有組中，id值越大，優先級越高，越先執行

• select_type: 表示查詢的類型。用于區別普通查詢、聯合查詢、子查詢等的復雜查詢。

• table: 輸出結果集的表 顯示這一步所訪問數據庫中表名稱（顯示這一行的數據是關于哪張表的），有時不是真實的表名字，可能是簡稱，例如上面的e，d，也可能是第幾步執行的結果的簡稱

• partitions:匹配的分區

• type:對表訪問方式，表示MySQL在表中找到所需行的方式，又稱“訪問類型”。

• possible_keys:表示查詢時，可能使用的索引

• key:表示實際使用的索引

• key_len:索引字段的長度

• ref:列與索引的比較

• rows:掃描出的行數(估算的行數)

• filtered:按表條件過濾的行百分比

• Extra:執行情況的描述和說明

挑選一些重要信息詳細說明：

• select_type

  • SIMPLE 簡單的select查詢，查詢中不包含子查詢或者UNION

  • PRIMARY 查詢中若包含任何復雜的子部分，最外層查詢則被標記為PRIMARY

  • SUBQUERY 在SELECT或WHERE列表中包含了子查詢

  • DERIVED 在FROM列表中包含的子查詢被標記為DERIVED（衍生），MySQL會遞歸執行這些子查詢，把結果放在臨時表中

  • UNION 若第二個SELECT出現在UNION之后，則被標記為UNION：若UNION包含在FROM子句的子查詢中，外層SELECT將被標記為：DERIVED

  • UNION RESULT 從UNION表獲取結果的SELECT

• type

  • mysql找到數據行的方式，效率排名

  • NULL > system > const > eq_ref > ref > range > index > all

***一般來說，得保證查詢至少達到range級別，最好能達到ref。

1. system 表只有一行記錄（等于系統表），這是const類型的特列，平時不會出現，這個也可以忽略不計

2. const 通過索引一次就找到了，const用于比較primary key 和 unique key，因為只匹配一行數據，所以很快。如果將主鍵置于where列表中，mysql就能將該查詢轉換為一個常量

3. eq_ref 唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵索引和唯一索引 區別于const eq_ref用于聯表查詢的情況

4. ref 非唯一索引掃描，返回匹配某個單獨值的所有行，本質上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而，他可能會找到多個符合條件的行，所以他應該屬于查找和掃描的混合體

5. range 只檢索給定范圍的行，使用一個索引來選擇行，一般是在where中出現between、<、>、in等查詢，范圍掃描好于全表掃描，因為他只需要開始于索引的某一點，而結束于另一點，不用掃描全部索引

6. index Full Index Scan，Index與All區別為index類型只遍歷索引樹。通常比All快，因為索引文件通常比數據文件小。也就是說，雖然all和index都是讀全表，但是index是從索引中讀取的，而all是從硬盤讀取的

7. ALL Full Table Scan,將遍歷全表以找到匹配的行

• possible_keys

指出mysql能使用哪個索引在表中找到記錄，查詢涉及到的字段若存在索引，則該索引被列出，但不一定被查詢使用（該查詢可以利用的索引，如果沒有任何索引顯示null）
實際使用的索引，如果為NULL，則沒有使用索引。（可能原因包括沒有建立索引或索引失效）
查詢中若使用了覆蓋索引（select 后要查詢的字段剛好和創建的索引字段完全相同），則該索引僅出現在key列表中 possible_keys為null

• key

key列顯示mysql實際決定使用的索引，必然包含在possible_keys中。如果沒有選擇索引，鍵是NULL。想要強制使用或者忽視possible_keys列中的索引，在查詢時指定FORCE INDEX、USE INDEX或者IGNORE index

• key_len

表示索引中使用的字節數，可通過該列計算查詢中使用的索引的長度，在不損失精確性的情況下，長度越短越好。key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的。

• ref

顯示索引的那一列被使用了，如果可能的話，最好是一個常數。哪些列或常量被用于查找索引列上的值。

• rows

根據表統計信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數，也就是說，用的越少越好

• extra

包含不適合在其他列中顯式但十分重要的額外信息

• Using Index:表示相應的select操作中使用了覆蓋索引（Covering Index），避免訪問了表的數據行，效率不錯。如果同時出現using where，表明索引被用來執行索引鍵值的查找；如果沒有同時出現using where，表明索引用來讀取數據而非執行查找動作。

• Using where:不用讀取表中所有信息，僅通過索引就可以獲取所需數據，這發生在對表的全部的請求列都是同一個索引的部分的時候，表示mysql服務器將在存儲引擎檢索行后再進行過濾

• Using temporary：表示MySQL需要使用臨時表來存儲結果集，常見于排序和分組查詢，常見 group by ; order by

• Using filesort：當Query中包含 order by 操作，而且無法利用索引完成的排序操作稱為“文件排序”

• Using join buffer：表明使用了連接緩存,比如說在查詢的時候，多表join的次數非常多，那么將配置文件中的緩沖區的join buffer調大一些。

• Impossible where：where子句的值總是false，不能用來獲取任何元組

• Select tables optimized away：這個值意味著僅通過使用索引，優化器可能僅從聚合函數結果中返回一行

• No tables used：Query語句中使用from dual 或不含任何from子句

以上兩種信息表示mysql無法使用索引

1. using filesort ：表示mysql會對結果使用一個外部索引排序，而不是從表里按索引次序讀到相關內容，可能在內存或磁盤上排序。mysql中無法利用索引完成的操作稱為文件排序

2. using temporary: 使用了用臨時表保存中間結果，MySQL在對查詢結果排序時使用臨時表。常見于排序order by和分組查詢group by。