住建城乡建设网站,长春网站建设培训,石家庄最近新闻事件,帮公司做网站赚钱吗文章目录 简介索引是万能的吗索引的种类有哪些#xff1f;普通/唯一/主键/全文索引聚集索引与非聚集索引单一索引与联合索引 总结参考文献 简介
索引在SQL优化中占了很大的比重#xff0c;甚至可以说#xff0c;对SQL的优化#xff0c;其实就是对索引的优化。
但是索引并… 文章目录 简介索引是万能的吗索引的种类有哪些普通/唯一/主键/全文索引聚集索引与非聚集索引单一索引与联合索引 总结参考文献 简介
索引在SQL优化中占了很大的比重甚至可以说对SQL的优化其实就是对索引的优化。
但是索引并不是万能的用好了索引确实可以提升SQL的查询效率甚至提升十倍以上。但是用错了索引也可能会降低查询的效率。
索引是万能的吗
什么是索引index呢
数据库中的索引就好比是一本书的目录可以帮我们快速进行特定值的定位查找从而提高整体查询的效率。
所以索引就是帮助数据库管理系统高效获取指定数据的一种数据结构。
如果不使用索引的话我要找一个特定值就必须从第一条数据开始一条一条扫描直到找到我需要的数据。可想而知的慢。
那既然如此我把所有字段都建上索引不就好了那整个表岂不是查啥都会很快
倒也不是。
首先如果你的数据行数比较少比如说还不到1000行这就不需要创建索引了可能直接遍历的速度还更快因为启用索引也是有时间开销的。
另外当数据重复度大的时候比如高于10%也可以不对这个字段进行索引。就像我们之前说过的性别字段如果需要在100w行数据里查找出那50w行性别为男的数据加上索引也不会变快因为索引里一般是只保存了主键即使快速定位到了这50w主键下一步还是要回表依次取出这50w数据。
那性别字段真的不适合创建索引吗
也不绝对。
假设有一个女儿国100w人里只有10个男性那么当我们要通过select * from user where gender1;来筛选出男性的记录时针对性别建了索引要比不建索引的效率快的多。
因此我们可以得出结论索引的价值在于快速定位少量数据。如果需要定位的数据有很多那么索引就失去了它的使用价值比如通常情况下的性别字段通常情况下的男or女 不是指老美的近百种性别。
我们不仅要看字段中的数值个数还要根据数值的分布情况来考虑是否创建索引。
索引的种类有哪些
从功能逻辑上来讲索引主要有4类分别是普通索引、唯一索引、主键索引和全文索引。
从物理实现方式上来区分索引可以分为2种聚集索引和非聚集索引其中非聚集索引也被称为是二级索引或者辅助索引。
如果是按照字段个数进行划分则可以分为两类单一索引和联合索引。
普通/唯一/主键/全文索引
普通索引是基础索引没有任何约束主要是用来提升查询效率的
唯一索引是在普通索引的基础上对字段添加了唯一性约束
主键索引是在唯一索引的基础上对字段添加了非空约束也就是NOT NULL UNIQUE一张表里最多只能有一个主键索引。
全文索引用的不多MySQL自带的全文索引只支持英文。
前三种索引其实都是一类索引只不过是对数据的约束性逐渐提升。不过要注意单表中只能有一个主键索引因为数据存储在文件中只能按照一种顺序进行。
聚集索引与非聚集索引
聚集索引可以按照主键来排序存储数据主键索引后面就是数据类似数组可以简单理解成索引位置存储的就是对应的数据行这样子找到了索引值所在的位置就相当于找到了数据的位置在查找行的时候非常有效。
只有当表包含聚集索引时表内的数据行才会按照索引值在磁盘上进行物理排序和存储。每个表只能有一个聚集索引因为数据行本身只能按照一个顺序存储。
一般来讲主键索引或者唯一非空索引都可以作为聚集索引。
在MySQL的InnoDB里
如果一张表定义了主键索引那么这个主键索引就作为聚集索引。如果没有定义主键索引那么该表的第一个唯一非空索引作为聚集索引。如果也没有唯一非空索引那么InnoDB会生成一个隐藏的自增主键作为聚集索引。
总之InnoDB一定会给自己搞出一个聚集索引用来指导自身在磁盘上的排序。
非聚集索引是什么呢
在数据库管理系统中会有单独的存储空间来存放非聚集索引。这些索引是按照顺序存储的但是索引里存储的并不是对应的数据行而是数据行存储的地址。
也就是说在正式使用的时候系统会进行两次查找第一次先找到索引第二次去索引对应的位置取出数据行。
这不同于聚集索引聚集索引相当于是索引里存储的就是对应的数据行索引排序后相当于是对所有数据行排序了。非聚集索引是维护有单独的索引表它只保证索引表是有序的而数据行仍然是随机存储的。
复制一下教程里对聚集索引和非聚集索引的总结
聚集索引的叶子节点存储的就是我们的数据记录非聚集索引的叶子节点存储的是数据位置。非聚集索引不会影响数据表的物理存储顺序。一个表只能有一个聚集索引因为只能有一种排序存储的方式但可以有多个非聚集索引也就是多个索引目录提供数据检索。使用聚集索引的时候数据的查询效率高但如果对数据进行插入删除更新等操作效率会比非聚集索引低。
前两项总结好理解该如何理解第三项总结里的插入/删除/更新时聚集索引会更慢呢
主要是因为聚集索引其实是面向读取的设计。
由于我们的数据会按照聚集索引的大小顺序依次写入磁盘所以当我们更新或者插入一条随机的数据时所有的记录需要重新排序并按照新顺序重写进磁盘。
而非聚集索引由于维护的只是个索引表只需要更新下索引表就可以数据行还是随机存。
所以在数据量很大的情况下这个效率的差距是很明显的。
单一索引与联合索引
索引列为一列时是单一索引
多个列组合在一起创建的索引叫做联合索引。
创建联合索引时我们需要注意创建时的顺序问题因为联合索引 (x, y, z) 和 (z, y, x) 在使用的时候效率可能会存在差别。
这是因为联合索引的 最左匹配原则 。就是按照最左优先的方式进行索引的匹配。
比如以x, y, z的顺序创建联合索引如果查询条件是where x1 and y2 and z3就可以匹配上索引如果查询条件是where y2就匹配不上联合索引。
其实就是说如果联合索引最左边的字段没有进入查询条件里那么联合索引就无法发挥作用。
总结
对where字句的字段建立索引可以大幅度提升查询的效率。
采用聚集索引进行数据查询比使用非聚集索引的查询效率略高。
索引其实还存在一些不足比如说占用额外的存储空间、会降低数据库的写操作性能等如果有多个索引的话还会增加索引选择的时间。因此在使用索引的时候需要在效率和维护代价之间做平衡。
参考文献
23丨索引的概览用还是不用索引这是一个问题
