数据库索引

引言

说白了,数据库的索引问题就是查找问题

数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据库中表的数据.索引的实现通常使用B树和变种的B+树(mysql常用的索引就是B+树)

除了数据之外,数据库系统还维护为满足特定查找算法的数据结构,这些数据结构以某种方式引用数据.这种数据结构就是索引

创建索引的好处

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

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

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

创建索引的坏处

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

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

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

应该在哪些列上创建索引呢

①经常需要搜索的列上

②作为主键的列上

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

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

⑤经常需要排序的列上

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

不应该在哪些列上创建索引

①查询中很少用到的列

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

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

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

索引的分类和使用

按物理存储角度分:

聚集索引

表记录的排列顺序和索引的排列顺序一致,所以查询效率快,只要找到第一个索引值记录,其余连续性的记录在物理上一样连续存放.聚集索引的缺点就是修改慢,因为为了使表记录和索引的排列顺序一致,在插入记录的时候,会对数据页重新排序

非聚集索引

表记录和索引的排列顺序不一定一致,两种索引都采用B+树的结构,非聚集索引的叶子层并不和实际数据页相重叠,而采用叶子层包含一个指向表记录的指针.非聚集索引层次多,不会造成数据重排

按逻辑角度分

2)普通索引,最基本的索引,它没有任何的限制

4)复合索引(又叫做多列索引,联合索引):多个字段上建立的索引,提高复合条件查询的速度

创建联合索引:create index idx_name_age on student(name,age);

查看索引:show index from student;

数据库索引在什么情况下失效

(1)条件中用or(这就是为什么少用or的原因)

(2)

对于多列(复合、联合)索引,不是使用的第一部分,则不会使用索引。(最左匹配原则或者叫做最左前缀原则)

比如:Index_SoftWareDetail索引包含(a,b,c) 三列,但是查询条件里面,没有a,b 列,只有c 列,那么 Index_SoftWareDetail索引也不起作用

例如:bc   c   acb bac 都是不行的

(3)like的模糊查询以%开头,索引失效

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

(5)如果MySQL预计使用全表扫描要比使用索引快,则不使用索引

(6)判断索引列是否不等于某个值时。‘!=’操作符。比如:select * from SoftWareDetailInfo where SoftUseLine != 0

(7)

对索引列进行运算。这里运算包括+-*/等运算。也包括使用函数。比如:

select * from SoftWareDetailInfo where SoftUseLine +0= 0

此时索引不起作用。

select * from SoftWareDetailInfo where count(SoftUseLine) = 0

此时索引也不起作用。

也就是说如果不是直接判断索引字段列,而是判断运算或其它函数处理后的索引列索引均不起作用。

(8)索引字段进行判空查询时。也就是对索引字段判断是否为NULL时。语句为is null 或is not null。

  比如:select * from SoftWareDetailInfo where CreateTime is null 此时就不检索time字段上的索引表了。也就是索引在这条语句执行时失效了。

  接着再执行

select * from SoftWareDetailInfo where CreateTime = '2015-04-11 00:00:00' 此时就会检索索引表了。索引又起作用了。

(9)范围列可以用到索引(联合索引必须是最左前缀),但是范围列后面的列无法用到索引

索引的优化

①尽量不要使用左模糊和全模糊,如果需要可以使用搜索引擎来解决

②union,in和or都可以命中索引,建议使用in

③负向条件查询不能使用索引,可以优化为in查询

负向条件查询有:!=  <>  not in  not like等等

例如:select * from user where status!=1 and status!=2

优化为:select * from user where status in (0,3,4);

④合理使用联合索引的最左前缀原则

如果在(a,b,c)三个字段上建立联合索引,那么它能够加快 a | (a,b) | (a,b,c) 三组查询速度。

比如说把(username,password)建立了联合索引,因为业务上几乎没有password的单条件查询,而有很多username的单条件查询需求,所以应该建立(username,password)的联合索引,而不要建立(password,username)的联合索引

注意:(1)建立联合索引的时候,要把查询频率较高的列放在最左边

(2)如果建立了(a,b)索引,就不必再独立建立a索引。同理如果建立了(a,b,c)联合索引,就不必再独立建立a,(a,b)索引

(3)存在非等号和等号混合判断条件时,在建索引时,请把等号条件的列前置。如     where a>? and b=?,那么即使 a 的区分度更高,也必须把 b 放在索引的最前列。

(4)最左前缀原则,并不是要求where后的顺序和联合索引的一致。下面的 SQL 语句也可以命中 (login_name, passwd) 这个联合索引。

  • selectuid, login_time from user where passwd=? andlogin_name=?

但还是建议 where 后的顺序和联合索引一致,养成好习惯。

⑤把计算放到业务层而不是数据库层。(因为对索引列进行运算,不能命中索引)

⑥表数据比较少、更新十分频繁、数据区分度不高的字段上不宜建立索引。

一般区分度在80%以上的时候就可以建立索引,区分度可以使用 count(distinct(列名))/count(*) 来计算。

⑦强制类型转换会全表扫描

例如:如果phone字段是varchar类型,则下面的sql不能命中索引

select * from user where phone = 18838003017

可以优化为:select * from user where phone = ‘18838003017’

⑧利用覆盖索引进行查询操作,避免回表

select uid,login_time from user where username=? and password=?

如果建立了(username,password,login_time)的联合索引,由于login_time已经建立在索引中了,被查询的username和password就不用去row上获取数据了,从而加速查询

⑨在order by和group by中要注意索引的有序性

如果order by是组合索引的一部分,应该将该字段放在组合索引的最后

例如:where a=? and b=? order by c ->可以建立联合索引(a,b,c)

如果索引中有范围查找,则索引的有序性无法利用

例如:where a>10 order by b ->索引(a,b)无法排序

⑩建立索引的列,不许为null

单列索引不存 null 值,复合索引不存全为 null 的值,如果列允许为 null,可能会得到“不符合预期”的结果集,所以,请使用 not null 约束以及默认值。

sql语句的优化

①能用到索引尽量用到索引.对索引的优化实际上就是sql语句的调优

②任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

③尽量使用where,而不要使用having

④尽量使用多表查询,不要使用子查询

⑤where后的and.or左右执行顺序是从右至左

运算符为and时--尽量把为假的放在右边

运算符为or时--尽量把为真的放在右边

 

 

发布了100 篇原创文章 · 获赞 78 · 访问量 14万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览