陕西西安网站设计公司,小型广告公司简介范文大全,北京软件公司有哪些,怎么购买国外服务器创建高性能的索引 文章目录 创建高性能的索引一、索引基础二、索引的优点三、高性能的索引策略 一、索引基础
要理解MySQL中索引是如何工作的#xff0c;最简单的方法就是去看看一本书的“索引 ”部分#xff1a;如果在一本书中找到某个特定主题#xff0c;一般会先看书的“…创建高性能的索引 文章目录 创建高性能的索引一、索引基础二、索引的优点三、高性能的索引策略 一、索引基础
要理解MySQL中索引是如何工作的最简单的方法就是去看看一本书的“索引 ”部分如果在一本书中找到某个特定主题一般会先看书的“索引”找到对应的页码。
在MySQL中存储引擎用类似的方法使用索引 其先在索引中找到对应值然后根据匹配的索引记录找到 对应的数据行。假如要运行下面的查询 mysql SELECT first_name from sakila.actor where actor_id5; 如果在actor_id列上建有索引则MySQL将使用该索引找到actor_id为5的行也就是说MySQL先在索引上按值进行查找然后返回所有包含 该值的数据行。
索引可以包含一个列或多个列的值。如果索引 包含多个列那么列的顺序也很重要。因为MySQL只能高效地使用索引的最左前缀列。
索引结构中字段先后不受制于查询中相等判断条件表达式字段的顺序而受制于非等条件判断表达式。
索引的类型 在MySQL中索引是在存储引擎层而不是在服务器层实现的。 B-Tree索引 全值匹配指和索引中所有的列进行匹配。例如匹配key的所有字段(last_name,first_name,dob)。 匹配最左前缀只使用索引的第一列例如只使用last_name 匹配列前缀也可匹配某列的值开头部分。比如J开头的人这里只使用索引的第一列。 匹配范围值查找姓在Allen到Barry之间的人。治理只使用索引的第一列。 精确匹配某一列并范围匹配另一列第一列last_name精确匹配第二列first_name范围匹配。 只访问索引的查询即查询只需要访问索引。
B-Tree索引限制 如果不是按照索引的最左列开始查找则无法使用索引。如上面例子的索引无法查找名称为Bill的人也无法查找某个特定生日的人。 不能跳过索引中的列。无法查找姓为Smath并且在特定日期出生的人因为跳过了first_name列。 如果查询中有某个列的范围查询则其右边所有的列都无法使用索引优化查询。例如查询where last_name”Smath” and first_name like “%J” and dob’1970-02-10’。这个查询只能使用索引的前两列因为like是 一个范围条件。
哈希索引 哈希索引使用哈希表实现只有精确匹配索引所有列的查询才有效。
哈希索引限制 哈希索引只能包含哈希值和行指针。所以不能使用索引的值避免读行。 哈希索引并不是按照索引值顺序存储的所以无法进行排序。 哈希索引不支持部分索引匹配列查找。 哈希索引只支持等值比较查询。 访问哈希索引非常快除非有哈希冲突。当哈希出现冲突时会进行链表存储。 哈希冲突时索引重建代价会很高。
InnoDB引擎有一个特殊的功能叫做“自适应哈希索引adaptive hash index”。当InnoDB注意到某些索引值被使用的非常频繁时它会在内存中基于B-Tree索引之上再创建一个哈希索引这样就让B-Tree索引也具有哈希索引的一些优点比如快速的哈希查找。
二、索引的优点
1.索引大大减少了服务器需要扫描的数据量。 2.索引可以帮助服务器避免排序和临时表。 3.索引可以将随机I/O变为顺序I/O
三、高性能的索引策略
独立的列 select actor_id from actor where actor_id15 actor_id15无法被解析成actor_id4所以要将索引列单独存放在比较符合的一侧。
前缀索引和索引选择性 有时候需要索引很长的字符列这会让索引变得大且慢。 通常可以使用索引开始的部分字符这样可以大大节约索引空间从而提高索引效率。但这样也会降低索引的选择性。 select count(DISTINCT city)/count(*) from city_demo ALTER table city ADD KEY (city(7)) 索引的选择性是指不重复的索引值也称为基数cardinality和数据表的记录总数(#T)的比值范围从1/#T到1之间。索引的选择性越高则查询效率越高因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1这是最好的索引选择性性能也是最好的。 不重复索引值/记录总数接近0.031就可以使用了。
多列索引 多个单列索引会引起索引的合并并不是最优的策略。
选择合适的索引列顺序 当不需要考虑排序和分组时将选择性最高的列放在通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。 select * from payment where staff_id584 and customer_id30. 通过执行 select sum(customer_id30),sum(staff_id584) from payment 哪个列的基数小就把哪列放在最前列
前缀索引的条件值基数比正常值高的时候索引基本没什么用。比如索引的列满足全表所有的行。
聚簇索引 聚簇索引不是一种单独的索引类型而是一种数据存储方式。InnoDB聚簇索引保存了B-Tree索引和数据行。
覆盖索引 如果索引包含所需要查询字段的值就称为覆盖索引。 覆盖索引的好处 1、索引条目通常远小于数据行大小所以如果只需要读取索引那MySQL就会极大地减少数据访问量。 2、因为索引是按照列值顺序存储的至少在单个页内是这样所以对于I/O密集型的范围查询会比随机从磁盘读一行数据的I/O要少得多。 3、由于InnoDB的聚簇索引覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值如果二级主键能够覆盖查询则可以避免对主键索引的二次查询。
覆盖索引的缺点 1、插入速度严重依赖于插入顺序按照主键的顺序插入是加载数据页到InnoDB表中速度最快的方式。但是如果不按照主键顺序加载数据那么在加载完成后最好使用optimize table命令重新组织一下表。 2、更新聚簇索引列的代价很高因为会强制InnoDB将每个被更新的行移动到新的位置。插入的时候会面临页分裂的问题。页分裂会导致表占用更多的磁盘空间。 3、二级索引可能比想象的大因为二级索引的叶子结点保存了引用行的主键。 4、二级索引访问需要两次索引查找要回表对于InnoDB自适应hash索引能够减少这样的重复工作。
二级索引查找行需要找到叶子节点所对应的主键值再去找聚簇索引对应的值 使用了覆盖索引EXPLAIN的Extra列会显示Using index
在InnoDB中按主键顺序插入行 随机插入的缺点 写入的目标页可能不在内存缓存区那么插入记录的时候需要先从磁盘读取目标页到内存中。这会导致大量的随机I/O。如果是顺序插入由于是插入到上一个记录的后面则大多数情况下不需要开辟新页的情况磁盘页是已经加载到内存里的。 因为写入是乱序的InnoDB可能需要不断的做页分裂操作以便为新的行分配空间。而页分裂会导致移动大量的数据而且一次分裂至少要修改三个页而不是一个页。 由于频繁的分页页面会变得稀疏并被不规则的填充最后会导致数据碎片。
顺序的主键什么时候会造成更坏的结果 对于高并发工作负载在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会变成“热点”。因为所有的插入都发生在这里所以并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制如果遇到这个问题则可能需要考虑重新设计表或者应用或者更改innodb_autoinc_lock_mode配置。如果你的服务器版本还不支持innodb_autoinc_lock_mode参数可以升级到新版本的InnoDB可能对这种场景会工作的更好。
假如索引覆盖了where条件中的字段但不是整个查询涉及的字段还是会回表 获取数据行。 select * from products where actor’SEAN’ and title like ‘%APOLLO%’ 可以使用延迟关联解决 select * from products JOIN( select product_id from products where actor’SEAN’ and title like ‘%APOLLO%’ ) t1 ON t1.product_idproducts.id
使用索引扫描来做排序 MySQL有两种方式可以生成有序的结果通过排序操作或者按索引顺序扫描。如果EXPLAIN出来的type列的值为“index”则说明MySQL使用了索引扫描来做排序不要和Extra列的”Using index”搞混淆了。 只有当索引的列顺序和ORDER BY子句的顺序完全一致并且所有列的排序方向正序或倒序都一样时MySQL才能够使用索引来对结果做排序。 如果查询需要关联多张表则只有当ORDER BY子句引用的字段全部为第一个表时才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的需要满足索引的最左前缀的要求。否则MySQL都需要执行排序操作而无法利用索引排序。
压缩索引 MyISAM使用前缀压缩来减少索引的大小。
冗余和重复索引 重复索引是没有必要的 冗余索引可以满足不同条件的查询。
冗余索引和重复索引有一些不同。如果创建了索引(A,B)在创建索引(A)就是冗余索引因为这只是前一个索引的前缀索引。因此索引(A,B)也可以当作索引(A)来使用这种冗余只是对B-Tree索引来说的。如果再创建索引(B,A)则不是冗余索引索引(B)也不是因为B不是索引(A,B)的最左前缀列。另外其它不同类型的索引例如哈希索引或者全文索引也不会是B-Tree索引的冗余索引而无论覆盖的索引列是什么。
索引和锁 InnoDB在二级索引上使用共享锁主键索引使用排他锁。
