MySQL索引優化知識點有哪些

2024-07-24 12:32:47

字體：大中小

來源：轉載

供稿：網友

　　這篇文章主要介紹“MySQL索引優化知識點有哪些”，在日常操作中，相信很多人在MySQL索引優化知識點有哪些問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL索引優化知識點有哪些”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

　　MySQL索引優化知識點有哪些

　　一、索引的分類

　　索引可以大大提高MySQL的檢索速度。索引就像書中的目錄一樣為了讓我們更快的尋找到自己想要的數據，下面是MySQL常用的索引簡介。

　　MySQL索引優化知識點有哪些

　　1.1、普通索引、主鍵索引和唯一索引

　　(1) 普通索引

　　這是最基本的索引，它沒有任何限制。

　　如何使用？

　　//方式1

　　ALTER TABLE table_name ADD INDEX index_name ( column )

　　比如：ALTER TABLE users ADD INDEX index_users( id )

　　//方式2

　　CREATE INDEX index_name ON table_name (column_name)

　　比如：CREATE INDEX index_users ON users (id)

　　(2) 唯一索引

　　與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須一。

　　如何使用？

　　//方式1

　　ALTER TABLE table_name ADD UNIQUE [indexName] (column)

　　比如：ALTER TABLE users ADD UNIQUE index_users( id )

　　//方式2

　　CREATE UNIQUE INDEX index_name ON table_name (column_name)

　　比如：CREATE UNIQUE INDEX index_users ON users(id)

　　(3) 主鍵索引

　　它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候指定了主鍵，就會自動創建主鍵索引， CREATE INDEX不能用來創建主鍵索引，使用 ALTER TABLE來代替。

　　如何使用？

　　//方式1

　　ALTER TABLE table_name ADD PRIMARY KEY ( column )

　　比如：ALTER TABLE users ADD PRIMARY KEY ( id )

　　方式2：創建表的時候指定主鍵

　　1.2、聚集索引和非聚集索引

　　（1）聚集索引

　　聚集索引，又叫聚簇索引，所有的數據都存在聚集索引上，葉子節點直接對應數據，從中間級的索引頁的索引行直接對應數據頁。InnoDB存儲引擎中的主鍵默認就會創建一個聚集索引，每張表只能創建一個聚集索引。記錄的索引順序與物理順序相同，更適合between and和order by操作。

　　InnoDB聚集索引的葉子節點存儲行記錄，因此， InnoDB必須要有，且只有一個聚集索引：

　　（1）如果表定義了PK，則PK就是聚集索引；

　　（2）如果表沒有定義PK，則第一個not NULL unique列是聚集索引；

　　（3）否則，InnoDB會創建一個隱藏的row-id作為聚集索引

　　畫外音：所以PK查詢非常快，直接定位行記錄。

　　打個比方，一個表就像是我們以前用的新華字典，聚集索引就像是拼音目錄，而每個字存放的頁碼就是我們的數據物理地址，我們如果要查詢一個“哇”字，我們只需要查詢“哇”字對應在新華字典拼音目錄對應的頁碼，就可以查詢到對應的“哇”字所在的位置，而拼音目錄對應的A-Z的字順序，和新華字典實際存儲的字的順序A-Z也是一樣的，如果我們中文新出了一個字，拼音開頭第一個是B，那么他插入的時候也要按照拼音目錄順序插入到A字的后面。

　　（2）非聚集索引

　　非聚集索引，又叫非聚簇索引、輔助索引，所有的數據和索引的目錄是分開存的，葉子節點不存放具體的整行數據（葉子結點不直接指向數據頁），而是存儲的這一行的主鍵的值。

　　記錄的索引順序與物理順序無關。每張表可以有多個非聚集索引，需要更多磁盤和內存，多個索引會影響insert和update的速度。

　　畫外音：非聚集索引需要回表查詢，先定位主鍵值，再定位行記錄，因為要掃描兩遍索引樹，它的性能較掃一遍索引樹更低。

