数据库 之 MySQL的索引

忧郁者

　　1  概述
　　本文将介绍索引的相关概念，以及用EXPLAIN来分析索引
　　2  索引的相关概念
　　索引是指提取索引的创建在的表上字段中的数据，构建出一个独特的数据结构；
　　索引的作用：加速查询操作；副作用：降低写操作性能；
　　表中数据子集：把表中某个或某些字段的数据提取出来另存为一个特定数据结构组织的数据；
　　某个字段或某些字段：WHERE子句中用到的字段；
　　索引对写操作有影响，写操作只能放在对应的位置。
　　索引本身是平衡树。其排序是分层构建的。
　　索引能够加速读操作，但是对写操作是相反的效果。因为多一次索引就多一次写IO，因此要适量创建索引。
　　索引要构建在查询条件之上。即索引要建立在where语句中常用到的条件
　　索引是一种数据结构，是为了加速某种操作而设定的数据结构
　　mysql因为有索引，所以在海量数据的查询更高效
　　索引优点：
　　降低需要扫描的数据量，根据索引定位资源，减少IO次数；
　　可以帮助避免排序操作，因为索引本身就是根据顺序存放的，避免使用临时表；
　　帮助将随机IO转为顺序IO，提示系统性能；
　　3  索引类型
　　这里介绍两种索引类型：B+ TREE和HASH
　　B+ TREE
　　balance tree，顺序存储，每一个叶子结点到根结点的距离相同；将数据抽取出来后按顺序重新存放，占据一定空间，mysql数据库的索引一般为B tree索引，为左前缀索引，适合于范围类型的数据查询，最左侧的数据很关键，
　　适用于B+ TREE索引的查询类型：全键值、键值范围或键前缀；
　　全值匹配：精确匹配某个值；也称全键值匹配。
　　WHERE COLUMN = 'value';
　　匹配最左前缀：只精确匹配起头的部分；
　　WEHRE COLUMN LIKE 'PREFIX%';
　　匹配范围值：
　　精确匹配某一列，范围匹配另一列；如根据第一字段索引，如果第一字段一样，再根据第二字段索引
　　只用访问索引的查询，也叫覆盖索引；这是一种快速查询的方法
　　index(Name)，表示Name这个字段为索引，根据Name这个字段创建查询条件时，查询时，只需要查找索引，而不需要再到表中查找，效率会更高，如下
　　SELECT Name FROM students WHERE Name LIKE 'L%';
　　不适用B+ TREE索引：
　　如果查询条件不是从最左侧列开始，索引无效；如有个索引为index(age,Fname)，那么查询条件为 WHERE Fname='Jerry';    , WHERE age>30 AND Fname='Smith';，此时索引是无效的，因为不是从左侧索引开始查询
　　不能跳过索引中的某列；
　　如有索引为index(name,age,gender)
　　条件：WHERE name='black' and age > 30;该索引有效
　　条件：WHERE name='black' AND gender='F';跳过age这个索引项，则为无效索引
　　如果查询中的某个列是为范围查询，那么其右侧的列都无法再使用索引优化查询；
　　WHERE age>30 AND Fname='Smith';此时左侧的age>30这个条件匹配到的范围更大，那么 Fname='Smith'就无效。如果条件为WHERE  Fname='Smith'   AND age>30;就有效
　　Hash索引
　　基于哈希表实现，特别适用于值的精确匹配查询；键值索引。
　　hash索引也叫键值索引。基于键查找值，MyISAM和Innodb都不支持hash索引，只有memory存储引擎才支持hash.但是innodb支持自适应hash索引。
　　适用场景：
　　只支持等值的精确比较查询，例如=, IN(), ；MySQL仅对memory存储引擎支持显式的hash索引；
　　index(name)
　　where name="sunny"
　　不用场景：
　　所有非精确值查询；
　　4  高性能索引策略
　　以下是高性能索引策略，即用好索引的原则
　　(1) 在WHERE中独立使用列，尽量避免其参与运算；
　　WHERE age+2 > 32 ; 不允许这样的操作
　　(2) 左前缀索引：索引构建于字段的最左侧的多少个字符，要通过索引选择性来评估
　　索引选择性：不重复的索引值和数据表的记录总数的比值；
　　(3) 多列索引：
　　AND连接的多个查询条件更适合使用多列索引，而非多个单键索引；
　　where gender='F' and age>18;创建索引为index(gender,age)而不创建为index(gender)  index(age)两个单键索引，因为index(gender,age)更高效
　　(4) 选择合适的索引列次序：选择性最高的放左侧；
　　5  EXPLAIN来分析索引有效性
　　EXPLAIN解析某个select语句是否会用到索引以及如何使用索引，以此来判断自己定义的索引是否有效，把没有用的索引删除
　　EXPLAIN [explain_type] SELECT select_options
　　explain_type:
　　EXTENDED
　　| PARTITIONS
　　输出结果：
　　id: 1
　　select_type: SIMPLE
　　table: students
　　type: const
　　possible_keys: PRIMARY
　　key: PRIMARY
　　key_len: 4
　　ref: const
　　rows: 1
　　Extra:
　　输出结果字段介绍
　　id：当前查询语句中，第个SELECT语句的编号；可能是复杂查询，就会有多个查询
　　例子：复杂查询
　　MariaDB [sunny]> select name,age from students where age > (select avg(age) from students);
　　复杂的查询的类型主要三种：
　　简单子查询
　　用于FROM中的子查询
　　联合查询
　　注意：联合查询的分析结果会出现一个额外的匿名临时表；
　　select_type：查询类型：
　　简单查询：SIMPLE
　　复杂查询：
　　简单子查询：SUBQUERY
　　用于FROM中的子查询：DERIVED
　　联合查询中的第一个查询：PRIMARY
　　联合查询中的第一个查询之后的其它查询：UNION
　　联合查询生成的临时表：UNION RESULT
　　table：查询针对的表；
　　type：关联类型，或称为访问类型，也叫访问方法；即MySQL如何去查询表中的行，即获取数据的类型，有如下的方式：
　　ALL：全表扫描；
　　index：根据索引的顺序进行的全表扫描；但同时如果Extra列出现了"Using index”表示使用了覆盖索引；
　　range：有范围限制地根据索引实现范围扫描；扫描位置始于索引中的某一项，结束于另一项；
　　ref：根据索引返回的表中匹配到某单个值的所有行（匹配给定值的行不止一个）；
　　例子：
　　以下结果中有多个age=60的记录，则type为ref
　　MariaDB [sunny]> explain select name from students where age=60;
　　eq_ref：根据索引返回的表中匹配到某单个值的单一行，仅返回一个行，但需要与某个额外的参考值比较，而不是常数，如通过变量得到的；
　　const，system：与某个常数比较，且只返回一行；这两个是最高效.
　　例子
　　以下结果中，id为主键，得到的type为const

