Mysql性能优化-5.索引

1.索引是什么？

索引，index。关键字与数据位置映射关系，称之为索引。
关键字：从数据中提取，用于标识，检索数据的特定内容。
使用索引的目的，加快检索。索引是最常用的优化手段。

索引检索快的原因

• 关键字相对于数据本身，数据量小。
• 关键字都是排序的。遍历可以确定位置。

2.MySQL中索引类型

• 普通index索引，对索引关键字没限制。
• 唯一unique index 索引，要求记录提供的关键字不能重复。
• 主键primary key索引，要求关键字不能重复，同时不能为null。
• 全文fulltext index索引。

不同的类型，仅仅对关键字的限制不同，其他方面都一致。

3.索引管理语法

创建索引

create table user_index1(
    id int auto_increment primary key,
    first_name varchar(16),
    last_name varchar(16),
    sn varchar(16),
    information text,
    key (first_name, last_name),
    unique key(sn),
    fulltext key(information)
) engine=myisam;

create table user_index2(
    id int auto_increment primary key,
    first_name varchar(16),
    last_name varchar(16),
    sn varchar(16),
    information text
) engine=myisam;

alter table user_index2
add key (first_name, last_name),
add unique key(sn),
add fulltext key(information);

删除索引

alter table innodb1 drop primary key;  删除主键索引
alter table user_index1 modify id int, drop primary key; 先去自增，再删主键索引
alter table user_index1 drop key key-name;  利用索引名字可以删除，普通，唯一，全文索引。

查看索引

