程式設計師應該如何構建高性能Mysql索引

2020-12-17 Java技術架構

索引應該是各種資料庫優化方案之中成本最低，見效最快的解決方案了，現在就重點講下：企業級應用如何構建高效索引，以及應該注意些什麼。

索引類型

1、B-tree索引

Myisam和innodb中，默認用B-tree索引，是一種平衡樹。可以抽象一下---B-tree系統，可理解為"排好序的快速查找結構」

2、hash索引

在memory表裡，默認是hash索引， hash的理論查詢時間複雜度為O(1)

既然hash的查找如此高效,為什麼不都用hash索引？

1:hash函數計算後的結果，是隨機的，沒有辦法對範圍查詢進行優化.

2: 無法利用前綴索引. 比如 在btree中, field列的值「hellopworld」,並加索引

查詢 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引. (左前綴索引)

因為hash(『helloword』),和hash(『hello』),兩者的關係仍為隨機

3: 排序也無法優化.

4: 必須回行。就是說通過索引拿到數據位置，必須回到表中取數據

以下是建立索引時的注意事項

1.索引不是越多越好，單張表中索引數量不宜超過8個合理創建聯合索引，(a，b，c) 相當於 (a) 、(a，b) 、(a，b，c)禁冗餘索引

索引是雙刃劍，會增加維護負擔，增大IO壓力。(a,b,c)、(a,b)，後者為冗餘索引。可以利用前綴索引來達到加速的目的，減輕維護負擔

2.SELECT只獲取必要的欄位，不建議使 SELECT *

好處：減少網絡帶寬消耗；能有效利用覆蓋索引，表結構變更對程序基本無影響

3.合理使用覆蓋索引減少IO，避免排序

4.索引覆蓋是指如果查詢的列恰好是索引的一部分,那麼查詢只需要在索引文件上進行,不需要回行到磁碟再找數據。這種查詢速度非常快，稱為"索引覆蓋」

理想的索引：1:查詢頻繁 2:區分度高 3:長度小 4: 儘量能覆蓋常用查詢欄位

註：主鍵查詢是天然的覆蓋索引

5.儘量避免用NULL()

原因: NULL不利於索引，要用特殊的字節來標註。在磁碟上佔據的空間其實更大。

6.索引的主鍵值，應儘量是連續增長的值，而不是要是隨機值(不要用隨機字符串或UUID)，否則會造成大量的頁分裂與頁移動

7.不對過的VARCHAR欄位建立索引。建議優先考慮前綴索引，或添加CRC32或MD5偽列並建立索引

8.在常用的列上加上適當索引

例: where cat_id=3 and price>100 ; //查詢第3個欄目,100元以上的商品

誤: cat_id上,和, price上都加上索引.

錯: 只能用上cat_id或Price索引,因為是獨立的索引,同時只能用上1個

9.多列索引上,索引發揮作用,需要滿足左前綴要求

以 index(a,b,c) 為例

10.選擇區分度大的列建立索引。

組合索引中，區分度大的欄位放在最前重要SQL必須被索引：update、delete的where條件列、order by、group by、distinct欄位、多表join欄位。

11.不建議使用%前導查詢，例如：like「%abc」，無法利用到索引。

12.不建議使用負向查詢，例如notin、!=、notlike。使用EXPLAIN判斷SQL語句是否合理使用索引，儘量避免extra列出現：Using File Sort、Using Temporary

