day07-2MySQL索引

• 2022 年 10 月 7 日
• 筆記
• 數據庫基礎

MySQL索引

說起提高數據庫性能，索引是最物美價廉的東西了。不用加內存，不用改程序，不用調sql，查詢速度就能提高千百倍。

例子

首先，創建一個有800萬條數據的表

-- 創建測試數據庫 tmp
CREATE DATABASE tmp;

CREATE TABLE dept( /*部門表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;

#創建表EMP僱員
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*編號*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上級編號*/
hiredate DATE NOT NULL,/*入職時間*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*紅利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部門編號*/
) ;

#工資級別表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
);

#測試數據
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

DELIMITER $$

#創建一個函數，名字 rand_string，可以隨機返回我指定的個數字符串
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #該函數會返回一個字符串
BEGIN
#定義了一個變量 chars_str， 類型  varchar(100)
#默認給 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
 DECLARE chars_str VARCHAR(100) DEFAULT
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0; 
 WHILE i < n DO
    # concat 函數 : 連接函數mysql函數
   SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
   SET i = i + 1;
   END WHILE;
  RETURN return_str;
  END $$


 #這裡我們又自定了一個函數,返回一個隨機的部門號
CREATE FUNCTION rand_num( )
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(10+RAND()*500);
RETURN i;
END $$

 #創建一個存儲過程， 可以添加僱員
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把autocommit設置成0
 #autocommit = 0 含義: 不要自動提交
 SET autocommit = 0; #默認不提交sql語句
 REPEAT
 SET i = i + 1;
 #通過前面寫的函數隨機產生字符串和部門編號，然後加入到emp表
 INSERT INTO emp VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
  UNTIL i = max_num
 END REPEAT;
 #commit整體提交所有sql語句，提高效率
   COMMIT;
 END $$

 #添加8000000數據
CALL insert_emp(100001,8000000)$$

#命令結束符，再重新設置為;
DELIMITER ;

以上創建800萬條數據執行時間為九分十六秒

SELECT COUNT(*) FROM emp;

-- 在沒有創建索引時，我們查詢一條記錄
SELECT * FROM emp WHERE empno = 1234567 -- 使用了9.29秒

-- 使用索引來優化一下
-- 在沒有創建索引前，emp.ibd的大小是524m
-- 創建empno索引後emp.ibd的大小是655m[索引本身也會佔用空間]
-- 創建ename索引後,emp.ibd的大小是827m

-- empno_index :索引名稱
-- on emp(empno):表示在emp表的empno列創建索引
CREATE INDEX empno_index ON emp(empno)

-- 在創建索引後查詢一條記錄
SELECT * FROM emp WHERE empno = 1234567 -- 使用了0.003秒


-- 創建索引後只對創建索引的列有效
SELECT * FROM emp WHERE ename = 'KsInoJ';-- 使用了9.664秒

CREATE INDEX ename_index ON emp(ename)
SELECT * FROM emp WHERE ename = 'KsInoJ';-- 使用了0.914秒

可以看到在創建了索引之後查詢速度有了飛速的提升

1.索引原理

• 索引的原理

沒有索引為什麼會變慢？因為會進行全表掃描

有索引為什麼會變快？會形成一個索引的數據結構，比如二叉樹、B樹等

• 索引的代價：
  • 磁盤佔用
  • 對dml（update delete insert）語句的效率影響

雖然索引對dml語句效率有影響，但是在項目開發中絕大多數操作是select，利大於弊

2.索引的使用

• 索引的類型

1. 主鍵索引，主鍵自動地為主鍵索引（類型 primary key）

2. 唯一索引（unique），unique索引

3. 普通索引（index）

4. 全文索引（fulltext）[適用於MylSAM]

   一般開發不使用mysql自帶的全文索引，而是使用：全文搜索 Solr 和 ElasticSearch(ES)

• 語法

1. 創建索引

   方法一：

   create [unique] index index_name on table_name(col_name[(length)] [asc|desc],...);
   

   方法二：

   alter table table_name add index [index_name] (index_col_name,...);
   

2. 添加主鍵索引

   alter table 表名 add primary key(列名,...);
   

3. 刪除索引

   DROP INDEX id_name ON table_name;
   

   alter table table_name drop index index_name;
   

4. 刪除主鍵索引

   ALTER TABLE table_name DROP PRIMARY KEY;
   

5. 查詢索引（三種方式）

   show index(es) from table_name;
   show keys from table_name;
   desc table_name;
   

例子

-- 演示mysql索引的使用
-- 創建索引

CREATE TABLE t25(
	id INT,
	`name` VARCHAR(32)
);

-- 1.查詢表是否有索引
SHOW INDEXES FROM t25;

-- 2.添加索引
-- 2.1添加唯一索引
CREATE UNIQUE INDEX id_index ON t25 (id);

-- 2.2添加普通索引
CREATE INDEX name_index ON t25 (`name`);

-- 如何選擇？
-- 如果某列的值不會重複，則優先考慮使用unique索引，否則使用普通索引

-- 添加普通索引方式2
ALTER TABLE t25 ADD INDEX id_index (id);

-- 2.3添加主鍵索引
CREATE TABLE t26(
	id INT,
	`name` VARCHAR(32)
);
ALTER TABLE t26 ADD PRIMARY KEY(id);

SHOW INDEXES FROM t26;

-- 刪除索引
DROP INDEX id_index ON t25;
DROP INDEX name_index ON t25;

-- 刪除主鍵索引
ALTER TABLE t26 DROP PRIMARY KEY;

-- 修改索引：先刪除，再添加新的索引

-- 查詢索引
-- 方式1
SHOW INDEX FROM t25;
-- 方式2
SHOW INDEXES FROM t25;
-- 方式3
SHOW KEYS FROM t25;
-- 方式4
DESC t25;

• 練習

3.小結

那些列上適合使用索引？

1. 較頻繁地作為查詢條件的字段應該創建索引
2. 唯一性太差的字段不適合單獨創建索引
3. 更新非常頻繁的字段不適合創建索引
4. 不會出現在where子句中的字段不該創建索引