　　其實按照定義，除了聚集索引以外的索引都是非聚集索引，只是人們想細分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引類比成現實生活中的東西，那么非聚集索引就像新華字典的偏旁字典，他結構順序與實際存放順序不一定一致。

　　1.3、聯合索引最左匹配原則

　　聯合索引又叫復合索引，對表上的多個字段同時建立的索引(有順序，ABC，ACB是完全不同的兩種聯合索引。)以聯合索引(a,b,c)為例，建立這樣的索引相當于建立了索引a、ab、abc三個索引。

　　遵循最左前綴原則（必須帶著最左邊的列做條件才能命中索引），且從出現范圍開始索引失效；

　　當遇到范圍查詢(>、<、between、like)就會停止匹配。也就是：

　　#這樣a,b可以用到（a,b,c），c不可以

　　select * from t where a=1 and b>1 and c =1;

　　這條語句只有 a,b 會用到索引，c 都不能用到索引。

　　create index mix_ind on 表名 (id,name,email);

　　select * from 表名 where id = 123; # 命中索引

　　select * from 表名 where id = 123 and name = 'pamela'; # 命中索引

　　select * from 表名 where id > 123 and name = 'pamela'; # id命中，name不命中索引，因為出現范圍

　　select * from 表名 where id = 123 and email = 'pamela@123.com'; # 命中索引

　　select * from 表名 where email = 'pamela@123.com'; # 不命中索引，因為條件中沒有id

　　select * from 表名 where name='pamela' and email = 'pamela@123.com'; # 不命中

　　A:select * from student where age = 16 and name = '小張'

　　B:select * from student where name = '小張' and sex = '男'

　　C:select * from student where name = '小張' and sex = '男' and age = 18

　　D:select * from student where age > 20 and name = '小張'

　　E:select * from student where age != 15 and name = '小張'

　　A遵從最左匹配原則，age是在最左邊，所以A走索引；

　　B直接從name開始，沒有遵從最左匹配原則，所以不走索引；

　　C雖然從name開始，但是有索引最左邊的age，mysql內部會自動轉成where age = '18' and name = '小張' and sex = '男' 這種，所以還是遵從最左匹配原則；

　　D這個是因為age>20是范圍，范圍字段會結束索引對范圍后面索引字段的使用，所以只有走了age這個索引；

　　E這個雖然遵循最左匹配原則，但是不走索引，因為!= 不走索引；

　　question1:如何給下列sql語句加上聯合索引？

　　select * from test where a = 1 and b = 1 and c = 1;

　　answer：

　　咱們一看，直接加索引(a,b,c)就可以了，其實不然，也不能說這個答案不對，只能說這個答案不夠完整。因為mysql在執行的時候會經歷優化器過程，所以會把sql語句自動優化成符合索引的順序，所以索引(a,b,c) (a,c,b) 或者是(c,b,a)都是可以的，那我們究竟如何確定索引呢？這個就得根據實際情況來了，比如a字段是表示性別的，只有0，1和2三個值來表示未知，男，女三種性別，那么把a字段放在聯合索引的最后會比較合適，反正哪個字段的內容重復率越高，就把哪個字段往聯合索引的后面放。

　　question2:如何給下列sql語句加上索引？

　　SELECT * FROM table WHERE a > 1 and b = 2;

　　answer：

　　如果咱們建立索引(a,b),那么a>1是可以走到索引的，但是b=2就沒法走到索引了。但是如果咱們建立索引(b,a),那么sql語句會被自動優化成 where b=2 and a> 1，這樣a和b都能走到索引，所以建立索引(b,a)比較合適

　　1.4、索引覆蓋和回表

　　使用聚集索引（主鍵或第一個唯一索引）就不會回表，非聚集索引就會回表。當select的數據列被所建索引覆蓋時不需要回表，可以直接取得數據。

