表创建
| 方法 | 描述 |
|---|---|
| 尽量使用非定长类型 | varchar 代替 char |
尽可能使用 not null |
● 非 null 字段的处理要比 null 字段的处理高效些！且不需要判断是否为 null。● null 在MySQL 中，不好处理，存储需要额外空间，运算也需要特殊的运算符 |
| 单表字段不宜过多 | 二三十个就极限了 |
主键的选择
● 全局唯一性：不能出现重复的 ID 号，既然是唯一标识，这是最基本的要求。
● 趋势递增：在 MySQL InnoDB 引擎中使用的是聚集索引，由于多数 RDBMS 使用 B-tree 的数据结构来存储索引数据，在主键的选择上面我们应该尽量使用有序的主键保证写入性能。
● 单调递增：保证下一个 ID 一定大于上一个 ID，例如事务版本号、IM 增量消息、排序等特殊需求。
● 信息安全：如果 ID 是连续的，恶意用户的扒取工作就非常容易做了，直接按照顺序下载指定 URL 即可；如果是订单号就更危险了，竞对可以直接知道我们一天的单量。所以在一些应用场景下，会需要 ID 无规则、不规则。
| 主键方案 | 描述 |
|---|---|
| 主键自增的形式 | 让不同表初始化一个不同的初始值，然后按指定的步长进行自增。例如有3张拆分表，初始主键值为1，2，3，自增步长为3 |
| SnowFlake 雪花算法 | 生成一个的 64 位比特位的 long 类型的唯一 id。 优点： ● 高并发分布式环境下生成不重复 id，每秒可生成百万个不重复 id。 ● 基于时间戳，以及同一时间戳下序列号自增，基本保证 id 有序递增。 ● 不依赖第三方库或者中间件。 ● 算法简单，在内存中进行，效率高。 缺点： ● 依赖服务器时间，服务器时钟回拨时可能会生成重复 id。 ● 法中可通过记录最后一个生成 id 时的时间戳来解决，每次生成 id 之前比较当前服务器时钟是否被回拨，避免生成重复 id。 |
UidGenerator |
百度开源的分布式唯一 ID 生成器。 UidGenerator 是 Java 实现的，基于 Snowflake 算法的唯一 ID 生成器。 |
Leaf |
美团点评分布式 ID 生成系统 |
索引
索引创建
| 方法 | 描述 |
|---|---|
经常 where 的字段 |
可以尝试在一个字段未建立索引时，根据该字段查询的效率，然后对该字段建立索引 |
经常 order by 的字段 |
● 当我们使用 order by 将查询结果按照某个字段排序时，如果该字段没有建立索引那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果） ● 但是如果我们对该字段建立索引 alter table 表名 add index(字段名)，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可 |
经常 join 的字段 |
对 join语句匹配关系（on）涉及的字段建立索引能够提高效率 |
| 使用短索引 | 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间， 如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。 |
| 定义有外键的数据列 | 定义有外键的数据列一定要建立索引。 |
索引不创建
| 方法 | 描述 |
|---|---|
| 基数较小的表 | 索引效果较差，没有必要在此列建立索引 |
| 更新频繁字段 | 更新频繁字段不适合创建索引 |
| 重复值过多的列 | 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低) |
| 数据量大的列 | 对于定义为 text、image 和 bit 的数据类型的列不要建立索引。 |
| 不要过度索引 | 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。 在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。 所以只保持需要的索引有利于查询即可。 |
| 尽量的扩展索引，不要新建索引 | 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。 |
索引失效
| 失效场景 | 描述 |
|---|---|
| 类型转换 | ● 索引列是字符串时，如果传入的条件参数是整数，会先转换成浮点数，再全表扫描，导致索引失效； ● 条件参数要尽可能与列的类型相同，避免隐式转换，或者把传入的条件参数转换成索引列的类型。 |
| 字段进行函数运算 | 比如下面两条SQL语句在语义上相同，但是第一条会使用主键索引而第二条不会 ● select * from user where id = 20-1;● select * from user where id+1 = 20; |
like 查询以通配符开头 |
● select * from article where title like '%mysql%'; 这种 SQL 的执行计划用不了索引（ like 语句匹配表达式以通配符开头），因此只能做全表扫描● select * from article where title like 'mysql%';这种 like 是可以利用索引的（当然前提是 title 字段建立过索引）。 |
or 其中某个条件无索引 |
一但有一边无索引可用就会导致整个 SQL 语句的全表扫描 |
| 复合索引只对第一个字段有效 | ● alter table person add index(first_name,last_name);● 索引先按照从 first_name 中提取的关键字排序，如果无法确定先后再按照从 last_name提取的关键字排序，也就是说该索引表只是按照记录的 first_name 字段值有序。● 因此 select * from person where first_name = ? 是可以利用索引的，而 select * from person where last_name = ? 无法利用索引。● 对于 select * person from first_name = ? and last_name = ?，复合索引就比对 first_name 和 last_name 单独建立索引要高效些。 |
MySQL 为什么要使用 B+Tree 作为索引结构
● 首先，常规的数据库存储引擎，一般都是采用 B 树，或者 B+ 树来实现索引的存储。
● 因为 B 树是一种多路平衡树，用这种存储结构来存储大量数据的情况下，它的整体高度相比二叉树来说，会矮很多。
● 而对于数据库来说，所有数据必然是存储在磁盘上的，磁盘 IO 的效率实际上是很低的，特别是在随机磁盘 IO 的情况下效率更低。
● 所以树的高度就能够决定磁盘 IO 的次数，磁盘 IO 次数越少，性能的提升就会越大，这也是为什么采用 B 树作为索引存储结构的原因。
● 但是在 MySQL 的 InnoDB 存储引擎里面，采用的是 B+ 树作为索引存储结构。
● 相比于 B 树的结构，B+ 树做了几个方面的优化。
● 第一个是 B+ 树的所有数据都存储在叶子节点上，非叶子节点只会存储索引。
● 第二个是叶子节点的数据是使用双向链表进行关联的。
● 所以使用 B+ 树作为索引存储的结构，有几个方面的原因。
● 1.B+ 树非叶子节点不存储数据，所以每一层能够存储的索引数量会增加，意味着 B+ 树在相同高度的情况下存储的数据量比 B 树要多，使得磁盘 IO 的次数更少。
● 2.MySQL 中范围查询是一个比较常用的操作，而 B+ 树的所有存储在叶子节点的数据使用双向链表 来关联。
所以在查询的时候只需要查两个节点进行遍历就行（只需要找到起始节点，然后基于叶子节点的链表结构往下读取即可），而 B 树需要获取所有节点，所以 B+ 树在范围查询效率上会更高。
● 3.在数据检索方面，由于所有的数据都存储在叶子节点，所以 B+ 树的 IO 次数会更加稳定一些。
● 4.因为叶子节点存储所有数据，所以 B+ 树的全局扫描能力更强一些，因为它只需要扫描叶子节点。但是 B 树需要遍历整个树。
● 另外基于 B+ 树这样的结构，如果采用自增的整形数据作为主键，还能够更好的避免增加数据的时候，带来的叶子节点分裂导致大量运算的问题。
● 总的来说我认为技术方案的选择，更多的是去解决当前场景下的特定问题
● 并不是说 B+ 树就是最好的选择，就像 MengoDB 里面采用 B 树结构，其实是关系型数据库和非关系型数据库的一个差异。
● 以上就是我对这个问题的理解。
MySQL 索引的优缺点
优点
● 通过 B+ 树 的结构来存储数据，可以大大减少数据检索时磁盘 IO 次数，从而提升数据查询性能。
● B+ 树索引在进行范围查找的时候，只需要找到起始节点，然后基于叶子节点的链表结构往下读取即可，查询效率较高。
● 通过唯一索引约束，可以保证数据表中每行数据的唯一性。
缺点
● 数据在做增加、修改、删除，需要涉及到索引的维护，当数据量较大的情况下，维护索引会带来较大的性能开销。
● 一个表中允许存在一个聚簇索引和多个非聚簇索引，但是索引数不能创建太多，否则会造成索引维护成本过高。
● 创建索引的时候，需要考虑到索引字段值的分散性，如果字段的重复数据过多，创建索引反而会带来性能降低。
聚簇索引和非聚簇索引的区别
| 区别 | 聚簇索引 | 非聚簇索引 |
|---|---|---|
| 存储方式不同 | 聚簇索引是将数据按照索引顺序存储在磁盘上， 因此聚簇索引的数据存储和索引存储是混合在一起的； |
而非聚簇索引则是将索引和数据分开存储的。 |
| 唯一性不同 | 聚簇索引必须是唯一的， 因为它们是按照索引顺序存储数据的， 如果有两条数据具有相同的索引值，则它们将无法区分； | 而非聚簇索引可以是唯一的， 也可以不是唯一的。 |
| 查询效率不同 | 对于聚簇索引来说，查询效率往往比非聚簇索引更高， 因为聚簇索引将数据存储在一起， 查询时可以更快地定位到所需的数据行； | 而对于非聚簇索引来说，查询时需要先查找索引， 再根据索引找到对应的数据行，因此查询效率相对较低。 |
| 插入数据效率不同 | 对于聚簇索引来说，由于数据按照索引顺序存储， 因此在插入新数据时，可能需要移动已有的数据， 因此插入数据的效率较低； | 而对于非聚簇索引来说， 插入数据时只需要更新索引，因此效率相对较高。 |
- 需要注意的是，一个表只能有一个聚簇索引，因为数据只能按照一种顺序存储；
- 而可以有多个非聚簇索引，以满足不同的查询需求。
- 在设计数据库时，需要根据具体的应用场景和查询需求选择不同的索引类型。
查询
脏读，幻读，不可重复读
limit 5000000,10 和 limit 10 速度一样吗？
● limit 5000000,10 表示从结果集中的第 5000000 数据开始，返回 10 行数据。
● limit 10 表示返回结果集中前 10 行数据。
速度差异主要取决于两个关键因素
- 1.数据量
- 如果数据量很小，那么两个查询的速度相差不大。
- 如果数据量很大，那么
limit 5000000,10需要跳过大量的数据行才能够返回结果。limit 10则不需要跳过这些数据行。
- 2.索引
- 如果有合适的索引，
limit 5000000,10数据库可以通过索引直接定位到资金的行号返回结果。limit 10只需要返回前 10 行即可。
- 如果有合适的索引，
● 但是不管什么因素，limit 5000000,10 的查询速度肯定比 limit 10 的速度更慢一些。
● 随着数据量的增大，这个差异会更加明显。
两个优化方案
● 通过 sql 语句优化，通过子查询跳过
select *from userwhere id >= (select id from user order by id limit 1 offset 4999999)order by idlimit 10
● 业务层面优化，正常查询很少会出现跳过 5000000 的情况，这种设计本身存在问题。
如果设计到大量数据的分页查询，可以设计冷热数据分离和针对运营用到的数据进行分析
Mysql 慢查询该如何优化？
● 检查是否走了索引，如果没有则优化 SQL 利用索引
● 检查所利用索引，是否是最优索引
● 检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据
● 检查表中数据是否过多，是否应该进行分库分表了
● 检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源
SQL 优化
● 加索引。增加索引是一种简单高效的手段，但是需要选择合适的列，同时避免导致索引失效的操作，比如 Like，函数等。
● 避免返回不必要的数据列，减少返回的数据列可以增加查询的效率。
● 根据查询分析器适当优化 SQL 的结构，比如是否走全表扫描，避免子查询等。
● 分库分表。在单标数据量较大的情况或者并发连接数过高的情况下，通过这种方式可以有效提升查询效率。
● 读写分离。针对读多写少的场景，这样可以保证写操作的数据库承受更小的压力，也可以缓解独占锁和共享锁的竞争。
分布式数据库
分片中间件
分片算法
存储过程
为什么不推荐使用存储过程
Mybatis 的一级、二级缓存
MyBatis提供了两种级别的缓存：一级缓存（本地缓存）和二级缓存（全局缓存）。它们分别位于不同的作用范围，有不同的特性和使用场景。
一级缓存（本地缓存）：
1.作用范围： 一级缓存是在 SqlSession 的生命周期内有效，也就是说，每个 SqlSession 拥有独立的一级缓存。
2.默认开启： 一级缓存在 MyBatis 中默认是开启的，无需额外配置。
3.特点： 当执行查询操作时，查询的结果会被缓存在当前 SqlSession 中。如果再次执行相同的查询，MyBatis 会首先尝试从缓存中获取数据，而不再访问数据库。
4.自动刷新：MyBatis会在执行 insert、update、delete 等写操作时自动清空一级缓存，以保持数据的一致性。
二级缓存（全局缓存）：
1.作用范围： 二级缓存是在多个 SqlSession 之间共享的，即多个 SqlSession 可以共享同一个二级缓存。
2.配置开启： 二级缓存需要手动配置开启，需要在映射文件的 <mapper> 标签下添加 <cache> 元素。
<cache eviction="LRU" flushInterval="60000" size="1024" readOnly="true"/>
3.特点： 二级缓存能够跨 SqlSession 共享查询结果，有效减少数据库访问次数。它的数据存储在全局范围的缓存中，可以由多个SqlSession 访问。
4.缓存策略： 你可以根据需求选择不同的缓存策略（例如 LRU、FIFO 等），以及配置缓存的大小、刷新间隔等参数。
5.注意事项： 二级缓存可以缓存的对象需要是可序列化的，要确保对象可以正确地序列化和反序列化。另外，对于关联数据的更新操作，需要手动清除相关的二级缓存，以避免脏数据的问题。
需要注意的是，虽然缓存可以提高查询性能，但不合理的使用缓存可能导致数据不一致等问题，特别是在分布式环境下。因此，在使用缓存时需要根据业务需求和性能测试结果进行合理的配置和管理。