datetime 索引_MySQL執行sql時一個表隻能用到一個索引嗎？

目錄

• #1709 - Index column size too large. The maximum column size is 767 bytes.是什麼原因導緻的，如何處理？
• 舉幾個你遇到的MySQL中的隐式轉換案例
• 你覺得理想的MySQL備份政策應該是怎樣的，實際上你真正的備份政策又是怎樣的，為什麼會有不同，出于什麼原因呢？
• MySQL執行sql時一個表隻能用到一個索引嗎？
• MySQL的字首索引有什麼特點？使用上有什麼使用場景和限制麼?

一、#1709 - Index column size too large. The maximum column size is 767 bytes.是什麼原因導緻的，如何處理？ （一）原因

1、對于行格式為REDUNDANT和COMPACT的InnoDB表來說，索引的最大長度為767位元組

2、行格式為DYNAMIC和COMPRESSED格式的InnoDB表最大索引長度允許達到3072位元組（注：8.0版本之前，要求

innodb_file_format=Barracuda

，且

innodb_large_prefix=1

。 且8.0以後該參數已廢棄）

3、注意，即便設定

innodb_large_prefix=1

，但若行格式是REDUNDANT和COMPACT時調整無效，且5.7.6版本以前該參數預設是關閉的，5.7.7版本後預設開啟。8.0後該參數也已廢棄

4、索引長度計算規則：

4.1 一般地，key_len等于索引列類型位元組長度，例如tinyint類型為1位元組，int類型為4位元組，bigint為8位元組

4.2 如果是字元串類型，還需要同時考慮字元集因素(latin1為1位元組/gbk為2位元組/utf8為3位元組/utf8mb4為4位元組)，例如：CHAR(30) UTF8則key_len至少是90位元組

4.3 如果是日期時間型還需要考慮精度值（5.6.4版本以後），如datetime為5位元組+精度值，timestamp為4位元組+精度值

4.4 若該列類型定義時允許NULL，還需要再加1位元組

4.5 若該列類型為變長類型，例如 VARCHAR（TEXTBLOB不允許整列建立索引，如果建立部分索引也被視為動态列類型），還需要再加2位元組

（二）處理方法

1、降低索引長度，采用部分索引而不是整列索引，如：

create index idx_xx on t(code(30))

2、修改

innodb_file_format

為Barracuda，同時修改表的row format為DYNAMIC，使得最大索引長度增加到3072位元組，如：

alter table t row_format=dynamic

二、舉幾個你遇到的MySQL中的隐式轉換案例

1.表中定義字元類型，where條件傳入數字類型，如：

code varchar(10), SQL: select * from t where code=1

2.關聯字元類型不一緻(驅動表int傳入被驅動表字元串中關聯)，如：

t1.a int,t2.a varchar(10), SQL: select * from t1 left join t2 on t1.a=t2.a

3.關聯兩表字元集類型不一緻(驅動表大字元集傳入被驅動表小字元集關聯)，如：

t1 utf8mb4, t2 utf8, SQL: select * from t1 left join t2 on t1.a=t2.a

4.關聯兩表字元校對規則不一緻，如：

t1表coll為

utf8_general_ci

,t2表為

ucs2_general_ci,SQL: select * from t1,t2 where t1.a=t2.a

5.字元串被截斷(嚴格來說不算隐式轉換，但容易被忽略)，如SQL:

where a='1aaa'

這個條件也能查到a=1的記錄

判斷出現類型轉換方法：

1、在指令行檢視執行計劃，通過show warnings可以觀察到

due to type or collation conversion on field

2、若發生類型轉換，一般來說無法使用索引，執行計劃的key那列通常是NULL

三、你覺得理想的MySQL備份政策應該是怎樣的，實際上你真正的備份政策又是怎樣的，為什麼會有不同，出于什麼原因呢？ （一）備份目的

備份的主要目的是確定資料安全。在資料檔案出現損壞或者誤操作時用于資料恢複

（二）全備政策

