解决程序慢，要学会预测表容积，不能一味地加索引

CSDN技术头条

发布于 2018-07-30 11:19:56

1.1K0

发布于 2018-07-30 11:19:56

文章被收录于专栏：CSDN技术头条

索引是应用程序设计和开发的一个重要方面。如果索引过多，应用程序中的更新、删除等操作会变慢，性能会受到影响；如果索引过少，对查询性能又会产生影响。

要找到的一个平衡点，这对应用的性能很重要。

一些研发人员总是认为 SQL 加下索引就好，所以总是在事后才发现系统慢了，就添加索引，其实这是源于一种错误的思维。

如果从系统设计之初，应该想到哪些地方需要添加索引，能预测表容量增长和未来一年的业务情况。

研发人员对于数据库的工作往往停留在应用的层面，比如编写 SQL 语句、存储过程之类，他们不会关心索引，一是认为现有系统不糊表增长过大，二是这是 DBA 的事，后续让 DBA 加上。

而 DBA 往往不了解业务的数据源，添加索引需要通过监控大量的 SQL 语句，从中找到问题。

这个时间的消耗和评估，必然大于初始添加索引所需要的时间，并且可能会遗漏一部分索引。

当然也不是索引越多越好，笔者公司的订单表，就长达数 10 个索引。

曾经导致业务更新此表时，缓慢，后续优化精简了几个索引，合并了联合索引，速度提升挺明显。

因此索引的添加要基于原理和业务情况，做整体考虑，不是一蹴而就的。

准备

在介绍索引之前，我们需要先了解一下没有索引的时候如何查找数据。

为了方便理解，我们如下有那个等值搜索条件为对一个列精确匹配的情况，所谓精确匹配就是如下：

select column from table where cloumn=xxx；

在一个页中的查找

假如目前表中的记录比较少，所有的记录都可以存放到一个页中，在查找数据的时候可以根据搜索条件的不同分为两种情况：

主键为搜索条件

这个查找过程基本很明显，可以直接在页目录中使用二分查找法快速定位到对应的位置，然而再遍历该位置对应分组中的数据便可以快速找到指定的数据。

其他列搜索条件

对非主键列的查找的过程可就不这么幸运了，因为在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。

这种情况下只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。很显然，这种查找的效率是非常低的。

多页中查找

大多数情况下，表中的存放记录都是非常多的，需要较多的数据页存放这些记录。在很多页中查找记录的话氛围如下：

1. 定位到记录所在的页。

2. 从定位到的页中查找对应的记录。

不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找。

在每一个页中根据我们上边阐述过的查找方式去查找指定的记录。

因为要遍历所有的数据页，所以这种方式显然是超级耗时的，如果一个表有一亿条记录，使用这种方式去查找记录会长时间占用 db 资源，更会导致应用程序不断超时，业务卡顿。

所以这个时候索引就该出场了。

innodb 存储引擎索引

innodb 存储引擎支持的 2 种索引 b+ 树索引和 hash 索引。

然而 innodb 存储引擎的 hash 索引是自适应的，innodb 会根据表的使用情况自动自动为表生成 hash 索引，不能干预是否在一种表中生成哈希索引。

b+ 树索引是就是传统意义上的索引，这是目前关系型数据库最常用、最有效的索引。b+ 树的索引构造类比与二叉树，根据键值快速找到数据。

mysql 里的 btree，其实是 b+tre 不是 b 树（b - 树）。

二叉树

二叉树具备如下特点：

二叉树的每个节点至多只有 2 课子树（不存在度大于 2 的节点）
二叉树的子树有左右有序的之分，不能颠倒
一些概念：B Tree，B 树，B-Tree，B - 树

B- 树很容易和 Binary Tree（二叉树）混淆。

B- 树：

平衡的多叉树，不仅是二叉树，概括来说是一个节点可以拥有多于 2 个子节点的多叉查找树。

与自平衡二叉查找树不同，B- 树为系统最优化大块数据的读和写操作。

B-tree 算法减少定位记录时所经历的中间过程，从而加快存取速度。普遍运用在数据库和文件系统。

一棵 m 阶（比如 4 阶）的 B 树满足下列条件：

树中每个节点至多有 m 个（4 个）子节点；
除根节点和叶子节点外，其它每个节点至少有 m/2 个（2 个）子节点；
若根节点不是叶子节点，则至少有 2 个子节点；
所有叶子节点都出现在同一层，叶子节点不包含任何关键字信息；
有 k 个子节点的非终端节点 (叶子节点) 恰好包含有 k-1 个关键字。

B- 树的搜索：

从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；
重复，直到所对应的儿子指针为空，或已经是叶子结点。

B- 树的特性：

