MySQL 隔离数据列和前缀索引的使用总结

隔离数据列

通常，我们会发现查询语句会妨碍MySQL使用索引。除非在查询语句中列是独立的，否则MySQL不会使用这些列的索引。“隔离”的意思是索引列不应该成为表达式的一部分或者在一个查询函数体中。例如下面的例子就不会命中actor_id这个索引。

SELECT `actor_id` FROM `actor` WHERE `actor_id` + 1 = 2;

SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

通常是可以将索引列前面的部分字符建立索引来替换全字段索引提高性能和节省空间。但这种方式会使得选择性变差。索引的选择性是指独立的索引值筛选出的数据占整个数据集合的比例。高选择性的索引可以让MySQL过滤掉更多无关的数据。例如，一个唯一索引的选择性是1。列的前缀通常在选择性方面已经能够提供足够好的性能。如果使用BLOB或TEXT或非常长的VARCHAR字段列，你必须定义前缀索引，以为MySQL不允许做全长度索引。

你需要在使用更长的前缀以获得更好的选择性和足够短的前缀以节省存储空间之间平衡。为了确定一个合适的前缀长度，查找出最高频的值，然后和最频繁的前缀进行比较。例如以城市数据表为例，我们可以使用如下的语句统计：

SELECT COUNT(*) as cnt, `name` FROM `common_city` GROUP BY `name` ORDER BY cnt DESC LIMIT 10

SELECT COUNT(*) as cnt, LEFT(`name`, 1) as pref FROM `common_city` GROUP BY pref ORDER BY cnt DESC LIMIT 10

SELECT COUNT(*) as cnt, LEFT(`name`, 3) as pref FROM `common_city` GROUP BY pref ORDER BY cnt DESC LIMIT 10

SELECT   COUNT(DISTINCT LEFT(`name`, 1)) / COUNT(`name`) as pref1,   COUNT(DISTINCT LEFT(`name`, 2)) / COUNT(`name`) as pref2,   COUNT(DISTINCT LEFT(`name`, 3)) / COUNT(`name`) as pref3,   COUNT(DISTINCT LEFT(`name`, 4)) / COUNT(`name`) as pref4 FROM `common_city`

ALTER TABLE `common_city` ADD KEY (name(3));

隔离数据列

更多相关文章

随机推荐