Mysql基础

Mysql基础

删除

cmd	删除内容	语言类型	删除速度
drop	数据+结构	DML(可回滚)	快
truncate	数据	DDL（不可回滚）	中
delete	数据	DDL（不可回滚）	慢

说明

1）表结构指表的定义与约束、索引、触发器等

2）delete语句每次从表中删除一行，并将该操作作为事务记录到日志

3）truncate释放存储数据的页来删除数据，再重新创建表

存储引擎

	InnoDB	MyISAM
主键	没有设定主键会自动生成一个6字节的主键（用户透明）	可以无主键
外键支持	√	×
索引类型	聚集索引	非聚集索引
索引位置	索引和数据一起存储	索引和数据分开存储
事务支持	√	×
锁粒度	行锁	表锁
count()	遍历表，开销大（加了where之后两者一致）	保存了表总行数

说明

1）不确定扫描范围时InnoDBy会锁定全表，InnoDB行锁是通过给索引加锁实现的，只有通过索引条件检索数据才使用行级锁。

2）Innode的唯一索引，普通索引等辅助索引（二级索引）需要两次查询，先查询到主键，通过主键在查找数据。

3）数据库操作大多是读操作考虑MyISAM，若频繁读写而且要求数据安全（崩溃恢复）考虑InnoDB

什么时候需要用到索引？

• 主键自动建立索引
• 频繁在where里用到的字段
• 统计，分组，排序字段
• 与其他表关联的外键字段

那什么时候不建议使用索引呢？

• 表记录少
• 经常进行增删改的表
• 数据频繁更新的字段

为什么Mysql不选择Hash索引？

Hash索引的优势是精确查找的话，速度会更快，但

1. Hash索引不适合范围查找，而B+树特别适合范围查找（特别是聚簇索引的时候）
2. Hash索引每次查询要加载所有的索引数据到内存当中，而B+树只需要根据匹配规则选择对应的叶子数据加载即可

B树和B+树的区别

一颗m阶B树具有以下特点：（阶数表示最多有几个孩子）

1. 根节点的关键字数目：1 <= k <= m-1，非根节点的关键字数目：m/2 <= k <= m-1
2. 每个节点中关键字从小到大排列，而且左子树 < 根 < 右子树
3. 叶子节点处在同一层

B+树在B树基础之上的不同之处：

1. 非叶子节点只存储索引，不存储数据
2. 叶子节点按关键字大小顺序链接

为啥用B+树不用B树咧？

• 每个非叶子节点存储的索引更多，树的层级更少，查询数据更快

• 所有查询都到叶子节点，性能稳定

• 范围查找遍历叶子链表，不需要重复遍历

REGEXP正则表达式

模式	描述
^	匹配输入字符串的开始位置。
$	匹配输入字符串的结束位置。
.	匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用 '[.\n]' 的模式。
[...]	字符集合。匹配所包含的任意一个字符。
[^...]	非字符集合。匹配未包含的任意字符。
[a-z]	字符范围。匹配指定范围内的任意字符。
[^a-z]	负值字符范围。匹配任何不在指定范围内的任意字符。
p1|p2|p3	匹配 p1 或 p2 或 p3。
*	匹配前面的子表达式零次或多次。
+	匹配前面的子表达式一次或多次。
	n 是一个非负整数。匹配确定的 n 次。
	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

事务并发的问题

1. 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。
2. 不可重复读：事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据作更新并提交，导致事务A多次读取同一数据时结果不一样。
3. 幻读：事务 A 按一定条件查询，事务B插入一条数据，事务A再查时结果多一条数据。

事务的四种隔离级别

读未提交

可以读到事务没提交的数据，会导致脏读

读提交（sql server，oracle默认）

事务提交后才能读，解决了脏读，但不可重复读

可重复读（mysql默认）

事务开始后读到的数据不受其他事务影响，但仍旧幻读

串行化

所有操作流水进行，并发性能极差

乐观锁与悲观锁

悲观锁

数据处理过程中锁定数据，依靠数据库的锁机制实现

注意若不是索引而是全表扫描将会锁表

乐观锁

利用数据版本号（增加version字段）

读取时读version，更新时++version；提交更新时判断version与第一次读出是否一致，若不一致数据过期，更新作废。

锁的种类

lock tables test_tbl read;
-- lock tables test_tbl write;
-- unlock tables;

共享锁

加锁后其他事务可以读但不可以写

排他锁

加锁后其他事务不能读也不能写

间隙锁

当范围查找并申请锁时，会对范围内但不存在的数据加锁以防止幻读

小表驱动大表

• in先子查询，将内外表join，in适合内表小
• exists遍历循环外表，查看外表有无与内表一致，exists适合内表大