mysql 索引压缩_高性能的MySQL（5）索引策略一压缩，冗余，重复，索引和锁

Love The Way You Lie 2023-01-11 13:27 108阅读 0赞

一、压缩索引

MyISAM使用前缀压缩来减少索引的大小，默认只压缩字符串，但是通过设置也可以对整数做压缩。

压缩可以使用更少的空间，代价是某些操作可能更慢。特别是倒序的查询，测试表明，对于CPU密集型的应用，查询会很慢，特别是倒序。对于I/O密集型应用，查询可能会不错。

可以在CREATE TABLE的语句中指定PACK\_KEYS参数来指定索引压缩方式。

二、冗余和重复索引

重复索引：指在相同的列上按照相同的顺序创建的相同类型的索引，要尽量避免重复索引，除非在同一列上创建不同类型的索引来满足不同的查询需求。比方说 key(col) 和 fulltext key(col)。

冗余索引：如果创建了索引(A，B)，再创建索引(A)就是冗余索引。因为索引(A，B)也可以当作(A)来使用(只针对B-Tree)。但是如果再创建索引(B，A)或者(B)，则不是冗余索引。或者类型不同，比方说哈希，全文索引等。

冗余索引通常发生在添加索引的时候，大多数情况下都不需要冗余索引，而是尽量扩展已有的索引，除非扩展已有的索引会导致索引很大，从而影响其他使用索引的性能。

例如：在一个整数列的索引上扩展一个很长的varchar列的索引，性能可能就会急剧下降。但是增加一个新的索引，就会对增删改操作影响很大，所以要平衡使用。

如何找到这写索引，以便删除，可以访问INFORMATION\_SCHEMA，或者一些现有的工具来定位。

有一个值得注意的地方：

对于InnoDB因为二级索引包换了主键，所以列(A)上的索引就相当与(A，ID)，所以类似

where A=5 order by id 这样的查询，这个索引会很有用。但是扩展为索引(A，B)

之后，则实际变成了(A，B，ID)，则order by就无法用到索引排序了。

三、索引和锁

索引可以让查询锁定更少的行，因为索引可以让查询不访问那些不需要的行，那么就会锁定更少的行。这有2点好处：

1、减少锁定行带来的额外的开销。

2、锁定超过需要的行会增加锁争用和减少并发性。

InnoDB只有在访问行的时候才会对其加锁，而索引能减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据返回给服务器层以后，MySQL服务器才能应用WHERE子句进行过滤。这时候已经无法避免锁定行了，InnoDB已经锁定了所有返回的数据行。

举例说明：表结构

