Mysql索引

Wesley13
• 阅读 228

  有时候需要索引很长的字符列,如 BLOB、TEXT 或者很长的 VARCHAR 类型的列,这会让索引变得很大,导致查询很慢。对于这种情况,我们可以使用前缀索引来索引开始的部分字符,这样可以大大的节约索引空间,从而提高索引效率。但这样也会降低索引的选择性。

  索引的选择性是指:不重复的索引值(也称为基数,cardinality)和数据表的记录总数(_#T_)的比值,范围从1/#T到1之间。

  索引的选择性越高则查询效率越高,因为选择性高的索引可以过滤更多的行。唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。如下示例:

 1 // 创建表
 2 CREATE TABLE sakila.city_demo(
 3     city VARCHAR(50) NOT NULL
 4 );
 5 
 6 // 自我复制
 7 INSERT INTO sakila.city_demo(city) SELECT `city` FROM sakila.city;
 8 
 9 // 从城市表中随机赋值数据到城市测试表
10 UPDATE sakila.city_demo SET city = (SELECT city FROM sakila.city ORDER BY RAND() LIMIT 1);

现在我们已经有了一个测试数据集,现在,我们来统计城市表中,最常见的城市:

 1 SELECT 
 2     COUNT(*) AS cnt, 
 3     city
 4 FROM
 5     sakila.city_demo 
 6 GROUP BY 
 7     city 
 8 ORDER BY 
 9     cnt DESC 
10 LIMIT 10;

结果集如下:

Mysql索引

以上的10个城市就是最频繁出现的城市前缀,现在先从3个前缀字母开始:

 1 SELECT 
 2     COUNT(*) AS cnt , 
 3     LEFT(city, 3) AS pref
 4 FROM
 5     sakila.city_demo
 6 GROUP BY 
 7     pref
 8 ORDER BY
 9     cnt DESC
10 LIMIT 10;

结果集如下:

Mysql索引

 由上结果可知,每个前缀都比原来的城市出现的次数更多,因此唯一前缀比唯一城市要少得多。然后我们继续增加长度测试,直到这个前缀的选择性接近完整列的选择性。最后,我们发现,当长度为7时,最适合:

Mysql索引

计算合适的前缀长度的另外一个方法就是计算完整性的选择性,并使前缀的选择性接近于完整列的选择性。下面显示如何计算完整列的选择性:

1 SELECT
2     COUNT(DISTINCT city)/COUNT(*) 
3 FROM 
4     sakila.city_demo;

结果集为:

Mysql索引

通常来说,如果前缀的选择性能够接近0.031,基本已经可以用了。当然,也可以在一个查询中,针对不同前缀长度进行计算,这对于大表非常有用。

1 SELECT
2     COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
3     COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
4     COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
5     COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
6     COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7,
7 FROM
8     sakila.city_demo;

结果集如下:

Mysql索引

查询显示当前缀索引长度到达7的时候,再增加前缀长度,选择性提升的幅度已经很小了,处于索引长度越长,索引越大,查询越慢的考虑,所以长度为7是比较适合的。

当然,只看平均选择性是不够的,也有例外的情况。根据上面的平均选择性来看,你可能会认为前缀长度为4或者5的索引已经足够了,但如果数据分布很不均匀,可能会存在陷阱,现在我们来观察前缀长度为4的最长出现城市的次数:

Mysql索引

由上可知,如果前缀是4个字节,则最常出现的前缀的出现次数比最常出现的城市的出现次数要大很多。即这些值的选择性比平均选择性要低。

下面我们来演示如何创建前缀索引:

ALTER TABLE sakila.city_demo ADD KEY (city(7));

优点:

  能使索引更小、更快。

缺点:

  无法使用前缀索引进行ORDER BY 和 GROUP BY ,也无法使用前缀索引做覆盖扫描。

常见的应用场景:

  针对很长的十六进制唯一ID使用前缀索引。

参考资料:

  高性能MySQL(第3版)

点赞
收藏
评论区
推荐文章
添砖java的啾 添砖java的啾
1年前
distinct效率更高还是group by效率更高?
目录00 结论01 distinct的使用02 group by的使用03 distinct和group by原理 04 推荐group by的原因 00结论 先说大致的结论(完整结论在文末):在语义相同,有索引的情况下group by和distinct都能使用索引,效率相同。在语义相同,无索引的情况下:distinct效率高于group by。原因是di
blmius blmius
1年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录 问题 用navicat导入数据时,报错: 原因这是因为当前的MySQL不支持datetime为0的情况。 解决修改sql\mode: sql\mode:SQL Mode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。 全局s
Peter20 Peter20
1年前
什么是索引?Mysql目前主要的几种索引类型
一、索引MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。创
Wesley13 Wesley13
1年前
MySQL千万级别优化·中
MySQL 千万级别的查询优化手段·中 ------------------- #### 单列索引(假设在 v\_record 表中存在 id 列的索引) **1、WHERE 条件使用** ​ EXPLAIN SELECT \* FROM v\_record WHERE id = 2 ​ 结论:利用索引进行回表查询 **2、SELECT 字段使用*
Wesley13 Wesley13
1年前
MySQL索引类型
### 一、简介 MySQL目前主要有以下几种索引类型: 1.普通索引 2.唯一索引 3.主键索引 4.组合索引 5.全文索引 ### 二、语句 CREATE TABLE table_name[col_name data type] [unique|fulltext][index|key][index_name](c
Wesley13 Wesley13
1年前
MySQL索引的索引长度问题
MySQL的每个单表中所创建的索引长度是有限制的,且对不同存储引擎下的表有不同的限制。 在MyISAM表中,创建组合索引时,创建的索引长度不能超过1000,注意这里索引的长度的计算是根据表字段设定的长度来标量的,例如: create table test(id int,name1 varchar(300),name2 varchar(300),nam
Wesley13 Wesley13
1年前
MySQL之索引(四)
**压缩索引** MyISAM使用前缀压缩来减少索引的大小,从而让更多的索引可以放入内存中,这在某些情况下能极大地提高性能。默认只压缩字符串,但通过参数设置也可以对整数做压缩。 MyISAM压缩每个索引块的方法是,先完全保存索引块中的第一个值,然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分,把这部分存储起来即可。例如,索引块中的第
Stella981 Stella981
1年前
ELK学习笔记之ElasticSearch的索引详解
0x00 ElasticSearch的索引和MySQL的索引方式对比 ---------------------------------- Elasticsearch是通过Lucene的倒排索引技术实现比关系型数据库更快的过滤。特别是它对多条件的过滤支持非常好,比如年龄在18和30之间,性别为女性这样的组合查询。 倒排索引很多地方都有介绍,但是其比关系型
Wesley13 Wesley13
1年前
Java面试通关要点汇总集之核心篇参考答案
核心篇 --- ### 数据存储 * MySQL 索引使用的注意事项 > 1.索引不会包含有NULL的列 > > 只要列中包含有NULL值,都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此符合索引就是无效的。 > > > 2.使用短索引
3A网络 3A网络
2个月前
开发一个不需要重写成 Hive QL 的大数据 SQL 引擎
# 开发一个不需要重写成 Hive QL 的大数据 SQL 引擎 学习大数据技术的核心原理,掌握一些高效的思考和思维方式,构建自己的技术知识体系。明白了原理,有时甚至不需要学习,顺着原理就可以推导出各种实现细节。 各种知识表象看杂乱无章,若只是学习
3A网络 3A网络
2个月前
理解 virt、res、shr 之间的关系(linux 系统篇)
# 理解 virt、res、shr 之间的关系(linux 系统篇) **前言** 想必在 linux 上写过程序的同学都有分析进程占用多少内存的经历,或者被问到这样的问题 —— 你的程序在运行时占用了多少内存(物理内存)? 通常我们可以通过 t