🚀进修MySQL~(叁)
函数
数据函数
作用于记录
-- 绝对值 |
作用于字段
- 也叫
聚合函数
(常用)
/* |
字符串函数
-- 返回字符串包含的字符数 |
时间函数
-- 获取当前日期(两种一样) |
系统信息
-- 版本 |
分组
/* |
MD5 加密
Message-Digest Algorithm 5(信息-摘要算法 5),用于确保信息传输完整一致。是计算机广泛使用的杂凑算法(摘要算法、哈希算法).
初始化
-- 新建表
CREATE TABLE `testmd5` (
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入数据(明文密码)
INSERT INTO
testmd5
VALUES
(1, 'kuangshen', '123456'),(2, 'qinjiang', '456789');加密
-- 加密全列
update
testmd5
set
pwd = md5(pwd);
-- 插入时加密
INSERT INTO
testmd5
VALUES(4, 'kuangshen3', md5('123456'));
-- 检查账号密码是否对应
SELECT
*
FROM
testmd5
WHERE
`name` = 'kuangshen'
AND `pwd` = MD5('123456');
事务
- 一组遵循 ACID 原则的 SQL 指令
ACID 原则
原子性(Atomic)
- 同一批次的 SQL 指令,要么全通过,要么全取消,不存在中间态
一致性(Consist)
- 事务前后数据完整性一致
隔离性(Isolated)
多个用户并发执行多事务,会使其
串行化
执行,同一时间只能有一个请求作用于同一数据有可能隔离失败导致一些问题(如下)
脏读 : 一个事务读取了另一个没有提交的事务
不可重复读 : 在同一个事务内,再次读取表中的数据,表发生了改变
幻读(虚读) : 在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致
持久性(Durable)
- 事务一旦提交成功,便不会被外界因素影响而导致回滚.
基本语法
/* |
逻辑图
例子
CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9, 2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO
account (`name`, `cash`)
VALUES('A', 2000.00),('B', 10000.00);
-- 开始
SET
autocommit = 0;
START TRANSACTION;
UPDATE
account
SET
cash = cash -500
WHERE
`name` = 'A';
UPDATE
account
SET
cash = cash + 500
WHERE
`name` = 'B';
COMMIT;
SET
autocommit = 1;
索引
索引是帮助 MySQL 高校获取数据的数据结构.
- 提高查询,分组,全文搜索速度
- 表间参照&数据唯一性功能.
分类
主键索引 (Primary Key)
- 最常见,唯一性(一个表只能有一个)
唯一索引 (Unique)
- 保证某列中记录值唯一,同一表可以有多个唯一索引
常规索引 (Key/Index)
- 默认的索引类型
全文索引 (FullText)
- MyISAM/InnoDB 引擎下才能使用,对字段类型也有要求,适用于大型数据
添加&删除索引
方法一:创建表时 |
EXPLAIN
/* |
性能分析
建表
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 NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4 COMMENT ='app用户表';生成随机数据
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
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), '[email protected]', 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();- 上面的有可能无效,可以用下面的
{
"schema": "study",
"table": "app_user",
"mockStartIndex": "auto",
"mockCount": 100000,
"mockValueReference": "http://mockjs.com/examples.html#DPD",
"mock": {
"id": {
"type": "bigint",
"value": "$mockIndex"
},
"name": {
"type": "varchar",
"value": "@string('lower',5)"
},
"email": {
"type": "varchar",
"value": "@string('lower',5)"
},
"phone": {
"type": "varchar",
"value": "@string('lower',5)"
},
"gender": {
"type": "tinyint",
"value": "@integer(0,1)"
},
"password": {
"type": "varchar",
"value": "@string('lower',5)"
},
"age": {
"type": "tinyint",
"value": "@integer(0,100)"
},
"create_time": {
"type": "datetime",
"value": "@now('yyyy-MM-dd HH:mm:ss')"
},
"update_time": {
"type": "timestamp",
"value": "@now('yyyy-MM-dd HH:mm:ss')"
}
}
}性能测试
无索引下 select 耗时 & 搜索行数
study> SELECT
*
FROM
app_user
WHERE
name = 'wkjvo'
[2021-03-17 12:06:29] 在 202 ms (execution: 72 ms, fetching: 130 ms) 中从 1 开始检索到1 行
rows: 99946
添加索引
CREATE INDEX idx_app_user_name ON app_user(name);
有索引的 select
study> SELECT
*
FROM
app_user
WHERE
name = 'wkjvo'
[2021-03-17 12:12:11] 在 100 ms (execution: 6 ms, fetching: 94 ms) 中从 1 开始检索到1 行
rows: 1
索引规矩
无索引时是暴力遍历搜索,数据表越大越慢;有索引时是对特定数据结构索引,搜索速度快,但是在增删改时有额外性能开销.
- 不要对
经常变动
的数据加索引 小数据量
的表建议不要加索引- 一般只在查找条件的字段索引
- 不要对
索引数据结构
- hash 单个索引快,范围索引慢
- Btree
不同的数据库引擎所支持的索引类型以及索引数据结构不同
- InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引
- MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引
本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 ⭐️齐下无贰⭐️!
评论