日本XXXX裸体XXXX在线观看,乱中年女人伦AV一区二区,色狠狠一区二区三区熟女

  • <em id="d9ukz"><acronym id="d9ukz"><input id="d9ukz"></input></acronym></em>
  • MySQL如何設計索引更高效?

    來源:掘金
    2021-02-27
    1149
    導語:數據庫系列更新到現在我想大家對所有的概念都已有個大概認識了,這周我在看評論的時候我發現有個網友的提問我覺得很有意思:帥丙如何設計一個索引?你們都是怎么設計索引的?怎么設計更高效? 我一想索引我寫過很多了呀,沒道理讀者還不會啊,但是我一回頭看完,那確實,我就寫了索引的概念,優劣勢,沒提到怎么設計,那這篇文章又這樣應運而生了。 本文還是會有很多之前寫過的重復概念,但是也是為了大家能更好的理解MySQL中幾種索引設計的原理。
    閱讀本文大概需要8分鐘

    我們知道,索引是一個基于鏈表實現的樹狀Tree結構,能夠快速的檢索數據,目前幾乎所RDBMS數據庫都實現了索引特性,比如MySQL的B+Tree索引,MongoDB的BTree索引等。

    在業務開發過程中,索引設計高效與否決定了接口對應SQL的執行效率,高效的索引可以降低接口的Response Time,同時還可以降低成本,我們要現實的目標是:索引設計->降低接口響應時間->降低服務器配置->降低成本,最終要落實到成本上來,因為老板最關心的是成本。

    今天就跟大家聊聊MySQL中的索引以及如何設計索引,使用索引才能提降低接口的RT,提高用戶體檢。

    MySQL中的索引

    MySQL中的InnoDB引擎使用B+Tree結構來存儲索引,可以盡量減少數據查詢時磁盤IO次數,同時樹的高度直接影響了查詢的性能,一般樹的高度維持在 3~4 層。

    B+Tree由三部分組成:根root、枝branch以及Leaf葉子,其中root和branch不存儲數據,只存儲指針地址,數據全部存儲在Leaf Node,同時Leaf Node之間用雙向鏈表鏈接,結構如下:

    從上面可以看到,每個Leaf Node是三部分組成的,即前驅指針p_prev,數據data以及后繼指針p_next,同時數據data是有序的,默認是升序ASC,分布在B+tree右邊的鍵值總是大于左邊的,同時從root到每個Leaf的距離是相等的,也就是訪問任何一個Leaf Node需要的IO是一樣的,即索引樹的高度Level + 1次IO操作。

    我們可以將MySQL中的索引可以看成一張小表,占用磁盤空間,創建索引的過程其實就是按照索引列排序的過程,先在sort_buffer_size進行排序,如果排序的數據量大,sort_buffer_size容量不下,就需要通過臨時文件來排序,最重要的是通過索引可以避免排序操作(distinct,group by,order by)。

    聚集索引

    MySQL中的表是IOT(Index Organization Table,索引組織表),數據按照主鍵id順序存儲(邏輯上是連續,物理上不連續),而且主鍵id是聚集索引(clustered index),存儲著整行數據,如果沒有顯示的指定主鍵,MySQL會將所有的列組合起來構造一個row_id作為primary key,例如表users(id, user_id, user_name, phone, primary key(id)),id是聚集索引,存儲了id, user_id, user_name, phone整行的數據。

    輔助索引

    ">

    輔助索引也稱為二級索引,索引中除了存儲索引列外,還存儲了主鍵id,對于user_name的索引idx_user_name(user_name)而言,其實等價于idx_user_name(user_name, id),MySQL會自動在輔助索引的最后添加上主鍵id,熟悉Oracle數據庫的都知道,索引里除了索引列還存儲了row_id(代表數據的物理位置,由四部分組成:對象編號+數據文件號+數據塊號+數據行號),我們在創建輔助索引也可以顯示添加主鍵id。

    -- 創建user_name列上的索引
    mysql> create index idx_user_name on users(user_name);
    -- 顯示添加主鍵id創建索引
    mysql> create index idx_user_name_id on users(user_name,id);
    -- 對比兩個索引的統計數據
    mysql> select a.space as tbl_spaceid, a.table_id, a.name as table_name, row_format, space_type,  b.index_id , b.name as index_name, n_fields, page_no, b.type as index_type  from information_schema.INNODB_TABLES a left join information_schema.INNODB_INDEXES b  on a.table_id =b.table_id where a.name = 'test/users';
    +-------------+----------+------------+------------+------------+----------+------------------+----------+------
    | tbl_spaceid | table_id | table_name | row_format | space_type | index_id | index_name       | n_fields | page_no | index_type |
    +-------------+----------+------------+------------+------------+----------+------------------+----------+------
    |         518 |     1586 | test/users | Dynamic    | Single     |     1254 | PRIMARY          |        9 |       4 |          3 |
    |         518 |     1586 | test/users | Dynamic    | Single     |     4003 | idx_user_name    |        2 |       5 |          0 |
    |         518 |     1586 | test/users | Dynamic    | Single     |     4004 | idx_user_name_id |        2 |      45 |          0 |
    mysql> select index_name, last_update, stat_name, stat_value, stat_description from mysql.innodb_index_stats where index_name in ('idx_user_name','idx_user_name_id');
    +------------------+---------------------+--------------+------------+-----------------------------------+
    | index_name       | last_update         | stat_name    | stat_value | stat_description                  |
    +------------------+---------------------+--------------+------------+-----------------------------------+   
    | idx_user_name    | 2021-01-02 17:14:48 | n_leaf_pages |       1358 | Number of leaf pages in the index |
    | idx_user_name    | 2021-01-02 17:14:48 | size         |       1572 | Number of pages in the index      |
    | idx_user_name_id | 2021-01-02 17:14:48 | n_leaf_pages |       1358 | Number of leaf pages in the index |
    | idx_user_name_id | 2021-01-02 17:14:48 | size         |       1572 | Number of pages in the index      |
    復制代碼

    對比一下兩個索引的結果,n_fields表示索引中的列數,n_leaf_pages表示索引中的葉子頁數,size表示索引中的總頁數,通過數據比對就可以看到,輔助索引中確實包含了主鍵id,也說明了這兩個索引時完全一致。

    Index_namen_fieldsn_leaf_pagessize
    idx_user_name213581572
    idx_user_name_id213581572

    索引回表

    上面證明了輔助索引包含主鍵id,如果通過輔助索引列去過濾數據有可能需要回表,舉個例子:業務需要通過用戶名user_name去查詢用戶表users的信息,業務接口對應的SQL:

    select  user_id, user_name, phone from users where user_name = 'Laaa';
    復制代碼

    我們知道,對于索引idx_user_name而言,其實就是一個小表idx_user_name(user_name, id),如果只查詢索引中的列,只需要掃描索引就能獲取到所需數據,是不需要回表的,如下SQL語句:

    SQL 1: select id, user_name from users where user_name = 'Laaa';

    SQL 2: select id from users where user_name = 'Laaa';

    mysql> explain select id, name from users where name = 'Laaa';
    +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+-------
    | id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+-------
    |  1 | SIMPLE      | users | NULL       | ref  | idx_user_name | idx_user_name | 82      | const |    1 |   100.00 | Using index |
    mysql> explain select id from users where name = 'Laaa';
    +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+-------
    | id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+-------
    |  1 | SIMPLE      | users | NULL       | ref  | idx_user_name | idx_user_name | 82      | const |    1 |   100.00 | Using index |
    復制代碼

    SQL 1和SQL 2的執行計劃中的Extra=Using index 表示使用覆蓋索引掃描,不需要回表,再來看上面的業務SQL:

    select user_id, user_name, phone from users where user_name = 'Laaa';

    可以看到select后面的user_id,phone列不在索引idx_user_name中,就需要通過主鍵id進行回表查找,MySQL內部分如下兩個階段處理:

    Section 1select **id** from users where user_name = 'Laaa' //id = 100101

    Section 2: select user_id, user_name, phone from users where id = 100101;

    Section 2的操作稱為回表,即通過輔助索引中的主鍵id去原表中查找數據。

    索引高度

    MySQL的索引時B+tree結構,即使表里有上億條數據,索引的高度都不會很高,通常維持在3-4層左右,我來計算下索引idx_name的高度,從上面知道索引信息:index_id = 4003, page_no = 5,它的偏移量offset就是page_no x innodo_page_size + 64 = 81984,通過hexdump進行查看

    $hexdump -s 81984 -n 10 /usr/local/var/mysql/test/users.ibd
    0014040 00 02 00 00 00 00 00 00 0f a3                  
    001404a
    復制代碼

    其中索引的PAGE_LEVEL為00,即idx_user_name索引高度為1,0f a3 代表索引編號,轉換為十進制是4003,正是index_id。

    數據掃描方式

    全表掃描

    從左到右依次掃描整個B+Tree獲取數據,掃描整個表數據,IO開銷大,速度慢,鎖等嚴重,影響MySQL的并發。

    對于OLAP的業務場景,需要掃描返回大量數據,這時候全表掃描的順序IO效率更高。

    索引掃描

    通常來講索引比表小,掃描的數據量小,消耗的IO少,執行速度塊,幾乎沒有鎖等,能夠提高MySQL的并發。

    對于OLTP系統,希望所有的SQL都能命中合適的索引總是美好的。

    主要區別就是掃描數據量大小以及IO的操作,全表掃描是順序IO,索引掃描是隨機IO,MySQL對此做了優化,增加了change buffer特性來提高IO性能。

    索引優化案例

    分頁查詢優化

    業務要根據時間范圍查詢交易記錄,接口原始的SQL如下:

    select  * from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20;
    復制代碼

    表trade_info上有索引idx_status_create_time(status,create_time),通過上面分析知道,等價于索引**(status,create_time,id)**,對于典型的分頁limit m, n來說,越往后翻頁越慢,也就是m越大會越慢,因為要定位m位置需要掃描的數據越來越多,導致IO開銷比較大,這里可以利用輔助索引的覆蓋掃描來進行優化,先獲取id,這一步就是索引覆蓋掃描,不需要回表,然后通過id跟原表trade_info進行關聯,改寫后的SQL如下:

    select * from trade_info a ,
    
    (select  id from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20) as b   //這一步走的是索引覆蓋掃描,不需要回表
     where a.id = b.id;
    復制代碼

    很多同學只知道這樣寫效率高,但是未必知道為什么要這樣改寫,理解索引特性對編寫高質量的SQL尤為重要。

    分而治之總是不錯的

    營銷系統有一批過期的優惠卷要失效,核心SQL如下:

    -- 需要更新的數據量500w
    update coupons set status = 1 where status =0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59';
    復制代碼

    在Oracle里更新500w數據是很快,因為可以利用多個cpu core去執行,但是MySQL就需要注意了,一個SQL只能使用一個cpu core去處理,如果SQL很復雜或執行很慢,就會阻塞后面的SQL請求,造成活動連接數暴增,MySQL CPU 100%,相應的接口Timeout,同時對于主從復制架構,而且做了業務讀寫分離,更新500w數據需要5分鐘,Master上執行了5分鐘,binlog傳到了slave也需要執行5分鐘,那就是Slave延遲5分鐘,在這期間會造成業務臟數據,比如重復下單等。

    優化思路:先獲取where條件中的最小id和最大id,然后分批次去更新,每個批次1000條,這樣既能快速完成更新,又能保證主從復制不會出現延遲。

    優化如下:

    1. 先獲取要更新的數據范圍內的最小id和最大id(表沒有物理delete,所以id是連續的)
    mysql> explain select min(id) min_id, max(id) max_id from coupons where status =0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59'; 
    +----+-------------+-------+------------+-------+------------------------+------------------------+---------+---
    | id | select_type | table | partitions | type  | possible_keys          | key                    | key_len | ref  | rows   | filtered | Extra                    |
    +----+-------------+-------+------------+-------+------------------------+------------------------+---------+---
    |  1 | SIMPLE      | users | NULL       | range | idx_status_create_time | idx_status_create_time | 6       | NULL | 180300 |   100.00 | Using where; Using index |
    復制代碼

    ? Extra=Using where; Using index使用了索引idx_status_create_time,同時需要的數據都在索引中能找到,所以不需要回表查詢數據。

    1. 以每次1000條commit一次進行循環update,主要代碼如下:
    current_id = min_id;
    for  current_id < max_id do
      update coupons set status = 1 where id >=current_id and id <= current_id + 1000;  //通過主鍵id更新1000條很快
    commit;
    current_id += 1000;
    done
    復制代碼

    這兩個案例告訴我們,要充分利用輔助索引包含主鍵id的特性,先通過索引獲取主鍵id走覆蓋索引掃描,不需要回表,然后再通過id去關聯操作是高效的,同時根據MySQL的特性使用分而治之的思想既能高效完成操作,又能避免主從復制延遲產生的業務數據混亂。

    MySQL索引設計

    熟悉了索引的特性之后,就可以在業務開發過程中設計高質量的索引,降低接口的響應時間。

    前綴索引

    對于使用REDUNDANT或者COMPACT格式的InnoDB表,索引鍵前綴長度限制為767字節。如果TEXT或VARCHAR列的列前綴索引超過191個字符,則可能會達到此限制,假定為utf8mb4字符集,每個字符最多4個字節。

    可以通過設置參數innodb_large_prefix來開啟或禁用索引前綴長度的限制,即是設置為OFF,索引雖然可以創建成功,也會有一個警告,主要是因為index size會很大,效率大量的IO的操作,即使MySQL優化器命中了該索引,效率也不會很高。

    -- 設置innodb_large_prefix=OFF禁用索引前綴限制,雖然可以創建成功,但是有警告。
    mysql> create index idx_nickname on users(nickname);    // `nickname` varchar(255)
    Records: 0  Duplicates: 0  Warnings: 1
    mysql> show warnings;
    +---------+------+---------------------------------------------------------+
    | Level   | Code | Message                                                 |
    +---------+------+---------------------------------------------------------+
    | Warning | 1071 | Specified key was too long; max key length is 767 bytes |
    復制代碼

    業務發展初期,為了快速實現功能,對一些數據表字段的長度定義都比較寬松,比如用戶表users的昵稱nickname定義為varchar(128),而且有業務接口需要通過nickname查詢,系統運行了一段時間之后,查詢users表最大的nickname長度為30,這個時候就可以創建前綴索引來減小索引的長度提升性能。

    -- `nickname` varchar(128) DEFAULT NULL定義的執行計劃
    mysql> explain select * from users where nickname = 'Laaa';
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+--------
    | id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+--------
    |  1 | SIMPLE      | users | NULL       | ref  | idx_nickname  | idx_nickname | 515     | const |    1 |   100.00 | NULL  |
    復制代碼

    key_len=515,由于表和列都是utf8mb4字符集,每個字符占4個字節,變長數據類型+2Bytes,允許NULL額外+1Bytes,即128 x 4 + 2 + 1 = 515Bytes。創建前綴索引,前綴長度也可以不是當前表的數據列最大值,應該是區分度最高的那部分長度,一般能達到90%以上即可,例如email字段存儲都是類似這樣的值xxxx@yyy.com,前綴索引的最大長度可以是xxxx這部分的最大長度即可。

    -- 創建前綴索引,前綴長度為30
    mysql> create index idx_nickname_part on users(nickname(30));
    -- 查看執行計劃
    mysql> explain select * from users where nickname = 'Laaa';
    +----+-------------+-------+------------+------+--------------------------------+-------------------+---------+-
    | id | select_type | table | partitions | type | possible_keys                  | key               | key_len | ref   | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+--------------------------------+-------------------+---------+-
    |  1 | SIMPLE      | users | NULL       | ref  | idx_nickname_part,idx_nickname | idx_nickname_part | 123     | const |    1 |   100.00 | Using where |
    復制代碼

    可以看到優化器選擇了前綴索引,索引長度為123,即30 x 4 + 2 + 1 = 123 Bytes,大小不到原來的四分之。

    前綴索引雖然可以減小索引的大小,但是不能消除排序。

    mysql> explain select gender,count(*) from users where nickname like 'User100%' group by nickname limit 10;
    +----+-------------+-------+------------+-------+--------------------------------+--------------+---------+-----
    | id | select_type | table | partitions | type  | possible_keys                  | key          | key_len | ref  | rows | filtered | Extra                 |
    +----+-------------+-------+------------+-------+--------------------------------+--------------+---------+-----
    |  1 | SIMPLE      | users | NULL       | range | idx_nickname_part,idx_nickname | idx_nickname | 515     | NULL |  899 |   100.00 | Using index condition |
    --可以看到Extra= Using index condition表示使用了索引,但是需要回表查詢數據,沒有發生排序操作。
    mysql> explain select gender,count(*) from users where nickname like  'User100%' group by nickname limit 10;
    +----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------
    | id | select_type | table | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                        |
    +----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------
    |  1 | SIMPLE      | users | NULL       | range | idx_nickname_part | idx_nickname_part | 123     | NULL |  899 |   100.00 | Using where; Using temporary |
    --可以看到Extra= Using where; Using temporaryn表示在使用了索引的情況下,需要回表去查詢所需的數據,同時發生了排序操作。
    復制代碼

    復合索引

    在單列索引不能很好的過濾數據的時候,可以結合where條件中其他字段來創建復合索引,更好的去過濾數據,減少IO的掃描次數,舉個例子:業務需要按照時間段來查詢交易記錄,有如下的SQL:

    select  * from trade_info where status = 1 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59';
    復制代碼

    開發同學根據以往復合索引的設計的經驗:唯一值多選擇性好的列作為復合索引的前導列,所以創建復合索idx_create_time_status是高效的,因為create_time是一秒一個值,唯一值很多,選擇性很好,而status只有離散的6個值,所以認為這樣創建是沒問題的,但是這個經驗只適合于等值條件過濾,不適合有范圍條件過濾的情況,例如idx_user_id_status(user_id,status)這個是沒問題的,但是對于包含有create_time范圍的復合索引來說,就不適應了,我們來看下這兩種不同索引順序的差異,即idx_status_create_time和idx_create_time_status。

    -- 分別創建兩種不同的復合索引
    mysql> create index idx_status_create_time on trade_info(status, create_time);
    mysql> create index idx_create_time_status on trade_info(create_time,status);
    -- 查看SQL的執行計劃
    mysql> explain select * from users where status = 1 and create_time >='2021-10-01 00:00:00' and create_time <= '2021-10-07 23:59:59';
    +----+-------------+-------+------------+-------+-----------------------------------------------+---------------
    | id | select_type | table | partitions | type  | possible_keys                                 | key                    | key_len | ref  | rows  | filtered | Extra                 |
    +----+-------------+-------+------------+-------+-----------------------------------------------+---------------
    |  1 | SIMPLE      | trade_info | NULL       | range | idx_status_create_time,idx_create_time_status | idx_status_create_time | 6       | NULL | 98518 |   100.00 | Using index condition |
    復制代碼

    從執行計劃可以看到,兩種不同順序的復合索引都存在的情況,MySQL優化器選擇的是idx_status_create_time索引,那為什么不選擇idx_create_time_status,我們通過optimizer_trace來跟蹤優化器的選擇。

    -- 開啟optimizer_trace跟蹤
    mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;
    -- 執行SQL語句
    mysql> select * from trade_info where status = 1 and create_time >='2021-10-01 00:00:00' and create_time <= '2021-10-07 23:59:59';
    -- 查看跟蹤結果
    mysql>SELECT trace FROM information_schema.OPTIMIZER_TRACE\G;
    復制代碼

    ">

    對比下兩個索引的統計數據,如下所示:

    復合索引TypeRows參與過濾索引列ChosenCause
    idx_status_create_timeIndex Range Scan98518status AND create_timeTrueCost低
    idx_create_time_statusIndex Range Scan98518create_timeFalseCost高

    MySQL優化器是基于Cost的,COST主要包括IO_COST和CPU_COST,MySQL的CBO(Cost-Based Optimizer基于成本的優化器)總是選擇Cost最小的作為最終的執行計劃去執行,從上面的分析,CBO選擇的是復合索引idx_status_create_time,因為該索引中的status和create_time都能參與了數據過濾,成本較低;而idx_create_time_status只有create_time參數數據過濾,status被忽略了,其實CBO將其簡化為單列索引idx_create_time,選擇性沒有復合索引idx_status_create_time好。

    復合索引設計原則

    1. 將范圍查詢的列放在復合索引的最后面,例如idx_status_create_time。
    2. 列過濾的頻繁越高,選擇性越好,應該作為復合索引的前導列,適用于等值查找,例如idx_user_id_status。

    這兩個原則不是矛盾的,而是相輔相成的。

    跳躍索引

    一般情況下,如果表users有復合索引idx_status_create_time,我們都知道,單獨用create_time去查詢,MySQL優化器是不走索引,所以還需要再創建一個單列索引idx_create_time。用過Oracle的同學都知道,是可以走索引跳躍掃描(Index Skip Scan),在MySQL 8.0也實現Oracle類似的索引跳躍掃描,在優化器選項也可以看到skip_scan=on。

    | optimizer_switch             |use_invisible_indexes=off,skip_scan=on,hash_join=on |
    復制代碼

    適合復合索引前導列唯一值少,后導列唯一值多的情況,如果前導列唯一值變多了,則MySQL CBO不會選擇索引跳躍掃描,取決于索引列的數據分表情況。

    mysql> explain select id, user_id,status, phone from users where create_time >='2021-01-02 23:01:00' and create_time <= '2021-01-03 23:01:00';
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----
    |  1 | SIMPLE      | users | NULL       | range  | idx_status_create_time          | idx_status_create_time | NULL    | NULL | 15636 |    11.11 | Using where; Using index for skip scan|
    復制代碼

    也可以通過optimizer_switch='skip_scan=off'來關閉索引跳躍掃描特性。

    相關標簽
    免費獲取專屬 《策劃方案 》及報價
    免費體驗我們的業務系統、OA系統、在線教育、電商系統、智慧辦公等產品定制化方案,助力您的信息化發展之路
    即時交流
    在線咨詢 電話咨詢
    在線咨詢
    產品經理

    一對一產品經理

    180 8812 7777
    電話咨詢

    電話咨詢

    0871-6718 6978
    到訪面聊
    返回頂部
    日本XXXX裸体XXXX在线观看,乱中年女人伦AV一区二区,色狠狠一区二区三区熟女