一 一道面试题
面试官:你造sql优化吗?
小码农:知道
面试官:谈谈吧。
小码农:好的。(心想:呵呵,这难不倒我,刚看过,熟得很...)
1 对查询进行优化,首先应考虑在 where 及 order by 涉及的列上建立索引。
2 应尽量避免在 where 子句中使用!=或<>操作符。
3 应尽量避免在 where 子句中使用 or 来连接条件。
4 应尽量避免在 where 子句中对字段进行表达式操作。
5 应尽量避免在where子句中对字段进行函数操作。
6 任何地方都不要使用 select * from t。
。。。
面试官:好的,为什么要这样呢?
小码农:主要是为了避免索引失效,导致全表扫描。
面试官:为什么这些操作会导致索引失效?
小码农:因为不走索引
面试官:为什么不走索引?
小码农:因为索引失效啊
面试官:我知道索引失效,我是问你为什么会出现索引失效?
小码农:因为它不走索引了,所以就会失效啊
面试官:。。。
入行编程不久的朋友对于sql优化的认知可能多半也仅限于上面的水平,对于底层的东西还是没有深入的理解,也是工作中确实基本用不到,平时做的无非就是建库建表、简单增删改查,数据量也不多,觉得没有必要研究那么多。但是想要往更高级进阶,一些底层技术还是要了解的。
二 索引的本质
想要回答上面的问题,得从索引的本质说起,很多我们平时看到的记忆的sql优化方法都是基于索引本质的。而谈到一门技术,不管是什么技术,我觉得首先要考虑的问题是这门技术为什么会产生,它是为解决什么问题的?
索引的目的:
以上是mysql官网关于索引介绍的第一段话,简单翻译:索引就是通过具体的列值快速查找到行记录。如果没有索引,mysql只能通过全表扫描查找相关数据。表越大,开销就越大。如果表建立了索引,mysql就能从数据文件中快速找到具体的位置,而不需要查看所有的数据。从而就比读每取一行数据快得多。
索引的本质:索引就是排好序的数据结构。
这么说有点抽象,我们举个栗子。。。
表里面有7行数据,我们要查找col2=23的一行数据
没有索引:需要从第一行、第二行、第三行、、、一次查找到第7行,需要查找7次,而每行数据在磁盘的位置不一定就是连续的。
有索引:通过col2创建一棵二叉树,每个节点存储col2的 值作为key,数据行在磁盘的地址为value,第一次查找到二叉树的根节点34,第二次查找到左节点22,第三次就可以找到23了,然后拿着地址去取数据就可以了。
这里先有这样的概念就可以了。其实mysql底层用的并不是二叉树,而是其他的,后面说。
三 索引的数据结构类型
上面是mysql官网关于索引的第二段话,简单翻译:大多数mysq索引(主键索引、唯一索引、全文索引)都存储在b树中,其他情况:空间数据类型用的是r树,memory表也支持hash索引,innodb用反向链表存储全文索引。
其实更具体地说,mysql用的是b树的改进版b+树。
通常来说,比较适合做索引的数据结构还有二叉树,红黑树,hash表,b树,那么为什么b+树会成为王者呢?
1 二叉树的问题:
还是以上面的7行数据为例,如果以col1作为索引,索引树将是什么样呢?
是不是有点眼熟?没错,跟链表没啥区别了。当数量很大,索引值连续单调分布,就没啥效率可言了,所以排除了二叉树。
2 红黑树的问题:
那换红黑树的话,索引树如何呢?
红黑树会比二叉树好一点,树没有那么高,但是当数据量很大的时候,高度还是不可控的。排除红黑树。
3 hash表的问题:
存储结构是这样的
当不发生hash碰撞的时候,一次hash运算就可以找到我们需要的数据了,快不快?快是快,但是有个很大的弊端,就是不能做范围查找,而我们在写sql的时候显然范围查询是经常会用到的。排除hash表。
4 b树的问题:
几点说明:1 b树的每个节点都是存储数据的
2 索引元素不重复
b树确实能在很大程度上降低树的高度,而这正是树形结构作为索引效率高低的关键,but,还不够完美,看看b+树就知道了。
5 b+树王者:
几点说明:1 非叶子节点只存索引,不存具体的行数据,完整的数据存在叶子节点,非叶子节点变小了,自然能存更多的索引,高度自然就更低了
2 叶子节点包含所有的索引字段
3 叶子节点用指针链接,提高了区间访问性能
对比一下是不是比b树更胜一筹呢。
四 索引失效怎么看
只要心中有 “树” ,判断索引是否失效,或者说sql走不走索引就通透多了,那就看根据sql的条件看看在索引树里面是不是有序的。
比如:select id from t where substring(name,1,3)='abc' --name以abc开头
本来索引字段name是根据name值排好顺序放入b+树的,截取name中的部分字符(mysql才不去判断你是截取开头、中间、还是后面)之后就没办法按正常的查找方法定位索引了,所以就不用走索引了。
但是如果改成select id from t where name like 'abc%' ,以确定的字符(abc)打头,在索引树里面是可以快速定位的,索引会走索引。
再比如:一个很常见的问题,为什么dba建议使用自增主键而不是uuid?
原因很简单,因为如果是自增主键,添加数据的时候只需要在索引树的最后面添加一个索引就可以了,但是如果是uuid,要保证插入的id在索引树里面的顺序就不是简单的加在后面了,还要先查找适合它的位置
再比如:联合索引失效的判断,以(name,age,address)为例
在索引树里面是按照,name,age,position的顺序排列的
按照name去找就是有序的,跳过name直接按age去找,或者跳过name直接按照pisition去找就不一定是有序的,所以跳过name就会导致索引失效
。。。
情况还有很多很多。总之,从索引树的角度去看索引失效问题才是正道,而不是死记,知其然不知其所以然。
学无止境,让学习成为一种习惯。
本人水平有限,有不对的地方请指教,谢谢。