索引总结-CSDN博客

                        这是数据库索引相关内容的最后一篇
复制代码

对于单表，我们知道了如何设计《最佳索引》;

但是现实中，往往比较复杂，有联合查询、嵌套查询、子查询等等；我们从以下几方面展开

对优化器来说难的谓词
跨表查询
设计出色索引的步骤

1. 对优化器来说难的谓词

我们来猜一猜，优化器比较难处理的谓词可能有哪些？

肯定要排除AND，这足够简单；大于/小于呢，单纯来看也还行；between and呢；有范围，应该能接受，那还有什么？
比如Or,这明显对优化器有点难了，为什么，因为它是不满足A，还得看B，等根据查询结果来进行判断需不需要看B；
还有LIKE, 如果LIKE以%开头，那么无疑给查询带来了难度；
复制代码

所以呢，难的谓词有什么？

对，就是or、like、in这些，没有办法通过简单的扫描得出结论的，不建议在这些列建立索引。
复制代码

这些谓词，我们有个统一的称呼，称呼他们为非布尔谓词。那么相反，
简单直接的谓词就是布尔谓词，就是好的谓词，可以建立索引

2. 跨表查询

跨表查询总结起来就是自个表的部分，以及连接处.

1.连接处是否在索引中很重要
2.就算每个单表都是最佳索引，但是如果有大量随机IO，仍然会导致查询慢

select CNO, CNAME FROM TABLE1 WHERE CTYPE = 1;
select DNAME FROM TABLE2 WHERE DNO = CNO AND DCOM = 2;

对于TABLE1,最佳索引为（CTYPE, CNO, CNAME）；
对于TABLE2,最佳索引为（DNO, DCOM, DNAME）;

乍一看，如果这两表联合查询，应该没啥问题，通过CNO链接，CNO也在索引中；

实际呢，在TABLE1中，CTYPE=1查出来的CNO，可能不止1个，假设1w个；那么对于没个CNO，都会触发TABLE2进行随机访问
我们可以粗略大概计算一下LRT

LRT = 10ms + 1w*0.01ms + 1w * 10ms

对于TABLE1,数据全部在索引中，访问第一条索引发生了一次随机访问，剩余索引为顺序访问，所以是10ms + 1w*0.01ms
对于TABLE2,其访问顺序取决于TABLE1得到的CNO，CTYPE=1的情况下,CNO顺序存储的概率不大，我们假设他们是分散存储的，那么每次FETCH CNO，就会导致一次随机访问索引，大概有1W*10ms的时长

最终，查询速度并不理想，虽然每个表都是最佳索引，问题在哪？在于跨表查询导致的随机访问；
复制代码

在上述情况下，如何快速提升速度，如果在设计阶段，不妨考虑将两表合并，或者将CTYPE,也放入到TABLE2中；

往往大家特别排斥冗余数据，但是从效率的角度出发，冗余数据真的不好吗，它的维护成本和效率成本有没有好好比较一下呢？

当然如果实际运行中数据查询并不慢，并不需要纠结这些；如果能够通过宽索引或者半宽索引解决，就最好不要推翻或追加。

3. 设计出色索引的步骤

1.当表结构设计出来时，就该开始索引的设计
2.用BQ或者QUBE进行索引设计检查，如果还是没办法满足需求，就要考虑合并表，减少跨表
3.根据应用需要尝试继续添加必要的索引
4.如果表增删改的频率很高（50次/秒），那需要用QUBE评估下最多容许添加多少索引
5.根据具体的数据库调整策略
6.SQL编写以后，继续使用BQ、QUBE、BJQ等方式进行检查
7.发布后，使用EXPLAIN进行检查
8.跟踪报告
复制代码

                                其他相关章节
复制代码

数据库索引相关文章之一：《B树，一点都不神秘》
数据库索引相关文章之二：《B树很简单,插入so easy》
数据库索引相关文章之三：《索引》
数据库索引相关文章之四：《什么索引算是好的索引》
数据库索引相关文章之五：《如何发现及替换不合适的索引》
数据库索引相关文章之六：《索引总结》