當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

分度值1g是什么意思_都什么年代了，还有人在 MySQL 索引上碰一鼻子灰？

發布時間：2024/1/23 数据库 25 豆豆

生活随笔收集整理的這篇文章主要介紹了分度值1g是什么意思_都什么年代了，还有人在 MySQL 索引上碰一鼻子灰？小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

前言

索引有很多種，hash索引，B樹索引，B+樹索引，全文索引等。Mysql支持多種存儲引擎，多種存儲引擎對索引的支持也各不相同。本文探究Mysql為什么使用B+樹來作為索引的數據結構，索引的原理已經Sql中索引的優化。

Mysql官方對索引的定義是：索引（Index）是幫助Mysql高效獲取數據的數據結構。提取句子主干就是：索引是數據結構。

索引的原理

索引的目的

索引的目的在于提高查詢或檢索效率。例如我們要在字典中查詢“mysql”這個單詞，是不是先要查詢m開頭的單詞表，然后在查詢第二個字母為y的單詞，然后縮小范圍繼續找，知道找到“mysql”這個單詞為止或者查無此詞。這就好像我們沿著一個樹從樹根開始找，沿著主干，樹干，到最后的末梢，走了其中的一條路徑。這比一個查詢一個鏈表的結構，從頭找到尾，在大多數情況下，效率要高得多。

Mysql的索引為什么是B+樹

為什么不用普通的二叉樹，這里就不必多說了，因為對于大的數據量，二叉樹的高度太高，索引的效率低下。這里主要說明為什么不用B樹（B-樹就是B樹），而是用B+樹。

B樹（B-樹）介紹

我們都知道二叉樹查詢的時間復雜度為Ｏ(logN)，查詢效率已經夠高了，但為什么還要有B樹和B+樹呢？答案是磁盤IO。我們都知道，IO操作的效率很低，當有存儲的有很大的數據量，查詢的時候，我們不可能把全部數據都加載到內存中，只能逐一加載磁盤頁，每個磁盤頁對應樹的節點，造成大量的磁盤IO操作（最壞情況下，磁盤IO操作次數是樹的高度），平衡二叉樹由于樹的高度太大造成磁盤IO讀寫過于頻繁，從而導致效率低下，所以多路查找樹-B樹/B+樹應運而生。

下面是一個三階的B樹（實際中節點元素很多）

B樹有以下特點：在一個節點中存放著數據和指針，且相互間隔
在同一個節點中，key是增序的
如果一個節點最左邊的指針不為空，則它指定的節點左右的key小于最左邊的key。中間的指針指向的節點的key位于相鄰兩個key的中間。
B樹中不同節點存放的key和指針可能數量不一致，但是每個節點的域和上限是一致的，所以在實現中B樹往往對每個節點申請同等大小的空間
每個非葉子節點由n-1個key和n個指針組成，其中d<=n<=2d

B+樹

B+樹有以下特點：

內節點不存儲data，只存儲key和指針，葉子結點不存儲指針，只存儲key和data
內節點和葉子結點的大小不同，因為存儲的東西不同
每個非葉子結點的指針上限為2d而不是2d+1
因為節點內部沒有data，所以有更多的空間放key，所以B+樹的出度一般比B樹要大，而對于一定的數據，出度大的話，樹的深度就小，所以B+樹的檢索效率比B樹高

為什么B+樹比B樹更適合Mysql索引

B+樹的磁盤讀取代價低：因為B+樹的非葉子結點沒有存儲數據，所以如果把所有同一內部節點的關鍵字存放在同一盤塊中，那么盤塊所能容納的關鍵字數量也越多。一次性讀內存中的需要查找的關鍵字也就越多。相對來說IO讀寫次數也就降低了。
B+樹的查詢效率更穩定：由于B+樹的分支結點并不是最終指向文件內容的結點，只是葉子結點的索引，所以任意關鍵字的查找都必須從根節點走向分支結點，查詢路徑相同。但B樹的分支結點保存有數據，所以查詢路徑可能不同。
B+樹便于執行掃庫操作：由于B+樹的數據都存儲在葉子節點上，分支節點均為索引，方便掃庫，只需掃一遍葉子即可。但是B樹在分支節點上都保存著數據，要找到具體的順序數據，需要執行一次中序遍歷來查找

Mysql的索引實現

我們知道Mysql有兩種常用的存儲引擎，MyISAM和InnoDB，這兩種存儲引擎對索引的實現方式是不同的。

MyISAM索引實現

MyISAM使用B+樹作為索引的結構，葉子結點的data域存放的是數據記錄的地址。

上圖中是以Col1作為主鍵的MyISAM主索引的示意圖。可以看到，組下面一層葉子結點的data域存放的是數據記錄的地址。如果我們在字段Col2上建一個輔助索引，那么索引的結構如下：

