MySQL 高階優化

HuDu發表於2020-08-04
MySql優化
系列文章：
MySQL - 初識MySQL 運算元據庫 修改刪除表 建立資料庫表 MySQL 的資料管理及 DML 語句 DQL（Date Query Language）資料庫查詢語句 事務 索引 JDBC 資料庫操作 資料庫連線池 事務 MySQL 高階優化

索引（阿里規約）

MySQL 高階優化

索引資料結構

B+ 樹

Hash

MySQL 高階優化

以Col2為例，當插入資料時，會對插入的資料進行一個雜湊運算，把運算的雜湊雜湊的值作為地址值。

但是hash沒法很好的處理範圍查詢，但是B+ 樹在底層時對所有的資料進行了維護，樹的每層都是有序的。

B 樹

  • 葉子節點具有相同的深度，葉節點的指標為空
  • 所有索引元素不重複
  • 節點中的資料索引從左到右遞增排列

B+ 樹

多叉平衡樹

  • 非葉子節點不儲存data，只儲存索引（冗餘），可以放更多的索引
  • 葉子芥蒂娜包含所有索引欄位
  • 葉子節點用指標連線，提高區間訪問的效能

檢視mysql底層給B+ 樹的一個節點設定的大小數為16KB

SHOW GLOBAL STATUS LIKE 'Innodb_page_size';

MySQL 高階優化

以下圖為例，三層，索引拿bigint來計算一下，bigint是佔8個位元組，然後一個節點會有很多索引，一個索引會有一個指標，對應6個位元組，所以一個索引全部大小為（8+6）就是14個位元組，一個節點可以放1170個索引。

第二層同樣可以放1170個索引，不過第三層每個索引對應不止有bigint，還有data資料，data資料可能是索引所在行的磁碟檔案的地址，也可能是索引所在行的其它列的欄位，放不了1170個，如果按一個索引1kb算，第三層每個節點放16個索引。

所以樹的高度為3的B+樹就 總共放1170 X 1170 X 16 = 21902400 個索引

並且MySQL索引底層的B+樹的根節點是常駐記憶體（RAM）的。

MySQL 高階優化

MySQL的儲存引擎是表級別的。

建立兩張表，分別用不同的資料庫引擎

CREATE TABLE `test_innodb_lock`(
    `a` int(11) NOT NULL,
    `b` varchar(255) DEFAULT NULL,
    KEY `idx_a` (`a`),
    KEY `idx_b` (`b`)
)ENGINE=INNODB DEFAULT charset=utf8

CREATE TABLE     `test_myisam`(
    `id` int(11) NOT NULL auto_increment,
    `name` varchar(20) DEFAULT NULL,
    PRIMARY KEY(`id`)
)ENGINE=myisam auto_increment=2 default charset=utf8

可以看到實際上是在磁碟檔案中mysql的data目錄下具體的目錄下儲存。

MySQL 高階優化

SDI是Serialized Dictionary Information的縮寫，是MySQL8.0重新設計資料詞典後引入的新產物。對於非InnoDB引擎，MySQL提供了另外一中可讀的檔案格式來描述表的後設資料資訊，在磁碟上以 $tbname.sdi的命名儲存在資料庫目錄下。

  • MyISAM索引檔案和資料檔案是分離的（非聚集）

查詢過程為，現判斷是否有索引，有的話根據myi檔案找到索引，然後根據myd找到所在行的檔案地址

MySQL 高階優化

  • InnoDB 索引實現（聚集）
    • 表資料檔案本身就是按B+ Tree組織的一個索引結構檔案
    • 聚集索引葉子節點包含了完整的資料記錄
    • 為什麼InnoDB表必須有主鍵，並且推薦使用整型的自增主鍵？
    • 為什麼非空主鍵索引結構葉子節點儲存的是主鍵值？（一致性和節省儲存空間）

如果innodb表沒有主鍵，會自動根據找一列資料，這一列資料會中的每一個資料都是唯一的，會把那一列資料拿出來對錶進行維護。
如果連資料唯一的列都沒有，innodb會在最後增加一列隱藏列，它幫你維護表。都是B+ 樹。mysql資源是非常緊張的，每個表最好一定要建立主鍵，並且最好是整型自增，不推薦UUID這種字串比較，因為效率會變慢。為什麼要自增，不自增B+要自平衡，會導致執行insert語句等效率。

MySQL 高階優化

MySQL 高階優化

B+ 樹索引的葉子節點兩兩之間，是有一個雙向指標，放相鄰節點的磁碟檔案地址，可以在任意節點通過指標快速定位相鄰節點的位置。

若要查詢索引大於20的資料，先從根節點，快速定位到索引為20的節點，然後直接把索引20之後的所有的資料拿出來。

B 樹葉子節點之間沒有雙向指標。B 樹是每個節點都會有資料，B+ 樹把所有的資料都放到葉子幾點上，非葉子節點是冗餘索引，葉子有完整的索引。

由於B 樹上的每個節點都會放置資料，所以層放的節點會更少，存放相同量的資料，B樹的層數會更加高。

聯合索引

建立示例表

CREATE TABLE `employees` (
    `id` int(11) not null auto_increment,
    `name` varchar(24) not null default '' COMMENT '姓名',
    `age` int(11) not null default '0' comment '年齡',
    `position` varchar(20) not null default '' comment '職位',
    `hire_time` timestamp not null default current_timestamp comment '入職時間',
    primary key(`id`),
    key `idx_name_age_position` (`name`,`age`,`position`) using btree
)ENGINE = innodb auto_increment=4 default charset=utf8 comment='員工記錄表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW()),('HanMeimei',23,'dev',NOW()),('Lucy',23,'dev',NOW());

MySQL 高階優化

它是按照建立索引的順序進行依次比較。

key `idx_name_age_position` (`name`,`age`,`position`) using btree;

EXPLAIN SELECT * FROM employees WHERE NAME = 'Bill' AND age = 31;
EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';
EXPLAIN SELECT * FROM employees WHERE position = 'manager';

最左字首原則，所以上面的語句只有第一條會執行。
原因：
在整張表之中查詢，如果忽略索引的第一個欄位，直接從後面欄位查詢，欄位不是排好序的，無法通過索引查詢，還需要全樹比對查詢。

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章