【数据库】索引的使用

前言

在数据库设计过程中，常需要考虑性能，好的设计可以大大提高sql 语句的增删改查速度。在表的创建过程中，我们时常会用到主键，而主键又会自动构建索引。在数据库的学习过程中，大家都知道好的索引可以大大提高查询速度，那么到底什么是索引呢？好与坏能否形象点？

其实在以前的博文中【SQL】基础知识整理，我们对这个索引概念是有介绍过的，但在实际使用过程中，除了主键自动创建的，也没有进行很好的应用。而且当时是初次学习数据库，随着这几年的开发，对实际开发情况也有了一定了解，除了常用的语句，其余也有点模糊了，但这个索引概念是很重要的。尤其是好的设计，在接口执行时，可以用的时间更少，系统性能更高。因此，针对这个概念，我们进行重拾，好好牢固牢固基础，好好看看索引好，到底好在哪里？以后写sql时候，能否用上？

本博文以一个示例出发，介绍有索引和没有索引情况，同一句sql 的查询效果，同时通过这个示例也逐渐理解为什么有时候在编写sql时，看大家oderBy时会有多个条件，但其实效果就是一个条件？（基础还是太零碎了，时间久远，横向要学，但纵向更得补补，这才是首要，傻傻虎虎得努力呀）

出发示例

示例基于pg 数据库开始，数据库管理器用dbeaver

创建表

CREATE TABLE mytable(
idserial int primary key,
category_id int default 0not null ,
user_id int default 0not null ,
adddate int default 0not null
);

Explain 查看sql执行计划

使用方法：explain + sql 语句，可以查看执行计划，从而知道数据库是如何处理sql语句的，查看该sql语句有没有使用上了索引，有没有做全表扫描。

where 查询解析

基础的sql 语句SELECT * FROM mytable WHERE category_id=1 AND user_id=2，然后进行有索引和无索引的解析。

EXPLAIN
SELECT * FROM mytable
WHERE category_id=1 AND user_id=2

无索引

通过语句查看，得到结果如下：

有索引

创建category_id 和 user_id 的索引，命名可以用“表名_字段1名_字段2名”，方便查看。

CREATE INDEX mytable_categoryid_userid ON mytable(category_id,user_id);

然后通过解析语句进行再次查看，得到结果如下：

可知已经使用了索引mytable_categoryid_userid 。

接下来我们看带oderby的，体验索引的魅力！同时看完后面的内容，就可以理解为什么有时候在编写sql时，看大家oderBy时会有多个条件，但其实效果就是一个条件？

where + oderBy 查询解析

在这里的sql 解析语句如下：

EXPLAIN
SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY adddate DESC;

无索引

通过语句查看，得到结果如下：

先排序，然后用之前设置的索引mytable_categoryid_userid 选择

有索引

创建category_id 、 user_id 和 adddate的索引，语句如下：

CREATE INDEX mytable_categoryid_userid_adddate ON mytable (category_id,user_id,adddate);

通过语句查看，得到结果如下：

这样就可以减少排序，直接用索引。

总结

通过建立索引可以极大地提高在数据库中获取所需信息的速度，同时还能提高服务器处理相关搜索请求的效率，但是建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。

• 对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引；
• 对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。