sql优化

1.大批量插入数据

(1) 主键顺序插入
因为innodb的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排序,可以有效的提高导入数据的效率
如果innodb没有主键,系统会生成内部列作为主键,所以如果可以给表创建一个主键,就可以利用这一点,提高导入数据的效率

2.insert优化

当进行数据的insert操作时,可以考虑采用以下几种优化方式

• 如果需要同时对一张表插入很多数据时,应该尽量使用多个值表的insert语句,这种方式可以大大缩短客户端与数据库之间的连接,关闭等消耗,使得效率比分开执行单个insert语句块

insert into tb_test values (1, 'Tom');
insert into tb_test values (2, 'Cat');
insert into tb_test values (3, 'Jerry');

优化后:

insert into tb_test values (1, 'Tom'), (2, 'Cat'), (3, 'Jerry');

• 在事务中进行数据插入

start transaction
insert into tb_test values (1, 'Tom');
insert into tb_test values (2, 'Cat');
insert into tb_test values (3, 'Jerry');
commit;

• 数据有序插入

insert into tb_test values (1, 'Tom');
insert into tb_test values (2, 'Cat');
insert into tb_test values (3, 'Jerry');

3.优化order by语句

create table emp (
	id int(11) not null auto_increment,
	name varchar(100) not null,
	age int(3) not null,
	salary int(11) default null,
	primary key (id)
) engine = innodb default charset = utf8;

INSERT into emp values ('1', 'Tom', '25', '2300');
INSERT into emp values ('2', 'Jerry', '30', '3500');
INSERT into emp values ('3', 'Luci', '25', '2800');
INSERT into emp values ('4', 'Jay', '36', '3500');
INSERT into emp values ('5', 'Tom2', '21', '2200');
INSERT into emp values ('6', 'Jerry2', '31', '3300');
INSERT into emp values ('7', 'Luci2', '26', '2700');
INSERT into emp values ('8', 'Jay2', '33', '3500');
INSERT into emp values ('9', 'Tom3', '23', '2400');
INSERT into emp values ('10', 'Jerry3', '32', '3100');
INSERT into emp values ('11', 'Luci3', '26', '2900');
INSERT into emp values ('12', 'Jay3', '37', '4500');

create index idx_emp_age_salary on emp(age, salary);

我们现在的索引有两个
主键和idx_emp_age_salary

两种排序方式
(1) 第一种是通过对返回数据进行排序,也就是通常说的filesort排序,所有不是通过索引直接返回排序结果的排序都叫filesort排序

(2) 第二种通过有序索引排序扫描直接返回有序数据,这种情况为using index,不需要额外排序,操作效率高