MySQL中如何優(yōu)化索引，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

為諸暨等地區(qū)用戶提供了全套網(wǎng)頁設計制作服務，及諸暨網(wǎng)站建設行業(yè)解決方案。主營業(yè)務為成都做網(wǎng)站、網(wǎng)站建設、諸暨網(wǎng)站設計，以傳統(tǒng)方式定制建設網(wǎng)站，并提供域名空間備案等一條龍服務，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務。我們深信只要達到每一位用戶的要求，就會得到認可，從而選擇與我們長期合作。這樣，我們也可以走得更遠！

文章的腦圖如下：

索引優(yōu)化規(guī)則

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

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

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

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

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

select * from doc where status=1 union all select * from doc where status=2;

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

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

or 新版的 MySQL 能夠命中索引，查詢優(yōu)化耗費的 CPU 比 in多，不建議頻繁用or。

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

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

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

• ②如果數(shù)據(jù)量太少，mysql制定執(zhí)行計劃時發(fā)現(xiàn)全表掃描比索引查找更快，所以會不使用索引;

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

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

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

• 例如下面SQL語句：

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

可以優(yōu)化為 in 查詢：

select * from doc where status in (0,3,4);

4、聯(lián)合索引最左前綴原則

• 如果在(a,b,c)三個字段上建立聯(lián)合索引，那么他會自動建立 a| (a,b) | (a,b,c)組索引。

• 登錄業(yè)務需求，SQL語句如下：

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

• 可以建立(login_name, passwd)的聯(lián)合索引。因為業(yè)務上幾乎沒有passwd 的單條件查詢需求，而有很多l(xiāng)ogin_name  的單條件查詢需求，所以可以建立(login_name, passwd)的聯(lián)合索引，而不是(passwd, login_name)。

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術社區(qū)

2. 建立聯(lián)合索引的時候，區(qū)分度最高的字段在最左邊

3. 存在非等號和等號混合判斷條件時，在建立索引時，把等號條件的列前置。如 where a>? and b=?，那么即使a 的區(qū)分度更高，也必須把 b  放在索引的最前列。

4. 最左前綴查詢時，并不是指SQL語句的where順序要和聯(lián)合索引一致。

• 下面的 SQL 語句也可以命中 (login_name, passwd) 這個聯(lián)合索引：

select uid, login_time from user where passwd=? andlogin_name=?

• 但還是建議 where 后的順序和聯(lián)合索引一致，養(yǎng)成好習慣。

假如index(a,b,c), where a=3 and b like 'abc%' and c=4，a能用，b能用，c不能用。

5、不能使用索引中范圍條件右邊的列(范圍列可以用到索引)，范圍列之后列的索引全失效

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

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

