本文用于记录笔者在学习和使用MySQL时遇到的一些问题以及思考。

主要分为四个部分，也都是之前在面试的时候被提问过的问题，在写过第一次面试的面经之后就再没有总结过面试的问题了，其实在第二次面试被挂了之后就一直都在摆烂。

从头开始总结一下最近一段时间学到的一些内容，避免以后在秋招的时候还是不会。

B+树

有关 B+ 树的问题实在第二次面试的时候被提问到的，当时的回答也是一塌糊涂。

学习MySQL的索引，就避不开要了解 B+ 树，在 MySQL 里 InnoDB 存储引擎就是采用 B+ 树来组织数据的，这是我们在背八股文时一定要记住的东西。那么在 B+ 树里的节点里存放的是什么呢？查询数据的过程又是怎样的？为什么要选择 B+ 树来组织数据呢？下面我们来深入学习一下。

从数据页的角度看 B+ 树

这次，我们从数据页的角度看 B+ 树，看看每个节点长啥样。

InnoDB 是如何进行存储数据的？

MySQL 支持多种存储引擎，不同的存储引擎，存储数据的方式也是不同，我们最常用的就是 InnoDB 存储引擎。

记录时按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

因此，InnoDB 的数据时按「数据页」为单位来读写的，也就是说，当需要读一条数据的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。InnoDB 数据页的默认大小是 16KB，数据页包括七个部分，结构如下图：

这 7 个部分的作用如下图：

在 File Header 中有两个指针，分别指向上一个数据页和下一个数据页，连接起来的页相当于一个双向的链表，如下图所示：

采用链表的结构是让数据页之间不需要是物理上的连续的，而是逻辑上的连续。

数据页的主要作用是存储记录，也就是数据库的数据，所以重点说一下数据页中的 User Records 是怎么组织数据的。

数据页中的记录按照「主键」顺序组成单向链表，单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。

因此，数据页中有一个页目录，起到记录的索引作用，就像我们书那样，针对书中内容的每个章节设立了一个目录，想看某个章节的时候，可以查看目录，快速找到对应的章节的页数，而数据页中的页目录就是为了能快速找到记录。

那 InnoDB 是如何给记录创建页目录的呢？页目录与记录的关系如下图：

页目录创建的过程如下：

将所有的记录划分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录；
每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段（上图中粉红色字段）
页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。

从图可以看到，页目录就是由多个槽组成的，槽相当于分组记录的索引。然后，因为记录是按照「主键值」从小到大排序的，所以我们通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到对应的记录，无需从最小记录开始遍历整个页中的记录链表。

如果某个槽内的记录很多，然后因为记录都是单向链表串起来的，那这样在槽内查找某个记录的时间复杂度不就是 O(n) 了吗？

InnoDB 对每个分组中的记录条数都是有规定的，槽内的记录就只有几条：

第一个分组中的记录只能有 1 条记录；
最后一个分组中的记录条数范围只能在 1-8 条之间；
剩下的分组中记录条数范围只能在 4-8 条之间。

B+ 树是如何进行查询的？

上面我们都是在说一个数据页中的记录检索，因为一个数据页中的记录是有限的，且主键值是有序的，所以通过对所有记录进行分组，然后将组号（槽号）存储到页目录，使其起到索引作用，通过二分查找的方法快速检索到记录在哪个分组，来降低检索的时间复杂度。

但是，当我们需要存储大量的记录时，就需要多个数据页，这时我们就需要考虑如何建立合适的索引，才能方便定位记录所在的页。

为了解决这个问题，InnoDB 采用了 B+ 树作为索引。磁盘的 I/O 操作次数对索引的使用效率至关重要，因此在构造索引的时候，我们更倾向于采用“矮胖”的 B+ 树数据结构，这样所需要进行的磁盘 I/O 次数更少，而且 B+ 树更适合进行关键字的范围查询。

InnoDB 里的 B+ 树中的每个节点都是一个数据页，结构示意图如下：

通过上图，我们看出 B+ 树的特点：

只有叶子节点（最底层的节点）才存放了数据，非叶子节点（其他上层节）仅用来存放目录项作为索引。
非叶子节点分为不同层次，通过分层来降低每一层的搜索量；
所有节点按照索引键大小排序，构成一个双向链表，便于范围查询；

可以得出，在定位记录所在哪一个页时，也是通过二分法快速定位到包含该记录的页。定位到该页后，又会在该页内进行二分法快速定位记录所在的分组（槽号），最后在分组内进行遍历查找。

