> 데이터 베이스 > MySQL 튜토리얼 > 高性能的MySQL(5)索引策略一压缩,冗余,重复,索引和锁

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

WBOY
풀어 주다: 2016-06-07 17:38:29
원래의
1267명이 탐색했습니다.

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

一、压缩索引

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

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

171221780.png

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

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



本文出自 “phper-每天一点点~” 博客,请务必保留此出处

관련 라벨:
원천:php.cn
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
인기 튜토리얼
더>
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