Mysql 索引的使用

Mysql的日志类型
日志是用于记录数据库的运行情况，以及用户对数据库执行的各类操作。当数据库发生故障时，可以根据日志分析和解决问题，从而对数据库进行恢复。

日志	描述
重做日志(redo log)	重做日志是一种物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的
回滚日志(undo log)	回滚日志是一种逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事物之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。
二进制日志(binlog)	二进制日志是一种逻辑格式的日志，以二进制文件的形式记录了数据库中的操作，但不记录查询语句。错误日志(errorlog)：错误日志记录着mysql启动和停止，以及服务器在运行过程中发生的错误的相关信息。
慢查询日志(slow query log)	慢查询日志记录执行时间过长和没有使用索引的查询语句。
一般查询日志(general log)	记录了服务器接收到的每一个查询或是命令，无论这些查询或是命令是否正确甚至是包含语法错误，general log都会将其记录下来。
中继日志(relay log)	中继日志类似于二进制；可用于复制架构中，使从服务器和主服务器的数据保持一致。

慢查询日志
慢查询日志用于记录Mysql数据库中响应时间超过指定阈值的语句。慢查询日志通常也被称之为慢日志，因为它不仅仅只针对select语句，像insert、update、delete等语句，只要响应时间超过所设定阈值都会记录在慢查询日志中。

参数	描述
slow_query_log	是否开启慢查询日志，1表示开启，0表示关闭。
slow_query_log_file	慢查询日志存储路径，可选。注意：Mysql 5.6之前的版本，参数名为log-slow-queries
long_query_time	阈值，当SQL语句的响应时间超过该阈值就会被记录到日志中。
log_queries_not_using_indexes	未使用索引的查询也被记录到慢查询日志中，可选。
log_output	日志存储方式，默认为FILE。 log_output= 'FILE’表示将日志存入文件 log_output= 'TABLE’表示将日志存入数据库 log_output= 'FILE,TABLE’表示同时将日志存入文件和数据库

如何开启慢查询日志
慢查询日志可以通过命令临时设置，也可以修改配置文件永久设置。

#查看是否开启慢查询日志
show variables like 'slow%';

#临时开启慢查询日志
set slow_query_log='ON'; #如果提示参数是全局的，需要在前面加global
set long_query_time=1;

#慢查询日志文件所在位置
show variables like '%datadir%';

查询分析器EXPLAIN

explain命令可以查看SQL语句的执行计划。当explain与SQL语句一起使用时，Mysql将显示来自优化器的有关语句执行计划的信息。也就是说，Mysql解释了它将如何处理语句，包括有关如何联接表以及以何种顺序联接表的信息。

explain能做什么？

1. 分析出表的读取顺序
2. 数据读取操作的操作类型
3. 哪些索引可以使用
4. 哪些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被优化器查询

explain的使用
explain的使用很简单，只需要在SQL语句之前加上explain命令即可，除了select语句外，explain也能分析insert、update和delete语句。

explain结果解析

参数	描述
id	执行select子句或操作表的顺序
select_type	查询的类型，如SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION等
table	当前行使用的表名
partitions	匹配的分区
type	连接类型，如system、const、eq_ref、ref、range、index、all等(从左到右效果逐渐减弱)
possible_keys	可能使用的索引
key	实际使用的索引，NULL表示未使用索引
key_len	查询中使用的索引长度
ref	列与索引的比较
rows	扫描的行数
filtered	选取的行数占扫描的行数的百分比，理想的结果是100
extra	其它额外信息

索引的使用

什么是索引
索引是一种特殊的数据结构，类似于图书的目录，它能够极大地提升数据库的查询效率。如果没有索引，在查询数据时必须扫描表中的所有记录才能找出符合条件的记录，这种全表扫描的查询效率非常低。

常见的索引种类
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定记录。

数据库的索引好比一本书的目录，能够加快数据库的查询速度；
索引是快速搜索的关键，如果不加索引，查找任何一条特定的数据都会进行一次全表扫描。

索引种类	描述
普通索引	最基本的索引，没有任何限制，仅加速查询。
唯一索引	索引列的值必须唯一，但允许有空值。
主键索引	一种特殊的唯一索引，不允许有空值。一般是在建表的同时自动创建主键索引。
复合索引	两个或多个列上的索引被称作复合索引。
全文索引	对文本内容进行分词索引。

索引的使用

1、创建索引

#创建普通索引
create index indexName on tableName(columnName(length));
#创建唯一索引
create unique index indexName on tableName(columnName(length));
#创建复合索引
create index indexName on tableName(columnName1,columnName2,...);

2、删除索引

drop index [indexName] on tableName;

3、查看索引

show index from tableName;

索引的实战经验：

1. 选择区分度高的列建立索引，区分度计算公式：count(distinct col)/count(*),表示字段不重复的比例
2. 避免对索引列进行计算
3. 每次查询每张表仅能使用一个索引

复合索引的前导列特性

在Mysql中，如果创建了复合索引(name,salary,dept)，就相当于创建了(name,salary,dept)、(name,salary)和(name)三个索引，这被称为复合索引前导列特性，因此在创建复合索引时应该将最常用作查询条件的列放在最左边，依次递减。

未使用索引的情况

select * from employee where salary=8800;
select * from employee where dept='部门A';
select * from employee where salary=8800 and dept='部门A';

使用索引的情况

select * from employee where name='Lau';
select * from employee where name='Lau' and salary=8800;
select * from employee where name='Lau' and salary=8800 and dept='部门A';

覆盖索引

覆盖索引又称之为索引覆盖，即select的数据列只从索引中就能得到，不必读取数据行，也就是只需扫描索引就可以得到查询结果。

关于覆盖索引的几点说明:

1. 使用覆盖索引，只需要从索引中就能检索到需要的数据，而不要再扫描数据表；
2. 索引的体量往往要比数据表小很多，因此只读取索引速度会非常快，也会极大减少数据访问量；
3. Mysql的查询优化器会在执行查询前判断，是否有一个索引可以覆盖所有的查询列；
4. 并非所有类型的索引都可以作为覆盖索引，覆盖索引必须要存储索引列的值。像哈希索引、空间索引、全文索引等并不会真正存储索引列的值。

如何判断使用了覆盖索引

当一个查询使用了覆盖索引，在查询分析器explain的extra列可以看到’Using index’。