Mysql索引优化

三流程序员03
41 阅读3分钟

优化

创建表，插入10w条数据

DROP TABLE  if EXISTS `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('亚索',i),i,'dev');
		set i=i+1;
	end while;
end;;
delimiter ;
call insert_emp();

Mysql如何选择合适的索引

trace工具

常见的sql优化

order by优化

1、符合最左前缀原则，排序的时候使用到了覆盖索引的name列

image.png

2、排序的字段顺序和索引建立的顺序不一致，导致使用name列索引筛选出来的数据无法使用索引树，需要文件排序；反过来age和position就可以使用到索引树排序

image.png

3、多个等值查询也无法使用索引（使用索引查询出的数据无序）

image.png

Using filesort文件排序原理

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；占用空间大，排完序后就是要查询的最终结果

双路排序（回表排序模式）：首先取出排序字段和ID号，然后在 sort buffer 中进行排序，排序完后需要再次回表取其它需要的字段，占用内存空间小

MySQL 通过比较系统变量max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来 判断使用哪种排序模式。

image.png

如果字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式； 如果字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模式。

使用单路排序时有可能会用到临时文件（查询数据>sort buffer时），这时候排序效率慢，可以适当调大sort_buffer_size的值，

总结

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低。

2、order by满足两种情况会使用Using index。

  1. order by语句使用索引最左前列。
  2. 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

group by优化

group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。

注意，where高于having，能写在where中的限定条件就不要去having限定了。

分页查询优化

常规写法,下面语句会先读取出90005行数据，再丢掉前90000条数据，所以数据量越大时，后面的查询效率越慢，时间复杂度O(n)

image.png

优化一：利用主键索引（不实用）

image.png 优化后的sql走了索引，扫描行数减少，效率提高，但是使用这个需要具备两个前提条件

  • 主键必须自增且连续（中间不能删除数据，否则优化前后查出的数据会不一致）
  • 查询结果是按照主键排序

优化二：利用

join关联查询优化

count(*)优化

慢查询优化

慢查询，Mysql提供一种日志记录响应时间超过阈值的语句，需要手动开启；开启后会带来一定的性能影响

是否开启慢查询日志，默认阈值为10s

image.png

索引设计原则

1、代码先行，索引后上。先将主体业务开发完毕，再分析sql建立索引

2、联合索引尽量覆盖查询条件（尽量少用单值索引），一个联合索引比多个单值索引占的空间也要小。

3、不要在小基数字段上建立索引。字段里的值区分度不高（性别等），由于大量值都相等，建好索引也无法快速进行树的查找

4、大字段建立索引时，比如varchar(255)会占用更多的磁盘空间，在查找时需要扫描更多的磁盘数据页，可以使用该字段的前20个字符来建立索引（前缀索引，前提是区分度高），比如index(name(20),age,position)，缺点是order by无法使用该索引。

5、where与order by冲突时优先where

6、基于慢sql查询做优化

开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第7天，点击查看活动详情

avatar
文章
阅读
粉丝