【mysql】--高性能索引策略

下面的内容是学习《高性能mysql第三版》这本书的总结，有不对的地方请留言指教。
下面策略中重点关注：前缀索引、联合索引的选择顺序、覆盖索引。

一、独立的列

索引列不能是表达式的一部分，也不能是函数的参数。

二、前缀索引

对于BLOB、TEXT或很长的VARCHAR类型的列，必须使用前缀索引，因为mysql不允许索引这些列的完整长度。
前缀索引一般适用于 like '%'查询，无法使用前缀索引做order by和 group by，也无法*使用前缀索引做覆盖扫描。
使用字段的一定前缀长度构建索引。前缀的“基数”应接近于完整列的“基数”，但又不能太长(为了节省空间)，需要选择合适的前缀长度。实验表明，前缀长度为7时是最佳的。

2.1、具体例子

【下面的例子是自己真实验证】
有两个表content_191008_test和content_191125_p 有数据量都在380万左右，其中title varchar(150)。这是一个长的varchar类型列。
content_191008_test 创建一个前缀索引。另一个表不创建索引。

alter table content_191008_test add key (title(7));

执行like '**%'查询操作。
[SQL]
SELECT * from content_191008_test where title like ‘到了土耳%’;
受影响的行: 0
时间: 0.032s

[SQL]
SELECT * from content_191125_p where title like ‘到了土耳%’;
受影响的行: 0
时间: 18.391s
-----上面结果明显发现，加了前缀索引比不加前缀索引提升了574倍。

三、合适的(联合)索引列顺序

经验法则：将选择性最高的列放到索引最前列。
不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。

这部分主要讲解如何创建联合索引，按照“经验法则”，那些“基数”越大的放在前面更好，但还是需要根据实际情况下来决定。
比如说userId、createTime两个字段，createTime的基数一般来说比userId要大，但在实际情况下，createTime很多情况是进行范围查询，这样就破坏了"联合索引的最左原则"，导致很多的查询都受限不走索引。除非，多建立一个索引，这样在数据量很大的场景下，多一个索引，在更新时会引起大量失效，造成额外的开支。
这里只是举个例子分析，那么在具体应用场景中，还是根据业务场景来决定怎么选择联合索引的列顺序。
下面只是客观的介绍怎么计算“选择性高”的方法。

3.1、如何计算“选择性”，来调整索引列顺序？

如下面create_time、user_id、publish_time、status四个字段需要建立联合索引，那么下面分析来选择合适的索引列顺序。
content_191125_p表和content_191125_p1表，都有390万的数据。利用公式计算如下：

SELECT count(DISTINCT create_time)/count(*) AS cr_s,
 count(DISTINCT user_id)/count(*) AS ta_s,
 count(DISTINCT publish_time)/count(*) AS pu_s,
 count(DISTINCT status)/count(*) AS st_s,
count(*) from content_191125_p;

SELECT count(DISTINCT create_time)/count(*) AS cr_s,
 count(DISTINCT user_id)/count(*) AS ta_s,
 count(DISTINCT publish_time)/count(*) AS pu_s,
 count(DISTINCT status)/count(*) AS st_s,
count(*) from content_191125_p1;

基于上面的结果，对于两个表分别建立索引
content_191125_p表索引是

content_191125_p1表索引是

建立索引后，实际查询的结果如下：

[SQL]
SELECT * from content_191125_p where status=0 and user_id=‘132790262404’ and publish_time>=‘2019-11-25 14:17:13’;
受影响的行: 0
时间: 10.047s

[SQL]
SELECT * from content_191125_p where create_time =‘2019-11-25 14:16:48’ and publish_time>=‘2019-11-25 14:17:13’ and user_id=‘132790262404’ and status=0;
受影响的行: 0
时间: 0.020s

[SQL]

SELECT * from content_191125_p1 where create_time =‘2019-11-25 14:16:48’ and publish_time>=‘2019-11-25 14:17:13’ and user_id=‘132790262404’;
受影响的行: 0
时间: 10.292s

[SQL]
SELECT * from content_191125_p1 where status=0 and user_id=‘132790262404’ and publish_time>=‘2019-11-25 14:17:13’ and create_time =‘2019-11-25 14:16:48’;
受影响的行: 0
时间: 0.024s

—按照上面的分析，明显联合索引比没索引速度要快。
但并没有验证出“选择性最高的列放到索引最前列”的效果在哪？ --留着待后续再思考。

四、覆盖索引和索引覆盖

覆盖索引：
Select的数据列不必从数据表读取，直接从二级索引中就可以取得。
一个索引包含(或覆盖)所有需要查询的字段的值，称为覆盖索引。【索引的叶子节点已经包含要查询的数据】

索引覆盖：只通过索引，就能取到所需要的数据。

五、冗余和重复索引

重复索引：相同的列上按照相同的顺序创建的相同类型的索引。
冗余索引：比如索引(A)，又创建索引(A,B)，那么索引(A)冗余了。

-------尽量避免处理冗余和重复索引。