mysql笔记

爱学习的Chloe
326 阅读17分钟

数据库类型

int(10)：10表示显示宽度，当保存的数字小于10位且配置了zerofill的话会自动补齐到10位，超过10位就直接保存。所以int实际可存储长度都是4个字节，-2^31~2^31，与括号中数字没有关系。
char（10）：固定存储10个字符，注意是字符不是字节，不足10个字符用空格补齐，默认会去掉末尾的空格
varchar(512): 可变长度字符串，最大可以存储512个字符，括号中数字表示最大可存储字符数，原样保存数据
blob： 保存大数据，如图片、文章，与text区别是，blob存储的是二进制数据，所以可以直接保存图片数据
text： 保存大数据，如博客，文章，保存的是字符数据，不能直接保存图片数据，所以一般博客里的图片是保存url

innodb与myisam的区别

是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
是否支持外键： MyISAM不支持，而InnoDB支持。
是否支持MVCC：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一

聚簇索引

innodb引擎用的是聚簇索引，myisam引擎用的是非聚簇索引；
innodb将数据（整行记录）和索引（主键索引）都存放在一个文件中，找到索引也就找到了数据，这种叫聚簇索引；
innodb非主键索引则指向对主键的引用。
myisam将数据和索引是单独存放两个文件的，table.myi记录索引，table.myd记录数据，所以查数据是先去table.myi找到table.myi对应的数据的行位置，这种的叫非聚簇索引。
从上面可以看出，如果用到了主键索引那聚簇索引会快很多！

锁和隔离级别

事务特性

  • 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  • 一致性（Consistency）： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
  • 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  • 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

事务并发带来的问题

  • 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
  • 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
  • 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
  • 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

如何解决并发问题？

mysql为了解决上述并发问题，引入了锁机制和MVCC机制，在不同的隔离级别下实现略有不同。 先看下有哪些隔离级别：

  • READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  • REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

InnoDB存储引擎的锁的算法有三种：

Record lock：单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record+gap 锁定一个范围，包含记录本身，为了解决幻读问题

innodb在更新或select..for update默认加的是Next-key lock,注意innodb锁的都是索引而非记录，以下有几种特殊情况：

  • 如果没有走索引，那mysql就会先锁全表然后释放不符合条件的行的锁，浪费性能
  • 当查询的索引含有唯一属性时，将next-key lock降级为record key

为什么select语句不需要加锁？

select是不需要加锁的，在查询时如果该行已经被加上排他锁（说明有其他事务在更新），那就会采用快照读（MVCC实现）。MVCC只在RC和RR隔离级别下生效，因为RU是直接读取最新版本号的不需要排除未提交的，串行化是直接通过锁控制。MVCC是通过在表中额外增加三列（版本号）实现。

在innodb的RC或RR模式下，快照读读不到事务未提交的数据。具体是通过Readview实现。术语是readview，用白话说就是，记录了未提交事务的表，事务会依据该表选择合适的行版本号进行快照读操作，具体选择版本号的方法是挑选除readview表的版本号外的最新版本号。但需要注意的是，RC级别下readview表会随着其他未提交事务的提交而发生变动，但RR不会。当隔离级别为RC，事务一开始便生成readview表，而若是事务执行期间其他事务发生提交操作，此时readview也会相应地改变，之后的快照读会依照改变后的readview表读取最新提交版本的数据，所以RC下会有不可重复读问题。当隔离级别为RR，事务执行一条语句时才生成readview，且之后不会再变动，就克服了不可重复读的问题。

简单总结：
RC和RR都是读取提交事务中的最新版本号的数据（除readview外），但是RC会在事务中的每个相同的select都去更新readview，假设两个相同select之间有其他事务提交，这样就会造成不可重复读；而RR情况下，readview是在第一个select生成后就不会变了的，所以即使后面有其他事务提交了，但readview没更新，这样提交的那条数据版本号与readview表中的重复，就会在选择中排除掉，从而避免不可重复读。

索引

innodb索引实现

B+tree,是一种多叉查找平衡树，算法复杂度logn，数据都存在叶子节点中。
B+树与B树区别：

  • b+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”；
  • b+树查询必须查找到叶子节点，b树只要匹配到即可不用管元素位置，因此b+树查找更稳定（并不慢）；
  • 对于范围查找来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历

最左前缀原则

如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引；
如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；

  • mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。
    比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  • =和in可以乱序。
    比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

前缀索引

当要索引的列字符很多时 索引则会很大且变慢，mysql可以只索引列开始的部分字符串 节约索引空间 从而提高索引效率。

ALTER TABLE table_name ADD index(column_name(prefix_length));

但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

覆盖索引

覆盖索引：就是包含了所有查询字段 (where,select,ordery by,group by 包含的字段) 的索引
我们知道利用普通索引的查询过程是：
1、走一遍普通索引找到对应的主键id，因为普通索引中叶子节点的data属性存的是主键id的引用； 2、拿第一步得到的主键id再去主键索引里找到对应的数据记录（主键索引的叶子节点存的是整行记录）
这样会走两边索引，如果记录少的话还不如扫全表，那有什么办法走一遍索引就找到要的数据，而不用回表查询？
答案是覆盖索引，覆盖索引就是只需要走一遍索引就可以查出所需数据，其实就是select（为了简化理解先不考虑ordery by,group by）的字段都建立了联合索引，这样走第一遍索引的时候索引里面就存有这些列的数据了，不用再回表查了，举例：

select id,name from user where name='yangcong';

假设name建立了索引，这样就是索引覆盖了

select id,name,sex from user where name='yangcong';

