黄元昌 / 智慧配置 / Java数据库之索引

分享

   

Java数据库之索引

2019-08-10  黄元昌

索引:

索引是一种提高查询效率的数据结构(B树或者是哈希结构);索引是创建在数据库表中,是对数据库表中的一列或者多列的值进行排序的一个结果,好处就是提高查询效率

一般情况下,一次查询只用一个索引;

索引的分类:

  • 普通索引:没有任何限制,可以给任意字段创建普通索引;

  • 唯一性索引:使用unique修饰的字段,值不能重复的,主键索引就属于唯一性索引;

  • 主键索引:使用primary key修饰的字段自动创建主键索引;

  • 单列索引:在一个字段上创建索引 ;

  • 多列索引:在多个字段上创建索引;

  • 全文索引:使用fulltext参数可以设置全文索引,只支持char\varchar\text类型的字段上,常用于数据量较大的字符串类型上;

索引的创建和删除的SQL语句:

(一)索引的创建

在创建表的时候指定索引

create table table_name( id int, name varchar(20), index(id) );

在已经创建好的表上添加索引

create [unique|fulltext|spatial] index idx_id(索引名) on 表名(id属性名);alter table 表名 add [unique|fulltext|spatial] index index_name; //第二种方法

(二)索引的删除

drop index index_name(索引名) on 表名

索引执行过程分析:

使用explain关键分析查询SQL

 explain select * from Student where Sname like 'zhaolei'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: Student type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: Using where possible_keys:表示SQL执行可能会命中的索引有哪些; key:表示执行过程真正使用的索引名称 rows:表示查询影响的数据行数 当前查询possible_keys\key都为null,则未命中索引 rows=4表示当前查询操作对每一行数据都进行比较  添加索引后,分析执行过程 explain select * from Student where Sname like 'zhaolei'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Student type: range possible_keys: idx_name key: idx_name key_len: 27 ref: NULL rows: 1 Extra: Using index condition 1 row in set (0.01 sec)通过explain关键分析查询SQL,可以看出当前查询命中索引idx_name, rows: 1表示通过一条记录就查询到了结果

索引的底层原理

mysql支持两种索引,一种是B树索引,一种是哈希表索引;

问:数据库中常见的慢查询的优化方式是什么?

答:加索引;

问:问什么加索引可以优化查询?

答:因为能减少磁盘IO;

问:怎么减少磁盘IO的?

答:索引是一种优化查询的数据结构,比如在MySQL中用到的B+树,这种数据结构是可以优化查询的,所以我们可以利用索引来快速的查找数据;

问:那你知道哪些数据结构可以提高查询速度吗?

答:红黑树,二叉树,哈希表,B树(B-树),B+树等;

问:那为什么MySQL使用B+树呢?

答:如下:

MYSQL InnoDB存储引擎,基于B-树(实际MYSQL采用的是B+树)的索引结构。B-树是一种m阶平衡树,叶子节点都在同一层,由于每一个节点存储的数据量比较大,所以整个B-树的层数是非常低的,基本上不超过三层;

由于磁盘的读取也是按block块操作的(内存是按page页面操作的),因此B-树的节点大小一般设置为和磁盘块大小一致,这样一个B-树节点,就可以通过一次磁盘I/O把一个磁盘块的数据全部存储下来,所以当使用B-树存储索引的时候,磁盘I/O的操作次数是最少的(MySQL的读写效率,主要集中在磁盘I/O上)。

那么MySQL最终为什么要采用B+树存储索引结构呢,那么看看B-树和B+树在存储结构上有什么不同?

  • B-树的每一个节点,存了关键字和对应的数据地址,而B+树的非叶子节点只存关键字,不存数据地址。因此B+树的每一个非叶子节点存储的关键字是远远多于B-树的,B+树的叶子节点存放关键字和数据,因此,从树的高度上来说,B+树的高度要小于B-树,使用的磁盘I/O次数少,因此查询会更快一些。

  • B-树由于每个节点都存储关键字和数据,因此离根节点近的数据,查询的就快,离根节点远的数据,查询的就慢;B+树所有的数据都存在叶子节点上,因此在B+树上搜索关键字,找到对应数据的时间是比较平均的,没有快慢之分。

  • 在B-树上如果做区间查找,遍历的节点是非常多的;B+树所有叶子节点被连接成了有序链表结构,因此做整表遍历和区间查找是非常容易的。