聚簇索引和二级索引

另外，索引又可以分成聚簇索引和非聚簇索引（二级索引），它们区别就在于叶子节点存放的是什么数据：

聚簇索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚簇索引的叶子节点；
二级索引的叶子节点存放的是主键值，而不是实际数据。

因为表的数据都是存放在聚簇索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚簇索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个。

InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

如果有主键，默认会使用主键作为聚簇索引的索引键；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；

一张表只能有一个聚簇索引，那为了实现非主键字段的快速搜索，就引出了二级索引（非聚簇索引/辅助索引），它也是利用了 B+ 树的数据结构，但是二级索引的叶子节点存放的是主键值，不是实际数据。

二级索引的 B+ 树如下图，数据部分为主键值：

因此，如果某个查询语句使用了二级索引，但是查询的数据不是主键值，这时在二级索引找到主键值后，需要去聚簇索引中获得数据行，这个过程就叫作「回表」，也就是说要查两个 B+ 树才能查到数据。不过，当查询的数据是主键值时，因为只在二级索引就能查询到，不用再去聚簇索引查，这个过程就叫作「索引覆盖」，也就是只需要查一个 B+ 树就能找到数据。

看了这么多关于 B+ 树和数据库索引的内容，我们对其也有了基本的了解，那就来再解决一个问题吧，为什么要用 B+ 树，而不是其他的数据结构，也顺便了解一下其他的树相关的知识。

为什么 MySQL 采用 B+ 树作为索引？

要解释这个问题，其实不单单要从数据结构的角度出发，还要考虑磁盘 I/O 操作次数，因为 MySQL 的数据是存储在磁盘中的。

怎样的索引的数据结构是好的？

MySQL 的数据是持久化的，意味着数据（索引+记录）是保存在磁盘上的，因为这样及时设备断电了，数据也不会丢失。

这样就带来了一个新的问题，速度太慢，因为磁盘是一个慢的离谱的存储设备。磁盘中读取的速度比从内存中读取的速度要慢上万倍，甚至几十万倍。

由于数据库的索引是保存到磁盘上的，因此当我们通过索引查找某行数据的时候，就需要先从磁盘读取索引到内存，再通过索引从磁盘中找到某行数据，然后读入到内存，也就是说查询过程中会发生多次磁盘 I/O，而磁盘 I/O 次数越多，所消耗的时间也就越大。

所以，我们希望索引的数据结构能在尽可能少的磁盘的 I/O 操作中完成查询工作，因为磁盘 I/O 操作越少，所消耗的时间也就越小。

另外，MySQL 是支持范围查找的，所以索引的数据结构不仅要能高效地查询某一个记录，而且也要能高效地执行范围查找。

所以，要设计一个适合 MySQL 索引的数据结构，至少满足以下要求：

能在尽可能少的磁盘的 I/O 操作中完成查询工作；
要能高效地查询某一个记录，也要能高效地执行范围查找；

索引的数据最好是按顺序排列的，这样才能使用「二分查找法」高效定位数据。

二分查找树

用数组来实现线性排序的数据虽然简单好用，但是插入新元素的时候性能太低。

其次，有序的数组在使用二分查找的时候，每次查找都要不断计算中间的位置。

那我们能不能设计一个非线形且天然适合二分查找的数据结构呢？

有的，请看下图这个神奇的操作，找到所有二分查找中用到的所有中间节点，把他们用指针连起来，并将最中间的节点作为根节点。

请添加图片描述

这样这个数组就变成了一个二叉查找树。

二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点，这样我们在查询数据时，不需要计算中间节点的位置了，只需将查找的数据与节点的数据进行比较。

另外，二叉查找树解决了插入新节点的问题，因为二叉查找树是一个跳跃结构，不必连续排列。这样在插入的时候，新节点可以放在任何位置，不会像线性结构那样插入一个元素，所有元素都需要向后排列。

因此，二叉查找树解决了连续结构插入新元素开销很大的问题，同时又保持着天然的二分结构。

那么既然已经解决了所有问题，是不是就可以直接用二叉查找树来组织索引了。

不行，因为二叉查找树有一个致命的问题，那就是当新插入的数据全都比已有数据大或者小，那这个二叉查找树就变成了一个链表了，也就失去了现有的优势。

请添加图片描述

自平衡二叉树

