代码之家 › 专栏 › 技术社区 › Napster Long

为什么前缀索引比mysql中的索引慢?

sql-optimization indexing mysql

Napster Long · 技术社区 · 8 年前

表:(数量2100W)

CREATE TABLE `prefix` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `number` int(11) NOT NULL,
  `string` varchar(750) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_string_prefix10` (`string`(10)),
  KEY `idx_string` (`string`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

歧视:

select count(distinct(left(string,10)))/count(*) from prefix;
+-------------------------------------------+
| count(distinct(left(string,10)))/count(*) |
+-------------------------------------------+
|                                    0.9999 |
+-------------------------------------------+

结果:

select sql_no_cache count(*) from prefix force index(idx_string_prefix10) 
where string <"1505d28b"
243.96sï¼241.88s

select sql_no_cache count(*) from prefix force index(idx_string) 
where string < "1505d28b"
7.96sï¼7.21sï¼7.53s

为什么前缀索引比mysql中的索引慢?234;¼原谅我破碎的英语ê¼

explain select sql_no_cache count(*) from prefix force index(idx_string_prefix10) 
where string < "1505d28b";

+----+-------------+--------+------------+-------+---------------------+---------------------+---------+------+---------+----------+-------------+
| id | select_type |  table | partitions |  type |       possible_keys |                 key | key_len |  ref |    rows | filtered |       Extra |
+----+-------------+--------+------------+-------+---------------------+---------------------+---------+------+---------+----------+-------------+
|  1 |      SIMPLE | prefix |       NULL | range | idx_string_prefix10 | idx_string_prefix10 |      42 | NULL | 3489704 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------------+---------------------+---------+------+---------+----------+-------------+