阿里巴巴开发手册解析个人笔记（五）Mysql规约(二）索引规约_索引 阿里规约

读者回复说 上一节这样子分类看着难受，还是按照原来的来

(二）索引规约

索引口诀

回忆一下索引的几句口诀
摘自周阳老师的mysql高级课程

全值匹配我最爱，最左前缀要遵守 
带头大哥不能死，中间兄弟不能断 
索引列上少计算，范围之后全失效 
LIKE符号写最右，覆盖索引不写星 
不等空值还有or，索引失效要少用 
var引号不能丢，SQL高级也不难 
分组之前必排序，一定要上索引啊
1
2
3
4
5
6
7

1. 【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

说明： 不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的； 另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生

在mysql中貌似唯一约束就是唯一索引
例子摘自：http://www.mysqltutorial.org/mysql-unique-constraint/

#供应商表
drop table if EXISTS supplier;
CREATE TABLE  supplier (
    supplier_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    phone VARCHAR(12) NOT NULL UNIQUE,
    address VARCHAR(50) NOT NULL
   
);
ALTER TABLE supplier ADD unique(name,address); #或者采用该写法
create unique index uk_wb_blog on supplier (name,address); #或者这种写法
/*

第一个UNIQUE约束应用于phone列。这意味着每个供应商都必须拥有不同的电话号码。换句话说，没有两个供应商拥有相同的电话号码。

第二个UNIQUE约束具有一个名称uc_name_address，该名称强制使用名称和地址列中值的唯一性。这意味着供应商可以使用相同的名称或地址，但不能使用相同的名称和地址。
*/
#实验验证唯一索引
INSERT INTO supplier(name, phone, address)
VALUES('ABC Inc', '408-908-2476','4000 North 1st Street, San Jose, CA, USA');

/*同一个phone 更换地址，报sql错误，原因因为phone是唯一索引，不能有同一个*/
INSERT INTO supplier(name, phone, address)
VALUES('XYZ Corporation', '408-908-2476','4001 North 1st Street, San Jose, CA, USA');

/*不同的电话，相同的地址，不同的名称，注意组合索引里是并且关系，所以可以运行成功*/
INSERT INTO supplier(name, phone, address)
VALUES('XYZ Corporation', '408-908-2567','400 North 1st Street, San Jose, CA, USA');

/*不同的电话，相同的地址，相同的名称，注意组合索引里是并且关系，不能存在相同的地址和名称，失败*/
INSERT INTO supplier(name, phone, address)
VALUES('XYZ Corporation', '408-908-102','400 North 1st Street, San Jose, CA, USA');

#移除唯一索引
DROP INDEX index_name ON table_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

2. 【强制】超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。

说明： 即使双表 join 也要注意表索引、 SQL 性能。
摘自https://www.zhihu.com/question/56236190/

MySQL一般使用的是“Nested Loop Join”，即嵌套连接。 算法类似如下：

For each tuple r in tbl1 
do
	 For each tuple s in tbl2 
	 	do    
	 		 If r and s satisfy the join condition      
	 		    Then output the tuple <r,s>
1
2
3
4
5
6

三表效率去到 O(n3),数据量大起来效率一般
二：在这种限制下SQL怎么写？
可是我确实需要两个表里的数据链接在一起啊，我们可以做个冗余，建表的时候，就把这些列放在一个表里
比如一开始有student(id, name)，class(id, description)，student_class(student_id, class_id)三张表，这样是符合数据库范式的(第一范式，第二范式，第三范式，BC范式等)
没有任何冗余，但是马上就不符合“编程规范“了，那我们可以用一张大表代替它，student_class_full(student_id, class_id, name, description)，这样name和description可能要被存储多份，但是由于不需要join了，查询的性能就可以提高很多了。任何的规范都是在特定情况下的某种妥协，脱离了这个环境，就不一定成立了。

2.1 题外话 为什么要小表驱动大表？

http://www.voidcn.com/article/p-goolyeks-bpz.html

outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);
outer_row = outer_iterator.next;
while(outer_row) {
    inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;
    inner_row = inner_iterator.next;
    while(inner_row) {
        output[inner_row.yy,outer_row.xx];
        inner_row = inner_iterator.next;
    }
    outer_row = outer_iterator.next;
}
1
2
3
4
5
6
7
8
9
10
11

过滤后留下的结果集M,N(M>N)
1.如果都走全表的话，大表做驱动和小表做驱动都是M*N
2.如果走索引的话：
个人猜测索引是根据一个hash算法来的，一个hash算法假设公式是 y = 30x+3000 ,我们也可以知道 x = (y-3000)/ 30 ,加入我们知道了y，也就是知道外循环的值，我们找x的效率就会高，所以外循环数量少了，y少了，速度就快了。个人猜测。

3. 【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可

说明： 索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定

参考了这篇文章：https://blog.csdn.net/zhoukun1008/article/details/68942893

现在有这么一个需求，比如说商品表中的商品名称和商品描述两个字段，有时候我们根据商品名称或者描述来查询商品，但是所有的名称或者商品描述又不同，其内容长度又长短不一，但是商品名称或者描述又是经常查询的字段，我们怎样给商品描述或者商品名称添加索引哪？也就是说在商品名称或者商品描述上面的索引长度应该设置多长最合适哪？

区分度与索引长度的权衡
索引长度越低，索引在内存中占的长度越小，排序越快，然而区分度就越低。这样不利于查找。

索引长度越长，区分度就高，虽然利于查找了，但是索引在内存中占得空间就多了。

计算title中，从左边数第10个不重复的汉字的行数与总行数的比
假设一个商品表叫item，商品名称叫 title，
select count(distinct left(title,10))/count(*) from item;

4. 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

说明： 索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引，全模糊也会失效。
全模糊使用elasticsearch才能解决这个问题

5. 【推荐】如果有 order by 的场景，请注意利用索引的有序性。

order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
正例： where a=? and b=? order by c; 索引： a_b_c
反例： 索引中有范围查找，那么索引有序性无法利用，如： WHERE a>10 ORDER BY b; 索引a_b 无法排序。

为什么？
我找了这篇索引的原理：http://blog.codinglabs.org/articles/theory-of-mysql-index.html 我只看懂了这个innodb的图。我们可以知道，当是组合索引的时候，innodb是按顺序来地址寻找的，假设a是开始的搜索，innodb会在a的data域去找他的b索引树，再在b的索引树再找C，树的命运决定了，一旦碰到范围查询，组合 索引大部分会失效，单值索引会有效，毕竟只插一张表。

6. 【推荐】利用覆盖索引来进行查询操作， 避免回表。

说明： 如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览
一下就好，这个目录就是起到覆盖索引的作用。
正例： 能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用 explain 的结果， extra 列会出现： using index。

为什么？我觉的不太需要解释，有索引就最好用索引来搜索数据，不要有索引不用，这时候其实

7. 【推荐】利用延迟关联或者子查询优化超多分页场景。

说明： MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。
正例： 先快速定位需要获取的 id 段，然后再关联：
SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

为什么？可以理解为 limit 10000,20 也是返回了一章 10020的表，这时候我们把他inner join，小表驱动大表，至少能够使用索引能够更快一些。

当数据量到达一百万时，这个也是很损耗性能的。
摘自：
https://blog.csdn.net/AinUser/article/details/72803175
https://segmentfault.com/a/1190000005007706

8. 【推荐】 SQL 性能优化的目标：至少要达到 range 级别， 要求是 ref 级别， 如果可以是 consts最好。

说明：
1） consts 单表中最多只有一个匹配行（主键或者唯一索引） ，在优化阶段即可读取到数据。
2） ref 指的是使用普通的索引（normal index） 。
3） range 对索引进行范围检索。
反例： explain 表的结果， type=index，索引物理文件全扫描，速度非常慢，这个 index 级
别比较 range 还低，与全表扫描是小巫见大巫。
摘抄来自：https://blog.csdn.net/xtdhqdhq/article/details/20377273

explain 可以分析 select 语句的执行，即 MySQL 的“执行计划。


一、type 列

MySQL 在表里找到所需行的方式。包括（由左至右，由最差到最好）：
| All | index | range | ref | eq_ref | const,system | null |

ALL（所有）
全表扫描，MySQL 从头到尾扫描整张表查找行。
mysql> explain select * from a\G
...
         type: ALL
如果加上 limit 如 select * from a limit 100 MySQL 会扫描 100 行，但扫描方式不会变，还是从头到尾扫描。

index（索引）
根据索引来读取数据，如果索引已包含了查询数据，只需扫描索引树，否则执行全表扫描和All类似；  
create table a(a_id int not null, key(a_id));
insert into a value(1),(2);
mysql> explain select a_id from a\G
...
         type: index

range(范围)
以范围的形式扫描索引
建表：
create table a(a_id int not null, key(a_id));
insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
mysql> explain select * from a where a_id > 1\G
...
         type: range
...

IN 比较符也会用 range 表示：
mysql> explain select * from a where a_id in (1,3,4)\G
...
         type: range
...

`
ref（引用）
非唯一性索引访问
建表：
create table a(a_id int not null, key(a_id));
insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
mysql> explain select * from a where a_id=1\G
...
         type: ref
...

eq_ref（等值引用）
使用有唯一性索引查找（主键或唯一性索引）
建表及插入数据：
create table a(id int primary key);
create table a_info(id int primary key, title char(1));
insert into a value(1),(2);
insert into a_info value(1, 'a'),(2, 'b');
mysql> explain select * from a join a_info using(id);
...+--------+--------+...
...| table  | type   |...
...+--------+--------+...
...| a      | index  |...
...| a_info | eq_ref |...
...+--------+--------+...
此时 a_info 每条记录与 a 一一对应，通过主键 id 关联起来，所以 a_info 的 type 为 eq_ref。
删除 a_info 的主键：ALTER TABLE  `a_info` DROP PRIMARY KEY;
现在 a_info 已经没有索引了：
mysql> explain select * from a join a_info using(id);
+----+...+--------+--------+...
| id |...| table  | type   |...
+----+...+--------+--------+...
|  1 |...| a_info | ALL    |...
|  1 |...| a      | eq_ref |...
+----+...+--------+--------+...
这次 MySQL 调整了执行顺序，先全表扫描 a_info 表，再对表 a 进行 eq_ref 查找，因为 a 表 id 还是主键。
删除 a 的主键：alter table a drop primary key;
现在 a 也没有索引了：
mysql> explain select * from a join a_info using(id);
...+--------+------+...
...| table  | type |...
...+--------+------+...
...| a      | ALL  |...
...| a_info | ALL  |...
...+--------+------+...
现在两个表都使用全表扫描了。


建表及插入数据：
create table a(id int primary key);
create table a_info(id int, title char(1), key(id));
insert into a value(1),(2);
insert into a_info value(1, 'a'),(2, 'b');
现在 a_info 表 id 列变为普通索引（非唯一性索引）：
mysql> explain select * from a join a_info using(id) where a.id=1;
...+--------+-------+...
...| table  | type  |...
...+--------+-------+...
...| a      | const |...
...| a_info | ref   |...
...+--------+-------+...
a_info 表 type 变为 ref 类型了。
所以，唯一性索引才会出现 eq_ref （非唯一性索引会出现 ref ），因为唯一，所以最多只返回一条记录，找到后无需继续查找，因此比 ref 更快。

const（常量连接）
被称为“常量”，这个词不好理解，不过出现 const 的话就表示发生下面两种情况：
在整个查询过程中这个表最多只会有一条匹配的行，比如主键 id=1 就肯定只有一行，只需读取一次表数据便能取得所需的结果，且表数据在分解执行计划时读取。返回值直接放在 select 语句中，类似 select 1 AS f 。可以通过 extended 选择查看内部过程：

建表及插入数据：
create table a(id int primary key, c1 char(20) not null, c2 text not null, c3 text not null);
insert into a values(1, 'asdfasdf', 'asdfasdf', 'asdfasdf'), (2, 'asdfasdf', 'asdfasdf', 'asdfasdf');
mysql> explain extended select * from a where id=1\G
...
         type: const
possible_keys: PRIMARY
          key: PRIMARY
...
用 show warnings 查看 MySQL 是如何优化的：
mysql> show warnings\G
...
Message: select '1' AS `id`,'asdfasdf' AS `c1`,'asdfasdf' AS `c2`,'asdfasdf' AS
`c3` from `test`.`a` where 1
查询返回的结果为：
mysql> select * from a where id=1;
+----+----------+----------+----------+
| id | c1       | c2       | c3       |
+----+----------+----------+----------+
|  1 | asdfasdf | asdfasdf | asdfasdf |
+----+----------+----------+----------+
可以看出，返回结果中的字段值都以“值 AS 字段名”的形式直接出现在优化后的 select 语句中。
修改一下查询：
mysql> explain select * from a where id in(1,2)\G
...
         type: range
...
当返回结果超过 1 条时， type 便不再为 const 了。
重新建表及插入数据：
create table a (id int not null);
insert into a value(1),(2),(3);
mysql> explain select * from a where id=1\G
...
         type: ALL
目前表中只有一条 id=1 的记录，但 type 已为 ALL ，因为只有唯一性索引才能保证表中最多只有一条记录，只有这样 type 才有可能为 const 。
为 id 加普通索引后， type 变为 ref ，改为加唯一或主键索引后， type 便变为 const 了。



二、Extra 列
Extra表示附加信息，常见的有如下几种（也按查询效率从高到低排列）：

Using index：表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，这个叫覆盖索引。如果同时出现Using where，代表使用索引来查找读取记录， 也是可以用到索引的，但是需要查询到数据表。
Using where：表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where。如果type列是ALL或index，而没有出现该信息，则你有可能在执行错误的查询：返回所有数据。  
Using filesort：不是“使用文件索引”的含义！filesort是MySQL所实现的一种排序策略，通常在使用到排序语句ORDER BY的时候，会出现该信息。  
Using temporary：表示为了得到结果，使用了临时表，这通常是出现在多表联合查询，结果排序的场合。

如果EXPLAIN出现后面两个信息（Using filesort，Using temporary），而rows又比较大，通常意味着你需要调整查询语句，或者需要添加索引，总之需要尽量消除这两个信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156

9. 【推荐】建组合索引的时候，区分度最高的在最左边。

正例： 如果 where a=? and b=? ， 如果 a 列的几乎接近于唯一值，那么只需要单建 idx_a
索引即可。
说明： 存在非等号和等号混合时，在建索引时，请把等号条件的列前置。如： where c>? and
d=? 那么即使 c 的区分度更高，也必须把 d 放在索引的最前列， 即索引 idx_d_c。

为什么？这个的原因其实很简单，区分度高，肯定索引的效率高，索引是一个寻找查树的过程，需要更快的命中，而如果有范围查询，要使索引有效，必须要等值查询，所以d要放在前方

10.【推荐】 防止因字段类型不同造成的隐式转换， 导致索引失效。

例子：http://ourmysql.com/archives/970

CREATE TABLE `indextest` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`age` tinyint(3) unsigned NOT NULL DEFAULT '0',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_age` (`age`),
KEY `idx_create` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

INSERT INTO `indextest` (`id`, `name`, `age`, `create_time`) VALUES ('1', 'hello', '10', '2019-01-07 01:59:20');
INSERT INTO `indextest` (`id`, `name`, `age`, `create_time`) VALUES ('2', 'world', '20', '2019-01-07 01:59:28');
INSERT INTO `indextest` (`id`, `name`, `age`, `create_time`) VALUES ('3', '111222', '30', '2019-01-07 01:59:38');
INSERT INTO `indextest` (`id`, `name`, `age`, `create_time`) VALUES ('4', 'wow', '40', '2019-01-07 01:59:50');

/*
MySQL 的文档 (Type Conversion in Expression Evaluation) 中提到，在做比较时，会按这样的规则进行必要的类型转换：

两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
两个参数都是字符串，会按照字符串来比较，不做类型转换
两个参数都是整数，按照整数来比较，不做类型转换
十六进制的值和非数字做比较时，会被当做二进制串，和数字做比较时会按下面的规则处理
有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
所有其他情况下，两个参数都会被转换为浮点数再进行比较
*/

#正确命中，因为使用了正确的类型
explain select age from  indextest where name='111222'
/*
按照这些规则，对于上面的例子来说，name 字段的值和查询参数 ’111222′ 都会被转换为浮点数才会做比较
，而很多文本都能转换为和 111222 相等的数值，比如 ’111222′, ’111222aabb’, ‘ 111222′ 和 ’11122.2e1′，
所以 MySQL 不能有效使用索引，就退化为索引扫描甚至是全表扫描。
*/
explain select age from  indextest where name=111222
select '111222aabb' = 111222 #返回true

/*MySQL 可以将查询参数 ’30′ 转换为确定的数值 30，之后可以快速地在索引中找到与之相等的数值。*/
explain select name from indextest where age='30'

#使用函数对索引字段做显式类型转换或者计算也会使 MySQL 无法使用索引
explain select name from  indextest where cast(age as unsigned)=30 #ALL，也和
/*
使用 between 有效 使用
对于本例的需求，是想查找 create_time 是 2012-02-02 这一天的记录，用变通的方法，避免在索引字段上做运算就可以有效使用索引了：
*/
explain select * from indextest where create_time between '2012-02-02' and '2012-02-03'

#也可以使用，因为是单值索引，这个例子有点不妥当
explain select * from indextest where create_time >='2012-02-02'  and create_time < '2012-02-05'


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

11.【参考】创建索引时避免有如下极端误解：

1） 宁滥勿缺。 认为一个查询就需要建一个索引。
2） 宁缺勿滥。 认为索引会消耗空间、严重拖慢更新和新增速度。
3） 抵制惟一索引。 认为业务的惟一性一律需要在应用层通过“先查后插”方式解决