MyISAM索引檢索算法是這樣的，首先按照B+樹的搜索算法查詢索引，如果指定的key存在，則取出data域的值，然后用data域的地址查詢數據記錄。MyISAM的索引方式也叫“非聚集的”，跟InnoDB的“聚集索引”相區分，因為數據記錄和索引不在一起。InnoDB索引實現

InnoDB的索引實現方式與MyISAM的索引實現方式的區別有兩個：

第一，InnoDB的數據文件本身就是索引文件。在InnoDB中，數據文件本身就是按B+樹組織的一個索引結構，而且是主索引結構。數據和索引在一起，葉子結點保存了完整的數據記錄，這種索引叫做聚集索引。因為InnoDB的數據文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識數據記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含字段作為主鍵，這個字段長度為6個字節，類型為長整形。

第二，InnoDB輔助索引的data域存儲的是相應記錄主鍵的值而不是地址。如圖，下圖是定義在Col3上的一個輔助索引的示意圖。葉子結點存儲了col3的值和對應的主鍵col1的值。

索引優化

墻裂建議使用自增主鍵

在使用InnoDB作為存儲引擎時，如果沒有特殊需要，請永遠是用一個與業務無關的自增字段作為主鍵，而且這個字段長度不宜過大。為什么？InnoDB使用聚集索引，數據記錄本身存放在主索引（B+樹）的葉子結點上，這就要求同一個葉子結點（大小為一個內存頁或磁盤頁）的數據記錄按主鍵順序存放，每當一條新的記錄插入時，mysql會根據其主鍵將其插入適當的節點和位置，如果頁面達到裝載因子（InnoDB默認為15/16），則開辟一個新的頁（節點）。如果使用自增主鍵，那么每次插入新的記錄，記錄就會順序插入到當前節點的下一個位置。這樣就會形成一個緊湊的索引結構，每次插入不需要移動已有數據，因此效率很高。如下圖：

如果使用非自增主鍵（例如身份證號或學號這種無序字符串），每次插入主鍵近似隨機，每次記錄都要插入到現有索引頁的中間的某個位置，這時不得不移動元素來完成插入，增加了開銷。如下圖：

索引的最左前綴原則

聯合索引：mysql可以將多個列按照順序作為一個索引，這種索引叫做聯合索引。

索引的最左匹配原則是：假如索引列分別為A，B，C，順序也是A，B，C，那么：

查詢的時候，如果查詢【A】，【A，B】，【A，B，C】，可以使用索引查詢。
如果查詢的時候，查詢【A，C】，由于中間缺失了B，那么C這個索引是用不到的，只能用到A索引。
如果查詢的時候，查詢【B】，【B，C】或【C】，由于缺失了最左前綴A，那么是用不到這個聯合索引的，除非有其他索引。
如果查詢的時候使用范圍查詢，并且是最左前綴，那么可以用到索引，但是范圍后面的字段無法用到索引。

這個原則可以結合索引的原理來理解：Mysql索引是B+樹這種復合結構，當索引是聯合索引，比如【name,age,sex】時，B+樹是按照從左到右的順序建立索引樹的。當（張三，20，M）這樣的數據來檢索時，B+樹會優先根據name來確定下一步的搜索方向，如果name相同再比較name和sex，最后得到檢索的數據。但當（20，M）這樣的數據來的時候，mysql就不知道該查哪個節點，因為建立索引的時候，name就是第一個比較因子，必須先根據name去確定下一步去哪里搜索。當（張三，M）這樣的數據來時，可以根據name是“張三”，來確定下一步的搜索，然后再去匹配性別是“M”的數據，因此只能用到聯合索引中name這個索引。

其他原則

1、盡量選擇區分度高的列作為索引，區分度公式：count(distinct col)/count(*)，表示字段不重復的比例，比例越大，我們掃描的記錄數就越少，唯一性的列的區分度為1。這就是為什么不建議在狀態，性別這樣區分度很小的列上建立索引的原因。

2、索引列在sql語句中不能參與運算，否則會導致索引失效。例如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數據表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。應該改成create_time = unix_timestamp(’2014-05-29’);

3、聯合索引比單個索引的性價比更高。例如，建立【A，B，C】這個聯合索引，相當于建立了【A】，【A,B】，【A，B，C】這三個索引。這就要求我們盡量的擴展索引而不是新建索引，具體情況還需具體分析。

4、頻繁進行查詢的字段應該新建索引，與其他表進行關聯的字段可以考慮新建索引，查詢中排序的字段可以考慮建立索引以提高排序的效率（這里舉個例子，很多時候查詢記錄希望按照創建時間倒序返回，通常有人會這樣做order by create_time desc，但是如果create_time不是索引，而這個表有自增主鍵id，那么order by id desc返回結果一樣，但是效率會提高）。

Mysql優化

導致sql執行慢的原因

