索引

介绍

索引是帮助mysql高效获取数据库的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

优缺点

索引结构

Mysql 的索引是在存储引擎层实现的，不通的存储引擎又不通的结构，主要包含以下几种：

存储引擎支与索引

• 索引分类

在InnoDB存储引擎中，根据索引的存储形式，可以分为以下两种

聚集索引选择规则

1 如果存在主键，主键索引就是聚集索引
2 如果不存在主键，将使用第一个唯一索引作为聚集索引
3 如果表没有主键 或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

索引语法

1 创建索引

    CREATE[UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name, ...)

2 查看索引

  SHOW INDEX FROM table_name 

3 删除索引

 DROP INDEX index_name ON table_name;

SQL 性能分析

SQL 执行频率

 MySQL 客户端链接成功后， 通过 SHOW [session | global ] status 命令可以提供服务器状态信息。通过如下之类，可以查看当前数据的 INSERT , UPDATE , DELETE ，SELECT 的访问频次；
SHOW GLOBAL STATUS LIKE 'Com_______';

mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 27    |
| Com_delete    | 0     |
| Com_insert    | 2     |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 314   |
| Com_signal    | 0     |
| Com_update    | 5     |
| Com_xa_end    | 0     |
+---------------+-------+
10 rows in set (0.00 sec)

慢查询日志

  SHOW variables like 'slow_query_log'； // 查看是否开启慢SQL查询日志

慢查询日志记录了所有执行的时间超过指定参数（long_query_time, 单位：秒，默认是10秒 ）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

 # 开启MySQL 慢日志查询开关
slow_query_log=1
 # 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log

profile 详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了，通过have_profiling参数 ，能够看到当前MySQL是否支持profile操作

 SELECT @@have_profiling;

explain 执行计划

EXPLAIN 或者 DESC 命令获取MySQL 如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

 # 直接在select 语句之前加上关键字 explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件；

EXPLAIN 执行激活各字段含义：

• id ： select查询的序列号，表示查询中执行的select子句或者是操作表的顺序（id相同，执行顺序从上到下;id不同，值越大，越先执行）
• select_type ：表示SELECT 的类型，常见的取值有SIMPLE(简单表，即不使用表连接或者子查询),PRIMARY(主查询，即外层的查询). UNION(UNION中的第二个或者后面的查询语句)，SUBQUERY(SELECT/WHERE之后包含了子查询)等
• type :表示连接类型，性能由好到差的连接类型为 NULL, system, const , eq_ref, ref, range, index, all
• possible_key: 显示可能应用在这张表上的索引，一个或多个
• key : 实际使用的索引，如果为NULL,则没有使用索引
• key_len : 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精度情况下，长度越短越好
• rows： MySQL 认为必须要执行查询的行数，在InnoDB引擎的表中， 是一个估计值，可能并不总是准确的
• filtered：表示返回结果的行数占需要读取行数的百分比，filtered的值越大越好。

索引的使用原则

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃某一列，索引将部分失效(后面的字段索引失败)

explain select *  from tb_user where profession='软件工程' and age=31 and status ='0';

explain select * from tb_user where profession= '软件工程' and age =31;

explain select * from tb_user where profession='软件工程'；

explain select * from tb_user where age = 31 and status = '0';

explain select * from tb_user where status = '0';

范围查询

联合索引中，出现方位查询 （> , < ） , 范围查询右侧的列索引失效 （范围查询尽量使用>= 或者 <= ）

explain select * from tb_user where profession='软件工程' and age > 30 and status = '0';
 // age 左边会走索引 右边没有走索引

explain select * from tb_user where profession='软件工程' and age >=30 and status = '0';
// 都会走联合索引 

索引列运算

不要在索引列上进行运算操作，索引将失效
例如：

explain select * from tb_user where phone = '17799990015';

 explain select * from tb_user where substing(phone, 10, 2) = '15';

隐式类型转换

字符串不加引号会触发隐式类型转换，索引会失效

模糊查询

如果是尾部进行模糊匹配，索引不会失效，如果是 头部进行模糊匹配索引会失效

or连接条件

如果or分割开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及的索引都不好被用到

数据分布影响

如果Mysql评估索引比全表扫描还要慢，则不使用索引

SQL 提示

 use index: // 提示使用
 explain select * from tb_user use index(id_user_pro) where profession="软件工程"；
 ignore index: // 不使用
 explain select * from tb_user ignore index(idx_user_pro) where profession='软件工程';
 force index: //必须使用
 explain select * from tb_user force index(idx_user_pro) where profession='软件工程';

覆盖索引

尽量使用覆盖索引（查询使用了所以，并且需要返回的列，在改索引中已经全部能够找到），减少select *;

前缀所以

当前字段类型为字符串（varchar, text等）时，有时候需要索引很长的字符串，这回让索引biang的很大，查询时，浪费了大量的磁盘IO,影响查询效率，此时可以只将字符串的一部分前缀，建立索引，这样key8大大节约索引空间，从而提高索引效率。

语法

 create index index_xxxx on table_name(column(n));

前缀的长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的几率总数的比值，索引选择性越高则查询效率越高，唯一索引的选择是1，这是最好的索引选择性，性能也是最好的

 SELECT count(distinct email) / count(*) from tb_user;
 SELECT count(distinct substring(email, 1, 5))/count(*) from tb_user;

单列索引与联合索引

单列索引： 即一个索引只包含单个列
联合索引： 即一个索引包含了多个列
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
单列索引情况：

 explain select id, phone, name from tb_user where phone='17799990010' and name ='韩信';

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询

索引的设计原则

• 针对数据量较大，且查询比较频繁的表建立索引
• 针对于常作为查询条件（WHERE）， 排序(order by), 分组（GROUP BY）操作的字段建立索引
• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度高，使用索引的效率高
• 如果是字符串类型的字段，字段的长度较长，可以针对字段的特点建立前缀索引
• 尽量使用联合索引， 酱烧单列索引， 查询时 联合索引很多时候可以覆盖索引， 节省存储空间，避免回表，提高查询效率
• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
• 如果索引列不能存储NULL值，庆祝创建表时使用NOT NULL约束它，当优化器知道每列是否包含NULL值时，它可以更好的确定哪个索引最有效的用于查询