Mysql基础（五）索引

一、索引的体验

说起提高数据库性能，索引是最物美价廉的东西了，不用加内存，不用改程序，不用调sql，查询速度就可能提高百倍千倍

举例说明：

构建海量数据表【8000000】，对比与索引与无索引的区别

测试数据和表：

copy
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 ;


-- 创建索引
-- empno_index 表示索引名
-- emp(empno) 表示在emp表的empon字段上创建
create index empno_index on emp(empno)

-- 索引本身也会占用空间

-- 创建索引后查询的速度
select * from emp 
where empno = 1234568

-- 没有在ename创建索引时，查询速度还是很慢
select * from emp 
where ename = 'axJxCT'

二、索引介绍

1、为什么没有索引会慢？

假如有一张员工表（emp），在没有索引时，执行 select * from emp where id = 1，那么数据表就会进行一次全表扫描，一条一条的去比较查找，即使第一条就找到匹配的数据，但是不会立刻返回找到的数据，而是会继续往下找，因为不知道数据表中是否还会有匹配的数据，仍然进行全表扫描，这就导致查询速度很慢

2、使用索引为什么快？

当在 id 列上创建索引，那么 id 列就会形成一颗二叉树搜索树

索引的简单原理图：

3、索引的代价：

（1）磁盘占用

（2）对（update delete insert）语句的效率影响

三、索引类型

1、主键索引，主键自动的为主键索引（类型 primary key）

copy
create table t1(
    -- 主键，同事也是索引，称为主键索引
	id int primary key,
    name varchar(32)
)

2、唯一索引（unique）

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

3、普通索引（index）

4、全文索引（fulltext）【使用于MyISAM】，开发中考虑使用：全文搜索 Solr 和ElasticSearch（ES）

四、索引的使用

1、添加索引

copy
-- 方式一
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、添加主键（索引）

copy
alter table 表名 add primary key(列名)

3、删除索引

copy
-- 方式一
drop index index_name on table_name
-- 方式二
alter table table_name drop index index_name

4、删除主键索引

copy
alter table table_name drop primary key

5、查询索引

copy
-- 方式一
show index from table_name
-- 方式二
show indexes from table_name
-- 方式三
show keys from table_name

测试代码：

copy
-- 测试表
create table t25(
 id int,
 `name` varchar(32)
)

-- 查询表是否有索引
show indexes from t25

-- 添加索引
-- 方式一
-- 添加唯一索引
create unique index id_index on t25(id)
-- 添加普通索引
create index id_index on t25(id)
-- 方式二
-- 添加普通索引
alter table t25 add index id_index(id)

create table t26(
 id int,
 `name` varchar(32)
)
-- 添加主键索引
-- 方式一，在创建表是指定字段为 primary key 就是添加主键索引
-- 方式二
alter table t26 add primary key (id)

-- 删除索引
-- 方式一
drop index id_index on t25
-- 方式二
alter table t25 drop index id_index

-- 删除主键索引
-- 主键所用不用指定字段，一张表只有一个主键索引
alter table t26 drop primary key

show index from t26

-- 修改索引，先删除原先的索引，再添加新的索引

-- 查询索引
-- 方式一
show index from t25
-- 方式二
show indexes from t26
-- 方式三
show keys from t25
-- 方式四
desc t25

问题：

1、如何选择唯一索引和普通索引

• 如果某列的值，是不会重复的，则有限考虑使用 unique 索引，否则使用普通索引

五、索引规则

1、比较频繁的作为查询条件字段应该创建索引

copy
select * from emp where empno = 1

2、唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

copy
select * from emp where sex = '男'

3、更新非常频繁的字段不适合创建索引

copy
select * from emp where logincount = 1

4、不会出现在 where 子句中字段不该创建索引