为了解决二叉查找树会在极端情况下退化成链表的问题，后面就有人提出平衡二叉查找树（AVL 树）。

主要是在二叉查找树的基础上增加了一些条件约束：每个节点的左子树和右子树的高度差不能超过 1。也就是说节点的左子树和右子树仍然为平衡二叉树。

除了平衡二叉查找树，还有很多自平衡的二叉树，比如红黑树，它也是通过一些约束条件来达到自平衡，不过红黑树的约束条件比较复杂，由于也被面试官提到过类似的问题，后面会单独写一篇博客来记录。

现在，平衡二叉树退化成为链表的情况被解决了，但是随着数据的插入，树会变得越来越高，查询次数也就越来越多。

下面我们来看一下二叉树和三叉树在有相同节点个数的情况下高度的差别。

当树的节点越多的时候，并且树的分叉数 M 越大的时候，M 叉树的高度会远小于二叉树的高度。

B 树

对于上一节中提到的问题，一个新的数据结构被提出——B 树，它不在限制一个节点就只能有 2 个子节点，而是允许 M 个子节点 (M>2)，从而降低树的高度。

B 树的每一个节点最多可以包括 M 个子节点，M 称为 B 树的阶，特点就是每个节点最多有 M-1个数据，超过这些要求的话，就会分裂节点。

这么看来，B 树好像又解决了所有已经出现的问题，还有新的吗，当然。毕竟我们的主角是 B+ 树。

正所谓“祸兮福所倚，福兮祸所伏”，正是因为 B 树的每个节点上都存了数据，就导致每个节点能存储的键值和指针变少了，因为每一页的大小是固定的，对吧？

在我们查询位于底层的某个节点（比如 A 记录）过程中，「非 A 记录节点」里的记录数据会从磁盘加载到内存，但是这些记录数据是没用的，我们只是想读取这些节点的索引数据来做比较查询，而「非 A 记录节点」里的记录数据对我们是没用的，这样不仅增多磁盘 I/O 操作次数，也占用内存资源。

另外，如果使用 B 树来做范围查询的话，需要使用中序遍历，这会涉及多个节点的磁盘 I/O 问题，从而导致整体速度下降。

B+ 树

好了好了，没想到设计一个索引需要的数据结构能遇到这么多困难，现在我们的救世主——B+ 树他终于来了。

B+ 树就是对 B 树做了一个升级，B+树的非叶子节点只存储键值，不存储数据，而叶子节点存储了所有的数据，并且构成了一个有序链表。

用户1260737：B+树

这样做的好处是，非叶子节点上由于没有存储数据，就可以存储更多的键值对，树就变得更加矮胖了，于是就更有劲了，每次搬的砖也就更多了。

由此一来，查找数据进行的磁盘 IO 就更少了，查询的效率也就更高了。

再加上叶子节点构成了一个有序链表，范围查询时就可以直接通过叶子节点间的指针顺序访问整个查询范围内的所有记录，而无需对树进行多次遍历。

至此，所有出现的问题都得到了解决，当然也并不是在所有的数据库中都使用 B+ 树来存储索引。比如在 MongoDB 中存储索引的数据结构就是 B 树。详细原因还是在后面的博客中记录吧，挖个坑先……

索引失效

腾讯第二次面试时提的问题，我回答的不知道，后来美团面试也问了，我答了几种情况，当时就只是背下来了，也不知道为什么，面试官想要更深入地问，我就又不会了。

那在本文中就详细解释一下这个问题吧。

在工作中，如果我们想提高一条语句查询速度，通常都会想对字段建立索引。

但是索引并不是万能的。建立了索引，并不意味着任何查询语句都能走索引扫描。

稍不注意，可能你写的查询语句是会导致索引失效，从而走了全表扫描，虽然查询的结果没问题，但是查询的性能大大降低（之前我甚至以为索引失效是索引直接被删除了，脑子坏了）。

索引长什么样？

我们先来看看索引存储结构长什么样？因为只有知道索引的存储结构，才能更好的理解索引失效的问题。

索引的存储结构跟 MySQL 使用哪种存储引擎有关，因为存储引擎就是负责将数据持久化在磁盘中，而不同的存储引擎采用的索引数据结构也会不相同。

MySQL 默认的存储引擎是 InnoDB，它采用 B+Tree 作为索引的数据结构。在创建表时，InnoDB 存储引擎默认会创建一个主键索引，也就是聚簇索引，其它索引都属于二级索引。