　　覆蓋索引是select的數據列只用從索引中就能夠取得，不必讀取數據行，換句話說查詢列要被所建的索引覆蓋。覆蓋索引在查詢過程中不需要回表。只需要在一棵索引樹上就能獲取SQL所需的所有列數據，無需回表速度更快。

　　覆蓋索引其核心就是只從輔助索引要數據。那么, 普通索引(單字段)和聯合索引，以及唯一索引都能實現覆蓋索引的作用。explain的輸出結果Extra字段為Using index時，能夠觸發索引覆蓋。

　　create index ind_id on 表名(id); # 對id字段創建了索引

　　select id from 表名 where id > 100; # 覆蓋索引：在查找一條數據的時候，命中索引，不需要再回表

　　select max(id) from 表名 where id > 100; # 覆蓋索引：在查找一條數據的時候，命中索引，不需要再回表

　　select count(id) from 表名 where id > 100; # 覆蓋索引：在查找一條數據的時候，命中索引，不需要再回表

　　select name from 表名 where id > 100; # 相對慢

　　(1) 如何實現索引覆蓋？

　　常見的方法是：將被查詢的字段，建立到聯合索引里去。

　　select id,name from user where name='shenjian';

　　MySQL索引優化知識點有哪些

　　能夠命中name索引，索引葉子節點存儲了主鍵id，通過name的索引樹即可獲取id和name，無需回表，符合索引覆蓋，效率較高。

　　Extra：Using index。

　　(2)哪些場景可以利用索引覆蓋來優化SQL？

　　場景1：全表count查詢優化

　　MySQL索引優化知識點有哪些

　　原表為：user(PK id, name, sex)；不能利用索引覆蓋

　　select count(name) from user;

　　添加索引，就能夠利用索引覆蓋提效

　　alter table user add key(name);

　　場景2：列查詢回表優化

　　這個例子不再贅述，將單列索引(name)升級為聯合索引(name, sex)，即可避免回表。

　　場景3：分頁查詢

　　將單列索引(name)升級為聯合索引(name, sex)，也可以避免回表。

　　1.5、前綴索引

　　前綴索引說白了就是對文本的前幾個字符（具體是幾個字符在建立索引時指定）建立索引，這樣建立起來的索引更小，所以查詢更快。

　　ALTER TABLE table_name ADD KEY(column_name(prefix_length));

　　MySQL 前綴索引能有效減小索引文件的大小，提高索引的速度。但是前綴索引也有它的壞處：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前綴索引，也不能把它們用作覆蓋索引(Covering Index)。

　　1.6、索引合并

　　深入理解 index merge 是使用索引進行優化的重要基礎之一。理解了 index merge 技術，我們才知道應該如何在表上建立索引。

　　為什么會有index merge？

　　我們的 where 中可能有多個條件(或者join)涉及到多個字段，它們之間進行 AND 或者 OR，那么此時就有可能會使用到 index merge 技術。index merge 技術如果簡單的說，其實就是：對多個索引分別進行條件掃描，然后將它們各自的結果進行合并(intersect/union)。

　　MySQL5.0之前，一個表一次只能使用一個索引，無法同時使用多個索引分別進行條件掃描。但是從5.1開始，引入了 index merge 優化技術，對同一個表可以使用多個索引分別進行條件掃描。

　　索引合并是指分別創建的兩個索引，在某一次查詢中臨時合并成一條索引。

　　# 索引合并

　　create index ind_id on 表名(id);

　　create index ind_email on 表名(email);

　　select * from 表名 where id=100 or email = 'pamela@123.com' # 索引合并，臨時把兩個索引ind_id和ind_email合并成一個索引

　　1.7、索引下推

　　(1)索引下推簡介

　　索引條件下推(Index Condition Pushdown),簡稱ICP。MySQL5.6新添加，用于優化數據的查詢。通過索引下推對于非主鍵索引進行優化，可有效減少回表次數，從而提高效率。

