社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  DATABASE

数据库应用——MySQL事务和索引

stan Z • 4 年前 • 169 次点击  

什么是事务

要么都成功,要么都失败

就是将一组SQL放在一个批次中去执行

事务原则:ACID 原则 原子性,一致性,隔离性,持久性 脏读 幻读

博客参考连接: 大佬写的

原子性(Atomicity)

要么都成功,要么都失败

一致性(Consistency)

事务前后的数据完整性要保证一致

隔离性(Isolation)

事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。

持久性(Durability)

事务一旦提交则不可逆,被持久化到数据库中

隔离所导致的问题
脏读:

指一个事务读取了另外一个事务未提交的数据。

不可重复读:

在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。
(一般是行影响,多了一行)

执行事务

-- =======================  事务  =========================

-- mysql 是默认开启事务自动提交的
SET autocommit = 0 /* 关闭 */
SET autocommit = 1 /* 开启(默认的) */

-- 手动处理事务
SET autocommit = 0  -- 关闭自动提交

-- 事务开启
START TRANSACTION	-- 标记一个事务的开始,从这个之后的 sql 都在同一个事务内


-- 提交:持久化(成功!)
COMMIT

-- 回滚:回到原来的样子(失败!)
ROLLBACK

-- 事务结束
SET autocommit = 1	-- 开启自动提交

-- 了解
SAVEPOINT 保存点名	-- 设置一个事物的保存点
ROLLBACK TO SAVEPOINT 保存点名	-- 回滚到保存点
RELEASE SAVEPOINT 保存点名	-- 撤销保存点
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26

模拟事务

-- 模拟转账
-- 创建shop数据库
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci

-- 创建表
CREATE TABLE `account`(
	`id` INT(3) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL,
	


    
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入数据
INSERT INTO account(`name`,`money`)
VALUES ('A',2000.00),('B',10000.00)

-- 模拟转账:事务
SET autocommit = 0; 	-- 关闭自动提交
START TRANSACTION 	-- 开启一个事务(一组事务)
UPDATE account SET money=money-500 WHERE `name` = 'A'	-- A减少500
UPDATE account SET money=money+500 WHERE `name` = 'B'	-- B加500


ROLLBACK; -- 回滚
select * from account;
+----+------+----------+
| id | name | money    |
+----+------+----------+
|  1 | A    |  2000.00 |
|  2 | B    | 10000.00 |
+----+------+----------+
2 rows in set (0.00 sec)

COMMIT;	-- 提交事务
select * from account;
+----+------+----------+
| id | name | money    |
+----+------+----------+
|  1 | A    |  1500.00 |
|  2 | B    | 10500.00 |
+----+------+----------+
2 rows in set (0.00 sec)

SET autocommit = 1;  -- 恢复默认值
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44

索引

MySQL官方对索引的定义为: 索引(Index)是帮助MySQL高效获取数据的数据结构。

提取句子主干,就可以得到索引的本质:索引是数据结构。

索引的分类

在一个表中,主键索引只能有一个,唯一索引可以有多个

  • 主键索引 (PRIMARY KEY)

    • 唯一的标识,主键不可重复,只能有一个列作为主键
  • 唯一索引(UNIQUE KEY)

    • 避免重复的列出现,唯一索引可以重复,多个列都可以标识为 唯一索引
  • 常规索引(KEY / INDEX)

    • 默认的,index。key关键字来设置
  • 全文索引(FullText)

    • 在特定的数据库引擎下才有,MyISAM
    • 快速定位数据

基础语法

-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕之后,增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student 


-- 增加一个索引	(索引名) 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`);

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student;	-- 非全文索引

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+


EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('田');

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23

测试索引

创建测试表

-- 创建一个测试表

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`


    
phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别(0:男;1:女)',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

-- 5.7版本代码
第一个语句 delimiter 将 mysql 解释器命令行的结束符由”;” 改成了”$$”,
  让存储过程内的命令遇到”;” 不执行
DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
DETERMINISTIC
BEGIN
 DECLARE num INT DEFAULT 1000000;
 DECLARE i INT DEFAULT 0;
 WHILE i < num DO
  INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'24736743@qq.com',CONCAT('18',FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
  SET i = i + 1;
 END WHILE;
 RETURN i;
END $$
SELECT mock_data()$$

+-------------+
| mock_data() |
+-------------+
|     1000000 |
+-------------+
1 row in set (16.59 sec)

--  8.0.18版本代码

DROP FUNCTION IF EXISTS mock_data; -- 写函数之前必须要写,标志:$$
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT -- 注意returns,否则报错。
DETERMINISTIC -- 8.0版本需要多这么一行
BEGIN
	DECLARE num INT DEFAULT 1000000; -- num 作为截止数字,定义为百万,
	DECLARE i INT DEFAULT 0;
	


    
WHILE i < num DO
	 INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)VALUES(CONCAT('用户', i),'965499224@qq.com', CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
	SET i = i + 1;
	END WHILE;
	RETURN i;
END;
SELECT mock_data();	-- 总耗时     : 41.436 sec

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59

创建索引

SELECT * FROM app_user WHERE `name` = '用户9999';	
1 row in set (0.33


    
 sec)
SELECT * FROM app_user WHERE `name` = '用户999999';	
1 row in set (0.31 sec)

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

-- 创建一个索引
-- id _ 表名 _ 字段名
-- create index 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM app_user WHERE `name` = '用户9999';	
1 row in set (0.00 sec)

SELECT * FROM app_user WHERE `name` = '用户999999';	
1 row in set (0.00 sec)

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';	
-- 只查了1条数据 直接查到数据
-- rows 是 1

-- 删除索引对象:
-- drop index 索引名 on 表名;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

在这里插入图片描述
索引在小数据量的时候,用处不大,但是在大数据的时候,区别十分明显

索引原则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表完全不用加索引
  • 索引一般加在常用来查询的字段上

什么时候考虑给字段添加索引?

  • 数据量庞大。(根据客户的需求,根据线上的环境)
  • 该字段很少的DML操作。(因为字段进行修改操作,索引也需要维护)
  • 该字段经常出现在where子句中。(经常根据哪个字段查询)

注意 :主键和具有unique约束的字段自动会添加索引。
根据主键查询效率较高。尽量根据主键检索。

索引的数据结构

Hash 类型的索引

Btree:InnoDB 默认数据结构

参考资料: 大佬写的

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/111456
 
169 次点击