一文简单了解MySQL前缀索引

当要索引的列字符很多时 索引则会很大且变慢

( 可以只索引列开始的部分字符串 节约索引空间 从而提高索引效率 )

原则: 降低重复的索引值

例如现在有一个地区表

area gdp code
chinaShanghai 100 aaa
chinaDalian 200 bbb
usaNewYork 300 ccc
chinaFuxin 400 ddd
chinaBeijing 500 eee

发现 area 字段很多都是以 china 开头的

那么如果以前1-5位字符做前缀索引就会出现大量索引值重复的情况

索引值重复性越低 查询效率也就越高

前缀索引测试

// 创建一个测试表
CREATE TABLE `x_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `x_name` varchar(255) NOT NULL,
  `x_time` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4145025 DEFAULT CHARSET=utf8mb4

// 添加200万条测试数据
INSERT INTO x_test(x_name,x_time) SELECT CONCAT(rand()*3300102,x_name),x_time FROM x_test WHERE id < 30000;

200万 测试数据

  • 在无任何索引的情况下随便查询一条
    SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';查询时间:2.253s
  • 添加前缀索引 ( 以第一位字符创建前缀索引 )
    alter table x_test add index(x_name(1))再次查询相同sql语句
    SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';查询时间:3.291s
    当使用第一位字符创建前缀索引后 貌似查询的时间更长了
    因为只第一位字符而言索引值的重读性太大了
    200万条数据全以数字开头那么平均20万条的数据都是相同的索引值
  • 重新建立前缀索引 这次以前4位字符来创建
    alter table x_test add index(x_name(4));再次查询相同sql语句
    SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';查询时间:0.703s
    这次以前4位创建索引 大大减少了索引值的重复性 查询速度从3秒提升到0.7秒
  • 200万条数据都以数字开头 而0-9排列组合7位则可达到千万种组合
    也就是以前7位来做索引则不会出现重复索引值的情况了
    alter table x_test add index(x_name(7));再次查询相同sql语句
    SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';查询时间:0.014s ( 首次执行无缓存状态下 )

补充:使用前缀索引的执行过程

  1. 从index2的索引树上,找到满足索引值是“zhangs”的记录,找到第一个是ID1;
  2. 到主键索引树上查到ID1这一行,判断email的值满不满足where后的条件,不满足这一行丢弃。
  3. 继续回到index2这个索引树上查下一条记录,发现如果还是"zhangs",取出ID2,再回到ID2索引树上进行判断,如果值正确,将结果返回结果集中。
  4. 重复执行以上流程,直到从index2索引树上取出的数据不是“zhangs”,循环结束。

通过以上执行流程的分析你就可以知道,前缀索引会导致扫描的行数变多,这和你所指定前缀的长度有关。或许email(7)中的区分度就比email(6)高,就不会扫描那么多行。

也即是说使用前缀索引,定义好长度,就可以节省空间又不用额外增加太多的查询成本

那怎样定义前缀索引长度比较好呢?

实际上,建立索引时关注的是区分度,区分度越高,越能体现索引的价值和他的优势。因此我们可以通过统计索引上有不同的值来判断要使用多长的前缀。

select count(distinct email) as L from User;

MySQL 5.7 参考手册 - 前缀索引

总结

到此这篇关于MySQL前缀索引的文章就介绍到这了,更多相关MySQL前缀索引内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 通过实例认识MySQL中前缀索引的用法

    今天在测试环境中加一个索引时候发现一警告 root@test 07:57:52>alter table article drop index ind_article_url; Query OK, 144384 rows affected (16.29 sec) Records: 144384 Duplicates: 0 Warnings: 0 root@test 07:58:40>alter table article add index ind_article_url(url); Query

  • 图文详解Mysql索引的最左前缀原则

    目录 前言 1. 定义 2. 全索引顺序 3. 部分索引顺序 3.1 正序 3.2 乱序 4. 模糊索引 5. 范围索引 总结 前言 之所以有这个最左前缀索引 归根结底是mysql的数据库结构 B+树 在实际问题中 比如 索引index (a,b,c)有三个字段, 使用查询语句select * from table where c = '1' ,sql语句不会走index索引的 select * from table where b =‘1’ and c ='2' 这个语句也不会走index索引

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

    隔离数据列 通常,我们会发现查询语句会妨碍MySQL使用索引.除非在查询语句中列是独立的,否则MySQL不会使用这些列的索引."隔离"的意思是索引列不应该成为表达式的一部分或者在一个查询函数体中.例如下面的例子就不会命中actor_id这个索引. SELECT `actor_id` FROM `actor` WHERE `actor_id` + 1 = 2; 对于人来说,很容易知道查询条件实际是actor_id = 4,但是MySQL不会这么处理,因此养成简化WHERE判决条件的习惯,

  • MySQL前缀索引导致的慢查询分析总结

    前端时间跟一个DB相关的项目,alanc反馈有一个查询,使用索引比不使用索引慢很多倍,有点毁三观.所以跟进了一下,用explain,看了看2个查询不同的结果. 不用索引的查询的时候结果如下,实际查询中速度比较块. 复制代码 代码如下: mysql> explain select * from rosterusers limit 10000,3 ; +----+-------------+-------------+------+---------------+------+---------+-

  • 一文简单了解MySQL前缀索引

    当要索引的列字符很多时 索引则会很大且变慢 ( 可以只索引列开始的部分字符串 节约索引空间 从而提高索引效率 ) 原则: 降低重复的索引值 例如现在有一个地区表 area gdp code chinaShanghai 100 aaa chinaDalian 200 bbb usaNewYork 300 ccc chinaFuxin 400 ddd chinaBeijing 500 eee 发现 area 字段很多都是以 china 开头的 那么如果以前1-5位字符做前缀索引就会出现大量索引值重复

  • 一文搞懂什么是MySQL前缀索引

    目录 一.什么是前缀索引 二.为什么要用前缀索引 三.怎么创建前缀索引 四.使用前缀索引需要注意的事项 五.小结 一.什么是前缀索引 所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快! 有点类似于 Oracle 中对字段使用 Left 函数来建立函数索引,只不过 MySQL 的这个前缀索引在查询时是内部自动完成匹配的,并不需要使用 Left 函数. 二.为什么要用前缀索引 可能有的同

  • 简单介绍MySQL中索引的使用方法

    数据库索引是一个数据结构,提高操作的速度,在一个表中可以使用一个或多个列,提供两个快速随机查找和高效的顺序访问记录的基础创建索引. 在创建索引时,它应该被认为是将SQL查询的那些列上创建一个或多个索引的列. 实际上,指数也保持主键或索引字段和指针的实际表中每条记录的表型. 用户无法看到索引,它们只是用来加快查询速度,将用于数据库搜索引擎找到的记录速度非常快. INSERT和UPDATE语句表上的索引需要更多的时间,成为快速对这些表的SELECT语句.究其原因是,当进行插入或更新,数据库以及需要惰

  • MySQL字符串前缀索引使用

    目录 1. 前缀索引与全部索引概念 2. 前缀索引与全部索引数据结构 3. 前缀索引与全部索引引执行流程 4. 前缀索引长度如何取舍 5. 前缀索引对覆盖索引的影响 6. 其他解决方案 7. 梳理总结 1. 前缀索引与全部索引概念 怎么给字符串字段加索引?现在,几乎所有的系统都支持邮箱登录,如何在邮箱这样的字段上建立合理的索引,是我们今天要讨论的问题. 假设,你现在维护一个支持邮箱登录的系统,用户表是这么定义的 create table SUser( ID bigint unsigned pri

  • 简单介绍下MYSQL的索引类型

    一.介绍一下索引的类型 Mysql常见索引有:主键索引.唯一索引.普通索引.全文索引.组合索引 PRIMARY KEY(主键索引) ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) UNIQUE(唯一索引) ALTER TABLE `table_name` ADD UNIQUE (`column`) INDEX(普通索引)      ALTER TABLE `table_name` ADD INDEX index_name ( `colu

  • MySQL联合索引遵循最左前缀匹配原则

    面试官: 我看你的简历上写着精通MySQL,问你个简单的问题,MySQL联合索引有什么特性? 心想,这还不简单,这不是问到我手心里了吗? 听我给你背一遍八股文! 我: MySQL联合索引遵循最左前缀匹配原则,即最左优先,查询的时候会优先匹配最左边的索引. 例如当我们在 (a,b,c) 三个字段上创建联合索引时,实际上是创建了三个索引,分别是(a).(a,b).(a,b,c). 查询条件中包含这些索引的时候,查询就会用到索引.例如下面的查询条件,就可以用到索引: select * from tab

  • 一文弄懂MySQL索引创建原则

    目录 一.适合创建索引 1.字段的数值有唯一性限制 2.频繁作为Where查询条件的字段 3.经常Groupby和Orderby的列 4.Update.Delete的where条件列 5.Distinct字段需要创建索引 6.多表Join连接操作时,创建索引注意事项 7.使用列的类型小的创建索引 8.使用字符串前缀创建索引 9.区分度高的列适合作为索引 10.使用最频繁的列放到联合索引的左侧 11.在多个字段都要创建索引的情况下,联合索引由于单值索引 二.不适合创建索引 1.在where中使用不

随机推荐