文档章节

mysql数据库索引介绍

偶尔诗文
 偶尔诗文
发布于 2017/04/22 13:16
字数 1312
阅读 9
收藏 0

对于mysql数据库索引的数据结构可以参考这篇文章:

            http://blog.codinglabs.org/articles/theory-of-mysql-index.html

1,mysql数据库索引的类型

  • B+tree

            如果mysql没有特定指明,多半说的就是b+tree 索引,这种索引意味着所有的值都是按照顺序存储的,并且每一个叶子到根的距离是相同的,具体结构如图:

  b-tree索引的查询有效的类型如下:

  1. 全值匹配
  2. 匹配最左前列:联合索引的时候,只能单独使用最左那一列
  3. 匹配列前缀:可以匹配某一索引列值的开头部分,但是也只能使用联合索引的第一列
  4. 匹配范围值
  5. 精确匹配某一列,但是范围匹配另一列
  6. 只访问索引查询:也就是覆盖索引,查询的数据为索引列。

 下面是一些使用索引的限制:

  1. 联合索引中,如果不是按照索引的最左列查找,那么无法使用索引
  2. 不能跳过索引中的列:例如联合索引(A,B,C),如果查询条件为A,C那么其实只是用到了A
  3. 联合索引查询中如果使用了某个列的范围查询,那其右面的所有列都无法使用索引优化。
  • hash索引

            基于hash表实现,只有精确匹配索引所有列的查询才有效。memory引擎默认的索引类型。

            hash索引的限制

  1. hash索引只包含hash值和指针行,不存储字段值,所以不能使用索引中的值避免读取行。
  2. 无法用于排序
  3. 不支持部分索引列查询,因为存的是hash值,而不是数据
  4. 只支持等值比较查询,不支持范围查询,b-tree是支持的
  5. 除非有很多hash冲突,否则查询是非常快的
  6. 如果hash冲突很多的话,索引维护代价是很高的

            innoDB有一种特殊的功能,当他发现有索引值被经常使用的时候,可以在b-tree索引的基础上创建一个hash索引。

  •     空间索引(R-TREE)

        与B-tree索引相比,这类索引适合所有维度的查询,而不只限于前缀查询

  • 全文索引
  • 其他第三方索引

2,高性能索引策略

  • 独立的列

          在写sql的时候,索引列不能使表达式的一部分,例如索引列为A ,那么where A+1=2  或者 to_days(A) <= 10 这两种写法索引 是无效的  

  • 前缀索引和索引选择性

           有时候索引列很长,这就导致维护索引的开销会很大,这个时候不妨取索引列的前面的部分索引索引,而不是整个列的内容。那么如何选择索引列前缀的长度呢?这就需要引入一个概念,选择性=不重复的索引值的数量(基数)/表的记录总数。索引的选择性越高则查询的效率越高,当选择性趋于1的时候,就可以截取了。

  • 多列索引
  • 选择合适的索引列顺序

            通常情况下是将选择性高的索引列放在前面

  • 聚簇索引

            聚簇索引就是存储此索引的节点上同时也存储了这行除了索引的其他数据。innodb就是这个样子,表的主键都是聚簇索引

  • 覆盖索引

            如果一个索引包含所有需要查询字段的值,我们就称之为覆盖索引。如果使用了覆盖索引,使用explain分析sql的时候extra显示项为Using index。例如表table有一个多列索引(a,b),那么select a,b from table   就是用的覆盖索引

  • 使用索引扫描来做排序

            mysql可以使用同一个索引既做排序又用于查找行,因此设计索引时候尽可能满足这两个条件;只有当索引列顺序和order by字句得到顺序完全一致,并且所有列的排序方向都一样的时候,mysql才能够使用索引对结果进行排序。如果查询关联多张表,只有当order by子句引用的字段为第一个表的时候,才可以使用索引做排序。除了上面的条件外还有一个方法可以使用索引做排序,那就是索引的最左列赋值了常量的时候,order by子句就不需要将所有索引列全部顺序展示了。例如:

            表table有多列索引(a,b,c),那么 select * from table where a='1' order by b,c这种也是可以用为索引列做排序的

  • 压缩(前缀压缩)索引          

        myisam就是使用的这个技术,例如索引块第一个值“perform”,第二个值“performqsfs”,压缩完之后为"7,qsfs"  

  • 尽量避免创建冗余索引,尽可能扩展(建立联合索引),而不是新增
  • 不要创建不使用的索引
  • 索引和锁

        索引可以让查询锁定更少的行,innoDB的二级索引使用共享锁,主键索引使用排他锁。

© 著作权归作者所有

偶尔诗文
粉丝 12
博文 148
码字总数 65607
作品 0
沈阳
高级程序员
私信 提问
Mysql文件的导入导出和alter语句的使用

用了一段时间的MySQL,作为一个初学者,觉得很有必要来总结一下一些基本的用法。不够成熟,但有必要记录,持续更新吧!在摸索中前进,在总结中升华! 一、MySQL导入sql 文件 即MySQL数据库导...

惠风康桥
2013/01/04
173
0
PHP 学习必备技能(基础略过)

1.面向对象编程 面向对象编程基本概念 类和对象的关系 如何定义类 成员属性(变量) 如何创建对象实例及如何访问对象属性 对象在内存中存在的形式 栈、堆、全局区、常量区和代码区的关系 成员方...

风雪中的舞者
2015/08/05
100
0
SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方
2017/12/25
0
0
Elasticsearch 2.3.3 搜索引擎的elasticsearch-jdbc插件安装

Elasticsearch 2.3.3的jdbc插件安装跟之前的版本是不一样的,之前的版本,网上的内容介绍的都是elasticsearch使用river同步mysql数据 ,哪些都是老的文章了,最新的版本是不适用的。那么我们...

老猿
2016/06/20
960
3
MySQL基础教程

这是一个基础的MySQL教程,通过教程的学习后可以到达一个初级到中级 MySQL应用水平级别 ,主要介绍一些MySQL中基本的SQL语句。如果这是您第一次使用关系数据库管理系统(之前没有学习SQL相关...

易百教程
2016/10/11
121
0

没有更多内容

加载失败,请刷新页面

加载更多

springboot 403 问题

添加WebAppConfigurer 配置 @Configuration@EnableAutoConfigurationpublic class WebAppConfigurer extends WebMvcConfigurerAdapter { public WebAppConfigurer() { } ......

布袋和尚_爱吃鱼
15分钟前
3
0
Python自动更换壁纸爬虫与tkinter结合

直接上代码 import ctypesimport timeimport requestsimport osfrom threading import Threadfrom tkinter import Tk, Label, Button,Entry,StringVar,messagebox# '放到AppData\Roami......

物种起源-达尔文
15分钟前
2
0
Postgresql Study 笔记

Postgresql 安装 Windows, MAC Install Postgresql 下载地址: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads Linux Install sudo apt-get update sudo apt-get in......

slagga
17分钟前
3
0
layer.open 打开新页面传参问题

如图所示,点击出售,把A页面的数据传到弹框上面,因为弹框比较复杂,所以使用引入一个新页面。 A.html a.js B.html b.js 1、第一种方案 sellInte: function (){ var obj = document.g...

木九天
20分钟前
3
0
沙龙报名 | 区块链数据服务技术应用实践

京东云是国内首家提供区块链数据在线分析服务产品的公司,也是行业内首家对区块链数据服务进行开源的公司。 本次沙龙是京东云BDS开源后,首次在深圳举办线下沙龙,我们将邀请京东云BDS团队核...

京东云技术新知
21分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部