MySQL 的 MyISAM 存储引擎支持多种索引数据结构，比如 B+ 树索引、R 树索引、Full-Text 索引。MyISAM 存储引擎在创建表时，创建的主键索引默认使用的是 B+ 树索引。

虽然，InnoDB 和 MyISAM 都支持 B+ 树索引，但是它们数据的存储结构实现方式不同。不同之处在于：

InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身；
MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址；

举个栗子：

这里有一张 t_user 表，其中 id 字段为主键索引，其他都是普通字段。

如果使用的是 MyISAM 存储引擎，B+ 树索引的叶子节点保存数据的物理地址，即用户数据的指针，如下图：

如果使用的是 InnoDB 存储引擎， B+ 树索引的叶子节点保存数据本身，如下图所示（图中叶子节点之间是双向链表）。

InnoDB 存储引擎根据索引类型不同，分为聚簇索引（上图就是聚簇索引）和二级索引。它们区别在于，聚簇索引的叶子节点存放的是实际数据，所有完整的用户数据都存放在聚簇索引的叶子节点，而二级索引的叶子节点存放的是主键值，而不是实际数据。

如果将 name 字段设置为普通索引，那么这个二级索引长下图这样（图中叶子节点之间是双向链表）

在我们使用「主键索引」字段作为条件查询的时候，如果要查询的数据都在「聚簇索引」的叶子节点里，那么就会在「聚簇索引」中的 B+ 树检索到对应的叶子节点，然后直接读取要查询的数据。如下面这条语句：

1 2	// id 字段为主键索引 select * from t_user where id=1;

在我们使用「二级索引」字段作为条件查询的时候，如果要查询的数据都在「聚簇索引」的叶子节点里，那么需要检索两颗B+树：

先在「二级索引」的 B+ 树找到对应的叶子节点，获取主键值；
然后用上一步获取的主键值，在「聚簇索引」中的 B+ 树检索到对应的叶子节点，然后获取要查询的数据。

上面这个过程叫做回表，如下面这条语句：

1 2	// name 字段为二级索引 select * from t_user where name="林某";

在我们使用「二级索引」字段作为条件查询的时候，如果要查询的数据在「二级索引」的叶子节点，那么只需要在「二级索引」的 B+ 树找到对应的叶子节点，然后读取要查询的数据，这个过程叫做覆盖索引。如下面这条语句：

1 2	// name 字段为二级索引 select id from t_user where name="林某";

什么情况会导致索引失效？

对索引使用左或者左右模糊匹配

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。

为什么 like 关键字左或者左右模糊匹配无法走索引呢？

因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。

假设我们要查询 name 字段前缀为「林」的数据，也就是 name like '林%'，扫描索引的过程：

首节点查询比较：林这个字的拼音大小比首节点的第一个索引值中的陈字大，但是比首节点的第二个索引值中的周字小，所以选择去节点2继续查询；
节点 2 查询比较：节点2的第一个索引值中的陈字的拼音大小比林字小，所以继续看下一个索引值，发现节点2有与林字前缀匹配的索引值，于是就往叶子节点查询，即叶子节点4；
节点 4 查询比较：节点4的第一个索引值的前缀符合林字，于是就读取该行数据，接着继续往右匹配，直到匹配不到前缀为林的索引值。

如果使用 name like '%林' 方式来查询，因为查询的结果可能是「陈林、张林、周林」等之类的，所以不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。

对索引使用函数

有时候我们会用一些 MySQL 自带的函数来得到我们想要的结果，这时候要注意了，如果查询条件中对索引字段使用函数，就会导致索引失效。

为什么对索引使用函数，就无法走索引了呢？

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。

不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。

对索引进行表达式计算

在查询条件中对索引进行表达式计算，也是无法走索引的。

为什么对索引进行表达式计算，就无法走索引了呢？

原因跟对索引使用函数差不多。

因为索引保存的是索引字段的原始值，而不是 id + 1 表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

对索引隐式类型转换

如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，你会在执行计划的结果发现这条语句会走全表扫描。

MySQL 的数据类型转换规则是什么？是会将字符串转成数字处理，还是将数字转换成字符串处理。

《MySQL45讲》里有一个简单的测试方式，就是通过 select “10” > 9 的结果来知道MySQL 的数据类型转换规则是什么：

