存储层mysql总结

存储层（mysql）

mysql 的使用：

略

mysql的引擎：

1.InnoDB:mysql5.5之后，

+ 灾难恢复性比较好
+ 支持事物。默认的事物的隔离级别为可重复读（通过MVCC实现）
+ 使用的颗粒度为行级锁，可以支持更高的并发；
+ 支持外键
+ 在Innodb中存在着缓存管理，通过缓冲池，将索引和数据全部缓存起来，加快查询速度。
+ InnoDB不保存表的具体行数

2.MyIsam

+ MyISAM拥有较高的插入、查询速度，但不支持事物。
+ 每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16。
+ NULL被允许在索引的列中，这个值占每个键的0~1个字节。

 

区别：

1）InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

2）MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用 。

3）InnoDB支持外键，MyISAM不支持。

4）从MySQL5.5.5以后，InnoDB是默认引擎 。

5）InnoDB不支持FULLTEXT类型的索引 。

6）InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表 。

7）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引 。

8）清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表 。

9）InnoDB支持行锁。

mysql的事物的四大特性：

1、原子性（Atomicity）
​
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
​
2、一致性（Consistency）
​
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。举例来说，假设用户A和用户B两者的钱加起来一共是1000，那么不管A和B之间如何转账、转几次账，事务结束后两个用户的钱相加起来应该还得是1000，这就是事务的一致性。
​
3、隔离性（Isolation）
​
隔离性是当多个用户并发访问数据库时，比如同时操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。
​
4、持久性（Durability）
​
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务已经正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成。否则的话就会造成我们虽然看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。这是不允许的。
​

事务的隔离级别

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted(未授权读取、读未提交)、Read committed（授权读取、读提交）、Repeatable read（可重复读取）、Serializable（序列化），这四个级别可以逐个解决脏读、不可重复读、幻象读这几类问题。

这里写图片描述

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。大多数数据库的默认级别就是Read committed，比如Sql Server , Oracle。MySQL的默认隔离级别就是Repeatable read。

MySQL的默认隔离级别就是Repeatable read。

mysql的锁：

MySQL大致可归纳为以下3种锁：
​
- 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
- 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
- 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
​
MySQL表级锁有两种模式：表共享锁（Table Read Lock）和表独占写锁（Table Write Lock）。
​
- 对MyISAM的读操作，不会阻塞其他用户对同一表请求，但会阻塞对同一表的写请求；
- 对MyISAM的写操作，则会阻塞其他用户对同一表的读和写操作；
- MyISAM表的读操作和写操作之间，以及写操作之间是串行的。
​
当一个线程获得对一个表的写锁后，只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。
​
InnoDB实现了以下两种类型的行锁。
​
- 共享锁（s）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
- 排他锁（Ｘ）：允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。
​
另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
​
意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
​
意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
​
 如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。

总结

对于ＭyISAM的表锁，主要有以下几点

（１）共享读锁（S）之间是兼容的，但共享读锁（S）和排他写锁（X）之间，以及排他写锁之间（X）是互斥的，也就是说读和写是串行的。

（２）在一定条件下，ＭyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表和插入的锁争用问题。

（3）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，ＭyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

对于InnoDB表，主要有以下几点

（１）InnoDB的行销是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。

（２）InnoDB间隙锁机制，以及InnoDB使用间隙锁的原因。

（３）在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

（４）ＭySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响。

（５）锁冲突甚至死锁很难完全避免。

在了解InnoDB的锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

  • 尽量使用较低的隔离级别

  • 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会。

  • 选择合理的事务大小，小事务发生锁冲突的几率也更小。

  • 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁。

  • 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大减少死锁的机会。

  • 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。

  • 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁。

  • 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

