频道栏目
首页 > 数据库 > SQL Server > 正文
msql优化--索引优化
2017-07-15 10:38:13      个评论    来源:驰骋翱翔  
收藏   我要投稿

msql优化--索引优化,关于数据库优化指标主要为:响应时间,扫描行数,返回行数。对于响应时间,主要体现在服务器级别,服务器性能,网络响应速度等;索引主要为了减少扫描行数;根据limit来限制返回行数。

常用的索引有:B-Tree索引,哈希索引,空间索引,全文索引。除此之外,按照数据存储方式可以分为聚集索引和非聚集索引。

 

【索引类型】

B-Tree索引
\
B-tree索引按照二叉树的方式进行存储,叶子页保存着索引的值,枝节点按照一定范围进行存储索引值。mysql查询通过访问枝节点快速定位到叶子节点,通过叶子节点存储的行地址查找到磁盘中的数据,这样减少扫描数据行。
B-Tree索引的查找方式:全键值查找(遍历所有的索引值),键值范围(in语句查找等),建前缀查找(match匹配方式查找),精确某一列范围匹配另外一列,值访问索引查询(直接定位到某一个值)。
在使用B-Tree索引作为查询条件时有很多限制条件,注意这些限制条件对我们的查询优化有很多帮助,下面通过例子的方式进行描述: \ \

1. 在多列索引中,必须按照最左列开始查找,不然无法使用索引特性。例如上例:where条件不能使用last_name,first_name,dob的顺序进行索引查找
2. 不能跳过索引列。例如:查询条件first_name,dob的查询将不能发挥索引特性。
3. 查询条件中部使用范围查询,则右边的条件则不能使用索引查询。例如:first_name使用了in条件查询,则last_name,dob则不是索引查询。


哈希索引

哈希索引只能精确匹配索引列值才能发挥作用,其存储结构是将索引值通过哈希算法将键值进行存储。且哈希索引支持的数据引擎是memory,我们通用的InnoDB数据引擎是无法直接支持哈希索引的,它可以通过自适应哈希索引技术模拟哈希索引的功能。
\ 哈希索引的存储方式按照键值顺序存储,属于常数查找,存储内容与B-Tree索引相比不会存储索引列的值。因此查询时先访问索引页,然后通过行地址访问数据行,减少扫描数据行。
哈希索引的限制: 1. 索引页只包含哈希值和行指针,不包含数据值。 2. 数据行不是按照索引值的顺序来存储的,这样在排序操作时,索引列顺序和数据行顺序不一致导致性能消耗。
3. 必须匹配索引列的值,不能使用模糊匹配(like,match语句)
4. 只支持等值比较(<,>,<>则不支持)


聚簇索引


聚簇索引是InnoDB支持,但MyISAM数据引擎不支持的一种数据存储方式。它将数据行放入到索引页中,存储方式与B-Tree索引及其相似。
InnoDB通过主键来聚集数据,如果没有主键则通过唯一非空索引代替。 \

如上图,聚簇索引的索引值和数据保存在一起,这样访问速度更快。它任有一些限制:
1. 聚簇索引将数据行全部放入内存中,访问顺序不在重要了。
2. 插入顺序严重依赖杀入的位置,数据行的移动导致速度降低,因此不适合经常进入插入数据的表应用。
3. 当一个索引页无法存放所有的数据时,出现索引页断裂的问题
4. 二次索引需要扫描全表,速度下降。

【高效索引策略】

当我们了解了索引的存储特点及限制,则在建立索引的时候就能够有效的避免这些索引限制,下面我们通过如何选择索引列,索引列的数值最好具备什么样的特性,以及应用索引列的技巧进行阐述:
1. 索引要作为单独的列,不能使用表达式一部分或者函数的参数。
2. 索引列要尽可能简短,从性能考虑使用UUID作为索引列是很糟糕的!它在扫描查询,维护顺序上要消耗更多的资源。
3. 选择合适的前缀索引,在每个索引值对应的数据均匀的前提下,根据索引选择性进行判断(去重索引列值个数/总行数)
4. 对于单个索引来说,将查询出最少的数据条件放到前面。
5. 按照索引的顺序进行排序操作(可以使用explain命令,type类型是index)
6. 关联多张表时,on条件都使用索引列,order by子句都为第一个表时,才使用索引做排序。


【总结】

索引在查询提供了很大的便利,但是在更新和插入操作又带来了负担,我们最好在执行增删改操作的时候禁用索引,然后再启用。
点击复制链接 与好友分享!回本站首页
上一篇:SQL中使用WithAs处理层级关系小记
下一篇:数据库_SQL高级查询
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训

版权所有: 红黑联盟--致力于做实用的IT技术学习网站