mysql知识点整理

达文曦
80 阅读8分钟

笛卡尔积

A = {0,1} B = {2,3,4}

集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式：

A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}；

B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1）}；

注意:在表数据过大的时候，可以使用from table1 join table2 on ,避免使用where

索引

  1. 普通索引/唯一索引：

    1. 普通索引：允许重复和空值 INDEX(column)；
    2. 唯一索引：列值唯一，允许有空值，主键除外 UNIQUE INDEX UniqIdx(column)

  2. 单列索引/组合索引：

    1. 单列索引：一个索引只包含单个列，一个表可以有多个单列索引；
    2. 组合索引：表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会使用。INDEX MultiIdx(column1,column2)

  3. 全文索引/空间索引:只有MyISAM引擎可以使用

在查询数量较大时，可以用索引（在where条件中使用），增删改效率会降低

  1. 聚簇索引和非聚簇索引

    1. 聚簇索引（主键索引）：Innodb引擎使用的是聚簇索引，叶子节点存储的是行数据，并且是连续的，非叶子节点存储的是主键（没有主键则由mysql生成唯一RowId）。
    2. 二级索引：叶子节点存储的是主键，非叶子节点存储的是索引列值。
    3. 非聚簇索引：MyIsam引擎使用的是非聚簇索引，叶子节点存储的是数据行地址，也就是说根据索引查找到数据行的位置再去磁盘查找数据

  2. 覆盖索引

    1. Explain 分析sql时候，Extra显示use Index是代表使用了覆盖索引
    2. select查询语句时，查询字段都被二级索引覆盖，不需要再到主键索引进行回表查询时，代表覆盖索引。

  3. 设计原则：

1.索引并非越多越好，会占用磁盘空间，也会影响增删改的效率

2.避免对经常更新的表建索引。

3.数据量小的表不需要建索引

4.不同值较多的列上建索引，比如：男和女这种的就不建议建

存储引擎

  1. ISAM 读取效率快 内存占用低 不支持事务 不能容错，表级锁，支持全文索引
  2. MyISAM 不支持事务，表级别锁
  3. HEAP
  4. InnoDB

Innodb 给mysql提供了具有提交 回滚和崩溃恢复能力的事务安全的存储引擎,行级锁

删除重复数据

DELETE FROM class WHERE caption IN(SELECT caption FROM class GROUP BY caption HAVING COUNT(*)>1) AND

cid NOT IN(SELECT MIN(cid) FROM class GROUP BY caption HAVING COUNT(*)>1);

group by语句中select指定的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数中，常见的聚合函数如下表

avg(column) 平均数

count(column) 统计个数

min(column) 最小值

max(column) 最大值

sum(column) 求和

优化

索引的优化

  1. 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
  2. 尽量使用短索引，如果可以，应该制定一个前缀长度
  3. 对于经常在where子句使用的列，最好设置索引，这样会加快查找速度
  4. 对于有多个列where或者order by子句的，应该建立复合索引
  5. 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
  6. 尽量不要在列上进行运算（函数操作和表达式操作）
  7. 尽量不要使用not in和<>操作

sql查询语句的优化

  • 查询时，能不要就不用，尽量写全字段名
  • 大部分情况连接效率远大于子查询
  • 多使用explain和profile分析查询语句
  • 查看慢查询日志，找出执行时间长的sql语句优化
  • 多表连接时，尽量小表驱动大表，即小表 join 大表
  • 在千万级分页时使用limit
  • 对于经常使用的查询，可以开启缓存
  • 避免函数索引
  • 用In来代替or
  • like %在前面用不了索引
  • 批量INSERT插入

表的优化

  1. 表的字段尽可能用NOT NULL
  2. 字段长度固定的表查询会更快
  3. 把数据库的大表按时间或一些标志分成小表
  4. 将表分区

sql高并发优化

  • 使用缓存
  • 使用存储过程
  • 延迟修改，将多次请求保存到缓存里，定时将缓存同步到数据库里（容易造成数据丢失）
  • 使用索引配合where使用
  • 分表
  • 集群

表设计

  • 选择存储引擎
  • 结构设计
  • 主键
  • 数据类型长度
  • 合适的索引
  • 注释

b-tree和b+tree

blog.csdn.net/b_x_p/artic…

www.cnblogs.com/vianzhang/p…

一棵m阶的B-Tree有如下特性：

  1. 每个节点最多有m个孩子。

  2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。

  3. 若根节点不是叶子节点，则至少有2个孩子

  4. 所有叶子节点都在同一层，且不包含其它关键字信息

  5. 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）

  6. 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1

  7. ki(i=1,…n)为关键字，且关键字升序排序。

  8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