　　MariaDB [sunny]> explain select name from students where>　　possiable_keys：查询中可能会用到的索引,可能用到的索引会有多个；
　　key：查询中使用的索引，真正用到的索引如果有的话，是只有一个；
　　key_len：查询中用到的索引长度，表示索引的前几个字节；
　　ref：在利用key字段所显示的索引完成查询操作时所引用的列或常量值；
　　rows：MySQL估计出的为找到所有的目标项而需要读取的行数；这个值是大于或等于符合条件的行；
　　Extra：额外信息
　　Using index：使用了覆盖索引进行的查询；高效的方法。
　　Using where：拿到数据后还要再次进行过滤才能得到最终结果；
　　Using temporary：使用了临时表以完成查询；
　　Using filesort：对结果使用了一个外部索引排序；只查询出的结果如果超过16M的大小，那么要使用内存中的临时表来排序就不能实现，因此要把数据放到磁盘上再读出来，因此效率低，所以，如果看到额外信息中有Using filesort，建议进行优化，否则执行性能会很差。
　　站点访问很慢，有可能是站点受到DDos***，或者是站点的访问量太大，也有可能是是业务层有问题，如代码有bug，要么是数据层问题,因为并发量太大或者是发起了复制查询，索引创建不合理等。
　　例子
　　创建表，并用for循环插入大量数据，然后用EXPLAIN来分析索引的有效性
　　创建表
　　create table students(id int unsigned auto_increment primary key,name char(30) not null,age tinyint unsigned,gender enum('F','M'),major varchar(200));
　　插入数据
　　MariaDB [sunny]> insert into students values (1,"sunny",28,"M","math"),(2,"tracy",27,"F","Engilsh");
　　注意，这里为了大量生成数据，接下来直接在shell里生成用for循环生成大量的数据，进行插入
　　因为字段gender是枚举型，这里就定义一个数组gender('F' 'M')进行生成
　　定义数组
　　[root@CentOS7A sunny]#gender=('F' 'M')
　　用for循环来插入大量数据，可以用mysql -e在shell里执行sql语句
[root@CentOS7A sunny]#for i in {1..1000};do mysql -uroot -pPass123456 -e "insert into sunny.students(name,age,gender) values('stu$i','$[$RANDOM%80+18]','${gender[$RANDOM%2]}');";done　　explain分析查询语句
　　MariaDB [sunny]> explain select * from students where age>90;
　　+------+-------------+----------+------+---------------+------+---------+------+------+-------------+

