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秒

可以看到在创建了索引之后查询速度有了飞速的提升
1.索引原理