mysql> show create table innodb1;
+---------+--------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                     |
+---------+--------------------------------------------------------------+
| innodb1 | CREATE TABLE `innodb1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> use information_schema;
Database changed
mysql> show tables;

mysql> desc innodb1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| title | varchar(16) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> show create table user_index1;

4.执行计划，explain，execution plain

在执行的select前，使用关键字explain，可以获取该查询语句的执行计划：（暂时仅仅支持select，后续MySQL会支持update，delete，insert等）

mysql> explain select * from t_student where id = 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

执行计划是：当执行SQL时候，先分析，优化，形成执行计划，按照执行计划执行。

5.索引使用场景（重点）

建立的索引，会在哪些情况被使用。

• where 查询

mysql> explain select * from t_student where user = "4c0-a"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 702509
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> alter table t_student add index(user);
Query OK, 702509 rows affected (5.12 sec)

mysql> explain select * from t_student where user = "4c0-a"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: ref
possible_keys: user
          key: user
      key_len: 194
          ref: const
         rows: 11
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

• order by 排序

扩展知识，没有索引时，使用了文件排序（外部排序），性能较低。需要将数据读取到内存，但是不能一次性全读取，需要分段读取。合并排序结束。
加入索引后，基于索引完成查询，没有外部排序。

mysql> select * from t_student order by first_name limit 5;
+--------+------------+-----------+--------+-------+----------+----------+
| id     | first_name | last_name | gender | user  | password | class_id |
+--------+------------+-----------+--------+-------+----------+----------+
| 578380 | 00000      | 1163d     |      2 | 140-a | 6bb-4    |        1 |
| 547587 | 00001      | 2d3ec     |      2 | 722-a | e86-4    |        1 |
| 458632 | 00002      | 5a235     |      1 | 441-d | c3a-4    |        1 |
| 405988 | 00003      | c4cd3     |      2 | b53-9 | 7a3-4    |        1 |
| 239846 | 0000b      | 5f456     |      1 | 597-7 | 9dc-4    |        1 |
+--------+------------+-----------+--------+-------+----------+----------+
5 rows in set (0.17 sec)

mysql> explain select * from t_student order by first_name limit 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 702509
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

mysql> alter table t_student add index(first_name);
Query OK, 702509 rows affected (8.82 sec)
Records: 702509  Duplicates: 0  Warnings: 0

mysql> explain select * from t_student order by first_name limit 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: index
possible_keys: NULL
          key: first_name
      key_len: 194
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> select * from t_student order by first_name limit 5;
+--------+------------+-----------+--------+-------+----------+----------+
| id     | first_name | last_name | gender | user  | password | class_id |
+--------+------------+-----------+--------+-------+----------+----------+
| 578380 | 00000      | 1163d     |      2 | 140-a | 6bb-4    |        1 |
| 547587 | 00001      | 2d3ec     |      2 | 722-a | e86-4    |        1 |
| 458632 | 00002      | 5a235     |      1 | 441-d | c3a-4    |        1 |
| 405988 | 00003      | c4cd3     |      2 | b53-9 | 7a3-4    |        1 |
| 239846 | 0000b      | 5f456     |      1 | 597-7 | 9dc-4    |        1 |
+--------+------------+-----------+--------+-------+----------+----------+
5 rows in set (0.00 sec)

• join 连接

mysql> select c.*,count(s.id) from t_class c join t_student s on c.id=s.class_id group by c.id limit 10;
+----+--------+------+-------+-------+-------------+
| id | number | type | level | begin | count(s.id) |
+----+--------+------+-------+-------+-------------+
|  1 |     37 | 化学 | 小学  |     1 |         642 |
|  2 |     34 | 生物 | 初中  |     2 |         668 |
|  3 |     43 | 物理 | 小学  |     2 |         703 |
|  4 |     32 | 物理 | 大学  |     2 |         703 |
|  5 |     38 | 生物 | 大学  |     1 |         689 |
|  6 |     34 | 语文 | 小学  |     1 |         693 |
|  7 |     43 | 数学 | 高中  |     2 |         674 |
|  8 |     47 | 生物 | 大学  |     2 |         702 |
|  9 |     35 | 语文 | 小学  |     2 |         727 |
| 10 |     32 | 数学 | 初中  |     1 |         675 |
+----+--------+------+-------+-------+-------------+
10 rows in set (4.74 sec)

mysql> explain select c.*,count(s.id) from t_class c join t_student s on c.id=s.class_id group by c.id limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 700000
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: mysql_test.s.class_id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

mysql> alter table t_student add index(class_id);
Query OK, 700000 rows affected (1.73 sec)
Records: 700000  Duplicates: 0  Warnings: 0

mysql> select c.*,count(s.id) from t_class c join t_student s on c.id=s.class_id group by c.id limit 10;
+----+--------+------+-------+-------+-------------+
| id | number | type | level | begin | count(s.id) |
+----+--------+------+-------+-------+-------------+
|  1 |     37 | 化学 | 小学  |     1 |         642 |
|  2 |     34 | 生物 | 初中  |     2 |         668 |
|  3 |     43 | 物理 | 小学  |     2 |         703 |
|  4 |     32 | 物理 | 大学  |     2 |         703 |
|  5 |     38 | 生物 | 大学  |     1 |         689 |
|  6 |     34 | 语文 | 小学  |     1 |         693 |
|  7 |     43 | 数学 | 高中  |     2 |         674 |
|  8 |     47 | 生物 | 大学  |     2 |         702 |
|  9 |     35 | 语文 | 小学  |     2 |         727 |
| 10 |     32 | 数学 | 初中  |     1 |         675 |
+----+--------+------+-------+-------+-------------+
10 rows in set (0.05 sec)

• 索引覆盖

mysql> alter table t_student add index(first_name, last_name);
Query OK, 700000 rows affected (10.03 sec)
Records: 700000  Duplicates: 0  Warnings: 0

mysql> select first_name, last_name from t_student where first_name like 'abc%' limit 5;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| abc01      | bf787     |
| abc08      | 1c6fd     |
| abc0b      | 6b3af     |
| abc0f      | b1042     |
| abc0f      | bd276     |
+------------+-----------+
5 rows in set (0.00 sec)

mysql> explain select first_name, last_name from t_student where first_name like 'abc%' limit 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: range
possible_keys: first_name
          key: first_name
      key_len: 194
          ref: NULL
         rows: 158
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

mysql> explain select first_name, last_name, user from t_student limit 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 700000
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

使用复合索引时，如果查询中包含索引字段且多出其他字段，（如：复合索引（first_name，last_name）查询字段（first_name，last_name， user）），则不能使用复合索引，称之为索引覆盖。

6.语法细节（要点）

在满足索引使用场景下，索引也不一定被使用。
主要的原因，就是语法不严谨导致的！

6.1 在索引列上做任何操作（计算、函数、（自动or手动）类型转换）。

mysql> explain select * from t_student where id = 21-1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_student where id-1 = 21\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 700000
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

6.2 like查询，不能以通配符开头。

MySQL中的通配符:

• %，任意字符的任意数量，reg：（.*）
• _，任意一个字符。reg：（.）

mysql> explain select * from t_student where user like 'aff%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: range
possible_keys: user
          key: user
      key_len: 194
          ref: NULL
         rows: 169
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_student where user like '%1a-f'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 700000
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

6.3 复合索引的非最左侧字段，不能独立使用索引

例：index(first_name,last_name) fisrt_name可以，last_name不可以

mysql> explain select * from t_student where first_name like 'aaa%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: range
possible_keys: first_name
          key: first_name
      key_len: 194
          ref: NULL
         rows: 162
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_student where last_name like 'aaa%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 700000
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

复合索引性能大于单独索引取交集

6.4 查询字段is null经过索引，is not null不经过索引。

mysql> alter table t_student modify user varchar(64) default '';
Query OK, 700000 rows affected (14.60 sec)
Records: 700000  Duplicates: 0  Warnings: 0

mysql> update t_student set user = null where id in (1, 2);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> explain select * from t_student where user is null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: ref
possible_keys: user
          key: user
      key_len: 195
          ref: const
         rows: 4
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_student where user is not null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: ALL
possible_keys: user
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 700000
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

6.5 OR，保证两边索引条件都可用

mysql> explain select * from t_student where user like 'a%' or class_id < 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: index_merge
possible_keys: class_id,user
          key: user,class_id
      key_len: 195,4
          ref: NULL
         rows: 49406
     filtered: 100.00
        Extra: Using sort_union(user,class_id); Using where
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_student where last_name like 'a%' or class_id < 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: ALL
possible_keys: class_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 700000
     filtered: 40.74
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

6.6 状态值，不容易使用到索引

gender 1,2 当同时匹配大量记录时，MySQL会认为时索引的开销比全表扫描还大，会主动去放弃索引。

mysql> explain select * from t_student where gender in (1,2)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: ALL
possible_keys: gender
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 700000
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_student where id in (1,2)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

7.如何创建索引

• 建立基础索引：在where,order,join字段上建立索引优化。组合索引：基于业务逻辑。
• 如果条件经常性出现在一起，多字段索引，升级为复合索引。
• 如果通过增加个别字段，就可以出现索引覆盖，增加个别字段。
• 查询时，不会用到的索引，应该删除掉。

8.前缀索引

index (field(10))
使用字段field的前10个字符建立索引。默认是使用字段的全部内容建立索引。
前缀的标识度，足够的情况下，需要使用前缀索引。
例如，密码字段，就适合使用前缀索引：
实操的难度，在与前缀的长度。需要分析，多长的前缀，标识度足够。
如下， 建立索引时，使用前十位的前缀即可

mysql> select count(*)/count(distinct password) from t_student;
+-----------------------------------+
| count(*)/count(distinct password) |
+-----------------------------------+
|                            1.0000 |
+-----------------------------------+
1 row in set (5.15 sec)

mysql> select count(*)/count(distinct left(password,9)) from t_student;
+-------------------------------------------+
| count(*)/count(distinct left(password,9)) |
+-------------------------------------------+
|                                    1.0001 |
+-------------------------------------------+
1 row in set (5.00 sec)

mysql> select count(*)/count(distinct left(password,10)) from t_student;
+--------------------------------------------+
| count(*)/count(distinct left(password,10)) |
+--------------------------------------------+
|                                     1.0000 |
+--------------------------------------------+
1 row in set (5.03 sec)

mysql> alter table t_student add index (password(10));
Query OK, 700000 rows affected (23.95 sec)
Records: 700000  Duplicates: 0  Warnings: 0

mysql> select count(*)/count(distinct left(password,10)) from t_student;
+--------------------------------------------+
| count(*)/count(distinct left(password,10)) |
+--------------------------------------------+
|                                     1.0000 |
+--------------------------------------------+
1 row in set (1.21 sec)

9.存储的存储结构

• BTree索引
• Hash索引
• 聚簇索引

以上概念指的是索引的数据结构，装X时使用

10.BTree（多路平衡查找树）索引

索引存储在磁盘上所用的基础的通用的存储结构。无论MySQL，MongoDB，或者其他的数据库，在磁盘上存储索引时，用的都是BTree结构。

• 一个BTree节点，存储多个索引关键字。多少是由节点大小和关键字大小来确定，通常节点的大小是固定的，由计算机文件系统来确定，一次性磁盘读取内容量（512kb），就是一个节点的大小。
• 大量的关键字分散到多个节点上进行存储。
• 通过上层节点的子节点指针，指向下层节点，来管理所有的节点的。子节点指针位于关键字之间。
• 指针指向的子节点中的关键字的顺序，一定位于指针两侧的关键字之间。
• BTree结构可以在尽量少的磁盘读取下，遍历大量的节点关键字。

例如，每个节点可以存储1000个关键字，深度为2的两层可以存储大约100W个关键字。查找一个关键字，仅需2次磁盘读取，就可以遍历100W个关键字。

11.聚簇索引，聚集索引

关键字的记录在一起进行存储。称之为聚簇结构，聚簇索引。常规的，索引是关键字和记录位置的映射关系。而聚簇，不是关键字和记录映射，而是关键字和记录就存储在一起。也是在BTree的基础上升级改造的。数据结构称为B+Tree。在MySQL中，仅仅是Innodb的主键索引为B+Tree结构。其他索引包括Innodb的非主键索引都是BTree结构。

12.Hash索引

当索引被载入到内存时，采用的存储结构。哈希结构就是key-value的列表结构。