MySQL数据库
2022-03-19 00:30:15 0 举报AI智能生成
MySQL数据库
作者其他创作
大纲/内容
基础概念
为什么需要数据库：1、数据需要持久化；2、数据需要方便查询；3、数据需要方便管理
三大范式：第一范式：每一列都不可再拆分；第二范式：在第一范式的基础上，非主键完全依赖于主键，而不能依赖于主键的一部分（即指表中每一行数据必须与主键相关）；第三范式：在第二范式的基础上，非主键列只依赖于主键，而不能依赖于其它键（不存在传递依赖）
binlog格式
statement：每一条会修改数据的sql都会记录到binlog中
row
mixed
字段与长度
整形：包括tinyint（1字节）、smallInt（2字节）、mediumint（3）字节、int（4字节）、bigint（8字节）正数。正数类型可以被指定长度，如int（11）表示长度为11的int类型，但大多数场景长度时没有意义的，他不会限制值的合法范围，自会影响字符显示的个数。
浮点类型：包含float、double、decimal，float和double都是有取值范围的，支持使用标准的浮点进行近似运算，decimal是进行字符串处理的
字符类型：包括varchar、char、text、blob。varchar用于存储可变长字符串，比定长更节省空间，使用1-2个字节存储字符串长度，小于255字节时，用一个字节，否则用两个字节，超出字段长度时内容会被截断。char是定长的，根据定义的长度给足够的空间，适合存储较短的字符串，或所有值都接近同一个长度，超长同样会被截断
存储引擎
解决数据、索引存储方式的问题，是一套文件系统的实现。数据库管理系统根据引擎进行创建、查询、更新、删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。
memory引擎：所有的数据都在内存中，数据处理速度快，但安全性不高。它使用存储在内存中的内容来创建表，而且数据全部放在内存中，但表结构存在磁盘中。要注意服务器要有足够的内存，内存出现异常、重启或关机都会丢失
myisam引擎：曾经的默认存储引擎，表分成了三个文件：frm存储表结构、myd存储数据、myi存储索引。优点是占用空间小（可被压缩），处理速度快，查询功能更优，缺点是不支持事务的完整性和并发性，不支持hash索引。适合读写插入为主的应用程序，如博客系统、新闻网站
innoDB引擎：提供了事务的处理、回滚、奔溃修复能力、多版本控制、支持外键。需要一个自增列为主键（用于维护索引结构的B+树的特性，不用频繁的分裂）。缺点是读写效率较差、占用较大的数据空间，不支持全文索引
区别：innoDB是聚簇索引，主键索引的叶子节点存储着行数据，主键索引高效，非主键索引的叶子节点存储的是主键和其它带索引的列数据，查询时做到覆盖索引会比较高效；myisam使用的是非聚簇索引，叶子节点的存储是行数据地址，需要再次寻址。
innoDB的四大特性
插入缓冲：指非主键索引的非聚簇索引，会读取到内存，待多个增删改操作完成后，再合并写入到磁盘索引。（存在一定的索引更新不及时或断点索引异常问题，需要考虑恢复后重建索引，log恢复）
二次写
自适应hash索引：创建的时候虽然可选hash索引，但保存后还是btree。自适应hash是指存储引擎会监控各表索引页的查询，如果频繁操作会为这一页的数据创建hash索引，提升系统性能。是通过缓冲池中B+树的页进行构建的，因此速度很快，是索引的索引。由存储引擎自动维护，进行创建和删除
预读
索引
索引是一种特殊的文件，包含着对数据表所有记录的引用指针。索引是一种数据结构，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新表数据，通常的实现方式为b树或b+树。
优缺点：优点：加快数据的检索速度；缺点：时间方面索引的创建和维护需要耗费时间，数据的增删改需要动态维护索引，一定程度上降低了执行效率，空间方面上增加了存储的物理空间。
使用场景：1、作为where条件的查询字段；2、order by的排序字段；3、join的关联字段；4、索引覆盖（指若查询的字段都建立过索引，则直接从索引表中返回，不需要回表）
索引的类型：主键索引（不允许重复、不允许为null，一个表只有一个主键）、唯一索引（不允许重复、允许为null一个表允许多个列、多个唯一索引）、普通索引（没有唯一性限制，允许null值）、全文索引（关键字模糊匹配技术，效率比like高，原理为使用倒排索引在辅助表中存储单词及其文档的映射关系，通过关联数组实现）
索引的数据结构
分支主题
B+树：1、非叶子节点只存储键值（键值指的是数据库的主键）信息；2、所有叶子节点之间都有一个链指针（存储子节点地址的信息）；3、数据记录都存在叶子节点当中。
b树和B+树的区别：B树键值都存在内部节点和叶子节点当中，叶子节点互相独立；B+树内部节点只是键，没有值，叶子节点同时存放键和值，叶子节点之间有链相联
hash索引：通过hash算法将数据转换成定长的hash值，与这条数据的行指针一起存入hash表的相应位置，发生hash碰撞就以链表形式存储
索引的基本原理：索引是用来快速定位寻找指定记录的值的数据。如果没有索引，一般需要遍历整张表。索引的原理就是对数据进行排序：1、把创建了索引的列的内容进行排序；2、对排序的结果生成倒排表；3、在倒排表上拼上数据地址链；4、查询的时候先拿到倒排表内容，再取出数据地址链，拿到具体的数据
索引的创建原则
1、要符合最左匹配原则，是组合索引非常重要的规则，mysql会一直从左到右匹配直到范围查询（范围查询指>、<、between、like）就停止匹配。所以最频繁使用的一列要放在最左边
2、较为频繁作为查询条件的列才去创建索引、更新频繁的字段不适合创建索引、不能有效区分数据的列不适合作为索引（如sex、枚举）等
3、尽量扩展索引而不是新建索引、定义外键的字段尽量建立索引
4、扩展：百万级别的数据如何删除高效：1、先删除索引；2、删除需要删除的元素；3、重建索引
索引的选型与对比
1、B树内部节点同时存放了键值，频繁访问的数据放在根节点能有效提升查询效率，对热点数据的重复查询支持性更好。但叶子节点没有链，需要每一层进行来回遍历，需要更加多的内存置换次数和时间开销
2、B+树内部节点只有键，依次读取可在内存也中存储更加多的键，快速缩小查询范围。叶子节点有链相联，查询数据的时候先从根节点找到叶子节点，再进行链表遍历即可。如果使用的是覆盖索引无需回表查询
3、hash结构进行等值查询速度快，但是无法进行范围查询，不支持索引排序，无法避免回表查询数据
4、为什么使用B+树而不使用B树：1、B树只适合随机索引不适合顺序索引；2、B+树的空间利用率更加高，可减少IO次数。索引文件也比较大无法常驻内存，B+树一个磁盘块能容纳更加多的索引记录，减少了IO消耗；3、B+树查询效率稳定，查询路径几乎一致；4、B+树元素遍历高效，指针节点数据有序
5、B+树支持聚簇索引和非聚簇索引，主键对应的数据是聚簇索引，只要定位到了索引就定位到了数据，无需回表查询；非聚簇索引是索引呵呵数据分开存储的结构，定位到索引的时候回表查询数据，也称辅助索引，但若发生了索引覆盖无需回表查询
事务
四大特性
1、原子性：事务是最小的执行单位，不允许分割。原子性动作要么全部完成，要么全部不完成。强调的是状态，不允许部分成功
2、一致性：数据在执行事务前后保持一致，多个事务对同一个数据的读取时相同的，强调数据的可见性，即指事务中间的数据对外部不可见，只有最初和最终的数据对外可见
3、隔离性：并发事务之间数据库是独立的，事务之间不会互相受到干扰
4、持久性：事务一旦被提交之后，对数据库中的数据的改变是持久的，即便数据库发生故障也不应对其有任何影响
隔离级别
1、脏读：读取到未提交的数据；2、不可重复读：两次查询过程中得到的数据不一致；3、幻读：一般指统计数量多次读取不一致
隔离级别1：读未提交：最低的隔离级别，允许读到未提交的数据。可能发生脏读、幻读、不可重复读
隔离级别2：读已提交：一个事务的更新结果只有在提交之后才能被其它事务读取，可防止脏读，不能防止可重复读和幻读
隔离级别3：可重复读：对同一字段的多次读取结果都是一样的，除非是被本身的事务所修改。可以防止脏读、不可重复读，仍有可能发生幻读
隔离级别4：可串行化：最高的隔离级别，完全服从ACID的隔离级别，所有的事务逐个执行，事务之间不可能发生干扰
锁
锁是并发事务执行数据一致性的保证，是数据访问的一个顺序性的机制
读未提交的情况下，读数据不需加共享锁；读已提交——读操作加共享锁；可重复读——读加共享锁并且事务提交后才释放共享锁
粒度
表级锁：myisam采用，粒度大，加锁简单，实现简单，不会出现死锁，并发量低，锁冲突率高，可分为共享读锁和排他写锁
行级锁：innoDB默认使用，开销大加锁慢，会出现死锁，锁冲突率低，并发高，粒度小。基于索引实现
页级锁：锁定相邻记录，会出现死锁，并发度一般
INNODB锁算法
record-lock：单行记录上的锁，定位到具体的记录加锁
gap-lock：间隙锁，锁定一个范围，不包括记录本身
next-key-lock：锁定一个范围，包括记录本身。是行锁和间隙锁的结合，默认的加锁单位
死锁
指两个或两个以上的事务在同一个资源上相互占用，并请求锁定对方的资源，导致的恶性循环现象
解决办法：1、不同的事务按顺序访问表；2、同一事务尽量减少锁定资源；3、非常容易发生死锁的部分用表级锁
SQL优化
低性能sql语句的定位，最重要的是使用执行计划，可使用explain命令查看执行计划。最重要的是查看是否使用索引、使用了什么类型的索引、索引的相关信息等
sql的生命周期：1、建立链接；2、解析sql并生成执行计划，执行；3、读取数据到内存并进行逻辑处理；4、发送结果到客户端，关闭链接
大数据量表查询的优化思路
1、优化表结构，sql语句，索引
2、应用层加缓存，如cache、redis
3、主从复制，读写分离
4、垂直拆分，降低模块耦合度，将大的系统分割成多个小系统，即分布式
5、水平拆分，设计合理的sharding-cloum，适当冗余数据、将所有全量数冗余到ES（为每个字段都建立一个倒排索引、lucene）、冷热数据迁移、归档
慢查询优化
1、通过配置 slow_query_log开启慢查询日志，配置long_query_time临界时间，sql查询超过临界时间会记录到xx-slow.log中
2、分析sql语句，是否load了额外的数据和列，进行改造重写；分析执行计划，查看索引使用情况；对大查询进行切分成小查询。where条件不要使用isnull、函数、不等号、表达式、in、not in、like等，否则会导致权表扫描
3、考虑表中数据是否过大，应该横向或纵向分库分表
4、应用层面，使用缓存、增加汇总表、使用MySQL缓存等
数据库优化
结构优化：大表拆分、增加中间表、增加临时表、增加冗余字段
数据进程异常处理办法：操作系统top命令、show processlist、kill掉再观察；或者查看是否有大量的session链接进来，考虑限流等
大表优化：1、限制数据查询范围；2、读写分离；3、MySQL缓存、应用缓存。4、垂直拆分表（按列拆分）、水平分表（按数据行拆分）
分库分表后问题解决：1、分布式事务的处理；2、跨库join要在业务层面上避免；3、数据迁移、容量规划、扩容等；4、ID的问题，需要分布式ID的支持（考虑使用美团leaf算法，基于segment到数据库中获取自增ID段并加载到内存）
0 条评论
下一页
