work hard work smart

专注于Java后端开发。 不断总结,举一反三。
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

数据库优化索引

Posted on 2020-01-12 21:19  work hard work smart  阅读(141)  评论(0编辑  收藏  举报

为什么要使用索引?

索引可以避免全表扫描去查找索引,提升检索效率。

 

什么样的信息能成为索引?

主键,唯一键等能区分数据唯一性的字段都能成为索引。

 

索引的数据结构?

主流是B+树,还有Hash,Bitmap。其中MySQL数据库不支持Bitmap索引。

 

密集索引和哈希索引的区别?

密集索引稀疏索引 

 

索引的优化

1、二叉查找树

 左侧的节点比右侧的节点小。

 时间复杂度O(logn)

 

平衡二叉搜索树

每个节点的左子树和右子树的高度差不超过1

对于n个节点,树的深度是log2n, 查询的时间复杂度是O(log2n)  例如64个节点,最多只要查询6次。

 

 

 

2、B-Tree

定义:

根节点至少包括两个孩子

树种每个节点最多含有m个孩子(m>=2)

除根节点和叶子节点,其它每个节点至少有ceil(m/2) 个孩子

所有叶子节点都位于同一层 

 

3、B+-Tree

B+树是B树的变体,其定义基本与B树相同,除了

非叶子节点的子树指针与关键字个数相同。

非叶子节点的子树指针P[i], 指向关键字(K[i],k[i+1])的字树

非叶子节点仅用来索引,数据都保存在叶子节点中

所有叶子节点均有一个链指针指向下一个叶子节点

 

 

结论:

B+树更适合用来做索引

B+树对磁盘读写代价更低

B+数的查找效率更加稳定

B+数更有利于对数据库的扫描

 

B-Tree和B+Tree的区别?

1) B+Tree  有n个子节点的节点中含有n个关键字

B-Tree是n个子节点有n-1个关键字

2) B+Tree中,所有的叶子节点中包含了全部关键字的信息,且叶子节点按照关键字的大小自小到大顺序链接,构成一个有序链表

B-Tree的叶子节点不包括全部关键字。

3) B+Tree中,非叶子节点仅用于索引,不保存数据,记录放在叶子节点中。

B-Tree中,非叶子节点既保存索引,也保存数据记录。

 

B-Tree(B+Tree)特性

完全匹配   index(name)   where name = '张三'

范围匹配  index (age)   where age > 34

前缀匹配   index(name)    where name like '张%'   。 右模糊可以使用索引,左模糊不能使用索引,如'%三'

 

B-Tree(B+Tree)限制?

index(name, age, sex)

 1) 查询条件不包括最左列,无法使用索引。

     where   age = 34 and sex = 1 无法使用索引。

2) 跳过了索引中的列,则无法完全使用索引

where  name = ‘张三’   and   sex = 32        跳过了age,只能使用name索引这一列。

3) 查询中某个列的范围(模糊) 查询,则其右边所有列都无法使用索引

   where name=‘张三’  and  age > 32 and sex = 1   ,只能用name,age两列。

 

 

MySQL数据引擎存储方式

1) InnoDB存储方式

B+Tree

主键索引: 叶子节点存储主键及数据

非主键索引(二级索引、辅助索引): 叶子节点存储索引以及主键。

 

2) MyISAM存储方式

B+Tree

主键/非主键索引的叶子节点都是存储指向数据块的指针。

 

总结:

InnoDB称为聚簇索引

MyISAM称为非聚簇索引。

 

 

 4、Hash索引

 

 Hash索引特性(缺点):

仅能满足“=”,“IN”,不能使用范围查询,模糊查询

无法被用来避免数据的排序操作。Hash索引并不是按照索引值排序,所以没法使用排序

不能利用部分索引键查询。  hash(a,b)   a,b两列的hash所有, where a = 1,只有一列a,

不能避免表扫描

遇到大量Hash值相等的情况后性能不一定就会比B-Tree索引高。一般性能比B-Tree(B+Tree) 要好一些。

 Hash冲突越严重,性能下降越厉害。

 

 

 

 

5、BitMap索引(位图索引)(使用较少)

 

6、空间索引(R-Tree索引) 使用较少

存储GIS数据,基于R-Tree

MySQL 5.7 开始InnoDB支持空间索引。

 

7、全文索引

适应全文搜索的需求

MySQL 5.7之前,全文索引不支持中文,经常搭配Sphinx。

MySQL 5.7 起,内置ngram, 支持中文。

全文索引目前用的不多,主要使用ES等搜索引擎。

 

8、创建索引的原则(原则只是参数,还要根据实际情况)

哪些场景建议创建索引

哪些场景不建议创建索引。

1) 建议创建索引的场景

select 语句,频繁作为where条件的字段

update/delete语句的where条件

需要分组、排序的字段

distinct所使用的字段

字段值有唯一性约束

对于多表查询,联接字段应创建索引,且类型务必保持一致,避免隐式转换。

 

2) 不建议创建索引的场景

where字句里用不到的字段

表的记录非常少

有大量重复数据,选择性低。如性别字段 (索引的选择性越高,查询效率越好,因为可以在查找的过滤更多行)

频繁更新的字段,如果创建索引要考虑其索引维护开销。(如修改/删除某条数据,相应的要更新索引)

 

9、可能导致索引失效的场景

1) 索引列不独立。独立是指: 列不能是表达式的一部分,也不能是函数的参数

select * from employees where emp_no +1 = 10003。 这里列emp_no是表达式的一部分,不能使用索引。

解决方法:  select * from employees where emp_no = 10002

 

select * from employees where SUBSTRING(first_name, 1, 3 ) = 'Geo' (first_name是索引列)

解决方法: 预先计算好结果,再传过来,在where条件的左侧,不要使用函数;或者使用等价

select * from employees where first_name like 'Geo%' ;

 

2) 使用了左模糊

select * from employees where first_name like '%Geo%' ;

解决方法: 尽量避免使用左模糊。如果避免不了,可以考虑搜索引擎去解决。

 

3) 使用OR查询的部分字段没有索引

select  *  from employees where  first_name = 'Georgi'  or  last_name = 'Georgi'   first_name是索引字段,last_name 不是索引字段。这条SQL不能使用索引。

解决方法: 将last_name 字段添加成索引。

 

4) 字符串条件未使用'' 引起来。

explain select * from dept_emp where dept_no = 3;

dept_no  是字符型,这里 3没有加单引号, 不能使用索引。 type为All

 

解决方法: 规范的编写SQL

 explain select * from dept_emp where dept_no = '3';

 type为ref,可以使用索引。

 

 

5) 不符合最左前缀原则测查询 

explain select * from employees where first_name = 'Facello'

索引为Index(last_name, first_name)   last_name在前面,此时无法使用索引。

解决方法: 将索引的顺序倒一下, 索引为Index( first_name,last_name)。或者Index( first_name)

 

6) 索引字段建议添加NOT NULL约束

单列索引无法储null值,符合索引无法储全为null的值

查询时,采用is null条件时,不能利用到索引,只能全表扫描

explain select * from  users where mobile is null。 (mobile可以允许为空,这条SQL不能使用索引)

解决方法: 把索引字段设置成Not null, 甚至可以把所有字段都设置成not null,并为字段设置默认值。

 

7) 隐式转换导致索引失效。

 where a.Id = b.Id   a表id是int,b表id为varchar,则存在隐式转换

解决方法: 创建表的时候要规范,统一为int或varchar。

作者:Work Hard Work Smart
出处:http://www.cnblogs.com/linlf03/
欢迎任何形式的转载,未经作者同意,请保留此段声明!