首页 > 试题广场 >

假设MySQL数据库

[不定项选择题]

假设MySQL数据库表:

create table T{

k int unsigned not null auto_increment,

a date,

b varchar(24),

c int,d varchar(24),

primary key(k),unique key a_index (a DESC,b DESC),

key k1(b),key k2(c),key k3(d));

如下哪些sql语句查询能较好的利用索引?()

```
select b from WHERE b like 'aaa%';
```

select a,b from T WHERE a='2015-10-25' ORDER BY b ASC,c ASC;

select a,b,c from T WHERE a='2015-10-25' ORDER BY b ASC;

select a,b,c from T WHERE a='2015-10-25' ORDER BY a,b;

查看正确选项

阿森先生

求选项c和d的区别

发表于 2017-02-09 14:32:18 回复(0)

day-day-up

虽然这道题我做错了，但是看到大家的解释后，自己也有一点想法，说一下我的拙见吧：

我觉得B选项是因为只需查找a, b，所以没必要按照 c ASC进行排序

而C选项查找 a,b,c却只用了 b ASC，索引未得到充分利用

发表于 2016-08-24 20:17:30 回复(0)

mcq

解析如下：

一、什么是索引：

简单的来说，建立索引在进行数据库操作的时候不需要全盘一条条的扫描，删选出符合的记录，索引内部自己有一套优化算法，因此借助索引来对数据库进行操作可以提高查询的效率。

二、什么时候建立的索引将失效或效率不高（情况有很多，这里列举常见的几种，假设在字段name上建立了索引）：

1、使用了运算符!=，以及关键字not in, not exist等，认为产生的结果集很大，往往导致引擎不走索引而是走全盘扫描

2、对索引字段使用了函数，如where substr(name, 1, 3)=‘mark’，导致索引无效

3、使用like和通配符，第一个字符是%将导致索引失效，如where name like "%ark“ （A正确）

.....

三、order by与索引

首先利用where进行数据查询，这一步是免不了的，至于这一步有没有利用索引暂时不考虑，关键是在获取所有符合的记录后还需要进行排序，看看order by是如何利用索引的。

如果order by中的字段有建立索引，同时：

1、该字段没有出现在where中，则在排序的时候需要正常排序，默认order by是升序排序, 故索引没有对排序产生有利帮助（B,C错误）

2、该字段同时同时出现在where中，则在获取记录后不进行排序，而是直接利用索引，效率变高。（D正确）

补充： group by也和order by类似

发表于 2016-09-05 16:19:01 回复(7)

revivedSuN

0.答案中语句格式太乱首先整理下sql语句格式

create table T(
       k INT UNSIGNED NOT NULL AUTO_INCREMENT,
       a DATE,
       b varchar(24),
       c INT,
       d varchar(24),
       PRIMARY KEY(k),
       UNIQUE KEY a_index(a DESC,b DESC),
       KEY k1(b),
       KEY k2(c),
       KEY k3(d)
)

1. 题目问题是哪些sql语句查询能较好的利用索引。4个选项中的sql语句可以分为两类

没有使用排序(A)，使用了排序(B,C,D)。 那么这个“ 较好 ”我理解为两种情况。第一，对于没有使用排序的查询，可以直接通过查询索引返回所需要的结果。第二，对于使用了排序的查询语句在利用了索引的同时，排序是否也利用索引进行排序而不是进行外部排序(explain 中的using filesort)。如果是根据这两点评判，我觉得是ACD，原因如下。

选项A . select b from WHERE b like 'aaa%'; 根据条件b查询，使用的是索k1（通过explain可以看到），由于通过索引查询,索引列包含结果b(select b)因此只需要通过查询索引，而不需要在访问原表即可得到最终结果。至于like操作，mysql是能在索引中左最左前缀匹配的like比较，这种比较会被转换为普通的范围查询，而like 中 %若位于开头则无法比较，只能全表扫描。

选项B. select a,b from T WHERE a='2015-10-25' ORDER BY b ASC,c ASC; 查询是通过索引a_index（a,b）完成，而排序中的字段b,c无法通过索引直接来排序，因此需要再次使用外部排序最终获取结果。（通过explain的extra字段using filesort可以看出）

选项C. select a,b,c from T WHERE a='2015-10-25' ORDER BY b ASC; 通过explain可以看到使用了a_index索引，由于a字段在查询时使用了常量，因此b满足最左前最的条件，可以使用索引排序。如果将条件where中条件换成c = xxx，再观察explain结果，会发现出现using filesort.

选项D. select a,b,c from T WHERE a='2015-10-25' ORDER BY a,b; 显然排序可以直接通过索引a_index完成，而不需要额外的filesort.

参考：

[1]高性能mysql第3版第5章创建高性能的索引与附录D explain

[2]mysql官方手册 http://dev.mysql.com/doc/refman/5.7/en/create-table.html

编辑于 2016-09-12 00:34:30 回复(2)

LoftWhale