1、硬件問題：如網絡速度慢，內存不足，I/O吞吐量小，磁盤空間滿了等。

2、沒有使用索引或者索引失效。

3、數據過多（分庫分表）。

4、服務器或參數設置不當。

分析解決慢sql方法

1、先觀察，開啟慢查詢日志，設置相應的閾值（比如超過3秒就是慢sql），再生產環境跑個一天，看看哪些sql比較慢。

2、explain和慢sql分析，比如sql語句寫的不好，沒有使用索引或者索引失效，或者sql語句太過復雜，關聯查詢和嵌套子查詢太多等等。

3、Show Profile是比explain更近一步的執行細節，可以查詢到執行每一個SQL都干了什么事，這些事分別花了多少秒。

4、找DBA或者運維對Mysql進行服務器的參數調優。

配置優化

基本配置

innodb_buffer_pool_size：這是安裝完InnoDB后第一個應該設置的選項。緩沖池是數據和索引緩存的地方：這個值越大越好，這能保證你在大多數的讀取操作時使用的是內存而不是硬盤。典型的值是5-6GB(8GB內存)，20-25GB(32GB內存)，100-120GB(128GB內存)。
innodb_log_file_size：這是redo日志的大小。redo日志被用于確保寫操作快速而可靠并且在崩潰時恢復。一直到MySQL 5.5，redo日志的總尺寸被限定在4GB(默認可以有2個log文件)。這在MySQL5.6里被提高了。如果你知道你的應用程序需要頻繁的寫入數據并且你使用的時MySQL5.6，一開始就可以設置成4G。
max_connections：如果你經常看到'Too many connections'錯誤，是因為max_connections的值太低了因為應用程序沒有正確的關閉數據庫連接，你需要比默認的151連接數更大的值。max_connection值被設高了(例如1000或更高)之后一個主要缺陷是當服務器運行1000個或更高的活動事務時會變的沒有響應。

InnoDB配置

innodb_file_per_table：這項設置告知InnoDB是否需要將所有表的數據和索引存放在共享表空間里（innodb_file_per_table = OFF）或者為每張表的數據單獨放在一個.ibd文件（innodb_file_per_table = ON）。每張表一個文件允許你在drop、truncate或者rebuild表時回收磁盤空間。這對于一些高級特性也是有必要的，比如數據壓縮。你不想讓每張表一個文件的主要場景是：有非常多的表（比如10k+）。
innodb_flush_log_at_trx_commit：默認值為1，表示InnoDB完全支持ACID特性。當你的主要關注點是數據安全的時候這個值是最合適的，比如在一個主節點上。但是對于磁盤（讀寫）速度較慢的系統，它會帶來很巨大的開銷，因為每次將改變flush到redo日志都需要額外的fsyncs。將它的值設置為2會導致不太可靠（reliable）因為提交的事務僅僅每秒才flush一次到redo日志，但對于一些場景是可以接受的，比如對于主節點的備份節點這個值是可以接受。
innodb_flush_method：這項配置決定了數據和日志寫入硬盤的方式。一般來說，如果你有硬件RAID控制器，并且其獨立緩存采用write-back機制，并有著電池斷電保護，那么應該設置配置為O_DIRECT；否則，大多數情況下應將其設為fdatasync（默認值）
innodb_log_buffer_size：這項配置決定了為尚未執行的事務分配的緩存。其默認值（1MB）一般來說已經夠用了，但是如果你的事務中包含有二進制大對象或者大文本字段的話，這點緩存很快就會被填滿并觸發額外的I/O操作。

執行計劃Explain

準備數據

