引言
说白了,数据库的索引问题就是查找问题
数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据库中表的数据.索引的实现通常使用B树和变种的B+树(mysql常用的索引就是B+树)
除了数据之外,数据库系统还维护为满足特定查找算法的数据结构,这些数据结构以某种方式引用数
创建索引的好处
1:通过创建索引,可以在查询的过程中,提高系统的性能
2:通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
3:在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间
创建索引的坏处
1:创建索引和维护索引需要消耗时间,而且随着数据量的增大而增加;
2:索引需要占用物理空间,创建立聚簇索引,所需要的空间会更大;
3:在对表数据进行添加删除或修改需要消耗较多的时间,因为索引也要动态的去维护;
在哪些列上适合用索引
1:经常用于搜索的列;
2:作为主键列,(不经常修改的列)
3:经常用在连接的列上,这些列主要是一些外键列,可以加快连接速度;
4:经常需要根据范围搜索的列上;
5:经常需要排序的列;
6:经常用在where子句上的列;
不适合在哪些列上创建索引
1:查询中很少用到的列
2:对于那些具有很少数据的列,(用户表的性别,bit数据类型的列)
3:对于那些定义text(文本),image(图片),url(路径地址)的列,因为这些列的数据量相当较大;
4:当对修改性能的要求远远大于搜索性能,因为当增加索引时,会提高搜索性能,但是会降低修改性能;
索引的分类和使用
按物理存储角度分:
1:聚集索引
记录表的排列顺序和索引的排列顺序一致,所以查询效率加快,只要找到第一个索引值的记录,其余连续性的记录在物理上一样连续存放。
聚集索引的缺点:修改慢,因为为了使记录表和索引表排列顺序一致,在插入的时候会对数据进行重新排序。
2:非聚集索引
表记录和索引的排列顺序不一定一致,两种索引采用的是B+数的数据结构,非聚集索引的叶子层并不和实际数据页相重叠,而采用叶子层包含指向记录表的指针,非聚集索引层次多,不会造成数据重排。
逻辑角度分:
1:主键索引:它是一种唯一索引,不允许有空值。一般在表建表的时候同时创建主键
2:普通索引,最基本的索引,没有任何限制;
3:唯一索引:它与普通索引类似,唯一不同的是:索引列的值必须唯一,允许空值;
3.1:主键一定是唯一性索引,但唯一性索引并不一定是主键。
3.2:一个表中可以有多个唯一性索引,但只能有一个主键。
3.3:主键不允许为空值,唯一性索引可以为空值。
3.4:索引可以提高查询速度。
4:复合索引(又称多列索引,联合索引):多个字段上建立的索引,提高复合条件查询的速度;
#创建联合索引:
CREATE INDEX ids_proName_money ON pro_order(pro_name,money);
#查看索引:
SHOW INDEX FROM pro_order
数据库索引在什么情况下失效
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 pro_order where money != 0;
7:对索引列进行运算。这里运算包含‘+-*/’等运算符。也包括使用函数。
SELECT * FROM pro_order WHERE money+0 =0;
此时索引不起作用。
SELECT * FROM pro_order WHERE COUNT(money)=0;
此时索引也不起作用。
如果不是不是直接判断索引字段列,而是判断运算或其它函数处理后的索引列索引均不起作用;
8:索引字段进行判断空查询是,(对索引列字段判断是否为NULL时),语句为IS NULL 或 IS NOT NULL;
SELECT * FROM pro_order WHERE create_time IS NULL
此时就不检索time字段上的索引表了,也就是索引在这条语句执行时效。
接着在执行
SELECT * FROM pro_order WHERE create_time='2021-08-09 13:25:32'
此时就会检索索引表,索引又起作用了。
9:范围列可以用到索引(联合索引必须是最左前缀),但是范围列后面的列无法使用索引;
优化索引
1:尽量不要使用左模糊和全模糊,如果需要可以使用搜索引擎来解决。
2:union,in或or都可以命中索引,建议使用 in;
3:负向条件查询不能使用索引,可以优化为 in查询;
负向条件查询有:!= ,<> ,not in,not,like等等;
select * from sys_user where `status`!=1 and `status`!=2
优化
select * from sys_user where `status` in(0,3,4)
4:合理使用联合索引的最左前缀原则
如果在(a,b,c)三个字段上建立联合索引,那么它能够加快 a | (a,b) | (a,b,c) 三组查询速度。
比如说把(username,password)建立了联合索引,因为业务上几乎没有password的单条件查询,而有很多username的单条件查询需求,所以应该建立(username,password)的联合索引,而不要建立(password,username)的联合索引
注意:
4.1:建立联合索引的时候,要把查询频率较高的列放在最左边
4.2:如果建立了(a,b)索引,就不必再独立建立a索引。同理如果建立了(a,b,c)联合索引,就不必再独立建立a,(a,b)索引
4.3:存在非等号和等号混合判断条件时,在建索引时,请把等号条件的列前置。
如 where a>? and b=?,那么即使 a 的区分度更高,也必须把 b 放在索引的最前列。
4.4:最左前缀原则,并不是要求where后的顺序和联合索引的一致。
下面的 SQL 语句也可以命中 (login_name, passwd) 这个联合索引。
select uid, login_time from sys_user where passwd=? and login_name=?
但还是建议 where 后的顺序和联合索引一致,养成好习惯。
5:把计算放到业务层而不是数据库层。(因为对索引列进行运算,不能命中索引)
6:表数据比较少、更新十分频繁、数据区分度不高的字段上不宜建立索引。
一般区分度在80%以上的时候就可以建立索引,区分度可以使用
count(distinct(列名)) / count(*) 来计算。
7:强制类型转换会全表扫描
例如:如果phone字段是varchar类型,则下面的sql不能命中索引
select * from sys_user where phone = 18838003017
可以优化为:
select * from sys_user where phone = '18838003017'
8:利用覆盖索引进行查询操作,避免回表
select uid,login_time from sys_user where username=? and password=?
如果建立了(username,password,login_time)的联合索引,由于login_time已经建立在索引中了,被查询的username和password就不用去row上获取数据了,从而加速查询
9:在order by和group by中要注意索引的有序性
如果order by是组合索引的一部分,应该将该字段放在组合索引的最后
例如:where a=? and b=? order by c ->可以建立联合索引(a,b,c)
如果索引中有范围查找,则索引的有序性无法利用
例如:where a>10 order by b ->索引(a,b)无法排序
10:建立索引的列,不许为null
单列索引不存 null 值,复合索引不存全为 null 的值,如果列允许为 null,可能会得到“不符合预期”的结果集,所以,请使用 not null 约束以及默认值。
SQL语句的优化
1:能用到索引尽量用到索引.对索引的优化实际上就是sql语句的调优
2:任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。
3:尽量使用where,而不要使用having
4:尽量使用多表查询,不要使用子查询
5:where后的and.or左右执行顺序是从右至左
运算符为and时--尽量把为假的放在右边
运算符为or时--尽量把为真的放在右边