程序地带

浅谈sql索引


索引是什么

假如你手上有一个你公司的客户表,老板说找什么客户你就得帮他找出来。


客户不多的时候,你拿着手指一行一行滑,费不了多少时间就能找到。


后来公司做大了,客户越来越多,好几页的客户,你发现,一行一行滑真的好累啊,最主要找慢了还得挨老板叼。


他妈的,吃力不讨好。


那咋办?


我相信这么聪明的你不会坐以待毙的。


你可能会自己做一些记录,比如拿个小本本写上,


28岁的客户在第一页
29岁的客户在第二页


或者


姓张的客户在第二页
姓李的客户在第三页和第四页


当然这些要根据那张客户表的实际情况来。


这样子,下次老板叫你找29岁的客户,你就一下子翻到第二页,一下子就找到了,轻松又漂亮地解决了问题。


这么机智地解决了问题,当上ceo,迎娶白富美就指日可待了。


好了,美好故事到此就结束了。


真实的情况是怎么样的呢?


真实的情况就是数据库就是故事中的你,你就是故事中的老板,故事中的小本本,就是咱们今天要讲的索引。


索引的特点

那么从这个故事中可以看出索引有什么特点呢?


为了提高查找效率而建立

如果你不给数据库加索引的话,多数情况下,它就真的是一行行找,效率极低。


数据量少的时候不需要索引

但数据量少的时候,也没必要建索引,你想想啊,数据量少的时候,你一下子就找到了,速度比你去翻小本本时间可能还要快点,就不要浪费一个小本本了。
MySQL的索引本质也是一张表的,建立索引也需要相应的空间。


索引是建立在表的数据上的

上面的故事里我也说了,小本本的内容要根据你表里的实际情况来的。
这样的话,如果建立了索引,就要注意两个点:


不要实际删除数据。
假如你有批客户闹掰了,你一生气,把客户表中那一整页都撕了。
那你下次按照【31岁的客户在第20页】这个规则去找,但是前面的就被你撕了,现在31岁的客户就提前了几页,你数到第20页,发现找不到,人都傻了。
MySQL也是这样的,如果删除数据,会导致按照索引查找的数据不会在原先的位置上。


频繁更新的字段不要建立索引。
假设用户的年龄天天变,那最好也不要记在小本本上了,否则你每天都要去更新小本本,今天是【31岁的客户在第20页】,明天就要改成【32岁的客户在第20页】了。
MySQL也是这样的,如果建立索引的字段频繁更新,这样便会导致之前建立的索引需要频繁更新。


MySQL索引分类

人家MySQL建立索引的方式比我们记小本本的方式要聪明有效率地多了。
你可以看到我上面做小本本的方式都是根据表中的某一列来的,比如


【31岁的客户在第20页】这个是根据客户的年龄这一列来做的;
【姓李的客户在第三页和第四页】这个使用客户的名字这一列来做的。


在MySQL中,我们也只是需要告诉MySQL用哪些列来做索引即可,然后接下来的事他就会自己做。
咱们建立的索引呢,根据使用列的情况不同,可以分类如下:


单值索引:即一个索引只包含单个列。一个表可以有多个单列索引。


唯一索引:索引列的值必须唯一,但允许有空值。


复合索引:即一个索引包含多个列。


假如现在有一个people表,内有字段id(主键不需要做索引),name,age,phone_number(电话号码)那么:


单值索引:可以单独用name或age做一个索引,任何一个字段都可以。这样的索引可以做多个。
唯一索引:和单值索引一样,但做索引的该字段必须唯一,比如你确定people表中phone_number的值唯一的话,那么便可以在上面建立唯一索引。
复合索引:可以用(name,age)或(age,phone_number)或(name,age,phone_number)做一个索引。

建议:建立复合索引,且一个表不要超过5个索引。


基本语法

创建(如果加上UNIQUE则创建唯一索引):


CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));

ALTER mytable ADD [UNIQUE] INDEX[indexName] ON (columnname(length));


删除:


DROP INDEX [indexName]ON mytable;


查看:


SHOW INDEX FROM tableG


MySQL索引结构

上面的索引建立好后,MySQL是按照什么样的策略去查找数据的呢。
有几种结构,下面讲的是比较常用的BTree结构。



图片介绍:


如图一颗B+树,浅蓝色表示磁盘块,每个磁盘块包括几个数据项(深蓝色)和指针(黄色)。


如磁盘块1包括数据项17和35,包含指针P1、P2、P3;P1表示小于17的磁盘块,P2表示在17-35之间的磁盘块,P3表示大于35的磁盘块。


真实的数据只存在于叶子节点,非叶子节点不存储真实数据,只存储指引搜索方向的数据项。


如17、35并不真实存在数据表中。


查找过程(以上图查找数据项29):


首先把磁盘块1由磁盘加载到内存,此时发生一次IO;在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,因为内存时间非常短(相比磁盘的IO)可以忽略不计。


将磁盘块1的P2指向的磁盘块3由磁盘加载到内存,发生第二次IO;确定29在26和30之间,指向磁盘块3的P2指针。


将磁盘块3的P2指针指向的磁盘块8加载到内存,发生第三次IO,同时内存中做二分查找找到29。


查询结束,总计三次IO。


真实的情况是:3层的B+树可以表示上百万的数据,如果上百万的数据查找只需要3次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要上百万次IO。


总结:减少IO次数可以减少查询时间,提高性能,那么怎么减少IO次数?
答案:增加树的广度而非深度。B+树的叶子节点可以多。


建立索引的时机
哪些情况需要创建索引
主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段,外键关系建立索引
频繁更新的字段不适合创建索引 -- 因为每次更新不只更新记录还会更新索引
Where里用不到的字段的不创建索引
单键/组合索引的选择问题 -- 在高并发下倾向创建组合索引
查询中排序的字段 -- 排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段
哪些情况不需要创建索引
表记录太少 -- mysql300w左右就可以考虑建索引了
经常增删改的表 -- 因为索引要跟着更新
数据重复且分布平均的表字段 -- 可以用(该字段不同的数据的数量)/(该字段总的数据量),值越接近1,说明不怎么重复,越有建索引的价值。
最后

其实索引只是一个优化数据库查询速度的一个工具,数据量少的时候完全可以不用,​他也有缺点,一旦你对数据表有修改,那么相应的索引也要做修改操作。


而且关于索引,难点在于针对一个具体的表去做出最合适的索引,这不只要看你表里有什么内容,更多要看你的业务,你的业务会经常根据哪些关键词查找,这个更考验一个人的能力吧。


最后祝各位​,早日当上CEO,迎娶白富美​。​


版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://www.cnblogs.com/tandk-blog/p/14283455.html

随机推荐

pythonqmks201906_201906

https://blog.csdn.net/g11d111/article/details/80896137​blog.csdn.net20190531:1.collections.Or...

weixin_39621860 阅读(175)

java主类与源代码名称_第一个Java程序

java主类与源代码名称_第一个Java程序

配置环境变量配置JAVA_HOME配置classpath 告诉java指令到哪里去找字节码javac指令可以跨目录去编译  编译其他目录下源代码java不能跨目录去解释执行代码一、编写一个java程序...

小方有点小方 阅读(889)

Spring Boot简介

Spring Boot简介

使用SpringBoot快速搭建第一个项目快速入门,你就说一个字:香SpringBoot介绍#SpringBoot不是一个新的技术,而是简化Spring应用的...

Kevinnsm 阅读(240)