最近在工作中遇见一些问题，在抽取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 最好不要和我一样使用*.