　　|>　　+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
　　|    1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |  950 | Using where |
　　+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
　　1 row in set (0.01 sec)
　　以上结果表示，select语句的查询类型是simple,是在students表执行,获取数据的类型是all，即做全表扫描，将整个表载入内存，遍历所有数据得到的结果，装入了950行数据，使用where条件得到的。possible_keys是NULL，表示没有用到索引
　　创建索引
　　#创建单索引
　　MariaDB [sunny]> create index age on students(age);
　　#创建多字段索引
　　MariaDB [sunny]> create index age_and_name on students(age,name);
　　查看索引，同一Key_name出现多次，表示是多字段的索引
　　MariaDB [sunny]> show index from students;
　　删除索引
　　MariaDB [sunny]> drop index age_and_name on students;
　　索引创建完成后，再次用explain分析
　　MariaDB [sunny]> explain select * from students where age>90;
　　+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+

　　|>　　+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
　　|    1 | SIMPLE      | students | range | age           | age  | 2       | NULL |  104 | Using index condition |
　　+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
　　1 row in set (0.00 sec)
　　以上结果表示select语句的查询类型是simple,是在students表执行,获取数据的类型是range,即排序，possible_keys表示可能用到的索引是age，key实际用到的索引就是age,key_len 只用到的索引项的前2个字节，载入104行数据，使用的是索引的条件查询
　　MariaDB [sunny]> explain select name from students where age>90;
　　+------+-------------+----------+-------+------------------+--------------+---------+------+------+--------------------------+

　　|>　　+------+-------------+----------+-------+------------------+--------------+---------+------+------+--------------------------+
　　|    1 | SIMPLE      | students | range | age,age_and_name | age_and_name | 2       | NULL |  103 | Using where; Using index |
　　+------+-------------+----------+-------+------------------+--------------+---------+------+------+--------------------------+
　　1 row in set (0.01 sec)
　　以上结果possible_keys有两个age,age_and_name索引，实际用到的索引key是age_and_name，extra中有Using index表示覆盖索引这里如果索引是index(name,age),这里就用不上索引index(name,age)，而是单纯的age这个索引
　　关于explain更多介绍，建议查看文章：MySQL查询优化之explain的深入解析：http://www.iyunv.net/article/38357.htm