一、索引
1.什么是索引
在数据之外,数据库还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用指向数据,这样就可以在这些数据结构上实现高效查找,这些数据结构就是索引。
简单来说索引的出现就是为了提高数据的查询效率,就像书的目录一样。在书籍中,用户不必翻阅完整个书就能根据目录迅速地找到所需要的信息。在数据库中,索引也允许数据库程序迅速地找到表中的数据,而不必扫描整个数据库。
2.索引的分类
2.1 单列索引:一个索引只包含单个列,但一个表中可以有多个单列索引。
2.1.1 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。
2.1.2 唯一索引:索引列中的值必须是唯一的,但是允许为空值。
2.1.3 主键索引:是一种特殊的唯一索引,不允许有空值。(主键约束,就是一个主键索引)
2.2 组合索引:在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合。
2.3 全文索引:全文索引,只有在MyISAM引擎上才能使用,只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。全文索引,就是在一堆文字中,通过其中的某个关键字等,就能找到该字段所属的记录行。
2.4 空间索引:空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据类型有四种,GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时,使用SPATIAL关键字。要求,引擎为MyISAM,创建空间索引的列,必须将其声明为NOT NULL。
3.索引的优势
1)提高数据检索效率,降低磁盘IO成本
2)通过对数据的排序,降低排序成本
4.索引的劣势
1)索引虽提高了查询效率,但同时降低了更新、修改、删除的效率,因为MySQL不仅要保存数据,还要维护数据和索引的关系。
2)需要成本去维护索引。一个性能良好的索引需要不断的去尝试,以找到最优解。
5.什么情况下适合建立索引
1)主键自动建立唯一索引
2)频繁作为查询条件的字段(where后面的字段)
3)查询中与其他表关联的字段(各种join on后面的字段)
4)单值/复合索引选择?(高并发下倾向选择复合索引)
5)查询中排序的字段
6)查询中统计或分组的字段
6.什么情况下不适合建立索引
1)表数据太少
2)频繁更新的字段
3)where后面用不到的字段
7.什么时候会出现索引失效
1)like以通配符开头('%abc')会导致索引失效,违反最左前缀法则
最左前缀法则:查询从索引的最左前列开始并且不跳过索引中的列。
2)在索引列上做任何操作(计算、函数、类型转换),会导致索引失效而转向全表扫描
3)存储引擎不能使用索引中范围条件右边的列,举例:select id,name from student where id > 50 and name = '张三',会导致name索引失效
4)尽量使用覆盖索引,不要select *
5)MySQL在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描,理由也很简单,B+Tree叶子节点用指针相连且是排好序的,这种数据结构只能解决有序的定值查询,像不等于这种无法利用索引查询。
6)IS NULL、IS NOT NULL无法使用索引,理由同上
7)字符串不加单引号索引失效
隐式转换-->函数操作
8)用or连接时会导致索引失效
二、索引优化
1.索引优化规则
(1)like语句的前导模糊查询不能使用索引,因为页面搜索严禁左模糊或者全模糊,如果需要可以使用搜索引擎来解决。
(2)union、in、or 都能够命中索引,建议使用 in
union能够命中索引,并且MySQL 耗费的 CPU 最少。
in能够命中索引,查询优化耗费的 CPU 比 union all 多,但可以忽略不计,一般情况下建议使用 in。
or 新版的 MySQL 能够命中索引,查询优化耗费的 CPU 比 in多,不建议频繁用or。
补充:有些地方说在where条件中使用or,索引会失效,造成全表扫描,这是个误区:
- ①要求where子句使用的所有字段,都必须建立索引;
- ②如果数据量太少,mysql制定执行计划时发现全表扫描比索引查找更快,所以会不使用索引;
- ③确保mysql版本5.0以上,且查询优化器开启了index_merge_union=on, 也就是变量optimizer_switch里存在index_merge_union且为on。
(3)负向条件查询不能使用索引
负向条件有:!=、<>、not in、not exists、not like 等。
(4)联合索引最左前缀原则
- 如果在(a,b,c)三个字段上建立联合索引,那么他会自动建立 a| (a,b) | (a,b,c)组索引。
- ①建立联合索引的时候,区分度最高的字段在最左边;
- ②存在非等号和等号混合判断条件时,在建立索引时,把等号条件的列前置。如 where a>? and b=?,那么即使a 的区分度更高,也必须把 b 放在索引的最前列;
- ③最左前缀查询时,并不是指SQL语句的where顺序要和联合索引一致。
(5)不能使用索引中范围条件右边的列(范围列可以用到索引),范围列之后列的索引全失效
- 范围条件有:<、<=、>、>=、between等。
- 索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引。
(6)不要在索引列上面做任何操作(计算、函数),否则会导致索引失效而转向全表扫描
(7)强制类型转换会全表扫描
字符串类型不加单引号会导致索引失效,因为mysql会自己做类型转换,相当于在索引列上进行了操作。
(8)更新十分频繁、数据区分度不高的列不宜建立索引
- 更新会变更 B+ 树,更新频繁的字段建立索引会大大降低数据库性能。
- “性别”这种区分度不大的属性,建立索引是没有什么意义的,不能有效过滤数据,性能与全表扫描类似。
- 一般区分度在80%以上的时候就可以建立索引,区分度可以使用 count(distinct(列名))/count(*) 来计算。
(9)利用覆盖索引来进行查询操作,避免回表,减少select * 的使用
- 覆盖索引:查询的列和所建立的索引的列个数相同,字段相同。
- 被查询的列,数据能从索引中取得,而不用通过行定位符 row-locator 再到 row 上获取,即“被查询列要被所建的索引覆盖”,这能够加速查询速度。
(10)索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时,尽量使用not null 约束以及默认值。
(11)is null, is not null无法使用索引
(12)如果有order by、group by的场景,请注意利用索引的有序性
order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现file_sort 的情况,影响查询性能。
(13)使用短索引(前缀索引)
- 对列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果该列在前10个或20个字符内,可以做到既使得前缀索引的区分度接近全列索引,那么就不要对整个列进行索引。因为短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作,减少索引文件的维护开销。可以使用count(distinct leftIndex(列名, 索引长度))/count(*) 来计算前缀索引的区分度。
- 但缺点是不能用于 ORDER BY 和 GROUP BY 操作,也不能用于覆盖索引。
- 不过很多时候没必要对全字段建立索引,根据实际文本区分度决定索引长度即可。
(14)利用延迟关联或者子查询优化超多分页场景
MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回 N 行,那当 offset 特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写。
(15)如果明确知道只有一条结果返回,limit 1 能够提高效率
(16)超过三个表最好不要 join
- 需要 join 的字段,数据类型必须一致,多表关联查询时,保证被关联的字段需要有索引。
- 例如:left join是由左边决定的,左边的数据一定都有,所以右边是我们的关键点,建立索引要建右边的。当然如果索引在左边,可以用right join。
(17)单表索引建议控制在5个以内
(18)SQL 性能优化 explain 中的 type:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好
- consts:单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。
- ref:使用普通的索引(Normal Index)。
- range:对索引进行范围检索。
- 当 type=index 时,索引物理文件全扫,速度非常慢。
(19)业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引
不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的。另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生。
(20)创建索引时避免以下错误观念
索引越多越好,认为需要一个查询就建一个索引。
宁缺勿滥,认为索引会消耗空间、严重拖慢更新和新增速度。
抵制惟一索引,认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。
过早优化,在不了解系统的情况下就开始优化。