mysql的索引：

  • 索引的优点：

    1.通过创建唯一索引，可以保证数据库每一行数据的唯一性

    2.可以大大提高查询速度

    3.可以加速表与表的连接

    4.可以显著的减少查询中分组和排序的时间。

    • 索引的缺点：

    1.创建索引和维护索引需要时间，而且数据量越大时间越长

    2.创建索引需要占据磁盘的空间，如果有大量的索引，可能比数据文件更快达到最大文件尺寸

    3.当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度

  1. 索引的分类：

    1.普通索引(Normal)：基本索引类型，允许在定义索引的列里插入空值或重复值。

    2.唯一索引(Unique)：索引列值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值

    3.单列索引：只包含一个列的索引，一个表中可以有多个

    4.组合索引：包含多个列的索引，查询条件包含这些列的最左边的字段的时候，索引就会被引用，遵循最左缀原则

    5.全文索引(Full Text):在定义的值中支持全文查找，允许空值和重复值，可以在CHAR，VARCHAR或者TEXT字段类型上创建，仅支持MyISAM存储引擎

    6.空间索引：针对空间数据做的索引，支持的数据类型有4种，分别是GEOMETRY,POINT,LINESTRING和POLYGON。创建空间索引的列必须声明为非空值（NOT NULL），仅支持MyISAM存储引擎

  2. 索引的设计原则

    1.不是越多越好

    2.常更新的表越少越好

    3.数据量小的表最好不要建立索引

    4.不同的值比较多的列才需要建立索引

    5.某种数据本身具备唯一性的时候，建立唯一性索引，可以保证定义的列的数据完整性，以提高查询熟度

    6.频繁进行排序或分组的列(group by或者是order by)可以建立索引，提高搜索速度

    7.经常用于查询条件的字段应该建立索引

    mysql存储文件问题：

    innodb存储引擎有两种表空间的管理方式，分别是：

    1）共享表空间（可拆分为多个小的表空间文件），这个是我们目前多数数据库使用的方法；

    2）独立表空间，每一个表有一个独立的表空间。

    ①共享表空间： 优点：可以将表空间分成多个文件存放到不同的磁盘上（表空间文件大小不受表大小的限制，一个表可以分布在不同步的文件上）。 缺点：所有数据和索引存放在一个文件中，则随着数据的增加，将会有一个很大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样如果对于一个表做了大量删除操作后表空间中将有大量空隙。对于共享表空间管理的方式下，一旦表空间被分配，就不能再回缩了。当出现临时建索引或是创建一个临时表的操作表空间扩大后，就是删除相关的表也没办法回缩那部分空间了。 ②独立表空间：在配置文件（my.cnf）中设置： innodb_file_per_table 特点：每个表都有自已独立的表空间；每个表的数据和索引都会存在自已的表空间中。 优点：表空间对应的磁盘空间可以被收回（Drop table操作自动回收表空间，如果对于删除大量数据后的表可以通过:alter table tbl_name engine=innodb;回缩不用的空间。 缺点：如果单表增加过大，如超过100G，性能也会受到影响。在这种情况下，如果使用共享表空间可以把文件分开，但有同样有一个问题，如果访问的范围过大同样会访问多个文件，一样会比较慢。如果使用独立表空间，可以考虑使用分区表的方法，在一定程度上缓解问题。此外，当启用独立表空间模式时，需要合理调整innodb_open_files参数的设置。 解决: 1）ibdata1数据太大：只能通过dump，导出建库的sql语句，再重建的方法。 2）mysql-bin Log太大： ①手动删除： 删除某个日志：mysql>PURGE MASTER LOGS TO ‘mysql-bin.010′; 删除某天前的日志：mysql>PURGE MASTER LOGS BEFORE ’2010-12-22 13:00:00′; ②在/etc/my.cnf里设置只保存N天的bin-log日志 expire_logs_days = 30 //Binary Log自动删除的天数

    MySQL优化--分区、分表、分库

    • 为什么要分表和分区？

    日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。

    • 什么是分表？

    分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。

    • 什么是分区？

    分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

    • 分表和分区比较

    • 分区比较简单，由数据库自身维护数据关系。

    • 分表比分区更复杂，但是性能稍微好一点点。

    • 但分表会造成业务代码更复杂，要通过代码指定数据存储到特定的表。

    • 错误的分表操作，容易引发其他问题

    数据库分库(物理层面进行拆分)

    • 跨库Join的问题，虽然可以通过基础表多数据库同步复制来解决，但也增加了复杂度。

    • 不同业务的拆分：编程复杂(根据业务选择对应的数据库)，做关联业务联级操作的时候，有分布式事务的问题。

    • 同一类业务的拆分：编程复杂，尤其在涉及到复杂业务查询时，有可能需要多库多次查询，然后进行结果集合并。

    MySQL 分区、分表、分库

    \1. 分区、分表、分库都可以大幅提升数据库读的性能。 \2. 数据库分库可以提升并发写的速度：通过简单的hash取余、日期时间、或业务区域等等，来拆分同一类业务数据（如订单数据）。或者通过不同业务领域，来拆分不同业务数据（商品、客户、订单、采购等等）。 \3. 分区最简单，由数据库自身维护数据关系； 　分表复杂，需要开发人员指定数据读写在哪张子表； 　分库最复杂，除了为对应的数据选择对应的数据库以外，还需要解决跨库的分布式事务问题。 \4. 分区、分表、分库并不冲突，比如在读写分离的业务场景，对读的数据库的某些表进行分区或者分表。或者对于一些容易编程的业务表采用分表，编程复杂的业务用分区，都是可行的。对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。

