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

隔离数据列

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

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

对于人来说，很容易知道查询条件实际是actor_id = 4，但是MySQL不会这么处理，因此养成简化WHERE判决条件的习惯，这意味着索引列独立地在比较操作符的一侧。下面是另外一个普遍错误的案例：

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

可以看到这些城市名称出现的次数比较多。现在我们可以使用1个字的前缀查找最为频繁的城市名称前缀。

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

可以看到1个字找出来的数据集更多了，这会导致独立选中的机会越少，因此需要调整一下前缀的长度。例如调到3个字。

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`

数值越接近于1效果越好，但是也可以看到，随着前缀长度的加长改善的空间越小。只看平均值并不是一个好主意，还需要检查一下最坏情况。也许会觉得3-4个字足够了，但是如果数据分布很不均匀，那可能会存在陷阱。因此还需要检查一下前缀少的是不是存在一个前缀对应的数据与其他相比极其多的情况。最后可以给指定的列加前缀索引。

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

前缀索引在节省空间和提高效率方面表现不错，但是也有缺陷，那就是在ORDER BY和GROUP BY上无法使用索引（实际验证在MySQL 5.7以上版本也有用）。另外一种常见的场景是在较长的十六进制字符串中，例如存储的sessionId，取前8位前缀做索引将过滤很多无关数据，效果很好。

以上就是MySQL 隔离数据列和前缀索引的使用总结的详细内容，更多关于MySQL 隔离数据列和前缀索引的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • 聊聊MySQL事务的特性和隔离级别

    网上对于此类的文章已经十分饱和了,那还写的原因很简单--作为自己的理解笔记. 前言   此篇文章作为自己学习MySQL的一些个人理解,使用的引擎是InnoDb.首先先讲讲事务的概念,在<高性能MySQL>第三版中其对事务的描述是这样的: 事务就是一组原子性的SQL查询,或者说一个独立的工作单元.如果数据库引擎能够成功地对数据库应用该组查询的全部语句,那么就执行该组查询.如果其中有任何一条语句因为崩溃或其他原因无法执行,那么所有的语句都不会执行. ​​  换句话说,事务就是一个整体单位,里面的S

  • Mysql事务隔离级别之读提交详解

    Mysql事务隔离级别之读提交详解

    查看mysql 事务隔离级别 mysql> show variables like '%isolation%'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+ 1 row in set (0.00 sec) 可以看到

  • MySQL事务及Spring隔离级别实现原理详解

    1.事务具有ACID特性 原子性(atomicity):一个事务被事务不可分割的最小工作单元,要么全部提交,要么全部失败回滚. 一致性(consistency):数据库总是从一致性状态到另一个一致性状态,它只包含成功事务提交的结果 隔离型(isolation):事务所做的修改在最终提交一起,对其他事务是不可见的 持久性(durability):一旦事务提交,则其所做的修改就会永久保存到数据库中. 2.事务的隔离级别 1)隔离级别的定义与问题 READ UNCOMMITTED(读未提交):事务的修

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

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

  • Mysql事务隔离级别原理实例解析

    引言 大家在面试中一定碰到过 说说事务的隔离级别吧? 老实说,事务隔离级别这个问题,无论是校招还是社招,面试官都爱问!然而目前网上很多文章,说句实在话啊,我看了后我都怀疑作者弄懂没!因为他们对可重复读(Repeatable Read)和串行化(serializable)的解析实在是看的我一头雾水! 再加上很多书都说可重复读解决了幻读问题,比如<mysql技术内幕--innodb存储引擎>等,不一一列举了,因此网上关于事务隔离级别的文章大多是有问题的,所以再开一文说明! 本文所讲大部分内容,皆有

  • 通过实例认识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隔离性之Read View的用法说明

    Mysql隔离性之Read View的用法说明

    当前事务能读取到哪个历史版本? Read View是事务开启时,当前所有事务的一个集合,这个数据结构中存储了当前Read View中最大的ID及最小的ID. 这就是当前活跃事务列表,如下所示: ct-trx --> trx11 --> trx9 --> trx6 --> trx5 --> trx3; ct-trx 表示当前事务的id,对应上面的read_view数据结构如下, read_view->creator_trx_id = ct-trx; read_view-&

  • 简述MySql四种事务隔离级别

    隔离级别: 隔离性其实比想象的要复杂. 在SQL标准中定义了四种隔离级别, 每一个事务中所做的修改,哪些在事务内和事务间是可见的,哪些是不可见的.较低级别的隔离通常可以执行更高的并发,系统的开销也更低. 下面简单地介绍一下四种隔离级别. 1.READ UNCOMMITTED(未提交读) 在 READ UNCOMMITTED级别, 事务中的修改, 即使没有提交, 对其他事务也都是可见的. 事务可以读取未提交的数据, 这也被称为脏读 (Dirty Read). 这个级别会导致很多问题,从性能上来说,

  • 详解MySQL中事务隔离级别的实现原理

    前言 说到数据库事务,大家脑子里一定很容易蹦出一堆事务的相关知识,如事务的ACID特性,隔离级别,解决的问题(脏读,不可重复读,幻读)等等,但是可能很少有人真正的清楚事务的这些特性又是怎么实现的,为什么要有四个隔离级别. 今天我们就先来聊聊MySQL中事务的隔离性的实现原理,后续还会继续出文章分析其他特性的实现原理. 当然MySQL博大精深,文章疏漏之处在所难免,欢迎批评指正. 说明 MySQL的事务实现逻辑是位于引擎层的,并且不是所有的引擎都支持事务的,下面的说明都是以InnoDB引擎为基准.

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

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

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

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

    一文简单了解MySQL前缀索引

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

  • Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE

    Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE

    场景 产品中有一张图片表pics,数据量将近100万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化 表结构很简单,主要字段: 复制代码 代码如下: user_id 用户ID picname 图片名称 smallimg 小图名称 一个用户会有多条图片记录,现在有一个根据user_id建立的索引:uid,查询语句也很简单:取得某用户的图片集合: 复制代码 代码如下: select picname, smallimg from pics where user_id = xxx; 优化

  • MySQL为数据表建立索引的原则详解

    目录 1.索引是什么? 2.索引的优点? 3.索引的缺点? 4.在建立索引的时候,都有哪些需要考虑的因素呢? 1.只为用于搜索.排序.分组的列创建索引 2.索引列的类型尽量小 3.为列前缀建立索引 4.覆盖索引 5.让索引列以列名的形式在搜索条件中单独出现 6.新插入记录时主键大小对效率的影响 7.冗余和重复索引 总结 面试题: 索引是什么? 索引的优点? 索引的缺点? 在建立索引的时候都有哪些需要考虑的因素呢? 为数据表建立索引的原则有哪些? 什么是索引覆盖? 非聚簇索引一定会回表查询吗? 1

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

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

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

  • MySQL字符串前缀索引使用

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

  • 正确理解Mysql中的列索引和多列索引

    Mysql数据库提供两种类型的索引,如果没正确设置,索引的利用效率会大打折扣却完全不知问题出在这. 复制代码 代码如下: CREATE TABLE test (    id         INT NOT NULL,    last_name  CHAR(30) NOT NULL,    first_name CHAR(30) NOT NULL,    PRIMARY KEY (id),    INDEX name (last_name,first_name)); 以上创建的其实是一个多列索引,

  • MySQL修改表一次添加多个列(字段)和索引的方法

    MySQL修改表一次添加多个列(字段) ALTER TABLE table_name ADD func varchar(50), ADD gene varchar(50), ADD genedetail varchar(50); MySQL修改表一次添加多个索引 ALTER TABLE  table_name ADD INDEX idx1 ( `func`), ADD INDEX idx2 ( `func`,`gene`), ADD INDEX idx3( `genedetail`); 以上这篇

随机推荐