MySQL 索引（入門）：

一、介紹

　　1.什么是索引？

　　一般的應用系統，讀寫比例在10:1左右，而且插入操作和一般的更新操作很少出現性能問題，在生產環境中，我們遇到最多的，也是最容易出問題的，還是一些復雜的查詢操作，因此對查詢陳述句的優化顯然是重中之重，說起加速查詢，就不得不提到索引了，

　　2.為什么要有索引呢？

　　　　引在MySQL中也叫做“鍵”，是存盤引擎用于快速找到記錄的一種資料結構，索引對于良好的性能
　　　　非常關鍵，尤其是當表中的資料量越來越大時，索引對于性能的影響愈發重要，
　　　　索引優化應該是對查詢性能優化最有效的手段了，索引能夠輕易將查詢性能提高好幾個數量級，
　　　　索引相當于字典的音序表，如果要查某個字，如果不使用音序表，則需要從幾百頁中逐頁去查，

　　3.主要優點：

　　　　索引大大減小了服務器需要掃描的資料量，從而大大加快資料的檢索速度，這也是創建索引的最主要的原因，

　　　　可以加速表和表之間的連接，特別是在實作資料的參考完整性方面特別有意義，

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

　　4.缺點：

　　　　創建索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加
　　　　索引需要占物理空間，除了資料表占用資料空間之外，每一個索引還要占用一定的物理空間，
　　　　表中的資料進行增、刪、改的時候，索引也要動態的維護，這就降低了整數的維護速度
　　　　對于非常小的表，大部分情況下簡單的全表掃描更高效

二、索引的本質

　　 索引原理：索引的目的在于提高查詢效率，與我們查閱圖書所用的目錄是一個道理：先定位到章，然后定位到該章下的一個小節，然后找到頁數，相似的例子還有：查字典，查火車車次，飛機航班等

　　索引本質：通過不斷地縮小想要獲取資料的范圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件，也就是說，有了這種索引機制，我們可以總是用同一種查找方式來鎖定資料，

三、MySQL的索引分類

1.普通索引 index :加速查找


2.唯一索引　　與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值，如果是組合索引，則列值的組合必須是唯一的，創建方法和普通索引類似，
    主鍵索引：primary key ：加速查找+約束（不為空且唯一）
    唯一索引：unique：加速查找+約束 （唯一）

3.聯合索引（組合索引）　　平時用的SQL查詢 陳述句一般都有比較多的限制條件，所以為了進一步榨取MySQL的效率，就要考慮建立組合索引，

    -primary key(id,name):聯合主鍵索引
    -unique(id,name):聯合唯一索引
    -index(id,name):聯合普通索引

4.全文索引 fulltext :用于搜索很長一篇文章的時候，效果最好，

5.空間索引 spatial :了解就好，幾乎不用

　　案例：

CREATE DATABASE IF NOT EXISTS info DEFAULT CHARSET utf8;
USE info;
# 創建表
CREATE TABLE IF NOT EXISTS emp(emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20),
salary INT,
dept_id INT,
manager_id INT
);

#　插入資料
INSERT INTO emp (emp_name,salary,dept_id,manager_id)VALUES
("張三",1000,1,1),
("李四",2000,1,1),
("王五",5000,2,3),
("林青霞",8000,3,6),
("風清揚",6000,5,4),
("花無缺",10000,5,5),
("景天",14000,8,7),
("趙敏",17000,8,7);
# 給一個列的每個值 創建單獨標記 利用索引創建的這些標記可以進行一個排序，更快查詢 
SELECT * FROM emp WHERE emp_name = "李四";

# 1.1 創建索引:單獨創建索引
CREATE INDEX index_name ON emp(emp_name);
SELECT * FROM emp WHERE emp_name = "李四";

# 1.2 創建索引：修改表結構創建索引
ALTER TABLE emp ADD INDEX index_salary(salary);

# 1.3 創建索引：創建表時，創建索引
CREATE TABLE IF NOT  EXISTS article(id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(30) NOT NULL,
content VARCHAR(50),
dt DATE,
PRIMARY KEY(id),
INDEX index_title(title)
);

# 2 洗掉索引
DROP INDEX index_name ON emp;

# 3.1 唯一索引：索引標記的列的值必須唯一
CREATE UNIQUE INDEX index_name ON emp(emp_name);

# 3.2 修改表時創建唯一索引
ALTER TABLE article ADD UNIQUE INDEX index_content(content);

# 3.3 創建表時創建唯一索引
CREATE TABLE IF NOT EXISTS test (id INT NOT NULL AUTO_INCREMENT ,
tname VARCHAR(20) NOT NULL,
age INT NOT NULL,
s_id INT NOT NULL,
PRIMARY KEY (id),
UNIQUE INDEX index_id(s_id)
) 
 
# 4.1 單獨創建組合索引
CREATE INDEX index_ct ON article(content,dt);
 SELECT * FROM article WHERE content='123' AND dt = "2020-10-10"

# 4.2 修改表結構時創建組合結構
ALTER TABLE test  ADD INDEX index_to(tname,age);

# 4.3 創建表時創建組合索引
CREATE TABLE IF NOT  EXISTS test02(id INT NOT NULL AUTO_INCREMENT,
tname VARCHAR(20) NOT NULL,
age INT NOT NULL,
s_id INT NOT NULL,
PRIMARY KEY (id),
UNIQUE INDEX index_to(s_id,id)
)

四、復合索引

　　一列的索引稱為單列索引，多列的稱為復合索引，因為BTREE索引是順序排列的，所以比較適合范圍查詢，但是在復合索引中，還應注意列數目、列的順序以及前面范圍查詢的列對后邊列的影響，

# 建表
create table staffs(
    id int primary key auto_increment,
    name varchar(24) not null default '' comment '姓名',
    age int not null default 0 comment '年齡',
    pos varchar(20) not null default '' comment '職位',
    add_time timestamp not null default current_timestamp comment '入職時間'
  ) charset utf8 comment '員工記錄表';

# 添加三列的復合索引
alter table staffs add index idx_nap(name, age, pos);

#1. 全值匹配
select * from staffs where name = 'July' and age = '23' and pos = 'dev' ;

#2. 匹配最左列    對于復合索引來說，不總是匹配所有欄位列，但是可以匹配索引中靠左的列，
select * from staffs where name = 'July' and age = '23';   
# key欄位顯示用到了索引，注意，key_len欄位（表示本次陳述句使用的索引長度）數值比上一條小了，意思是它并未使用全部索引列，事實上只用到了name和age列

#3. 匹配列前綴
select * fromstaffs where name like 'J%'；
# 即一個索引中列的前一部分，主要用在模糊匹配

#4. 匹配范圍
select * from staffs where name > 'Mary'；

#5. 精確匹配一列并范圍匹配右側相鄰列
select * from staffs where name = 'July' and age > 25；
# 即前一列是固定值，后一列是范圍值，它用了name與age兩個列的索引（key_len推測）

#6. 只訪問索引的查詢

　select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev';

　select name,age from staffs where name = July and age > 25;

　# 比如staffs表的情況，索引建立在(name,age,pos)上面，前面一直是讀取的全部列，如果我們用到了哪些列的索引，查詢時也只查這些列的資料，就是只訪問索引的查詢

標籤：其他

上一篇：如何本地navicat連接虛擬機安裝的linux 的mysql

下一篇：MySQL變數、流程控制和游標