服务端

mysql知识点整理(持续更新)

mysql

一、锁相关

1、悲观锁：

  • 悲观的认为每次操作数据的时候都会有人来修改数据，所以使用数据时对其加锁，确保在自己使用的过程中数据不会被别人修改，使用完成后进行数据解锁。由于数据进行加锁，期间对该数据进行读写的其他线程都会进行等待。写入频繁使用悲观锁

    2、乐观锁：

  • 所以每次获取数据的时候都不会进行加锁，但是在更新数据的时候需要判断该数据是否被别人修改过。一般是在sql的where上带版本号或时间戳，写代码时候自己实现，读取频繁使用乐观锁

    3、共享锁：(自己读写，其他读)

  • 如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

    4、排他锁：(只能自己读写)

  • 如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

    5、简要说明为什么会发生死锁?

  • 若干事务相互等待释放封锁，就陷入无限期等待状态，系统就进入死锁

    6、解决死锁的方法应从预防和解除的两个方面着手：

  • 死锁的预防方法：
    ①要求每一个事务必须一次封锁所要使用的全部数据（要么全成功，要么全不成功）
    ②规定封锁数据的顺序，所有事务必须按这个顺序实行封锁。
  • 允许死锁发生，然后解除它，如果发现死锁，则将其中一个代价较小的事物撤消
    ，回滚这个事务，并释放此事务持有的封锁，使其他事务继续运行。

二、事务隔离级别

1、Read uncommitted（读未提交）（脏读）:

  • 在该隔离级别，所有事务都可以看到其它未提交事务的执行结果。可能会出现脏读。

    2、Read Committed（读已提交，简称： RC）（幻读）：

  • 一个事务只能看见已经提交事务所做的改变。因为同一事务的其它实例在该实例处理期间可能会有新的 commit，所以可能出现幻读。

    3、Repeatable Read（可重复读，简称：RR）（默认，结果相同）：

  • 这是 MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。消除了脏读、不可重复读，默认也不会出现幻读。

    4、Serializable（串行）：

  • 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。

    三、索引

    摘自

    1、重要概念

  • 基数：单个列唯一键（distict_keys）的数量叫做基数。
  • 回表：当对一个列创建索引之后，索引会包含该列的键值及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描，应该直接走全表扫描。
    EXPLAIN命令结果中的Using Index意味着不会回表，通过索引就可以获得主要的数据。Using Where则意味着需要回表取数据。

2、优化原则

  • 适合索引的列是出现在where子句中的列，或者连接子句中指定的列；
  • 基数- 较小的类，索引效果较差，没有必要在此列建立索引；
  • 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间；
  • 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

    四、b-tree索、b-tree索引、hash索引

    1、b+tree

  • B+ 树是 B 树的变体，定义基本与 B 树一致，与 B 树的不同点
  • 所有叶子节点中包含了全部关键字的信息
  • 非叶子节点上只存储 key 的信息，这样相对 B 树，可以增加每一页中存储 key 的数量
  • B 树是纵向扩展，最终变成一个 “瘦高个”，而 B+ 树是横向扩展的，最终会变成一个 “矮胖子”
  • 各叶子节点用指针进行连接
  • mysql使用b+树索引
  • innodb索引和数据存在一个文件中
  • myisam下索引叶子节点保存数据的地址而不是数据本身

    2、b-tree

  • 多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点；
  • 所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；
  • 树的每个节点都包含key值和data值，叶子节点可以看作外部节点，不包含任何信息；
  • 如果 data 比较大时，每一页存储的 key 会比较少；当数据比较多时，同样会有：“要经历多层节点才能查询在叶子节点的数据” 的问题。

    3、mysql中的b+tree

  • 在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；
  • 主聚集索引（clustered index）和辅助索引（secondary index）
  • 具体行数据在在聚集索引上，辅助索引叶子节点存储聚集索引的key值，也就是主见
  • 辅助索引的叶子节点并不包含行记录的全部数据
  • 树的磁盘读写代价更低
  • 树的查询效率更加稳定

    4、B*树：

  • 在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；

    5、mysql中的hash 索引

  • Hash索引仅仅能满足“=”,“IN”,“<=>”查询，不能使用范围查询
  • 联合索引中，Hash索引不能利用部分索引键查询
  • Hash索引无法避免数据的排序操作
  • Hash索引任何时候都不能避免表扫描
  • Hash索引遇到大量Hash值相等的情况后性能并不一定会比BTree高

五、分库、分表

