【如何成為SQL高手】第三關：索引及維護

👨?🎓 博主介紹：
IT邦德，江湖人稱jeames007，10年DBA作業經驗
中國DBA聯盟(ACDU)成員，目前從事DBA及程式編程

SQL對于現在的互聯網公司產研崗位幾乎是一個必備技能，但僅會SQL的話，應該是什么都做不了，
1.如果你是資料分析師，你需要熟練地把自己腦子里的資料和指標需求翻譯成SQL邏輯去查詢資料，進而完成自己的資料分析報告等，你的產出是分析報告，而不是SQL代碼；
2.如果你是數倉工程師（偏應用層），你需要根據業務邏輯去設計模型，撰寫調度任務去產出資料，以供業務人員使用，你的產出是資料模型和表；
3.如果你是演算法工程師，你可能需要用SQL來實作用戶標簽、特征工程等作業，但是這些是為你的模型訓練評估做基礎準備作業，你的產出是可以提升某些指標的演算法模型，

所以，SQL每個人都要用，但是用來衡量產出的并不是SQL本身，你需要用這個工具，去創造其它的價值，
IT邦德老師帶你成為SQL高手，那我們開始吧~

1.索引的創建

1.1. 普通索引

按照下圖的表結構創建tb_student1表，要求在創建的同時在studentName欄位上建立普通索引，
索引名為idx_studentName

create table if not exists tb_student1 (
studentNo CHAR(10) not NULL primary key comment '學號',
studentName VARCHAR(10) NOT null comment '姓名',
sex CHAR(2) comment '性別',
birthday date comment '出生日期',
native VARCHAR(20) comment '籍貫',
nation VARCHAR(10) DEFAULT '漢' comment '民族',
classNo CHAR(6) comment '所屬班級',
INDEX idx_studentName(studentName)
) ENGINE=InnoDB comment '學生表';

注：INDEX idx_studentName(studentName)這個是關鍵
此方法是創建新表的同時創建普通索引

1.2. 唯一索引

按照下圖的表結構創建tb_class1表，要求在創建的同時在className欄位上建立唯一索引，
索引名為uqidx_className

CREATE TABLE tb_class1 (
 classNo CHAR(6) PRIMARY KEY NOT NULL,
 className VARCHAR(20) NOT NULL,
 department VARCHAR(20),
 grade ENUM('1','2','3','4'), 
 classNum TINYINT,
 constraint uq_class unique(className),
 UNIQUE INDEX uqidx_className(className)
) engine=InnoDB default charset=gb2312;

注： UNIQUE INDEX uqidx_className(className)是關鍵
此方法是創建新表時創建唯一索引

1.3. 主鍵索引

按照如下的表結構創建tb_course1，要求創建的同時在courseNo欄位上建立主鍵索引

CREATE TABLE tb_course1 (
 courseNo CHAR(6) primary key comment '課程號',
 courseName VARCHAR(20) unique not NULL comment '課程名',
 credit DECIMAL(3,1) not NULL comment '學分',
 courseHour TINYINT(2) not NULL comment '課時數', 
 term TINYINT(2) comment '開課學期',
 priorCourse CHAR(6) comment '先修課程'
) engine=InnoDB default charset=gb2312;

注： courseNo CHAR(6) primary key comment '課程號' 是關鍵
次方法是在創建新表的同時創建主鍵索引

1.4. 升序降序索引

按照如下表結構創建tb_score1，
要求使用create index 陳述句對studentNo建立普通降序索引，索引名為idx_studentNo，
對courseNo建立普通升序索引，索引名為idx_courseNo.

CREATE TABLE tb_score1(
 studentNo CHAR(10) NOT NULL comment '學號',
 courseNo CHAR(6) NOT NULL comment '課程號',
 credit DECIMAL(4,1) not NULL comment '成績'
) engine=InnoDB default charset=gb2312;

alter table tb_score1 add index idx_studentNo(studentNo desc);
alter table tb_score1 add index idx_courseNo(courseNo);

使用create index陳述句創建基于欄位值前綴字符的索引
在tb_course上建立一個索引，要求按課程名稱courseName欄位值的前三個字符建立降序索引，
–函式要再加個括號
alter table tb_course add index idx_courseName1((left(courseName,3)) desc);
DROP INDEX idx_courseName1 on tb_course;

使用alter table陳述句建立普通索引：在tb_score上建立普通索引
要求使用alter table陳述句對courseNo欄位建立普通索引，索引名為idx_courseNo.
alter table tb_score add index idx_courseNo(courseNo);

2.索引的維護

2.1 查看索引

使用show index from tb_student1 \G;
查看tb_student表的索引，顯示內容中主要引數說明：
Table: 指明索引所在表的名字，
Non_unique: 該索引是不是唯一索引，如果是唯一索引，該值為0；如果不是唯一索引，該值為1.
Key_name: 索引的名字，如果在創建索引陳述句里使用了primary key關鍵字，且沒有明確給出索引名，則系統會為其指定一個索引名PRIMARY，
Column_name: 建立索引的列名稱.
Collation: A表示升序；NULL表示沒有排序，

2.2 洗掉普通索引

洗掉tb_student1表中idx_studentName索引
DROP INDEX idx_studentName on tb_student1;

2.3 洗掉主鍵索引

洗掉在表tb_student中定義的主鍵約束
Alter table tb_student drop primary key;
添加主鍵約束，用alter table陳述句在tb_student對studentNo重新添加主鍵，
Alter table tb_student add primary key(studentNo);

3.技能拓展

3.1 索引介紹

基于InnoDB，資料結構都是B+樹，特點是：
主鍵索引存盤的是Mysql整個資料行
普通索引存盤的是索引列和主鍵的值

Mysql 8.0.13版本及以上已經出現了函式索引

3.2 索引的創建原則

a.出現Where子句中的列（比較頻繁）
b.索引的基數越大的越好（唯一值）
c.短索引（列的長度）
d.排序 order by 、分組 group by (適合建索引)
e.資料量少的做索引

大家點贊、收藏、關注、評論啦 👇🏻👇🏻👇🏻微信公眾號👇🏻👇🏻👇🏻