数据库索引与SQL优化

秒速五厘米 2023-10-10 15:01 11阅读 0赞

## 一、索引 ##

#### 1.什么是索引 ####

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。简而言之，**数据库索引是排好序的数据结构，为了提高数据的查询效率**。

在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

MySQL常用的索引就是**B+树**。

#### 2、索引的分类 ####

我们经常从以下几个方面对索引进行分类

从**数据结构的角度**对索引进行分类

*  **B+tree**

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是**MySQL里默认和最常用的索引类型。**

*  **Hash**

由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。

HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

*  **Full-texts索引**

即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

*  **RTREE**

RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE，RTREE的优势在于范围查找。

从**物理存储的角度**对索引进行分类

*  **聚集索引**（聚簇索引）：是指数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。MySQL中一般默认主键为聚集索引。
 *  **非聚集索引**（二级索引）(辅助索引)：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。

从**索引字段特性角度**分类

*  主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个。
 *  唯一索引：加速查询 + 列值唯一（可以有null）
 *  普通索引：仅加速查询（MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。）
 *  前缀索引：也叫局部索引。

从**组成索引的字段个数角度**分类

*  单值索引：（最通用、最简单）一个索引只包含单个列，一个表中可以有多个单值索引
 *  联合索引（复合索引）：一个索引包括多个列，功能最强大

#### 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')会导致索引失效，违反**最左前缀法则**

最左前缀法则：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

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连接时会导致索引失效

#### 8. **为什么建议InnoDB必须建主键** ####

**降低数据库开销****。**因为数据库的资源非常宝贵，如果不手动建主键索引，MySQL底层依然会帮我们创建一个聚集索引来维护整张表的所有数据，因为B+Tree必须依靠索引才能建立。

#### 9. **为什么推荐使用整型主键** ####

整型比大小更快，整型对于UUID来说占用存储空间小。

#### 10. **为什么推荐使用自增主键** ####

用自增方便每次插入到叶子节点链的后面，对于B+树的分裂来说更加方便。如果不用自增的话，有可能插入到叶子节点的中间位置，对于B+树的分裂来说不太方便。主要影响数据写入表的性能。

#### 11. **为什么InnoDB非主键索引存储的是主键值** ####

保持一致性，当数据库表进行DML操作时，同一行记录的页地址会发生改变，因非主键索引保存的是主键的值，无需进行更改。同时还可以节省存储空间，因为Innodb数据本身就已经汇聚到主键索引所在的B+树上了， 如果普通索引还继续再保存一份数据，就会导致有多少索引就要存多少份数据。

## 二、复杂SQL优化方案 （索引优化） ##

1.  要注意like的使用，前模糊和全模糊不会走索引。
2.  尽量不要在索引列上做任何操作（计算、函数、类型转换），不然会导致索引失效而转向全表扫描
3.  查询的条件尽量使用索引字段，如某一个表有多个条件，就尽量使用复合索引查询，复合索引使用要注意字段的先后顺序。
4.  绝对避免select \*的使用，尽量select具体需要的字段，减少不必要字段的查询；
5.  避免使用不等于（!=），因为它不会使用索引。
6.  尽量避免使用is null或is not null。
7.  字符串一定要加单引号，字符串不加单引号索引失效。
8.  尽量将or 转换为 union all。
9.  用exists代替in，not exists代替not in，效率会更好；
10. 避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。
11. 千万不要 ORDER BY RAND()
12. 多表关联尽量用join，减少子查询的使用。表的关联字段如果能用主键就用主键，也就是尽可能的使用索引字段。如果关联字段不是索引字段可以根据情况考虑添加索引。