CREATE TABLE `user_info` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT,`name` VARCHAR(50) NOT NULL DEFAULT '',`age` INT(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `name_index` (`name`) )ENGINE = InnoDB DEFAULT CHARSET = utf8;INSERT INTO user_info (name, age) VALUES ('xys', 20); INSERT INTO user_info (name, age) VALUES ('a', 21); INSERT INTO user_info (name, age) VALUES ('b', 23); INSERT INTO user_info (name, age) VALUES ('c', 50); INSERT INTO user_info (name, age) VALUES ('d', 15); INSERT INTO user_info (name, age) VALUES ('e', 20); INSERT INTO user_info (name, age) VALUES ('f', 21); INSERT INTO user_info (name, age) VALUES ('g', 23); INSERT INTO user_info (name, age) VALUES ('h', 50); INSERT INTO user_info (name, age) VALUES ('i', 15);CREATE TABLE `order_info` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT,`user_id` BIGINT(20) DEFAULT NULL,`product_name` VARCHAR(50) NOT NULL DEFAULT '',`productor` VARCHAR(30) DEFAULT NULL,PRIMARY KEY (`id`),KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`) )ENGINE = InnoDB DEFAULT CHARSET = utf8;INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH'); INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL'); INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX'); INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH'); INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL'); INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA'); INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH'); INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH'); INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');

執行explain看看，索引使用情況在possible_keys、key和key_len這三列。

分析explain

id
id相同，執行順序由上而下

id不同，值越大越先執行

select_type

select_type總共有以下幾種類型：

1、SIMPLE：表示查詢不使用UNION或子查詢

2、PRIMARY：表示此查詢是最外層的查詢

3、SUBQUERY：表示此查詢是第一個查詢

4、UNION：表示此查詢是UNION第二或隨后的查詢 5、DEPENDENT UNION：UNION中的第二個或后面的查詢語句，取決于外面的查詢 6、UNION RESULT：UNION的結果 7、DEPENDENT SUBQUERY：子查詢中的第一個SELECT，取決于外面的查詢，即子查詢依賴于外面查詢的結果 8、DERIVED：衍生，表示導出表的SELECT

table table表示查詢涉及的表或衍生的表

id=1的table derived2表示是由id=2的u和o衍生出來的

type
type字段比較重要，它是判斷查詢是否高效的重要依據。
1、system：表中只有一條數據，這種類型是特殊的const類型
2、const：針對主鍵或唯一索引的等號條件進行掃描，最多只返回一條數據，查詢速度極快，因為它僅僅讀取一次即可。
3、eq_ref：此類型通常出現在多表join，表示對于前表的每一個結果，都只能匹配到后表的一行結果，且查詢的比較操作通常是=，查詢效率較高。
4、ref：此類型通常是多表join，針對非唯一索引，或者非主鍵索引，或者使用了最左前綴規則的索引。
5、range：表示使用索引范圍查詢，通過索引字段范圍獲取表中部分數據記錄，這個類型通常出現在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。
6、index：表示全索引掃描，和ALL類型類似，只不過All類型是全表掃描，index是掃描所有的索引，而不掃描數據。index 類型通常出現在：所要查詢的數據直接在索引樹中就可以獲取到,而不需要掃描數據。當是這種情況時，Extra 字段會顯示 Using index。
7、ALL：表示全表掃描，性能最差。當數據量大的時候，對數據庫會是巨大的災難。
possible_keys
它表示mysql在查詢時，可能使用到的索引。注意：有些索引即使在possible_keys中出現，也不表示真正的會用到，mysql 在查詢時具體使用了哪些索引，由 key 字段決定。
key
這是mysql在查詢時真正用到的索引
key_len
表示mysql使用索引的字節數，這個字段可以判斷組合索引是否完全被使用
ref
這個字段表示索引的哪一列被使用了，如果可能的話，會是一個常量。
rows
這也是判斷sql性能好壞的一個重要字段。mysql 查詢優化器根據統計信息，估算 sql要查找到結果集需要掃描讀取的數據行數。原則上來說,rows越小查詢效率越高。
extra
explain 中的很多額外的信息會在 extra 字段顯示，常見的有以下幾種：
1、using filesort ：表示 mysql 需額外的排序操作，不能通過索引順序達到排序效果。一般有 using filesort都建議優化去掉，因為這樣的查詢 cpu 資源消耗大。
2、using index：覆蓋索引掃描，表示查詢在索引樹中就可查找所需數據，不用掃描表數據文件，往往說明性能不錯。
3、using temporary：查詢有使用臨時表, 一般出現于排序，分組和多表 join 的情況，查詢效率不高，建議優化。
4、using where ：表明使用了where過濾。

總結

常見的索引原則：

選擇唯一性索引：唯一性索引的值是惟一的，可以更快速的通過該索引確定某條記錄。
為經常需要排序、分組和聯合操作的字段建立索引。
為常作為查詢條件的字段建立索引。
限制索引的數目：索引不是越多越好，太多的索引會使插入和更新變慢，因為需要維護索引樹。
盡量使用數據量少的索引，如果索引的值很長，那么查詢的速度會受到影響。
最左前綴匹配原則，是非常重要的原則。
盡量選擇區分度高的列作為索引，區分度的公式是表示字段不重復的比例。
索引列不能參與計算，保持列“干凈”：帶函數的查詢不參與索引。
刪除不再使用或很少使用的索引。
盡量的擴展索引，而不是新建索引，能使用組合索引就不要新建索引。

作者：堅持就是勝利
鏈接：https://juejin.im/post/5d67702cf265da03f333664c

總結

以上是生活随笔為你收集整理的分度值1g是什么意思_都什么年代了，还有人在 MySQL 索引上碰一鼻子灰？的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：程序员吐槽_某程序员吐槽一程序员大佬竟然
下一篇： api php usdt 以太坊_以太坊

数据库

分度值1g是什么意思_都什么年代了，还有人在 MySQL 索引上碰一鼻子灰？

推薦閱讀：

前言

索引的原理

索引優化

Mysql優化

總結

總結