　　如果沒有索引下推優化（或稱ICP優化），當進行索引查詢時，首先根據索引來查找記錄，然后再根據where條件來過濾記錄；在支持ICP優化后，MySQL會在取出索引的同時，判斷是否可以進行where條件過濾再進行索引查詢，也就是說提前執行where的部分過濾操作，在某些場景下，可以大大減少回表次數，從而提升整體性能。

　　當你不使用ICP,通過使用非主鍵索引（普通索引or二級索引）進行查詢，存儲引擎通過索引檢索數據，然后返回給MySQL服務器，服務器再判斷是否符合條件。

　　使用ICP，當存在索引的列做為判斷條件時，MySQL服務器將這一部分判斷條件傳遞給存儲引擎，然后存儲引擎通過判斷索引是否符合MySQL服務器傳遞的條件，只有當索引符合條件時才會將數據檢索出來返回給MySQL服務器。

　　(2)適用場景

　　當需要整表掃描，e.g.:range,ref,eq_ref....

　　適用InnoDB引擎和MyISAM引擎查詢（5.6版本不適用分區查詢，5.7版本可以用于分區表查詢）。

　　InnoDB引擎僅僅適用二級索引。（原因InnoDB聚簇索引將整行數據讀到InnoDB緩沖區）。

　　子查詢條件不能下推。觸發條件不能下推，調用存儲過程條件不能下推。

　　二、索引優化規則

　　查詢的條件字段盡量用索引字段

　　2.0、and/or條件相連

　　and條件相連，有一列有索引就會命中索引，加快查詢速度；or條件相連，所有列都有索引才能命中索引，加快查詢速度；

　　create index mix_ind on 表名 (id);

　　select * from 表名 where id = 123 and name = 'pamela'; # 有一列有索引，速度快

　　select * from 表名 where id = 123 or name = 'pamela'; # 不是所有列都有索引，速度慢

　　2.1、like語句的前導模糊查詢不能使用索引

　　select * from doc where title like '%XX'； --不能使用索引

　　select * from doc where title like 'XX%'； --非前導模糊查詢，可以使用索引

　　因為頁面搜索嚴禁左模糊或者全模糊，如果需要可以使用搜索引擎來解決。

　　2.2、union、in、or 都能夠命中索引，建議使用 in

　　union能夠命中索引，并且MySQL 耗費的 CPU 最少。

　　select * from doc where status=1

　　union all

　　select * from doc where status=2;

　　in能夠命中索引，查詢優化耗費的 CPU 比 union all 多，但可以忽略不計，一般情況下建議使用 in。

　　select * from doc where status in (1, 2);

　　or 新版的 MySQL（MySQL5.0后）索引合并能夠命中索引，查詢優化耗費的 CPU 比 in多，不建議頻繁用or。

　　select * from doc where status = 1 or status = 2

　　補充：有些地方說在where條件中使用or，索引會失效，造成全表掃描，這是個誤區：

　　要求where子句使用的所有字段，都必須建立索引；

　　如果數據量太少，mysql制定執行計劃時發現全表掃描比索引查找更快，所以會不使用索引；

　　確保mysql版本5.0以上，且查詢優化器開啟了index_merge_union=on, 也就是變量optimizer_switch里存在index_merge_union且為on。

　　2.3、負向條件查詢不能使用索引

　　負向條件有：!=、<>、not in、not exists、not like 等。

　　例如下面SQL語句：

　　select * from doc where status != 1 and status != 2;

　　可以優化為 in 查詢：

　　select * from doc where status in （0,3,4）;

　　2.4、聯合索引最左前綴原則

　　如果在(a,b,c)三個字段上建立聯合索引，那么他會自動建立 a| (a,b) | (a,b,c)組索引。聯合索引遵循最左前綴原則（必須帶著最左邊的列做條件才能命中索引），且從出現范圍開始索引失效；

　　create index mix_ind on 表名 (id,name,email);

　　select * from 表名 where id = 123; # 命中索引

　　select * from 表名 where id > 123; # 不命中索引，因為出現范圍

