MySQL创建高性能索引的全步骤

一、索引基础

 

1. 索引的类型

1.1 b-tree 索引

大多数mysql存储引擎默认使用的是b+树的索引，不同的存储引擎用不同的方式使用b+树索引，myisam使用前缀压缩技术使得索引更小，但是innodb则按照元数据格式进行存储；myisam索引通过数据的物理位置引用被索引的行，而innodb则根据主键引用被索引的行。

b树 和 b+ 树

b树：

b+树：

区别：

• b树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；b+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中
• 在 b树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而 b+树中每个记录 的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看 b树的性能好像要比 b+树好，而在实际应用中却是 b+树的性能要好些。因为 b+树的非叶子节点不存放实际的数据， 这样每个节点可容纳的元素个数比 b树多，树高比 b树小，这样带来的好处是减少磁盘访问次数。尽管 b+树找到 一个记录所需的比较次数要比 b树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中 b+树的性能可能还会好些，而且 b+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有 文件，一个表中的所有记录等），这也是很多数据库和文件系统使用 b+树的缘故

为什么说 b+树比 b-树更适合实际应用中操作系统的文件索引和数据库索引？

• b+树的磁盘读写代价更低
  • b+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 b 树更小。如果把所有同一内部结点 的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 io 读写次数也就降低了
• b+树的查询效率更加稳定
  • 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当

为什么不用红黑树？

• b+树更少的查找次数
  • 平衡树查找操作的时间复杂度和树高 h 相关，o(h)=o(logdn)，其中 d 为每个节点的出度。
  • 红黑树的出度为 2，而 b+树 的出度一般都非常大，所以红黑树的树高 h 很明显比 b+树 大非常多，查找的次数也就更多。
• b+树利用磁盘预读特性
  • 为了减少磁盘 i/o 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。
  • 操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 i/o 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入

1.2 哈希索引

哈希索引基于哈希表实现，对于每一行数据，存储引擎会对所有的索引列计算一个哈希码，通过哈希码能以 o(1) 时间进行查找，但是无法用于排序与分组，并且只支持精确查找，无法用于部分查找和范围查找。

在mysql 中，只有memory引擎显式支持哈希索引

innodb 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 b+tree 索引之上再创建一个哈希索引，这样就让 b+tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

1.3 空间数据索引（r-tree）

myisam 存储引擎支持空间数据索引（r-tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

必须使用 gis 相关的函数来维护数据。

1.4 全文索引

myisam 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。

查找条件使用 match against，而不是普通的 where。全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。

innodb 存储引擎在 mysql 5.6.4 版本中也开始支持全文索引。

二、索引的优缺点

 

优点

• 索引大大减少了服务器需要扫描的数据量
• 通过索引可以帮助服务器避免排序和临时表，降低cpu消耗
• 可以将随机io变为顺序io，加快io速度

缺点

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

三、高性能索引策略

 

1. 独立的列

如果mysql查询的列不是独立的，就不会使用索引，“独立的列”指的是，索引列不能是表达式的一部分，也不能是函数的参数

例如

  ?

1

mysql> select id, name from t_user where id + 1 = 5;

mysql无法解析这个 id + 1 方程式，我们应该养成简化where条件的习惯

2. 前缀索引

有时候需要索引很长的字符列，这会让索引变得大且慢

比如对于 blob、text 和 varchar 类型的列，必须使用前缀索引，只索引开始的部分字符。

前缀长度的选取需要根据索引选择性来确定

3. 多列索引

很多人对于多列索引的理解都不够，一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高mysql的查询性能，所以引入“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。

例如下面的语句中，最好把 username 和 password 设置为多列索引。

  ?

1

select username, password from t_user where username = 'aiguodala' and password = 'aiguodala' ;

4. 合适的索引列顺序

让选择性最强的索引列放在前面。

索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。

5. 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。

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

聚集的数据的优缺点

优点：

• 可以把相关的数据保存在一起
  • 例如实现电子邮箱时，根据用户id来聚集数据，这样只需要从磁盘读取少量的数据就可以获取某个用户的全部邮件，如果没有聚簇索引，获取每封邮件都会导致一次磁盘io
• 数据访问更快，聚簇索引将索引和数据保存在同一个b+树中，能更快的查找数据
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点：

• 聚簇数据最大限度提高了io密集型应用的性能，但是如果数据全部放在内存中，则访问的顺序就不重要，聚簇索引也没有优势
• 插入速度严重依赖于插入顺序，如果不是按照主键的顺序加载数据，那么加载完成后最好使用optimize table命令重新组织一下表，所以建议选择自增的主键
• 更新聚簇索引列的代价很高，因为会强制innodb将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次分裂操作。页分裂会导致表占用更多的磁盘空间。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。