到这里，平衡二叉树解决了存在线性链表的问题，数据查询的效率好像也还可以，基本能达到O(log2(n))， 那为什么mysql不选择这样的数据结构呢，他又存在什么样的问题呢？

  • 问题1： 搜索效率不足，一般来说，在树结构中，数据所处的深度，决定了搜索时的IO次数。如上图中搜索id = 8的数据，需要进行3次IO。当数据量到达几百万的时候，树的高度就会很恐怖。
  • 问题2： 查询不不稳定，如果查询的数据落在根节点，只需要一次IO，如果是叶子节点或者是支节点，会需要多次IO才可以。
  • 问题3： 节点存储的数据内容太少。没有很好利用操作系统和磁盘数据交换特性，也没有利用好磁盘IO的预读能力。因为操作系统和磁盘之间一次数据交换是已页为单位的，一页 = 4K，即每次IO操作系统会将4K数据加载进内存。但是，在二叉树每个节点的结构只保存一个关键字，一个数据区，两个子节点的引用，并不能够填满4K的内容。幸幸苦苦做了一次的IO操作，却只加载了一个关键字，在树的高度很高，恰好又搜索的关键字位于叶子节点或者支节点的时候，取一个关键字要做很多次的IO。

Innodb行锁

  1. innodb存储引擎默认是加的行锁

  2. update,insert,delete都会加锁，未使用到索引，会变成表锁

  3. 共享锁和排它锁

    1. 共享锁S: select... Lock in share model,对同一行的操作，读不阻塞，写阻塞；update锁住行以后，共享锁查询会一直等待update释放后再查询（当前读），select不会，走的快照读（MVCC）
    2. 排他锁X: select... for update 排他锁，对同一行的操作，读写都阻塞(select不会阻塞，快照读)
    3. 间隙锁：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；当前读的情况下,为了避免幻读,产生间隙锁。

事务、隔离级别

mysql事务：zhuanlan.zhihu.com/p/76743929

  1. 原子性:事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做。undo log实现
  2. 一致性:事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
  3. 隔离性:一个事务的执行不能其它事务干扰。
  4. 永久性:也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。redo log实现

隔离级别

  1. 未提交读:所有事务都可以看到其他未提交事务的执行结果。脏读
  2. 已提交读:一个事务只能看见已经提交事务所做的改变。无法避免不可重复读
  3. 可重复读:它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）{同一事物,多次查询,得到的行数不一致}。InnoDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。
  4. 串行化读:这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

MVVC多版本并发控制

MVCC是在并发访问数据库时，通过对数据做多版本管理，避免因为写锁的阻塞而造成读数据的并发阻塞问题。

  1. MVCC只在读取已提交（Read Committed） 和可重复读（Repeatable Read） 两个事务级别下有效。其是通过Undo日志中的版本链和ReadView一致性视图来实现的。
  2. 在MySQL中，会默认为我们的表后面添加三个隐藏字段
  • DB_TRX_ID: 事务ID，记录的是当前事务在做INSERT或UPDATE语句操作时的事务ID；
  • DB_ROLL_PTR： 指向上一个版本数据在undo log 里的位置指针，回滚指针，通过它可以将不同的版本串联起来，形成版本链。
  • DB_ROW_ID: 隐藏ID ，当创建表没有合适的索引作为聚集索引时，会用该隐藏ID创建聚集索引;
  1. ReadView 读视图：已提交读：开启事务，每次select都会重新生成一份readView；可重复读：开启事务，只会在第一次select生成readView，其他select沿用第一次的readView。
  • trx_ids: 当前系统活跃(未提交)事务版本号集合。
  • up_limit_id:记录 trx_list 列表中事务 ID 最小的 ID
  • low_limit_id: Read View 生成时刻系统尚未分配的下一个事务 ID

ReadView匹配条件

  1. 首先比较 DB_TRX_ID < up_limit_id,如果小于，则当前事务能看到 DB_TRX_ID 所在的记录，如果大于等于进入下一个判断
  2. 接下来判断 DB_TRX_ID >= low_limit_id,如果大于等于则代表 DB_TRX_ID 所在的记录在 Read View 生成后才出现的，那么对于当前事务肯定不可见，如果小于，则进入下一步判断
  3. 判断 DB_TRX_ID 是否在活跃事务中，如果在，则代表在 Read View 生成时刻，这个事务还是活跃状态，还没有 commit，无法查到数据，则说明这个事务在 Read View 生成之前就已经开始 commit，可以被查到
  4. 不满足readView条件的，会再到undo log里根据版本链查询历史数据的DB_TRX_ID，再进行条件判断，直到满足。

MVCC多版本并发控制 - Li&Fan - 博客园

数据库MVCC多版本并发控制原理_Eric的博客-CSDN博客_mvcc多版本并发控制

分库分表方案

其他

mysql分页数据量大解决方案

1.利用覆盖索引，select id from tb limit 10000,10,id为主键，利用聚簇索引查询，效率更高

2.查询字段多，无法覆盖索引，可以使用join，select *

avatar
文章
阅读
粉丝