　　select * from 表名 where id = 123 and name = 'pamela'; # 命中索引

　　select * from 表名 where id > 123 and name = 'pamela'; # 不命中索引，因為出現范圍

　　select * from 表名 where id = 123 and email = 'pamela@123.com'; # 命中索引

　　select * from 表名 where email = 'pamela@123.com'; # 不命中索引，因為條件中沒有id

　　select * from 表名 where name='pamela' and email = 'pamela@123.com'; # 不命中索引，因為條件中沒有id

　　登錄業務需求，SQL語句如下：

　　select uid, login_time from user where login_name=? andpasswd=?

　　可以建立(login_name, passwd)的聯合索引。因為業務上幾乎沒有passwd 的單條件查詢需求，而有很多login_name 的單條件查詢需求，所以可以建立(login_name, passwd)的聯合索引，而不是(passwd, login_name)。

　　2.5、不能使用索引中范圍條件右邊的列（范圍列可以用到索引），范圍列之后列的索引全失效。

　　范圍條件有：<、<=、>、>=、between等。

　　索引最多用于一個范圍列，如果查詢條件中有兩個范圍列則無法全用到索引。

　　假如有聯合索引 (empno、title、fromdate)，那么下面的 SQL 中 emp_no 可以用到索引，而title 和 from_date 則使用不到索引。

　　select * from employees.titles where emp_no < 10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'

　　2.6、不要在索引列上面做任何操作（計算、函數），否則會導致索引失效而轉向全表掃描。

　　例如下面的 SQL 語句，即使 date 上建立了索引，也會全表掃描：

　　select * from doc where YEAR(create_time) <= '2016';

　　可優化為值計算，如下：

　　select * from doc where create_time <= '2016-01-01';

　　比如下面的 SQL 語句：

　　select * from order where date < = CURDATE()；

　　可以優化為：

　　select * from order where date < = '2018-01-2412:00:00';

　　2.7、強制類型轉換會全表掃描

　　字符串類型不加單引號會導致索引失效，因為mysql會自己做類型轉換,相當于在索引列上進行了操作。

　　如果 phone 字段是 varchar 類型，則下面的 SQL 不能命中索引。

　　select * from user where phone=13800001234

　　可以優化為：

　　select * from user where phone='13800001234';

　　2.8、更新十分頻繁、數據區分度不高的列不宜建立索引

　　更新會變更 B+ 樹，更新頻繁的字段建立索引會大大降低數據庫性能。

　　“性別”這種區分度不大的屬性，建立索引是沒有什么意義的，不能有效過濾數據，性能與全表掃描類似。

　　一般區分度在80%以上的時候就可以建立索引，區分度可以使用 count(distinct(列名))/count(*) 來計算。

　　2.9、利用覆蓋索引來進行查詢操作，避免回表，減少select * 的使用

　　覆蓋索引：查詢的列和所建立的索引的列個數相同，字段相同。

　　被查詢的列，數據能從索引中取得，而不用通過行定位符 row-locator 再到 row 上獲取，即“被查詢列要被所建的索引覆蓋”，這能夠加速查詢速度。

　　例如登錄業務需求，SQL語句如下。

　　select uid, login_time from user where login_name=? and passwd=?

　　可以建立(login_name, passwd, login_time)的聯合索引，由于 login_time 已經建立在索引中了，被查詢的 uid 和 login_time 就不用去 row 上獲取數據了，從而加速查詢。

　　2.10、索引不會包含有NULL值的列

　　只要列中包含有NULL值都將不會被包含在索引中，復合索引中只要有一列含有NULL值，那么這一列對于此復合索引就是無效的。所以我們在數據庫設計時，盡量使用not null 約束以及默認值。

　　2.11、is null, is not null無法使用索引

　　2.12、如果有order by、group by的場景，請注意利用索引的有序性

　　order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現file_sort 的情況，影響查詢性能。

　　例如對于語句 where a=? and b=? order by c，可以建立聯合索引(a,b,c)。

