客户让我给她写个爬虫-dql语言原理（2）

你烦不烦啊
64 阅读9分钟

客户让我给她写个爬虫

MySQL索引优化实战：从原理到实践的深度剖析

在MySQL数据库的世界里，索引就像是图书馆里的目录，能帮助我们快速定位和获取数据。但如果索引使用不当，反而会降低查询效率。今天，就带大家深入学习MySQL索引优化，从基础概念到实战案例，全方位提升你的数据库性能优化能力。

一、示例表与数据准备

我们先创建一个employees表，并插入一些示例数据，方便后续的测试和分析。

CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

-- 插入大量示例数据
drop procedure if exists insert_emp; 
delimiter ;;
create procedure insert_emp
begin
    declare i int;
    set i=1;
    while(i<=100000)do
        insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev'); 
        set i=i+1;
    end while;
end;;
delimiter ;
call insert_emp();

二、索引使用的常见问题与优化策略

（一）联合索引第一个字段用范围不会走索引

EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

在这个查询中，联合索引idx_name_age_position的第一个字段name使用了范围查找。从EXPLAIN的结果可以看到，查询类型为ALL，表示全表扫描。这是因为MySQL内部认为第一个字段就用范围查找，结果集可能很大，回表效率不高，所以选择全表扫描。

（二）强制走索引

EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';

使用force index可以强制查询走指定的索引。虽然这样扫描的行数看上去少了点，但由于回表效率不高，最终查找效率不一定比全表扫描高。我们通过一个小实验来验证：

-- 关闭查询缓存
set global query_cache_size=0;
set global query_cache_type=0;

-- 执行时间0.333s
SELECT * FROM employees WHERE name > 'LiLei';

-- 执行时间0.444s
SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei';

可以看到，强制走索引后的查询时间反而更长。

（三）覆盖索引优化

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

在这个查询中，只查询了联合索引idx_name_age_position中的字段，不需要回表查询主键索引，这种方式称为覆盖索引。从EXPLAIN结果中的Extra字段Using where; Using index可以看出，查询使用了覆盖索引，提高了查询效率。

（四）in和or在不同数据量下的索引使用

在表数据量比较大的情况下，in和or条件会走索引；在表记录不多的情况下，会选择全表扫描。我们通过EXPLAIN来查看两种情况：

-- 数据量较大时
EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';

-- 复制表并保留少量记录
CREATE TABLE employees_copy LIKE employees;
INSERT INTO employees_copy SELECT * FROM employees WHERE id IN (1, 2, 3);

-- 数据量较小时
EXPLAIN SELECT * FROM employees_copy WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees_copy WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';

（五）like KK% 与索引下推

EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

一般情况下，like KK%会走索引。这里涉及到一个重要概念——索引下推（Index Condition Pushdown，ICP）。在MySQL 5.6之前，对于联合索引，查询只能在联合索引里匹配到名字是LiLei开头的索引，然后逐个回表比对其他字段。而MySQL 5.6引入索引下推优化后，可以在索引遍历过程中，先对索引中包含的所有字段做判断，过滤掉不符合条件的记录之后再回表，有效减少回表次数。但索引下推只能用于二级索引，对于innodb的主键索引（聚簇索引）不起作用。

三、MySQL如何选择合适的索引

我们可以使用trace工具来查看MySQL选择索引的过程。例如：

-- 开启trace
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; 

-- 执行查询
mysql> select * from employees where name > 'a' order by position;

-- 查看trace结果
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;

通过分析trace结果中的各个阶段信息，我们可以了解MySQL如何评估不同索引的使用成本，从而选择最优的访问路径。比如在rows_estimation阶段，会分析全表扫描和索引扫描的成本，最终在considered_execution_plans中确定选择哪种方式。

四、常见sql深入优化

（一）Order by与Group by优化

通过多个EXPLAIN示例，我们来分析Order by的优化要点：

  1. Case1：
EXPLAIN select * from employees where name ='iei' and position ='dev' order by age;

利用最左前缀法则，查询用到了name索引，age索引列用于排序，没有出现Using filesort，说明排序效率较高。

  1. Case2：
EXPLAIN select * from employees where name ='iei' order by position;

从explain结果看，key_len=74，查询使用了name索引，但由于用position排序跳过了age，出现了Using filesort，排序效率降低。

  1. Case3：
EXPLAIN select * from employees where name ='iei' order by age,position;

查找只用到索引name，age和position用于排序，无Using filesort。

  1. Case4：
EXPLAIN select * from employees where name ='iei' order by position,age;

与Case 3执行结果类似，但出现了Using filesort，因为索引创建顺序和排序顺序不一致。

  1. Case5：
