MySQL数据库优化(2)

MySQL优化

大批量插入数据优化

1.将数据按照id有序排列

2.使用load关键字（100万条：有序20s，无序1分50秒）

3.插入之前，关闭唯一性校验（SET UNIQUE_CHECKS=0），插完再打开（SET UNIQUE_CHECKS=1）唯一性校验。

4.关闭自动提交事务（SET AUTOCOMMIT=0），使用手动提交（SET AUTOCOMMIT=1）。

insert优化

原始方法：

#频繁的连接和关闭连接
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jack');

优化后：

#合并insert,只需要连接一次数据库
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jack');

在事务中进行数据插入。手动提交、有序插入

#开始事务
start transaction;
#插入数据		数据量大的情况下，分段提交（每1万条提交一次）
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jack');
#提交事务
commit

order by语句优化

排序方式：filesort(效率低)、index(效率高)

表名：emp

属性：id name age salary

加索引：create index idx_emp_age_salary on emp(age,salary);

#效率高 Using index
select id,age,salary from emp order by age;	
#效率低 Using filesort
select id,age,salary,name from emp order by age;	

因为：主键id默认有索引；age,salary加了索引；name没加索引；

索引的顺序是age,salary；所以：

#效率高 Using index					排序一致	顺序：age,salary
select id,age,salary from emp order by age desc,salary desc;	
#效率低 Using index;Using filesort		排序一致    顺序：salary,age
select id,age,salary from emp order by salary desc,age desc;
#效率低 Using index;Using filesort		排序不一致
select id,age,salary from emp order by age desc,salary asc;	

filesort的优化：

对于filesort，MySQL有两种排序算法：两次扫描算法、一次扫描算法；

一次扫描算法排序时内存开销较大，但是排序效率比两次扫描算法要高。

提升排序效率：提高 sort_buffer_size 和 max_length_for_sort_data

#查看命令
SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'max_length_for_sort_data';

group by语句的优化

group by是在排序的基础上进行了分组操作。

方案：

#加索引
create index idx_emp_age_salary on emp(age,salary);
#去排序
order by null;

优化嵌套查询

方案：多表联查替换子查询

表：t_user、t_role、user_role

#原始语句
SELECT * FROM t_user WHERE id IN(SELECT user_id FROM user_role);
#优化后
SELECT * FROM t_user u,user_role ur WHERE u.id = ur.user_id;

优化or条件

建议使用union替换or

#原始语句	EXPLAIN查看执行计划
EXPLAIN SELECT * FROM emp WHERE id = 1 OR NAME = 'Tom';
#优化
EXPLAIN SELECT * FROM emp WHERE id = 1 OR id = 10;
#再优化
EXPLAIN SELECT * FROM emp WHERE id = 1 UNION SELECT * FROM emp WHERE id = 10;

#原始语句
EXPLAIN SELECT * FROM emp WHERE id = 1 OR age = 20;
#优化后
EXPLAIN SELECT * FROM emp WHERE id = 1 UNION SELECT * FROM emp WHERE age = 20;

优化分页查询

案例：

#查询总记录条数
SELECT COUNT(*) FROM tb_item;
#查询第1页，每页返回10条记录(limit 0,10)
SELECT * FROM tb_item limt 10;
#查询第2页，从第11条记录开始查询10条记录
SELECT * FROM tb_item limt 10,10;
#查询第3页，从第21条记录开始查询10条记录
SELECT * FROM tb_item limt 20,10;

#查询第20万页，从第2000001条记录开始查询10条记录
#limit起始值较大，效率变低；
#因为要对前2000010条记录进行排序，然后丢弃前2000000条记录，只保留后10条
SELECT * FROM tb_item limt 2000000,10;

优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

#思路分析
#拿到的只有(2000011-2000020)条记录的id值
SELECT id FROM tb_item ORDER BY id LIMIT 2000000,10;
#再拿这些id去tb_item表里面去查数据
SELECT * FROM tb_item t,(SELECT id FROM tb_item ORDER BY id LIMIT 2000000,10) a WHERE  t.id = a.id;

#原始语句
SELECT * FROM tb_item limt 2000000,10;
#优化后
SELECT * FROM tb_item t,(SELECT id FROM tb_item ORDER BY id LIMIT 2000000,10) a WHERE  t.id = a.id;

优化思路二

该方案适用于主键自增(主键不能断层，如：1,2,3,4,5,8,9)的表，可以把Limit 查询转换成某个位置的查询 。

#原始语句
SELECT * FROM tb_item limt 2000000,10;
#优化后	查询id大于2000000的10条数据
SELECT * FROM tb_item WHERE id>2000000 LIMIT 10;

使用SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

案例：

#查看tb_seller表
SELECT * FROM tb_seller;
#查看索引
SHOW INDEX FROM tb_seller;
#创建单列索引
CREATE INDEX idx_seller_name ON tb_seller(NAME);
CREATE INDEX idx_seller_status ON tb_seller(STATUS);
CREATE INDEX idx_seller_address ON tb_seller(address);
#创建复合索引
CREATE INDEX idx_seller_name_sta_addr ON tb_seller(NAME,STATUS,address);

USE INDEX

使用某个索引，节省了比较索引步骤，帮助数据库选择索引。

use index是一种参考，建议数据库使用某个索引，并不是强制令其使用某个索引。

#原始语句
#可以使用的索引：idx_seller_name单列索引、idx_seller_name_sta_addr复合索引
#最终数据库经过比较索引，选择了idx_seller_name单列索引
EXPLAIN SELECT * FROM tb_seller WHERE NAME = '小米科技';
#改变索引：
#人为改变索引,令其使用idx_seller_name_sta_addr复合索引
#数据库只能使用idx_seller_name_sta_addr复合索引
EXPLAIN SELECT * FROM tb_seller USE INDEX(idx_seller_name_sta_addr) WHERE NAME = '小米科技';
#改变索引：
#人为改变索引,令其使用idx_seller_name单列索引
#数据库只能使用idx_seller_name单列索引
EXPLAIN SELECT * FROM tb_seller USE INDEX(idx_seller_name) WHERE NAME = '小米科技';

IGNORE INDEX

忽略某个索引，节省了比较索引步骤，帮助数据库选择索引。

#原始语句
EXPLAIN SELECT * FROM tb_seller WHERE NAME = '小米科技';
#忽略idx_seller_name单列索引，只能选择使用idx_seller_name_sta_addr复合索引
EXPLAIN SELECT * FROM tb_seller IGNORE INDEX(idx_seller_name) WHERE NAME = '小米科技';
#忽略idx_seller_name_sta_addr复合索引，只能选择使用idx_seller_name单列索引
EXPLAIN SELECT * FROM tb_seller IGNORE INDEX(idx_seller_name_sta_addr) WHERE NAME = '小米科技';

FORCE INDEX

强制使用某个索引。

#原始语句
#可能用到的索引：idx_seller_address
#实际上用到的索引：NULL
#原理：如果全表扫描比走索引还快，就不走索引
EXPLAIN SELECT * FROM tb_seller WHERE address = '北京市';
#人为改变索引,建议数据库使用idx_seller_address单列索引
#use index是一种参考，建议数据库使用某个索引，并不是强制令其使用某个索引。
EXPLAIN SELECT * FROM tb_seller USE INDEX(idx_seller_address) WHERE address = '北京市';
#force index强制数据库使用idx_seller_address单列索引
EXPLAIN SELECT * FROM tb_seller FORCE INDEX(idx_seller_address) WHERE address = '北京市';