SQL优化
插入数据
批量插入数据
不要一条一条的数据插入数据,但不建议超过1000条,500-1000条最合适。如果更多的数据,则分开批量插入,而尽量减少mysql的连接关闭,从而提高效率
INSERT INTO tableName(xx) VALUES(xx),(xx),(xx);
大批量插入数据
如果一次性需要插入大批量数据,使用INSERT语句插入行能较低,此时可以使用MySQL数据库提供的load指令进行插入,一行一行的分开,列与列用,
或;
分隔开
# 在客户端连接服务端时,加上参数 --local-infile
mysql --local-infile -u root -p
#设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile=1;
#执行load指令将准备好的数据,加载到表结构中
load data local infile '文件路径' into table tableName fields terminated by '分隔号' lines terminated by '\n';
手动提交事务
MySQL是自动帮我们提交事务的,我们可以手动的开启提交事务,从而避免多次的开启和提交事务
START TRANSACTION;
INSERT INTO tableName(xx) VALUES(xx),(xx),(xx);
INSERT INTO tableName(xx) VALUES(xx),(xx),(xx);
INSERT INTO tableName(xx) VALUES(xx),(xx),(xx);
COMMIT;
主键顺序插入
因为有关数据结构的问题
# 主键乱序插入:2 4 1 3 8 6
# 主键顺序插入:1 2 3 4 6 8 -- 推荐
# 主键顺序插入性能高于乱序插入
主键优化
数据组织方式
在InnoDB存储引擎中,表数据都是根据组件顺序组织存放的,这种存储方式的表称为索引组织表(index organized table IOT)
聚集索引的存储方式(叶子节点下面存的是行的数据)
逻辑存储结构
所以,如果是顺序的主键插入的话,就可以直接插入到数据的最后面,如果是乱序的插入,后面的page就需要页分裂,重新整理顺序分配页结构
页分裂
页是最小的存储单元
页可以为空,也可以填充一半,也可以填充100%。每个也包含了2-N行数据(如果一行数据多大,会行溢出),根据主键排列
页合并
当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且他的空间变得允许其他记录声明使用。
当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近也的(前或后)看看是否可以将两个页合并以优化空间使用
MERGE_THRESHOLD: 合并也的阈值,可以自己设置,在创建表或创建索引时指定
主键设计原则
- 满足业务需求的情况下,尽量降低主键的长度
- 插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键
- 尽量不要使用UUID做主键或者其他自然主键,如身份证号
- 业务操作时,避免对主键的修改
ORDER BY 优化
- Using filesort: 通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫FileSort排序
- Using index:通过有序索引顺序扫描直接返回有序数据,这种情况即我using index,不需要额外排序,操作效率高
根据 WHERE 条件查询差不多,ORDER BY 也需要创建安装排序条件给条件字段创建索引,也满足最左前缀法,创建索引时,可以指定字段的顺逆序
CREATE INDEX idx_user_age_phon_ad ON tableName(cloName1 asc, cloName2 desc);
规则:
- 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则
- 尽量使用覆盖索引
- 多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)
- 如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小sort_buffer_size(默认256k)
GROUP BY 优化
一般的优化都是在创建索引来完成的
SELECT xx, count(*) FROM tableName GROUP BY xx; # 一般分组查询是按分组的条件返回字段的
规则:
- 在分组操作时,可以通过索引来提高效率
- 分组操作时,在索引的使用也是满足最左前缀法则的
LIMIT 优化
在大数据量情况下,越往后越耗时
SELECT * FROM tableName limit 0, 10; # 0: 起始行,10: 从起始行开始查询多少条
一个常见有非常头疼的问题就是 limit 200000, 10 此时需要MySql排序前 20000010 记录,仅仅返回200000-200010的记录,其它记录丢弃,查询排序的代价非常大
优化思路:一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化
# where in 之后不能使用 limit
# 所以我们可以使用自查询获得全部数据,先按主键排序查询出来,再查询所有字段
SELECT * FROM tableName t, (SELECT id FROM tableName ORDER BY id LIMIT 200000, 10) a WHERE t.id = a.id;
COUNT 优化
MyISAM 引擎吧一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率非常高
InnoDB 引擎执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数
InnoDB 优化思路:自己计数(自己维护)
count的几种用法
count()是一个聚合函数,对于返回的结果集,一行一行地判断,如果count函数的参数不是NULL,累计值就加1,否则不加,最后返回累计值
用法:count(*)、count(主键)、count(字段)、count(1)
-
count(*): 表中的总记录数
InnoDB引擎不会吧全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加
-
count(主键):表中的总记录数
InnoDB引擎会遍历整张表,把每一行的主键id值都取出来,返回给服务层。服务层拿到主键后,直接按行进行累加(主键不可能为NULL)
-
count(字段): 表中字段的值不为NULL的总记录数
- 没有NOT NULL约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为NULL,不为NULL,计数累加
- 有NOT NULL约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,直接按行进行累加(不为NULL,不用再判断)
-
count(1): xx?
InnoDB 引擎整张表遍历,但不取值。服务层对于返回的每一行,放一个数字“1”进去,直接按行进行累加
效率性能排序:count( * ) ≈ count(1) > count(主键) > count(字段),所以建议尽量使用count( * )
UPDATE 优化
InnoDB引擎是行锁,所以,UPDATE的时候,会把这行锁住,但是条件不添加索引的话,加的锁会是表锁,那么,其它也不能更新。但是条件加了索引的话,就可以是行锁,更新其它的行时是可以的。
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁,升级为表锁,并发性能就会降低