知识大全
char、varchar和text
MyISAM与InnoDB两者之间区别
索引命中
数据库三大范式
第一范式
定义：每一个列都不可拆分
第二范式
假设有SLC(Sno, Sdept, Sloc, Cno, Grade)，其中Sno是学号，Sdept是学院，Sloc是学生住处，Cno是课程号，grade是分数
- 插入异常：例如插入Sno = 1023，Sdept = CS，SLOC = C12，若该学生未选课，即Cno为空，则插入失败；
- 删除异常：如果学生把自己唯一选的一门课都退了，那他的信息就完全丢失了；
- 数据冗余：某学生选了10门课，那Sdept和Sloc都要重复10次；
- 修改复杂：若学生转系，那他选的所有课的行都要修改。
因此引入第二范式：若关系模式R∈1NF，并且每一个非主属性都完全函数依赖于R的码，则R∈2NF。
即把SLC拆分为：SC（Sno， Cno， Grade） 和 SL（Sno， Sdept， Sloc）。
| Sno | Sdept | Sloc |
|---|---|---|
| 200112 | CS | C12 |
| 200113 | CS | C12 |
| 200114 | CS | C12 |
| 200115 | CS | C12 |
因此引出第三范式，消除传递依赖：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键，即把SL拆分为：
mysql数据类型
具体的数据类型有
-
整数类型
INT(11)影响显示字符的个数，但是不会影响储存空间。 -
字符串类型
区别
VARCHAR用于存储可变长字符串，它比定长类型更节省空间。
VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，否则使用2字节表示。
VARCHAR存储的内容超出设置的长度时，内容会被截断。
CHAR是定长的，根据定义的字符串长度分配足够的空间。
CHAR会根据需要使用空格进行填充方便比较。
CHAR适合存储很短的字符串，或者所有值都接近同一个长度。
CHAR存储的内容超出设置的长度时，内容同样会被截断。
使用策略：
对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。
对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。
使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。
尽量避免使用TEXT类型，查询时会使用临时表，导致严重的性能开销。 -
枚举类型（ENUM）
把不重复的数据存储为一个预定义的集合，
有时可以使用ENUM代替常用的字符串类型，
ENUM存储非常紧凑，会把列表值压缩到一个或两个字节，
ENUM在内部存储时，其实存的是整数，
尽量避免使用数字作为ENUM枚举的常量，因为容易混乱，
排序是按照内部存储的整数。 -
避免使用NULL值
- 在创建数据表时，最好定义为NOT NULL
- NULL值需要占用空间
- 查询语句中包含允许null字段的那些查询，mysql难以优化，而且它会使索引、索引统计和值更加复杂。
B树与B+树
- 在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值；
- B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。
B树与B+树的对比
- B树可以在内部节点储存键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率；
- B+树的内部节点只存放键，因此，一次IO可以把更多的键读到内存中，由于索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。
- 由于B+树的叶节点由一条链相连，因此对全部数据进行遍历或者范围查询性能都比B树要好；
- B+树的查询效率更稳定，都是logN，即树的高度；
索引技术
索引创建的原则
在InnoDB中，通常在主键上建立聚簇索引，聚簇索引和非聚簇索引的区别：
- 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到：a = 1 and b = 2 and d = 4 and c > 3。其实就是建立的联合索引的顺序要与查询语句匹配。
- 对于联合索引，离散大（差异大，例如年龄的离散度大于性别）的字段放到前面，可以通过count函数来查看离散值；
- 较频繁作为查询条件的字段才去创建索引
- 更新频繁字段不适合创建索引
- 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
- 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
- 定义有外键的数据列一定要建立索引
- 创建索引的列最好不要有NULL，应设置为NOTNULL，用0或空串代替NULL
百万级别或以上的数据如何删除
关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。
- 我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
- 然后删除其中无用数据（此过程需要不到两分钟）
- 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
- 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。
InnoDB与MyISAM引擎
| MYISAM | InnoDB | |
|---|---|---|
| 记录存储顺序 | 按照插入顺序 | 按主键大小有序插入 |
| 外键 | 不支持 | 支持 |
| 锁机制（锁机制几乎对用户透明） | 表级锁 | 行级锁，表级锁 |
| 事务 | 不支持 | 支持 |
| insert select | 比较快 | |
| delete update | 比较快 | |
| select count(*) | 内部维护了一个计数器，可以直接调取 | |
| 索引的实现方式 | B+树，myisam是堆表 | B+树，是索引组织表 |
| 哈希索引 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 主键 | 不一定有 | 必须有，用户指定或系统自动生成 |
1.MyISAM和InnoDB索引的区别：
- InnoDB索引是聚簇索引，MyISAM是非聚簇索引；
- InnoDB的主键索引的叶子节点储存着行数据，因此主键索引非常高效；
- MYISAM索引的叶子储存着行数据地址，需要再寻址一次才得到行数据；
- InnoDB非主键索引（辅助索引）的叶子储存着主键，辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。
- 为什么MYISAM查询比较快：
- InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。 - InnoDB表必须有主键（用户没有指定的话会自己找或生产一个主键），而Myisam可以没有
- 推荐用自增ID来作为主键，自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂。
- InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2.InnoDB引擎的4大特性
- 插入缓冲
- 二次写
- 自适应哈希索引
- 预读
数据库查询优化
1.索引字段优化：
- 避免对索引字段进行计算操作
- 避免在索引字段上使用NOT，>，<，!=
- 避免在索引字段上使用is NULL和is NOT NULL，这两个都是不会应用索引的，
- 避免在索引字段上出现数据类型转换，例如…where id = 123456可优化为where id = ‘123456’
- 避免在索引字段上使用函数
- 避免建立索引的列中使用空值
- between and 中间有一步会转义成 >= <= ，所以两者性能相近
2.where应用法则：
- 避免在where子句中使用in，not，or，可以用exist和not exist代替in和not in：select num from table_a where num in (select num from table_b);——>select num from table_a where exists(select num from b where num = table_a.num);
3.select应用法则
- 少使用select *
4.排序的避免
- 避免使用耗费资源的操作，带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL语句会启动SQL引擎执行，耗费资源的排序功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序。
5.LIKE优化
- LIKE '%供应商%'不会利用索引，要改成’具体供应商%'之类的浅醉索引，这样就会利用索引。
6.UNION操作优化
- 如果两个表没有公共行，则用UNION ALL代替UNION，避免排序。
7.联合索引
- MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。
- MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。
- 当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。
数据库的事务
事务的四大原则：
ACID：
- 原子性：一个事务中的操作要么都做，要么都不做事务是不可拆分的。 事务必须以 Commit/Rollback 结束
- 一致性：使数据库从一个一致性状态变到另一个一致性状态。单独运行的事务，必须保证保持数据库的一致状态。
一致性与原子性密切相关。一致性状态：数据库中只包含成功事务提交的结果；不一致状态：数据库中包含失败事务的结果。 - 隔离性：一个事务不被其他事务干扰，各个并发事务之间是独立的。
- 持久性：事务被提交之后，对于数据库的改变时持久的。
脏读、不可重复读、幻读
- 脏读：事务A修改数据之后，事务B读取该数据，结果事务A回滚，导致事务B读出的数据是不正确的；
- 不可重复读：一个事务在两次查询过程中数据不一致，可能是在两次查询之间有其他事务修改了原有的数据；
- 幻读：在一个事务的两次查询中数据**笔数（行的数量）**不一致，例如有一个事务查询了几行(Row)数据，而另一个事务却在此时插入了新的几行数据，先前的事务在接下来的查询中，就会发现有几行数据是它先前所没有的。
事务的隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ-UNCOMMITTED | √ | √ | √ |
| READ_COMMITED | × | √ | √ |
| REPEATABLE-READ | × | × | √ |
| SERIALIZABLE | × | × | × |
- READ-UNCOMMITTED(读取未提交)：持续X锁；
- READ-COMMITTED(读取已提交)：持续X锁+临时S锁
- REPEATABLE-READ(可重复读)：持续X锁+持续S锁
- SERIALIZABLE(可串行化)： 最高的隔离级别，锁定整个范围的键，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
锁
锁的粒度
1.行级锁：
- 力度最细的锁，一次锁一行
- 加锁的开销最大，加锁慢
- 会出现死锁
- 锁冲突概率低
2.表级锁：
- 锁定粒度最大，表示对当前操作的整张表加锁
- 开销小，加锁快
- 不会出现死锁
- 锁冲突概率高
3.页级锁
- 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁，一次锁定相邻的一组记录
- 开销介于行锁与表锁之间
- 会出现死锁
- 锁冲突概率介于表锁与行锁之间
4.InnoDB与MYISAM支持的锁
- InnoDB支持行级锁，行级锁，表级锁，默认为行级锁
- MYISAM支持表级锁
- InnoDB是基于索引来完成行锁
乐观锁与悲观锁
1.乐观锁
-
假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性，比较跟上一次的版本号，如果一样则更新，如果失败则要重复读-比较-写的操作；
-
通过version的方式来进行锁定；
-
乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
2.悲观锁 -
悲观锁就是锁定数据，屏蔽一切可能违反数据完整性的操作，在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制，例如工作锁和互斥锁；
-
如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。
死锁
1.产生死锁的必要条件：
- 互斥条件：进程要求对所分配的资源进行排它性控制，即在一段时间内某资源仅为一进程所占用。
- 请求和保持条件：当进程因请求资源而阻塞时，对已获得的资源保持不放。
- 不剥夺条件：进程已获得的资源在未使用完之前，不能剥夺，只能在使用完时由自己释放。
- 环路等待条件：在发生死锁时，必然存在一个进程–资源的环形链。
触发器
定义
- 触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。
使用场景
- 可以通过数据库中的相关表实现级联更改。
- 实时监控某张表中的某个字段的更改而需要做出相应的处理。
- 例如可以生成某些业务的编号。
- 注意不要滥用，否则会造成数据库及应用程序的维护困难。
触发器的类型
- Before Insert
- After Insert
- Before Update
- After Update
- Before Delete
- After Delete
扫一扫
45万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
