歡迎來到愛書不愛輸的程序猿的博客, 本博客致力于知識分享，與更多的人進行學習交流

本文收錄于SQL應知應會專欄,本專欄主要用于記錄對于數據庫的一些學習，有基礎也有進階，有MySQL也有Oracle

前言

?今天開始SQL的索引的篇章，同樣我們會講到MySQL和Oracle的索引，大家拭目以待吧
??今天這篇主要簡單介紹了索引的概念和優(yōu)缺點，同時直接進入主題——如何創(chuàng)建索引，同時我們會針對索引進行一些分類，而今天講的是按照邏輯分類后的普通索引(單列索引)以及復合索引，分別講了索引的創(chuàng)建和應用
??文章中提供了代碼和很具體的截圖，希望大家跟著一起學起來
希望文章的內容對大家有所幫助，如果有什么不足的地方，大家可以在評論區(qū)或者私信我，感謝大家的支持
??那么，快拿出你的電腦，跟著文章一起學習起來吧

一、索引

1.簡介

• 索引是對數據庫表中一列或多列的值進行排序的一種結構，使用索引可快速訪問數據庫表中的特定信息。

1.1 索引的優(yōu)點

• 索引大大減小了服務器需要掃描的數據量，從而大大加快數據的檢索速度，這也是創(chuàng)建索引的最主要的原因。

• 索引可以幫助服務器避免排序和創(chuàng)建臨時表

• 索引可以將隨機IO變成順序IO

• 索引對于InnoDB（對索引支持行級鎖）非常重要，因為它可以讓查詢鎖更少的元組，提高了表訪問并發(fā)性

• 關于InnoDB、索引和鎖：InnoDB在二級索引上使用共享鎖（讀鎖），但訪問主鍵索引需要排他鎖（寫鎖）

• 通過創(chuàng)建唯一性索引，可以保證數據庫表中每一行數據的唯一性。

• 可以加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義。

• 在使用分組和排序子句進行數據檢索時，同樣可以顯著減少查詢中分組和排序的時間。

• 通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。

1.2 索引的缺點

• 創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加

• 索引需要占物理空間，除了數據表占用數據空間之外，每一個索引還要占用一定的物理空間，如果需要建立聚簇索引，那么需要占用的空間會更大

• 對表中的數據進行增、刪、改的時候，索引也要動態(tài)的維護，這就降低了整數的維護速度

• 如果某個數據列包含許多重復的內容，為它建立索引就沒有太大的實際效果。

• 對于非常小的表，大部分情況下簡單的全表掃描更高效

2. 索引類型之邏輯分類

2.1普通索引(單列索引) 的創(chuàng)建

• 單列索引是最基本的索引,沒有任何的限制

• 方法1:直接創(chuàng)建索引

CREATE INDEX index_name ON table_name(col_name);

• 方法2：修改表結構的方式添加索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

• 方法3:創(chuàng)建表的時候同時創(chuàng)建索引

CREATE TABLE news (
    id int(11) NOT NULL AUTO_INCREMENT ,
    title varchar(255)  NOT NULL ,
    content varchar(255)  NULL ,
    time varchar(20) NULL DEFAULT NULL ,
    PRIMARY KEY (id),  -- 默認使用B樹索引
    INDEX index_name (title(255)) -- 默認使用B樹索引
)

2.2普通索引(單列索引) 的查看

• 可以通過show create table news 查看

CREATE TABLE `news` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` varchar(255) DEFAULT NULL,
  `time` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

• 也可以通過轉儲SQL文件進行查看

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for news
-- ----------------------------
DROP TABLE IF EXISTS `news`;
CREATE TABLE `news`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `content` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `time` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_name`(`title`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

2.3 復合索引(組合索引)的創(chuàng)建

• 復合索引是在多個字段上創(chuàng)建的索引
• 復合索引遵守**“最左前綴”原則**，即在查詢條件中使用了復合索引的第一個字段，索引才會被使用。因此，在復合索引中索引列的順序至關重要。

• 方法1：創(chuàng)建一個復合索引

create index index_name on table_name(col_name1,col_name2,...);

• 方法2：按照修改表結構的方式添加索引

alter table table_name add index index_name(col_name,col_name2,...);

• 方法3：直接使用工具(此處是Navicat)進行添加
  

2.4 復合索引(組合索引)的應用

2.4.1 直接使用select *查詢前面添加索引的表

• 發(fā)現并沒有使用到索引 ，而是全表掃描

explain
select * from emp

2.4.2 查詢具體的字段

• 使用到前面添加的復合索引，但是還是要掃描整個索引樹

explain
select empno from emp

2.4.3 遵循最左前綴原則，對復合索引中的索引字段按照順序進行查詢

• 我們發(fā)現查詢都使用到了索引

explain
select * from emp where empno = '7499'

explain
select * from emp where empno = '7499' and ename = 'ALLEN'

explain
select * from emp where empno = '7499' and ename = 'ALLEN' and deptno = '30'

2.4.4 遵循最左前綴原則，對復合索引中的索引字段不按照順序進行查詢

• 發(fā)現使用復合索引的第一個字段，與在SQL語句中的順序無關

explain
select * from emp where ename = 'ALLEN' and deptno = '30' and empno = '7499' 

2.4.5 不遵循最左前綴原則，對復合索引中的索引字段進行查詢

• 發(fā)現并沒有使用到索引

explain
select * from emp where ename = 'ALLEN' and deptno = '30'

2.4.6 in & not in

• where in 也會用到索引

explain
select * from emp where empno in (7499,7521)

• not in 沒有in的效率高

explain
select * from emp where empno not in (7499,7521)

小結

??感謝大家耐心的看完這篇文章，這篇文章是MySQL索引的第1篇文章，我們在MySQL方面寫了很多內容了，大家可以去我的專欄SQL應知應會 進行學習，如果大家覺著還算可以，那么就給個三連支持一下吧
??也可以加入我的社區(qū)一起學習呀
?如果想要繼續(xù)關注和學習后續(xù)更多的內容，那就關注一下愛書不愛輸的程序猿吧，當然，如果大家還有什么其他方面的知識點想要看，可以在評論區(qū)或者私信我

文章來源地址http://www.zghlxwxcb.cn/news/detail-636751.html

到了這里，關于【SQL應知應會】索引（一）? MySQL版的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！