关键字集合分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
其搜索性能等价于在关键字全集内做一次二分查找；
自动层次控制；

mysql 的索引是 b + 树实现，是 B 树（B Tree）的变体，也是一种多路搜索树。

其定义基本与 B - 树同，除了：
非叶子结点的子树指针与关键字个数相同；
非叶子结点的子树指针 P[i]，指向关键字值属于 [K[i], K[i+1]) 的子树（B - 树是开区间）；
为所有叶子结点增加一个链指针；
所有关键字都在叶子结点出现；

关键区别是：

B + 树只有达到叶子结点才命中（B - 树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；
B + 树每个叶子节点都有双向指针；
B 树分支节点和叶节点均保存记录的关键码和记录的指针；B + 树分支节点只保存记录关键码的复制，无记录指针。所有记录都集中在叶节点一层，并且叶节点可以构成一维线性表，便于连续访问和范围查询。

核心概念

b + 树的索引其本质是 b + 树在数据库中的实现，但是 b + 树索引在数据库中有一个特点就是其高扇区性。

所以在数据库中 b + 树的高度一般都在 2-3 层，也就是对于查找一个值，最多需要 2-3 次 io。

再就上一次字段列回表 io，假如一般磁盘每秒至少可以做到 100 次 io，2-3 次的 io，意味着查询只需要 0.02s-0.03s。

这个时间对于应用程序而言是微乎其微的，所以为啥更新语句最好基于主键更新，如：

update car set status=2 where id=1;

数据库中的 b + 树索引分为聚集索引和辅助聚集索引，但不管是那种方式，其内部实现都是 b + 树实现。

所以为啥大公司面试问索引底层其实就是问你 b+s 树实现，也就是间接考察你的数据结构和常用算法。

b + 树内部都是高度平衡的，叶子节点存放着所有数据。

聚集索引和非聚集索引最大的不同，叶子节点是否存储的是一整行的信息。

聚集索引

由于 innodb 是索引组织表，表中数据按照主键顺序存放。

而聚集索引就是按照每张表的主键创造一颗 b + 树，并且页子节点存放着整行的信息，也就是可以把聚集索引的叶节点成为数据页。

聚集索引的这个特性决定索引组织表中数据也是索引的一部分。

同 b + 树结构一样，每个数据页都是通过双向链表来连接。这也是为啥查询时间快的原因，算法时间复杂度都低。

聚集索引的好处，它基于主键的排序查找和范围查找速度非常快。

叶节点的数据就是我们需要查询的数据，比如我们需要查询注册用户的最新 10 位。

由于 b + 树索引是双向链表的，我们可以快速找到定位最后一个数据页，也可以用 explain 分析：

explain select ID，name from user order by id desc limit 10\g

另外一个是范围查找，如果要查找主键一个范围内的数据，通过页节点上层中间节点就可以得到页的范围，之后直接读取数据页。

辅助索引

对于辅助索引，也就是非聚集索引，叶级别不包含行的全部数据。

叶节点除来包含主键，另外每个叶级别中的索引行中包含一个标记，该标记就是告诉存储引擎，哪里可以找到与索引行对应的行数据。

因此辅助索引的标记就是相应行的聚集索引键。也可以等同于 c 语言中的指针，真正的值是通过地址编号去获取。

辅助索引的存在并不影响数据在聚集索引中的组织，因此一个表可以有多个辅助索引。

当通过辅助索引来查找数据时，存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后通过主键索引找到行的记录。

比如，在一棵高度为 3 的辅助索引中查找数据，那么需要对这颗树遍历三次才可以找到指定主键。

如果聚集索引树的高度也是 3，那么还需要对聚集索引进行三次查找，才能最终找到一个完整行数据所在的页，因此一共需要 6 次 io 来访问最终的一个数据页。

可能根据上述阐述，有人会问既然辅助索引比聚集索引的 io 多，为何还有存在的必要？

从上述特性可以知道，一个表中聚集索引占用的空间时很大的，因为它存储了全部数据，而辅助索引，是建立在一些列需要经常查询上。

除这些列外，剩下就是用来回表的指针信息，所以相对而言，辅助索引的占用空间比聚集索引小很多，特别是在一个表中的列数很多或是这些列中有大字段时，因为一般不会在大字段上建立索引。

因此比如 select count(*) from user; 语句，一些优化器就会选择表中最小的索引来作为统计的目标索引，因为它占用空间最小，IO 也会最小，性能相应的更快一些。

哈希索引

hash index 是建立在哈希表的基础上，它只对使用了索引中的每一列的精确查找有用。

对于每一行，存储引擎计算出了被索引的哈希码（Hash Code），它是一个较小的值，并且有可能和其他行的哈希码不同。

