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

Love The Way You Lie 2023-01-11 13:27 207阅读 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 条评论，207人围观）

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

相关阅读

相关 MySQL索引优化: 如何避免冗余索引

在MySQL中，为了避免冗余索引，你可以遵循以下几点： 1. **理解业务需求**：首先你需要了解你的数据表以及查询的实际需求。如果某个查询经常使用某个字段，那么这个字段的索

╰半橙微兮°/ 2025年03月04日 20:09/ 0 赞/ 80 阅读

相关 MySQL索引优化：无索引或冗余索引的示例

在MySQL中，索引是提高查询性能的重要工具。以下是一些无索引或冗余索引的示例： 1. **未使用任何索引**：假设你有一个用户表(user)，并且你想查找某个特定用

阳光穿透心脏的1/2处/ 2025年02月19日 22:03/ 0 赞/ 66 阅读

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

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

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

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

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

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

相关索引压缩（一）

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

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

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

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

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

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

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

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

相关 MySQL高性能创建高性能的索引

索引能够轻易将查询性能提高几个数量级。 1.如果查询的列不是独立的，mysql就不会使用索引例如：select actor\_id from sakila.actor w

客官°小女子只卖身不卖艺/ 2022年08月27日 13:55/ 0 赞/ 319 阅读

相关 [高性能MySQL]-创建高性能的索引

> 本文是关于创建索引的的内容： > （1）索引的类型 > （2）索引的优点 > （3）优化索引的策略这里给出一个索引的思维导图： ![这里写图片描述][

我就是我/ 2022年07月12日 13:44/ 0 赞/ 320 阅读

相关高性能索引策略

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

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