如何运用MySQL数据库索引?

愿天堂没有BUG

共 3471字,需浏览 7分钟

 ·

2021-04-18 03:47

1.单列索引与组合索引执行效率有差别么?

在firstname、lastname、age这三个列上分别创建单列索引,效果是否和创建一个firstname、lastname、age的多列索引一样呢?当然不是的,两者完全不同。

当我们执行查询的时候,MySQL只能使用一个索引。如果你有3个单列的索引,MySQL会试图选择一个限制最严格的索引。但即使是限制最严格的单列索引,它的限制能力也肯定远远低于firstname、lastname、age这3个列上的多列索引。

由于索引文件以B-树格式保存,MySQL能够立即转到合适的firstname,然后再转到合适的lastname,最后转到合适的age。

在没有扫描数据文件任何一个记录的情况下,MySQL就正确地找出了搜索的目标记录!

2.组合索引有什么好处?最左前缀是什么?

多列索引还有另外一个优点,它通过称为最左前缀(Leftmost Prefixing)的概念体现出来。

继续考虑前面的例子,现在有一个firstname、lastname、age列上的多列索引,我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时,MySQL将使用fname_lname_age索引:

firstname,lastname,age
firstname,lastname
firstname

它相当于我们创建了(firstname,lastname,age)、(firstname,lastname)以及(firstname)这些列组合上的索引。

下面这些查询都能够使用这个fname_lname_age索引:

SELECT peopleid FROM people WHERE firstname=‘Mike’ AND lastname=‘Sullivan’ AND age=‘17’;

SELECT peopleid FROM people WHERE firstname=‘Mike’ AND lastname=‘Sullivan’;

SELECT peopleid FROM people WHERE firstname=‘Mike’;


The following queries cannot use the index at all:
SELECT peopleid FROM people WHERE lastname=‘Sullivan’;

SELECT peopleid FROM people WHERE age=‘17’;

SELECT peopleid FROM people WHERE lastname=‘Sullivan’ AND age=‘17’;

3.怎样选择索引列?

在性能优化过程中,选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列:

  • 在WHERE子句中出现的列;

  • 在join子句中出现的列。

1)通常在where和join的判断字段上,都建立索引。以方便查询速度,在判断条件列上使用索引,方便快定位记录。

2)可以在一列或者多列创建索引。

  • 如果经常同时搜索两列或多列或按两列或多列排序时,建立组合索引会很大提高查询速度。例如,如果经常在同一查询中为姓和名两列设置判据,那么在这两列上创建多列索引将很有意义。

3)在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;

4)在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;

SELECT age ## 不使用索引

FROM people WHERE firstname=‘Mike’ ## 考虑使用索引

AND lastname=‘Sullivan’ ## 考虑使用索引

这个查询与前面的查询略有不同,但仍属于简单查询。由于age是在SELECT部分被引用,MySQL不会用它来限制列选择操作。

因此,对于这个查询来说,创建age列的索引没有什么必要。下面是一个更复杂的例子:

SELECT people.age, ##不使用索引

town.name ##不使用索引

FROM people LEFT JOIN town ON
people.townid=town.townid ##考虑使用索引

WHERE firstname=‘Mike’ ##考虑使用索引

AND lastname=‘Sullivan’ ##考虑使用索引

与前面的例子一样,由于firstname和lastname出现在WHERE子句中,因此这两个列仍旧有创建索引的必要。除此之外,由于town表的townid列出现在join子句中,因此我们需要考虑创建该列的索引。

那么,我们是否可以简单地认为应该索引WHERE子句和join子句中出现的每一个列呢?差不多如此,但并不完全。我们还必须考虑到对列进行比较的操作符类型。MySQL只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE。

可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符(%或者_)开头的情形。例如,“SELECT peopleid FROM people WHERE firstname LIKE ‘Mich%’;”这个查询将使用索引,但“SELECT peopleid FROM people WHERE firstname LIKE ‘%ike’;”这个查询不会使用索引。

4.哪些字段不适合加索引?

1、查询中很少使用的列不应该创建索引;

2、对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度;

3、对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少,不利于使用索引;

4、当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。

5.索引的返回结果是什么?

结果是一个中间结果集,数据库根据中间结果集再去查找数据库中的具体表的记录。

6.具体查询时会选择使用哪个索引文件?

每次查询只能使用一个索引,默认数据库会选择限制条件最严格的索引。

7.怎样判断是否使用了索引?及查看索引的使用性能?

使用explain命令。

EXPLAIN
SELECT * FROM mytable
WHERE category_id=1 AND user_id=2;
This is what Postgres 7.1 returns (exactlyasI expected)
NOTICE:QUERY PLAN:
Index Scan using mytable_categoryid_userid on
mytable(cost=0.00…2.02 rows=1 width=16)
EXPLAIN

以上是 postgres的数据,可以看到该数据库在查询的时候使用了一个索引,而且它使用的是创建的第二个索引。看到上面命名的好处了吧,马上就知道它使用适当的索引了。

8.排序分组是怎么使用索引的?

索引默认是已经自动排序的,所以在使用排序或者分组时候,用索引去查询速度会很快,产生排序或分组的中间结果集,然后根据中间结果集定位具体的表中的记录。

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

很简单,就像为where子句中的字段建立一个索引一样,也为ORDER BY的子句中的字段建立一个索引:
CREATE INDEX mytable_categoryid_userid_adddate ON mytable (category_id,user_id,adddate);

注意:“mytable_categoryid_userid_adddate"将会被截短为"mytable_categoryid_userid_addda”



最后

最后祝愿你能成为一名优秀的工程师!


浏览 32
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报