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，
再执行查询语句.