MySQL 笔记四

索引的使用：

 

当使用数据库十分慢的时候：找到哪慢-->为什么慢-->

慢查询日志（找到是哪个数据库的那张表超出了阈值）：

MySQL的日志类型：

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

日志的分类：

 认识慢查询日志：

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

如何开启慢查询日志：

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

#查看是否开启慢查询日志 show variables like 'slow%';
#临时开启慢查询日志 set slow_query_log='ON'; set long_query_time=1;
#慢查询日志文件所在位置 show variables like '%datadir%'

例：

#显示慢日志的一些变量信息
#show variables like 'slow%';

#修改慢日志的阈值为1
#set global slow_launch_time=1;

#查看是否修改成功
#show variables like 'slow%';

#打开慢日志
#set global slow_query_log=ON;

#查看慢日志文件的存储方式
#show variables like 'log_output%';

#如果慢日志文件的存储方式是文件，查看文件的地址
#show variables like 'datadir%';

#使用语句验证慢日志是否记录
select sleep(5);
#只要大于阈值就好了

 如果不是文件存储，那就在mydb下的slow_log。

查询分析器EXPLAIN（分析一条语句的效率）:

explain简介:

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

explain的使用:

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

explain能做什么？

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

explain结果解析：

type是从右往左越来越好。

例：

 用explain分析select * from employee where id=13  和select * from employee where name='Jean'

两条语句的效率。

explain select * from employee where name='Jean';    //type是all
explain select * from employee where id=13;        //type是const

索引的基本使用(一般一条MySQL语句慢都和索引有关):

什么是索引：

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

常见的索引种类：

        索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定 记录。 ü 数据库的索引好比一本书的目录，能够加快数据库的查询速度； ü 索引是快速搜索的关键，如果不加索引，查找任何一条特定的数据都会进行一次全表扫描。

note:加了索引也不一定会使用索引，当使用like关键字的时候，即使加了索引type也是all

索引的使用（这里的语法和DDL有点相似）：

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;

 例（单个字段的索引）：给employee表的名字，字段建一个索引，再使用explain分析器，进行分析。

#第一次查询索引，只查询到了主键的索引
mysql> show index from employee\G;
*************************** 1. row ***************************
        Table: employee
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 13
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

#为name创建一个索引
mysql> create index idx_name on employee(name);
Query OK, 0 rows affected (0.04 sec)

#使用explain分析器，进行分析查找名字为Jean的全部信息
#可以发现type是ref
mysql> explain select * from employee where name='Jean';
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_name      | idx_name | 123     | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+----------+---------+-

#使用explain分析器，进行分析模糊查询
#可以发现type是all
mysql> explain select * from employee where name like '%e%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   13 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+-----

#删除索引
mysql> drop index idx_name on employee;
Query OK, 0 rows affected (0.03 sec)

复合索引前导列特性（多个字段的索引）：

复合索引前导列特性：

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

show index from employee;
create index index_name_salary_dept on employee(name,salary,dept);
explain select * from employee where name='Jean';    //type是ref
explain select * from employee where name='Jean' and dept='部门A';    //type是ref
explain select * from employee where name='Jean' and dept='部门A' and salary=8800;    //type是ref
explain select * from employee where dept='部门A' and salary=8800;    //type是all

覆盖索引（对索引的关键词extra进行探索，使查询的更快）：

什么是覆盖索引：

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

当一个查询使用了覆盖索引，在查询分析器EXPLAIN的Extra列可以看到“Using index”。

例：

mysql> explain select name from employee\G;    
mysql> explain select name,salary from employee\G;    
mysql> explain select sex from employee\G;    
mysql> explain select id from employee;        
mysql> explain select sex from employee where sex='男'\G;


#
#例一extra 是 using index，因为这里之前创建了一个复合索引，当索引可以覆盖要查询的数据时（就是索
#引，中可以拿的到你要的值，直接看最后一个例子，虽然sex并没有索引但是extra是using index,因为where
#语句中是sex='男'，但是这条语句的type还是ALL，因为他没有索引）就不会去查询表了，直接从索引中获取数
#据。
#
#我认为有无索引只是决定关键字type、而extra是由索引和where决定的，并且是不必要也不充分条件。索引覆
#盖，只是取值快而已