索引简介

在数据库表的相关字段上建立索引可以有效地提高SELECT的查询性能。索引的优点包括：减少服务器需扫描的数据量，帮助服务器尽量避免进行排序及使用临时表，可以将随机I/O转换为顺序I/O。索引确实可以提升服务器的性能，但是索引的建立需要占用额外的磁盘空间，在插入数据或更改表时，索引需要更新，有时甚至需要重构，在一张表上，建立的索引越多，插入数据的速度就越慢。所以不宜过多，根据需要建立即可。

索引是在MySQL的存储引擎中实现的，不同的存储引擎支持的索引类型也不同。常见的索引有以下几种：

1、B-Tree索引：大部分存储引擎都支持，能够适用于全值匹配、左前缀匹配、匹配范围值等场景。

2、HASH索引：只包含哈希值和行指针，不是按照索引值顺序存储，仅支持等值比较查询（=,<=>,IN()）

3、R-Tree（空间索引）：MyISAM的一个特殊索引类型，通常应用于地理空间数据类型

4、Full-text（全文索引）：主要用于全文的检索，仅MyISAM支持。


索引的相关语法

创建索引：

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

例：create index name_index using btree on teachers (name)    #通过using 指定索引类型


删除索引：

DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name

例：drop index name_index on teachers;


也可以通过alter语句实现索引的添加和删除。

添加：alter table tb5 add index indexName(column1,column2....);

删除：alter table tb5 drop index name;


查看某张表使用的索引：

show index from tableName;      #末尾用\G替代分号可竖排显示信息

查看某张表使用的存储引擎：

show table status like 'tableName';


相关服务器的状态变量

在服务器的状态变量中有以下几个可以用来查看服务器上各种SQL语句的执行频率（show status like 'variable_name'）。

Com_select        #select查询次数

Com_insert        #insert操作次数，批量插入的insert操作，只累加一次。

Com_update     #update操作次数

Com_delete       #delete操作次数


一下几个变量与上面的类似，不同的是这里只记录InnoDB存储引擎的相关操作的次数。

Innodb_rows_deleted

Innodb_rows_inserted

Innodb_rows_read

Innodb_rows_updated 

Com_commit       #事务提交的次数

Com_rollback       #事务回滚的次数


查看索引的使用情况：

MariaDB [hellodb]> show status like 'handler_read%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Handler_read_key         | 30    |
........
| Handler_read_rnd         | 0     |
| Handler_read_rnd_deleted | 0     |
| Handler_read_rnd_next    | 963   |
+--------------------------+-------+

Handler_read_key                 #表示一个行被索引读取的次数

Handler_read_rnd_next        #表示在数据文件中读取下一行的请求数

Handler_read_key的值反映了索引的使用情况，若这个值很小说明索引很少被用到，Handler_read_rnd_next的值很大则说明查询进行了大量的扫描，需要建立索引来改善性能。


通过explain分析SQL语句的执行计划

可以通过在select语句前加上explain查看这个语句的执行计划，例如这个查询语句是否有使用到索引，使用哪个字段上的索引，使用索引字段的长度，扫描的行数等等。

例如：

MariaDB [hellodb]> explain select * from students where gender='m'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25
        Extra: Using where

id：SELECT语句的标识符（主查询的id通常为1）

select_type：查询类型

     SIMPLE                #简单查询

     PRIMARY             #union的第一个查询（或最外的查询）

     UNION                #在联合查询中，相对于PRIMARY的其他查询语句

     UNION RESULT   #UNION的执行结果

     SUBQUERY          #子查询

     DERIVED              #用于FROM子句中的子查询；

table：查询语句所关系到的表的名字；

possible_keys：表示查询时可能使用的索引

key：表示实际使用的索引

key_len：使用到索引字段的长度

rows：扫描的行数

Extra：额外信息，执行情况的说明和描述

    1）using where    #用where后面的语句筛选出匹配的行

    2）using index     #所需要的数据从索引就能够全部获取到（覆盖索引特性）

    3）using index for group-by   #类似using index，用于group by中

    4）using filesort                       #将检索到的数据放到内存中进行排序，若内存装不下会放到磁盘上，性能很差

    5）using index condition         #过滤操作下推到存储引擎来完成

type：访问类型

    1）system     #表中仅有一行数据

    2）const        #表中至多有一行匹配且根据PRIMARY KEY或UNIQUE KEY（NOT NULL）进行查询。

如下，其中stuid为主键

MariaDB [hellodb]> explain select * from students where stuid=3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

     3）eq_ref       #类似于const，表中至多有一个匹配到的行，在连接操作中进行等值比较时，用到PRIMARY KEY或UNIQUE KEY（NOT NULL）索引时，此种结果才会出现

MariaDB [hellodb]> explain select * from students s,scores c where s.stuid=c.stuid\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: hellodb.c.StuID
         rows: 1
        Extra: 
2 rows in set (0.00 sec)

     4）ref      #表示查询时索引的类型不是PRIMARY KEY，也不是UNIQUE KEY（NOT NULL），或者只能用到索引的左前缀的访问类型

MariaDB [hellodb]> explain select * from students where age=23\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ref
possible_keys: age_index
          key: age_index
      key_len: 1
          ref: const
         rows: 4
        Extra: 
1 row in set (0.00 sec)

    5）fulltext                    #用于全文索引中的纯文本匹配方法来检索记录的时候用到

    6）index_merge           #使用索引合并优化的方式来访问记录类型（用到的索引不止一个）

    7）unique_subquery    #通常出现于IN子查询中（in的后面是一个查询主键字段的子查询）

    8）index_subquery      #类似于unique_subquery，区别在于in后面是查询非唯一索引字段的子查询

    9）range                      #带有范围限制的索引；

MariaDB [hellodb]> explain select * from students where stuid>20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 5
        Extra: Using where
1 row in set (0.00 sec)

   10）index                     #全索引扫描

MariaDB [hellodb]> explain select stuid from students\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: index
possible_keys: NULL
          key: age_index
      key_len: 1
          ref: NULL
         rows: 25
        Extra: Using index
1 row in set (0.00 sec)

   11）ALL                        #全表扫描

type由上至下性能逐渐变差。


能够使用索引场景

1）全值匹配，对索引中的列指定具体值

2）对索引列的值进行范围查询

例如：explain select * from customer where customer_id>10 and customer_id<20\G

3）最左前缀匹配

例如：select * from students where name like 'Xu%'

当使用的索引是复合索引时，where后面的条件必须包括复合索引的第一列，才能使用索引。若没有包括复合索引的第一列，而是仅适用于后面的几列，则不会利用索引。

4）只访问索引的查询，查询的列都在索引的字段中，效率较高


不能使用索引的场景

1）以%开头的like查询不能使用B-Tree索引，这种推荐使用全文索引

2）数据类型出现隐式转换的时候

例如：

MariaDB [hellodb]> explain select * from students where name=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ALL
possible_keys: name_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25
        Extra: Using where
1 row in set (0.00 sec)

若把name后面的字符串加上引号则可以使用索引

MariaDB [hellodb]> explain select * from students where name='1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ref
possible_keys: name_index
          key: name_index
      key_len: 152
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

3）使用复合索引的情况下，查询条件不包含复合索引的第一列字段

4）条件中有or时，or的任意一边没有使用索引

.................^_^