目录
1. 索引
1.1概念
1.2 作用
1.3 使用场合
1.4 使用
查看索引:
创建索引 :
删除索引 :
1.5 案例
2. 事务
2.1事务的概念
2.2使用
1. 索引
1.1概念
索引是一种特殊的文件,包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引, 并指定索引的类型,各类索引有各自的数据结构实现。
1.2 作用
1.数据库中的表、数据、索引之间的关系,类似于书架上的图书、书籍内容和书籍目录的关系。
2.索引所起的作用类似书籍目录,可用于快速定位、检索数据。
3.索引对于提高数据库的性能有很大的帮助。
1.3 使用场合
要考虑对数据库表的某列或某几列创建索引,需要考虑以下几点:
1.数据量较大,且经常对这些列进行条件查询。2.该数据库表的插入操作,及对这些列的修改操作频率较低。3.索引会占用额外的磁盘空间。4.满足以上条件时,考虑对表中的这些字段创建索引,以提高查询效率。反之,如果非条件查询列,或经常做插入、修改操作,或磁盘空间不足时,不考虑创建索引。
1.4 使用
创建主键约束(
PRIMARY KEY
)、唯一约束(
UNIQUE
)、外键约束(
FOREIGN KEY
)时,会自动创建 对应列的索引。
PRIMARY KEY
)、唯一约束(
UNIQUE
)、外键约束(
FOREIGN KEY
)时,会自动创建 对应列的索引。
查看索引:
show index from 表名;
案例:查看学生表已有的索引
show index
from
student;
创建索引 :
对于非主键、非唯一约束、非外键的字段,可以创建普通索引
create
index
索引名
on
表名
(
字段名
);
案例:创建班级表中,name字段的索引
create index idx_classes_name on classes(name);
删除索引 :
drop
index
索引名
on
表名
;
案例:删除班级表中name字段的索引
drop index idx_classes_name on classes;
1.5 案例
准备测试表:
创建用户表DROP TABLE
IF EXISTS test_user;CREATE TABLE
test_user (id_number
INT
,name
VARCHAR
(
20
) comment
‘
姓名
‘
,age
INT
comment
‘
年龄
‘
,create_time
timestamp
comment
‘
创建日期
‘);
–构建一个
8000000
条记录的数据—
构建的海量表数据需要有差异性,所以使用存储过程来创建, 拷贝下面代码就可以了,暂时不用理解—
产生名字drop
function if exists rand_name;delimiter $$create
function rand_name(n
INT
, l
INT
)returns
varchar
(
255
)begindeclare return_str
varchar
(
255
) default
”
;declare i
int
default
0
;while i
if i=
0
thenset
return_str = rand_string(l);elseset
return_str =concat(return_str,concat(
‘ ‘
, rand_string(l)));end if;set
i = i +
1
;end while;return return_str;end $$delimiter ;—
产生随机字符串drop
function if exists rand_string;delimiter $$create
function rand_string(n
INT
)returns
varchar
(
255
)begindeclare lower_str
varchar
(
100
) default‘abcdefghijklmnopqrstuvwxyz’
;declare upper_str
varchar
(
100
) default‘ABCDEFJHIJKLMNOPQRSTUVWXYZ’
;declare return_str
varchar
(
255
) default
”
;declare tmp
int
default
5
+rand_num(n);while i
if i=
0
thenset
return_str=concat(return_str,substring(upper_str,floor(
1
+rand()*
26
),
1
));set
return_str=concat(return_str,substring(lower_str,floor(
1
+rand()*
26
),
1
));end if;set
i = i +
1
;end while;return return_str;end $$delimiter ;—
产生随机数字drop
function if exists rand_num;delimiter $$create
function rand_num(n
int
)returns
int
(
5
)begindeclare i
int
default
0
;set
i = floor(rand()*n);return i;end $$delimiter ;—
向用户表批量添加数据drop
procedure if exists insert_user;delimiter $$create
procedure insert_user(
in
start
int
(
10
),
in
max_num
int
(
10
))begindeclare i
int
default
0
;set
autocommit =
0
;repeatset
i = i +
1
;insert into
test_user
values
((start+i) ,rand_name(
2
,5
),rand_num(
120
),CURRENT_TIMESTAMP);until i = max_numend repeat;commit;end $$delimiter ;—
执行存储过程,添加
8000000
条用户记录call insert_user(
1
,
8000000
);
查询 id_number 为778899的用户信息:
—
可以看到耗时
4.93
秒,这还是在本机一个人来操作,在实际项目中,如果放在公网中,假如同时有
1000个人并发查询,那很可能就死机。select
*
from
test_user
where
id_number=
556677
;
为提供查询速度,创建 id_number 字段的索引:
create index idx_test_user_id_number on test_user(id_number);
换一个身份证号查询,并比较执行时间:
select * from test_user where id_number=776655;
2. 事务
2.1事务的概念
事务指逻辑上的一组操作,组成这组操作的各个单元,要么全部成功,要么全部失败。
在不同的环境中,都可以有事务。对应在数据库中,就是数据库事务。
2.2使用
(
1
)开启事务:
start transaction;
1
)开启事务:
start transaction;
(
2
)执行多条
SQL
语句
2
)执行多条
SQL
语句
(
3
)回滚或提交:
rollback/commit; 说明:rollback
即是全部失败,
commit
即是全部成功。
3
)回滚或提交:
rollback/commit; 说明:rollback
即是全部失败,
commit
即是全部成功。
start transaction;—
阿里巴巴账户减少
2000update
accout
set
money=money-
2000
where
name =
‘
阿里巴巴
‘
;—
四十大盗账户增加
2000update
accout
set
money=money+
2000
where
name =
‘
四十大盗
‘
;commit;
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net