Mysql 索引的简单实践-原创手记-慕课网

最近在工作中遇见一些问题，在抽取Mysql数据到另一个库的时候会遇到类似这种错误:
 Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
 可能会误认为是数据库连接池没有配置检活，但是这里是因为数据量太大，而且没有使用到索引，走的全表扫描长时间返回不了查询结果而引起的。
 sql类似：select * from xxx where a is null or b = '' and c ='2018-12-18'
 下面简单学习一下mysql的索引。

新建测试表

随便拿了自己平时开发小demo的一个表
CREATE TABLE `faya_job_log` (
  `id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '日志id',
  `job_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '任务id',
  `job_desc` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '任务描述',
  `remote_ip` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '任务执行的机器地址',
  `load_balance` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '负载策略',
  `ha` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '失败策略',
  `status` int(11) DEFAULT NULL COMMENT '任务执行状态 成功 失败',
  `retry` int(11) DEFAULT NULL COMMENT '重试次数',
  `message` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '任务执行信息',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务日志';

导入几条数据
INSERT INTO `faya_job_manager`.`faya_job_log` (`id`, `job_id`, `job_desc`, `remote_ip`, `load_balance`, `ha`, `status`, `retry`, `message`, `create_time`, `update_time`) VALUES ('1', '1', '哈哈哈哈', '10.10.10.1', '1', '1', '0', '1', '哈小米你好', '2018-12-19 10:47:38', '2018-12-19 12:39:04');
INSERT INTO `faya_job_manager`.`faya_job_log` (`id`, `job_id`, `job_desc`, `remote_ip`, `load_balance`, `ha`, `status`, `retry`, `message`, `create_time`, `update_time`) VALUES ('2', '2', '哈哈哈哈', '10.10.10.1', '1', '1', '1', '1', '哈哈哈哈你好', '2018-12-19 10:48:03', '2018-12-19 10:48:03');
INSERT INTO `faya_job_manager`.`faya_job_log` (`id`, `job_id`, `job_desc`, `remote_ip`, `load_balance`, `ha`, `status`, `retry`, `message`, `create_time`, `update_time`) VALUES ('3', '3', '哈哈哈', '10.10.10.1', '1', '1', '1', '1', '哈哈哈哈你好', '2018-12-19 10:48:24', '2018-12-19 10:48:24');
INSERT INTO `faya_job_manager`.`faya_job_log` (`id`, `job_id`, `job_desc`, `remote_ip`, `load_balance`, `ha`, `status`, `retry`, `message`, `create_time`, `update_time`) VALUES ('4', '4', '哈哈哈哈你好', '10.10.10.1', '1', '1', '1', '11', '哈哈哈哈你好', '2018-12-19 10:49:07', '2018-12-19 10:49:21');
INSERT INTO `faya_job_manager`.`faya_job_log` (`id`, `job_id`, `job_desc`, `remote_ip`, `load_balance`, `ha`, `status`, `retry`, `message`, `create_time`, `update_time`) VALUES ('5', '5', '哈哈哈哈你好', '10.10.10.1', '1', '1', NULL, '1', '哈哈哈哈你好', '2018-12-19 10:49:20', '2018-12-19 12:32:28');

索引常用的操作

- 索引常用的操作
新增一个普通索引
alter table faya_job_log add index index_job_id (job_id) ;

删除索引
drop index index_job_id on faya_job_log ;

查询表存在的索引
SHOW INDEX FROM faya_job_log;

EXPLAIN命令

先了解 这个命令具体的返回
EXPLAIN select * from faya_job_log;
id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra 
......

一般我会关注几个字段
(1)rows：扫描行的数量，就是这个sql执行会扫描的数据行数，行数越大意味着查询会越慢。

(2)type:代表MySQL在表中查找数据的方式 ，常见的如下，性能由差到最好：

type=ALL:     全表扫描，MySQL遍历全表来找到匹配行
type=index:   索引全扫描，MySQL遍历整个索引来查询匹配行，并不会扫描表
type=range:   索引范围扫描
type=ref:	  非唯一索引扫描
type=eq_ref:  唯一索引扫描
type=const,system:	单表最多有一个匹配行,出现在根据主键primary key或者 唯一索引 unique index 进行的查询

(3)possible_keys: 表示查询可能使用的索引

(4)key: 实际使用的索引

SQL DEMO

explain select * from faya_job_log where id="1"
;
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | faya_job_log | NULL       | const | PRIMARY       | PRIMARY | 130     | const |    1 |      100 | NULL  |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

因为id是表的主键，所以可以看到rows是1，type是const。

接下来我们根据job_id查询
explain select * from faya_job_log where job_id="1";
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | faya_job_log | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |      100 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+

可以看到为没有使用索引，所以此时rows是5，type是ALL

新增一个普通索引
alter table faya_job_log add index index_job_id (job_id) ;

 explain select * from faya_job_log where job_id="1";
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | faya_job_log | NULL       | ref  | index_job_id  | index_job_id | 130     | const |    1 |      100 | NULL  |
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
可以看到当我们新增索引后type变为了ref,rows变为了1。

接下来我们根据job_id和status查询
explain select * from faya_job_log where job_id="1" or status ="0";
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | faya_job_log | NULL       | ALL  | index_job_id  | NULL | NULL    | NULL |    5 |      100 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+

发现type=type，rows=5.原因是在 where 子句中使用 or 如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，
这里我们的status没有索引.
可以修改sql语句:
explain select * from faya_job_log where job_id="1" union all  select * from faya_job_log where status ="0";
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | faya_job_log | NULL       | ref  | index_job_id  | index_job_id | 130     | const |    1 |      100 | NULL        |
|  2 | UNION       | faya_job_log | NULL       | ALL  | NULL          | NULL         | NULL    | NULL  |    5 |      100 | Using where |
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+

可以看到id还是会使用主键索引

我们给status加上索引
alter table faya_job_log add index index_status (status) ;

explain select * from faya_job_log where job_id="1" or status ="0";
+----+-------------+--------------+------------+------+---------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys             | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | faya_job_log | NULL       | ALL  | index_job_id,index_status | NULL | NULL    | NULL |    5 |      100 | Using where |
+----+-------------+--------------+------------+------+---------------------------+------+---------+------+------+----------+-------------+
会发现同样使用不了索引

修改sql执行
explain select * from faya_job_log where job_id="1" union all  select * from faya_job_log where status ="0";
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | faya_job_log | NULL       | ref  | index_job_id  | index_job_id | 130     | const |    1 |      100 | NULL  |
|  2 | UNION       | faya_job_log | NULL       | ref  | index_status  | index_status | 5       | const |    1 |      100 | NULL  |
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
此时会发现使用了我们的索引


我们把之前的两个索引删除
drop index index_job_id on faya_job_log ;
drop index index_status on faya_job_log ;
新建复合索引
ALTER TABLE faya_job_log ADD INDEX index_job_id_status (job_id,status);

执行下面的查询
explain select job_id from faya_job_log where job_id="1" and status =0;
+----+-------------+--------------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys       | key                 | key_len | ref         | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | faya_job_log | NULL       | ref  | index_job_id_status | index_job_id_status | 135     | const,const |    1 |      100 | Using index |
+----+-------------+--------------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-------------+

会发现使用了我们建立的索引,rows=1

但是当我们执行
explain select * from faya_job_log where status =0;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | faya_job_log | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |      100 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
发现没有使用索引
此时注意是因为最左原则:复合索引的情况下，查询条件不包含索引列最左边部分，不会命中复合索引

比如
explain select job_id,status from faya_job_log where job_id="1"
;
+----+-------------+--------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | faya_job_log | NULL       | ref  | index_job_id_status | index_job_id_status | 130     | const |    1 |      100 | Using index |
+----+-------------+--------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+

可以看到此时就使用了索引。

总结

关于索引的使用太多了，这里只是简单举例子,学习怎么使用explain命令。
大家可以多使用explain去查看自己的sql执行是否使用了索引。而且select 最好不要和我一样使用*.