建站
咨詢
售后
建站咨詢
新聞中心
MySQL數(shù)據(jù)庫(kù)是現(xiàn)代應(yīng)用程序中使用最廣泛的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)之一。其高效性和可擴(kuò)展性使其成為開發(fā)者們的首選。然而，隨著數(shù)據(jù)庫(kù)中數(shù)據(jù)量的不斷增加，許多開發(fā)者會(huì)發(fā)現(xiàn)性能會(huì)變得越來越慢。這時(shí)，建立索引就成為了優(yōu)化MySQL數(shù)據(jù)庫(kù)性能的關(guān)鍵。本篇文章將指導(dǎo)你如何正確地建立索引，以充分利用MySQL的性能。
成都創(chuàng)新互聯(lián)公司服務(wù)項(xiàng)目包括長(zhǎng)汀網(wǎng)站建設(shè)、長(zhǎng)汀網(wǎng)站制作、長(zhǎng)汀網(wǎng)頁(yè)制作以及長(zhǎng)汀網(wǎng)絡(luò)營(yíng)銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，長(zhǎng)汀網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到長(zhǎng)汀省份的部分城市，未來相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！
1. 索引的概念
讓我們來理解一下索引的概念。索引可以看作是一本書的目錄，能夠幫助我們更快地找到特定的信息。在數(shù)據(jù)庫(kù)中，索引指向數(shù)據(jù)表中特定列的位置，允許快速查找和排序數(shù)據(jù)。無需遍歷整個(gè)數(shù)據(jù)表，只需在索引中搜索就可以找到需要的數(shù)據(jù)。
2. 建立索引的好處
建立索引的好處是顯而易見的。它可以提高查詢速度。如果你有大量的數(shù)據(jù)，沒有索引的查詢就會(huì)變得非常耗時(shí)。相反，有了索引，則可以僅在索引上進(jìn)行查詢，從而減少了不必要的搜索，大大加快了查詢速度。
建立索引可以加速數(shù)據(jù)的排序。如果沒有索引，則需要遍歷整個(gè)數(shù)據(jù)表并逐個(gè)比較記錄的值。而有了索引，可以直接在索引上進(jìn)行排序。
此外，索引還可以提高數(shù)據(jù)庫(kù)的整體性能。通過加快查詢速度和排序速度，可以為應(yīng)用程序節(jié)省大量的時(shí)間和資源。
3. 如何建立索引
現(xiàn)在，我們來具體了解如何建立索引。雖然建立索引很容易，但是錯(cuò)誤的設(shè)計(jì)和實(shí)現(xiàn)會(huì)產(chǎn)生不利影響。因此，需要注意以下幾點(diǎn)：
（1）選擇正確的列建立索引
根據(jù)你的數(shù)據(jù)表中最常查詢的列建立索引是更好的選擇。這些列通常是那些在WHERE語(yǔ)句中出現(xiàn)的列。如果你有多個(gè)常用查詢的列，則需要根據(jù)你的查詢模式來判斷利用哪些列建立索引。
（2）避免過度索引
雖然索引可以提高查詢速度，但是過于頻繁的索引也會(huì)降低數(shù)據(jù)庫(kù)的性能。每個(gè)索引都需要額外的空間和時(shí)間來維護(hù)，特別是在數(shù)據(jù)表的寫入過程中。因此，要注意合理選擇需要建立索引的列。
（3）使用復(fù)合索引
如果你需要同時(shí)查詢多個(gè)列，那么可以使用復(fù)合索引。復(fù)合索引指的是同時(shí)在多個(gè)列上建立索引，從而優(yōu)化多列查詢的速度。與單列索引相比，復(fù)合索引大大減少了數(shù)據(jù)庫(kù)的負(fù)擔(dān)。
（4）避免在大文本/二進(jìn)制列上建立索引
在大文本、二進(jìn)制列上建立索引需要大量的空間和時(shí)間，且無法提高查詢速度。因此，盡可能避免在這些列上建立索引。
（5）使用前綴索引
如果你需要在長(zhǎng)文本列上建立索引，那么可以使用前綴索引。前綴索引只索引字符串的前面部分，從而節(jié)省空間和時(shí)間。
（6）避免重復(fù)的數(shù)據(jù)
重復(fù)的數(shù)據(jù)在索引中也需要占用額外的空間，因此盡可能避免使用重復(fù)數(shù)據(jù)。如果需要使用，請(qǐng)考慮建立唯一索引。
4. 如何優(yōu)化索引
通過上述建立索引的方法可以大大提高M(jìn)ySQL數(shù)據(jù)庫(kù)的性能。然而，在實(shí)踐中，只是簡(jiǎn)單地建立索引是不夠的，你還需要優(yōu)化它們以便充分利用索引在MySQL中的功效。以下是一些優(yōu)化建立索引的方法：
（1）使用EXPLN命令來檢查查詢
在執(zhí)行查詢之前，可以使用EXPLN命令來檢查MySQL如何處理查詢。它可以幫助你了解查詢?nèi)绾问褂盟饕⑹褂媚膫€(gè)索引以及是否進(jìn)行全表掃描。這樣可以發(fā)現(xiàn)索引的不足之處，并嘗試添加新的索引。
（2）使用MySQL的Profiler來監(jiān)控查詢性能
MySQL的Profiler是一個(gè)有用的工具，可以幫助你監(jiān)控查詢性能并分析查詢速度。它可以告訴你哪些查詢使用了索引，哪些查詢需要改進(jìn)。通過定期使用Profiler來分析數(shù)據(jù)庫(kù)的性能，可以幫助你診斷問題并進(jìn)行調(diào)整。
（3）定期優(yōu)化索引
需要注意的是，索引并非一成不變。隨著時(shí)間的推移，數(shù)據(jù)表中的數(shù)據(jù)會(huì)發(fā)生變化，因此需要定期優(yōu)化索引。定期執(zhí)行OPTIMIZE TABLE命令可以清理垃圾數(shù)據(jù)并重新構(gòu)建索引，從而使索引更加高效。
建立索引是優(yōu)化MySQL數(shù)據(jù)庫(kù)性能的關(guān)鍵。正確地建立索引可以極大地加快查詢速度、排序速度和整體性能。通過正確地選擇要索引的列、避免過度索引、使用復(fù)合索引等優(yōu)化技術(shù)，可以更大限度地發(fā)揮索引的作用，從而提高M(jìn)ySQL的性能。
相關(guān)問題拓展閱讀：
- 如何創(chuàng)建mysql索引以及索引的優(yōu)缺點(diǎn)
如何創(chuàng)建mysql索引以及索引的優(yōu)缺點(diǎn)
在滿足語(yǔ)句需求的情況下，盡量少的訪問資源是數(shù)據(jù)庫(kù)設(shè)計(jì)的重要原則，這和執(zhí)行的 SQL 有直接的關(guān)系，索引問題又是 SQL 問題中出現(xiàn)頻率更高的，常見的索引問題包括：無索引（失效）、隱式轉(zhuǎn)換。
1. SQL 執(zhí)行流程看一個(gè)問題，在下面這個(gè)表 T 中，如果我要執(zhí)行 select * from T where k between 3 and 5; 需要執(zhí)行幾次樹的搜索操作，會(huì)掃描多少行？mysql> create table T ( -> ID int primary key, -> k int NOT NULL DEFAULT 0, -> s varchar(16) NOT NULL DEFAULT ”, -> index k(k)) -> engine=InnoDB;mysql> insert into T values(100,1, ‘a(chǎn)a’),(200,2,’bb’),\ (300,3,’cc’),(500,5,’ee’),(600,6,’ff’羨緩),(700,7,’gg’);
這分別是 ID 字段索引樹、k 字段索引樹。
這條 SQL 語(yǔ)句的執(zhí)行流程：
1. 在 k 索引樹上找到 k=3，獲得 ID=3002. 回表到 ID 索引樹查找 ID=300 的記錄，對(duì)應(yīng) R33. 在 k 索引樹找到下一個(gè)值 k=5，ID=5004. 再回到 ID 索引樹找到對(duì)應(yīng) ID=500 的 R4
5. 在 k 索引樹去下一個(gè)值 k=6，不符合條件，循環(huán)結(jié)束
這個(gè)過程讀取了 k 索引樹的三條記錄，回表了兩次。因?yàn)椴樵兘Y(jié)果所需要的數(shù)據(jù)只在主鍵索引上有，所以必須得回表。所以，我們?cè)撊绾瓮ㄟ^優(yōu)化索引，來避免回表呢？
2. 常見索引優(yōu)化2.1 覆蓋索引覆蓋索引，換言之就是索引要覆蓋我們的查詢請(qǐng)求，無需回表。
如果執(zhí)行的語(yǔ)句是 select ID from T wherek between 3 and 5;，這樣的話因?yàn)?ID 的值在 k 索引樹上，就不需要回表了。
覆蓋索引可以減少樹的搜索次數(shù)，顯著提升查詢性能，是常用的性能優(yōu)化手段。宴派絕
但是，維護(hù)索引是有代價(jià)的，所以在建立冗余索引來支持覆蓋索引時(shí)要權(quán)衡利弊。
2.2 最左前綴原則
B+ 樹的數(shù)據(jù)項(xiàng)是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，比如 (name,sex，age) 的時(shí)候，B+ 樹是按照從左到右的順序來建立搜索樹的，當(dāng) (張三,F,26) 這樣的數(shù)據(jù)來檢索的時(shí)候，B+ 樹會(huì)優(yōu)先比較 name 來確定下一步的檢索晌姿方向，如果 name 相同再依次比較 sex 和 age，最后得到檢索的數(shù)據(jù)。
# 有這樣一個(gè)表 P
mysql> create table P (id int primary key, name varchar(10) not null, sex varchar(1), age int, index tl(name,sex,age)) engine=IInnoDB;
mysql> insert into P values(1,’張三’,’F’,26),(2,’張三’,’M’,27),(3,’李四’,’F’,28),(4,’烏茲’,’F’,22),(5,’張三’,’M’,21),(6,’王五’,’M’,28);
# 下面的語(yǔ)句結(jié)果相同
mysql> select * from P where name=’張三’ and sex=’F’; ## A1
mysql> select * from P where sex=’F’ and age=26;## A2
# explain 看一下
mysql> explain select * from P where name=’張三’ and sex=’F’;
+—-++++——+-+——+++——+++
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra|
+—-++++——+-+——+++——+++
| 1 | SIMPLE | P | NULL| ref | tl| tl || const,const | 1 | 100.00 | Using index |
+—-++++——+-+——+++——+++
mysql> explain select * from P where sex=’F’ and age=26;
+—-+++++-+——++——+——+++
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+++++-+——++——+——+++
| 1 | SIMPLE | P | NULL| index | NULL| tl || NULL | 6 | 16.67 | Using where; Using index |
+—-+++++-+——++——+——+++
可以清楚的看到，A1 使用 tl 索引，A2 進(jìn)行了全表掃描，雖然 A2 的兩個(gè)條件都在 tl 索引中出現(xiàn)，但是沒有使用到 name 列，不符合最左前綴原則，無法使用索引。所以在建立聯(lián)合索引的時(shí)候，如何安排索引內(nèi)的字段排序是關(guān)鍵。評(píng)估標(biāo)準(zhǔn)是索引的復(fù)用能力，因?yàn)橹С肿钭笄熬Y，所以當(dāng)建立（a，b）這個(gè)聯(lián)合索引之后，就不需要給 a 單獨(dú)建立索引。原則上，如果通過調(diào)整順序，可以少維護(hù)一個(gè)索引，那么這個(gè)順序往往就是需要優(yōu)先考慮采用的。上面這個(gè)例子中，如果查詢條件里只有 b，就是沒法利用（a，b）這個(gè)聯(lián)合索引的，這時(shí)候就不得不維護(hù)另一個(gè)索引，也就是說要同時(shí)維護(hù)（a，b）、（b）兩個(gè)索引。這樣的話，就需要考慮空間占用了，比如，name 和 age 的聯(lián)合索引，name 字段比 age 字段占用空間大，所以創(chuàng)建（name，age）聯(lián)合索引和（age）索引占用空間是要小于（age，name）、(name）索引的。
2.3 索引下推
以人員表的聯(lián)合索引（name, age）為例。如果現(xiàn)在有一個(gè)需求：檢索出表中“名字之一個(gè)字是張，而且年齡是26歲的所有男性”。那么，SQL 語(yǔ)句是這么寫的mysql> select * from tuser where name like ‘張%’ and age=26 and sex=M;
通過最左前綴索引規(guī)則，會(huì)找到 ID1，然后需要判斷其他條件是否滿足在 MySQL 5.6 之前，只能從 ID1 開始一個(gè)個(gè)回表。到主鍵索引上找出數(shù)據(jù)行，再對(duì)比字段值。而 MySQL 5.6 引入的索引下推優(yōu)化(index condition pushdown)，可以在索引遍歷過程中，對(duì)索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數(shù)。這樣，減少了回表次數(shù)和之后再次過濾的工作量，明顯提高檢索速度。
2.4 隱式類型轉(zhuǎn)化
隱式類型轉(zhuǎn)化主要原因是，表結(jié)構(gòu)中指定的數(shù)據(jù)類型與傳入的數(shù)據(jù)類型不同，導(dǎo)致索引無法使用。所以有兩種方案：
修改表結(jié)構(gòu)，修改字段數(shù)據(jù)類型。
修改應(yīng)用，將應(yīng)用中傳入的字符類型改為與表結(jié)構(gòu)相同類型。
3. 為什么會(huì)選錯(cuò)索引3.1 優(yōu)化器選擇索引是優(yōu)化器的工作，其目的是找到一個(gè)更優(yōu)的執(zhí)行方案，用最小的代價(jià)去執(zhí)行語(yǔ)句。在數(shù)據(jù)庫(kù)中，掃描行數(shù)是影響執(zhí)行代價(jià)的因素之一。掃描的行數(shù)越少，意味著訪問磁盤數(shù)據(jù)的次數(shù)越少，消耗的 CPU 資源越少。當(dāng)然，掃描行數(shù)并不是唯一的判斷標(biāo)準(zhǔn)，優(yōu)化器還會(huì)結(jié)合是否使用臨時(shí)表、是否排序等因素進(jìn)行綜合判斷。
3.2 掃描行數(shù)
MySQL 在真正開始執(zhí)行語(yǔ)句之前，并不能精確的知道滿足這個(gè)條件的記錄有多少條，只能通過索引的區(qū)分度來判斷。顯然，一個(gè)索引上不同的值越多，索引的區(qū)分度就越好，而一個(gè)索引上不同值的個(gè)數(shù)我們稱為“基數(shù)”，也就是說，這個(gè)基數(shù)越大，索引的區(qū)分度越好。# 通過 show index 方法，查看索引的基數(shù)mysql> show index from t;++++++++++——+++-+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |++++++++++——+++-+| t || PRIMARY || id| A|| NULL | NULL | | REE || || t || a|| a| A|| NULL | NULL | YES | REE || || t || b|| b| A|| NULL | NULL | YES | REE || |++++++++++——+++-+
MySQL 使用采樣統(tǒng)計(jì)方法來估算基數(shù)：采樣統(tǒng)計(jì)的時(shí)候，InnoDB 默認(rèn)會(huì)選擇 N 個(gè)數(shù)據(jù)頁(yè)，統(tǒng)計(jì)這些頁(yè)面上的不同值，得到一個(gè)平均值，然后乘以這個(gè)索引的頁(yè)面數(shù)，就得到了這個(gè)索引的基數(shù)。而數(shù)據(jù)表是會(huì)持續(xù)更新的，索引統(tǒng)計(jì)信息也不會(huì)固定不變。所以，當(dāng)變更的數(shù)據(jù)行數(shù)超過 1/M 的時(shí)候，會(huì)自動(dòng)觸發(fā)重新做一次索引統(tǒng)計(jì)。
在 MySQL 中，有兩種存儲(chǔ)索引統(tǒng)計(jì)的方式，可以通過設(shè)置參數(shù) innodb_stats_persistent 的值來選擇：
on 表示統(tǒng)計(jì)信息會(huì)持久化存儲(chǔ)。默認(rèn) N = 20，M = 10。
off 表示統(tǒng)計(jì)信息只存儲(chǔ)在內(nèi)存中。默認(rèn) N = 8，M = 16。
由于是采樣統(tǒng)計(jì)，所以不管 N 是 20 還是 8，這個(gè)基數(shù)都很容易不準(zhǔn)確。所以，冤有頭債有主，MySQL 選錯(cuò)索引，還得歸咎到?jīng)]能準(zhǔn)確地判斷出掃描行數(shù)。
可以用 yze table 來重新統(tǒng)計(jì)索引信息，進(jìn)行修正。
ANAZE TABLE tbl_name …
關(guān)于mysql數(shù)據(jù)庫(kù)建立索引的介紹到此就結(jié)束了，不知道你從中找到你需要的信息了嗎 ？如果你還想了解更多這方面的信息，記得收藏關(guān)注本站。
香港服務(wù)器選創(chuàng)新互聯(lián)，2H2G首月10元開通。
創(chuàng)新互聯(lián)（www.cdcxhl.com）互聯(lián)網(wǎng)服務(wù)提供商,擁有超過10年的服務(wù)器租用、服務(wù)器托管、云服務(wù)器、虛擬主機(jī)、網(wǎng)站系統(tǒng)開發(fā)經(jīng)驗(yàn)。專業(yè)提供云主機(jī)、虛擬主機(jī)、域名注冊(cè)、VPS主機(jī)、云服務(wù)器、香港云服務(wù)器、免備案服務(wù)器等。
網(wǎng)頁(yè)名稱：如何優(yōu)化MySQL數(shù)據(jù)庫(kù)性能：建立索引指南(mysql數(shù)據(jù)庫(kù)建立索引)
網(wǎng)頁(yè)鏈接：http://www.dlmjj.cn/article/djoieei.html
