數據庫中90%程序員面試會遇到的索引優(yōu)化問題有哪些

這篇文章主要介紹數據庫中90%程序員面試會遇到的索引優(yōu)化問題有哪些，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

永年網站建設公司創(chuàng)新互聯(lián),永年網站設計制作，有大型網站制作公司豐富經驗。已為永年1000多家提供企業(yè)網站建設服務。企業(yè)網站搭建\成都外貿網站建設要多少錢，請找那個售后服務好的永年做網站的公司定做！

關于索引，分為以下幾點來講解（技術文）：

  • 索引的概述(什么是索引,索引的優(yōu)缺點)

  • 索引的基本使用（創(chuàng)建索引）

  • 索引的基本原理（面試重點）

  • 索引的數據結構（B樹，hash）

  • 創(chuàng)建索引的原則（重中之重，面試必問！敬請收藏！）

  • 百萬級別或以上的數據如何刪除

一、索引的概述

1）什么是索引？

索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分)，它們包含著對數據表里所有記錄的引用指針。更通俗的說，索引就相當于目錄。當你在用新華字典時，幫你把目錄撕掉了，你查詢某個字開頭的成語只能從第一頁翻到第一千頁。累！把目錄還給你，則能快速定位！

2）索引的優(yōu)缺點：

可以大大加快數據的檢索速度，這也是創(chuàng)建索引的最主要的原因。，且通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。但是，索引也是有缺點的：索引需要額外的維護成本；因為索引文件是單獨存在的文件,對數據的增加,修改,刪除,都會產生額外的對索引文件的操作,這些操作需要消耗額外的IO,會降低增/改/刪的執(zhí)行效率。

二、索引的基本使用（真技術文）

1）創(chuàng)建索引：（三種方式）

第一種方式：

數據庫中90%程序員面試會遇到的索引優(yōu)化問題有哪些

第二種方式：使用ALTER TABLE命令去增加索引：

ALTER TABLE用來創(chuàng)建普通索引、UNIQUE索引或PRIMARY KEY索引。

數據庫中90%程序員面試會遇到的索引優(yōu)化問題有哪些

其中table_name是要增加索引的表名，column_list指出對哪些列進行索引，多列時各列之間用逗號分隔。

索引名index_name可自己命名，缺省時，MySQL將根據第一個索引列賦一個名稱。另外，ALTER TABLE允許在單個語句中更改多個表，因此可以在同時創(chuàng)建多個索引。

第三種方式：使用CREATE INDEX命令創(chuàng)建

CREATE INDEX可對表增加普通索引或UNIQUE索引。（但是，不能創(chuàng)建PRIMARY KEY索引）

數據庫中90%程序員面試會遇到的索引優(yōu)化問題有哪些

三、索引的基本原理（不想像別的文章那樣一大堆篇幅廢話）

索引用來快速地尋找那些具有特定值的記錄。如果沒有索引，一般來說執(zhí)行查詢時遍歷整張表。

索引的原理很簡單，就是把無序的數據變成有序的查詢

      1、把創(chuàng)建了索引的列的內容進行排序

      2、對排序結果生成倒排表

      3、在倒排表內容上拼上數據地址鏈

      4、在查詢的時候，先拿到倒排表內容，再取出數據地址鏈，從而拿到具體數據

四、索引的數據結構（b樹，hash）

1）B樹索引

mysql通過存儲引擎取數據，基本上90%的人用的就是InnoDB了，按照實現方式分，InnoDB的索引類型目前只有兩種：BTREE（B樹）索引和HASH索引。B樹索引是Mysql數據庫中使用最頻繁的索引類型，基本所有存儲引擎都支持BTree索引。通常我們說的索引不出意外指的就是（B樹）索引（實際是用B+樹實現的，因為在查看表索引時，mysql一律打印BTREE，所以簡稱為B樹索引）

數據庫中90%程序員面試會遇到的索引優(yōu)化問題有哪些

查詢方式：

主鍵索引區(qū):PI(關聯(lián)保存的時數據的地址)按主鍵查詢,

普通索引區(qū):si(關聯(lián)的id的地址,然后再到達上面的地址)。所以按主鍵查詢,速度最快

B+tree性質：

1.）n棵子tree的節(jié)點包含n個關鍵字，不用來保存數據而是保存數據的索引。

2.）所有的葉子結點中包含了全部關鍵字的信息，及指向含這些關鍵字記錄的指針，且葉子結點本身依關鍵字的大小自小而大順序鏈接。

3.）所有的非終端結點可以看成是索引部分，結點中僅含其子樹中的最大（或最?。╆P鍵字。

4.）B+ 樹中，數據對象的插入和刪除僅在葉節(jié)點上進行。

5.）B+樹有2個頭指針，一個是樹的根節(jié)點，一個是最小關鍵碼的葉節(jié)點。

2）哈希索引（好技術文）

簡要說下，類似于數據結構中簡單實現的HASH表（散列表）一樣，當我們在mysql中用哈希索引時，主要就是通過Hash算法（常見的Hash算法有直接定址法、平方取中法、折疊法、除數取余法、隨機數法），將數據庫字段數據轉換成定長的Hash值，與這條數據的行指針一并存入Hash表的對應位置；如果發(fā)生Hash碰撞（兩個不同關鍵字的Hash值相同），則在對應Hash鍵下以鏈表形式存儲。當然這只是簡略模擬圖。