如果规则是 MySQL 会将自动「字符串」转换成「数字」，就相当于 select 10 > 9，这个就是数字比较，所以结果应该是 1；
如果规则是 MySQL 会将自动「数字」转换成「字符串」，就相当于 select “10” > “9”，这个是字符串比较，字符串比较大小是逐位从高位到低位逐个比较（按ascii码），那么”10”字符串相当于 “1”和“0”字符的组合，所以先是拿 “1” 字符和 “9” 字符比较，因为 “1” 字符比 “9” 字符小，所以结果应该是 0。

在 MySQL 中，执行的结果如下图：

上面的结果为 1，说明 MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。

所以，在索引字段为字符串类型，比较字段为数字类型时，MySQL会对索引使用函数实现隐式类型转换，从而导致索引失效。

联合索引非最左匹配

对主键字段建立的索引叫做聚簇索引，对普通字段建立的索引叫做二级索引。

那么多个普通字段组合在一起创建的索引就叫做联合索引，也叫组合索引。

创建联合索引时，我们需要注意创建时的顺序问题，因为联合索引 (a, b, c) 和 (c, b, a) 在使用的时候会存在差别。

联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

where b=2；
where c=3；
where b=2 and c=3；

有一个比较特殊的查询条件：where a = 1 and c = 3 ，符合最左匹配吗？

这种其实严格意义上来说是属于索引截断，不同版本处理方式也不一样。

MySQL 5.5 的话，前面 a 会走索引，在联合索引找到主键值后，开始回表，到主键索引读取数据行，Server 层从存储引擎层获取到数据行后，然后在 Server 层再比对 c 字段的值。

从 MySQL 5.6 之后，有一个索引下推功能，可以在存储引擎层进行索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，再返还给 Server 层，从而减少回表次数。

索引下推的大概原理是：截断的字段不会在 Server 层进行条件判断，而是会被下推到「存储引擎层」进行条件判断（因为 c 字段的值是在 (a, b, c) 联合索引里的），然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据，无需再回表读取数据来进行判断，减少回表次数，从而提升了性能。

为什么联合索引不遵循最左匹配原则就会失效？

原因是，在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。

也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。

WHERE 字句中地 OR

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

这是因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

精简版

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
当我们在查询条件中对索引列使用函数，就会导致索引失效。
当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。
MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

百万/千万级数据库如何优化？

百万级、千万级数据处理，个人认为核心关键在于数据存储方案设计，存储方案设计的是否合理，直接影响到数据CRUD操作。总体设计可以考虑一下几个方面进行设计考虑：

数据存储结构设计
索引设计
数据主键设计
查询方案设计

由于本人也没有这个实力去真正实操百万千万级别地数据库，所以更到还是纸上谈兵，图个乐就行了。

百万级

数据存储结构设计

表字段 not null，因为 null 值很难查询优化且占用额外的索引空间，推荐默认数字 0。
数据状态类型的字段，比如 status, type 等等，尽量不要定义负数，如 -1。因为这样可以加上 UNSIGNED，数值容量就会扩大一倍。
可以的话用 TINYINT、SMALLINT 等代替 INT，尽量不使用 BIGINT，因为占的空间更小。
字符串类型的字段会比数字类型占的空间更大，所以尽量用整型代替字符串，很多场景是可以通过编码逻辑来实现用整型代替的。
字符串类型长度不要随意设置，保证满足业务的前提下尽量小。
用整型来存 IP。
单表不要有太多字段，建议在20以内。
为能预见的字段提前预留，因为数据量越大，修改数据结构越耗时。

索引设计

索引，空间换时间的优化策略，基本上根据业务需求设计好索引，足以应付百万级的数据量，养成使用 explain 的习惯，关于 explain 也可以访问：explain 让你的 sql 写的更踏实了解更多。
一个常识：索引并不是越多越好，索引是会降低数据写入性能的。
索引字段长度尽量短，这样能够节省大量索引空间；
取消外键，可交由程序来约束，性能更好。
复合索引的匹配最左列规则，索引的顺序和查询条件保持一致，尽量去除没必要的单列索引。
值分布较少的字段（不重复的较少）不适合建索引，比如像性别这种只有两三个值的情况字段建立索引意义不大。
需要排序的字段建议加上索引，因为索引是会排序的，能提高查询性能。
字符串字段使用前缀索引，不使用全字段索引，可大幅减小索引空间。

查询语句优化

