小编给大家分享一下MySQL聚簇索引和非聚簇索引怎么用,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!
索引分为聚簇索引和非聚簇索引。以一本英文课本为例,要找第8课,直接翻书,若先翻到第5课,则往后翻,再翻到第10课,则又往前翻。这本书本身就是一个索引,即“聚簇索引”。
如果要找”fire”这个单词,会翻到书后面的附录,这个附录是按字母排序的,找到F字母那一块,再找到”fir免费主机域名e”,对应的会是它在第几课。这个附录,为“非聚簇索引”。
由此可见,聚簇索引,索引的顺序就是数据存放的顺序,所以,很容易理解,一张数据表只能有一个聚簇索引。
聚簇索引要比非聚簇索引查询效率高很多,特别是范围查询的时候。所以,至于聚簇索引到底应该为主键,还是其他字段,这个可以再讨论。
1、MYSQL的索引
mysql中,不同的存储引擎对索引的实现方式不同,大致说下MyISAM和InnoDB两种存储引擎。
MyISAM的B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的。这里的索引都是非聚簇索引。
MyISAM还采用压缩机制存储索引,比如,第一个索引为“her”,第二个索引为“here”,那么第二个索引会被存储为“3,e”,这样的缺点是同一个节点中的索引只能采用顺序查找。
InnoDB的数据文件本身就是索引文件,B+Tree的叶子节点上的data就是数据本身,key为主键,这是聚簇索引。非聚簇索引,叶子节点上的data是主键(所以聚簇索引的key,不能过长)。为什么存放的主键,而不是记录所在地址呢,理由相当简单,因为记录所在地址并不能保证一定不会变,但主键可以保证。
至于为什么主键通常建议使用自增id呢?
2、聚簇索引
聚簇索引的数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id,那么可以想象,它会干些什么,不断地调整数据的物理地址、分页,当然也有其他一些措施来减少这些操作,但却无法彻底避免。但,如果是自增的,那就简单了,它只需要一页一页地写,索引结构相对紧凑,磁盘碎片少,效率也高。
聚簇索引不但在检索上可以大大滴提高效率,在数据读取上也一样。比如:需要查询f~t的所有单词。
一个使用MyISAM的主索引,一个使用InnoDB的聚簇索引。两种索引的B+Tree检索时间一样,但读取时却有了差异。
因为MyISAM的主索引并非聚簇索引,那么他的数据的物理地址必然是凌乱的,拿到这些物理地址,按照合适的算法进行I/O读取,于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。
不过,如果涉及到大数据量的排序、全表扫描、count之类的操作的话,还是MyISAM占优势些,因为索引所占空间小,这些操作是需要在内存中完成的。
鉴于聚簇索引的范围查询效率,很多人认为使用主键作为聚簇索引太多浪费,毕竟几乎不会使用主键进行范围查询。但若再考虑到聚簇索引的存储,就不好定论了。
下表给出了何时使用聚簇索引与非聚簇索引:
动作
使用聚簇索引
使用非聚簇索引
列经常被分组排序
应
应
返回某范围内的数据
应
不应
一个或极少不同值
不应
不应
小数目的不同值
应
不应
大数目的不同值
不应
应
频繁更新的列
不应
应
外键列
应
应
主键列
应
应
频繁修改索引列
不应
应
根据调优实践,要注意聚簇索引的选择。首先我们要找到我们最多用到的SQL查询,像本例就是那句类似的组合条件查询的情况,这种情况最好使用组合聚簇索引,而且最多用到的字段要放在组合聚簇索引的前面,否则的话就索引就不会有好的效果。
Index seek 为什么比 Index scan好?
索引扫描也就是遍历B树,而seek是B树查找直接定位。
Index scan多半是出现在索引列在表达式中。数据库引擎无法直接确定你要的列的值,所以只能扫描整个整个索引进行计算。index seek就要好很多.数据库引擎只需要扫描几个分支节点就可以定位到你要的记录。回过来,如果聚集索引的叶子节点就是记录,那么Clustered Index Scan就基本等同于full table scan。
一些优化原则
1、缺省情况下建立的索引是非聚簇索引,但有时它并不是最佳的。在非群集索引下,数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说:
a.有大量重复值、且经常有范围查询( > , =,虑建立群集索引;
b.经常同时存取多列,且每列都含有重复值可考虑建立组合索引;
c.组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好,恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引,维护索引集合就要做相应的更新工作。
2、ORDER BY和GROPU BY使用ORDER BY和GROUP BY短语,任何一种索引都有助于SELECT的性能提高。
3、多表操作在被实际执行前,查询优化器会根据连接条件,列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表;内外表的选择可由公式:外层表中的匹配行数*内层表中每一次查找的次数确定,乘积最小为最佳方案。
4、任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。
5、IN、OR子句常会使用工作表,使索引失效。如果不产生大量重复值,可以考虑把子句拆开。拆开的子句中应该包含索引。
建立聚簇索引的思想
1、大多数表都应该有聚簇索引或使用分区来降低对表尾页的竞争,在一个高事务的环境中,对最后一页的封锁严重影响系统的吞吐量。
2、在聚簇索引下,数据在物理上按顺序排在数据页上,重复值也排在一起,因而在那些包含范围检查(between、、>=)或使用group by或orderby的查询时,一旦找到具有范围中第一个键值的行,具有后续索引值的行保证物理上毗连在一起而不必进一步搜索,避免了大范围扫描,可以大大提高查询速度。
3、在一个频繁发生插入操作的表上建立聚簇索引时,不要建在具有单调上升值的列(如IDENTITY)上,否则会经常引起封锁冲突。
4、在聚簇索引中不要包含经常修改的列,因为码值修改后,数据行必须移动到新的位置。
5、选择聚簇索引应基于where子句和连接操作的类型。
聚簇索引的侯选列
1、主键列,该列在where子句中使用并且插入是随机的。
2、按范围存取的列,如pri_order > 100 and pri_order 3、在group by或order by中使用的列。
4、不经常修改的列。
5、在连接操作中使用的列。
非聚簇索引
非聚簇索引,叶级页指向表中的记录,记录的物理顺序与逻辑顺序没有必然的联系。非聚簇索引则更像书的标准索引表,索引表中的顺序通常与实际的页码顺序是不一致的。
每个表只能有一个聚簇索引,因为一个表中的记录只能以一种物理顺序存放。但是,一个表免费主机域名可以有不止一个非聚簇索引。实际上,对每个表你最多可以建立249个非聚簇索引。非聚簇索引需要大量的硬盘空间和内存。另外,虽然非聚簇索引可以提高从表中取数据的速度,它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚簇索引的表中的数据时,必须同时更新索引。因此你对一个表建立非聚簇索引时要慎重考虑。如果你预计一个表需要频繁地更新数据,那么不要对它建立太多非聚簇索引。另外,如果硬盘和内存空间有限,也应该限制使用非聚簇索引的数量
非聚簇索引的使用
SQLServer缺省情况下建立的索引是非聚簇索引,由于非聚簇索引不重新组织表中的数据,而是对每一行存储索引列值并用一个指针指向数据所在的页面。换句话说非聚簇索引具有在索引结构和数据本身之间的一个额外级。一个表如果没有聚簇索引时,可有250个非聚簇索引。每个非聚簇索引提供访问数据的不同排序顺序。在建立非聚簇索引时,要权衡索引对查询速度的加快与降低修改速度之间的利弊。另外,还要考虑这些问题:
1、索引需要使用多少空间。
2、合适的列是否稳定。
3、索引键是如何选择的,扫描效果是否更佳。
4、是否有许多重复值。
对更新频繁的表来说,表上的非聚簇索引比聚簇索引和根本没有索引需要更多的额外开销。对移到新页的每一行而言,指向该数据的每个非聚簇索引的页级行也必须更新,有时可能还需要索引页的分理。从一个页面删除数据的进程也会有类似的开销,另外,删除进程还必须把数据移到页面上部,以保证数据的连续性。所以,建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情况:
1、某列常用于集合函数(如Sum,….)。
2、某列常用于join,order by,group by。
3、查寻出的数据不超过表中数据量的20%。以上是“MySQL聚簇索引和非聚簇索引怎么用”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注云技术行业资讯频道!
MySQL中如何启用并分析慢查询日志,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。Mysql有一项功能可以记录长时间查询的慢记录,具体是多长时间可以自己配置,但是慢查询也未必就是不…