赏析致敬钟南山和梵净山中国疫情启示录 http://www.binglicar.com/zwwazz/5344.html数据库优化
数据库优化的目标：吞吐量更大，响应速度更快
通过用户反馈，日志分析，服务器资源使用监控，数据库内部状况监控
数据库优化的维度选择：
选择好的DBMS
优化表的设计
优化原则：尽量遵循第三范式（数据结构清晰，减少冗杂，减少增删改数据时容易出现的异常情况）。
若分析查询应用多，尤其多表查询多可以反范式优化。
表字段类型选择，字段可以采用数字型，就不要用字符型，字符长度设计的尽量短些，长度不定时使用VARCHAR类型。
超键：唯一标识属性集
候选键：超键不包括多余属性就是候选键
主键：从候选键中选出一个作为主键
主属性：候选键中的属性
非主属性：不包含在任何候选键中的属性
1NF：数据库表中的任何属性都是原子性，不可再分
2NF：所有非主属性都和候选键有完全依赖
3NF：任何非主属性都不传递依赖主属性
（高级范式满足所有比它低级的范式）
球员player表（球员编号，姓名，球队名称，球队主教）
球队教练传递依赖球员编号，不符合3NF范式，可以分表球员表（球员标号，姓名，球队名称），球队表（球队名称，球队教练）
BCNF：主属性对候选键不能存在部分依赖或传递依赖关系
范式的等级越高，数据表就越多，数据冗杂度也越低，但是相应的性能与读取效率会受到影响而降低，所以有时候可以以空间换时间，以提高冗杂度提高数据库的读取性能。这就是反范式设计。
反范式设计的使用场景：数据仓库（对增删改的实时性要求不强）
当冗余信息有价值或者能大幅度提高查询效率的时候，我们就可以采取反范式的优化。
比如订单中的收货人信息，包括姓名、电话和地址等。
数据仓库与数据库的区别：
数据仓库的设计目的是分析数据，数据表设计的目的在与捕获数据
数据仓库一般保存历史数据，数据表对增删改实时性要求高，存储用户在线。
数据仓库倾向于使用反范式设计，而数据库需要尽量避免数据冗杂，但也允许一定冗杂以提高数据库的查询效率。
3.优化逻辑查询
Sql等价变化
SQL的查询重写有子查询优化、等价谓词重写、视图重写、条件简化、连接消除、嵌套连接消除等。
4.优化物理查询
索引，表连接优化
物理查询优化是将逻辑查询的内容变成可以被执行的物理操作符，从而为后续执行器的执行提供准备。它的核心是高效地建立索引，并通过这些索引来做各种优化。
索引的种类（功能）：
普通索引：基础索引，无约束
唯一索引：在基础索引下，加了数据唯一性约束
主键索引：在唯一索引下，加了数据不为空约束。一张表只能有一个主键索引。
全文索引：全文搜索引擎
索引的种类（物理）：
聚集索引：按照主键来排序存储数据。一个表只能由一个聚集索引，以为表数据只能按照一种排序方式存储。
非聚集索引：非聚集索引专门由索引页顺序存储，但索引指向的内容时随机的。
聚集索引与非聚集索引的区别：
聚集索引的叶子节点存储数据记录，非聚集索引的叶子节点存储数据位置。非聚集索引不会影响数据表的物理存储（索引存储结构）
一个表只能由一个聚集索引，但可以由多个非聚集索引页
聚集索引在数据查找上效率高，但在数据增删改上效率低。
（在Where子句的字段建立索引，查询效率会提高）
索引使用注意事项
数据量少，数据重复度高，重复度超过10%，可以不适用索引。（性别中的男，女）
在where子句中对索引字段进行表达式运算会使索引失效
注意联合索引的使用顺序。创建联合索引会对多个字段建立索引，索引的顺序是（x,y,z）还是（z,x,y）在执行时有很大差别（最左原则）。
索引的数量不是越多越好，多个索引会使得优化器在评估路径时间增加了筛选索引的时间，进而影响评估效率。
索引的存储数据结构B+树
B+树：平衡多路搜索树
有k个孩子节点就有k个关键字。
非叶子节点的关键字也会存在子节点中，并且是子节点中最大或最小的。
非叶子节点只保存索引（此索引指B+树的索引不是数据库索引），不保存数据记录。
所有关键字都出现在叶子节点中，叶子节点连接成一个有序链表，并从小到大排列。
使用B+树的原因：
磁盘的IO操作占据数据库查询的大部分时间，B+树结构可以减少IO操作。
B+树相较B树更‘矮’，且在查询范围时相较B树更高效。
索引的另一个数据结构Hash索引：
通过Hash函数实现。key为索引项，value指向B+树中的叶子节点。
优点：可以快速查找到相应数据项缺点：仅实现=，，和IN查询
在MySql的InnoDB引擎中，会将使用较多的页数‘热页’放在缓存池中，使用Hash索引连接热页，从而起到快速使用的功能，称作‘自适应Hash索引’。
数据库缓冲池的作用：数据库读取数据有三种方式，内存读取，随机读取，顺序读取，重点是随机读取和顺序读取都是在硬盘层面进行，因此需要耗费大量的时间，以此缓存池会将经常使用的数据页留在池中，同时每次读取硬盘数据时，会同时读取数据页的附件的数据页，即顺序读取。顺序读取的方式可以使范围查询语句效率变高。
索引片：数据库根据索引查找数据时搜索的索引片段。
索引片有分为宽索引和窄索引（1到2个索引列），每个非聚集索引保存的数据项会同时保存主键，数据库根据索引找到数据行，又会根据主键再次找到数据表再查找数据行，这叫回表，这会导致检索速率减慢。窄索引会导致回表，因此在设计索引的时候，根据查找语句，避免窄索引。同时索引越宽，检索的索引页越多同样降低速率，因此需要在索引数量上寻找平衡。
在索引片的设计上同时要考虑过滤因子的影响，Where子句中，每一个条件都可以称作谓词，过滤因子可以称作谓词的选择性。谓词的选择性取决于谓词指向的数据中满足条件的行数占总行数的比例，如男女比例一般为50%，这就不是一个好的过滤因子。选择好的过滤因子可以让数据库检索的数据行减少，同样可以提高数据库效率。
设置索引的方向（三星索引）：
将Where子句中，等值谓词条件列作为索引片的开始索引
将GROUPBY和ORDERBY中的列加入索引
将SELECT字段中剩余的列加入索引
索引过多会占据过多的缓冲池空间，同时在增删改数据上成本会增加，因此三星索引很难实现，我们需要在成本与速度上取得平衡。
物理查询的另一个核心：锁
锁的分类（按照锁的力度）：行锁，页锁，表锁
行锁：锁粒度小，锁冲突概率低，可实现并发高，锁开销大，加锁慢，易死锁
页锁：介于行锁与表锁之间
表锁：锁粒度大，锁冲突概率高，可实现并发低，锁开销小，加锁快，不易死锁
（InnoDB和Orcal支持行锁和表锁，MyISAM支持表锁，BDB支持页锁和表锁，SQLSever支持行锁，页锁，表锁）
锁的分类（按照数据库管理角度）：共享锁，排它锁
共享锁：被锁的数据可以被其他事务读取，但不能修改。（在SELECT时会自动锁上共享锁）
排它锁：被锁上的数据其他事务无法查看和修改。（在INSERT，DELETE，UPDATE时会自动为数据挂上排它锁）
意向锁：意向锁只是一种标识，并不是真正意义上的锁，在数据库需要对表上锁时，往往需要遍历表中的行查看是否有数据被挂上行锁，这样效率会将降低，所以在锁上相应的锁后，如行锁，会在行所在表上挂上意向锁，标识告知此表有行被锁，不能挂上表锁。
共享锁有死锁的风险，共享阻止其他事务改动，但自身事务可以，如果同时对一个数
挂上共享锁，（注意是同时），那么两个事务都无法对数据行进行数据改动就会发生死锁。
两种锁方案：乐观锁，悲观锁
乐观锁：认为对同一数据进行读写操作的概率低，不需要对数据库上数据总是上锁，可以在程序（代码，非数据库）层面使用版本号机制和时间机制实现并发保护。
悲观锁：对死锁持保守意见，使用数据库上的上锁操作，总是保持数据的排他性。
乐观锁适合读操作多的场景，悲观锁适合读操作多的场景。
MVCC，乐观锁思想的实现。
事务的隔离等级（‘（）’为无实现右边隔离等级出现的问题异常问题）：
读未提交（脏读）读已提交（不可重复读）可重复度（幻读）串行化
串行化可以解决幻读，但并发程度大大降低，因此使用MVCC机制+next-key解决幻读。
查询优化器还需要确定SQL查询的路径，具体情况有：
单表扫描
两张表的扫描：嵌套循环连接、HASH连接、合并连接
多张表的扫描：多表查询时以为搜索空间很大，所以需要格外注意连接顺序。
查询优化器在逻辑优化后，会通过物理优化技术，通过计算模型对各种访问路径进行估算，寻找代价最小的路径执行。
RBO：基于规则的估算模型，根据经验总结出的规则，来衡量逻辑查询优化后模型的路径代价。
CBO：基于代价的估算模型，根据模型中的路径操作，计算设定好的路径时间代价之和从而得出模型的路径代价。
（Orcal8i之后就是使用CBO）
我们根据自身的配置设置数据库中的路径时间代价，从而更好的估算最好路径。
5.使用Ridis作为缓存
6.库级优化
水平切分，垂直切分数据库
预览时标签不可点收录于话题#个上一篇下一篇