愚认为：题目中的索引b是降序，而B，C两个选项都是升序，故会导致效率降低。而A，D两个选项没有指定升序降序，故会按照其定义的索引a desc，b desc 来进行操作，故而效率较高。所以选A，D。这是我的想法。。。如有不对，可指正

发表于 2015-12-08 12:31:54 回复(8)

zhaoying

create table T{
k int unsigned not null auto_increment, 自动递增 auto_increment

a date,

b varchar(24),

c int,
d varchar(24),

primary key(k),unique key a_index (a DESC,b DESC),

key k1(b),key k2(c),key k3(d)
);
Key是索引约束，对表中字段进行约束索引的，都是通过primary foreign unique等创建的。常见有foreign key，外键关联用的。
          KEY forum (status,type,displayorder) # 是多列索引（键）
          KEY tid (tid)                         # 是单列索引（键）。
尽量避免在一个复杂查询里面使用 LIKE '%parm1%'， 这里由于通配符(%)在搜寻词首出现，所以Oracle系统不使用 last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用:     select * from employee where last_name like ' c% ' ;
ORDER BY 语句用于根据指定的列对结果集进行排序。ORDER BY 语句默认按照升序对记录进行排序。
①SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；
--order by的字段混合ASC和DESC,不使用索引

发表于 2016-04-11 09:20:34 回复(1)

追梦青春

A中的like不是需要全表扫描吗

发表于 2016-02-01 23:21:11 回复(3)

W.sl

只有order by字段出现在where语句中，才会利用索引，而且必须保证两者（ASC,DESC）完全相同或者完全相反。B、C 都没出现在where语句中，故错

感觉A既然是用到了复合索引，那么where语句中应该先有a的，感觉A 也错了。但是explain以后显示用到的是 k1，也就是没有用复合索引。这个求大神讲解

编辑于 2016-10-30 19:31:03 回复(2)

过往云烟123

select b from WHERE b like 'aaa%';这个可以用到复合索引吗？字段b是复合索引中的第二个字段啊。。

发表于 2016-07-07 17:59:46 回复(7)

huixieqingchun

'aaa%'的形式也可以使用索引，只有‘%aaa'才需要全表查询；

发表于 2016-06-02 17:41:39 回复(0)

XianNEO

我在MySql workbench 中对这四个语句都进行了explain 结果发现只有第一条显示Using index 请问大神这是为什么呢？？？

发表于 2016-04-19 15:31:09 回复(1)

Samuel1995

只有order by中的字段出现在where里面，排序才会走索引

发表于 2021-09-20 11:43:22 回复(0)

nomico271

题中建立的联合索引为key a_index (a DESC,b DESC),

A：由于在b上建立了索引，like后的%写在左边相当于范围，导致索引失效，所以aaa%可以使用索引；

B、C：order by后的排序均为ASC，与建立的索引b的索引排序DESC相反，所以效率较低；

D：由于a是常量，所以很好的用到了索引a,b

发表于 2017-08-05 19:38:42 回复(0)

牛客lucky

D:既然 order by默认是按升序排序，而索引primary key(k),unique key a_index (a DESC,b DESC) a,b都是降序，那么D能较好的利用索引吗

发表于 2016-09-03 18:44:56 回复(0)

Nutopia

A的语句是错的吧？

发表于 2016-03-16 22:47:15 回复(0)

黄毅飞

The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause.

即使order by 中的内容没有完全和索引内容一样，但只要未在order by中的部分出现在了where语句中，也可以使用索引

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause.

在有些情况下，mysql不能使用索引去排序，但是也可以使用索引去找到相应的WHERE过滤后的结果集
A:可以利用，b 有单独的索引k1，默认ASC可较好利用。
B:c无索引
C: 官方也有case ： SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;
D:mysql 官方case: SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
从人的角度来看C和D有区别吗？？对a加了constraint后，那么a只有一种情况呀。查询优化能将这两条语句解释一个目标执行方案吗？

编辑于 2015-12-10 11:51:22 回复(0)

虚无飘渺^

在有些情况下，mysql不能使用索引去排序，但是也可以使用索引去找到相应的WHERE过滤后的结果集

发表于 2023-04-20 21:29:02 回复(0)

取个name太难

假设KEY test(a,b,c) (1) order by 能使用索引最左前缀 -order by a -order by a,b -order by a,b,c -order by a asc,b asc,c asc -order by a desc,b desc,c desc (2) 如果where使用索引最左前缀定位为常量，则order by可以使用索引 -wherea=constorder by b,c -where a=constandb=constorder by c -where a=constand b>consst order by b,c ( 3) 不能使用索引进行排序 -order by a asc,b desc, c desc /*排序不一致*/ -where g=const order by b,c /*丢失a索引*/ -where a=constorder by c /*丢失b索引*/ -where a=constorder by a,d /*d不是索引一部分*/ -where a in (....) order by b,c /*对于排序来说，多个相等条件也是范围查询*/ 转自https://blog.csdn.net/weixin_35952427/article/details/112037911

发表于 2021-09-25 17:15:46 回复(0)