MySQL（索引）

0. 概念

• 在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。（索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。）

1. 认识索引

• 索引（index）:好比书的目录，用于加快查找的效率

• 索引的作用：加快查找效率.减慢插入和删除,修改效率.(需要同步调整索引结果)

2. 常见索引分为

• 主键索引(primary key)
• 唯一索引(unique)
• 普通索引(index)
• 全文索引(fulltext)- -解决中子文索引问题

3. 索引的数据结构

3.1 MySQL 与磁盘交互基本单位

磁盘这个硬件设备的基本单位是 512 字节，而 MySQL InnoDB引擎 使用 16KB 进行IO交互。即， MySQL 和磁盘进行数据交互的基本单位是 16KB 。这个基本数据单元，在 MySQL 这里叫做page（注意：与系统的page区分）

3.2 建立共识

• MySQL 中的数据文件，是以page为单位保存在磁盘当中的。
• MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。
• 申请出来的16KB的page需要先描述（结构体）再组织（b+树或b树）。
• Page分为目录页和数据页。目录页只放各个下级Page的最小键值（类似红黑树的键值对）。
• 查找的时候，自定向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数。

3.3 InnoDB 在建立索引结构来管理数据的时候，其他数据结构为何不行？

• 链表？线性遍历效率低下。
• 二叉搜索树？退化问题，可能退化成为线性结构。
• AVL 树&&红黑树？虽然是平衡或者近似平衡，但是毕竟是二叉结构，相比较多阶B+，意味着树整体过高，大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IO Page交互。虽然你很秀，但是有更秀的。
• Hash？官方的索引实现方式中，MySQL 是支持HASH的，不过 InnoDB 和 MyISAM并不支持，Hash跟进其算法特征，决定了虽然有时候也很快(O(1))，不过，在面对范围查找就明显不行（例如：我们查找班级成绩80到100分的人的具体信息）。

4. 索引的应用场景

• 应用的场景主要是应用在查找很频繁,但是插入,删除,修改都不频繁的场景
• 频繁的插入和删除会导致效率下降（调整管理page所用到的数据结构）

5. 索引操作

5.1 创建主键索引

• 第一种方式

create table user1(
id int primary key,
name varchar(30)
);

• 第二种方式：

create table user2(
id int,
name varchar(30),
primary key(id)
);

• 第三种方式：

create table user3(
id int,
name varchar(30)
);
alter table user3 add primary key(id);

主键索引的特点：

• 一个表中，最多有一个主键索引，当然可以使符合主键。
• 主键索引的效率高（主键不可重复）。
• 创建主键索引的列，它的值不能为null，且不能重复。
• 主键索引的列基本上是int。

5.2 唯一索引的创建

• 第一种方式

create table user4(
id int primary key,
name varchar(30) unique
);

• 第二种方式：

create table user5(
id int primary key,
name varchar(30),
unique(name)
);

• 第三种方式：

create table user6(
id int primary key,
name varchar(30)
);
alter table user6 add unique(name);

唯一索引的特点：

• 一个表中，可以有多个唯一索引。
• 查询效率高。
• 如果在某一列建立唯一索引，必须保证这列不能有重复数据。
• 如果一个唯一索引上指定not null，等价于主键索引。

5.3 普通索引的创建

• 第一种方式

create table user7(
id int primary key,
name varchar(30),
index(name)
);

• 第二种方式：

create table user8(
id int primary key,
name varchar(30),
);
create index idx_name on user8(name);

• 第三种方式：

create table user9(
id int primary key,
name varchar(30)
);
alter table user9 add index(name);

普通索引的特点：

• 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多。
• 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引。

5.4 全文索引的创建

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)。

mysql> CREATE TABLE articles (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> title VARCHAR(200),
    -> body TEXT,
    -> FULLTEXT (title,body)
    -> )engine=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO articles (title,body) VALUES
    -> ('MySQL Tutorial','DBMS stands for DataBase ...'),
    -> ('How To Use MySQL Well','After you went through a ...'),
    -> ('Optimizing MySQL','In this tutorial we will show ...'),
    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> ('MySQL vs. YourSQL','In the following database comparison ...'),
    -> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.04 sec)
Records: 6  Duplicates: 0  Warnings: 0

• 使用全文搜素

mysql> select * from articles where match(title, body) against('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.01 sec)

• 通过explain来分析这个sql语句用到了索引

mysql> explain select * from articles where match(title, body) against('database')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articles
   partitions: NULL
         type: fulltext
possible_keys: title
          key: title --用到了title
      key_len: 0
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

5.5 查询索引

• 第一种方法： show keys from 表名;

mysql> show keys from articles\G
*************************** 1. row ***************************
        Table: articles --表名
   Non_unique: 0  -- 0表示唯一索引
     Key_name: PRIMARY -- 主键索引
 Seq_in_index: 1
  Column_name: id   -- 索引所在的列
    Collation: A
  Cardinality: 6
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE -- 索引用到的数据结构
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: title
 Seq_in_index: 1
  Column_name: title
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: title
 Seq_in_index: 2
  Column_name: body
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
3 rows in set (0.01 sec)

• 第二种方法: show index from 表名;
• 第三种方法（信息比较简略）： desc 表名；

5.6 删除索引

• 第一种方法-删除主键索引

alter table 表名 drop primary key;

• 第二种方法-其他索引的删除

alter table 表名 drop index 索引名;

• 第三种方法方法：

drop index 索引名 on 表名;

6. 索引创建原则

• 比较频繁作为查询条件的字段应该创建索引。
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件。
• 更新非常频繁的字段不适合作创建索引。
• 不会出现在where子句中的字段不该创建索引。