它把哈希码保存在索引中，并且保存了一个指向哈希表中的每一行的指针。

在 mysql 中，只有 memory/headp 存储引擎支持哈希索引。

哈希索引和 B + 树索引区别：

hash 索引查找数据基本上能一次定位数据，当然有大量碰撞的话性能也会下降。而 btree 索引就得在节点上挨着查找了，很明显在数据精确查找方面 hash 索引的效率是要高于 btree 的；
hash 索引不支持 like 查询。

索引的建议和类型

索引的建议

经常检索的列
经常用于表连接的列
经常排序 / 分组的列

对于上述这些条件，适合加索引。

然而，索引也有不应该建立的规则：

基数很低的列，比如男女、订单状态等
更新频繁检索不频繁的列
blob/text 等长内容的列
很少用于检索的列

oracle、mysql 都有哪些索引

逻辑上：

物理上：

B-tree：

索引结构：

b-tree
bitmap

想对比 mysql 都有哪些索引呢？

逻辑上：

物理上：

索引结构：

b-tree
hash 索引
全文索引
spatial index

其实为啥对比这两个数据库呢，其实如今 mysql 的发展趋势，innodb 就是小型的 oralce 的 iot 表。

对于 primary key 之外的索引，都是辅助索引，称为 SECONDARY KEY。

主键

innodb 主键特点：

索引定义时，如果不显示指定主键，会隐式加入主键值
索引定义时，如果显示指定主键，会加入主键值
在 5.6.9 后，优化器可以自动识别索引末尾的主键值，在这之前需要显示指定
最多只能有一个主键
主键值不能重复
加快数据的操作速度

笔者系统中早初有些业务表，木有主键，后期表超过几千万时，临时半夜加上索引，占用空间迅速降低。

主键设计建议：

对业务透明，不受业务变化的影响；
主键要很少修改和删除，对比笔者公司业务，表主键不会存在删除，数据都需要保留；
主键最好是自增的；
不要具有动态属性，比如最后修改时间。

innodb 引擎索引选择顺序：

显示申明的主键
第一个不包含 null 值的唯一列索引
内置的 rowid

聚集索引

聚集索引的概述已经阐述。

特点

每张表只能建一个聚集索引，日志型存储引擎 tukodb 除外；
innodb 中，聚集索引就是表，表就是聚集索引；
myisam 没有聚集索引的概念。

聚集索引的优先选择列

含有大量非重复值的列
使用 between，》或《返回一个范围值的列
被连续（顺序）访问的列
返回大量结果集的查询
经常被 join 的列

不建议的聚集索引列

修改频繁的列
唯一值很小的列
新增内容太过离散随机的列

索引的一些附加规范

mysql 不支持多列使用不同顺序

只能同时使用一种顺序，如：

alter table x add index (a , b)

能小类型别用大类型字段

一道思考题的总结：

基本相同的情况下（两个表都有自增列做主键，而且新数据都是顺序写入，相当于顺序存储），MyISAM 和 InnoDB 的全表随机逻辑扫描一遍（SELECT * FROM TABLE WHERE PKID = 随机 ID），哪个更快些？数据量是一亿？效果：MyISAM 耗时是 InnoDB 的 1.06 倍，InnoDB 耗时是 MyISAM 的 94%。

索引的性能分析和优化

如果系统中发现慢 SQL 或者性能影响业务的 sql，可以通过 EXPLAIN 来判断 SQL 的执行计划。

EXPLAIN [EXTENDED] SELECT…

查看执行计划会有如下信息：

id：1select_type：simple
table：t
possible_keys：primary
key：primary
key_len：4ref：constrows：1filtered：100.00extra：using index

关于 key_len 长度计算公式：

