高性能的MySQL(5)索引策略一压缩,冗余,重复,索引和锁

  1. 云栖社区>
  2. 博客>
  3. 正文

高性能的MySQL(5)索引策略一压缩,冗余,重复,索引和锁

技术小美 2017-11-23 16:27:00 浏览781
展开阅读全文

一、压缩索引

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已经锁定了所有返回的数据行。

举例说明:表结构

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `emp3` (
  `id` int(11) NOT NULL DEFAULT '0',
  `namevarchar(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会全表扫描并锁住所有的行,而不管是不是需要。





















本文转自shayang8851CTO博客,原文链接:http://blog.51cto.com/janephp/1312782,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
技术小美
+ 关注