相關焦點

  • 面對MySQL 查詢索引失效,程式設計師的六大優化技巧!
    第三步分析器：mysql在執行之前，首先會對sql語句做詞法解析和語法解析，以確定你要做什麼，並會識別語句中的關鍵詞，比如select，order by等，以及解析sql語法是否正確等。經過分析器，mysql就知道你要做什麼了。SQL 在執行的過程中經過優化器，並由優化器生成 SQL 的執行計劃。
  • Mysql索引:表在哪些情況下需要加索引,應該加哪種索引,你懂嗎
    在前面兩篇文章中我們了解到了mysql索引的重要性，那麼我們的數據表到底在哪種情況下才適合添加索引呢？索引有好多種我們應該添加哪種呢？今天我們就來一起聊聊這個問題。0.00 秒，表示執行時間不超過 10 毫秒從這個測試可以得出一個結果，mysql的索引可以有效地為聚合函數提高效率。
  • 什麼是 MySQL 索引?
    你會想為如此簡單的事情做全表掃描效率欠佳——資料庫是不是應該更聰明一點呢？這就像用人眼從頭到尾瀏覽整張表，很慢也不優雅，「索引」派上用場的時候到了，使用索引的全部意義就是：通過縮小一張表中需要查詢的記錄/行的數目來加快搜索的速度。
  • MySQL,多列索引
    這樣一來 最好的情況下也只能是「一星」索引，其性能比起真正最優的索引可能差幾個數量級。有時如果無法設計一個「三星」索引，那麼不如忽略掉WHERE子句，集中精力優化索引列的順序，或者創建一個全覆蓋索引。在多個列上建立獨立的單列索引大部分情況下並不能提高MySQL的查詢性能。
  • 如何查看 sql 查詢是否用到索引 ( mysql )
    如果將主鍵置於where列表中，mysql就能將該查詢轉換為一個常量eq_ref 唯一性索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配。常見於主鍵索引和唯一索引 區別於const eq_ref用於聯表查詢的情況ref 非唯一索引掃描，返回匹配某個單獨值的所有行，本質上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而，他可能會找到多個符合條件的行，所以他應該屬於查找和掃描的混合體range 只檢索給定範圍的行，使用一個索引來選擇行，一般是在where中出現between、<、>、in等查詢，
  • 作為一個Java 程式設計師 你應該會什麼
    一，JAVA架構師1、語法：Java 程式設計師必須比較熟悉語法，在寫代碼的時候IDE 的編輯器對某一行報錯應該能夠根據報錯信息 知道是什麼樣的語法錯誤並且知道任何修正。6、管理：Java 程式設計師必須熟悉使用ant 完成工程管理的常用任務，例如工程編譯、生成javadoc、生成jar、版本控制、自動測試。7、排錯：應該可以根據異常信息比較快速的定位問題的原因和大致位置。8、思想：必須掌握OOP 的主要要求，這樣使用Java 開發的系統才能是真正的Java 系統。
  • 基於MySQL的高性能資料庫應用開發
    首頁 > 語言 > 關鍵詞 > 資料庫最新資訊 > 正文 基於MySQL的高性能資料庫應用開發
  • MySQL的索引
    mysql>那麼這些記錄已經按照主鍵值的大小串聯成一個單向鍊表了，如圖所示：此時我們再來插入一條記錄：mysql> INSERT INTO index_demo VALUES(4, 4, 'a');Query OK, 1 row affected (0.00 sec)mysql>因為頁10最多只能放3條記錄，所以我們不得不再分配一個新頁：
  • 你說你熟悉MySQL索引,給我講講創建索引時應該注意什麼?
    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;insert into T values
  • Mysql中in到底走不走索引?
    當前找工作，對於一定年限的軟體開發者，都會被問到索引的相關問題，最近我發現對於mysql資料庫中in關鍵字走不走索引，有很多面試者回答的都不貼切
  • MySQL 的索引是什麼?怎麼優化?
    通常來說， 我們的查詢不應該出現 ALL 類型的查詢，因為這樣的查詢在數據量大的情況下，對資料庫的性能是巨大的災難。 如一個查詢是 ALL 類型查詢， 那麼一般來說可以對相應的欄位添加索引來避免。而 index 類型的查詢雖然不是全表掃描，但是它掃描了所有的索引，因此比 ALL 類型的稍快.後面的幾種類型都是利用了索引來查詢數據，因此可以過濾部分或大部分數據，因此查詢效率就比較高了。5.possible_keys它表示 mysql 在查詢時，可能使用到的索引。
  • mysql索引的使用
    黑馬程式設計師視頻庫播妞微信號：boniu120傳智播客旗下網際網路資訊、學習資源免費分享平臺
  • 圖解MySQL索引:如何正確使用索引?
    工作中可怕的是沒有建立索引，比這更可怕的是建好了索引又沒有使用到。本文將圍繞著如何優雅地使用索引，圖文並茂地和大家一起探討索引的正確打開姿勢，不談底層原理，只求工作實戰。1. 索引的特點page之間是雙鍊表形式，而每個page內部的數據則是單鍊表形式存在。
  • 初中級必須知道的MYSQL經典面試題
    資料庫作為一位JAVA工程師，不論你是初級程式設計師，還是中級程式設計師，高級程式設計師，MYSQL面試題是一定會問的，這個是考驗你對資料庫了解的深度和廣度。在我們平時面試過程中，那些mysql面試題是經常被問到的呢？你有哪些問題是不會的呢？
  • MySQL InnoDB 索引原理
    頁(page)頁是InnoDB存儲引擎的最小管理單位，每頁大小默認是16KB，從InnoDB 1.2.x版本開始，可以利用innodb_page_size來改變頁size，但是改變只能在初始化InnoDB實例前進行修改，之後便無法進行修改，除非mysqldump導出創建新庫
  • MySQL索引與索引優化
    MySQL的基本架構示意圖MySQL索引就是用於優化器上。索引：MySQL官方對於索引的定義為：索引是幫助MySQL高效獲取數據的數據結構。即可以理解為：索引是數據結構。索引是對資料庫表中一個或多個列的值進行排序的結構，建立索引有助於快速獲取信息。你也可以這樣理解：索引就是加快檢索表中數據的方法。資料庫的索引類似於書籍的索引。在書籍中，索引允許用戶不必翻閱完整個書就能迅速地找到所需要的信息。在資料庫中，索引也允許資料庫程序迅速地找到表中的數據，而不必掃描整個資料庫。
  • mysql索引分類及原理
    ('col');3、普通索引：用表中的普通列構建的索引，沒有任何限制ALTER TABLE 'table_name' ADD INDEX index_name('col');4、全文索引：用大文本對象的列構建的索引（下一部分會講解）ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index
  • MySQL 的索引是如何工作的?10 分鐘講清楚!
    （給數據分析與開發加星標，提升數據技能）來源：Harveyfordba.com/spend-10-min-to-understand-how-mysql-use-index.html
  • Mysql索引:深入理解InnoDb聚集索引與MyisAm非聚集索引值得一看
    （文章最後解答）資料庫兩大神器：索引+鎖，上篇中我們將了mysql的索引原理以及算法（如果不清楚的一定要看看，對理解這篇文章有幫助），這篇我們主要聊聊B+樹數據結構的索引。資料庫中B+樹索引的高度一般都是2~4層，所以我們在檢索一條數據的時候也只需要2~4次的IO即可。
  • MySQL操作之索引創建與刪除
    BTree樹的索引分類聚集索引，聚集索引一般是主鍵列輔助索引，輔助索引輔助聚集索引聚集索引生成過程>輔助索引，葉子結點只存儲索引列的有序值聚集索引，葉子結點存儲的是有序的整行數據MySQL的表數據存儲是聚集索引組織表輔助索引細分單列輔助索引