MySQL——索引优化，索引机制，索引类型及其使用

目录

1.索引优化速度

1. – 在没有创建索引时，查询一条记录 SELECT * FROM emp WHERE empno =123569 – 用时2.5s – emp.ibd占用520M
2. – 使用索引 – empno_index索引名称 CREATE INDEX empno_index ON emp(empno) – 索引创建成功后emp.ibd占用635M SELECT * FROM emp WHERE empno =123569 – 0.008s，原来是2.5s
3. 索引创建后，只对创建了索引的列有效

1.创建海量表

CREATETABLE dept(/*部门表*/
deptno MEDIUMINTUNSIGNEDNOTNULLDEFAULT0,
dname VARCHAR(20)NOTNULLDEFAULT"",
loc VARCHAR(13)NOTNULLDEFAULT"");#创建表EMP雇员CREATETABLE emp
(empno  MEDIUMINTUNSIGNEDNOTNULLDEFAULT0,/*编号*/
ename VARCHAR(20)NOTNULLDEFAULT"",/*名字*/
job VARCHAR(9)NOTNULLDEFAULT"",/*工作*/
mgr MEDIUMINTUNSIGNEDNOTNULLDEFAULT0,/*上级编号*/
hiredate DATENOTNULL,/*入职时间*/
sal DECIMAL(7,2)NOTNULL,/*薪水*/
comm DECIMAL(7,2)NOTNULL,/*红利*/
deptno MEDIUMINTUNSIGNEDNOTNULLDEFAULT0/*部门编号*/);#工资级别表CREATETABLE salgrade
(
grade MEDIUMINTUNSIGNEDNOTNULLDEFAULT0,
losal DECIMAL(17,2)NOTNULL,
hisal DECIMAL(17,2)NOTNULL);#测试数据INSERTINTO salgrade VALUES(1,700,1200);INSERTINTO salgrade VALUES(2,1201,1400);INSERTINTO salgrade VALUES(3,1401,2000);INSERTINTO salgrade VALUES(4,2001,3000);INSERTINTO salgrade VALUES(5,3001,9999);DELIMITER $$

#创建一个函数，名字 rand_string，可以随机返回我指定的个数字符串CREATEFUNCTION rand_string(n INT)RETURNSVARCHAR(255)#该函数会返回一个字符串BEGIN#定义了一个变量 chars_str， 类型  varchar(100)#默认给 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'DECLARE chars_str VARCHAR(100)DEFAULT'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';DECLARE return_str VARCHAR(255)DEFAULT'';DECLARE i INTDEFAULT0;WHILE i < n DO# concat 函数 : 连接函数mysql函数SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));SET i = i +1;ENDWHILE;RETURN return_str;END $$

 #这里我们又自定了一个函数,返回一个随机的部门号CREATEFUNCTION rand_num()RETURNSINT(5)BEGINDECLARE i INTDEFAULT0;SET i = FLOOR(10+RAND()*500);RETURN i;END $$

 #创建一个存储过程， 可以添加雇员CREATEPROCEDURE insert_emp(INSTARTINT(10),IN max_num INT(10))BEGINDECLARE i INTDEFAULT0;#set autocommit =0 把autocommit设置成0#autocommit = 0 含义: 不要自动提交SET autocommit =0;#默认不提交sql语句REPEATSET i = i +1;#通过前面写的函数随机产生字符串和部门编号，然后加入到emp表INSERTINTO emp VALUES((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
  UNTIL i = max_num
 ENDREPEAT;#commit整体提交所有sql语句，提高效率COMMIT;END $$

 #添加8000000数据CALL insert_emp(100001,8000000)$$

#命令结束符，再重新设置为;DELIMITER;

2.索引性能测试

SELECTCOUNT(*)FROM emp;-- 在没有创建索引时，查询一条记录SELECT*FROM emp
    WHERE empno =123569-- 用时2.5s-- emp.ibd占用520M-- 使用索引-- empno_index索引名称CREATEINDEX empno_index ON emp(empno)-- 索引创建成功后emp.ibd占用635MSELECT*FROM emp
    WHERE empno =123569-- 0.008s，原来是2.5s-- 索引创建后，只对创建了索引的列有效

2.索引机制

1. 普通查询：没有索引时，查询时进行全表扫描，查询速度慢
2. 索引查询：底层会形成一个二叉树，优化查询速度。
3. 索引代价：磁盘占用，对dml（update delete insert）语句的效率影响

3.索引类型

1.主键索引

主键自动的为主索引（类型peimary key）
create table t1（
id int primary key,-- 主键，同时也是索引，称为主键索引。
name varchar(32));

2.唯一索引

create table t2（
id int unique,-- id是唯一的，同时也是索引，称为unique索引。
name varchar(32));

3.普通索引（INDEX）

1. – 使用索引 – empno_index索引名称 CREATE INDEX empno_index ON emp(empno) – 索引创建成功后emp.ibd占用635M SELECT * FROM emp WHERE empno =123569 – 0.008s，原来是2.5s
2. 索引创建后，只对创建了索引的列有效

4.全文索引

mysql一般不使用

4.索引使用

1.创建索引

1. – 查询表是否有索引
2. – 添加索引 – 添加唯一索引
3. – 添加普通索引方式1 CREATE INDEX id_index ON t25 (id);
4. – 1. 如果某列的值，是不会重复的，则优先考虑使用unique索引, 否则使用普通索引
5. – 添加普通索引方式2 ALTER TABLE t25 ADD INDEX id_index (id)
6. – 添加主键索引 CREATE TABLE t26 ( id INT ,name VARCHAR(32)); ALTER TABLE t26 ADD PRIMARY KEY (id)

-- 创建索引CREATETABLE t25 (
    id INT,`name`VARCHAR(32));-- 查询表是否有索引SHOW INDEXES FROM t25;-- 添加索引-- 添加唯一索引 CREATEUNIQUEINDEX id_index ON t25 (id);-- 添加普通索引方式1CREATEINDEX id_index ON t25 (id);-- 如何选择 -- 1. 如果某列的值，是不会重复的，则优先考虑使用unique索引, 否则使用普通索引-- 添加普通索引方式2ALTERTABLE t25 ADDINDEX id_index (id)-- 添加主键索引CREATETABLE t26 (
    id INT,`name`VARCHAR(32));ALTERTABLE t26 ADDPRIMARYKEY(id)SHOWINDEXFROM t25

2.删除索引

-- 删除索引DROPINDEX id_index ON t25
-- 删除主键索引ALTERTABLE t26 DROPPRIMARYKEY

3.修改和查询索引

1. 修改索引 ， 先删除，在添加新的索引

-- 修改索引 ， 先删除，在添加新的索引-- 查询索引-- 1. 方式SHOWINDEXFROM t25
-- 2. 方式SHOW INDEXES FROM t25
-- 3. 方式SHOWKEYSFROM t25
-- 4 方式DESC t25