EXPLAIN select * from employees where name ='Liei' and age =18 order by position,age;

与Case 4对比，age为常量在排序中被优化，所以未出现Using filesort。

  1. Case6：
EXPLAIN select * from employees order by age asc, position desc;

虽然排序字段列与索引顺序一样，但position desc导致与索引排序方式不同，产生Using filesort。不过MySQL 8以上版本有降序索引可以支持这种查询方式。

  1. Case7：
EXPLAIN select * from employees where name in('iei','zhuge') order by age,position;

对于排序来说，多个相等条件也是范围查询，这里出现了Using filesort。

  1. Case8：
EXPLAIN select * from employees where name > 'a' order by name;

可以用覆盖索引优化，避免全表扫描和Using filesort。

优化总结：

  1. MySQL支持filesort和index两种排序方式，Using index效率高，filesort效率低。
  2. order by满足两种情况会使用Using index：一是使用索引最左前列；二是where子句与order by子句条件列组合满足索引最左前列。
  3. 尽量在索引列上完成排序，遵循索引创建时的最左前缀法则。
  4. 如果order by的条件不在索引列上，就会产生Using filesort。
  5. 能用覆盖索引尽量用覆盖索引。
  6. group by与order by类似，实质是先排序后分组，遵循最左前缀法则。对于group by的优化，如果不需要排序可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要用having。

（二）Using filesort文件排序原理详解

filesort有单路排序和双路排序两种方式：

  1. 单路排序：一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。用trace工具查看sort_mode信息显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >。
  2. 双路排序（回表排序模式）：首先根据条件取出排序字段和行ID，在sort buffer中排序，排序完后再次取回其他需要的字段。sort_mode信息显示< sort_key, rowid >。

MySQL通过比较系统变量max_length_for_sort_data（默认1024字节）和需要查询的字段总大小来判断使用哪种排序模式。如果字段总长度小于max_length_for_sort_data，使用单路排序；否则使用双路排序。我们可以通过示例验证：

-- 查看单路排序
EXPLAIN select * from employees where name = 'zhuge' order by position;
-- 开启trace
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; 
-- 执行查询
mysql> select * from employees where name = 'zhuge' order by position;
-- 查看trace结果
mysql> select * from information_schema.OPTIMIZER_TRACE;

-- 设置max_length_for_sort_data为10，查看双路排序
mysql> set max_length_for_sort_data = 10; 
-- 执行查询
mysql> select * from employees where name = 'zhuge' order by position;
-- 查看trace结果
mysql> select * from information_schema.OPTIMIZER_TRACE;
-- 关闭trace
mysql> set session optimizer_trace="enabled=off";

五、索引设计原则

（一）代码先行，索引后上

不要在建完表后马上建立索引，应在主体业务功能开发完毕，分析涉及该表的所有SQL后再建立索引。

（二）联合索引尽量覆盖条件

设计少量联合索引，让其尽量包含where、order by、group by的字段，并满足最左前缀原则。

（三）不要在小基数字段上建立索引

索引基数是字段在表中不同值的数量。例如性别字段，基数小，对其建立索引不如全表扫描，应选择基数大的字段建立索引。

（四）长字符串采用前缀索引

对于varchar类型的大字段，可以对前几个字符建立前缀索引，如KEY index(name(20),age,position)。但要注意，使用前缀索引后，order by和group by可能无法使用该索引。

（五）where与order by冲突时优先where

在where和order by索引设计冲突时，优先让where条件使用索引筛选数据，再进行排序，这样通常成本更低。

（六）基于慢sql查询做优化

根据监控后台的慢SQL，针对性地进行索引优化。

六、索引设计实战

以社交场景APP的用户搜索为例，用户表数据量通常较大。假设我们经常根据省市、性别、年龄等条件筛选用户，可能会生成如下SQL：

select xx from user where xx=xx and xx=xx order by xx limit xx,xx

我们可以设计联合索引，如(province,city,sex)。如果有年龄范围筛选，可以将age加入索引，但要注意范围查找条件应放在最后。如果还有爱好、最近登录时间等筛选条件，可以进一步优化索引设计，如(province,city,sex,hobby,is_login_in_latest_7_days,age)。对于一些特殊查询，如根据性别和评分查询，可以设计辅助联合索引(sex,score)。

通过合理设计索引，利用多字段联合索引和辅助索引，可以满足大部分查询需求，提高大数据量表的查询速度和性能。

希望通过这篇文章，大家对MySQL索引优化有更深入的理解和掌握，在实际项目中能够运用这些知识优化数据库性能。如果在学习过程中有任何疑问，欢迎在评论区留言交流。

avatar
文章
阅读
粉丝