找到
7
篇与
MySQL
相关的结果
-
再有人问你什么是分库分表，直接把这篇文章发给他 分库分表，是企业里面毕竟常见的针对高并发、数据量大的场景下的一种技术优化方案，也是一个非常高频的面试题。但是，因为很多人其实并没有非常丰富的分库分表的经验，所以能把这个问题回答的比较好的人其实还挺少的。 那么，本文就来试图把关于分库分表的事情，一次性的讲个清楚。 分库、分表、分库分表 首先，我们需要知道所谓”分库分表”，根本就不是一件事儿，而是三件事儿，他们要解决的问题也都不一样。 这三个事儿分别是”只分库不分表”、”只分表不分库”、以及”既分库又分表”。 什么时候分库？ 其实，分库主要解决的是并发量大的问题。因为并发量一旦上来了，那么数据库就可能会成为瓶颈，因为数据库的连接数是有限的，虽然可以调整，但是也不是无限调整的。 所以，当当你的数据库的读或者写的QPS过高，导致你的数据库连接数不足了的时候，就需要考虑分库了，通过增加数据库实例的方式来提供更多的可用数据库链接，从而提升系统的并发度。 比较典型的分库的场景就是我们在做微服务拆分的时候，就会按照业务边界，把各个业务的数据从一个单一的数据库中拆分开，分表把订单、物流、商品、会员等单独放到单独的数据库中。 还有就是有的时候可能会需要把历史订单挪到历史库里面去。这也是分库的一种具体做法。 什么时候分表？ 分库主要解决的是并发量大的问题，那分表其实主要解决的是数据量大的问题。 假如你的单表数据量非常大，因为并发不高，数据量连接可能还够，但是存储和查询的性能遇到了瓶颈了，你做了很多优化之后还是无法提升效率的时候，就需要考虑做分表了。 通过将数据拆分到多张表中，来减少单表的数据量，从而提升查询速度。 一般我们认为，单表行数超过 500 万行或者单表容量超过 2GB之后，才需要考虑做分库分表了，小于这个数据量，遇到性能问题先建议大家通过其他优化来解决。 什么时候既分库又分表？ 那么什么时候分库又分表呢，那就是既需要解决并发量大的问题，又需要解决数据量大的问题时候。通常情况下，高并发和数据量大的问题都是同时发生的，所以，我们会经常遇到分库分表需要同时进行的情况。 所以，当你的数据库链接也不够了，并且单表数据量也很大导致查询比较慢的时候，就需要做既分库又分表了。 横向拆分和纵向拆分 谈及到分库分表，那就要涉及到该如何做拆分的问题。 通常在做拆分的时候有两种分法，分别是横向拆分（水平拆分）和纵向拆分(垂直拆分)。假如我们有一张表，如果把这张表中某一条记录的多个字段，拆分到多张表中，这种就是纵向拆分。那如果把一张表中的不同的记录分别放到不同的表中，这种就是横向拆分。 横向拆分的结果是数据库表中的数据会分散到多张分表中，使得每一个单表中的数据的条数都有所下降。比如我们可以把不同的用户的订单分表拆分放到不同的表中。 纵向拆分的结果是数据库表中的数据的字段数会变少，使得每一个单表中的数据的存储有所下降。比如我可以把商品详情信息、价格信息、库存信息等等分别拆分到不同的表中。 还有我们谈到的针对不同的业务做拆分成多个数据库的这种情况，其实也是纵向拆分的一种。 分表字段的选择 在分库分表的过程中，我们需要有一个字段用来进行分表，比如按照用户分表、按照时间分表、按照地区分表。这里面的用户、时间、地区就是所谓的分表字段。 那么，在选择这个分表字段的时候，一定要注意，要根据实际的业务情况来做慎重的选择。 比如说我们要对交易订单进行分表的时候，我们可以选择的信息有很多，比如买家Id、卖家Id、订单号、时间、地区等等，具体应该如何选择呢？ 通常，如果有特殊的诉求，比如按照月度汇总、地区汇总等以外，我们通常建议大家按照买家Id进行分表。因为这样可以避免一个关键的问题那就是——数据倾斜（热点数据）。 买家还是卖家 首先，我们先说为什么不按照卖家分表？ 因为我们知道，电商网站上面是有很多买家和卖家的，但是，一个大的卖家可能会产生很多订单，比如像苏宁易购、当当等这种店铺，他每天在天猫产生的订单量就非常的大。如果按照卖家Id分表的话，那同一个卖家的很多订单都会分到同一张表。 那就会使得有一些表的数据量非常的大，但是有些表的数据量又很小，这就是发生了数据倾斜。这个卖家的数据就变成了热点数据，随着时间的增长，就会使得这个卖家的所有操作都变得异常缓慢。 但是，买家ID做分表字段就不会出现这类问题，因为一个不太容易出现一个买家能把数据买倾斜了。 但是需要注意的是，我们说按照买家Id做分表，保证的是同一个买家的所有订单都在同一张表 ，并不是要给每个买家都单独分配一张表。 我们在做分表路由的时候，是可以设定一定的规则的，比如我们想要分1024张表，那么我们可以用买家ID或者买家ID的hashcode对1024取模，结果是0000-1023，那么就存储到对应的编号的分表中就行了。 卖家查询怎么办 如果按照买家Id进行了分表，那卖家的查询怎么办，这不就意味着要跨表查询了吗？ 首先，业务问题我们要建立在业务背景下讨论。电商网站订单查询有几种场景？ 1、买家查自己的订单 2、卖家查自己的订单 3、平台的小二查用户的订单。 首先，我们用买家ID做了分表，那么买家来查询的时候，是一定可以把买家ID带过来的，我们直接去对应的表里面查询就行了。 那如果是卖家查呢？卖家查询的话，同样可以带卖家id过来，那么，我们可以有一个基于binlog、flink等准实时的同步一张卖家维度的分表，这张表只用来查询，来解决卖家查询的问题。 本质上就是用空间换时间的做法。 不知道大家看到这里会不会有这样的疑问：同步一张卖家表，这不又带来了大卖家的热点问题了吗？ 首先，我们说同步一张卖家维度的表来，但是其实所有的写操作还是要写到买家表的，只不过需要准实时同步的方案同步到卖家表中。也就是说，我们的这个卖家表理论上是没有业务的写操作，只有读操作的。 所以，这个卖家库只需要有高性能的读就行了，那这样的话就可以有很多选择了，比如可以部署到一些配置不用那么高的机器、或者其实可以干脆就不用MYSQL，而是采用HBASE、PolarDB、Lindorm等数据库就可以了。这些数据库都是可以海量数据，并提供高性能查询的。 还有呢就是，大卖家一般都是可以识别的，提前针对大卖家，把他的订单，再按照一定的规则拆分到多张表中。因为只有读，没有写操作，所以拆分多张表也不用考虑事务的问题。 按照订单查询怎么办 上面说的都是有买卖家ID的情况，那没有买卖家ID呢？用订单号直接查怎么办呢？ 这种问题的解决方案是，在生成订单号的时候，我们一般会把分表解决编码到订单号中去，因为订单生成的时候是一定可以知道买家ID的，那么我们就把买家ID的路由结果比如1023，作为一段固定的值放到订单号中就行了。这就是所谓的“基因法” 这样按照订单号查询的时候，解析出这段数字，直接去对应分表查询就好了。 至于还有人问其他的查询，没有买卖家ID，也没订单号的，那其实就属于是低频查询或者非核心功能查询了，那就可以用ES等搜索引擎的方案来解决了。就不赘述了。 分表算法 选定了分表字段之后，如何基于这个分表字段来准确的把数据分表到某一张表中呢？ 这就是分表算法要做的事情了，但是不管什么算法，我们都需要确保一个前提，那就是同一个分表字段，经过这个算法处理后，得到的结果一定是一致的，不可变的。 通常情况下，当我们对order表进行分表的时候，比如我们要分成128张表的话，那么得到的128表应该是:order_0000、order_0001、order_0002…..order_0126、order_0127 通常的分表算法有以下几种： 直接取模 在分库分表时，我们是事先可以知道要分成多少个库和多少张表的，所以，比较简单的就是取模的方式。 比如我们要分成128张表的话，就用一个整数来对128取模就行了，得到的结果如果是0002，那么就把数据放到order_0002这张表中。 Hash取模 那如果分表字段不是数字类型，而是字符串类型怎么办呢？有一个办法就是哈希取模，就是先对这个分表字段取Hash，然后在再取模。 但是需要注意的是，Java中的hash方法得到的结果有可能是负数，需要考虑这种负数的情况。 一致性Hash 前面两种取模方式都比较不错，可以使我们的数据比较均匀的分布到多张分表中。但是还是存在一个缺点。 那就是如果需要扩容二次分表，表的总数量发生变化时，就需要重新计算hash值，就需要涉及到数据迁移了。 为了解决扩容的问题，我们可以采用一致性哈希的方式来做分表。 一致性哈希可以按照常用的hash算法来将对应的key哈希到一个具有2^32次方个节点的空间中，形成成一个顺时针首尾相接的闭合的环形。所以当添加一台新的数据库服务器时，只有增加服务器的位置和逆时针方向第一台服务器之间的键会受影响。 全局ID的生成 涉及到分库分表，就会引申出分布式系统中唯一主键ID的生成问题，因为在单表中我们可以用数据库主键来做唯一ID，但是如果做了分库分表，多张单表中的自增主键就一定会发生冲突。那就不具备全局唯一性了。 那么，如何生成一个全局唯一的ID呢？有以下几种方式： UUID 很多人对UUID都不陌生，它是可以做到全局唯一的，而且生成方式也简单，但是我们通常不推荐使用他做唯一ID，首先UUID太长了，其次字符串的查询效率也比较慢，而且没有业务含义，根本看不懂。 基于某个单表做自增主键 多张单表生成的自增主键会冲突，但是如果所有的表中的主键都从同一张表生成是不是就可以了。 所有的表在需要主键的时候，都到这张表中获取一个自增的ID。 这样做是可以做到唯一，也能实现自增，但是问题是这个单表就变成整个系统的瓶颈，而且也存在单点问题，一旦他挂了，那整个数据库就都无法写入了。 基于多个单表+步长做自增主键 为了解决单个数据库做自曾主键的瓶颈及单点故障问题，我们可以引入多个表来一起生成就行了。 但是如何保证多张表里面生成的Id不重复呢？如果我们能实现以下的生成方式就行了： 实例1生成的ID从1000开始，到1999结束。 实例2生成的ID从2000开始，到2999结束。 实例3生成的ID从3000开始，到3999结束。 实例4生成的ID从4000开始，到4999结束。 这样就能避免ID重复了，那如果第一个实例的ID已经用到1999了怎么办？那就生成一个新的起始值： 实例1生成的ID从5000开始，到5999结束。 实例2生成的ID从6000开始，到6999结束。 实例3生成的ID从7000开始，到7999结束。 实例4生成的ID从8000开始，到8999结束。 我们把步长设置为1000，确保每一个单表中的主键起始值都不一样，并且比当前的最大值相差1000就行了。 雪花算法 雪花算法也是比较常用的一种分布式ID的生成方式，它具有全局唯一、递增、高可用的特点。 雪花算法生成的主键主要由 4 部分组成，1bit符号位、41bit时间戳位、10bit工作进程位以及 12bit 序列号位。 时间戳占用41bit，精确到毫秒，总共可以容纳约69年的时间。 工作进程位占用10bit，其中高位5bit是数据中心ID，低位5bit是工作节点ID，做多可以容纳1024个节点。 序列号占用12bit，每个节点每毫秒0开始不断累加，最多可以累加到4095，一共可以产生4096个ID。 所以，一个雪花算法可以在同一毫秒内最多可以生成1024 X 4096 = 4194304个唯一的ID 分库分表的工具 在选定了分表字段和分表算法之后，那么，如何把这些功能给实现出来，需要怎么做呢？ 我们如何可以做到像处理单表一样处理分库分表的数据呢？这就需要用到一个分库分表的工具了。 目前市面上比较不错的分库分表的开源框架主要有三个，分别是sharding-jdbc、TDDL和Mycat Sharding-JDBC 现在叫ShardingSphere（Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar这3款相互独立的产品组成）。它定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。 开原地址：https://shardingsphere.apache.org TDDL TDDL 是淘宝开源的一个用于访问数据库的中间件， 它集成了分库分表， 读写分离，权重调配，动态数据源配置等功能。封装 jdbc 的 DataSource给用户提供统一的基于客户端的使用。 开源地址：https://github.com/alibaba/tb_tddl Mycat Mycat是一款分布式关系型数据库中间件。它支持分布式SQL查询，兼容MySQL通信协议，以Java生态支持多种后端数据库，通过数据分片提高数据查询处理能力。 开源地址：https://github.com/MyCATApache/Mycat2 分库分表带来的问题 分库分表之后，会带来很多问题。 首先，做了分库分表之后，所有的读和写操作，都需要带着分表字段，这样才能知道具体去哪个库、哪张表中去查询数据。如果不带的话，就得支持全表扫描。 但是，单表的时候全表扫描比较容易，但是做了分库分表之后，就没办法做扫表的操作了，如果要扫表的话就要把所有的物理表都要扫一遍。 还有，一旦我们要从多个数据库中查询或者写入数据，就有很多事情都不能做了，比如跨库事务就是不支持的。 所以，分库分表之后就会带来因为不支持事务而导致的数据一致性的问题。 其次，做了分库分表之后，以前单表中很方便的分页查询、排序等等操作就都失效了。因为我们不能跨多表进行分页、排序。 总之，分库分表虽然能解决一些大数据量、高并发的问题，但是同时也会带来一些新的问题。所以，在做数据库优化的时候，还是建议大家优先选择其他的优化方式，最后再考虑分库分表。 总结 以上，本文介绍了分库分表的一些原因，以及如何做分库分表，并且讨论了其中比较关键的分表字段和分表算法的问题。还介绍了几款比较不错的分库分表的相关框架。 最后，还有一些需要大家注意的就是分库分表会引入一些新的问题，这些问题的解决成本也都不低，所以在做技术选型的时候也要做好这方面的评估。
-
Innodb的RR到底有没有解决幻读？ 关于Innodb中的REPEATABLE READ这种隔离级别到底有没有解决幻读？好像众说纷纭，大家的说法都不一致。 有的人说，RR当然没解决幻读了，因为只有Serializable才能解决幻读。 也有人说，RR解决了幻读，因为RR中加了间隙锁，就能解决幻读的问题。 还有人说，只有间隙锁是没用的，还有MVCC也帮助RR解决了幻读的问题。 那到底真实情况是怎么样的呢？ 我认为，InnoDB中的REPEATABLE READ这种隔离级别通过间隙锁+MVCC解决了大部分的幻读问题，只有一种特殊的幻读情况无法解决。 为什么这么说呢？这种特殊情况是怎么回事儿呢？本文就来把这个问题讲清楚。(本文中所有SQL的运行环境是MySQL 5.7.9 及MySQL 8.0.30） 什么是幻读 在介绍如何解决幻读之前，有必要再明确一下什么是幻读，确保大家理解是一致的。 幻读就是事务在做范围查询（SELECT)的过程中，有另外一个事务对范围内新增了记录(INSERT)，导致范围查询的结果条数不一致的现象。 有这样一张表： CREATE TABLE users ( id INT UNSIGNED AUTO_INCREMENT, gmt_create DATETIME NOT NULL, age INT NOT NULL, name VARCHAR(16) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB; INSERT INTO users(gmt_create,age,name) values(now(),18,'Hollis'); INSERT INTO users(gmt_create,age,name) values(now(),28,'HollisChuang'); INSERT INTO users(gmt_create,age,name) values(now(),38,'Hollis666'); 接着我们进行如下操作： 事务1 事务2 set session tx_isolation=’READ-COMMITTED’; BEGIN; SELECT * FROM users WHERE AGE > 10 AND AGE < 30; BEGIN; INSERT INTO users(gmt_create,age,name) values(now(),20,’Hollis999′); COMMIT; SELECT * FROM users WHERE AGE > 10 AND AGE < 30; 在这个例子中，在事务1中执行了两次相同的查询操作。但是两次操作中间事务2向数据库中增加了一条符合事务1的查询条件的数据，最终事务1的两次查询得到的结果是不一样的，这种现象就是幻读。 MVCC解决幻读 MVCC，是Multiversion Concurrency Control的缩写，翻译过来是多版本并发控制，和数据库锁一样，他也是一种并发控制的解决方案。它主要用来解决读-写并发的情况。关于MVCC的原理可以参考《再有人问你什么是MVCC，就把这篇文章发给他！》 我们知道，在MVCC中有两种读，一种是快照读、一种是当前读。 所谓快照读，就是读取的是快照数据，即快照生成的那一刻的数据，像我们常用的普通的SELECT语句在不加锁情况下就是快照读。 SELECT * FROM xx_table WHERE ... 在 RC 中，每次读取都会重新生成一个快照，总是读取行的最新版本。 在 RR 中，快照会在事务中第一次SELECT语句执行时生成，只有在本事务中对数据进行更改才会更新快照。 那么也就是说，如果在RR下，一个事务中的多次查询，是不会查询到其他的事务中的变更内容的，所以，也就是可以解决幻读的。 所以，针对上面的例子，如果我们把事务隔离级别设置为RR，那么因为有了MVCC的机制，就能解决幻读的问题： 事务1 事务2 set session tx_isolation=’REPEATABLE-READ’; BEGIN; SELECT * FROM users WHERE AGE > 10 AND AGE < 30; BEGIN; INSERT INTO users(gmt_create,age,name) values(now(),20,’Hollis999′); COMMIT; SELECT * FROM users WHERE AGE > 10 AND AGE < 30; 可以看到，同一个事务中的两次查询结果是一样的，就是在RR级别下，因为有快照读，所以第二次查询其实读取的是一个快照数据。 间隙锁与幻读 上面我们讲过了MVCC能解决RR级别下面的快照读的幻读问题，那么当前读下面的幻读问题怎么解决呢？ 当前读就是读取最新数据，所以，加锁的 SELECT，或者对数据进行增删改都会进行当前读，比如： SELECT * FROM xx_table LOCK IN SHARE MODE; SELECT * FROM xx_table FOR UPDATE; INSERT INTO xx_table ... DELETE FROM xx_table ... UPDATE xx_table ... 举一个下面的例子： 事务1 事务2 set session tx_isolation=’REPEATABLE-READ’; BEGIN; SELECT * FROM users WHERE AGE > 10 AND AGE < 30 FOR UPDATE BEGIN; INSERT INTO users(gmt_create,age,name) values(now(),20,’Hollis999′); 阻塞！！！ 向上面这种情况，在RR的级别下，当我们使用SELECT … FOR UPDATE的时候，会进行加锁，不仅仅会对行记录进行加锁，还会对记录之间的间隙进行加锁，这就叫做间隙锁（参考：数据库的锁，到底锁的是什么？）。因为记录之间的间隙被锁住了，所以事务2的插入操作就被阻塞了，一直到事务1把锁释放掉他才能执行成功。 因为事务2无法插入数据成功，所以也就不会存在幻读的现象了。所以，在RR级别中，通过加入间隙锁的方式，就避免了幻读现象的发生。 解决不了的幻读 前面我们介绍了快照读（无锁查询）和当前读（有锁查询）下是如何解决幻读的问题的，但是，上面的例子就是幻读的所有情况了吗？显然并不是。 我们说MVCC只能解决快照读的幻读，那如果在一个事务中发生了当前读，并且在另一个事务插入数据前没来得及加间隙锁的话，会发生什么呢？ 那么，我们稍加修改一下上面的SQL代码，通过当前读的方式进行查询数据： 事务1 事务2 set session tx_isolation=’REPEATABLE-READ’; BEGIN; SELECT * FROM users WHERE AGE > 10 AND AGE < 30 BEGIN; INSERT INTO users(gmt_create,age,name) values(now(),20,’Hollis999′); COMMIT; SELECT * FROM users WHERE AGE > 10 AND AGE < 30; SELECT * FROM users WHERE AGE > 10 AND AGE < 30 FOR UPDATE; 在上面的例子中，在事务1中，我们并没有在事务开启后立即加锁，而是进行了一次普通的查询，然后事务2插入数据成功之后，再通过事务1进行了2次查询。 我们发现，事务1后面的两次查询结果完全不一样，没加锁的情况下，就是快照读，读到的数据就和第一次查询是一样的，就不会发生幻读。但是第二次查询加了锁，就是当前读，那么读取到的数据就有其他事务提交的数据了，就发生了幻读。 那么，如果你理解了上面的这个例子，并且你也理解了当前读的概念，那么你很容易就能想到，下面的这个CASE其实也是会发生幻读的： 事务1 事务2 set session tx_isolation=’REPEATABLE-READ’; BEGIN; SELECT * FROM users WHERE AGE > 10 AND AGE < 30 BEGIN; INSERT INTO users(gmt_create,age,name) values(now(),20,’Hollis999′); COMMIT; SELECT * FROM users WHERE AGE > 10 AND AGE < 30; UPDATE users set name =”Hollis888″ where AGE = 20; SELECT * FROM users WHERE AGE > 10 AND AGE < 30; 这里发生幻读的原理，和上面的例子其实是一样的，那就是MVCC只能解决快照读中的幻读问题，而对于当前读（SELECT FOR UPDATE、UPDATE、DELETE等操作）还是会产生幻读的现象的。 UPDATE语句也是一种当前读，所以它是可以读到其他事务的提交结果的。 为什么事务1的最后一次查询和倒数第二次查询的结果也不一样呢？ 是因为根据快照读的定义，在RR中，如果本事务中发生了数据的修改，那么就会更新快照，那么最后一次查询的结果也就发生了变化。 如何避免幻读 那么了解了幻读的解决场景，以及不能解决的几个CASE之后，我们来总结一下该如何解决幻读的问题呢？ 首先，如果想要彻底解决幻读的问题，在InnoDB中只能使用Serializable这种隔离级别。 图源：MySQL 8.0 Reference Manual 那么，如果想在一定程度上解决或者避免发生幻读的话，使用RR也可以，但是RC、RU肯定是不行的。 在RR级别中，能使用快照读（无锁查询）的就使用快照读，这样不仅可以减少锁冲突，提升并发度，而且还能避免幻读的发生。 那么，如果在并发场景中，一定要加锁的话怎么办呢？那就一定要在事务一开始就立即加锁，这样就会有间隙锁，也能有效的避免幻读的发生。 但是需要注意的是，间隙锁是导致死锁的一个重要根源~所以，用起来也需要慎重。 总结 在RC级别中，幻读是没有办法解决的，因为RC中快照读是每一次都会重新生成快照，并且RC中也不会有间隙锁。 在RR级别中，因为有MVCC机制，对于普通的无锁查询，这种是属于快照读的，RR的快照读在同一个事务中只会读一次，所以在事务过程中，其他事务的变更不会影响到当前事务的查询结果。所以这种幻读是可以解决的。 当时，MVCC只能对快照读起作用，而对于加锁的读请求，这种属于当前读，当前读的话是可以查询到其他事务的变更的，所以会产生幻读。 想要解决幻读，可以使用Serializable这种隔离级别，或者使用RR也能解决大部分的幻读问题。 在RR级别下，为了避免幻读的发生，要么就是使用快照读，要么就是在事务一开始就加锁。
-
我面试几乎必问：你设计索引的原则是什么？怎么避免索引失效？ 之前我们已经详细介绍了关于索引的原理和索引的查询的原则，所谓工欲善其事必先利其器，各位在学习阶段一定要要循序渐进的来学习这块知识，千万不要眼高手低，一定要不急不躁，争取一个萝卜一个坑，学完后能一次性拿下这些知识点，然后再加以运用。 前面的文章我们讨论过，索引的设计要根据 WHERE 条件和 ORDER BY 还有 GROUP BY 后面的字段进行设计，至于原因具体在我前面的文章MySQL索引的原理有详细介绍。这里我们再简单概述下。 MySQL针对主键索引会维护一个B+树的结构，这个我们称之为聚簇索引，针对非主键（一般都是建立的联合索引）会对索引字段依次排序，然后从第一个字段值开始比较，第一个字段值相同就针对下一个字段值进行比较，依次往后推。 如果联合索引中的字段值都是一样的，那么就根据主键来排序。另外聚簇索引（主键索引）的B+树中保存的是一行记录的所有信息，非聚簇索引（非主键索引）仅仅保存索引字段值和主键字段值。 好了，对于索引原理的回顾我们就介绍到这里，本篇文章，我们继续介绍的是MySQL设置的基本原则，这个也很好理解，就是在设计和建立索引的时候需要遵循哪些原则，按照“标准”去建立索引。今天我们就将关于索引的设计的所有的原则一次性讲清楚。 再多说几句，关于这个知识点，在面试的时候，我经常会问候选人，以此来判断他对索引是不是真的有理解，而不是简单的背八股文！ 主键索引 对于主键索引其实是最简单的，但是这里有一些注意的地方还是再啰嗦下。 大家在设计主键的时候一定要是自增的，非常不建议使用UUID作为主键。 为什么？因为UUID是无序的，MySQL在维护聚簇索引的时候都是按照主键的顺序排序的，也就是说每个数据页中的数据一定是按照主键从小到排序的，而且，数据与数据之前是通过单向链表连接的，上一个数据页中的最大的主键的值一定是小于下一个数据页中的最小的主键的值，数据页和数据页之间是通过双向链表来维护的。 我们还是老规矩，画个图帮助大家理解 如果主键是自增的，MySQL只需要根据主键目录能很快的定位到新增的记录应该插入到哪里，如果主键不是自增的那么每次都需要从头开始比较，然后找到合适的位置，再将记录插入进去，这样真的严重影响效率，所以主键的设计一定要是自增的。 另外唯一索引和主键索引类似，但是唯一索引不一定是自增的，所以维护唯一索引的成本肯定是大于主键索引的。 但是唯一索引的值是唯一的（唯一索引可以有一个值为 NULL），可以更快的通过索引字段来确定一条记录，但是可能需要进行回表查询（至于什么是回表就不再赘述了，前面文章已经详细的讲解过了）。 为频繁查询的字段建立索引 我们在建立索引的时候，要为那些经常作为查询条件的字段建立索引，这样能够提高整个表的查询速度。 但是查询条件一般不是一个字段，所以一般是建立的联合索引比较多。 另外查询条件中一般会有like这样的模糊查询，如果是模糊查询请最好遵守最左前缀查询原则。 避免为”大字段”建立索引 这个可以换句话说：就是尽量使用数据量小的字段作为索引。 举个例子来说，假设有两个这样的字段，一个是varchar(5),一个是varchar(200)，这种情况下优先选择为varchar(5)的字段建立索引，因为MySQL在维护索引的时候是会将字段值一起维护的，那这样必然会导致索引占用更多的空间，另外在排序的时候需要花费更多的时间去对比。 那假如就要为varchar(100)建立索引呢？那就取部分数据，例如 address 类型为varchar(200)，在建立索引的时候可以这么写： CREATE INDEX tbl_address ON dual(address(20)); 选择区分度大的列作为索引 这又是什么意思？举个例子相信大家一下子就明白了。 假设现在有一个”性别”字段，里面存放的数据的值要么是男，要么是女，那么这样的字段很不适合作为索引。 这样的字段的值的主要特点就是区分度不够高，而区分度不高的字段不适合做索引，为什么呢？ 因为如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据。 在这些情况下，还不如不要索引，因为MySQL他还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。 惯用的百分比界线是”30%”。（匹配的数据量超过一定限制的时候查询器会放弃使用索引（这也是索引失效的场景之一哦）。 这就是原因。所以看到这里相信大家应该知道为什么要尽量避免使用基数小的字段作为索引了吧。其实这里涉及到MySQL的一个专有名词【Cardinality（索引基数）是mysql索引很重要的一个概念】 尽量为ORDER BY 和 GROUP BY 后面的字段建立索引 将 Order By后面的字段建立索引，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在B+树中的记录都是排序好的。 GROUP BY 和 ORDER BY 其实是类似，所以将这两个放在一起说了。 因为在GROUP BY 的时候也要先根据 GROUP BY 后面的字段排序，然后在执行聚合操作。 如果 GROUP BY 后面的字段没有排序，那么这个时候MySQL是需要先进行排序的，这样就会产生临时表，一个排好序的临时表，然后再在临时表中执行聚合操作，这样子当然效率很低了，如果 GROUP BY 后面的字段已经建立了索引，那么MySQL 就不需要再去排序，也就不会产生临时表。 然而比较坑的是，如果 GROUP BY的列和 ORDER BY的列不一样，即使都有索引也会产生临时表，其实对于这些情况我网上搜了下好像还很多，这里我给大家列出来，说实话，这些虽然是标准，但是这个标准好像很难实现，因为实际的场景肯定没这么简单和单纯 1. 如果GROUP BY 的列没有索引,产生临时表. 2. 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表. 3. 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表. 4. 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表. 5. 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表. 6. 如果DISTINCT 和 ORDER BY的列没有索引,产生临时表. 7. GROUP BY 和 ORDER BY 的列一样且是主键，但SELECT 列含有除GROUP BY列之外的列，也会产生临时表 不要在条件中使用函数 如果是已经建立好的索引的字段在使用的时候执行了函数操作，那么这个索引就使用不到了。 这是为什么？ 因为MySQL为该索引维护的B+树就是基于该字段原始数据的，如果正在使用过程中加了函数，MySQL就不会认为这个是原来的字段，那肯定不会走索引了。 但是如果有人就犟，那我就要使用到函数怎么办？总不能为了索引而改变业务啊？如果是使用MySQL内部函数导致索引失效的，那么在建立索引的时候可以连着函数一起创建。 这又是什么意思？假设有一个字段叫age，并为其创建了索引，但是使用的时候是这样子的 SELECT * FROM student WHERE round(age) = 2; 这个时候索引是使用不到的，那么如果真的非要让round(age)也走索引，那么你可以这么创建索引 create index stu_age_round on test(round(age)); 这个时候在通过上面的方式去查询，索引就是生效的，相信这个大家是能想明白的。 不要建立太多的索引 因为MySQL维护索引是需要空间和耗费性能的，MySQL会为每个索引字段维护一颗B+树。 所以如果索引过多，这无疑是增加了MySQL的负担。 频繁增删改的字段不要建立索引 这个就很好理解了，因为我们前面早就介绍过，字段的变化MySQL是需要重新维护索引的。 假设某个字段频繁修改，那就意味着需要频繁的重建索引，这必然影响MySQL的性能啊。这里不再多说了。 说到这里大部分说的是所以设计的时候需要注意的一些原则，其实真正的原则还是需要根据实际的业务变更的，没有所谓的“公式”，只要适合自己实际的业务场景的设计才是最好的。所以大家也不要过于追求“优化”，因为这样往往会适得其反，毕竟脱离了业务谈技术就是在耍流氓。 好了下面我们再来一起重点看看哪些情况下索引会失效。(PS:本文基本全是理论，我想画图来表达，结果发现根本无法下手希望大家再坚持下，就快完事了。) 索引失效的常见场景 使用 OR 关键字会导致索引失效，不过如果要想使用OR 又不想让索引失效，那就得需要为or条件中的每个列都建立索引。这很显然是和上面的不要建立太多的索引相违背。 联合索引如果不遵循最左前缀原则，那么索引也将失效 使用模糊查询的时候以%开头也会导致索引失效（这里就不再重复原因了，因为前面的文章都是说过了，这里就是为了帮助大家再会回忆下） 索引列如果使用了隐式转换也会导致索引失效 假设字段 age 类型为 int，那我们一般是这么查询的 SELECT * FROM student WHERE age=15 上面这种情况是能使用到索引的，但是如果你这么写 SELECT * FROM student WHERE age='15' 那这种情况是使用不到索引的，也就是age列情的索引是失效的。 如果字段基数小也可能会导致索引失效，具体在本文的上面部分已经详细解释了，也就是MySQL 查询优化器导致的。 其他的一些原则请大家还是要去看下索引的原理和查询的基本原则，如果没有前面的铺垫，这些看起来似乎有些空洞。所以请大家在索引这一块一定要循序渐进的学习，这一块基本也是我们平时在使用MySQL时候的一些核心知识点了。
-
MySQL遵循最左前缀匹配原则，面试官：回去等通知吧 我们都知道，MySQL的Innodb引擎中，索引是通过B+树来实现的。不管是普通索引还是联合索引，都需要构造一个B+树的索引结构。 那么，我们都知道普通索引的存储结构中在B+树的每个非节点上记录的索引的值，而这棵B+树的叶子节点上记录的是聚簇索引（主键索引）的值。 如： 那么，如果是联合索引的话，这棵B+树又是如何存储的呢？ 在联合索引中，联合索引(name,age)也是一个B+树，非叶子节点中记录的是name,age两个字段的值，叶子节点中记录的是name，age两个字段以及主键id的值。 在存储的过程中，如上图所示，当age不同时，按照age排序，当age相同时，则按照name排序。 所以，了解了索引的存储结构之后，我们就很容易理解最左前缀匹配了：因为索引底层是一个B+树，如果是联合索引的话，在构造B+树的时候，会先按照左边的key进行排序，左边的key相同时再依次按照右边的key排序。 所以，在通过索引查询的时候，也需要遵守最左前缀匹配的原则，也就是需要从联合索引的最左边开始进行匹配，这时候就要求查询语句的where条件中，包含最左边的索引的值。 在了解了最左前缀匹配之后，日常我们在工作中，经常在简历索引以及查询的时候，都会基于这个默认的约定进行索引的设计和SQL的优化。 大家都默认MySQL一定是遵循最左前缀匹配的。会认为当一个age,name的联合索引存在时，如果查询语句中不包含age作为条件，就一定不走索引。 MySQL一定是遵循最左前缀匹配的，这句话在以前是正确的，没有任何毛病。但是在MySQL 8.0中，就不一定了。 索引跳跃扫描 MySQL 8.0.13 版本中，对于range查询（什么是range后面会提到)，引入了索引跳跃扫描（Index Skip Scan）优化，支持不符合组合索引最左前缀原则条件下的SQL，依然能够使用组合索引，减少不必要的扫描。 通过一个例子给大家解释一下，首先有下面这样一张表（参考了MySQL官网的例子，但是我做了些改动和优化）： CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL); CREATE INDEX idx_t on t1(f1,f2); INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (2,1), (2,2), (2,3), (2,4), (2,5); INSERT INTO t1 SELECT f1, f2 + 5 FROM t1; INSERT INTO t1 SELECT f1, f2 + 10 FROM t1; INSERT INTO t1 SELECT f1, f2 + 20 FROM t1; INSERT INTO t1 SELECT f1, f2 + 40 FROM t1; 通过上面的SQL，先创建一张t1表，并把f1,f2两个字段设置为联合索引。之后再向其中插入一些记录。 分别在MySQL 5.7.9和MySQL 8.0.30上执行EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 = 40; 可以看到，主要有以下几个区别： MySQL 5.7中，type = index，rows=160，extra=Using where;Using index MySQL 8.0中，type = range，rows=16，extra=Using where;Using index for skip scan 这里面的type指的是扫描方式，range表示的是范围扫描，index表示的是索引树扫描，通常情况下，range要比index快得多。 从rows上也能看得出来，使用index的扫描方式共扫描了160行，而使用range的扫描方式只扫描了16行。 接着，重点来了，那就是为啥MySQL 8.0中的扫描方式可以更快呢？主要是因为Using index for skip scan 表示他用到了索引跳跃扫描的技术。 也就是说，虽然我们的SQL中，没有遵循最左前缀原则，只使用了f2作为查询条件，但是经过MySQL 8.0的优化以后，还是通过索引跳跃扫描的方式用到了索引了。 优化原理 那么他是怎么优化的呢？ 在MySQL 8.0.13 及以后的版本中，SELECT f1, f2 FROM t1 WHERE f2 = 40;SQL执行过程如下： 获取f1字段第一个唯一值，也就是f1=1 构造f1=1 and f2 = 40，进行范围查询 获取f1字段第二个唯一值，也就是f1=2 构造f1=2 and f2 = 40，进行范围查询 一直扫描完f1字段所有的唯一值，最后将结果合并返回 也就是说，最终执行的SQL语句是像下面这样的： SELECT f1, f2 FROM t1 WHERE f1 =1 and f2 = 40 UNION SELECT f1, f2 FROM t1 WHERE f1 =2 and f2 = 40; 即，MySQL的优化器帮我们把联合索引中的f1字段作为查询条件进行查询了。 限制条件 在知道了索引跳跃扫描的执行过程之后，很多聪明的读者其实就会发现，这种查询优化比较适合于f1的取值范围比较少，区分度不高的情况，一旦f1的区分度特别高的话，这种查询可能会更慢。 所以，真正要不要走索引跳跃扫描，还是要经过MySQL的优化器进行成本预估之后做决定的。 所以，这种优化一般用于那种联合索引中第一个字段区分度不高的情况。但是话又说回来了，我们一般不太会把区分度不高的字段放在联合索引的左边，不过事无绝对，既然MySQL给了一个优化的方案，就说明还是有这样的诉求的。 但是，我们不能依赖他这个优化，建立索引的时候，还是优先把区分度高的，查询频繁的字段放到联合索引的左边。 除此之外，在MySQL官网中，还提到索引跳跃扫描还有一些其他的限制条件： 表T至少有一个联合索引，但是对于联合索引(A,B,C,D)来说，A和D可以是空的，但B和C必须是非空的。 查询必须只能依赖一张表，不能多表join 查询中不能使用GROUP BY或DISTINCT语句 查询的字段必须是索引中的列
-
我的阿里二面，为什么MySQL选择REPEATABLE READ作为默认隔离级别？ 关于MySQL的事务隔离级别，相信很多读者都不陌生，网商有很多种相关的文章，很多人对于各种隔离级别，以及不同的级别可以解决的一些读现象都是如数家珍的。 如果大家对这部分知识不了解，可以看一下我几年前写的文章： 数据库的读现象浅析中介绍了什么是脏读、幻读、不可重复读以及他们产生的背景。 深入分析事务的隔离级别中介绍了数据库的隔离级别以及解决的读现象问题。 我们知道，ANSI/ISO SQL定义的标准隔离级别有四种，从高到底依次为：可序列化(Serializable)、可重复读(Repeatable Reads)、提交读(Read Committed)、未提交读(Read Uncommitted)。 RU隔离级别下，可能发生脏读、幻读、不可重复读等问题。 RC隔离级别下，解决了脏读的问题，存在幻读、不可重复读的问题。 RR隔离级别下，解决了脏读、不可重复读的问题，存在幻读的问题。 Serializable隔离级别下，解决了脏读、幻读、不可重复读的问题。 这四种隔离级别是ANSI/ISO SQL定义的标准定义的，我们比较常用的MySQL对这四种隔离级别是都支持的。但是Oracle数据库只支持Serializable和Read Committed。 但是，可能很多人都不知道，Oracle默认的隔离级别是 RC，而MySQL默认的隔离级别是 RR。 那么，你知道为什么Oracle选择RC作为默认级别，而MySQL要选择RR作为默认的隔离级别吗？ 这是我之前面试的时候，问过候选人的一个问题。 很多人认为这个问题没有意义，这不是在逼着我们背八股文么？ 但是其实并不是，如果你能耐心的看完这篇文章，你就会发现我的良苦用心。 Oracle 的隔离级别 前面我们说过，Oracle只只支持ANSI/ISO SQL定义的Serializable和Read Committed，其实，根据Oracle官方文档给出的介绍，Oracle支持三种隔离级别： 即Oracle支持Read Committed、Serializable和Read-Only。 Read-Only只读隔离级别类似于可序列化隔离级别，但是只读事务不允许在事务中修改数据，除非用户是SYS。 在Oracle这三种隔离级别中，Serializable和Read-Only显然都是不适合作为默认隔离级别的，那么就只剩Read Committed这个唯一的选择了。 MySQL 的隔离级别 相比于Oracle，MySQL的默认隔离级别的可选范围就比较大了。 首先，我们先从四种隔离级别中排除Serializable和Read Uncommitted这两种，主要是因为这两个级别一个隔离级别太高，一个太低。太高的就会影响并发度，太低的就有脏读现象。 那么，剩下的RR和RC两种，怎么选？ 这件事要从long long ago 说起。 在MySQL设计之处，他的定位就是提供一个稳定的关系型数据库。而为了要解决MySQL单点故障带来的问题，MySQL采用主从复制的机制。 所谓主从复制，其实就是通过搭建MySQL集群，整体对外提供服务，集群中的机器分为主服务器（Master）和从服务器（Slave），主服务器提供写服务，从服务器提供读服务。 为了保证主从服务器之间的数据的一致性，就需要进行数据同步，大致的同步过程如下，这里就不详细介绍了 MySQL在主从复制的过程中，数据的同步是通过bin log进行的，简单理解就是主服务器把数据变更记录到bin log中，然后再把bin log同步传输给从服务器，从服务器接收到bin log之后，再把其中的数据恢复到自己的数据库存储中。 那么，binlog里面记录的是什么内容呢？格式是怎样的呢？ MySQL的bin log主要支持三种格式，分别是statement、row以及mixed。MySQL是在5.1.5版本开始支持row的、在5.1.8版本中开始支持mixed。 statement和row最大的区别，当binlog的格式为statemen时，binlog 里面记录的就是 SQL 语句的原文（这句话很重要！！！后面会用的到）。 关于这几种格式的区别，就不在这里详细展开了，之所以要支持row格式，主要是因为statement格式中存在很多问题，最明显的就是可能会导致主从数据库的数据不一致。详细介绍可以参考丁奇在极客时间上面的分享《MySQL实战45讲》。 那么，讲这个主从同步和bin log我们要讲的隔离级别有啥关系呢？ 有关系，而且关系很大。 因为MySQL早期只有statement这种bin log格式，这时候，如果使用提交读(Read Committed)、未提交读(Read Uncommitted)这两种隔离级别会出现问题。 比如，在MySQL官网上，有人就给官方曾经提过一个相关的Bug 这个bug的复现过程如下： 有一个数据库表t1，表中有如下两条记录： CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, KEY `a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into t1 values(10,2),(20,1); 接着开始执行两个事务的写操作： 以上两个事务执行之后，数据库里面的记录会变成（11，2）和（20，2），这个发上在主库的数据变更大家都能理解。 因为事务的隔离级别是read committed，所以，事务1在更新时，只会对b=2这行加上行级锁，不会影响到事务2对b=1这行的写操作。 以上两个事务执行之后，会在bin log中记录两条记录，因为事务2先提交，所以UPDATE t1 SET b=2 where b=1;会被优先记录，然后再记录UPDATE t1 SET a=11 where b=2;（再次提醒：statement格式的bin log记录的是SQL语句的原文） 这样bin log同步到备库之后，SQL语句回放时，会先执行UPDATE t1 SET b=2 where b=1;，再执行UPDATE t1 SET a=11 where b=2;。 这时候，数据库中的数据就会变成（11，2）和（11，2）。这就导致主库和备库的数据不一致了！！！ 为了避免这样的问题发生。MySQL就把数据库的默认隔离级别设置成了Repetable Read，那么，Repetable Read的隔离级别下是如何解决这样问题的那？ 那是因为Repetable Read这种隔离级别，会在更新数据的时候不仅对更新的行加行级锁，还会增加GAP lock。上面的例子，在事务2执行的时候，因为事务1增加了GAP lock，就会导致事务执行被卡住，需要等事务1提交或者回滚后才能继续执行。（关于GAP lock，我后面会有文章单独介绍）。 除了设置默认的隔离级别外，MySQL还禁止在使用statement格式的bin log的情况下，使用READ COMMITTED作为事务隔离级别。 一旦用户主动修改隔离级别，尝试更新时，会报错： ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' 总结 所以，现在我们知道了，为什么MySQL选择RR作为默认的数据库隔离级别了吧，其实就是为了兼容历史上的那种statement格式的bin log。 那么，本文讲到这里，算是关于MySQL隔离级别这个知识点讲了大概不到1/5吧，通过这篇文章， 你或许还会有以下问题： 1、row格式和statement有什么区别？使用row的情况下，可以使用RR吗？ 2、文中提到的RC的GAP lock到底是什么？ 3、RR和RC到底有什么区别？RR是如何解决不可重复读问题的？ 4、既然MySQL数据库默认选择了RR，那么，为啥像阿里这种大的互联网公司会把默认的隔离级别改成RC？ 关于以上几个问题，你知道答案吗，或者你对哪个更感兴趣呢？欢迎留言！我会挑大家较为感兴趣的专题在后面的文章中继续深入展开介绍。 现在你还觉得这个问题没有意义吗？ 我其实是想通过这样一个看似无意义的问题，延展出更多的知识，这样可以更加全方位的了解候选人。
