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索引的优势是精确查找的话，速度会更快，但
- Hash索引不适合范围查找，而B+树特别适合范围查找（特别是聚簇索引的时候）
- Hash索引每次查询要加载所有的索引数据到内存当中，而B+树只需要根据匹配规则选择对应的叶子数据加载即可
B树和B+树的区别
一颗m阶B树具有以下特点：（阶数表示最多有几个孩子）
- 根节点的关键字数目：1 <= k <= m-1，非根节点的关键字数目：m/2 <= k <= m-1
- 每个节点中关键字从小到大排列，而且左子树 < 根 < 右子树
- 叶子节点处在同一层
B+树在B树基础之上的不同之处：
- 非叶子节点只存储索引，不存储数据
- 叶子节点按关键字大小顺序链接
为啥用B+树不用B树咧？
每个非叶子节点存储的索引更多，树的层级更少，查询数据更快
所有查询都到叶子节点，性能稳定
范围查找遍历叶子链表，不需要重复遍历
REGEXP正则表达式
| 模式 | 描述 |
|---|---|
| ^ | 匹配输入字符串的开始位置。 |
| $ | 匹配输入字符串的结束位置。 |
| . | 匹配除 "\n" 之外的任何单个字符。要匹配包括 ‘\n‘ 在内的任何字符，请使用 ‘[.\n]‘ 的模式。 |
| [...] | 字符集合。匹配所包含的任意一个字符。 |
| [^...] | 非字符集合。匹配未包含的任意字符。 |
| [a-z] | 字符范围。匹配指定范围内的任意字符。 |
| [^a-z] | 负值字符范围。匹配任何不在指定范围内的任意字符。 |
| p1|p2|p3 | 匹配 p1 或 p2 或 p3。 |
| * | 匹配前面的子表达式零次或多次。 |
| + | 匹配前面的子表达式一次或多次。 |
| {n} | n 是一个非负整数。匹配确定的 n 次。 |
| {n,m} | m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
事务并发的问题
- 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。
- 不可重复读：事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据作更新并提交，导致事务A多次读取同一数据时结果不一样。
- 幻读：事务 A 按一定条件查询，事务B插入一条数据，事务A再查时结果多一条数据。
事务的四种隔离级别
读未提交
可以读到事务没提交的数据，会导致脏读
读提交（sql server，oracle默认）
事务提交后才能读，解决了脏读，但不可重复读
可重复读（mysql默认）
事务开始后读到的数据不受其他事务影响，但仍旧幻读
串行化
所有操作流水进行，并发性能极差
乐观锁与悲观锁
悲观锁
数据处理过程中锁定数据，依靠数据库的锁机制实现
注意若不是索引而是全表扫描将会锁表
乐观锁
利用数据版本号（增加version字段）
读取时读version，更新时++version；提交更新时判断version与第一次读出是否一致，若不一致数据过期，更新作废。
锁的种类
lock tables test_tbl read; -- lock tables test_tbl write; -- unlock tables;
共享锁
加锁后其他事务可以读但不可以写
排他锁
加锁后其他事务不能读也不能写
间隙锁
当范围查找并申请锁时，会对范围内但不存在的数据加锁以防止幻读