精华内容
下载资源
问答
  • 当我们面对大量的数据查询时,为了提高查询效率,我们在数据库中总要使用到索引。那么索引究竟是怎么样的东西呢? 索引本质 索引其实就是一种数据结构,他将数据库中的数据以一定的数据结构算法进行存储,能够帮助...

    当我们面对大量的数据查询时,为了提高查询效率,我们在数据库中总要使用到索引。那么索引究竟是怎么样的东西呢?

    索引本质

    索引其实就是一种数据结构,他将数据库中的数据以一定的数据结构算法进行存储,能够帮助我们快速的检索数据库中的数据。

    何时使用索引

    1.当某些列的查询概率比较高或经常作为where条件的列。

    2.在作为主键的列上。

    3.在经常用在表连接的列上。

    在这些时候,我们就要考虑使用此列作为一个索引

    索引类型

    1.在Mysql数据库的索引中,主要包括Hash索引和B+ Tree索引,我们常用的InnoDB引擎,默认的是B+树。

    2.Hash索引和B+ Tree索引的区别:

        1)Hash索引底层是哈希表,哈希表是一种以key-value存储数据的结构,所以多个数据在存储关系上是完全没有任何顺序关系的,所以,对于区间查询(大于、小于的范围查询)是无法直接通过索引查询的,就需要全表扫描。所以,哈希索引只适用于等值查询的场景。而B+ Tree是一种多路平衡查询树,所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点),所以对于范围查询的时候不需要做全表扫描。

        2)哈希索引没办法利用索引完成排序(例如order by);

        3)哈希索引不支持多列联合索引的最左匹配规则。(多列索引(联合索引)有最左前缀的原则:即最左优先。
    如果我们建立了一个2列的联合索引(col1,col2),实际上已经建立了两个联合索引(col1)、(col1,col2);
    如果有一个3列索引(col1,col2,col3),实际上已经建立了三个联合索引(col1)、(col1,col2)、(col1,col2,col3),查询时,会先查最左的第一个索引col1。)

        4)如果有大量重复键值得情况下,哈希索引的效率会很低,因为存在哈希碰撞问题。

    3.在B+ Tree索引下还分为了聚簇索引和非聚簇索引。在 InnoDB 里,索引B+ Tree的叶子节点存储了整行数据的是主键索引,也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引,也被称之为非聚簇索引。聚簇索引查询会更快,因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值,查到主键的值以后,还需要再通过主键的值再回表进行一次查询。

    4.深入探索问题——什么情况下会发生明明创建了索引,但是执行的时候并没有通过索引呢?

    答:当查询优化器未选择到该索引的执行计划查询优化器: 一条SQL语句的查询,可以有不同的执行方案,至于最终选择哪种方案,需要通过优化器进行选择,选择执行成本最低的方案。 在一条单表查询语句真正执行之前,MySQL的查询优化器会找出执行该语句所有可能使用的方案,对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。 优化过程大致如下: 1、根据搜索条件,找出所有可能使用的索引 2、计算全表扫描的代价 3、计算使用不同索引执行查询的代价 4、对比各种执行方案的代价,找出成本最低的那一个)

    索引的缺点

    1.创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
    2.索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
    3.当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

    索引是否生效

    1.在数据库新建一个查询,使用 explain 关键字进行查询。

    例如 explain select * from user WHERE company_id=410 

    此时在查询结果中就会发现

    possible_keys列下面的值就是你创建的索引名称,如果为空,则说明你的索引未被使用到。

    展开全文
  • 介绍Lucene实现实时索引基本原理,通过几幅简单的图片,介绍了实时索引实现的过程;同时还给出了该系列中的索引的配置类实现

    转载请注明出处:http://blog.csdn.net/xiaojimanman/article/details/43982653

    http://www.llwjy.com/blogdetail/63d4c488a2cccb5851c0498d374951c9.html

          个人的博客小站也搭建成功,网址:www.llwjy.com ,欢迎大家来吐槽~


    基本原理

          在前面的博客中也说过,程序初始话索引文件是十分消耗系统资源的,因此要想实现实时索引就不能实时的去修改索引文件、重新加载索引文件,就必须考虑如何使用内存来实现这实时索引;在Lucene4.3.1版本(之前的版本也有,但是在后面的版本中就将NRT*相关的类删除了)中NRT*相关类就提供了创建实时索引(伪实时索引)的相关方法,将IndexWrite的相关操作委托给TrackingIndexWriter来处理,实现了内存索引和硬盘索引的结合,通过NRTManager为外部提供可用的索引,当然,在执行commit(之前创建索引中有相关介绍)操作之前,操作的数据都是存在内存中,一旦宕机或者服务重,这些数据都将丢失,因此就需要自己添加一个守护线程去不断的执行commit操作(commit操作十分消耗系统资源,索引不可能每一次修改都去执行该操作)。下面就通过几个简单的图来介绍一下实时索引的实现原理:

          在系统刚启动时候,存在两个索引:内存索引、硬盘索引,当然此时内存索引中是没有任何数据的,结构如下图所示:


          在系统运行过程中,一旦有索引的增加、删除、修改等操作,这些操作都是操作内存索引,而不是硬盘索引,如下图所示:


          当程序主动执行commit操作时,这是会将内存索引复制一份,我们称之为合并索引,同时将内存索引清空,用于之后的索引操作,此时系统中就存在内存索引、合并索引、硬盘索引,在想外提供服务的同时,也会将合并索引中的数据写入硬盘,如下图所示:


          当合并索引中的数据已经全部写入硬盘之后,程序会对硬盘索引重读,形成新的IndexReader,在新的硬盘IndexReader替换旧的硬盘IndexReader时,删除合并索引的IndexReader,这样系统又重新回到最初的状态(当然此时内存索引中可能会有数据),如下图所示:


          如此反复,一个实时索引的系统也就算完成了,当然这里也会有一定的风险,就是在宕机时可能会丢失一部分的数据。关于这个问题,如果数据准确度要求不是太高的话可以忽略,毕竟这种情况发生的概率太小了;如果对数据的准确度要求特别高的话,可以通过添加输出日志来完成。

          ps:Lucene内部的实现逻辑比上面复杂的多,这里只是简单的介绍一下实现原理,如要深入了解,还请详细阅读相关书籍、源码。


    配置类

          在这篇博客中就先把这个系列的实时索引的配置类介绍以下,后面就不再介绍了。

    ConfigBean

          ConfigBean类中,定义了一些索引的基本属性,如:索引名、硬盘存储位置、采用的分词器、commit操作执行频率、内存索引重读频率等,具体代码如下:

    /**  
     *@Description:  索引基础配置属性
     */
    package com.lulei.lucene.index.model;
    
    import org.apache.lucene.analysis.Analyzer;
    import org.apache.lucene.analysis.standard.StandardAnalyzer;
    import org.apache.lucene.util.Version;
    
    public class ConfigBean {
    	// 分词器
    	private Analyzer analyzer = new StandardAnalyzer(Version.LUCENE_43);
    	// 索引地址
    	private String indexPath = "/index/";
    	private double indexReopenMaxStaleSec = 10;
    	private double indexReopenMinStaleSec = 0.025;
    	// 索引commit时间
    	private int indexCommitSeconds = 60;
    	// 索引名称
    	private String indexName = "index";
    	//commit时是否输出相关信息
    	private boolean bprint = true;
    	
    	public Analyzer getAnalyzer() {
    		return analyzer;
    	}
    	public void setAnalyzer(Analyzer analyzer) {
    		this.analyzer = analyzer;
    	}
    	public String getIndexPath() {
    		return indexPath;
    	}
    	public void setIndexPath(String indexPath) {
    		if (!(indexPath.endsWith("\\") || indexPath.endsWith("/"))) {
    			indexPath += "/";
    		}
    		this.indexPath = indexPath;
    	}
    	public double getIndexReopenMaxStaleSec() {
    		return indexReopenMaxStaleSec;
    	}
    	public void setIndexReopenMaxStaleSec(double indexReopenMaxStaleSec) {
    		this.indexReopenMaxStaleSec = indexReopenMaxStaleSec;
    	}
    	public double getIndexReopenMinStaleSec() {
    		return indexReopenMinStaleSec;
    	}
    	public void setIndexReopenMinStaleSec(double indexReopenMinStaleSec) {
    		this.indexReopenMinStaleSec = indexReopenMinStaleSec;
    	}
    	public int getIndexCommitSeconds() {
    		return indexCommitSeconds;
    	}
    	public void setIndexCommitSeconds(int indexCommitSeconds) {
    		this.indexCommitSeconds = indexCommitSeconds;
    	}
    	public String getIndexName() {
    		return indexName;
    	}
    	public void setIndexName(String indexName) {
    		this.indexName = indexName;
    	}
    	public boolean isBprint() {
    		return bprint;
    	}
    	public void setBprint(boolean bprint) {
    		this.bprint = bprint;
    	}
    }
    
     

    IndexConfig

          在一个系统中并不一定只存在一个索引,也可能会是多个,所以又添加了一个IndexConfig类,具体代码如下:

     /**  
     *@Description: 索引的相关配置参数
     */ 
    package com.lulei.lucene.index.model;  
    
    import java.util.HashSet;
    
    public class IndexConfig {
    	//配置参数
    	private static HashSet<ConfigBean> configBean = null;
    	
    	//默认的配置
    	private static class LazyLoadIndexConfig {
    		private static final HashSet<ConfigBean> configBeanDefault = new HashSet<ConfigBean>();
    		 static {
    			 ConfigBean configBean = new ConfigBean();
    			 configBeanDefault.add(configBean);
    		 }
    	}
    
    	public static HashSet<ConfigBean> getConfigBean() {
    		//如果未对IndexConfig初始化,则使用默认配置
    		if (configBean == null) {
    			configBean = LazyLoadIndexConfig.configBeanDefault;
    		}
    		return configBean;
    	}
    
    	public static void setConfigBean(HashSet<ConfigBean> configBean) {
    		IndexConfig.configBean = configBean;
    	}
    }
    

    ps:最近发现其他网站可能会对博客转载,上面并没有源链接,如想查看更多关于 基于lucene的案例开发点击这里。或访问网址http://blog.csdn.net/xiaojimanman/article/category/2841877 或 http://www.llwjy.com/

    展开全文
  • 索引基本知识 索引匹配方式 哈希索引 当需要存储大量的URL,并且根据URL进行搜索查找,如果使用B+树,存储的内容就会很大 select id from url where url="" 也可以利用将url使用CRC32做哈希,可以使用以下查询方式...

    索引基本知识

    在这里插入图片描述

    索引匹配方式

    在这里插入图片描述

    哈希索引

    当需要存储大量的URL,并且根据URL进行搜索查找,如果使用B+树,存储的内容就会很大
    select id from url where url=""
    也可以利用将url使用CRC32做哈希,可以使用以下查询方式:
    select id fom url where url="" and url_crc=CRC32("")
    此查询性能较高原因是使用体积很小的索引来完成查找
    在这里插入图片描述

    组合索引

    当包含多个列作为索引,需要注意的是正确的顺序依赖于该索引的查询,同时需要考虑如何更好的满足排序和分组的需要
    案例,建立组合索引a,b,c,不同SQL语句使用索引情况:
    在这里插入图片描述where a=3 and b>10 and c=7 只使用索引a,b,因为b是范围查找,范围查找的后面就无法做精确匹配了,所以无论后面的c是否加过索引,都不走索引了。
    where a=3 and b=10 and c=7 ,就可以走索引a,b,c了。

    建立索引的时候,尽量选择长度较小的列,占用存储空间较小。

    簇族索引、非簇族索引

    innodb是簇族索引
    myisam是非簇族索引
    在这里插入图片描述聚簇索引,是为了减少对磁盘的IO。
    为什么按照主键的顺序插入是最快的方式:因为如果不按顺序插入/删除,会产生过多的页分裂/合并,影响效率。(类似于大数据的region分割)
    在这里插入图片描述

    覆盖索引

    在这里插入图片描述
    1、当发起一个被索引覆盖的查询时,在explain的extra列可以看到using index的信息,此时就使用了覆盖索引

    mysql> explain select store_id,film_id from inventory\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: inventory
       partitions: NULL
             type: index
    possible_keys: NULL
              key: idx_store_id_film_id
          key_len: 3
              ref: NULL
             rows: 4581
         filtered: 100.00
            Extra: Using index
    1 row in set, 1 warning (0.01 sec)
    
    

    2、在大多数存储引擎中,覆盖索引只能覆盖那些只访问索引中部分列的查询。不过,可以进一步的进行优化,可以使用innodb的二级索引来覆盖查询。

    例如:actor使用innodb存储引擎,并在last_name字段又二级索引,虽然该索引的列不包括主键actor_id,但也能够用于对actor_id做覆盖查询

    mysql> explain select actor_id,last_name from actor where last_name='HOPPER'\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: actor
       partitions: NULL
             type: ref
    possible_keys: idx_actor_last_name
              key: idx_actor_last_name
          key_len: 137
              ref: const
             rows: 2
         filtered: 100.00
            Extra: Using index
    1 row in set, 1 warning (0.00 sec)
    

    示例:出现了覆盖索引,显示using index
    在这里插入图片描述

    优化小细节

    避免where id+1=5这种查询,应该直接使用where id=4

    type列的好坏排序:
    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    在这里插入图片描述
    在这里插入图片描述

    前缀索引实例说明

    ​ 有时候需要索引很长的字符串,这会让索引变的大且慢,通常情况下可以使用某个列开始的部分字符串,这样大大的节约索引空间,从而提高索引效率,但这会降低索引的选择性

    索引的选择性是指不重复的索引值和数据表记录总数的比值,范围从1/#T到1之间。索引的选择性越高则查询效率越高,因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。

    ​ 一般情况下某个列前缀的选择性也是足够高的,足以满足查询的性能,但是对应BLOB,TEXT,VARCHAR类型的列,必须要使用前缀索引,因为mysql不允许索引这些列的完整长度,使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性,通过又不能太长。

    案例演示:

    --创建数据表
    create table citydemo(city varchar(50) not null);
    insert into citydemo(city) select city from city;
    
    --重复执行5次下面的sql语句
    insert into citydemo(city) select city from citydemo;
    
    --更新城市表的名称
    update citydemo set city=(select city from city order by rand() limit 1);
    
    --查找最常见的城市列表,发现每个值都出现45-65次,
    select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;
    
    --查找最频繁出现的城市前缀,先从3个前缀字母开始,发现比原来出现的次数更多,可以分别截取多个字符查看城市出现的次数
    select count(*) as cnt,left(city,3) as pref from citydemo group by pref order by cnt desc limit 10;
    select count(*) as cnt,left(city,7) as pref from citydemo group by pref order by cnt desc limit 10;
    --此时前缀的选择性接近于完整列的选择性
    
    --还可以通过另外一种方式来计算完整列的选择性,可以看到当前缀长度到达7之后,再增加前缀长度,选择性提升的幅度已经很小了
    select count(distinct left(city,3))/count(*) as sel3,
    count(distinct left(city,4))/count(*) as sel4,
    count(distinct left(city,5))/count(*) as sel5,
    count(distinct left(city,6))/count(*) as sel6,
    count(distinct left(city,7))/count(*) as sel7,
    count(distinct left(city,8))/count(*) as sel8 
    from citydemo;
    
    --计算完成之后可以创建前缀索引,只取前7个字节创建索引,节省索引的存储空间
    alter table citydemo add key(city(7));
    
    --注意:前缀索引是一种能使索引更小更快的有效方法,但是也包含缺点:mysql无法使用前缀索引做order by 和 group by。 
    

    OLTP/OLAP
    基数cardinality统计
    Hyperloglog算法
    基数越小,关联的时候效率越高

    使用索引扫描来做排序

    在这里插入图片描述
    ​ mysql有两种方式可以生成有序的结果:通过排序操作或者按索引顺序扫描,如果explain出来的type列的值为index,则说明mysql使用了索引扫描来做排序

    ​ 扫描索引本身是很快的,因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列,那么就不得不每扫描一条索引记录就得回表查询一次对应的行,这基本都是随机IO,因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢

    ​ mysql可以使用同一个索引即满足排序,又用于查找行,如果可能的话,设计索引时应该尽可能地同时满足这两种任务。

    ​ 只有当索引的列顺序和order by子句的顺序完全一致,并且所有列的排序方式都一样时,mysql才能够使用索引来对结果进行排序,如果查询需要关联多张表,则只有当orderby子句引用的字段全部为第一张表时,才能使用索引做排序。order by子句和查找型查询的限制是一样的,需要满足索引的最左前缀的要求,否则,mysql都需要执行顺序操作,而无法利用索引排序

    --sakila数据库中rental表在rental_date,inventory_id,customer_id上有rental_date的索引
    --使用rental_date索引为下面的查询做排序
    explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id,customer_id\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: rental
       partitions: NULL
             type: ref
    possible_keys: rental_date
              key: rental_date
          key_len: 5
              ref: const
             rows: 1
         filtered: 100.00
            Extra: Using index condition
    1 row in set, 1 warning (0.00 sec)
    --order by子句不满足索引的最左前缀的要求,也可以用于查询排序,这是因为所以你的第一列被指定为一个常数
    
    --该查询为索引的第一列提供了常量条件,而使用第二列进行排序,将两个列组合在一起,就形成了索引的最左前缀
    explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id desc\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: rental
       partitions: NULL
             type: ref
    possible_keys: rental_date
              key: rental_date
          key_len: 5
              ref: const
             rows: 1
         filtered: 100.00
            Extra: Using where
    1 row in set, 1 warning (0.00 sec)
    
    --下面的查询不会利用索引
    explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by rental_date,inventory_id\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: rental
       partitions: NULL
             type: ALL
    possible_keys: rental_date
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 16005
         filtered: 50.00
            Extra: Using where; Using filesort
    
    --该查询使用了两中不同的排序方向,但是索引列都是正序排序的,在排序列能组成最左前缀匹配的情况下,可以全部正序,可以全部逆序,都能走索引,但是不能一个正序,一个逆序。
    explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by inventory_id desc,customer_id asc\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: rental
       partitions: NULL
             type: ALL
    possible_keys: rental_date
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 16005
         filtered: 50.00
            Extra: Using where; Using filesort
    1 row in set, 1 warning (0.00 sec)
    
    --该查询中引用了一个不再索引中的列
    explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by inventory_id,staff_id\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: rental
       partitions: NULL
             type: ALL
    possible_keys: rental_date
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 16005
         filtered: 50.00
            Extra: Using where; Using filesort
    1 row in set, 1 warning (0.00 sec)
    
    

    索引监控

    在这里插入图片描述

    索引优化分析案例

    预先准备好数据

    SET FOREIGN_KEY_CHECKS=0;
    DROP TABLE IF EXISTS `itdragon_order_list`;
    CREATE TABLE `itdragon_order_list` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id,默认自增长',
      `transaction_id` varchar(150) DEFAULT NULL COMMENT '交易号',
      `gross` double DEFAULT NULL COMMENT '毛收入(RMB)',
      `net` double DEFAULT NULL COMMENT '净收入(RMB)',
      `stock_id` int(11) DEFAULT NULL COMMENT '发货仓库',
      `order_status` int(11) DEFAULT NULL COMMENT '订单状态',
      `descript` varchar(255) DEFAULT NULL COMMENT '客服备注',
      `finance_descript` varchar(255) DEFAULT NULL COMMENT '财务备注',
      `create_type` varchar(100) DEFAULT NULL COMMENT '创建类型',
      `order_level` int(11) DEFAULT NULL COMMENT '订单级别',
      `input_user` varchar(20) DEFAULT NULL COMMENT '录入人',
      `input_date` varchar(20) DEFAULT NULL COMMENT '录入时间',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8;
    
    INSERT INTO itdragon_order_list VALUES ('10000', '81X97310V32236260E', '6.6', '6.13', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-28 17:01:49');
    INSERT INTO itdragon_order_list VALUES ('10001', '61525478BB371361Q', '18.88', '18.79', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-18 17:01:50');
    INSERT INTO itdragon_order_list VALUES ('10002', '5RT64180WE555861V', '20.18', '20.17', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-09-08 17:01:49');
    
    

    逐步开始进行优化:

    第一个案例:

    select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
    --通过查看执行计划发现type=all,需要进行全表扫描
    explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
    
    --优化一、为transaction_id创建唯一索引
     create unique index idx_order_transaID on itdragon_order_list (transaction_id);
    --当创建索引之后,唯一索引对应的type是const,通过索引一次就可以找到结果,普通索引对应的type是ref,表示非唯一性索引赛秒,找到值还要进行扫描,直到将索引文件扫描完为止,显而易见,const的性能要高于ref
     explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
     
     --优化二、使用覆盖索引,查询的结果变成 transaction_id,当extra出现using index,表示使用了覆盖索引
     explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";
    

    第二个案例

    --创建复合索引
    create index idx_order_levelDate on itdragon_order_list (order_level,input_date);
    
    --创建索引之后发现跟没有创建索引一样,都是全表扫描,都是文件排序
    explain select * from itdragon_order_list order by order_level,input_date;
    
    --可以使用force index强制指定索引
    explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;
    --其实给订单排序意义不大,给订单级别添加索引意义也不大,因此可以先确定order_level的值,然后再给input_date排序
    explain select * from itdragon_order_list where order_level=3 order by input_date;
    
    展开全文
  • 索引原理

    万次阅读 多人点赞 2019-06-12 07:51:29
    有兴趣的同学可以看原文~ 使用索引很简单,只要能写创建表的语句,就肯定...然而, 会使用索引是一回事, 而深入理解索引原理又能恰到好处使用索引又是另一回事,这完全是两个天差地别的境界(我自己也还没有达到...

    文章转载于:https://www.cnblogs.com/aspwebchh/p/6652855.html

    有兴趣的同学可以看原文~

           使用索引很简单,只要能写创建表的语句,就肯定能写创建索引的语句,要知道这个世界上是不存在不会创建表的服务器端程序员的。然而, 会使用索引是一回事, 而深入理解索引原理又能恰到好处使用索引又是另一回事,这完全是两个天差地别的境界(我自己也还没有达到这层境界)。很大一部份程序员对索引的了解仅限于到“加索引能使查询变快”这个概念为止。

    • 为什么要给表加上主键?

    • 为什么加索引后会使查询变快?

    • 为什么加索引后会使写入、修改、删除变慢?

    • 什么情况下要同时在两个字段上建索引?

           这些问题他们可能不一定能说出答案。知道这些问题的答案有什么好处呢?如果开发的应用使用的数据库表中只有1万条数据,那么了解与不了解真的没有差别, 然而, 如果开发的应用有几百上千万甚至亿级别的数据,那么不深入了解索引的原理, 写出来程序就根本跑不动,就好比如果给货车装个轿车的引擎,这货车还能拉的动货吗?

           接下来就讲解一下上面提出的几个问题,希望对阅读者有帮助。

           网上很多讲解索引的文章对索引的描述是这样的「索引就像书的目录, 通过书的目录就准确的定位到了书籍具体的内容」,这句话描述的非常正确, 但就像脱了裤子放屁,说了跟没说一样,通过目录查找书的内容自然是要比一页一页的翻书找来的快,同样使用的索引的人难到会不知道,通过索引定位到数据比直接一条一条的查询来的快,不然他们为什么要建索引。

           想要理解索引原理必须清楚一种数据结构「平衡树」(非二叉),也就是b tree或者 b+ tree,重要的事情说三遍:“平衡树,平衡树,平衡树”。当然, 有的数据库也使用哈希桶作用索引的数据结构 , 然而, 主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。

           我们平时建表的时候都会为表加上主键, 在某些关系数据库中, 如果建表时不指定主键,数据库会拒绝建表的语句执行。 事实上, 一个加了主键的表,并不能被称之为「表」。一个没加主键的表,它的数据无序的放置在磁盘存储器上,一行一行的排列的很整齐, 跟我认知中的「表」很接近。如果给表上了主键,那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构,也就是上面说的「平衡树」结构,换句话说,就是整个表就变成了一个索引。没错, 再说一遍, 整个表变成了一个索引,也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键, 一个表只能有一个「聚集索引」,因为主键的作用就是把「表」的数据格式转换成「索引(平衡树)」的格式放置。

             

     

           上图就是带有主键的表(聚集索引)的结构图。图画的不是很好, 将就着看。其中树的所有结点(底部除外)的数据都是由主键字段中的数据构成,也就是通常我们指定主键的id字段。最下面部分是真正表中的数据。 假如我们执行一个SQL语句:

           select * from table where id = 1256;

           首先根据索引定位到1256这个值所在的叶结点,然后再通过叶结点取到id等于1256的数据行。 这里不讲解平衡树的运行细节, 但是从上图能看出,树一共有三层, 从根节点至叶节点只需要经过三次查找就能得到结果。如下图

              

           假如一张表有一亿条数据 ,需要查找其中某一条数据,按照常规逻辑, 一条一条的去匹配的话, 最坏的情况下需要匹配一亿次才能得到结果,用大O标记法就是O(n)最坏时间复杂度,这是无法接受的,而且这一亿条数据显然不能一次性读入内存供程序使用, 因此, 这一亿次匹配在不经缓存优化的情况下就是一亿次IO开销,以现在磁盘的IO能力和CPU的运算能力, 有可能需要几个月才能得出结果 。如果把这张表转换成平衡树结构(一棵非常茂盛和节点非常多的树),假设这棵树有10层,那么只需要10次IO开销就能查找到所需要的数据, 速度以指数级别提升,用大O标记法就是O(log n),n是记录总树,底数是树的分叉数,结果就是树的层次数。换言之,查找次数是以树的分叉数为底,记录总数的对数,用公式来表示就是

                

           用程序来表示就是Math.Log(100000000,10),100000000是记录数,10是树的分叉数(真实环境下分叉数远不止10), 结果就是查找次数,这里的结果从亿降到了个位数。因此,利用索引会使数据库查询有惊人的性能提升。

           然而, 事物都是有两面的, 索引能让数据库查询数据的速度上升, 而使写入数据的速度下降,原因很简单的, 因为平衡树这个结构必须一直维持在一个正确的状态, 增删改数据都会改变平衡树各节点中的索引数据内容,破坏树结构, 因此,在每次数据改变时, DBMS必须去重新梳理树(索引)的结构以确保它的正确,这会带来不小的性能开销,也就是为什么索引会给查询以外的操作带来副作用的原因。

           讲完聚集索引 , 接下来聊一下非聚集索引, 也就是我们平时经常提起和使用的常规索引。

           非聚集索引和聚集索引一样, 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段, 假如给user表的name字段加上索引 , 那么索引就是由name字段中的值构成,在数据改变时, DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 , 那么就会出现多个独立的索引结构,每个索引(非聚集索引)互相之间不存在关联。 如下图

                   

           每次给字段建一个新索引, 字段中的数据就会被复制一份出来, 用于生成索引。 因此, 给表添加索引,会增加表的体积, 占用磁盘存储空间。

           非聚集索引和聚集索引的区别在于, 通过聚集索引可以查到需要查找的数据, 而通过非聚集索引可以查到记录对应的主键值 , 再使用主键的值通过聚集索引查找到需要的数据,如下图

                

          不管以任何方式查询表, 最终都会利用主键通过聚集索引来定位到数据, 聚集索引(主键)是通往真实数据所在的唯一路径。

          然而, 有一种例外可以不使用聚集索引就能查询出所需要的数据, 这种非主流的方法 称之为「覆盖索引」查询, 也就是平时所说的复合索引或者多字段索引查询。 文章上面的内容已经指出, 当为字段建立索引以后, 字段中的内容会被同步到索引之中, 如果为一个索引指定两个字段, 那么这个两个字段的内容都会被同步至索引之中。

          先看下面这个SQL语句

          //建立索引

          create index index_birthday on user_info(birthday);

         //查询生日在1991年11月1日出生用户的用户名

         select user_name from user_info where birthday = '1991-11-1'

         这句SQL语句的执行过程如下

         首先,通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值

         然后,通过得到的主键ID值执行聚集索引查找,找到主键ID值对就的真实数据(数据行)存储的位置

         最后, 从得到的真实数据中取得user_name字段的值返回, 也就是取得最终的结果

         我们把birthday字段上的索引改成双字段的覆盖索引

         create index index_birthday_and_user_name on user_info(birthday, user_name);

         这句SQL语句的执行过程就会变为

         通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容,然而, 叶节点中除了有user_name表主键ID的值以外, user_name字段的值也在里面, 因此不需要通过主键ID值的查找数据行的真实所在, 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式, 可以省略不使用覆盖索引查找的后面两个步骤, 大大的提高了查询性能,如下图

             

           数据库索引的大致工作原理就是像文中所述, 然而细节方面可能会略有偏差,这但并不会对概念阐述的结果产生影响 。

    最后, 推荐三本关系数据库方面的书籍, 文中所讲解的概念内容都是来自于此。

         《SQL Server2005技术内幕之T-SQL查询》

           这本书虽然是针对SQL Server写的, 但是里面的大部份内容同样适用于其它关系数据库,此书对查询编写的技巧和优化讲解的非常透彻。

         《关系数据库系统概论》第四版

          王珊和萨师煊写的那本, 是大学计算机教材, 讲的通俗易懂, 在国内计算机书图书出版领域质量是排的上号的。

        《数据库系统概念》

          这本书在数据库领域非常出名, 被称之为帆船书, 书中内容博大精深,非一朝一夕可参透的。

    展开全文
  • 1、创建索引对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加索引的话,那么查找任何哪怕只是一条特定的数据...
  • Lucene:基于传统全文检索引擎的倒排索引,并实现了分块索引。与倒排所引相对立的是正排索引,也成为正向所引。 Lucene:简单的说,可以认为是围绕索引展开的,索引包含的内容比较广且复杂。接下来,将简单介绍。 ...
  • Mysql索引原理

    千次阅读 2018-06-05 21:22:11
    Mysql索引类型及其特性普通索引 最基本索引,它没有任何限制,也是我们大多数情况下用到的索引。–直接创建索引 CREATE INDEX index_name ON table(column(length)) –修改表结构的方式添加索引 ALTER TABLE ...
  • Mysql索引:图文并茂,深入探究索引原理和使用

    千次阅读 多人点赞 2020-11-25 16:43:44
    关于Mysql索引的走心总结,建议收藏,反复阅读。
  • INNODB索引实现原理

    千次阅读 2018-07-19 20:36:29
    本篇继续整理Innodb索引实现原理。本文基于《MySQL运维内参》第8章整理。 二 B+树  B+树属于索引的基础,不在详细介绍插入删除过程。只介绍特点。 1 搜索二叉树:每个节点有两个子节点,数据量的增大必然导致...
  • MySQL数据库的索引数据库的索引1、背景2、定义和特征3、索引的分类(功能上分类)4、MySQL下索引基本操作4、索引 的底层实现原理(重点)5、索引的分类(实现上分类)参考 数据库的索引 1、背景 我以为我对Mysql...
  • 这两种索引的运行原理 查询过程 对于普通索引来说,查找到满足条件的第一个记录后,需要查找下一个记录,直到碰到第一个不满足条件的记录。 对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录...
  • mysql索引底层原理分析

    万次阅读 多人点赞 2018-09-23 00:01:40
    大家都知道索引的重要性,基本用法在上章《最全面的mysql索引知识大盘点》已分享过,本章主要是探索索引的底层实现原理。当然了,我们还是以mysql为基准进行探讨。 目录 前言:innodb和myisam的区别 1.物理磁盘...
  • SQL索引工作原理

    千次阅读 2019-04-26 22:17:06
    在谈到索引基本概念的时候,我们就提到了这种方式: 图书馆的前台有很多索引卡片柜,里面分了若干的类别,诸如按照书名笔画或拼音顺序、作者笔画或拼音顺序等等,但不同之处有二:① 索引卡片上记录了每本书摆放...
  • MySQL索引原理

    万次阅读 多人点赞 2016-02-02 20:53:39
    B+树索引是B+树在数据库中的一种实现,是最常见也是数据库中使用最为频繁的一种索引。B+树中的B代表平衡(balance),而不是二叉(binary),因为B+树是从最早的平衡二叉树演化而来的。在讲B+树之前必须先了解二叉...
  • 数据库索引实现原理

    万次阅读 2018-07-17 22:18:59
    这篇文章是介绍MySQL数据库中的索引是如何根据需求一步步演变...Oracle和DB2数据库索引的实现基本上也是大同小异的。文章写得很通俗易懂,就转在这了。关于B+树和索引内部结构可以参考:《B 树、B- 树、B+ 树和B*...
  • 索引原理学习

    千次阅读 多人点赞 2018-08-14 22:47:47
    一、介绍 1.什么是索引? 一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的,也是最容易出问题的,还是...索引对于良好的性能 非常关键,尤其...
  • oracle索引原理

    千次阅读 2017-07-11 09:47:16
    一、索引原理   Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁,并导致数据库性能急剧下降或进程终止。而如果做出正确的选择,则...
  • Mysql数据库索引原理及算法原理

    千次阅读 2019-03-07 18:01:23
    面试的时候总会被提及一些关于数据库操作的问题,那么数据库索引作为一项热门问题,总会被问到。最近在网上看到了一篇关于mysql数据库索引的好文章,认真看完之后肯定受益匪浅,(虽说有的地方我不太理解)转来供...
  • mongo数据库索引原理

    万次阅读 2017-08-05 00:40:19
    索引的本质 索引(Index)是帮助数据库高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。 现在的数据库(mongo,mysql等)索引多采用B-Tree数据结构
  • MySql 的索引实现原理

    千次阅读 2015-01-18 17:26:55
    索引的本质 MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质...最基本的查询算法当然是顺序查找(linear search),这种复杂度为O(n)的算法在数据量
  • 索引的实现原理

    万次阅读 多人点赞 2017-09-17 21:47:38
    Oracle和DB2数据库索引的实现基本上也是大同小异的。文章写得很通俗易懂,就转在这了。关于B+树和索引内部结构可以参考:《B 树、B- 树、B+ 树和B* 树》和《深入理解DB2索引(Index)》。 00 – 背景知识...
  • 数据库的索引原理 索引作用 索引是用来快速查找特定值的记录。如果没有索引、一般来说执行查询时会遍历整个表。索引就是把无须的数据变成有序的,然后提高查询效率。 索引案例 索引原理 1、把创建了索引的列进行排序...
  • Lucene简介和索引原理

    千次阅读 2017-12-14 09:55:28
    基于Lucene检索引擎我们开发了自己的全文检索系统,承担起后台PB级、万亿条数据记录的检索工作,这里向... Lucene简介和索引原理 该部分从三方面展开:Lucene简介、索引原理、Lucene索引实现。1.1 Lucene简介 Lucen
  • MySQL索引底层实现原理

    千次阅读 多人点赞 2019-01-13 17:18:08
    1、 索引的本质 MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。...最基本的查询算法当然是顺序查找(linear search),这种复杂度为O(n)的算法在数据量很大时显然...
  • MySQL索引类型 一、简介 MySQL目前主要有以下几种索引类型: 1.普通索引 2.唯一索引 3.主键索引 4.组合索引 5.全文索引 二、语句 CREATE TABLE table_name[col_name data type] [unique|fulltext][index|key]...
  • MySQL索引原理及BTree(B-/+Tree)结构详解

    万次阅读 多人点赞 2018-11-20 16:52:25
    索引的本质 B-Tree和B+Tree B-Tree B+Tree 带有顺序访问指针的B+Tree 为什么使用B-Tree(B+Tree) 主存存取原理 磁盘存取原理 局部性原理与磁盘预读 B-/+Tree索引的性能分析 MySQL索引实现 MyISAM索引...
  • 倒排索引查询原理

    万次阅读 2018-08-03 13:58:02
    每个segment可以看做是一个独立的subindex,在建立索引的过程中,lucene会不断的flush内存中的数据持久化形成新的segment。多个segment也会不断的被merge成一个大的segment,在老的segment还有查询在读取的时候,...
  • 数据库索引底层原理及优化

    千次阅读 多人点赞 2018-09-11 14:11:18
    特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等。为了避免混乱,本文将只关注于BTree索引,因为这是平常...
  • 数据库索引原理及优化

    万次阅读 多人点赞 2016-09-26 14:30:10
    特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等。为了避免混乱,本文将只关注于BTree索引,因为这是平常...
  • 索引:提高数据库的性能。 常见的索引: 主键索引(primary key) 唯一索引(unique) 普通索引(index) 全文索引(fulltext) ...1. 主键索引(primary key) ...主键索引的列基本上是int 1.1 创建 在创建表的时...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 217,941
精华内容 87,176
关键字:

对于索引的基本原理