MySQL高性能索引策略(一)

230 阅读2分钟

这是我参与更文挑战的第5天,活动详情查看: 更文挑战

前言

MySQL 数据库中如果想要高效快速的查询数据,正确的创建和使用索引是关键,本文会介绍如何真正的发挥这些索引的优势。

独立的列

如果查询中的列不是独立的,则MySQL就不会使用索引。“独立的列”指的是索引列不能是表达式的一部分,也不能是函数的参数。

索引列不能是表达式的一部分

比如我们想要获取成绩表中扣分数小于10分的学生,我们可能写的sql如下

SELECT	* FROM	`chinese_score` WHERE	120- total_score < 10

那么,不会命中索引,因为索引列作为表达式的一部分出现了

image.png

如果要使用索引sql应该 先将扣分数后总分在程序中计算出来之后再查询

SELECT	* FROM	`chinese_score` WHERE total_score >110

索引列不能是函数的参数

比如我们想查询发货时间和收货时间大于10天的数据

SELECT ... WHERE TO_DAYS(Receive_Date) - TO_DAYS(Send_Date) > 10;

索引列作为函数的参数,这个sql也用不了索引。

前缀索引和索引选择性

索引很长的列的时候可以索引开始的部分字符,这样可以大大节约索引空间,进而提升索引效率,当然同时也降低了索引的选择性,也就是不重复的索引值和数据表(数量为T)的记录总数的比值,范围从1/T到1之间,选择性越高则查询效率越高,唯一索引的选择性是1。
对于BLOB和TEXT或者很长的VARCHAR类型的列,必须使用前缀索引。
计算合适的前缀长度的另外一个办法就是计算完整列的选择性,并使前缀的选择性接近于完整列的选择性。 计算完整列的选择性

SELECT COUNT(DISTINCT name)/COUNT(*) FROM `db_team`.`订单商品统计`;

计算不同前缀长度的选择性

SELECT COUNT(DISTINCT LEFT(name,3))/COUNT(*) as sel3,
      COUNT(DISTINCT LEFT(name,4)/COUNT(*) as sel4,
      COUNT(DISTINCT LEFT(name, 5))/COUNT(*) as se15,
      COUNT(DISTINCT LEFT(name, 6))/COUNT(*) as sel6,
      COUNT(DISTINCT LEFT(name, 7))/CoUNT(*) as sel7
      FROM `db_team`.`订单商品统计`;

需要注意的是前缀索引创建时需要注意那些数据分布不均匀的情况,防止在搜索这些数据的时候选择性可能非常糟糕。 使用语句创建前缀索引

ALTER TABLE `db_team`.`订单商品统计` 
ADD INDEX `idx_name`(`商品名称`(5));

直接用Navicat创建

image.png

前缀索引是一种能使索引更小、更快的有效办法,但另一方面也有其缺点:MySQL无法使用前缀索引做ORDER BY和GROUP BY,也无法使用前缀索引做覆盖扫描。

总结

今天我们讲解了MySQL如何创建高性能的索引的介绍,明天我们还将继续介绍其他建立高性能索引的方法,敬请期待,下篇再见!