• 假如有聯(lián)合索引 (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'

6、不要在索引列上面做任何操作(計算、函數(shù))，否則會導致索引失效而轉向全表掃描

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

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

可優(yōu)化為值計算，如下：

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

比如下面的 SQL 語句：

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

可以優(yōu)化為：

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

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

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

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

select * from user where phone=13800001234

可以優(yōu)化為：

select * from user where phone='13800001234';

8、更新十分頻繁、數(shù)據(jù)區(qū)分度不高的列不宜建立索引

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

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

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

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

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

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

• 例如登錄業(yè)務需求，SQL語句如下。

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

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

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

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

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

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

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

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

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

13、使用短索引(前綴索引)

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

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

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

14、利用延遲關聯(lián)或者子查詢優(yōu)化超多分頁場景

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

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

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

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

• 比如如下 SQL 語句：

select * from user where login_name=?;

• 可以優(yōu)化為：

select * from user where login_name=? limit 1

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

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

• 需要 join 的字段，數(shù)據(jù)類型必須一致，多表關聯(lián)查詢時，保證被關聯(lián)的字段需要有索引。

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

17、單表索引建議控制在5個以內(nèi)

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

• consts：單表中最多只有一個匹配行(主鍵或者唯一索引)，在優(yōu)化階段即可讀取到數(shù)據(jù)。

• ref：使用普通的索引(Normal Index)。

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

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

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

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

20.創(chuàng)建索引時避免以下錯誤觀念

索引越多越好，認為需要一個查詢就建一個索引。

寧缺勿濫，認為索引會消耗空間、嚴重拖慢更新和新增速度。

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

過早優(yōu)化，在不了解系統(tǒng)的情況下就開始優(yōu)化。

索引選擇性與前綴索引

• 既然索引可以加快查詢速度，那么是不是只要是查詢語句需要，就建上索引?答案是否定的。因為索引雖然加快了查詢速度，但索引也是有代價的：索引文件本身要消耗存儲空間，同時索引會加重插入、刪除和修改記錄時的負擔，另外，MySQL在運行時也要消耗資源維護索引，因此索引并不是越多越好。一般兩種情況下不建議建索引。

• 第一種情況是表記錄比較少，例如一兩千條甚至只有幾百條記錄的表，沒必要建索引，讓查詢做全表掃描就好了。至于多少條記錄才算多，這個個人有個人的看法，我個人的經(jīng)驗是以2000作為分界線，記錄數(shù)不超過  2000可以考慮不建索引，超過2000條可以酌情考慮索引。

• 另一種不建議建索引的情況是索引的選擇性較低。所謂索引的選擇性(Selectivity)，是指不重復的索引值(也叫基數(shù)，Cardinality)與表記錄數(shù)(#T)的比值：

Index Selectivity = Cardinality / #T

• 顯然選擇性的取值范圍為(0,  1]``，選擇性越高的索引價值越大，這是由B+Tree的性質決定的。例如，employees.titles表，如果title`字段經(jīng)常被單獨查詢，是否需要建索引，我們看一下它的選擇性：

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles; +-------------+ | Selectivity | +-------------+ |      0.0000 | +-------------+

• title的選擇性不足0.0001(精確值為0.00001579)，所以實在沒有什么必要為其單獨建索引。

• 有一種與索引選擇性有關的索引優(yōu)化策略叫做前綴索引，就是用列的前綴代替整個列作為索引key，當前綴長度合適時，可以做到既使得前綴索引的選擇性接近全列索引，同時因為索引key變短而減少了索引文件的大小和維護開銷。下面以employees.employees表為例介紹前綴索引的選擇和使用。

• 假設employees表只有一個索引

  ，那么如果我們想按名字搜索一個人，就只能全表掃描了：

EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido'; +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       | +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+ |  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 300024 | Using where | +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

• 如果頻繁按名字搜索員工，這樣顯然效率很低，因此我們可以考慮建索引。有兩種選擇，建

  或

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees; +-------------+ | Selectivity | +-------------+ |      0.0042 | +-------------+ SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees; +-------------+ | Selectivity | +-------------+ |      0.9313 | +-------------+

• 顯然選擇性太低，`選擇性很好，但是first_name和last_name加起來長度為30，有沒有兼顧長度和選擇性的辦法？可以考慮用first_name和last_name的前幾個字符建立索引，例如，看看其選擇性：

SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees; +-------------+ | Selectivity | +-------------+ |      0.7879 | +-------------+

• 選擇性還不錯，但離0.9313還是有點距離，那么把last_name前綴加到4：

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees; +-------------+ | Selectivity | +-------------+ |      0.9007 | +-------------+

• 這時選擇性已經(jīng)很理想了，而這個索引的長度只有18，比

ALTER TABLE employees.employees ADD INDEX `first_name_last_name4` (first_name, last_name(4));

• 此時再執(zhí)行一遍按名字查詢，比較分析一下與建索引前的結果：

SHOW PROFILES; +----------+------------+---------------------------------------------------------------------------------+ | Query_ID | Duration   | Query                                                                           | +----------+------------+---------------------------------------------------------------------------------+ |       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' | |       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' | +----------+------------+---------------------------------------------------------------------------------+

關于MySQL中如何優(yōu)化索引問題的解答就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注創(chuàng)新互聯(lián)行業(yè)資訊頻道了解更多相關知識。