mysql分频段存_哒哒哒之MySQL

知识点

事务

———————

**事务是是指一组sql语句组成的数据库逻辑处理单元，在这组的sql操作中，要么全部执行成功，要么全部执行失败。

--事务的特性 (ACID)

1. 原子性 (Atomicity)：所有操作全部完成或全部不完成，最小的执行单位。undo log

2. 一致性 (Consistency)：事务执行前后都处于一致性状态。redo log

3. 隔离性 (Isolation)：并发执行的各个事务之间不能互相干扰。S/X锁+MVCC

4. 持久性 (Durability)：事务执行完对数据的修改是永久的。

--Redo/Undo机制

1. Redo log记录被修改后的值，用来恢复未写入 data file 的更新成功的数据；

2. Undo log记录数据更新前的值，保证数据更新失败能够回滚。

--事务隔离级别

**默认隔离级别: （select @@transaction_isolation;）

1. 读未提交(Read Uncommitted)：一个事务读到另一个事务未提交读数据。读不加锁，写加排它锁。

2. 读已提交(Read Commited)：一个事务修改的数据提交后才能被另外一个事务读取。每次select生成MVCC版本。

3. 可重复读(Repeatable Read)：保证一个事务相同条件下前后两次获取的数据是一致的。第一次select生成MVCC版本。

4. 串行化(Serializable)：每次读操作都会加锁，快照读失效，事务串行执行。读加共享锁，写加排它锁

--为什么选读已提交作为事务隔离级别

1. 可重复读存在间隙锁，导致出现死锁的几率比RC大的多！

2. 可重复读条件列未命中索引会锁表！而在RC隔离级别下，只锁行

3. 在RC隔离级别下，半一致性读(semi-consistent)特性增加了update操作的并发性！

--并发(事务)问题

1. 脏读：读取被更新但没提交的字段

2. 不可重复读：T1读取了字段，然后T2更新UPDATE了该字段并提交之后，T1再次提取同一个字段，值便不相等

3. 幻读：事务T1从表中读取数据，然后T2进行了INSERT操作并提交，当T1再次读取的时候，结果不一致的情况发生

--锁

1. 共享锁/读锁：SELECT ... LOCK IN SHARE MODE;

2. 排它锁/写锁：SELECT ... FOR UPDATE;

3. 间隙锁：Gap Locks和Next-Key Locks

4. 行锁 5. 表锁 6. 悲观锁 7. 乐观锁

**MySQL 把行锁和间隙锁合并在一起，解决并发写和幻读的问题，这个锁叫做 Next-Key锁

**串行化时，读的时候加共享锁，写的时候加排它锁

**读未提交：读不加任何锁，写加排它锁

存储引擎

———————

MyISAM/InnoDB

1. myisam只支持表级锁，不支持事务、外键，支持全文索引；非聚簇索引(叶子节点存指针)

2. Innodb支持行级锁、事务、外键，不支持全文索引；聚簇索引。通过MVCC(多版本并发控制)解决了幻读问题。

3. myisam本身存储了表的总数据行，Innodb没有存储，查总行数myisam更快，如果加了查询条件两者就没有区别了

InnoDB四大特性：1. 插入缓冲；2. 二次写；3. 自适应哈希索引；4. 预读；

索引

————————————

**索引是一种数据结构。索引中是包含一个表中列的值和它的物理地址的值，并且这些值存储在一个数据结构中。

--优点（检索快，减少I/O次数；加快分组和排序；保证数据唯一性、表间参照完整性）

1. 加快数据检索 （定位快）

2. 保证数据的唯一性 （唯一约束）

3. 实现表与表之间的参照完整性（进行外键约束参照的列）

4. 在使用group by、order by 字句进行查询时，利用索引可以减少排序和分组的时间

--缺点（时间空间，减慢增删改）

1. 创建索引会需要一定的时间和数据空间。

2. 虽然创建索引增加了查询的速度，但是减慢了增删改的速度

**B树索引/哈希索引

1. B树索引具有范围查找和前缀查找的能力，复杂度为O(LogN)。

2. 哈希索引只能做等值查找，复杂度都是O(1)。

--聚簇索引/非聚簇索引

1. 聚簇索引：叶子节点中存放整行数据（数据页），一般是主键索引或非空的唯一索引

2. 非聚簇索引（辅助索引）：存放的是索引（主键值），指向专门的数据页的数据

--聚簇索引的优缺点

优点：

1. 数据访问更快：因为聚簇索引将索引和数据保存在同一个B+树中

2. 聚簇索引对于主键的排序查找和范围查找速度非常快

缺点：

1. 插入速度严重依赖于插入顺序：按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键

2. 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

3. 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

--逻辑角度

1. 普通索引：仅加速查询

2. 唯一索引：加速查询 + 列值唯一（可以有null）

3. 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个（聚簇索引）

4. 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

5. 外键索引：保证数据的一致性、完整性和实现级联操作

6. 全文索引：用大文本对象的列构建的索引

--不推荐使用索引（没必要用）

1. 数据唯一性差（一个字段的取值只有几种时，比如性别）

2. 频繁更新的字段

3. 字段不在where语句

4. 使用不等于(<>)

--索引失效的情况（没用）

1. 条件中有or：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

2. 组合索引：未使最左边的字段（最左前缀原则）

3. like查询以%开头，前导模糊查询不能利用索引