CREATE TABLE \`emp3\` (

\`id\` int(11) NOT NULL DEFAULT '0',

\`name\` varchar(100) NOT NULL,

\`job\` varchar(100) NOT NULL,

\`num1\` int(10) DEFAULT NULL,

\`num2\` int(10) DEFAULT NULL,

\`num3\` int(10) DEFAULT NULL,

\`job\_num\` int(10) DEFAULT NULL,

\`d\` date DEFAULT NULL,

PRIMARY KEY (\`id\`),

KEY \`job\_num\` (\`job\_num\`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

数据如下：

151658496.png

在一个终端执行如下的操作：

151920121.png

虽然只返回了10002-10004之间的行，但是实际上10001-10004都被锁定了

152151828.png

也就是说，底层存储引擎的操作是从索引开头开始获取满足id<10005的记录，服务器并没有告诉InnoDB可以过滤第一行的where条件。注意到EXPLAIN中的Extra出现了“Using where”，这表示MySQL服务器将存储引擎返回行以后再应用where过滤条件。

我们来证明第一行确实是被锁定了，保持这个终端链接不关闭，然后我们打开另一个终端。

171221780.png

这个查询会挂起，知道第一个事务释放了第一行的锁。

按照这个例子，即使使用了索引，InnoDB也可能锁住一些不需要的数据。如果不能使用索引查找和锁定行的话，结果会更糟。MySQL会全表扫描并锁住所有的行，而不管是不是需要。

©著作权归作者所有：来自51CTO博客作者shayang88的原创作品，如需转载，请注明出处，否则将追究法律责任

mysql索引Mysql

发表评论取消回复

表情：

评论列表（有 0 条评论，108人围观）

还没有评论，来说两句吧...

相关阅读

相关 mysql的索引与锁（高性能mysql必备）

今天再从两个方面谈一下mysql的索引和锁，这两个共同点就是没有共同点，hhh开玩笑。对于数据库，我们关心的问题就是如何让它多存，快找，不死机，那么从下面的方案中，是否

ゞ浴缸里的玫瑰/ 2024年04月08日 08:52/ 0 赞/ 21 阅读

相关 mysql 索引压缩_高性能的MySQL（5）索引策略一压缩，冗余，重复，索引和锁

一、压缩索引 MyISAM使用前缀压缩来减少索引的大小，默认只压缩字符串，但是通过设置也可以对整数做压缩。压缩可以使用更少的空间，代价是某些操作可能更慢。特别是倒序的查询

Love The Way You Lie/ 2023年01月11日 13:27/ 0 赞/ 109 阅读

相关索引压缩（一）

1.创建测试用表t\_compress\_index SQL> create table t\_compress\_index as select \ from all

朴灿烈づ我的快乐病毒、/ 2022年09月30日 10:59/ 0 赞/ 72 阅读

相关 MySQL高性能的索引策略(2)：索引排序、索引下推、常见索引失效情况

> 详细介绍了各种高性能的索引使用策略，比如索引排序、索引下推、压缩索引等等，以及常见索引失效的情况。前面我们已经介绍了各种类型的索引结构及其对应的优缺点： 1. [B

た入场券/ 2022年09月04日 02:49/ 0 赞/ 211 阅读

相关 MySQL高性能的索引策略(1)：前缀索引、索引选择性、联合索引、覆盖索引

> 详细介绍了各种高性能的索引使用策略，比如联合索引、索引顺序、聚簇索引、覆盖索引等等，以及常见索引失效的情况。前面我们已经介绍了各种类型的索引结构及其对应的优缺点： 1

Dear 丶/ 2022年09月03日 10:19/ 0 赞/ 106 阅读

相关 mysql高性能索引设计

索引是存储引擎用于快速找到记录的一种数据结构，这是索引的基本功能。可以把数据库的理解成一本书，而索引就是这本书的索引（每个章节在第几页），如果你知道你想查找的章节名称，就可以快

心已赠人/ 2022年05月24日 02:45/ 0 赞/ 227 阅读

相关高性能索引策略

1.隔离列如果在査询中没有隔离索引的列， MYSQL通常不会使用索引。“隔离”列意味着它不是表达式的一部分，也没有位于函数中。例如，下面的查询不能使用 actor

逃离我推掉我的手/ 2022年04月18日 04:48/ 0 赞/ 167 阅读

相关 Mysql的索引和锁

索引和锁在数据库中可以说是非常重要的知识点了，在面试中也会经常会被问到的。本文力求简单讲清每个知识点，希望大家看完能有所收获。声明：如果没有说明具体的数据库和存储引擎，默认指的

不念不忘少年蓝@/ 2022年03月21日 08:20/ 0 赞/ 202 阅读

相关高性能的索引策略

独立的列如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指：索引列不能是表达式的一部分；也不能是函数的参数。例如，对下面查询无

心已赠人/ 2021年12月05日 15:58/ 0 赞/ 235 阅读

相关高性能的索引策略

创建高新能的索引策略（1）独立的列：索引不能是表达式的一部分，也不能是函数的参数。 eg：mysql>SELECT actor\_id from actor whe

我不是女神ヾ/ 2021年09月15日 19:50/ 0 赞/ 271 阅读