关系型数据库中的索引

最新推荐文章于 2024-08-16 08:54:14 发布

PT1993

最新推荐文章于 2024-08-16 08:54:14 发布

阅读量2.5k

点赞数 1

分类专栏： mysql 文章标签： MYSQL 索引

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

说白了索引就是一个查找问题

索引是对数据库表中的一列或者说是多列进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。索引的一个主要目的就是加快检索表中数据的方法。

例子：这样一个查询语句selecr * from table1 where id=1000;
如果没有索引的话，必须遍历整个表，知道id等于10000的这一行被找到为止。但是有了索引之后（必须在id这一列上建立索引），即可在索引中查找，由于索引是经过某种算法优化过的，因为查找次数要少的多。可见索引是用定位的O(logN)。

数据库索引的数据结构基础:B+tree

数据库索引是用于提高数据库表的数据访问速度

a)、索引的目的是什么？

1 能够避免全表扫描（如：数据表中有1000条数据，我们只需要根据条件查询其中的一条，针对该列创建一个索引，只需要扫描这一条即可；如果不创建索引，我们得扫描1000条）
2 让服务器快速定位到表的位置，提高检索速度
3 帮助服务器避免排序和临时表的生成
4 将随机 I/O 变成顺序 I/O
5 创建唯一性索引，保证数据库表中每一行数据的唯一性
6 加速表和表之间的连接
7 使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

b)、索引对数据库系统的负面影响是什么？

1 创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加
2 索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间
3 当对表进行增、删、改的时候，索引也要动态维护，这样就降低了数据的维护速度。

c)、MySQL索引的创建原则（在哪些列上，以怎样的方式，创建索引更好）

1 在经常需要搜索的列上，可以加快搜索的速度；

2 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度；

3 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

4 选择性最高的列（选择性=不重复的索引值/总记录）

5 对字符串进行索引，保证选择性较高且前缀不能太长，即前缀的基数应接近完整列的基数，可以节省大量的索引空间；后缀索引：字段值反向存储；（注意：平均选择性高，数据分布不均，陷阱）

6 根据情况创建复合索引，复合索引可以提高查询效率；

7 避免创建过多索引，索引会额外占用磁盘空间，降低写操作效率；

8 主键尽可能选择较短的数据类型（如：int/bigint），可以有效减少索引的磁盘占用，提高查询效率；

d)、不应该在这些列上建立索引？

1 对于那些在查询中很少使用或者参考的列不应该创建索引，这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求
2 当修改性能远远大于检索性能时，不应该创建索引，这是因为修改功能和检索功能是互相矛盾的

如何在使用SQL创建索引：

之前的例子中，在Employee_Name列上创建索引的SQL如下：

CREATE INDEX name_index
ON Employee (Employee_Name)

如何创建联合索引

我们可以在雇员表上创建两个列的联合索引，SQL如下：

CREATE INDEX name_index
ON Employee (Employee_Name, Employee_Age)

索引的类型

索引有很多种类型，都是实现在存储引擎层的。

1 普通索引：最基本的索引，没有任何约束限制。
2 唯一索引：与普通索引类似，但是具有唯一性约束。
3 主键索引：特殊的唯一索引，不允许有空值。
4 组合索引：将多个列组合在一起创建，可以覆盖多个列。
5 外键索引：只有 InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作。【基本上不使用】
6 全文索引：MySQL自带的全文索引只能用于 MyISAM，并且只能对英文进行全文检索。【基本上不使用】
7 B+树索引
8 哈希索引
9 空间数据索引（R-Tree）

MyISAM存储引擎
用作地理数据存储
无须前缀查询
从所有维度索引数据
使用任意维度来组合查询
B+Tree的不同
可使用所有维度来索引数据，而无需最左前缀原则

MySQL索引的注意事项

① 复合索引遵循前缀原则；

// 创建一个索引
KEY(a, b, c); 

// 以下3种情况该索引生效：
WHERE a=1 and b=2 and c=3;
WHERE a=1 and b=2;
WHERE a=1

// 以下2情况该索引不生效：
WHERE b=2 and c=3; // 因为跳过了a
WHERE a=1 and c=3; // 因为跳过了b

常考题：

题目：我创建了一个 WHERE a=1 and b=2 或者 WHERE a=1这样的查询，我需要创建一个什么样的索引比较合适？
答案：KEY(a, b)；

注：KEY(a, b,c) 表示创建了一个索引，同时作用于a,b,c，目的是为了缩短查询范围（如：创建一个组合索引，章节段，查询第几章中的第几节的第几段，这样缩短了查询的范围）。

② like 查询，% 不能放前，可以使用全文索引；

where name like "%wang%"; // 不要使用该方法

③ column is null 可以使用索引；

④ 如果MySQL估计使用索引比全表扫描更慢，会放弃使用索引；

假设表中有100条数据，在写条件的时候这样写

where id > 1 and id < 100

即使对 ID设置了索引，在搜索的时候，搜索的是 2~99 之间的数据。

搜索过程：首先找 2 这条数据，去索引中看看它在什么位置，找到索引后，再去定位数据行；然后再去看 3，再去看索引，再去找数据行，；再去看 4，再去看索引，再去找数据行，… ，一直到 99 都要这样去操作。

所以在这里面本身来说，我们这样来查询，首先我们会多一个查询索引的步骤，所以MySQL认为，本身就100条数据，要查询其中的98条数据，而这98条数据都需要去看索引，所以就很慢，直接把这100条数据都扫描一遍，然后返回其中的98条不就可以了么，这样效率会更高，它会自动去放弃，不需要我们手动放弃，这是我们优化器做的事。

⑤ 如果 or 前的条件中的列有索引，后面的没有，索引都不会被用到； 老版本不合并，5.0及以上版本合并

where a or b; // 如果a中有索引，b中没有索引，该索引就会失效

⑥ 列类型是字符串类型，查询时一定要给值加引号，否则索引失效。

// 在创建的时候设置了 name varchar(16)
// 搜索的时候，name为100
where name = 100； // 写成这样的形式，可以搜索到，但是没有用到索引【因为本身是字符串类型，而写成了整型】
where name = "100"; // 此时才用到了索引【一定要给值加引号】