Mysql高性能学习心得笔记

索引是在存储引擎层而不是服务器层实现的。所以，并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有存储引擎都支持所有类型的

索引。即使多个存储引擎支持相同的索引，其底层的实现也可能不同。

存储引擎以不同的方式使用B-trees索引，性能也各有不同，各有优劣。例如，myisam使用前缀压缩技术使索引更小，但innodb则按照数据格式进行存储。再如myisam索引通过数据的物理位置引用被索引的行，而innodb则根据主键引用被索引的行。

索引对如下类型的查询有效：全值匹配，匹配最左前缀，匹配列前缀，匹配范围值，精确匹配某一列并范围匹配另外一列，只访问索引的查询。

B-tree索引的限制：

如果不是按照索引的最左列开始查找，则无法使用索引。

不能跳过索引中的列

如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询。

在memory存储引擎中支持非唯一哈希索引，也支持B-tree索引。

哈希索引的限制：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的只来避免读取行

哈希索引数据并不是按照索引顺序存储的，所以也就无法用于排序

哈希索引不支持部分索引列匹配查询

哈希索引只支持等值比较查询

访问哈希索引的数据非常快，除非有很多哈希冲突

如果哈希冲突很多的话，一些索引维护操作的代价也很高

全文索引：在相同的列上同时创建全文索引和基于值得B-tree索引不会有冲突，全文索引适用于Match against操作，而不是普通的where条件操作

索引的好处：

索引大大减少了服务器需要扫描的数据量

索引可以帮助服务器避免排序和临时表

索引可以随机I/O变成顺序I/O

前缀索引是一种能使索引跟小，跟快的有效方法，但另一方面也有其缺点：mysql无法使用前缀索引做order by和group by，也无法使用前缀索引做覆盖扫描

索引中包含多个列时，列的顺序非常重要，通常将选择性高的索引，放到前面

聚簇索引：

并不是一种单独的索引类型，而是一种数据存储方式，innodb的聚簇索引实际上在同一结构中保存了B-tree索引和数据行，一个表只能有一个聚簇索引

innodb将通过主键聚集数据，如果没有主键，innodb会选择一个唯一的非空索引代替。如果没有这样的索引，innodb会隐式定义一个主键来作为聚簇索引

优点：

可以把相关数据保存在一起

数据访问更快

使用覆盖索引扫描的查询可以直接使用页节点中的主键

缺点：

聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没有那么重要了，聚簇索引也没有优势

插入速度严重依赖于插入顺序。按照主键的顺序插入式加载数据到innodb表中速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载完成后最好使用

optimize table命令从新组织一下表

更新聚簇索引列的代价很高

基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题

聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于也业裂导致数据存储不连续的时候

二级索引可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键

二级索引访问需要两次索引查找，而不是一次

innodb的二级索引和聚簇索引很不相同。innodb二级索引的叶子节点中存储的不是行指针，而是主键，并以此作为指向行的指针。这样的策略减少了当出现行移动或者数据页分裂是二级索引的维护工作。使用主键值当做指针会让二级索引占用更多的空间，换来的好处是，innodb在移动时无需跟新二级索引中的这个指针

使用uuid聚簇索引的缺点：

写入的目标页可能已经刷新到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，innodb在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O

因为写入是乱序的，innodb不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页

由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片

如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一张表时，才能使用索引做排序。order by子句和查找型查询的限制是一样的；需要满足索引的最左前缀的要求；否则Mysql都需要执行排序操作，而无法利用索引排序

有一种情况下order by子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。where子句或者join子句中对这些列指定了常量，就可以‘弥补’索引的不足

对于CPU密集型应用，因为扫描需要随机查找，压缩索引使得Myisam在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在CPU内存资源与磁盘之间做权衡。压缩索引可能只需要十分之一大小的磁盘空间，如果是I/O密集型应用，对某系查询带来的好处会比成本多很多

重复索引：唯一索引和主键索引是相同的

冗余索引：索引(A,B)和索引A

在MySQL5.1和更新的版本中，innodb可以在服务器端过滤掉行后就释放锁，但是在早期的Mysql版本中，innodb只有在事务提交后才能释放锁

对于MyIsam存储引擎，表损坏通常是系统崩溃导致的。其他的引擎也会由于硬件问题，MySQL本身的缺陷或者操作系统额问题导致索引损坏

MySQL的查询优化器会通过两个API来了解存储引擎的索引值得分布信息，以决定如何使用索引。records_in_range和info

innodb_stats_sample_pages

碎片的三种类型：

行碎片：这种碎片指的是数据被存储为多个地方的多个碎片。即使查询只从索引中访问一行记录，行碎片也会导致性能下降

行间碎片：行间碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的

剩余空间碎片：剩余空间碎片是指数据页中有大量的空余空间

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29012686/viewspace-1133785/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29012686/viewspace-1133785/