mysql 索引建立准则

一、建立多列索引时，如何选择先后顺序

问题：建立索引令staff_id还是customer_id在前？

准则1经验准则：第一个索引筛选后，留下尽可能少的数据。

SELECT SUM(staff_id=2),Sum(customer_id=584) FROM payment

SUM(staff_id=2):7992   Sum(customer_id=584):30

所以customer_id对应条件值后，数目更少

 

但是经验准则非常依赖于具体值，所以我们一般使用准则2，令全局尽可能最优

 

准则2：选择性高的列，放在前面

SLECT COUNT(DISTINCT staff_id)/count(*)  AS staff_id_selectivity,

COUNT(DISTINCT customer_id)/count(*) AS customer_id_selectivity,

COUNT(*)

FROM payment

========================

staff_id_selectivity:0.0001

customer_id_selectivity:0.0373

COUNT(*):16049

customer_id选择性更高（也就是指定具体值后，出来的条目更少），所以将customer_id作为索引的第一列

选择性定义：不同值的条目/总条目，就是 可选择的东西的多少的意思。

 

注意，可能有一个问题，customer_id如果可选择性很高，但是其中某一个customer_id的数据条目非常多（比如customer_id=0代表未登录的客户），刚好未登录的客户又占大多数，

这个时候用这个索引顺序查找就会失效。

 

解决办法是在程序中禁止对这个id进行这条查找语句（需要单独写一个针对这个的查找语句）

 

二、利用覆盖索引直接获取数据

覆盖索引是一种普通的B-TREE多列索引（就是我们平时说的最多的），但是当这个多列索引覆盖我们要查找的所有列的时候，我们称之为索引可覆盖。

索引条目通常远小于数据行大小(比如b-tree的节点数会远小于数据条目)，所以读取索引会比读取数据快得多。

 

三、索引支持的操作符

>, <, <>, =,

 

不确定：!=

需要注意： like 只支持最左前缀匹配，比如where customer_id like "305%" 但是不可以like "%305"以通配符开头

 

四、explain 语句查询结果字段说明

id: 有几个select语句，就有几个id，

select_type: 

table:查找的表

type: all, index, 

possible_keys:

key:

rows:

extra:

 

关于explain的详解：https://www.jianshu.com/p/ea3fc71fdc45

 

 

四、对于createtime是否建立索引，有几个问题要考虑。

1、当我们在where里，使用createtime的时候，是否会取具体的某一天？或者某一个小时？因为createtime极少令createtime=156542315，

我们一般取一个范围，这就导致了即便createtime作为索引，它的可选择性很高，但是查找的性能却很差，因为我们需要扫描大量的，在这个范围内的条目。

这个跟id有点类似，我们不会取id>1，虽然选择性很高，但是扫描的条目依然很多，没有任何效率的提升。