1. 首頁 > 生活百科 > >

day07-2MySQL索引

生活百科day07-2MySQL

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

例子
首先，創建一個有800萬條數據的表
-- 創建測試數據庫 tmpCREATE DATABASE tmp;CREATE TABLE dept( /*部門表*/deptno MEDIUMINTUNSIGNEDNOT NULLDEFAULT 0,dname VARCHAR(20)NOT NULLDEFAULT "",loc VARCHAR(13) NOT NULL DEFAULT "") ;#創建表EMP雇員CREATE TABLE emp(empnoMEDIUMINT UNSIGNEDNOT NULLDEFAULT 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)BEGINDECLARE 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))BEGINDECLARE 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秒【day07-2MySQL索引】可以看到在創建了索引之后查詢速度有了飛速的提升
1.索引原理
  • 索引的原理

day07-2MySQL索引

文章插圖
沒有索引為什么會變慢？因為會進行全表掃描
有索引為什么會變快？會形成一個索引的數據結構，比如二叉樹、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(列名,...);

    推薦閱讀