索引

• • 1. 索引怎么建好？
  • 2. 索引容易失效的場景
  • 3. 連接查詢索引優(yōu)化
  • 4. order by，group by
  • 5. 覆蓋索引
  • 6. 索引下推

1. 索引怎么建好？

• 單表
  • 主鍵必須唯一，且單調(diào)遞增
  • 有唯一鍵的，盡量建立唯一鍵
  • where條件用得比較多的字段
  • 查詢條件多的，用聯(lián)合索引，如果帶有范圍條件的，將這個(gè)列，放在聯(lián)合索引的后面

• 多表查詢時(shí)候的索引

  • on條件后面列建立索引，select a.a1, a.a2, b.b1, b.b2 from a join b on a.a1 = b.b1; 這種sql兩個(gè)列的類型要相同。
  • 多用join代in查詢

• 其它

  • order by, group by的列
  • UPDATE、DELETE 的 WHERE 條件列

2. 索引容易失效的場景

①. 全值匹配, 聯(lián)合索引下，每個(gè)列都用上索引

②. 最左匹配原則

create index idx_name_age_classId on student(name, age, classId);-- 不生效 explain select * from student where age = 1;

查詢上面的語句不會(huì)走索引, 如果是name作為條件，索引才會(huì)生效

下面走了索引，name & age

explain select id, name, age, classId from student where name = 'jack' and age = 1;

③. 排序字段應(yīng)該放最后

# age 作為排序字段，放到后面 create index idx_cid_name_age on student(classId, name, age);explain select * from student where age > 48 and classId = 99 and name = 'jack';

---

④ 不等于, (<>, !=) 索引失效

⑤ is null 有效, is not null 索引失效

⑥ like查詢以通配符開始的失效，如果不走索引覆蓋的話。用es。

⑦ 函數(shù)導(dǎo)致索引失效

⑧ or 前后存在非索引的列, 索引失效

⑨ 數(shù)據(jù)庫和表的字符集統(tǒng)一使用utf8mb4

3. 連接查詢索引優(yōu)化

① 左連接 左邊的表是驅(qū)動(dòng)表 右邊的表是被驅(qū)動(dòng)表.
② 內(nèi)連接, 小表驅(qū)動(dòng)大表, 小表是結(jié)果集少的
③ 為join的字段創(chuàng)建索引，并且join的字段類型要一致

保證被驅(qū)動(dòng)表的JOIN字段已經(jīng)創(chuàng)建了索引
需要JOIN 的字段，數(shù)據(jù)類型保持絕對一致。
LEFT JOIN 時(shí)，選擇小表作為驅(qū)動(dòng)表， 大表作為被驅(qū)動(dòng)表 。減少外層循環(huán)的次數(shù)。
INNER JOIN 時(shí)，MySQL會(huì)自動(dòng)將 小結(jié)果集的表選為驅(qū)動(dòng)表 。選擇相信MySQL優(yōu)化策略。
能夠直接多表關(guān)聯(lián)的盡量直接關(guān)聯(lián)，不用子查詢。(減少查詢的趟數(shù))
不建議使用子查詢，建議將子查詢SQL拆開結(jié)合程序多次查詢，或使用 JOIN 來代替子查詢。
衍生表建不了索引。

4. order by，group by

• 排序, 單個(gè)字段, 請建立索引。多個(gè)字段建立聯(lián)合索引。 盡可能的使用index索引排序, 如果不能, 使用file sort 文件排序,加大內(nèi)存參數(shù)

  order by 有兩種排序方法：1，走索引；2，file sort 內(nèi)存排序。索引快，如果用內(nèi)存排序，吃資源。

SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表掃描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。當(dāng)然，某些情況下全表掃描，或者 FileSort 排序不一定比索引慢。但總的來說，我們還是要避免，以提高查詢效率。

盡量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用單索引列；如果不同就使用聯(lián)合索引

無法使用 Index 時(shí)，需要對 FileSort 方式進(jìn)行調(diào)優(yōu)。

• group by 也請走索引…

group by 使用索引的原則幾乎跟order by一致 ，group by 即使沒有過濾條件用到索引，也可以直接使用索引。
group by 先排序再分組，遵照索引建的最佳左前綴法則
當(dāng)無法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 參數(shù)的設(shè)置
where效率高于having，能寫在where限定的條件就不要寫在having中了
減少使用order by，和業(yè)務(wù)溝通能不排序就不排序，或?qū)⑴判蚍诺匠绦蚨巳プ觥rder by、group by、distinct這些語句較為耗費(fèi)CPU，數(shù)據(jù)庫的CPU資源是極其寶貴的。
包含了order by、group by、distinct這些查詢的語句，where條件過濾出來的結(jié)果集請保持在1000行以內(nèi)，否則SQL會(huì)很慢。

5. 覆蓋索引

對聯(lián)合索引來說的，查詢直接查索引。不會(huì)回表，一次查詢出來結(jié)果。查詢的列都是索引，查詢條件也是所有列。

所以這里的like統(tǒng)配匹配不會(huì)失效。

6. 索引下推

這個(gè)也是聯(lián)合索引中出現(xiàn)的，最大的特征是，用explain分析工具，查詢extra信息，顯示Using index condition。
① 索引的每個(gè)列和查詢的值比較一遍
② 匹配到的索引，再進(jìn)行回表拿到結(jié)果。

ICP的使用條件：
① 只能用于二級(jí)索引(secondary index)
②explain顯示的執(zhí)行計(jì)劃中type值（join 類型）為 range 、 ref 、 eq_ref 或者 ref_or_null 。
③ 并非全部where條件都可以用ICP篩選，如果where條件的字段不在索引列中，還是要讀取整表的記錄
到server端做where過濾。
④ ICP可以用于MyISAM和InnnoDB存儲(chǔ)引擎
⑤ MySQL 5.6版本的不支持分區(qū)表的ICP功能，5.7版本的開始支持。
⑥ 當(dāng)SQL使用覆蓋索引時(shí)，不支持ICP優(yōu)化方法。

總結(jié)

以上是生活随笔為你收集整理的索引常用注意事项的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。