4. 存在索引列的数据类型隐形转换。（1. 列类型是字符串未用引号；2. 索引列上有数学运算；3.使用函数）

5. mysql估计全表扫描更快 （ 比如数据量极少）

**索引失效分析工具：explain命令加在sql语句前面，在执行结果中查看key这一列的值，如果为NULL，说明没有使用索引

--主键和唯一索引的区别

**主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者有本质的差别

1. 主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键；

2. 主键不允许为空值，唯一索引列允许空值；

3. 一个表只能有一个主键，但是可以有多个唯一索引；

4. 主键可以被其他表引用为外键，唯一索引列不可以；

--主键、外键和索引

1. 主键：唯一标识一条记录，不能有重复的，不允许为空；用来保证数据完整性；主键只能有一个

2. 外键：表的外键是另一表的主键, 外键可以有重复的, 可以是空值；用来和其他表建立联系用的，保证一致性；可以有多个外键

3. 索引：该字段没有重复值，但可以有一个空值；是提高查询排序的速度；一个表可以有多个惟一索引

**覆盖索引：索引包含所有满足查询需要的数据，即不需要回表操作，可以优化Limit分页查询的效率

范式

————————————

1. 第一范式：原子性，字段不可分；数据库表中的任何属性都具有原子性的，不可再分解

2. 第二范式：唯一性，有主键且非主键字段依赖主键；对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性

3. 第三范式：非主键字段不能相互依赖；对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余

视图

————————————

**视图本质上是一种虚拟表。

--优点（简化sql查询，提高开发效率）

1. 简单化，数据所见即所得

2. 安全性，用户只能查询或修改他们所能见到得到的数据

3. 逻辑独立性，可以屏蔽真实表结构变化带来的影响

--缺点

1. 性能相对较差，简单的查询也会变得稍显复杂

2. 修改不方便，特变是复杂的聚合视图基本无法修改

查询语言分类

————————————

1. 数据查询语言DQL：SELECT子句，FROM子句，WHERE子句

2. 数据操纵语言DML：INSERT，UPDATE，DELETE

3. 数据定义语言DDL：表、视图、索引、同义词、聚簇等，DDL是隐性提交的，不能rollback，create, drop

4. 数据控制语言DCL：设置或者更改数据库用户或权限的语句，包括GRANT、DENY、REVOKE

删除表

————————————

1. delete : 仅删除表数据，支持条件过滤，支持回滚。记录日志。因此比较慢。

2. truncate: 仅删除所有数据，不支持条件过滤，不支持回滚。不记录日志，效率高于delete。

3. drop:删除表数据同时删除表结构。将表所占的空间都释放掉。删除效率最高。

in/not in/exists/not exists

————————————

1. not exist会对主子查询都会使用索引

2. exist会针对子查询的表使用索引

3. in与子查询一起使用的时候,只针对主查询使用索引

4. not in则不会使用任何索引

**子查询表大的用exists，子查询表小的用in；not exists比not in 快

--游标

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理。

--存储过程

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

--触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。

binlog

————————————

--录入格式

1. statement：保存修改数据的sql语句

2. row：保存修改的记录

3. mixed

技巧

--sql执行顺序

1. from 2. join……on 3. where 4. group by 5. avg,sum.... 6. having

7. select 8. distinct 9. order by 10. limit

--[LIMIT N][ OFFSET M]=limit m,n。limit是在order之后执行的。offset m不包括m，既limit 0,1=limit 1 offset 0=limit 1。

--去重，distinct。group by 可替代distinct。

--inner join...on... 输出两表共同的，可用where直接代替

--在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数（group by）一起使用。HAVING 子句可以让我们筛选分组后的各组数据。

--精确匹配采用=，范围请用in/not in

--sum(amount) over(partition by name order by mon rows between unbounded preceding and current row)

--排序

1. rank()：并列，占位

2. dense_rank()：并列，不占位

3. row_number() over()：不并列，row_number() over([partition by sex] order by age desc) as rank

--求top1/topN

1. 自链接（推荐）

SELECT a.*,b.max_score from sc a

INNER JOIN (

SELECT CId,max(score) max_score

from sc

GROUP BY CId ) b

on a.CId= b.CId and a.score= b.max_score;（每门课程中成绩最好的人）

2. 自查询（数据量不能大）

SELECT * from sc a

where score = (SELECT max(score) from sc where a.CId=CId )

3. EXISTS

SELECT * from sc a where

not EXISTS

(SELECT 1 from sc where a.score <score and a.CId= CId)

4. 窗口函数（推荐)

SELECT SId ,CId,score from (

SELECT SId,CId,score,dense_rank() over (partition by CId ORDER BY score desc) as ranking from sc ) b

where ranking=N; (更改此处条件可随性求top N, top1-N等)

--字符串连接

1. concat(s1, s2, ...)

2. concat_ws(x, s1, s2, ...) 有分割符x

3. group_concat(): 连接字段，多个值显示为一行；group_concat( [DISTINCT] 连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )

--字符串截取

1. left(str, length)

2. right(str, length)

3. substring(str, index[, len]) index>0从左边开始, index<0从右边开始；（sql索引从1开始）

4.

--查询用户的最长连续登陆天数

select uid, max(continuous_days) as maxday from

(

select uid, date_sub(date1,sort) as login_group, count(*) as continuoous_days from

(

select uid,udate, row_number() over(patition by uid order by udate) as sort from user_login

) a

group by uid, login_group

) b

group by uid;

版权所有，翻版不究。