1. 首頁 > 生活百科 > >

一文讀懂 MySQL 索引( 二 )

生活百科


  1. 在表上定義主鍵 PRIMARY KEY，InnoDB 將主鍵索引用作聚簇索引 。
  2. 如果表沒有定義主鍵，InnoDB 會選擇第一個不為 NULL 的唯一索引列用作聚簇索引 。
  3. 如果以上兩個都沒有，InnoDB 會使用一個 6 字節長整型的隱式字段 ROWID 字段構建聚簇索引 。該 ROWID 字段會在插入新行時自動遞增 。
創建方式：
CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT ,`name` varchar(255) NOT NULL ,PRIMARY KEY (`id`));為什么建表時沒有指定主鍵，MySQL 會默認使用一個隱式字段 ROWID 字段構建聚簇索引？這個在后面我們會提到
3.2 唯一索引與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值 。如果是組合索引，則列值的組合必須唯一 。
創建方式
CREATE UNIQUE INDEX indexName ON user(column)或者ALTER TABLE table_name ADD UNIQUE indexName ON (column)3.3 普通索引MySQL 基本的索引，沒有什么限制
創建方式：
CREATE INDEX index_name ON user(column)或者ALTER TABLE user ADD INDEX index_name ON (column)3.4 組合索引組合索引 ， 顧名思義，給 MySQL 多個字段同時加上索引，在使用時要遵循最左匹配原則
創建方式：
CREATE INDEX index_name ON user(column1,column2) -- 給 column1 和 column2 加上索引3.5 全文索引全文索引，主要用來查找文本中的關鍵字，不是直接與索引值相比較 。與我們常見的搜索引擎（如elasticsearch、solr 等）功能相似 。MySQL 全文索引性能一般，所以一般不用，作為了解即可
創建方式：
CREATE FULLTEXT INDEX index_column ON user(column)或者ALTER TABLE user ADD FULLTEXT index_column(column)4 索引設計4.1 三星索引三星索引是我們設計 MySQL 索引時的一個規范，符合三星索引的索引設計通常是比較好的設計
一星：索引中查詢相關的索引行是相鄰的，或者至少相距足夠靠近
二星：索引中數據列的順序和查找中排序順序相同
三星：索引中的列包含了查詢中需要的全部列 。索引包含查詢所需要的數據列 ， 不再進行全表查表，回表操作
下面舉一個例子為大家介紹一下三星索引是什么樣子的
現在有一張表 ， 表結構如下
CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(10) NOT NULL,`age` int(10) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;一星
我們現在給 age 加上索引
create index idx_age on user (age);查詢
select * from user where age in (10,20,35,43)這條語句不一定符合一星，因為 age 是一個范圍，數據可能比較分散
select * from user where age = 20;這條語句是符合一星的，因為索引是按照 age 從小到大排序的，所以 age = 20 的數據肯定是在一起的
二星
select * from user where age = 20 order by name;這條語句符合一星，但不符合二星 ， 因為數據列的順序是按照 age 排序的，如果現在改成 name 排序，可能導致索引順序與 order by 排序結果不同，結果如下：
一文讀懂 MySQL 索引

文章插圖

一文讀懂 MySQL 索引

文章插圖
select * from user where age = 20 order by age這條查詢語句則符合一星和二星
三星
select * from user where age = 20這條語句不符合三星，因為索引列中只有 id 和 age，沒有 name
select age from user where age = 20這條語句則符合三星，因為只查詢了 age，age 在索引中存在，不需要回表
4.2 回表上面三星索引提到了一個次回表 ， 那么回表是什么？
簡單點說，就是查詢語句中需要的列，在索引中不包含，需要根據主鍵 id 再查詢一次才能獲取到 。回表相當于多查詢一次，再查詢時我們要盡量避免回表查詢 。
因為普通索引中只包含了對應列和主鍵的值，比如 age 索引，那么 age 索引中包含的數據有 age，id 。此時如果需要 name 的話，需要先通過 age 索引找到對應的 id，然后再去主鍵索引上找到 name ， 主鍵索引包含了一行所有記錄的值 。這里回答了上面的問題，為什么 MySQL 一定要有主鍵索引 ， 因為主鍵索引子節點中包含了全部數據
一文讀懂 MySQL 索引

文章插圖
4.3 索引覆蓋CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(10) NOT NULL,`age` int(1) DEFAULT NULL,`sex` varchar(2) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_name_age` (`name`,`age`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;select name,age from user where name = "張三"-- 這條語句就使用了索引覆蓋，因為 name 和 age 再 idx_name_age 索引中都有，不需要回表查詢select name,age,sex from user where name = "張三"-- 如果加上了 sex ， 那么就需要回表查詢了，因為索引中不存在 sex 字段

推薦閱讀