这样就不行了。这种情况如果把(name)单列索引升级为联合索引(name, sex)就可以了。

水平分库分表

实现请看shadringJDBC

怎么保证全局id唯一呢？

  • redis
    redis作为全局id生成器，通过incrby key 1000，每次分配1000个id给一个表，这样redis压力小；
  • snowflake算法
    snowflake的结构如下(每部分用-分开):

0 - 0000000000 0000000000 0000000000 0000000000 0 - 00000 - 00000 - 000000000000

第一位为未使用，接下来的41位为毫秒级时间(41位的长度可以使用69年)，然后是5位datacenterId和5位workerId(10位的长度最多支持部署1024个节点） ，最后12位是毫秒内的计数（12位的计数顺序号支持每个节点每毫秒产生4096个ID序号）

一共加起来刚好64位，为一个Long型。(转换成字符串后长度最多19)

snowflake生成的ID整体上按照时间自增排序，并且整个分布式系统内不会产生ID碰撞（由datacenter和workerId作区分），并且效率较高。经测试snowflake每秒能够产生26万个ID。

通过多个维度查询呢？

一般分表是根据某个维度（比如用户id、订单号等）按照一定规则（hash或range）拆分，那其他维度比如商家怎么查呢？
答：一般是会根据商家id再分一次表，多存一份数据
那这样有几个维度查询就存几份数据，这样数据岂不是太多了？
答：还可以建一张维度索引表或者叫维度路由表，每次插入数据的时候路由表同步更新，路由表也是分表的，但只记录几个维度的id，相对来讲列数少很多。

分库后怎么解决分布式事务问题？

juejin.cn/post/684490…

  • 2PC
  • TCC
  • MQ

跨库join怎么解决？

在应用层面解决，分多次join多表，然后在应用层面合并结果

怎么取出最新100条？怎么分页查询？

1.每个库取前100条，然后在服务层排序最终得出全局最新的100条，分页也是一样的 2.分页只允许下一页，不支持指定页，这样可以利用上次页面的最后一个值作为下次查询条件

多数据源怎么管理？

shardingJDBC，先通过分片键路由到库，然后去连接对应的库，根据策略选择単连接还是多连接，查出来之后再进行结果集合并。

sql优化

explain

explain结果中个字段的解释：www.cnblogs.com/butterfly10…

索引失效的场景

  1. like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

  2. or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。

  3. 组合索引，不是使用第一列索引，即不符合最左前缀原则，索引失效。

  4. 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

  5. 在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。

  6. 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

  7. 对索引字段进行计算操作或使用函数。

  8. 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

数据量大时的怎么解决分页查询慢的问题？

  1. 子查询
    select * from orders_history where type=8 and id>=(select id from order_history where type =8 limit 10000,1) limit 100;
    通过select id可以提高一点查询速度，但是提高的不是很理想；
  2. id连续
    如果id连续的话，可以直接用 where id>10000 limit 100; 但实际上很难保证不删除数据
  3. 限制查询页数
    业务上做限制，不能查大页数的，比如限制最多只能跳到100页，后面的只能下一页，因为下一页的时候可以利用上一页的缓存数据进行直接查询。这样就快很多。

join的原理

  1. Index Nested-Loop Join（索引嵌套）
    这个要求非驱动表（匹配表s）上有索引，可以通过索引来减少比较，加速查询。 在查询时，驱动表（r）会根据关联字段的索引进行查找，挡在索引上找到符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表查询。 如果非驱动表（s）的关联健是主键的话，性能会非常高，如果不是主键，要进行多次回表查询，先关联索引，然后根据二级索引的主键ID进行回表操作，性能上比索引是主键要慢。

  2. Block Nested-Loop Join
    如果有索引，会选取第一种方式进行join，但如果join列没有索引，就会采用Block Nested-Loop Join。可以看到中间有个join buffer缓冲区，是将驱动表的所有join相关的列都先缓存到join buffer中，然后批量与匹配表进行匹配，将第一种多次比较合并为一次，降低了非驱动表（s）的访问频率。默认情况下join_buffer_size=256K，在查找的时候MySQL会将所有的需要的列缓存到join buffer当中，包括select的列，而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。

www.jianshu.com/p/16ad9669d…

mysql索引为什么用B+树？而不用红黑树或者B树？

  1. 首先红黑树和B树的时间复杂度是一样的都是O（logN）,但是相比于红黑树，B+树是多路搜索树，树的高度更低，每个节点存储的数据更多。正是因为这一点，B+树可以充分利用磁盘的预读功能，每次读出一整个节点，然后在内存中查找。而红黑树每个节点就存有一个数据（2路），每次预读出来的多个节点并没有什么逻辑关系（不是父子），只是物理存储上面的相邻。
  2. B+树是为磁盘索引而生。索引是存在于索引文件中，是存在于磁盘中的。因为索引通常是很大的，因此无法一次将全部索引加载到内存当中，因此每次只能从磁盘中读取一个磁盘页的数据到内存中。B+树一个节点的大小大概就是一个磁盘页的大小。
  3. B+树相比于B树的优势：
    B+树的关键字全部存放在叶子节点中，非叶子节点用来做索引，而叶子节点中有一个指针指向一下个叶子节点，所有的叶子节点组成一个有序链表。
    这样在范围查询，或者遍历的时候B+树直接遍历叶子节点即可，效率提升很大。

数据库索引采用B+树的主要原因是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。 B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

www.cnblogs.com/aspirant/p/… www.sohu.com/a/280609547…

binlog存储的三种模式

www.cnblogs.com/bill2014/p/…

一条sql语句的执行过程

github.com/whysoseriou…

avatar
文章
阅读
粉丝