1、为什么要分表：

  • 减小数据库的单表负担，缩短查询时间，减少锁等待的情况。

    表垂直分区

  • 减少一个表中的字段，根据id关联起来

    表水平分区

  • 使用规则（例如按id哈希、id区间、时间）分组，可以单库多表或多库多表

    分库分表后查询问题

  • 单库多表
    如果分表为myisam引擎的表，可以创建一个merge引擎的集合表来查询（merge引擎要求被合并表中不能又重复字段）
    如果是innodb引擎，只能根据规则单独查询在合并或使用搜索引擎解决
  • 多库多表
    将数据通过同步插件(例如logstash)或者mq同步到Elasticsearch，然后通过lasticsearch查询，
    如果数据量过大可以引入hbase 将数据存到hbase中，然后通过lasticsearch存储hbase的rowkey，然后查到rowkey去hbase中取数据

六、引擎

1、innodb：

  • MySQL 5.5 之后的默认存储引擎
  • 默认引擎
  • 支持行锁
  • 索引叶子节点存储数据
  • 支持事务
  • 默认select不加锁，UPDATE、INSERT、DELETE默认都是排它锁，可以通过（lock in share mode）和（for update）加共享锁和排他锁：
  • Record Lock：单个记录上的索引加锁，Gap Lock：间隙锁，对索引项之间的间隙加锁，但不包括记录本身，Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。
  • 如果不通过索引条件检索数据，那么 InnoDB 将对表中所有记录加锁，实际效果跟表锁一样（where不命中索引就锁表）。
  • 不擅长快速插入（插入前要排序，消耗时间）
  • .ibd文件存放索引和数据

    2、myisam：

  • MySQL 8.0弃用
  • MySQL 5.5 之前的默认存储引擎
  • 支持表锁
  • 不支持行锁
  • 不支持事务
  • 索- 引叶子节点只保存数据的地址而不是数据本身

七、explain

摘自

1、用途

  • 表的读取顺序如何
  • 数据读取操作有哪些操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间是如何引用
  • 每张- 表有多少行被优化器查询
    ……

    2、explain包含的字段

  • id //select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
  • select_type //查询类型
  • table //正在访问哪个表
  • partitions //匹配的分区
  • type //访问的类型
  • possible_keys //显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
  • key //实际使用到的索引，如果为NULL，则没有使用索引
  • key_len //表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
  • ref //显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
  • rows //根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
  • filtered //查询的表行占表的百分比
  • Extra //包含不适合在其它列中显示但十分重要的额外信息
    explain图

八、优化方向

1、正确的字段类型和长度
2、选择何时的存储引擎
3、推荐使用utf8mb4字符集，可以存表情，可以表示4个字节宽位，排序规则上general_ci 更快，unicode_ci 更准确。ci不区分大小写，cs区分大小写
4、少用多表操作（子查询，联合查询），而是将SQL拆分多次执行。如果查询很很小，会增加查询缓存的利用率。
5、慢sql查询处理
定位执行较慢的查询语句
show variables like “slow_query%”;
show variables like “%long_query%”;
开启日志
set global slow_query_log=1;
set long_query_time=0.5;
6、尽量不要出现null值 给默认值 （null不会走索引）
7、索引建立
8、使用explain检查sql的执行情况
9、开启表缓存 query_cache_type=On|1 ，查看show variables like ‘%query_cache%’;
10、主从配置
11、分库，分表
12、查看数据库链接池

九、主从同步延时问题

MySQL数据库主从同步延迟是怎么产生的。

主服务器何以并发, 但是从服务器的里面读取binlog 的线程仅有一个， 当某个SQL在从服务器上执行的时间稍长 或者由于某个SQL要进行锁表就会导致，主服务器的SQL大量积压，未被同步到从服务器里。这就导致了主从不一致， 也就是主从延迟。

如何查看是否存在延迟

1、可以通过监控show slave status\G命令输出的Seconds_Behind_Master参数的值来判断，是否有发生主从延时。：
NULL - 表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No,而非Yes.
0 - 该值为零，是我们极为渴望看到的情况，表示主从复制良好，可以认为lag不存在。
正值 - 表示主从已经出现延时，数字越大表示从库落后主库越多。
负值 - 几乎很少见，这是一个BUG值，该参数是不支持负值的，也就是不应该出现。

MySQL数据库主从同步延迟解决方案。

1、slave的sync_binlog设置为0或者关闭binlog：

  • sync_binlog 表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去，1最安全
    2、使用比主库更好的硬件设备作为slave。
    3、slave的innodb_flush_log_at_trx_commit 设置0：
  • 默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘
  • 设置2的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，只会在整个操作系统 挂了时才可能丢数据，一般不会丢失超过1-2秒的更新
  • 设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。
    4、–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志