下面的内容是学习《高性能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)冗余了。
-------尽量避免处理冗余和重复索引。