精华内容
下载资源
问答
  • 建立组合索引字段顺序优化

    千次阅读 2019-01-14 21:39:25
    建立组合索引字段顺序优化 简介 组合索引我们经常用到,建立组合索引大家也都会,但是如何考虑建立组合索引的顺序是一个值得推敲的事情。 正文 1. 尽量把最常用的字段放在最前面 对于我们需要创建的组合索引,如果...

    建立组合索引的字段顺序优化

    简介

    组合索引我们经常用到,建立组合索引大家也都会,但是如何考虑建立组合索引的顺序是一个值得推敲的事情。

    正文

    1. 尽量把最常用的字段放在最前面

    对于我们需要创建的组合索引,如果同时又经常单独使用其中某个字段作为查询条件,这样的字段是要求放在组合索引前面的。

    因为这种场景下,能直接使用组合索引做范围扫描,否则,如果该字段放在后面,可能走索引跳跃扫描,全索引扫描,甚至全表扫描。

    举例:
    1. 首先创建表

      create table t_userserviceinfo_test as select * from T_USERSERVICEINFO nologging;
      
    2. 创建索引,把常用字段 phonenumber 作为组合索引的前导列

      create index ix_userserviceinfo_test_1 on t_userserviceinfo_test(phonenumber,servstaus) tablespace ringidx; 
      
    3. 按号码查询,查看执行计划,走了该索引的范围扫描,很快就查到了结果。

    反例:
    1. 删除上面的索引

      drop index ix_userserviceinfo_test_1;
      
    2. 创建新的索引,把 phonenumber 不作为前导列

      create index ix_userserviceinfo_test_1 on t_userserviceinfo_test(servstaus,phonenumber) tablespace ringidx; 
      
    3. 同样按号码查询,查看执行计划,走了该索引的跳跃扫描,效果不好。

    当然 Oracle考虑执行COST,可能就不会走这个索引了,导致全表扫描。

    2. 尽量把离散值较高的字段往前放

       	1. 条件中有单独使用这个字段,那么使用该索引有很好的效果
       	2. 放置误用索引,如果离骚之较少的字段放前面,同时条件中仅包含该字段,那么 Oracle 可能会选择该索引,但是其实选择该索引,选择率很低。
    

    3. 查询时,有的列是非等值条件,有点是等值条件,则等值条件字段放在前面

       	1. 等值条件字段放在前面,在查找的时候,找到的索引块都是有效数据。
       2. 如果非等值字段放在前面,那么需要进行索引跳跃扫描,或者范围扫描,这是就扫描了很多无效的索引。
    
    举例:
    1. 现需要根据状态和时间查找数据

       select * from t_userserviceinfo_test t where servstatus = 1 and t.upstatusstime > sysdate -100;
      
    2. 简历两个索引,分别把状态和时间字段顺序颠倒:

      create index ix_userserv_test_1 on t_userserviceinfo_test(servstaus,upstatustime);
      
      create index ix_userserv_test_2 on t_userserviceinfo_test(upstatustime,servstaus);
      
    3. 使用第一个索引查找

      select /* +index(ix_userserv_test_1) */  *
      from t_userserviceinfo_test t where t.servstaus = 1 and t.upstatustime > sysdate -100;
      

      得到执行分析如下,看到一致读数量为 334

      数据的查找过程是:首先从 servstaus = 1,upstatustime = sysdate -100 开始,找到第一条满足 servstaus = 1,upstatustime > sysdate -100 的数据,然后在索引树叶子节点顺序查找,直到找到第一条不满足条件的数据(servstaus = 2),退出查找,这个过程中查找到的索引都是有效索引。

      1. 使用第二个索引查找:
      select /* +index(ix_userserv_test_2) */  *
      from t_userserviceinfo_test t where t.servstaus = 1 and t.upstatustime > sysdate -100;
      

      看到一致读是前面的十倍,效果很不好。

      数据超找过程是:根据 upstatustime > sysdate -100 走的范围索引扫描,同时通过 servstaus = 1 过滤数据,存在大量的无用查找。

    总结:

    建立组合索引要考虑自身以及其他场景的使用情况,不要随意指定顺序。

    展开全文
  • mysql组合索引字段顺序

    千次阅读 2019-06-11 17:15:25
    一般来说,可能是某些字段没有创建索引,或者是组合索引字段顺序与查询语句中字段顺序不符。 看下面的例子: 假设有一张订单表(orders),包含order_id和product_id二个字段。 一共有31条数据。符合下面语句的...

    很多时候,我们在mysql中创建了索引,但是某些查询还是很慢,根本就没有使用到索引!一般来说,可能是某些字段没有创建索引,或者是组合索引中字段的顺序与查询语句中字段的顺序不符。

    看下面的例子:
    假设有一张订单表(orders),包含order_id和product_id二个字段。
    一共有31条数据。符合下面语句的数据有5条。执行下面的sql语句:

    1

    2

    3

    select product_id

    from orders

    where order_id in (123312223132224);

    这条语句要mysql去根据order_id进行搜索,然后返回匹配记录中的product_id。所以组合索引应该按照以下的顺序创建:

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    create index orderid_productid on orders(order_id, product_id)

    mysql> explain select product_id from orders where order_id in (123312223132224) \G

    *************************** 1. row ***************************

               id: 1

      select_type: SIMPLE

            table: orders

             type: range

    possible_keys: orderid_productid

              key: orderid_productid

          key_len: 5

              ref: NULL

             rows: 5

            Extra: Using where; Using index

    1 row in set (0.00 sec)

    可以看到,这个组合索引被用到了,扫描的范围也很小,只有5行。如果把组合索引的顺序换成product_id, order_id的话,mysql就会去索引中搜索 *123 *312 *223 *132 *224,必然会有些慢了。

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    mysql> create index orderid_productid on orders(product_id, order_id);                                                      

    Query OK, 31 rows affected (0.01 sec)

    Records: 31  Duplicates: 0  Warnings: 0

     

    mysql> explain select product_id from orders where order_id in (123312223132224) \G

     

    *************************** 1. row ***************************

     

               id: 1

      select_type: SIMPLE

            table: orders

             type: index

    possible_keys: NULL

              key: orderid_productid

          key_len: 10

              ref: NULL

             rows: 31

            Extra: Using where; Using index

    1 row in set (0.00 sec)

    这次索引搜索的性能显然不能和上次相比了。rows:31,我的表中一共就31条数据。索引被使用部分的长度:key_len:10,比上一次的key_len:5多了一倍。不知道是这样在索引里面查找速度快,还是直接去全表扫描更快呢?

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    mysql> alter table orders add modify_a char(255default 'aaa';

    Query OK, 31 rows affected (0.01 sec)

    Records: 31  Duplicates: 0  Warnings: 0

     

    mysql>

    mysql>

    mysql> explain select modify_a from orders where order_id in (123312223132224) \G         

    *************************** 1. row ***************************

               id: 1

      select_type: SIMPLE

            table: orders

             type: ALL

    possible_keys: NULL

              key: NULL

          key_len: NULL

              ref: NULL

             rows: 31

            Extra: Using where

    1 row in set (0.00 sec)

    这样就不会用到索引了。 刚才是因为select的product_id与where中的order_id都在索引里面的。


    为什么要创建组合索引呢?这么简单的情况直接创建一个order_id的索引不就行了吗?果只有一个order_id索引,没什么问题,会用到这个索引,然后mysql要去磁盘上的表里面取到product_id。如果有组合索引的话,mysql可以完全从索引中取到product_id,速度自然会快。再多说几句组合索引的最左优先原则:
    组合索引的第一个字段必须出现在查询组句中,这个索引才会被用到。果有一个组合索引(col_a,col_b,col_c),下面的情况都会用到这个索引:

    1

    2

    3

    4

    col_a = "some value";

    col_a = "some value" and col_b = "some value";

    col_a = "some value" and col_b = "some value" and col_c = "some value";

    col_b = "some value" and col_a = "some value" and col_c = "some value";

    对于最后一条语句,mysql会自动优化成第三条的样子~~。下面的情况就不会用到索引:

    1

    2

    col_b = "aaaaaa";

    col_b = "aaaa" and col_c = "cccccc";

    列转自:http://hi.baidu.com/liuzhiqun/blog/item/4957bcb1aed1b5590823023c.html

    通过实例理解单列索引、多列索引以及最左前缀原则。实例:现在我们想查出满足以下条件的用户id:
    mysql>SELECT `uid` FROM people WHERE lname`='Liu'  AND `fname`='Zhiqun' AND `age`=26
    因为我们不想扫描整表,故考虑用索引。

    单列索引:
    ALTER TABLE people ADD INDEX lname (lname);
    将lname列建索引,这样就把范围限制在lname='Liu'的结果集1上,之后扫描结果集1,产生满足fname='Zhiqun'的结果集2,再扫描结果集2,找到 age=26的结果集3,即最终结果。

    由 于建立了lname列的索引,与执行表的完全扫描相比,效率提高了很多,但我们要求扫描的记录数量仍旧远远超过了实际所需 要的。虽然我们可以删除lname列上的索引,再创建fname或者age 列的索引,但是,不论在哪个列上创建索引搜索效率仍旧相似。

    2.多列索引:
    ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);
    为了提高搜索效率,我们需要考虑运用多列索引,由于索引文件以B-Tree格式保存,所以我们不用扫描任何记录,即可得到最终结果。

    注:在mysql中执行查询时,只能使用一个索引,如果我们在lname,fname,age上分别建索引,执行查询时,只能使用一个索引,mysql会选择一个最严格(获得结果集记录数最少)的索引。

    3.最左前缀:顾名思义,就是最左优先,上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引,(lname,fname)组合索引以及(lname,fname,age)组合索引。

    注:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。

    建立索引的时机

    到这里我们已经学会了建立索引,那么我们需要在什么情况下建立索引呢?一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。例如:

    1

    SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'

    此时就需要对city和age建立索引,由于mytable表的userame也出现在了JOIN子句中,也有对它建立索引的必要。

    刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时,MySQL不会使用索引。例如下句会使用索引:

    1

    SELECT * FROM mytable WHERE username like'admin%'

    下句就不会使用:

    1

    SELECT * FROM mytable WHEREt Name like'%admin'

    因此,在使用LIKE时应注意以上的区别。

    索引的不足之处

    上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:

    • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
    • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

    使用索引的注意事项

    使用索引时,有以下一些技巧和注意事项:

    • 索引不会包含有NULL值的列

    只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

    • 使用短索引

    对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    • 索引列排序

    MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

    • like语句操作

    一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

    • 不要在列上进行运算

    select * from users where YEAR(adddate)<2007; 

    将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成

    select * from users where adddate<‘2007-01-01’;  

    • 不使用NOT IN和<>操作

     

    原文地址:https://www.cnblogs.com/goody9807/p/7396195.html

    展开全文
  • 1.什么是索引?为什么要用索引? 1.1索引的含义 1.2为什么用? 2.索引的作用与缺点 2.1作用 2.2缺点 3.索引的使用场景 3.1应创建索引的场景 3.2不应创建索引的场景 4.索引的分类与说明 4.1主键索引 ...

    热门系列:


    目录

    1.什么是索引?为什么要用索引?

       1.1索引的含义

       1.2为什么用?

    2.索引的作用与缺点

       2.1作用

       2.2缺点

    3.索引的使用场景

      3.1应创建索引的场景

      3.2不应创建索引的场景

    4.索引的分类与说明

      4.1主键索引

      4.2单列索引

      4.3唯一索引

      4.4复合索引

      4.5聚集索引与非聚集索引

         4.5.1聚集索引

         4.5.2非聚集索引

         4.5.3使用及语法

         4.5.4使用场景对比

      4.6聚簇索引与非聚簇索引

         4.6.1聚簇索引

         4.6.2非聚簇索引

         4.6.3Mysql的MYISAM和INNODB引擎

         4.6.4对比总结

     4.7稠密索引与稀疏索引

         4.7.1稠密索引

         4.7.2稀疏索引

    5.索引的底层原理

         5.1 B-Tree

         5.2 B+Tree

         5.3 B-树和B+树的区别

    6.总结


    1.什么是索引?为什么要用索引?

      1.1索引的含义

    数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据库中表的数据.索引的实现通常使用B树和变种的B+树(mysql常用的索引就是B+树)。除了数据之外,数据库系统还维护为满足特定查找算法的数据结构,这些数据结构以某种方式引用数据.这种数据结构就是索引!

    简言之,索引就类似于书本,字典的目录!

      1.2为什么用?

    打个比方,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢。

    一言以蔽之,合理使用索引,可以加快数据库的查询效率和提升程序性能!


    2.索引的作用与缺点

      2.1作用

       ①通过创建索引,可以在查询的过程中,提高系统的性能

       ②通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性

       ③在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间

      2.2缺点

       ①创建索引和维护索引要耗费时间,而且时间随着数据量的增加而增大

       ②索引需要占用物理空间,如果要建立聚簇索引,所需要的空间会更大

       ③在对表中的数据进行增加删除和修改时需要耗费较多的时间,因为索引也要动态地维护


    3.索引的使用场景

      3.1应创建索引的场景

       ①经常需要搜索的列上

       ②作为主键的列上

       ③经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度

       ④经常需要根据范围进行搜索的列上

       ⑤经常需要排序的列上

       ⑥经常使用在where子句上面的列上

     

      3.2不应创建索引的场景

       ①查询中很少用到的列

       ②对于那些具有很少数据值的列.比如数据表中的性别列,bit数据类型的列

       ③对于那些定义为text,image的列.因为这些列的数据量相当大

       ④当对修改性能的要求远远大于搜索性能时.因为当增加索引时,会提高搜索性能,但是会降低修改性能


    4.索引的分类与说明

      4.1主键索引

       设定为主键后数据库会自动建立索引,innodb为聚簇索引

    #随表一起建索引:
    CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
      PRIMARY KEY(id) 
    );
    #使用AUTO_INCREMENT关键字的列必须有索引(只要有索引就行)。
    CREATE TABLE customer2 (id INT(10) UNSIGNED,customer_no VARCHAR(200),customer_name VARCHAR(200),
      PRIMARY KEY(id) 
    );
    #单独建主键索引:
    ALTER TABLE customer add PRIMARY KEY customer(customer_no);  
    #删除建主键索引:
    ALTER TABLE customer drop PRIMARY KEY ;  
    #修改建主键索引:
    #必须先删除掉(drop)原索引,再新建(add)索引

     

    4.2单列索引

       一个索引只包含单个列,一个表可以有多个单列索引

    #随表一起建索引:
    CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
      PRIMARY KEY(id),
      KEY (customer_name)  
    );
    #随表一起建立的索引 索引名同 列名(customer_name)
    #单独建单值索引:
    CREATE INDEX idx_customer_name ON customer(customer_name); 
    #删除索引:
    DROP INDEX idx_customer_name ;

     

    4.3唯一索引

       索引列的值必须唯一,但允许有空值

    #随表一起建索引:
    CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
      PRIMARY KEY(id),
      KEY (customer_name),
      UNIQUE (customer_no)
    );
    #建立 唯一索引时必须保证所有的值是唯一的(除了null),若有重复数据,会报错。   
    #单独建唯一索引:
    CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
    #删除索引:
    DROP INDEX idx_customer_no on customer ;

     

    4.4复合索引

    一个索引包含多个列,在数据库操作期间,复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)

    如果一个表中的数据在查询时有多个字段总是同时出现则这些字段就可以作为复合索引,形成索引覆盖可以提高查询的效率!

    #随表一起建索引:
    CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
      PRIMARY KEY(id),
      KEY (customer_name),
      UNIQUE (customer_name),
      KEY (customer_no,customer_name)
    );
    #单独建索引:
    CREATE INDEX idx_no_name ON customer(customer_no,customer_name); 
    #删除索引:
    DROP INDEX idx_no_name  on customer ;


      4.5聚集索引与非聚集索引

         4.5.1聚集索引

      聚集索引:指索引项的排序方式和表中数据记录排序方式一致的索引。它会根据聚集索引键的顺序来存储表中的数据,即对表 的数据按索引键的顺序进行排序,然后重新存储到磁盘上。因为数据在物理存放时只能有一种排列方式,所以一个表只能有一个聚集索引。比如字典中,用‘拼音’查汉字,就是聚集索引。因为正文中字都是按照拼音排序的。而用‘偏旁部首’查汉字,就是非聚集索引,因为正文中的字并不是按照偏旁部首排序的,我们通过检字表得到正文中的字在索引中的映射,然后通过映射找到所需要的字。

    聚集索引的使用场合为: 

    • a.查询命令的回传结果是以该字段为排序依据的; 
    • b.查询的结果返回一个区间的值; 
    • c.查询的结果返回某值相同的大量结果集。 

    聚集索引会降低 insert,和update操作的性能,所以,是否使用聚集索引要全面衡量。

     

         4.5.2非聚集索引

    非聚集索引:与聚集索引相反, 索引顺序与物理存储顺序不一致。

    非聚集索引的使用场合为: 

    • a.查询所获数据量较少时; 
    • b.某字段中的数据的唯一性比较高时;

    非聚集索引必须是稠密索引

     

        4.5.3使用及语法

    create [unique] [clustered] [nonclustered] index index_name  on {tabel/view} (column[dese/asc][....n])

    注: [unique] [clustered] [nonclustered]表示要创建索引的类型,以此为唯一索引,聚集索引,和非聚集索引,当省略unique选项时,建立非唯一索引.当省略clustered,nonclustered选项时.建立聚集索引,省略nonclustered选项时,建立唯一聚集索引。

     

        4.5.4使用场景对比

    动作描述 使用聚集索引 使用非聚集索引
    列经常被分组排序 使用 使用
    返回某范围内的数据 使用 不使用
    一个或极少不同值 不使用 不使用
    小数目的不同值 使用 不使用
    大数目的不同值 不使用 使用
    频繁更新的列 不使用 使用
    外键列 使用 使用
    主键列 使用 使用
    频繁修改索引列 不使用 使用

      4.6聚簇索引与非聚簇索引

        4.6.1聚簇索引

    聚簇索引并不是一种单独的索引类型,而是一种数据存储方式将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据。

    聚簇索引的特点:

    聚簇索引具有唯一性,由于聚簇索引是将数据跟索引结构放到一块,因此一个表仅有一个聚簇索引。

    表中行的物理顺序和索引中行的物理顺序是相同的在创建任何非聚簇索引之前创建聚簇索引,这是因为聚簇索引改变了表中行的物理顺序,数据行 按照一定的顺序排列,并且自动维护这个顺序;

    聚簇索引默认是主键,如果表中没有定义主键,InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引,InnoDB 会隐式定义一个主键(类似oracle中的RowId)来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引,必须先删除主键,然后添加我们想要的聚簇索引,最后恢复设置主键即可。

     

        4.6.2非聚簇索引

    不是聚簇索引的二级索引,也叫辅助索引,都称为非聚簇索引。将数据与索引分开存储,索引结构的叶子节点指向了数据对应的位置。

     

        4.6.3Mysql的MYISAM和INNODB引擎

    因为这两种引擎对非聚簇索引和聚簇索引的使用,就是他们之间很大的一个区别。所以结合这两个引擎,再对这两种索引展开些描述就更明了了。

    在innodb中,在聚簇索引之上创建的索引称之为辅助索引,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置,而是主键值,辅助索引访问数据总是需要二次查找

    1. InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用"where id = 14"这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。
    2. 若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。(重点在于通过其他键需要建立辅助索引)

    MyISAM使用的是非聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树

     

        4.6.4对比总结

    每次使用辅助索引检索都要经过两次B+树查找,看上去聚簇索引的效率明显要低于非聚簇索引,这不是多此一举吗?聚簇索引的优势在哪?

    1.由于行数据和聚簇索引的叶子节点存储在一起,同一页中会有多条行数据,访问同一数据页不同行记录时,已经把页加载到了Buffer中(缓存器),再次访问时,会在内存中完成访问,不必访问磁盘。这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据,获得数据更快。

    2.辅助索引的叶子节点,存储主键值,而不是数据的存放地址。好处是当行数据放生变化时,索引树的节点也需要分裂变化;或者是我们需要查找的数据,在上一次IO读写的缓存中没有,需要发生一次新的IO操作时,可以避免对辅助索引的维护工作,只需要维护聚簇索引树就好了。另一个好处是,因为辅助索引存放的是主键值,减少了辅助索引占用的存储空间大小。

    注:我们知道一次io读写,可以获取到16K大小的资源,我们称之为读取到的数据区域为Page。而我们的B树,B+树的索引结构,叶子节点上存放好多个关键字(索引值)和对应的数据,都会在一次IO操作中被读取到缓存中,所以在访问同一个页中的不同记录时,会在内存里操作,而不用再次进行IO操作了。除非发生了页的分裂,即要查询的行数据不在上次IO操作的换村里,才会触发新的IO操作。

    3.因为MyISAM的主索引并非聚簇索引,那么他的数据的物理地址必然是凌乱的,拿到这些物理地址,按照合适的算法进行I/O读取,于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。(强烈的对比)

    4.不过,如果涉及到大数据量的排序、全表扫描、count之类的操作的话,还是MyISAM占优势些,因为索引所占空间小,这些操作是需要在内存中完成的。

    5.当使用主键为聚簇索引时,主键最好不要使用uuid,因为uuid的值太过离散,不适合排序且可能出线新增加记录的uuid,会插入在索引树中间的位置,导致索引树调整复杂度变大,消耗更多的时间和资源。建议使用int类型的自增,方便排序并且默认会在索引树的末尾增加主键值,对索引树的结构影响最小。而且,主键值占用的存储空间越大,辅助索引中保存的主键值也会跟着变大,占用存储空间,也会影响到IO操作读取到的数据量。


      4.7稠密索引与稀疏索引

    在了解稠密索引和稀疏索引之前我们先来了解一下什么是聚焦索引。在一个文件中,可以有多个索引,分别基于不同的搜索码。如果包含数据记录的文件按照某个指定的顺序排列,那么该搜索码对应的索引就是聚焦索引。

        4.7.1稠密索引

    在稠密索引中,文件中的每个搜索码值都对应一个索引值。也就是说,稠密索引为数据记录文件的每一条记录都设一个键-指针对。如下图所示,索引项包括索引值以及指向该搜索码的第一条数据记录的指针,即我们所说的键-指针对。

       4.7.2稀疏索引

    在稀疏索引中,只为搜索码的某些值建立索引项。也就是说,稀疏索引为数据记录文件的每个存储块设一个键-指针对,存储块意味着块内存储单元连续。如下图所示。


    5.索引的底层原理

    此节我们抛开其他的数据库索引实现,主讲Mysql的索引底层实现。说到Mysql的索引,了解过的人应该知道,其底层是通过B+数来实现的数据结构存储。

    数据存储结构,决定了数据查找和操作时的效率,包括时间复杂度和空间复杂度。而在取舍的时候,也无非就是时间换空间,空间换时间的权衡罢了。所以,这就很好的解释了,为什么Mysql在索引的底层设计上,选用了B+数,而没有选用B-树,或是红黑树,AVL树等等其他数据结构。总之,就是使用B+树作为索引的结构存储,能在I/O性能上得到一个较大的优势。

    那么具体优势在哪里呢?咱们继续往下看。

    本章我们以B-树与B+树的对比,来阐述具体差异和B+树的优势。

     

      5.1 B-Tree

    B-树是一种多路自平衡的搜索树 它类似普通的平衡二叉树,不同的一点是B-树允许每个节点有更多的子节点。B-Tree 相对于 AVLTree 缩减了节点个数,使每次磁盘 I/O 取到内存的数据都发挥了作用,从而提高了查询效率。

    注:B-Tree就是我们常说的B树

    那么m阶 B-Tree 是满足下列条件的数据结构:

    • 所有键值分布在整颗树中
    • 搜索有可能在非叶子结点结束,在关键字全集内做一次查找,性能逼近二分查找
    • 每个节点最多拥有m个子树
    • 根节点至少有2个子树
    • 分支节点至少拥有m/2颗子树(除根节点和叶子节点外都是分支节点)
    • 所有叶子节点都在同一层、每个节点最多可以有m-1个key,并且以升序排列
       

    每个节点占用一个磁盘块,一个节点上有两个升序排序的关键字和三个指向子树根节点的指针,指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例,关键字为 17 和 35,P1 指针指向的子树的数据范围为小于 17,P2 指针指向的子树的数据范围为 17~35,P3 指针指向的子树的数据范围为大于 35。

    模拟查找关键字 29 的过程:

    1. 根据根节点找到磁盘块 1,读入内存。【磁盘 I/O 操作第 1 次】
    2. 比较关键字 29 在区间(17,35),找到磁盘块 1 的指针 P2。
    3. 根据 P2 指针找到磁盘块 3,读入内存。【磁盘 I/O 操作第 2 次】
    4. 比较关键字 29 在区间(26,30),找到磁盘块 3 的指针 P2。
    5. 根据 P2 指针找到磁盘块 8,读入内存。【磁盘 I/O 操作第 3 次】
    6. 在磁盘块 8 中的关键字列表中找到关键字 29。
    7. 分析上面过程,发现需要 3 次磁盘 I/O 操作,和 3 次内存查找操作。由于内存中的关键字是一个有序表结构,可以利用二分法查找提高效率。而 3 次磁盘 I/O 操作是影响整个 B-Tree 查找效率的决定因素。

    但同时B-Tree也存在问题:

    • 每个节点中有key,也有data,而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的 key 的数量很小。
    • 当存储的数据量很大时同样会导致 B-Tree 的深度较大,增大查询时的磁盘 I/O 次数,进而影响查询效率

     

    5.2 B+Tree

    B+Tree 是在 B-Tree 基础上的一种优化,InnoDB 存储引擎就是用 B+Tree 实现其索引结构。它带来的变化点:

    • B+树每个节点可以包含更多的节点,这样做有两个原因,一个是降低树的高度。另外一个是将数据范围变为多个区间,区间越多,数据检索越快
    • 非叶子节点存储key,叶子节点存储key和数据
    • 叶子节点两两指针相互链接(符合磁盘的预读特性),顺序查询性能更高

    注:MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的,因此力求达到树的深度不超过 3,也就是说 I/O 不需要超过 3 次。

    通常在B+Tree上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找

     

    5.3 B-树和B+树的区别

    • B+树内节点不存储数据,所有数据存储在叶节点导致查询时间复杂度固定为 log n
    • B-树查询时间复杂度不固定,与 key 在树中的位置有关,最好为O(1)
    • B+树叶节点两两相连可大大增加区间访问性,可使用在范围查询等
    • B+树更适合外部存储(存储磁盘数据)。由于内节点无 data 域,每个节点能索引的范围更大更精确。
       

    6.总结

        本章内容其实是我个人对索引这块的知识点的巩固与梳理。之前有很多总结,比较零散,趁当下有点时间,所以整理了出来。其中还有很多不足,或是有瑕疵的地方,若有不对之处,尽情拍砖指正。最后,也希望能够帮助到,正在学习的你,加油!

     

    本博客皆为学习、分享、探讨为本,欢迎各位朋友评论、点赞、收藏、关注,一起加油!

     

    展开全文
  • MySQL 中的 B-Tree 索引的物理文件大多都是以 B+tree的结构来存储的,也就是所有实际需要的数据都存放于 Tree 的 Leaf Node,而且到任何一个 Leaf Node 的最短路径的长度都是完全相同的,可能各种数据库(或 ...

    索引B-Tree:

    一般来说, MySQL 中的 B-Tree 索引的物理文件大多都是以 B+tree的结构来存储的,也就是所有实际需要的数据都存放于 Tree 的 Leaf Node,而且到任何一个 Leaf Node 的最短路径的长度都是完全相同的,可能各种数据库(或 MySQL 的各种存储引擎)在存放自己的 B-Tree 索引的时候会对存储结构稍作改造。如 Innodb 存储引擎的 B-Tree 索引实际使用的存储结构实际上是 B+Tree ,也就是在 B-Tree 数据结构的基础上做了很小的改造,在每一个Leaf Node 上面出了存放索引键值和主键的相关信息之外,B+Tree还存储了指向与该 Leaf Node 相邻的后一个 LeafNode 的指针信息,这主要是为了加快检索多个相邻 Leaf Node 的效率考虑。

    B-Tree对索引列是顺序组织存储的,所以很适合查找范围数据,例如,在一个基于文本域的索引树上,按字母顺序传递连续的值进行查找是非常合适的,所以像 “找出所有以 A 到 K 开头的名字” 这样的查找效率会非常高。

    因为索引树中的节点是有序的,所以除了按值查找之外,索引还可以用于查询中的ORDER BY(按顺序查找),GROUP BY(按分组查找)操作。一般来说,如果 B-Tree 可以按照某种方式查找到值,那么也可以按照这种方式用于排序。所以,索引对 ORDER BY 子句也可以满足对应的排序需求。

    在innodb引擎中,btree索引分为两种,1,聚簇索引(主键索引),或者说叫聚集索引,因为数据的逻辑顺序与物理顺序都是紧凑的。2.二级索引(非聚簇索引),或者说叫辅助索引。InnoDB中的主键索引是聚集索引,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录(整行数据)。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主键索引。但是innodb的二级索引,保存的是索引列值以及指向主键的指针,所以我们使用覆盖索引的做优化处理就是针对mysql的innodb的索引而言的。

    下面两张图显示mysql中innodb和myisam引擎的索引实现的原理

    看上去聚簇索引的效率明显要低于非聚簇索引,因为每次使用辅助索引检索都要经过两次B+树查找,这不是多此一举吗?聚簇索引的优势在哪?

    1. 由于行数据和叶子节点存储在一起,这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据,获得数据更快。
    2. 辅助索引使用主键作为"指针" 而不是使用行地址值作为指针的好处是,减少了当出现行移动或者数据页分裂时辅助索引的维护工作,使用主键值当作指针会让辅助索引占用更多的空间,换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针",使用聚簇索引可以保证不管这个主键B+树的节点如何变化,辅助索引树都不受影响。

    关于 InnoDB,索引和锁有一些很少有人知道的细节:InnoDB 在二级索引上使用共享(读)锁,但访问主键索引需要排他(写)锁。这消除了使用覆盖索引的可能性,并且使得 SELECT FOR UPDATE 比 LOCK IN SHARE MODE 或非锁定查询要慢很多。

    啊哈哈,概念扯多了。

    你需要知道的:

    1. 不要求每个人一定理解 联表查询(join/left join/inner join等)时的mysql运算过程,但对于字段选择性差意味着什么,组合索引字段顺序意味着什么,要求每个人必须了解;
    2. 把mysql客户端(如SQLyog,如HeidiSQL)放在桌面上,时不时拿出来 explain 一把,这是一种美德!
    3. 确保亲手查过SQL的执行计划,一定要注意看执行计划里的 possible_keys、key和rows这三个值,让影响行数尽量少,保证使用到正确的索引,减少不必要的Using temporary/Using filesort;
    4. 不要在选择性非常差的字段上建索引,原因参见优化策略A;
    5. 查询条件里出现范围查询(如A>7,A in (2,3))时,要警惕,不要建了组合索引却完全用不上,原因参见优化策略B;

     

    ——字段选择性的基础知识——

    引子:什么字段都可以建索引吗?

    如下表所示,sort 字段的选择性非常差,你可以执行 show index from ads 命令可以看到 sort 的 Cardinality(散列程度)只有 9,这种字段上本不应该建索引:

    Table

    Non_unique

    Key_name

    Seq_in_index

    Column_name

    Collation

    Cardinality

    Sub_part

    Packed

    Null

    Index_type

    Comment

    ads

    1

    sort

    1

    sort

    A

    9

    \N

    \N

     

    BTREE

     

     

    优化策略A:字段选择性

    • 选择性较低索引 可能带来的性能问题
      • 索引选择性=索引列唯一值/表记录数;
      • 选择性越高索引检索价值越高,消耗系统资源越少;选择性越低索引检索价值越低,消耗系统资源越多;
    • 查询条件含有多个字段时,不要在选择性很低字段上创建索引
      • 可通过创建组合索引来增强低字段选择性和避免选择性很低字段创建索引带来副作用;
      • 尽量减少possible_keys,正确索引会提高sql查询速度,过多索引会增加优化器选择索引的代价,不要滥用索引;

     

    ——组合索引字段顺序与范围查询之间的关系——

    引子:范围查询 city_id in (0,8,10) 能用组合索引 (ads_id,city_id) 吗?

    举例,

    ac 表有一个组合索引(ads_id,city_id)。

    那么如下 ac.city_id IN (0, 8005) 查询条件能用到 ac表的组合索引(ads_id,city_id) 吗?

    EXPLAIN

    SELECT ac.ads_id

    FROM ads,  ac

    WHERE

          ads.id = ac.ads_id

          AND ac.city_id IN (0, 8005) 

          AND ads.status = 'online'

          AND ac.start_time<UNIX_TIMESTAMP()

          AND ac.end_time>UNIX_TIMESTAMP()

    优化策略B:

    由于 mysql 索引是基于 B-Tree 的,所以组合索引有“字段顺序”概念。

    所以,查询条件中有 ac.city_id IN (0, 8005),而组合索引是 (ads_id,city_id),则该查询无法使用到这个组合索引。

    DBA总结道:

    组合索引查询的各种场景

    兹有 Index (A,B,C) ——组合索引多字段是有序的,并且是个完整的BTree 索引。

    • 下面条件可以用上该组合索引查询:
      • A>5
      • A=5 AND B>6
      • A=5 AND B=6 AND C=7
      • A=5 AND B IN (2,3) AND C>5
    • 下面条件将不能用上组合索引查询:
      • B>5 ——查询条件不包含组合索引首列字段
      • B=6 AND C=7 ——查询条件不包含组合索引首列字段
    • 下面条件将能用上部分组合索引查询:
      • A>5 AND B=2 ——当范围查询使用第一列,查询条件仅仅能使用第一列
      • A=5 AND B>6 AND C=2 ——范围查询使用第二列,查询条件仅仅能使用前二列

     

    组合索引排序的各种场景

    兹有组合索引 Index(A,B)。

    • 下面条件可以用上组合索引排序:
      • ORDER BY A——首列排序
      • A=5 ORDER BY B——第一列过滤后第二列排序
      • ORDER BY A DESC, B DESC——注意,此时两列以相同顺序排序
      • A>5 ORDER BY A——数据检索和排序都在第一列
    • 下面条件不能用上组合索引排序:
      • ORDER BY B ——排序在索引的第二列
      • A>5 ORDER BY B ——范围查询在第一列,排序在第二列
      • A IN(1,2) ORDER BY B ——理由同上
      • ORDER BY A ASC, B DESC ——注意,此时两列以不同顺序排序

     

    顺着组合索引怎么建继续往下延伸,请各位注意“索引合并”概念:

    • MySQL 5,0以下版本,SQL查询时,一张表只能用一个索引(use at most only one index for each referenced table),
    • 从 MySQL 5.0开始,引入了 index merge 概念,包括 Index Merge Union Access Algorithm(多个索引并集访问),包括Index Merge Intersection Access Algorithm(多个索引交集访问),可以在一个SQL查询里用到一张表里的多个索引。
    • MySQL 在5.6.7之前,使用 index merge 有一个重要的前提条件:没有 range 可以使用。

    索引合并的简单说明:

    • MySQL 索引合并能使用多个索引
      • SELECT * FROM TB WHERE A=5 AND B=6
        • 能分别使用索引(A) 和 (B) 或 索引合并;
        • 创建组合索引(A,B) 更好;
      • SELECT * FROM TB WHERE A=5 OR B=6
        • 能分别使用索引(A) 和 (B) 或 索引合并;
        • 组合索引(A,B)不能用于此查询,分别创建索引(A) 和 (B)会更好;
        • 或者使用 UNION ALL,SELECT * FROM TB WHERE A=5 UNION ALL SELECT * FROM TB WHERE A=6

    优化 LIMIT 分页:

    • 优化大偏移量的性能,尽可能地使用索引覆盖扫描,而不是查询所有的列,然后根据需要做一次关联操作再返回所需的列
    • 如“延迟关联”:SELECT * FROM TB INNER JOIN (SELECT id FROM TB ORDER BY id LIMIT 50,5) AS TB1 USING(id)
    • 有时候也可以将 LIMIT 查询转换为已知位置的查询,让 MySQL 通过范围扫描获得对应的结果
    • LIMIT 和 OFFSET 的问题,其实是 OFFSET 的问题,它会导致 MySQL 扫描大量不需要的行然后再抛弃掉。所以我们应该尽可能地避免这种大量扫描行的行为来优化分页查询

    最后的总结:

    仍然是强调再强调:

    记住,explain 后再提测是一种美德!

    关注公众号,分享干货,讨论技术

    展开全文
  • 数据库索引

    万次阅读 多人点赞 2018-11-11 09:27:25
    数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据库中表的数据.索引的实现通常使用B树和变种的B+树(mysql常用的索引就是B+树) 除了数据之外,数据库系统还维护为满足特定查找算法的数据...
  • 前面两篇文章 《解析B+树比B树更加适合做数据库索引的原因 》 和《从底层解析B+索引提高查询速度的原因》是从数据结构的角度分析了B+索引,并分别介绍了B+索引在两个主流存储引擎InnoDB和MyISAM中的实现。...
  • 索引的数据结构分析,数据库面试到索引最常见的问题分析,我总结了一下。
  • 数据库索引实例

    千次阅读 2018-08-15 17:10:45
    参考文献: [1].漫谈数据库索引 1.创建表并插入数据 在Sql Server2008中创建测试数据库Test,接着创建数据库表并插入数据,sql代码如下: USE Test IF E
  • 常用数据库字段类型及大小

    万次阅读 2016-05-21 12:01:06
    racle/MSSQL/Mysql 常用数据库字段类型及大小 ORACLE的数据类型 常用的数据库字段类型如下: 字段类型 中文说明 限制条件 其它说明 CHAR 固定长度字符串 最大长度2000 bytes ` VARCHAR2 可变长度的字符串...
  • 数据库索引简介

    千次阅读 2019-04-17 14:29:11
    建立的索引只对该字段有用,如果查询的字段改变,那么这个索引也就无效了,比如图书馆的书是按照书名的第一个字母排序的,那么你想要找作者叫张三的就不能用该索引了; 如果索引太多也会降低查询的速度 3、索引是...
  • 数据库索引学习

    千次阅读 2015-07-31 01:11:55
    数据库索引理论原理学习,为什么加索引速度更快,IO读更快操作更少,加索引注意事项,哪些场景下索引可以用到或者用不到
  • 原因:列的字段越大,建立索引时所需要的空间也就越大,这样一页中所能存储的索引节点的数量也就越少也越少,在遍历时所需要的 IO 次数也就越多,索引的性能也就越差。 方法a. 将字符串转换成数字类型存储,如:将 IP...
  • 本文皆学习自文内链接所指的文章,...包含多个字段数据库索引,比如INDEX idx_test(col_a, col_b)。这种包含多个字段的索引就被称为**“联合索引”**。 1.1.2 新华字典中的“联合索引” 新华字典里有一种目录被...
  • 数据库索引:联合索引基本知识

    千次阅读 2015-03-27 11:26:06
    数据库索引:联合索引基本知识
  • MySQL 运维 - 数据库索引 | 超详细

    千次阅读 2021-04-09 14:02:21
    - 数据库索引 | 超详细一、数据库索引二、索引的作用三、索引的副作用四、创建索引的原则依据五、索引的分类六、索引的创建方法① 普通索引② 唯一索引③ 主键索引④ 组合索引⑤ 全文索引七、查看索引① 各个字段的...
  • MySQL数据库索引

    万次阅读 多人点赞 2018-09-23 09:31:41
    数据库有哪些索引 唯一索引 聚簇索引与非聚簇索引 全文索引 使用索引一定能提高查询性能吗? 哪些情况下设置了索引但是无法使用 哪些情况下需要设置索引、哪些情况下不需要 什么情况下应该使用组合索引而非...
  • MySQL数据库索引问题

    千次阅读 2016-05-24 10:41:15
    一个索引会包含表中按照一定顺序排序的一列或多列字段数据库对象索引其实与书的目录非常类似,主要是为了提高从表中检索数据的速度。由于数据存储在数据库表中,所以索引是创建在数据库表对象上的,由表中的一个...
  • 常用的数据库字段类型及大小

    万次阅读 多人点赞 2018-08-28 08:30:15
    Oracle/MSSQL/Mysql 常用数据库字段类型及大小     ORACLE的数据类型  常用的数据库字段类型如下:  字段类型 中文说明 限制条件 其它说明  CHAR 固定长度字符串 最大长度2000 bytes `  VARCHAR2 可...
  • 深入理解数据库索引

    千次阅读 2019-02-14 07:19:17
    前言:数据库和数据库索引这两个东西是在服务器端开发领域应用最为广泛的两个概念,熟练使用数据库和数据库索引是后端开发人员在行业内生存的必备技能。数据库索引是用来提高数据库表的数据查询速度的。 一、索引...
  • MySql数据库索引介绍

    千次阅读 多人点赞 2019-05-27 17:08:27
    数据库索引对我们来说是透明的,因为数据库表创建索引前后,SQL语句都可以正常运行,索引的运用只是数据库引擎工作时候的优化手段。但是,这并不是说数据库索引仅仅是数据库设计开发人员和运维人员的事情,对于一个...
  • 数据库索引的优缺点

    千次阅读 2018-10-18 22:03:56
    ④ 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序 索引的缺点: ① 创建索引和维护索引需要时间成本,这个成本随着数据量的增加而加大 ② 创建索引和维护索引需要空间成本,每一条索引都要占据数据库的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 146,395
精华内容 58,558
关键字:

数据库索引字段的顺序