进阶-3.SQL优化

1. 插入数据

insert优化

• 批量插入

insert into user values(1,'tom'),(2,'Cat'),(3,'Hello');

• 手动事务提交

start transaction;
insert into user values(1,'tom'),(2,'Cat'),(3,'Hello');
insert into user values(4,'tom'),(5,'Cat'),(6,'Hello');
insert into user values(7,'tom'),(8,'Cat'),(9,'Hello');
commit;

• 主键顺序插入
• 大批量数据插入

如果一次性需要插入大批量数据，使用insert语句插入性能比较低，可以使用load指令插入

#客户端连接服务器，加上参数–local-infile

 mysql --local-infile -u root -p  

#设置全局参数local_infile为1,开启从本地加载文件导入数据的开关

mysql> set global local_infile=1;
Query OK, 0 rows affected (0.00 sec)
#查询参数值
mysql> select @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

#执行load命令将准备好的数据加载到数据库

load data local infile '数据路径' into table tb_user fields terminated by ',' lines terminated by '\n' 

主键顺序插入的性能高于乱序

2.主键优化

• 数据组织方式

在innoDB存储引擎中，表数据是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。

• 页分裂
  
• 页合并
  
  
• 主键设计原则

满足业务需求地情况下，尽量降低主键的长度
插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键
尽量不要使用uuid作为主键
业务操作时，避免对主键修改

3.order by 优化

show index from emp;
create index idx_emp_age_job_sal on  emp(age,job,salary);

SELECT id,age,job from emp order by age;

创建索引之后order by排序

EXPLAIN SELECT id,age, job from emp order by age,  job ;

order by排序进行降序，倒序索引

EXPLAIN SELECT id,age, job from emp order by age desc ,  job desc;

EXPLAIN SELECT id,age, job from emp order by age asc ,  job desc;

创建order by索引

create index idx_emp_age_job_ad on emp(age asc,job desc);

EXPLAIN SELECT id,age, job from emp order by age asc ,  job desc;

根据排序建立合适的索引
尽量使用覆盖索引
如果无法避免使用filesort

show variables like 'sort_buffer_size';#默认256k

4.group by 优化

Using temporary

explain select job,count(*) from emp group by job;

create index idx_emp_age_job_sal on  emp(age,job,salary);

mysql> explain select job,count(*) from emp group by job;
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | emp   | NULL       | index | idx_emp_age_job_sal | idx_emp_age_job_sal | 73      | NULL |   13 |   100.00 | Using index; Using temporary |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select age,job,count(*) from emp group by age,job;
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | index | idx_emp_age_job_sal | idx_emp_age_job_sal | 73      | NULL |   13 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select job,count(*) from emp  where age=50 group by job;
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ref  | idx_emp_age_job_sal | idx_emp_age_job_sal | 5       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+

5.limit优化

6.count优化

7.update优化

避免行锁升级为表锁

8.总结