MySQL 索引

================================================================================

概述：

================================================================================

MySQL的索引：

★定义：

• 索引：提取索引的创建在的表上字段中的数据，构建出一个独特的数据结构；

★作用：加速查询操作；

• 表中数据子集：把表中某个或某些字段的数据提取出来另存为一个特定数据结构组织的数据；

• 某个字段或某些字段：WHERE子句中用到的字段；

MySQL的索引类型：B+ TREE，HASH

 1.B+ TREE 索引

★B+ TREE：

• 顺序存储，每一个叶子结点到根结点的距离相同；

• 左前缀索引，适合于范围类型的数据查询；

★适用于B+ TREE索引的查询类型：全键值、键值范围或键前缀；

• 全值匹配：精确匹配某个值；

     ·eg：WHERE CLOUMN = ‘value‘;

• 匹配最左前缀：只精确匹配起头的部分；

    ·eg：WEHRE COLUMN LIKE ‘PREFIX%‘;

• 匹配范围值：（顺序排列）

    ·eg：WEHRE Age > 30;

• 精确匹配某一列，范围匹配另一列；

• 只用访问索引的查询：覆盖索引；（仅通过索引就能得到最终数据）

     ·eg: index(Name)

           SELECT Name FROM students WHERE Name LIKE ‘L%‘;

★不适用B+ TREE索引的查询类型：

• 如查询条件不是从最左侧列开始，索引无效；

    ·eg:index(age,name), WHERE name=‘Jerry‘(无效);WHERE age>30(有效);

• 不能跳过索引中的某列；

    ·eg:index(name,age,gender)

         WHERE name LIKE ‘j%‘ and gender=‘M‘ (无效，不能跳过阿age字段)

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

    ·eg:index(name,age,gender)

         WHERE second_name=‘black‘ and age > 30;（有效，如果把范围放前面就无效了）

 2.Hash索引：

★基于哈希表实现，特别适用于值的精确匹配查询；

★适用场景：

• 只支持等值比较查询，例如=, IN(), <=>

★不用场景：

• 所有非精确值查询；

• MySQL仅对memory存储引擎支持显式的hash索引；也就是说在mysql中只能使用B+ TREE 索引
  

 3.索引优点及高性能索引策略：

★索引优点：

• 降低需要扫描的数据量，减少了I/O次数；

• 可以帮助避免排序操作，避免使用临时表；

• 帮助将随机I/O转为顺序I/O；

★高性能索引策略：

☉在WHERE中独立使用列，尽量避免其参与运算；

☉左前缀索引：索引构建于字段的最左侧的多少个字符，要通过索引选择性来评估;

• 索引选择性：不重复的索引值和数据表的记录总数的比值；

• 尽量使用主键来构建索引，因为出现的重复率较小，效率最高

☉多列索引：

• AND连接的多个查询条件更适合使用多列索引，而非多个单键索引；

☉选择合适的索引列次序：选择性最高的放左侧； 

 3.索引的创建、查看、修改和删除：

★创建

☉语法：

• CREATE  [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON tbl_name (index_col_name,...) 索引字段名称

• index_col_name: 

       col_name [(length)] [ASC | DESC]

• {INDEX|KEY}  普通索引创建

• {FULLTEXT|SPATIAL} 全文索引，空间索引

★删除：

• DROP INDEX index_name ON tbl_name

★查看：

• SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name  [{FROM | IN} db_name] [WHERE expr]

★使用ALTER 命令添加和删除索引

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 

• 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):

• 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

• ALTER TABLE tbl_name ADD INDEX index_name (column_list):

• 添加普通索引，索引值可出现多次。

• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):

• 该语句指定了索引为 FULLTEXT ，用于全文索引。

 4.EXPLAIN来分析索引有效性：

★语法：

• EXPLAIN [explain_type] SELECT select_options

☉explain_type:

• EXTENDED

• PARTITIONS

★输出结果：

☉id：当前查询语句中，第几个SELECT语句的编号；

※复杂的查询的类型主要三种：

• 简单子查询；

• 用于FROM中的子查询；

• 联合查询

注意：联合查询的分析结果会出现一个额外的匿名临时表；

☉select_type：查询类型：

※简单查询：SIMPLE

※复杂查询：

• 简单子查询：SUBQUERY

• 用于FROM中的子查询：DERIVED

• 联合查询中的第一个查询：PRIMARY

• 联合查询中的第一个查询之后的其它查询：UNION

• 联合查询生成的临时表：UNION RESULT

☉table：查询针对的表；

☉type：关联类型，或称为访问类型，即MySQL如何去查询表中的行

• ALL：全表扫描；

• index：根据索引的顺序进行的全表扫描；但同时如果Extra列出现了"Using index”表示使用了覆盖索引；

• range：有范围限制地根据索引实现范围扫描；扫描位置始于索引中的某一项，结束于另一项；

• ref：根据索引返回的表中匹配到某单个值的所有行（匹配给定值的行不止一个）；

• eq_ref：根据索引返回的表中匹配到某单个值的单一行，仅返回一个行，但需要与某个额外的参考值比较，而不是常数；

• const，system：与某个常数比较，且只返回一行；
  

☉possiable_keys：查询中可能会用到的索引；

☉key：查询中使用的索引；

☉key_len：查询中用到的索引长度；

☉ref：在利用key字段所显示的索引完成查询操作时所引用的列或常量值； 

• < 上一篇

  科技下的仓库，数据库

• 下一篇 >

  数据库的管理