varchr(10) 变长字段且允许 NULL : 10_(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(10) 变长字段且不允许 NULL : 10_(Character Set：utf8=3,gbk=2,latin1=1)+2(变长字段)char(10) 固定字段且允许 NULL : 10_(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)char(10) 固定字段且不允许 NULL : 10_(Character Set：utf8=3,gbk=2,latin1=1)

也就是说索引 key_len 长度过大，也会影响 SQL 性能。所以为什么也不能默认 null，会占用字节，索引长度哟。

索引提高 SQL 效率的方法

利用索引加快查询速度
行记录检索
从索引记录中直接返回结果（联合索引）

min（）、max（）
order bygroup bydistinct

如果列定义为 DEFAULT NULL 时，NULL 值也会有索引，存放在索引树的最前端部分。

案例 1：

CREATE TABLE `test` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`c1` int(11) NOT NULL DEFAULT '0',`c2` int(10) unsigned DEFAULT NULL,`c5` int(10) unsigned NOT NULL DEFAULT '0',`c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`c4` varchar(200) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),KEY `idx_c1` (`c1`),KEY `key_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=2686347;

表特殊说明：

260 万行记录，c1、c2、c5 三个列值完全一样，但定义不一样：

c1 列定义为 NOT NULL DEFAULT 0，有索引
c2 列定义为 DEFAULT NULL，有索引
c5 列定义为 NOT NULL DEFAULT 0，无索引

mysql>explain select c1 from test where c1 = 12345 limit 1;

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| 1 | SIMPLE | test | ref | idx_c1 | idx_c1 | 4 | const | 32 | Using index |+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

对比一下：

mysql>explain select c5 from test where c5 = 12345 limit 1;

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 2568267 | Using where |+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

统计类业务：

mysql>explain select max(c2) from test;

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

求平均值，有索引时，扫描索引即可，无需全表扫描（避免回表）

mysql>explain select avg(c1) from test;

+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+
| 1 | SIMPLE | test | index | NULL | idx_c1 | 4 | NULL | 2568267 | Using index |+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+

利用索引提高排序效率

mysql>explain select c5 from test where c5 > 100 order by c5 limit 10;

+----+-------------+-------+------+---------------+------+---------+------+---------+-----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-----------------------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 2568267 | Using where; Using filesort |+----+-------------+-------+------+---------------+------+---------+------+---------+-----------------------------+

有索引，可以快速排序完成

mysql>explain select c5 from test where c1 > 100 order by c1 limit 10;

+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+
| 1 | SIMPLE | test | range | idx_c1 | idx_c1 | 4 | NULL | 1284133 | Using where |+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+

读取的列改成 c1

mysql> explain select c1 from test where c1 > 100 order by c1 limit 10;

+----+-------------+-------+-------+---------------+--------+---------+------+---------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+---------+--------------------------+
| 1 | SIMPLE | test | range | idx_c1 | idx_c1 | 4 | NULL | 1284133 | Using where; Using index |+----+-------------+-------+-------+---------------+--------+---------+------+---------+--------------------------+

mysql>explain select * from test t1 left join test t2 using (c1);
+----+-------------+-------+------+---------------+--------+---------+------------+---------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+------------+---------+-------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2568267 | || 1 | SIMPLE | t2 | ref | idx_c1 | idx_c1 | 4 | t1.c1 | 11 | |
+----+-------------+-------+------+---------------+--------+---------+------------+---------+-------+

mysql> explain select * from test t1 left join test t2 using (c5);
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+---------+-------+| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2568267 | |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2568267 | |+----+-------------+-------+------+---------------+------+---------+------+---------+-------+

28250937 VS 6595995383289，差了 233478 倍，再次表明不同的执行计划，性能差距很大呀。

NOT NULL 和 DEFAULT NULL 的区别

mysql>desc select count(c1) from test;
+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+
| 1 | SIMPLE | test | index | NULL | idx_c1 | 4 | NULL | 2568267 | Using index |+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+

mysql> desc select count(c2) from test;
+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+
| 1 | SIMPLE | test | index | NULL | key_c2 | 5 | NULL | 2568267 | Using index |+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+

mysql> desc select count(c1) from test where c1 is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

mysql> desc select count(c2) from test where c2 is null;
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 | SIMPLE | test | ref | key_c2 | key_c2 | 5 | const | 1 | Using where; Using index |+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+

利用 index merge - Using union

mysql> desc select * from test where c1 = 1234 or c2 = 4567 ;
+----+-------------+-------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
| 1 | SIMPLE | test | index_merge | idx_c1,key_c2 | idx_c1,key_c2 | 4,5 | NULL | 64 | Using union(idx_c1,key_c2); Using where |+----+-------------+-------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+

案例 2:

****
_emphasized text_
_测试索引影写入效率_

create table test (
id int unsigned not null auto_increment,
c1 int not null default '0',
c2 int not null default '0',
c3 int not null default '0',
c4 int not null default '0',
c5 timestamp not null,
c6 varchar(200) not null default '',
primary key(`id`),
KEY `idx_c2`(`c2`),
key `idx_c3`(`c3`)
);

-- 测试有无索引对比写入效率存储过程
delimiter $$$
CREATE PROCEDURE `insert_test`(in row_num int)
begin
declare i int default 1;while i <= row_num doinsert into test(id, c1, c2, c3,c4, c5,c6) values(i, floor(rand()_row_num),floor(rand()_row_num),floor(rand()_row_num),floor(rand()_row_num),now(), repeat('wubx', floor(rand()*20)));
set i = i+1;
END while;
end $$$

客户端调用：call insert_test (500000);

单纯插入初始化数据：