mysql
语法笔记
= & := 的区别
- 在set和update，两者均表示赋值
- 在select中，：=表示赋值，=表示比较
用变量实现行号
#
SET @rownum = 1；
SELECT tb.*, @rownum:=@rownum+1 AS rowNumber FROM tb
# or
SELECT tb.* from tb , (SELECT @rownum := 0) as bGROUP BY & GROUP_CONCAT
GROUP_CONCAT：将group by后同一组中的值连接起来
临时表和内存表
# 创建临时表
CREATE TEMPORARY TABLE tmp_table(name VARCHAR(10));
# 创建内存表
CREATE TABLE tmp_table(name VARCHAR(10)) ENGINE=MEMORY;临时表 | 内存表 | |
存储 | 表结构和数据均存在内存里 | 表结构存在磁盘，数据存在内存 |
重启 | 表结构和数据均丢失 | 数据丢失但表结构仍存在 |
对外可见 | 对其他session不可见 | 对其他session可见，因此不同session的临时表不能用相同的名字 |
show | show tables不可见 | show tables可见 |
功能实现
实现组内排序
- 对要分组的列进行排序，如money
- 初始化两个变量：
- @v：记录上一money值
- @r：通过判断@v和当前money值，对行号赋值
SELECT * FROM
(
SELECT
t.aid,
t.money,
IF
( @v <> t.money, @n := 1, @n := @n + 1 ) AS num,
IF
( @v <> t.money, @v := t.money, @v ) AS ctype
FROM
( SELECT tb.aid, tb.money FROM tb ORDER BY tb.money, tb.aid ) AS t,
( SELECT @v := 1, @n := 1 ) AS b
) rt
ORDER BY
rt.aidMysql和MongoDB
Mysql | MongoDB | |
类型 | 关系型数据库 | 非关系型数据库 |
- 关系型数据库的事务ACID
- 原子性（atomicity）：事务被视为最小工作单元，整个事务所有操作要么全部提交成功，要么全部失败回滚。
- 一致性（consistency）：数据库总是从一个状态到另一个状态。
- 隔离性（isolation）：分为四个隔离级别
- Read uncommitted（未提交读）：一个事务未提交，另一个可读到其未提交的内容。会出现脏读（Dirty Read）。
- Read committed（已提交读）：只能读取已提交的事务，但是在读取时只加记录锁，不加间隙锁。如果一个事务在读取数据过程中，另一事物新增了记录，则会导致两次读取的数据不同，即不可重复读（Non-Repeatable Read）。大部分数据库都是这种隔离级别，但mysql不是。
- Repeatable read（可重复读）：既有记录锁又有间隙锁，因此多次读取同一范围内的数据结果相同，但有可能发生幻读（Phantom Read）。mysql默认的隔离级别，但它使用了Next-Key Lock曲线解决了幻读的问题。
- Serializable（可串行化）：对所有事务强制串行执行，避免了前面所说的幻读问题，但性能较差。
- 持久性（durability）：一旦事务被提交，那么数据就一定会被写入到数据库中并持久存储起来。若想要回滚，则需要创建一个相反的事务对原操作进行补偿。
- 不同隔离级别会出现的三种问题
- Dirty Read（脏读）：读取到了未提交的数据。
- Non-Repeatable Read（不可重复读）：没有间隙锁，导致多次读取同一区域的数据结果不相同
- Phantom Read（幻读）：若两次读取数据之间有数据新增事务提交，两次读取的结果一致，但是若想增加该数据，会导致id重复无法提交。
数据库和实例
数据库是文件的集合，用户通过实例去访问数据库。
在Unix上，启动一个MySQL实例往往会产生两个进程：
- mysqld：真正的数据库守护进程
- mysqld_safe：用于检查和设置mysqld启动的控制进程，负责监控mysql进程的执行。
mysql的数据提取和存储引擎：InnoDB
- 存储单位从大到小分类：
- 表空间（tablespace）
- 段（segment）
- 区（extent）
- 页（page）
- 表存储原理：表定义和数据索引分开存储，均是通过二进制存储。
- .frm：存储表定义
- .ibd：存储数据索引，使用页作为磁盘管理的最小单位，数据按行存储，没16KB大小的页可以存放2-200行的记录。包括两部分：
- 公用：系统信息和数据库表数据和索引
- 表独有：当前表的数据和相关数据索引
锁
- 锁的种类
- 乐观锁：一种思想锁。对资源进行修改后，在写回时判断资源是否被修改，如果没有修改则进行重试。
- 不会出现死锁，响应速度快，并发量高
- mysql乐观锁一般要自主实现，如增加version进行版本控制
- 悲观锁：真正的锁，只有当锁被释放了，其他线程才能进行操作。
- 一般在冲突频率和重试成本较高时使用
- InnoDB实现的标准行级锁
- 共享锁（Shared Lock）：称为读锁，允许事务对数据进行读取。共享锁之间可兼容
- 在读取数据时需要显示指定加上共享锁：SELECT * FROM test LOCK IN SHARE MODE
- 互斥锁（Exclusive Lock）：称为写锁，允许事务对数据进行删除或更新。互斥锁与其他任何锁都不兼容
- 更新数据时会自动加上互斥锁：SELECT XXX FOR UPDATE
- InnoDB实现了表级锁，称为意向锁（Intention Lock），意向锁可快速让其他事务知道该表是否有（行级）共享锁。
- 意向共享锁：获取（行级）共享锁前，需要先加意向共享锁；
- 意向互斥锁：获取（行级）互斥锁前，需要先加意向互斥锁；
锁的算法
- 记录锁（Record Lock）
- 加到索引记录上的锁。
- 当查询语句通过索引作为过滤条件时，InnoDB会通过索引找到行记录的位置并加锁。
- 但如果过滤条件不是索引时，则因无法提前找到行记录的位置而锁定整个表。
- 间隙锁（Gap Lock）
- 锁定索引记录中一段连续的区域
- 当查询语句对索引的一段区域进行过滤时（WHERE…BETWEEN…AND），会把这段区域加锁，并阻止其他事务进行修改。
- 间隙锁的共享锁和互斥锁不互斥，不同事务可以同时持有一段相同范围的共享锁和互斥锁，但它会阻止其他事务向这个范围中添加新的记录。
- Next-Key锁
- 记录锁和间隙锁的结合。
- 当要更新某个值的数据时，InnoDB会在该值至前值的范围加上Next-Key锁，在该记录后面的范围加上间隙锁。
- Next-Key锁作用就是为了解决幻读的问题。
锁实测
transaction1 和 transaction2对同一张表操作，t1查询数据，t2写数据。
- t1查询不加锁不commit，t2成功写入但t1多次查询结果相同。
mysql默认Repeatable read隔离级别，t1查询没有commit，在其间即使t2插入了新数据，t1多次读到的数据相同。
-- t1
SELECT * FROM tb;
-- t2
INSERT INTO tb VALUES(1,2,3);
COMMIT;- t1查询加锁不commit，t2无法写入。
此时t2一直在等待t1释放锁，直到超时。
-- t1
SELECT * FROM tb LOCK IN SHARE MODE;
SELECT SLEEP(10);
-- t2
INSERT INTO tb VALUES(1,2,3);
COMMIT;- t1查询加锁且commit，t2在t1 commit后成功写入。
-- t1
SELECT * FROM tb LOCK IN SHARE MODE;
SELECT SLEEP(10);
COMMIT;
-- t2
INSERT INTO tb VALUES(1,2,3);
COMMIT;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
