MySQL 索引经典面试题及讲解

作为一名后端开发，Mysql 的使用必不可少，合理的使用索引和索引调优是后端开

发者必须掌握的技能之一。在日常数据库的问题当中，不合理的使用索引占大部分。

通过下面两道 MySQL 经典的面试题，我们来学习一下关于索引的优化。

1. 下面哪些语句会使用到索引，哪些索引起了作用？

CREATE TABLE t1 ( id int unsigned NOT NULL auto_increment, a int unsigned NOT NULL DEFAULT 0, b int unsigned NOT NULL DEFAULT 0, c int unsigned NOT NULL DEFAULT 0, PRIMARY KEY(id), KEY abc (a, b, c) USING BTREE ); (1). SELECT * FROM t1 WHERE a=3 AND b=5 AND c=4; (2). SELECT * FROM t1 WHERE c=4 AND b=6 AND a=3; (3). SELECT * FROM t1 WHERE a=3 AND c=7; (4). SELECT * FROM t1 WHERE a=3 AND b>7 AND c=3; (5). SELECT * FROM t1 WHERE b=3 AND c=4; (6). SELECT * FROM t1 WHERE a>4 AND b=7 AND c=9; (7). SELECT * FROM t1 WHERE a=3 ORDER BY b; (8). SELECT * FROM t1 WHERE a=3 ORDER BY c; (9). SELECT * FROM t1 WHERE b=3 ORDER BY a;

2. 如何存储和查询 url 地址，数据库表结构应该怎样设计？

针对上面的题目，我们来慢慢的分析

多列索引

单列索引，也就是索引之间相互独立，例如 (a)，(b)，(c)

多列索引，也叫组合索引，例如 (a, b, c)，

很多人对索引的理解不够，随意的给列加索引，比如给每个列都创建一个独立的索

引，或者以错误的顺序创建索引，比如，“给 WHRE 条件里面的列都建上独立索引”

CREATE TABLE t ( a INT, b INT, c INT, KEY(a), KEY(b), KEY(c) );

上述加索引的方式，大部分情况下并不能带来效率的提升，反而会因为索引加太多

导致插入效率低，浪费磁盘空间。 选择合适的索引列顺序

索引正确的顺序可以很好的满足排序和分组的需要（这里说明下，以下内容仅适用于

B-Tree 索引，因为哈希或其他类型索引并不会像 B-Tree 索引一样按顺序存储数据）。

选择索引列的经验法则：将选择性最高的列放到索引的最前列。

以下这种简单的方法可以统计出表中列的选择性

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity, COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity, COUNT(*) FROM payment ****************** 1.row ****************** staff_id_selectivity: 0.0001 customer_id_selectivity: 0.0373 COUNT(*): 160

可以看出，customer_id 的选择性更高，所以将 customer_id 作为索引列的第一列：

ALTER TABLE payment ADD KEY(customer_id, staff_id)

组合索引何时生效？

组合索引可以这样理解，比如（a,b,c），abc 都是排好序的，在任意一段 a 的下面 b

都是排好序的，任何一段 b 下面 c 都是排好序的。

组合索引的生效原则是：从前往后依次使用生效，如果中间某个索引没有使用，那么

断点前面的索引部分起作用，断点后面的索引没有起作用。

(1). SELECT * FROM t1 WHERE a=3 AND b=5 AND c=4;

a，b，c 三个索引都用到了

(2). SELECT * FROM t1 WHERE c=4 AND b=6 AND a=3;

a，b，c 三个索引都用到了

虽然 where 里面的条件顺序不是 a，b，c，

但在查询之前 where 里面的条件顺序会被 mysql 自动优化，效果跟上一句一样

(3). SELECT * FROM t1 WHERE a=3 AND c=7;

a 用到了，b 没有用到，所以 c 没有用到

(4). SELECT * FROM t1 WHERE a=3 AND b>7 AND c=3;

a，b 也用到了，c 没有用到，这个地方 b 是范围值，也算断点，只不过自身用到了索引

(5). SELECT * FROM t1 WHERE b=3 AND c=4;

因为最左索引列 a 没有用到，所以 b，c 也没有用到

(6). SELECT * FROM t1 WHERE a>4 AND b=7 AND c=9;

a 用到了，b 没有用到，c 没有用到

(7). SELECT * FROM t1 WHERE a=3 ORDER BY b;

a 用到了，b 在结果排序中也用到了索引的效果，

根据 B-Tree 的性质，a 下面任意一段的 b 是排好序的

(8). SELECT * FROM t1 WHERE a=3 ORDER BY c;

a 用到了，但是这个地方 c 没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort

(9). SELECT * FROM t1 WHERE b=3 ORDER BY a;

b 没有用到，排序中 a也没有发挥索引效果

通过上面的题目可以看出，多列索引的顺序至关重要，稍不小心就会踩坑。

索引值过长的优化

针对文章开头的那道题目，设计一张表存储大量的 url，然后根据 url 进行搜索查找。 简单的做法

一种简单的做法是直接存储，并以 url 作为索引

CREATE TABLE website ( id int unsigned NOT NULL auto_increment, url varchar(255) NOT NULL, PRIMARY KEY(id), KEY url (url) USING BTREE )

然后再以这样的方式查询：

SELECT id FROM website WHERE url="http://www.mysql.com";

这种做法快捷方便，但是有一个问题就是有时候 url 会很长，以 url 作为索引占用

空间很大，而且查询效率会很低.

加一些优化

优化这个问题？很简单！我们可以引入一个新的列 url_crc 作为索引，使用 CRC32

给 url 做 hash，也就是这样

CREATE TABLE website ( id int unsigned NOT NULL auto_increment, url varchar(255) NOT NULL, url_crc int unsigned NOT NULL DEFAULT 0, PRIMARY KEY(id), KEY url_crc (url_crc) USING BTREE )

查询语句需要注意，下面这种写法是错误的：

SELECT id FROM website WHERE url_crc32=CRC32("http://www.mysql.com");

首先简单科普一下 crc32，这是一种哈希算法，该算法会得出一个值，范围是

0~4294967296(2^32-1)，也就是说这种算法存在一定的碰撞概率，一旦哈希冲突了，

查询出来的结果就有可能是错误的。

为了处理哈希冲突，那么我们可以对查询语句做一点小小的改进。

SELECT id FROM website WHERE url_crc32=CRC32("http://www.mysql.com") AND url="http://www.mysql.com";

可以使用其他哈希算法吗？

当然可以了，使用 crc32 的原因是这个算法效率很高，而且生成的是一个数字，

占用空间较小。

而使用 SHA1 和 MD5 这些算法，效率较低且占用空间大，不过可以做一点小优化，

将 MD5 出来的字符串取前 16 位，然后再转换成数字存进数据库，这样虽然占用空

间小，但是效率依然比较低。 如何维护这个哈希值？

可以使用触发器，在插入和更新时维护 url_crc 列，也可以先在代码里计算好 crc32，

再执行查询语句.

MySQL

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。