非聚簇索引

将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

6. 覆盖索引

索引覆盖所有需要查询的字段的值

好处：

• 索引条目远小于数据行大小，所以可以几大减少数据访问量以及更容易全部放到内存
• 索引是按照列值顺序存储，对于io密级型的范围查询会比随机从磁盘读取每一行数据的io要少得多
• 一些存储引擎（例如 myisam）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
• innodb 的二级索引（非聚簇索引）在叶子结点保存了行的主键值，如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询

四、查询性能优化

 

1. explain 性能分析

使用 explain 关键字可以模拟优化器执行 sql 查询语句，从而知道 mysql 是如何处理你的 sql 语句的。分析你的查询语句或是表结构的性能瓶颈

举例：

1.1 id：表的读取顺序

id是select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

id相同：执行顺序为 从上至下执行

  ?

1

explain select * from t1, t2, t3 where t1.id = t2.id and t2.id = t3.id;

id不同：执行顺序为 id大的先执行

  ?

1

2 3 4

explain select t2.id from t2 where t2.id = ( select t1.id from t1 where t1.id = ( select t3.id from t3) );

1.2 select_type：查询操作类型

select_type代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

select_type 属性	含义
simple	简单的 select 查询,查询中不包含子查询或者 union
primary	查询中若包含任何复杂的子部分，最外层查询则被标记为 primary
derived	在 from 列表中包含的子查询被标记为 derived(衍生) mysql 会递归执行这些子查询, 把结果放在临时表里
subquery	在select或where列表中包含了子查询，where 后面是单个值（=）
depedent subquery	在select或where列表中包含了子查询,子查询基于外层，where 后面是一组值（in）
uncacheable subquery	无法使用缓存的子查询
union	若第二个select出现在union之后，则被标记为union； 若union包含在from子句的子查询中,外层select将被标记为：derived
union result	从union表获取结果的select

1.3 table:表的来源

table表示这个数据是基于哪张表的

1.4 type：访问类型

type 是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：

  ?

1

2 3 4

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all   --常见的顺序为 system > const > eq_ref > ref > range > index > all

一般来说，得保证查询至少达到 range 级别，最好能达到 ref

类型名	含义
system	表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计
const	表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于 where 列表中，mysql 就能将该查询转换为一个常量
eq_ref	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行， 然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
range	只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现 了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而 结束语另一点，不用扫描全部索引
index	出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组
all	full table scan，将遍历全表以找到匹配的行

1.5 possible_key：可能用到的索引

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一 定被查询实际使用

1.6 key：实际使用的索引

实际使用的索引。如果为null，则没有使用索引

1.7 key_len：索引使用字节数

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引

ken_len 越长，说明索引使用的越充分

1.8 ref：显示被使用的索引的具体信息

ref显示索引的哪一列被使用了，如果可能的话，可以是一个常数。哪些列或常量被用于查找索引列上的值

1.9 rows：被查询的行数

rows 列显示 mysql 认为它执行查询时必须检查的行数。越少越好！

1.10 extra：额外重要信息

其他的额外重要的信息

• using filesort：使用外部索引排序（未使用用户创建的索引）
  • 说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql 中无法利用索引 完成的排序操作称为“文件排序”
  • 出现 using filesort 说明sql语句设计的不好，没有按照创建的索引进行排序，或者未按照索引指定的顺序进行排序
• using temporary
  • 使了用临时表保存中间结果,mysql 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by
  • 出现 using temporary 说明sql语句设计的非常不好，可能是因为没有按照顺序使用复合索引
• using index
  • using index 代表表示相应的 select 操作中使用了覆盖索引(covering index)，避免访问了表的数据行，效率不错！
  • 如果同时出现 using where，表明索引被用来执行索引键值的查找
  • 如果没有同时出现 using where，表明索引只是用来读取数据而非利用索引执行查找。
• using where
  • 表明使用了 where 过滤
• using join buffer
  • 使用了连接缓存
• impossible where
  • where 子句的值总是 false，不能用来获取任何元组
• select tables optimized away
  • 在没有 group by 子句的情况下，基于索引优化 min/max 操作或者对于 myisam 存储引擎优化 count(*)操 作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

 

总结

 

到此这篇关于mysql创建高性能索引的文章就介绍到这了,更多相关mysql高性能索引内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！