MYSQL联合索引

explain SELECT * FROM  videos WHERE `online` = 1 and publish > 0 and update_time >= '1628651608' AND update_time <= '1629256408' LIMIT 1000000, 20

 

 

explain SELECT * FROM videos WHERE `online` = 1 AND kind='电视' and publish > 0 and update_time >= '1628651608' AND update_time <= '1629256408' LIMIT 1000000, 20

 

 

explain SELECT * FROM videos WHERE `online` = 1 AND kind='电视' and publish > 0 and update_time >= '1628651608' AND update_time <= '1629256408' ORDER BY update_time desc LIMIT 1000000, 20

 

 

 

explain SELECT * FROM videos WHERE `online` = 1 and update_time >= '1628651608' AND update_time <= '1629256408' ORDER BY update_time desc LIMIT 1000000, 20

 

 去掉索引idx_online中的update_time, (新增index索引update_time 或者去掉update_time索引)：【5.531s】

explain SELECT * FROM videos WHERE `online` = 1 and update_time >= '1628651608' AND update_time <= '1629256408' ORDER BY update_time desc LIMIT 1000000, 20

 ---------------------------------------------------------------------------------------------------------------------------------------------------

针对以下两种情况：limit 0,200 起始值较小时候，加update_time索引查询更快，随着limit越来越大，不加update_time索引查询更快。

保留索引idx_online中的update_time, 新增index索引update_time：【3.656s】

 

 

 

 保留索引idx_online中的update_time：【3.235】

 

 

filtered:表示返回结果的行数占需读取行数的百分比 Filtered列的值越大越好 Filtered列的值依赖于统计信息

Cardinality解释
官方文档的解释：
An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing
总结一下：
1、它代表的是索引中唯一值的数目的估计值。如果是myisam引擎，这个值是一个准确的值。如果是innodb引擎，这个值是一个估算的值，每次执行show index 时，可能会不一样
2、创建Index时（primary key除外），MyISAM的表Cardinality的值为null，InnoDB的表Cardinality的值大概为行数；
3、值的大小会影响到索引的选择
4、创建Index时，MyISAM的表Cardinality的值为null，InnoDB的表Cardinality的值大概为行数。
5、可以通过Analyze table来更新一张表或者mysqlcheck -Aa来进行更新整个数据库
6、可以通过 show index 查看其值