1、目前備份政策對于資料量小的庫（資料量在50G以下），可以采用邏輯全備的方式(例如mysqldump或mydumper)

2、對于資料量大（資料量在50G以上）的庫，一般采用xtrabackup進行實體備份

3、無論實體備份還是邏輯備份，盡可能在專屬從庫上執行，盡可能不要在主庫上進行備份

4、執行備份前，最好先确認資料庫中目前沒有未結束的大事務，或者未結束的大select

5、除了上述備份外，同時也要備份binlog檔案

6、建議在業務低峰期(例如淩晨)每天做全量備份

7、備份檔案同時最好放在遠端備份伺服器上，不要隻放在本地

（三）增量備份

1、xtrabackup支援在全備基礎上的增量備份

2、或者通過binlog備份做增量

（四）其他政策

1、如有條件，可以配置延遲從庫，在出現資料誤删需要快速恢複的情況下，那麼可以從延遲從庫立刻恢複資料

2、對于超大的庫（如日志庫），可以直接用主從的方式代替備份，master出問題，立即将業務切換到slave上

（五）備份檔案有效性測試

1、除了備份政策以外，還需要驗證備份的有效性，定期檢驗備份集

2、備份集檢驗包含備份有效性和備份完整性監測，通過是否能夠恢複備份集并抽樣通路表資料來檢驗備份的有效性，通過資料探針的方式來檢驗備份完整性（例如：備份前往某個表中插入特定标記資料，恢複後檢查該資料是否存在）

四、MySQL執行sql時一個表隻能用到一個索引嗎？

答：并不是，以下幾種情況一個表可能會使用多個索引

（一）index merge

在執行計劃的type列顯示index_merge，key顯示了使用的索引，key_len包含了這些索引清單的最長部分，根據Extra列的顯示可以将index merge分為三類

1、

Using intersect(...)

，對應的算法為Index Merge Intersection，如：c1、c2是兩個單列索引，

SELECT * FROM t1 WHERE c1 = 1 AND c2 = 2

;

2、

Using union(...)，Index Merge Union

，如：c1、c2、c3是三個單列索引，

SELECT * FROM t1 WHERE c1 = 1 OR c2 = 2 OR c3 = 3

;

3、

Using sort_union(...)，Index Merge Sort-Union

，如：c1、c2是兩個單列索引，

SELECT * FROM t1 WHERE c1 < 10 OR c2 < 20

;

（二）表自關聯

表自關聯同樣可以用到多個索引，如：

c1、c2是兩個單列索引，SQL如下：

select * from t as t1 join t as t2 on t1.c1=t2.c2 where t1.c2=1

;

補充：

有很多同學答了ICP，ICP是指将單個索引内無法參與index key的索引條件下推到引擎層進行過濾（也就是index filter階段下推到引擎層去執行），并不是将多個索引下推到引擎層過濾，需要注意差別

五、MySQL的字首索引有什麼特點？使用上有什麼使用場景和限制麼? （一）特點：

1、隻能對列的部分長度建立索引，減少索引的大小即key_len，達到節省空間、提高查詢效率的目的

2、可以通過length函數計算前n個字元長度的記錄條數占比來決定字首索引的長度，一般來說能夠覆寫80%-90%即可

例如，一個表總數是100萬，我們統計字元串字首長度小于15個字元的數量是80萬，那麼字首索引長度可能15個字元長度基本就夠用了

select count(*) from t where length(c1)<=15

;

(注意，這個SQL效率很低，不要線上執行)

（二）不足、缺點：

1、字首索引無法使用覆寫索引的特性，是以必須回表擷取資料

2、無法利用字首索引完成分組/排序

（三）使用場景：

1、适合亂序數字或前n個字元可選性高的情況或者是text/blob這種無法整列建立索引的大字段

2、不适合于前n個字元重複率很高或者需要利用覆寫索引優化的場景

公衆号：知數堂，更多MySQL幹貨知識，關注公衆号擷取。

原文連結：https://zhishutang.com/3YF

推薦閱讀：https://zhishutang.com/xdI