【MySQL进阶之路】索引操作——索引800万条数据示例

目录

创建主键索引

唯一索引的创建

普通索引的创建

查询索引

删除索引

构建800万条数据库

---

个人主页：东洛的克莱斯韦克-CSDN博客

创建主键索引

比较频繁作为查询条件的字段应该创建索引 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件 更新非常频繁的字段不适合作创建索引 不会出现在where子句中的字段不该创建索引

在创建表的时候，直接在字段名后指定 primary key

create table user1(id int primary key, name varchar(30));

在创建表的最后，指定某列或某几列为主键索引

create table user2(id int, name varchar(30), primary key(id));

创建表以后再添加主键

create table user3(id int, name varchar(30));

alter table user3 add primary key(id);

主键索引的特点： 一个表中，最多有一个主键索引，当然可以使符合主键 主键索引的效率高（主键不可重复） 创建主键索引的列，它的值不能为null，且不能重复 主键索引的列基本上是int

唯一索引的创建

在表定义时，在某列后直接指定unique唯一属性。

create table user4(id int primary key, name varchar(30) unique);

创建表时，在表的后面指定某列或某几列为unique

create table user5(id int primary key, name varchar(30), unique(name));

建表后添加

create table user6(id int primary key, name varchar(30)）；

alter table user6 add unique(name);

唯一索引的特点： 一个表中，可以有多个唯一索引 查询效率高 如果在某一列建立唯一索引，必须保证这列不能有重复数据 如果一个唯一索引上指定not null，等价于主键索引

普通索引的创建

在表的定义最后，指定某列为索引

create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) 
);

创建完表以后指定某列为普通索引

create table user9(id int primary key, name varchar(20), email
varchar(30));
alter table user9 add index(name); 

创建一个索引名为 idx_name 的索引

create table user10(id int primary key, name varchar(20), email
varchar(30));

create index idx_name on user10(name);

查询索引

 show keys from 表名

show index from 表名;

删除索引

第一种方法-删除主键索引：

alter table 表名 drop primary key;

第二种方法-其他索引的删除：

alter table 表名 drop index 索引名；

索引名就是show keys from 表名中的 Key_name 字段 

第三种方法方法：

drop index 索引名 on 表名 

构建800万条数据库

drop database if exists `bit_index`;
create database if not exists `bit_index` default character set utf8;
use `bit_index`;

-- 构建一个8000000条记录的数据
-- 构建的海量表数据需要有差异性，所以使用存储过程来创建， 拷贝下面代码就可以了，暂时不用理解

-- 产生随机字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;


-- 产生随机数字
delimiter $$
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
delimiter ;

-- 创建存储过程，向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into EMP values ((start+i)
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;

-- 雇员表
CREATE TABLE `EMP` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


-- 执行存储过程，添加8000000条记录
call insert_emp(100001, 8000000);

把上述代码粘到某个文件中，在MySQL客户端用source 把该数据库恢复出来

source 路径;

该过程会比较久，也会吃大量的服务器资源

查看表结构，并未有主键，或唯一键

查询某个数据时，用了4.24秒

添加索引

alter table EMP add index(empno);

几乎不耗时