哈希索引当然是由哈希表实现的,哈希表对数据并不排序,因此不适合做区间查找,效率非常低,需要搜索整个哈希表结构。

MySQL数据库的存储引擎MyISAM和InNoDB的索引结构

聚集索引和非聚集索引:聚集就是索引和数据存放在一个文件里面,非聚集索引就是索引和数据分别存放在两个文件里面;

MyISAM存储引擎(非聚集索引)

MyISAM引擎使用B+树作为索引结构、叶节点的数据域存放的是数据地址,在MyISAM引擎中,主索引和辅助索引在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复,如果给其他字段创建辅助索引,结构图如下:

Java数据库之索引

根据上面两张图,首先按照B+树搜索算法搜索索引,如果指定的key存在,则取出其数据域的值,然后以数据域的值为地址,读取相应的数据记录;

InNoDB存储引擎(聚集索引)

InNoDB存储引擎的主键索引,叶子节点中,索引关键字和数据是在一起存放的,如图:

Java数据库之索引

可以看到,索引关键字和数据存储在叶子节点中;

InNoDB辅助索引,叶子节点存放的是索引关键字和对应的主键(为了一致性和节省存储空间):

Java数据库之索引

辅助索引的B+树,先根据关键字找到对应的主键,再去主键索引树上找到对应的行记录数据,从索引树上可以看到,InNoDB的索引关键字和数据都是在一起存放的,体现在磁盘存储上,例如创建一个user表,在磁盘上只存储两种结构,user.frm(存储表的结构),user.idb(存储索引和数据);

联合索引

就是先根据第一个键排序,第一个键相同的话,按第二个键排序……

Java数据库之索引

索引的优化

  1. 索引的优化,主要就是分析索引在哪些情况下会失效的问题:

  2. 在where后使用or,导致索引失效(尽量少用or);

  3. 使用like ,like查询是以%开头;

  4. 复合索引遵守“最左前缀”原则,即在查询条件中使用了复合索引的第一个字段,索引才会被使用;

  5. 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引;

  6. 使用in导致索引失效;

  7. DATE_FORMAT()格式化时间,格式化后的时间再去比较,可能会导致索引失效;

  8. 对于order by、group by 、 union、 distinc 中的字段出现在where条件中时,才会利用索引!

总结下索引的优化:

  • 最左前缀匹配原则;

  • 主键外键一定要建索引;

  • 对 where,on,group by,order by 中出现的列使用索引;

  • 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0;

  • 对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键;

  • 索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

  • 为较长的字符串使用前缀索引;

  • 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可;

  • 不要过多创建索引, 权衡索引个数与DML之间关系,DML也就是插入、删除数据操作。这里需要权衡一个问题,建立索引的目的是为了提高查询效率的,但建立的索引过多,会影响插入、删除数据的速度,因为我们修改的表数据,索引也需要进行调整重建;

  • 对于like查询,”%”不要放在前面。

SELECT * FROMhoudunwangWHEREunameLIKE'后盾%' -- 走索引 SELECT * FROMhoudunwangWHEREunameLIKE '%后盾%' -- 不走索引
  • 查询where条件数据类型不匹配也无法使用索引

  • 字符串与数字比较不使用索引;

CREATE TABLEa(achar(10)); EXPLAIN SELECT * FROMaWHEREa='1' – 走索引 EXPLAIN SELECT * FROM a WHERE a=1 – 不走索引 
  • 正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因;

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多
    喜欢该文的人也喜欢 更多

    ×
    ×

    ¥.00

    微信或支付宝扫码支付:

    开通即同意《个图VIP服务协议》

    全部>>