无锁建索引 mysql mysql索引锁事务

转载

coolfengsy 2023-09-28 20:04:13

文章标签 无锁建索引 mysql mysql 数据库 sql 表名 文章分类 MySQL 数据库

MySQL索引事务

1. 索引

1.1 概念

1.2 作用

1.3 使用

1.4 案例

2. 事务

2.1 概念

2.2 使用

3. 内容重点总结

MySQL索引事务

1. 索引

1.1 概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，

并指定索引的类型，各类索引有各自的数据结构实现。

1.2 作用

数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
索引所起的作用类似书籍目录，可用于快速定位、检索数据。
索引对于提高数据库的性能有很大的帮助

1.3 使用

创建主键约束（ PRIMARY KEY ）、唯一约束（ UNIQUE ）、外键约束（ FOREIGN KEY ）时，会自动创建

对应列的索引。

查看索引

show index from 表名;

创建索引

create index 索引名 on 表名(字段名);

对于非主键、非唯一约束、非外键的字段，可以创建普通索引

删除索引

drop index 索引名 on 表名;

1.4 案例

准备测试表：

-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
 id_number INT,
 name VARCHAR(20) comment '姓名',
 age INT comment '年龄',
 create_time timestamp comment '创建日期'
);

准备测试数据批量插入用户数据:

-- 构建一个40000条记录的数据
-- 构建的海量表数据需要有差异性，所以使用存储过程来创建， 拷贝下面代码就可以了，暂时不用理解
-- 产生名字
drop function if exists rand_name;
delimiter $$
create function rand_name(n INT, l INT)
returns varchar(255)
begin
 declare return_str varchar(255) default '';
 declare i int default 0;
 while i < n do 
 if i=0 then
 set return_str = rand_string(l);
 else
 set 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)
begin
 declare lower_str varchar(100) default
 'abcdefghijklmnopqrstuvwxyz';
 declare upper_str varchar(100) default
 'ABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare return_str varchar(255) default '';
 declare i int default 0;
 declare tmp int default 5+rand_num(n);
 while i < tmp do 
 if i=0 then
 set return_str 
=concat(return_str,substring(upper_str,floor(1+rand()*26),1));
 else
 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)
begin
 declare 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))
begin
declare i int default 0; 
set autocommit = 0;  
 repeat
 set i = i + 1;
 insert into test_user values ((start+i) ,rand_name(2, 
5),rand_num(120),CURRENT_TIMESTAMP);
 until i = max_num
 end repeat;
 commit;
end $$
delimiter ;
-- 执行存储过程，添加40000条用户记录
call insert_user(1, 40000);

查询 id_number 为39999的用户信息：

select * from test_user where id_number=39999;

无锁建索引 mysql mysql索引锁事务_sql

为提供查询速度，创建id_number 字段的索引:

create index idx_test_user_id_number on test_user(id_number);

显示我们创建的索引:

无锁建索引 mysql mysql索引锁事务_表名_02

比较执行时间:

无锁建索引 mysql mysql索引锁事务_sql_03

索引保存的数据结构主要为B+树，及hash的方式。

2. 事务

2.1 概念

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。

在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。

2.2 使用

准备测试表：

drop table if exists accout;
create table accout(
id int primary key auto_increment,
name varchar(20) comment '账户名称',
money decimal(11,2) comment '金额'
);
insert into accout(name, money) values
('A', 5000),
('B', 1000);

比如A给B转账2000元:

--A账户减少2000
update accout set money=money-2000 where name = A';
--B账户增加2000
update accout set money=money+2000 where name = 'B';

假如在执行以上第一句 SQL 时，出现网络错误，或是数据库挂掉了，A的账户会减少 2000 ，但是

B的账户上就没有了增加的金额。

解决方案：使用事务来控制，保证以上两句 SQL 要么全部执行成功，要么全部执行失败。

开启事务：start transaction;
执行多条SQL语句
回滚或提交：rollback/commit;

start transaction;
-- A账户减少2000
update accout set money=money-2000 where name = 'A';
-- B账户增加2000
update accout set money=money+2000 where name = 'B';
commit;

说明： rollback 即是全部失败， commit 即是全部成功。