这是我参与更文挑战的第5天,活动详情查看: 更文挑战
前言
MySQL 数据库中如果想要高效快速的查询数据,正确的创建和使用索引是关键,本文会介绍如何真正的发挥这些索引的优势。
独立的列
如果查询中的列不是独立的,则MySQL就不会使用索引。“独立的列”指的是索引列不能是表达式的一部分,也不能是函数的参数。
索引列不能是表达式的一部分
比如我们想要获取成绩表中扣分数小于10分的学生,我们可能写的sql如下
SELECT * FROM `chinese_score` WHERE 120- total_score < 10
那么,不会命中索引,因为索引列作为表达式的一部分出现了
如果要使用索引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创建
前缀索引是一种能使索引更小、更快的有效办法,但另一方面也有其缺点:MySQL无法使用前缀索引做ORDER BY和GROUP BY,也无法使用前缀索引做覆盖扫描。
总结
今天我们讲解了MySQL如何创建高性能的索引的介绍,明天我们还将继续介绍其他建立高性能索引的方法,敬请期待,下篇再见!