數據庫中90%程序員面試會遇到的索引優(yōu)化問題有哪些

ps：關于數據結構，有興趣深入的朋友可以關注我后查看【數據結構】專題，這里不做詳細講解。

五、創(chuàng)建索引的原則（重中之重）

索引雖好，但也不是無限制的使用，最好符合一下幾個原則

1） 最左前綴匹配原則，組合索引非常重要的原則，mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。

2）較頻繁作為查詢條件的字段才去創(chuàng)建索引

3）更新頻繁字段不適合創(chuàng)建索引

4）若是不能有效區(qū)分數據的列不適合做索引列(如性別，男女未知，最多也就三種，區(qū)分度實在太低)

5）盡量的擴展索引，不要新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那么只需要修改原來的索引即可。

6）定義有外鍵的數據列一定要建立索引。

7）對于那些查詢中很少涉及的列，重復值比較多的列不要建立索引。

8）對于定義為text、image和bit的數據類型的列不要建立索引。

百萬級別或以上的數據如何刪除（真好技術文）

關于索引：由于索引需要額外的維護成本，因為索引文件是單獨存在的文件,所以當我們對數據的增加,修改,刪除,都會產生額外的對索引文件的操作,這些操作需要消耗額外的IO,會降低增/改/刪的執(zhí)行效率。所以，在我們刪除數據庫百萬級別數據的時候，查詢MySQL官方手冊得知刪除數據的速度和創(chuàng)建的索引數量是成正比的。

  • 所以我們想要刪除百萬數據的時候可以先刪除索引（此時大概耗時三分多鐘）

  • 然后刪除其中無用數據（此過程需要不到兩分鐘）

  • 刪除完成后重新創(chuàng)建索引(此時數據較少了)創(chuàng)建索引也非?？?，約十分鐘左右。

  • 與之前的直接刪除絕對是要快速很多，更別說萬一刪除中斷,一切刪除會回滾。那更是坑了。

常用的數據庫索引優(yōu)化語句

使用如下的表tb_test作為示例進行說明：

create table tb_test
(
id int not null,
age int not null, 
name varchar(30) not null,
addr varchar(50) not null
);
create unique index idx1_tb_test on tb_test(id);
create index idx2_tb_test on tb_test(name);
create index idx3_tb_test on tb_test(addr);

索引優(yōu)化建議

1.對索引列進行計算

例如，我們想要將表tb_test中id大于100的數據記錄中的age和name查找出來。

正確的SQL語句是：

select age,name from tb_test where id > 1*100;

不建議采用的SQL語句是：

select age,name from tb_test where id/100 > 1;

2.對索引列進行拼接

例如，我們想要將表tb_test中name為“zhou”、addr為“CQ”的記錄中的id和age查找出來。

正確的SQL語句是：

select id,age from tb_test where name='zhou' and addr='CQ';

不建議采用的SQL語句是：

select id,age from tb_test where concat(name,' ‘,addr) = ‘zhou CQ';

3.在索引列上is null或is not null的使用

例如，我們想要將表tb_test中id大于等于“0”的記錄中的age查找出來。

正確的SQL語句是：

select age from tb_test where id >= 0;

不建議采用的SQL語句是：

select age from tb_test where id is not null;

4.在索引列上or的使用

例如，我們想要將表tb_test中id等于101或102的記錄中的age和name查找出來。

正確的SQL語句(使用union)是：

select age,name from tb_test where id = 101 union select age,name from tb_test where id = 102;

不建議采用的SQL語句(使用or)是：

select age,name from tb_test where id = 101 or id = 102;

5.盡可能避免索引列在like的首字符使用通配符

例如，我們想要將表tb_test中name匹配“zho”的記錄中的id和age查找出來。

正確的SQL語句是：

select id,age from tb_test where name like ‘zho%';

不建議采用的SQL語句是：

select id,age from tb_test where name like ‘%ho%';

6.復合索引的使用

如果我們建立的索引是復合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統(tǒng)使用該索引。

例如，我們在表tb_test上新建了如下索引：

create index idx4_tb_test on tb_test(id,name,addr);

以上索引idx4_tb_test相當于建立了index(id)、index(id,name)、index(id,name,addr) 這3個索引。在SQL語句的where條件中單獨使用name或addr時不會使用到該索引，必須使用id時才會使用到該索引。

在我們編寫的SQL語句中，不正確地使用索引列可能會導致索引不被使用，而進行全表掃描，極大地降低了數據庫的性能。因此，學習正確的索引的使用方法實在是很有必要的。

以上是“數據庫中90%程序員面試會遇到的索引優(yōu)化問題有哪些”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

當前文章：數據庫中90%程序員面試會遇到的索引優(yōu)化問題有哪些
文章起源：http://muchs.cn/article26/phddjg.html

成都網站建設公司_創(chuàng)新互聯(lián)，為您提供動態(tài)網站、網站改版、全網營銷推廣、網站維護、、網站導航

廣告

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)

成都網站建設