mysql数据库索引语句_MySQL数据库之索引

一、什么是索引

索引是一种用于快速查询到匹配条件的数据的数据结构，是用来加快查询的技术。索引对良好的数据库性能来说，是一个非常重要的指标。当表中的数据量越来越大的时，其索引就越来越重要。

基本法则

索引应该构建在被用作《查询条件》的字段上

索引类型

1) B+ Tree索引

BTree树的特性：多路平衡树

树中每个节点最多包含m个子节点

每一个叶子节点到根节点的距离是相同的

B+ Tree树的特点：

有n棵子树的B+Tree最多含有n个key，而BTree最多含有n-1个key。

B+Tree的叶节点保存所有的key信息，依key大小顺序排列。

所有的非叶子节点都可以看作是key的索引部分，节点中只含有其子节点的最大(或最小)key。

适合B+Tree索引的查询类型：(全键值、最左前缀、匹配范围)

全值匹配：精确查找某个键的值，如name=“Jinjiao king”

匹配最左前缀： 只精确匹配起头部分 "Jin%"

匹配范围值： 从某个值到另一个值

精确匹配某列并范围匹配另一列

只访问索引的查询

不适合B+Tree索引的场景：

如果不从最左列开始，索引无效，如比如一个索引构建在(name、age)中，如果查询语句中，查询age>20的，这个索引就无效咯

不能跳过索引中的列 (StuID、Name、Age)

如果查询中某个列是范围查询，那边右侧的列都无法再使用索引优化查询

2)hash索引： 基于哈希表

不适用于顺序查询或模糊匹配的查询，只适合精确匹配的查询，只有memory存储引擎支持显示hash索引，其他的都不支持

3)空间索引(R-Tree)

只有MyISAM表支持空间索引

4)全文索引(FULLTEXT)

在文本中查找关键词 (开源搜索引擎：Lucene)

索引的优点

索引可以降低服务器需要扫描的数据量，减少IO次数

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

索引可以帮助将随机I/O转为顺序I/O

高性能的索引策略

1)索引的字段，不要做算术运算，如果该列使用算术运算，那么该列就无法使用索引咯

2)左前缀索引；索引构建在字段的左侧的多少个字符，要通过索引选择性来评估

3) 多列索引 (and)

4)选择合适的索引列次序，将选择性最高的放在左侧，范围匹配的放右侧

5) 为用来搜索、分类、分组的数据列构建索引；意思就是说，适合索引的数据列是哪些where子句出现的、order by、group by子句中出现的列

二)通过explain分析索引的有效性

使用方式：

EXPLAIN SELECT 获取查询执行计划信息

输出说明：

id： 当前查询语句中，每个select语句的编号

select_type：

简单查询： SIMPLE

简单子查询： SUBQUERY

用于from中的子查询： DERIVED

UNION语句的第一个之后的select语句： UNION

table： 查询对应的表

type： 即MySQL决定如何去查询表中行的方式

ALL:全表扫描

index：根据索引的次序进行全表扫描

range：范围扫描，有范围限制的根据索引来扫描。扫描位置始于索引中的某一点mysql索引表，结束于另一点

ref：基于等值的方式，根据索引返回表中匹配某单个值得所有行

eq_ref： 仅返回一个行

const、system： 直接返回单个行(最佳的)

possible_keys：查询可能会用到的索引

key： 查询中使用到的索引

key_len： 在索引中使用的字节数

ref： 在利用key字段所表示的索引完成查询时，所用的列或某常量值

rows： MySQL估计为找到所有的目标行而需要读取的行数(而不是最终的行数)

extra： 额外信息

Using index： 使用覆盖索引

Using where： MySQL服务器将在存储引擎层检索后，在进行一次过滤

Using temporary： MySQL对结果排序时会使用临时表

Using filesort：对结果使用一个外部索引排序，基于文件排序 (性能最差)

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!