MySQL之索引

索引：是特殊数据结构，定义在查找时作为查找条件的字段，在MySQL又称为键key，索引通过存储引擎实现。

优点：

　　索引可以降低服务需要扫描的数据量，减少了IO次数

　　索引可以帮助服务器避免排序和使用临时表

　　索引可以帮助将随机I/O转为顺序I/O

缺点：

　　占用额外空间，影响插入速度

 

管理索引

　　创建索引：

　　　　1、CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);

　　　　2、ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);

　　　　3、创建表的时候直接指定索引

　　　　　　CREATE TABLE mytable( 

　　　　　　ID INT NOT NULL,  

　　　　　　username VARCHAR(16) NOT NULL, 

　　　　　　INDEX [indexName] (username(length)) 

　　　　　　); 

　　删除索引：

　　　　DROP INDEX index_name ON tbl_name;

　　　　ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

　　查看索引：

　　　　SHOW INDEXES FROM [db_name.]tbl_name;

　　优化表空间：（清理删除的数据所占用的空间）

　　　　OPTIMIZE TABLE tb_name;

　　查看索引的使用情况，使用率

　　　　SET GLOBAL userstat=1;（修改/etc/my.cnf）

　　　　SHOW INDEX_STATISTICS;

 

分析索引的有效性

　　通过EXPLAIN来分析索引的有效性

　　EXPLAIN SELECT clause

　　　　获取查询执行计划信息，用来查看查询优化器如何执行查询

　　输出信息说明：

　　　　参考 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

mysql > EXPLAIN SELECT * FROM students WHERE  name='tom' ;   
+------+-------------+----------+------+---------------+--------------+---------+-------+------+-----------------------+
| id   | select_type | table    | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
+------+-------------+----------+------+---------------+--------------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | students | ref  | idx_name_age  | idx_name_age | 152     | const |    2 | Using index condition |
+------+-------------+----------+------+---------------+--------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

EXPLAIN语句各个字段介绍

　　id: 当前查询语句中，每个SELECT语句的编号

　　select_type：

　　　　简单查询为SIMPLE

　　　　复杂查询：

　　　　　　SUBQUERY 简单子查询

　　　　　　PRIMARY 最外面的SELECT

　　　　　　DERIVED 用于FROM中的子查询

　　　　　　UNION UNION语句的第一个之后的SELECT语句

　　　　　　UNION RESULT 匿名临时表

　　table：SELECT语句关联到的表

　　type：关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式，以下顺序，性能从低到高

　　　　ALL: 全表扫描

　　　　index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用覆盖索引，而非全表扫描

　　　　range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点

　　　　ref: 根据索引返回表中匹配某单个值的所有行

　　　　eq_ref：仅返回一个行，但与需要额外与某个参考值做比较

　　　　const, system: 直接返回单个行

　　possible_keys：查询可能会用到的索引

　　key：查询中使用到的索引

　　key_len： 在索引使用的字节数

　　ref： 在利用key字段所表示的索引完成查询时所用的列或某常量值

　　rows：MySQL估计为找所有的目标行而需要读取的行数

　　Extra：额外信息

　　　　Using index：MySQL将会使用覆盖索引，以避免访问表

　　　　Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤

　　　　Using temporary：MySQL对结果排序时会使用临时表

　　　　Using filesort：对结果使用一个外部索引排序