MySQL/MariaDB的索引使用

rgewr2

                      索引简介

在数据库表的相关字段上建立索引可以有效地提高SELECT的查询性能。索引的优点包括：减少服务器需扫描的数据量，帮助服务器尽量避免进行排序及使用临时表，可以将随机I/O转换为顺序I/O。索引确实可以提升服务器的性能，但是索引的建立需要占用额外的磁盘空间，在插入数据或更改表时，索引需要更新，有时甚至需要重构，在一张表上，建立的索引越多，插入数据的速度就越慢。所以所以不宜过多，根据需要建立即可。
索引是在MySQL的存储引擎中实现的，不同的存储引擎支持的索引类型也不同。常见的索引有以下几种：
1、B-Tree索引：大部分存储引擎都支持，能够适用于全值匹配、左前缀匹配、匹配范围值等场景。
2、HASH索引：只包含哈希值和行指针，不是按照索引值顺序存储，仅支持等值比较查询（=,<=>,IN()）
3、R-Tree（空间索引）：MyISAM的一个特殊索引类型，通常应用于地理空间数据类型
4、Full-text（全文索引）：主要用于全文的检索，仅MyISAM支持。


索引的相关语法

创建索引：

1 2 3 4

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 指定索引类型


删除索引：

1	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       #事务回滚的次数


查看索引的使用情况：

1 2 3 4 5 6 7 8 9 10

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查看这个语句的执行计划，例如这个查询语句是否有使用到索引，使用哪个字段上的索引，使用索引字段的长度，扫描的行数等等。
例如：

1 2 3 4 5 6 7 8 9 10 11 12

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为主键

1 2 3 4 5 6 7 8 9 10 11 12 13

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）索引时，此种结果才会出现

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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），或者只能用到索引的左前缀的访问类型

1 2 3 4 5 6 7 8 9 10 11 12 13

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                      #带有范围限制的索引；

1 2 3 4 5 6 7 8 9 10 11 12 13

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                     #全索引扫描

1 2 3 4 5 6 7 8 9 10 11 12 13

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）数据类型出现隐式转换的时候
例如：

1 2 3 4 5 6 7 8 9 10 11 12 13

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后面的字符串加上引号则可以使用索引

1 2 3 4 5 6 7 8 9 10 11 12 13

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的任意一边没有使用索引
.................^_^