尽量使用短查询替代复杂的内联查询。
查询不使用 select *，尽量查询带索引的字段，避免回表。
尽量使用 limit 对查询数量进行限制。
查询字段尽量落在索引上，尤其是复合索引，更需要注意最左前缀匹配。
拆分大的 delete / insert 操作，一方面会锁表，影响其他业务操作，还有一方面是 MySQL 对 sql 长度也是有限制的。
不建议使用 MySQL 的函数，计算等，可先由程序处理，从上面提的一些点会发现，能交由程序处理的尽量不要把压力转至数据库上。因为多数的服务器性能瓶颈都在数据库上。
查询 count，性能：count(1) = count(*) > count(主键) > count(其他字段)。
查询操作符能用 between 则不用 in，能用 in 则不用 or。
避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符，因为这些查询无法使用索引。
sql 尽量简单，少用 join，不建议两个 join 以上。

千万级

数据存储结构设计

到了这个阶段的数据量，数据本身已经有很大的价值了，数据除了满足常规业务需求外，还会有一些数据分析的需求。而这个时候数据可变动性不高，基本上不会考虑修改原有结构，一般会考虑从分区，分表，分库三方面做优化：
分区
分区是根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分，是一种水平划分。对应用来说是完全透明的，不影响应用的业务逻辑，即不用修改代码。因此能存更多的数据，查询，删除也支持按分区来操作，从而达到优化的目的。如果有考虑分区，可以提前做准备，避免下列一些限制：

一个表最多只能有1024个分区（6之后支持8192个分区）。但你实际操作的时候，最好不要一次性打开超过100 个分区，因为打开分区也是有时间损耗的。
如果分区字段中有主键或者唯一索引列，那么所有主键列和唯一索引列都必须包含进来，如果表中有主键或唯一索引，那么分区键必须是主键或唯一索引。
分区表中无法使用外键约束。
NULL值会使分区过滤无效，这样会被放入默认的分区里，请千万不要让分区字段出现NULL。
所有分区必须使用相同的存储引擎。

分表
分表分水平分表和垂直分表。
水平分表即拆分成数据结构相同的各个小表，如拆分成 table1, table2…，从而缓解数据库读写压力。
垂直分表即将一些字段分出去形成一个新表，各个表数据结构不相同，可以优化高并发下锁表的情况。
可想而知，分表的话，程序的逻辑是需要做修改的，所以，一般是在项目初期时，预见到大数据量的情况，才会考虑分表。后期阶段不建议分表，成本很大。
分库
分库一般是主从模式，一个数据库服务器主节点复制到一个或多个从节点多个数据库，主库负责写操作，从库负责读操作，从而达到主从分离，高可用，数据备份等优化目的。
当然，主从模式也会有一些缺陷，主从同步延迟，binlog 文件太大导致的问题等等，这里不细讲（笔者也学不动了）。
其他
冷热表隔离。对于历史的数据，查询和使用的人数少的情况，可以移入另一个冷数据库里，只提供查询用，来缓解热表数据量大的情况。

数据库表主键设计

数据库主键设计，个人推荐带有时间属性的自增长数字ID。（分布式自增长ID生成算法）
雪花算法
百度分布式ID算法
美团分布式ID算法
为什么要使用这些算法呢，这个与MySQL数据存储结构有关

从业务上来说

在设计数据库时不需要费尽心思去考虑设置哪个字段为主键。然后是这些字段只是理论上是唯一的，例如使用图书编号为主键，这个图书编号只是理论上来说是唯一的，但实践中可能会出现重复的情况。所以还是设置一个与业务无关的自增ID作为主键，然后增加一个图书编号的唯一性约束。

从技术上来说

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。总的来说就是可以提高查询和插入的性能。
对InnoDB来说主键索引既存储索引值，又在叶子节点中存储行的数据，也就是说数据文件本身就是按照b+树方式存放数据的。
如果没有定义主键，则会使用非空的UNIQUE键做主键 ; 如果没有非空的UNIQUE键，则系统生成一个6字节的rowid做主键;聚簇索引中，N行形成一个页（一页通常大小为16K)。如果碰到不规则数据插入时，为了保持B+树的平衡，会造成频繁的页分裂和页旋转，插入速度比较慢。所以聚簇索引的主键值应尽量是连续增长的值，而不是随机值(不要用随机字符串或UUID)。
故对于InnoDB的主键，尽量用整型，而且是递增的整型。这样在存储/查询上都是非常高效的。