mysql

MySQL学习笔记（八）：索引

2017-06-03  本文已影响38人  maxwellyue

索引的概念

索引用于快速找到与特定的列值相同的行。如果没有索引，MySQL必须从第一行开始，然后通过整个表读取找出相关行。表越大越费时。如果表对该列建立了索引，MySQL就能够迅速判断，寻求到该行，而无需看所有的数据的位置。这比顺序读取每一行快得多。
也就是说：
索引是用来解决查询相关问题的。当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引。你创建了合理的索引（实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录），MySQL就会在可以使用索引的时候（特定的操作），更快速地查询出你想要的数据。

索引的分类

从数据结构角度
从物理存储角度
从逻辑角度

创建索引

创建索引，就是对某个表的一个或多个列上建立索引。官方的定义语句和参数解释非常清晰：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]
    [algorithm_option | lock_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
1、创建索引的时机

通常来说，应该在创建的表的同时创建所有的索引。这一点对于使用InnoDB引擎的表来说尤其重要（因为主键决定了记录在数据文件中的物理结构）。

a.创建表的同时创建索引 （详见：CREATE TABLE
）

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

-- 只看与创建索引相关的部分：create_definition
create_definition:
    col_name column_definition
  -- 主键索引（指定该列为主键，同时创建主键索引）
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)[index_option] ...
  -- 普通索引（为该列创建普通索引）
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)[index_option] ...
  -- 唯一索引（指定该列为唯一键，同时创建唯一索引）
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY][index_name] [index_type] (index_col_name,...)[index_option] ...
  -- 全文索引（为该列创建全文索引）
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)[index_option] ...
  -- 外键索引（指定该列为外键，同时为该列创建外键索引）
  | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

b.已创建的表添加索引（详见： ALTER TABLE
）

-- 普通索引（INDEX或者KEY都会创建索引）
ALTER  TABLE tbl_name 
ADD {INDEX|KEY} [index_name][index_type] (index_col_name,...) [index_option] ...
-- 主键索引（虽然是添加主键，但是同时也会创建索引）
ALTER TABLE tbl_name 
ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)[index_option] ...
-- 唯一索引
ALTER  TABLE tbl_name 
ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name][index_type] (index_col_name,...) [index_option] ...
-- 全文索引
ALTER  TABLE tbl_name 
ADD FULLTEXT [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
-- 空间索引
ALTER TABLE  tbl_name 
ADD SPATIAL [INDEX|KEY] [index_name](index_col_name,...) [index_option] ...
-- 外键索引（虽然是添加外键，但是同时也会创建索引）
ALTER  TABLE tbl_name 
ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

以上的ALTER语句后面都还可以加入：
| ALGORITHM [=] {DEFAULT|INPLACE|COPY}
| LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

从这里可以看出，并不是只有出现INDEX这个词的时候，才会创建索引。索引的分类也可以从上面总结出来。值得注意的是主键索引也是一种UNIQUE类型的索引，不仅不能重复，也不能含有NULL值。
从上面也可以看出，创建索引的方式有很多种，第一种就是在创建表的时候直接创建索引，第二种就是针对已创建的表：通过ALERT TABLE命令或者直接用CREATE INDEX都是可以的。

2、索引分类
3、参数index_col_name 
index_col_name:
    col_name [(length)] [ASC | DESC]
4、参数index_option 
index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
存储引擎 允许的类型
InnoDB BTREE
MyISAM BTREE
MEMORY/HEAP HASH, BTREE
NDB HASH, BTREE

可以看出，Mysql索引方式主要有两种结构：BTREE和HASH。
HASH：将数据的索引以hash形式组织起来，因此当查找某一条记录的时候，速度非常快。不支持范围查找和排序等功能.
BTREE：数据结构以平衡树的形式来组织。因为是树型结构，所以更适合用来处理排序，范围查找等功能。相对HASH索引，BTREE在查找单条记录的速度虽然比不上HASH索引，但是更适合排序等操作，而且大部分都是范围查找的查询操作。

注意：不能为FULLTEXT INDEX or SPATIAL INDEX指定类型，FULLTEXT INDEX的实现取决于存储引擎，SPATIAL INDEX是使用 R-tree。
更详情见下表：

storage_engine_indeex.png
4、参数ALGORITHM 和 LOCK

这两个参数可以影响表的复制方法和该表的读写并发级别。

删除索引

DROP INDEX index_name ON tbl_name
ALTER TABLE tbl_name DROP INDEX index_name
-- 这种是删除主键，也会删除主键索引
ALTER TABLE tbl_name DROP PRIMARY KEY

使用索引

当执行以下操作的时候，MySQL会使用索引（前提是你为相应字段创建了索引）：

对于数据量较小的表，或者需要访问大部分数据的查询而言，索引并不重要。

联合索引的最左原则

假如你创建了这样一张表，同时创建了联合索引：

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name) -- 创建联合索引
);

那么，在下面的查询语句中会使用索引：

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

而下面的两条查询语句则不会使用索引：

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';

再来举一个例子：

SELECT * FROM tbl_name WHERE col1=val1; -- a
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;  --b

SELECT * FROM tbl_name WHERE col2=val2;  --c
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;  --d

-- 
假如联合索引为 (col1, col2, col3)，只有a和b会使用索引。
虽然c和d的查询中也包含了索引列，但是 (col2) 和(col2, col3) 不是 (col1, col2, col3)的最左前缀。

验证索引的使用

使用命令EXPLAIN。待补充。 Optimizing Queries with EXPLAIN

索引选择

索引虽然可以加快查询速度，但是也会带来问题：索引文件会使磁盘空间的开销变大，修改操作由于要同时去修改索引文件所以会导致修改时性能变差。

该部分引用自：MySQL索引入门简述和mysql索引需要了解的几个注意

索引选择原则

参考

20180101--添加MySQL索引的分类

上一篇下一篇

猜你喜欢

热点阅读