mysql表字段设计总结：

  1. 尽量避免null列

  • not in ,!=等负向条件下null列返回的是空结果

  • 对于null column，count(null column)是不计入统计结果的

  • null列会占用多一个字节的空间，来表明是否为空

  • 大多数情况下，null列都是可以用默认值来替代的

  1. 尽量使用更简单的数据类型

  1. 简单意味着更低的磁盘存储，内存占用和cpu性能开销，特别是大数据量高并发的情况下，对性能影响比较大

  2. 对于索引列，越小的数据类型，意味着每个数据页能存更多的分支，减少索引的次数

2.1 整数

整数类型主要有： tinyint,smallint,mediumint,int,bigint,分别用8，16，24，32，64个位进行存储，存储范围是-2N-1-1 ~ 2N-1,N表示存储的位数。可以使用unsigned来表明正数，这样可以使存储的上限变成2N-1,将近扩大了一半。 如果确定没有负数，可以使用unsigned 类型，让可存储的上限能扩大一倍，间接的使用更小的数据类型

2.2 实数

1 float和double来表示，属于浮点数，存在精度丢失问题，但是因为cpu原生支持浮点运算，所以性能更好,float最大支持存储4个字节，double最大支持8个字节 2 decimal 属于定点数，精确类型，但是由于是通过mysql实现的精度计算，相对于浮点型来讲性能要差点 实数类型选择： 1 如果精度要求不高，直接使用float或double 2 如果对精度要求高，性能要求不高，可以考虑使用decimal 3 如果对精度和性能都有要求，可以考虑将小数转为整型存储， 比如财务系统，需要精确为分，可以将金额乘以100进行保存到int或bigint里面

2.4 字符串

使用varchar要尽量设定小的字节数，因为mysql在创建临时表或排序的时候，会悲观的给varchar类型分配最大的长度

总结： 1 字段设计时尽量选择小而简单的数据类型越好

　　　  2 除非必要，不使用可空列，用默认值代替

　　     3 varchar类型虽然是变长，也应该按实际所需分配字节

　　　 4 整型不需要指定长度

　　　 5 只有正数考虑用unsigned,可以扩大上限

　　　 6 实数精度要求不高情况，优先使用浮点类型float，double,如果精度要求高可考虑用整型转化或直接使用decimal类型主从同步

主从同步：

全量同步，增量同步。 注意点（1. 如果多个Slave断线了，需要重启的时候，因为只要Slave启动，就会发送请求和主机全量同步，当多个同时出现的时候，就可能会程序紊乱）

mysql主从复制的步骤:

1)在主库与从库都安装mysql数据库;

2) 在主库的my.cnf配置文件中配置server-id 和log-bin;

3) 在登陆主库后创建认证用户并做授权;

4) 在从库的my.cnf配置文件中配置server-id;

5) 登陆从库后，指定master并开启同步开关。

mysql集群：

mysql慢查询：

MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。具体环境中，运行时间超过*long_query_time*值的SQL语句，则会被记录到慢查询日志中。*long_query_time*的默认值为10，意思是记录运行10秒以上的语句。默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

此外，慢查询日志支持将日志记录写入文件和数据库表。

mysql主从一制

pt-table-checksum是查看主从一致性的工具，

mysql树状结构

b-树特征：

1.根结点至少有两个子女。

2.每个中间节点都包含k-1个元素和k个孩子，其中 m/2 <= k <= m

3.每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m

4.所有的叶子结点都位于同一层。

5.每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划

相比于二叉树做了事情？（为什么不用二叉树）

  1. 二叉树的高度和深度比较多，所以每次的io 会比较多，性能比较差，最次的情况会到达叶子结点

  2. b-tree 我门相比于二叉树会低很多，我们最多的操作只是跟内存做了几次交互，性能提升，内存的操作和磁盘的io操作数量级不在一个等级上

  3. 由上看出： B-tree 将二叉树由一个“高瘦”的形象，变成了一个“矮胖”的形象。

掌握b-的结构，b-tree 的增加和删除

B+树的特征：

1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。

2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3.所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

B+树的优势：

1.单一节点存储更多的元素，使得查询的IO次数更少。

2.所有查询都要查找到叶子节点，查询性能稳定。

3.所有叶子节点形成有序链表，便于范围查询。

sql语句的使用：

略

posted @ 2020-08-04 15:53  醉魔君  阅读(236)  评论(1)  编辑  收藏  举报