　　如果索引中有范圍查找，那么索引有序性無法利用，如 WHERE a>10 ORDER BY b;，索引(a,b)無法排序。

　　2.13、使用短索引（前綴索引）

　　對列進行索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的列，如果該列在前10個或20個字符內，可以做到既使得前綴索引的區分度接近全列索引，那么就不要對整個列進行索引。因為短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作，減少索引文件的維護開銷。可以使用count(distinct leftIndex(列名, 索引長度))/count(*) 來計算前綴索引的區分度。

　　但缺點是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆蓋索引。

　　不過很多時候沒必要對全字段建立索引，根據實際文本區分度決定索引長度即可。

　　2.14、利用延遲關聯或者子查詢優化超多分頁場景

　　MySQL 并不是跳過 offset 行，而是取 offset+N 行，然后返回放棄前 offset 行，返回 N 行，那當 offset 特別大的時候，效率就非常的低下，要么控制返回的總頁數，要么對超過特定閾值的頁數進行 SQL 改寫。

　　示例如下，先快速定位需要獲取的id段，然后再關聯:

　　select a.* from 表1 a,(select id from 表1 where 條件 limit100000,20 ) b where a.id=b.id；

　　2.15、如果明確知道只有一條結果返回，limit 1 能夠提高效率

　　比如如下 SQL 語句：

　　select * from user where login_name=?;

　　可以優化為：

　　select * from user where login_name=? limit 1

　　自己明確知道只有一條結果，但數據庫并不知道，明確告訴它，讓它主動停止游標移動。

　　2.16、超過三個表最好不要 join

　　需要 join 的字段，數據類型必須一致，多表關聯查詢時，保證被關聯的字段需要有索引。

　　例如：left join是由左邊決定的，左邊的數據一定都有，所以右邊是我們的關鍵點，建立索引要建右邊的。當然如果索引在左邊，可以用right join。

　　2.17、單表索引建議控制在5個以內

　　2.18、SQL 性能優化 explain 中的 type：至少要達到 range 級別，要求是 ref 級別，如果可以是 consts 最好

　　consts：單表中最多只有一個匹配行（主鍵或者唯一索引），在優化階段即可讀取到數據。

　　ref：使用普通的索引（Normal Index）。

　　range：對索引進行范圍檢索。

　　當 type=index 時，索引物理文件全掃，速度非常慢。

　　2.19、業務上具有唯一特性的字段，即使是多個字段的組合，也必須建成唯一索引

　　不要以為唯一索引影響了 insert 速度，這個速度損耗可以忽略，但提高查找速度是明顯的。另外，即使在應用層做了非常完善的校驗控制，只要沒有唯一索引，根據墨菲定律，必然有臟數據產生。

　　2.20、創建索引時避免以下錯誤觀念

　　索引越多越好，認為需要一個查詢就建一個索引。寧缺勿濫，因為索引會消耗空間、嚴重拖慢更新和新增速度。

　　抵制惟一索引，認為業務的惟一性一律需要在應用層通過“先查后插”方式解決。

　　過早優化，在不了解系統的情況下就開始優化。

　　三、使用索引和不使用索引的情況

　　3.1、使用索引

　　主鍵自動建立唯一索引。

　　經常作為查詢條件在WHERE或者ORDER BY 語句中出現的列要建立索引。

　　查詢中與其他表關聯的字段，外鍵關系建立索引。

　　經常用于聚合函數的列要建立索引，如min()，max()等的聚合函數。

　　3.2、不使用索引

　　經常增刪改的列不要建立索引。

　　有大量重復的列不建立索引。

　　表記錄太少不要建立索引，因為數據較少，可能查詢全部數據花費的時間比遍歷索引的時間還要短，索引就可能不會產生優化效果。

　　到此，關于“MySQL索引優化知識點有哪些”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！

（編輯：武林網）

上一篇：MySQL數據庫初體會

下一篇：mysql數據庫角色如何創建