深圳建站模板公司,微商管理系统,网站开发工程师证,配置无法运行wordpress索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时#xff0c;索引对性能的影响愈发重要。 一、索引的类型
在MySQL中#xff0c;索引是在存储引擎层而不是服务器层实现的。所以没用统一的索引标准#xff0c;不同存储引擎的索引工作方式并不相同。
1.1、B-Tre… 索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时索引对性能的影响愈发重要。 一、索引的类型
在MySQL中索引是在存储引擎层而不是服务器层实现的。所以没用统一的索引标准不同存储引擎的索引工作方式并不相同。
1.1、B-Tree
B-Tree索引即使用B-Tree数据结构来存储数据。B-Tree通常意味着所有值都是按顺序存储的并且每个叶子页到根的距离相同。存储引擎已不同的方式来使用B-Tree索引性能也各不相同。
可以使用B-Tree索引的查询类型——全键值、键值范围和键前缀查找。其中键前缀查找只适用于根据最左前缀查找。
1.2、哈希索引
哈希索引基于哈希表实现只有精确匹配索引的所有列的查询才有效。在MySQL中只有Memory引擎显示支持哈希索引这也是Memory引擎的默认索引类型。
对于每一行数据存储引擎都会对所有的索引列计算一个哈希码如果多个列的哈希码相同索引会以链表的方式存放多个记录指针到同一个哈希条目中。哈希索引将所有的哈希码存储在索引中同时在哈希表中保存指向每个数据行的指针。
1.3、全文索引
全文索引是一种特殊类型的索引它查找的是文本中的关键词而不是直接比较索引中 的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节如 停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情而不是简单 的WHERE条件匹配。
在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突全文索引适用于 MATCH AGAINST操作而不是普通的WHERE条件操作。
1.4、其他索引类别
还有很多第三方的存储引擎使用不同类型的数据结构来存储索引。例如TokuDB使用分 形树索引(fractal tree index),这是一类较新开发的数据结构既有B-Tree的很多优点 也避免了 B.Tree的一些缺点。如果通读完本章可以看到很多关于InnoDB的主题包 括聚簇索引、覆盖索引等。多数情况下针对InnoDB的讨论也都适用于TokuDB。
二、索引的优点
索引可以让服务器快速地定位到表的指定位置。但是这并不是索引的唯一作用到目前 为止可以看到根据创建索引的数据结构不同索引也有一些其他的附加作用。总结下来索引的三大优点
索引大大减少了服务器需要扫描的数据量索引可以帮助服务器避免排序和临时表索引可以将随机I/O变为顺序I/O。 索引是最好的解决方案吗 索引并不总是最好的工具。总的来说只有当索引帮助存储引擎快速查找到记录带 来的好处大于其带来的额外工作时索引才是有效的。对于非常小的表大部分情 况下简单的全表扫描更高效。对于中到大型的表索引就非常有效。但对于特大型的表建立和使用索引的代价将随之增长。这种情况下则需要一种技术可以直接区分出查询需要的一组数据而不是一条记录一条记录地匹配。 三、高性能的索引策略
3.1、独立的列
索引列不能是表达式的一部分也不能是函数的参数。 例如SELECT actor_id FROM actor WHERE actor_id 1 5; 或者SELECT actor_id FROM actor WHERE f(actor_id) 5; 3.2、前缀索引和索引选择性
有时候需要索引很长的字符列这会让索引变得很大且很慢。此时可以有两个策略一个是自定义哈希索引另一个就是前缀索引。
前缀索引能大大节约索引空间从而提高索引效率但这样也会降低索引的选择性索引选择性——不重复的索引值和数据表记录总数的比值索引前缀长度的选择——计算法。例如LELECT COUNT(DISTINCT city)/COUNT() AS sel1, COUNT(DISTINCT LEFT(city, 3))/COUNT() AS sel2, …; 如果前缀的选择性接近sel1就可以使用了。有时候只看平均选择型也不靠谱还需要做进一步判断。缺点MySQL无法使用前缀索引做ORDER BY和GROUP BY也无法使用前缀索引做覆盖扫描有时候也可以使用前缀索引——可将对应列的字符串反序存储并创建前缀索引。
3.3、多列索引
为多列创建合适的索引
多列索引。例如key(col1, col2, col3)MySQL5.0之后的版本引入了“索引合并”的策略一定程度上可以使用表上的多个单列索引来定位表中的行索引合并策略有时候是一种优化后的结果但实际上更说明表上的索引建得很糟糕。 当出现服务器对多个索引做相交操作时多个AND通常意味着需要一个包含相关列的多列索引而不是多个独立的单列索引;当服务器需要对多个索引做联合操作时多个OR通常需要耗费大量的CPU和内存在算法的缓存、排序和合并上。
3.4、选择合适的索引顺序
正确的索引顺序依赖于使用该索引的查询并且同时需要考虑如何更好的满足排序和分组的需要索引可以按照升序或者降序进行扫描以满足精确符合列顺序的ORDER BY 、GROUP BY和DISTINCT等子句的查询需求索引列顺序的选择——在不考虑分组和排序的情况下将选择性最高的列放到索引最前面经验法则避免随机I/O和排序对于某些特殊用户和分组避免其使用普通的索引查询。
3.5、聚簇索引
聚簇索引就是按照每张表的主键构造一颗B树同时叶子节点中存放的就是整张表的行记录数据也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分每张表只能拥有一个聚簇索引。
Innodb通过主键聚集数据如果没有定义主键innodb会选择非空的唯一索引代替。如果没有这样的索引innodb会隐式的定义一个主键来作为聚簇索引。
聚簇索引的优点
数据访问更快因为聚簇索引将索引和数据保存在同一个B树中因此从聚簇索引中获取数据比非聚簇索引更快聚簇索引对于主键的排序查找和范围查找速度非常快
聚簇索引的缺点
插入速度严重依赖于插入顺序按照主键的顺序插入是最快的方式否则将会出现页分裂严重影响性能。因此对于InnoDB表我们一般都会定义一个自增的ID列为主键更新主键的代价很高因为将会导致被更新的行移动。因此对于InnoDB表我们一般定义主键为不可更新。二级索引访问需要两次索引查找第一次找到主键值第二次根据主键值找到行数据。
3.6、覆盖索引
通常开发人员会根据查询的where条件来创建合适的索引但是优秀的索引设计应该考虑到整个查询。其实mysql可以使用索引来直接获取列的数据。如果索引的叶子节点包含了要查询的数据那么就不用回表查询了也就是说这种索引包含亦称覆盖所有需要查询的字段的值我们称这种索引为覆盖索引
3.7、使用索引扫描排序
MySQL有两种方式可以生成有序结果通过排序操作按照索引顺序扫描。
只有当索引的列顺序和ORDER BY子句的顺序完全一致并且所有列的排序方向升序/降序都一样时MySQL才能使用索引来对结果做排序当查询需要关联多张表时只有当ORDER BY子句引用的字段全部来自第一张表时才能使用索引排序ORDER BY子句中的字段需要满足索引的最左前缀的要求才能使用索引排序当索引的前导列为常量时ORDER BY子句可以不满足索引的最左前缀要求也能使用索引排序。例如key(rental_date, inventory_id, customer_id)… where rental_data‘2018-01-08’ ORDER BY inventory_id DESC;
四、维护索引和表 维护表有三个目的找到并修复损坏的表维护准确的索引统计信息减少碎片 4.1、更新索引统计信息
MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息已决定如何使用索引信息。
records_in_range();info()。如果存储引擎向优化器提供的索引统计信息不准确就会导致优化器做出错误的优化决定这会严重影响查询性能。可通过执行ANALYZE TABLE 来重新生成统计信息以解决这个问题。
4.2、减少索引和数据的碎片
B-Tree索引可能会碎片化碎片化的索引可能会以很差或无序的方式存储在磁盘上这会降低查询效率表数据存储也可能碎片化。主要有行碎片、行间碎片、剩余空间碎片三种。对于MyISAM表这三类碎片都可能发生但InnoDB不会出现短小的行碎片InnoDB会移动短小的行并重写到一个片段中。【维护方法】可通过执行POTIMIZE TABLE或者导出再导入来重新整理数据对于那些不支持POTIMIZE TABLE命令的引擎可以执行ALTER TABLE操作来重建表。只需要将表的存储引擎改为当前的引擎即可。例如ALTER TABLE table ENGINEengine;
五、总结
索引是一个非常复杂的话题 MySQL和存储引擎访问数据的方式 加上索引的特性使得索引成为一个影响数据访问的有力而灵活的工作无论数据是在 磁盘中还是在内存中。
在MySQL中大多数情况下都会使用B-Tree索引。其他类型的索引大多只适用于特殊 的目的。如果在合适的场景中使用索引将大大提高査询的响应时间。
如果一个査询无法从所有可能的索引中获益则应该看看是否可以创建一个更合适的索 引来提升性能。如果不行也可以看看是否可以重写该査询将其转化成一个能够高效 利用现有索引或者新创建索引的査询。这也是下一章要介绍的内容。
参考
《高性能 MySQL 第三版》
聚簇索引和非聚簇索引
mysql-覆盖索引
创建高性能的索引
