南康区建设局网站,flash做网站导航,正规网站备案代理,wordpress知更鸟最新个人简介#xff1a;Java领域新星创作者#xff1b;阿里云技术博主、星级博主、专家博主#xff1b;正在Java学习的路上摸爬滚打#xff0c;记录学习的过程~ 个人主页#xff1a;.29.的博客 学习社区#xff1a;进去逛一逛~ SQL优化 ⑩③【MySQL】了解并掌握SQL优化1. 插… 个人简介Java领域新星创作者阿里云技术博主、星级博主、专家博主正在Java学习的路上摸爬滚打记录学习的过程~ 个人主页.29.的博客 学习社区进去逛一逛~ SQL优化 ⑩③【MySQL】了解并掌握SQL优化1. 插入数据 优化2. 主键优化3. order by 排序优化4. group by 分组优化5. limit 分页优化6. count 优化7. update 更新优化 ⑩③【MySQL】了解并掌握SQL优化 1. 插入数据 优化
insert优化
⚪使用批量插入 ⚪手动提交事务每次SQL语句执行后事务自动提交手动提交避免了多次提交提升效率 ⚪使用主键顺序插入顺序比乱序速度更快性能更高 大批量插入数据
如果一次性需要插入大批量数据使用insert语句插入性能较低此时可以使用MySQL数据库提供的load指令进行插入。操作如下
# 命令行客户端连接数据库时加上参数 --local-infile
mysql --local-infile -u root -p-- 查看从本地加载文件导入数据的开关是否开启
select local_infile;-- 设置全局参数local_infile为1表示开启从本地加载文件导入数据的开关。
set global local_infile1;-- 执行load指令将准备好的数据加载到表结构中
-- 加载文件: /root/sql.log 中的数据插入表
-- 字段间使用 逗号, 分隔
-- 行间使用 换行\n 分隔
load data local infile /root/sql.log into table 表名 fields terminated by , lines terminated by \n;2. 主键优化
数据组织方式
在InnoDB存储引擎中表数据都是根据主键顺序组织存放的这种存储方式的表称为索引组织表(index organized table 简称IOT)。 页分裂 页可以为空也可以填充一半也可以填充100%。每个页包含了至少2行数据如果一行数据多大会行溢出)根据主键排列。 分裂后插入↓ 重新设置指针↓ 页合并 当删除一行记录时实际上记录并没有被物理删除只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。 当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%)InnoDB会开始寻找最靠近的页前或后看看是否可以将两个页合并以优化空间使用。 合并↓ 主键设计原则
主键设计原则 ①在满足业务需求的情况下尽量降低主键的长度。②插入数据时尽量选择顺序插入选择使用AUTO_INCREMENT自增主键。③尽量不要使用UUID做为主键或者作为其他自然主键如身份证号。④在业务操作时尽量避免对主键的修改。 3. order by 排序优化
order by 优化
①. Using filesort : 通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区sort buffer中完成排序操作所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
②. Using index : 通过有序索引顺序扫描直接返回有序数据这种情况即为using index,.不需要额外排序操作效率高。
排序效率Using index Using filesort order by优化策略: ①根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则。 -- 没有建立索引时排序性能为Using filesort
explain select id,age,phone from tb_user order by age,phone;-- 为排序字段建立合适索引
create index idx_age_phone_aa on tb_user(age,phone);
-- 等价于
create index idx_age_phone_aa on tb_user(age asc,phone asc);
-- 建立索引后排序性能提升为Using index ②尽量使用覆盖索引非覆盖索引需要回表查询会从Using index 变为 Using filesort。 ③多字段排序一个升序一个降序此时需要注意联合索引在创建时的规则(ASC\DESC)。 -- 一个升序一个降序
select id,age,phone from tb_user order by age asc,phone desc;-- 注意联合索引在创建时的规则
create index idx_age_phone_ad on tb_user(age asc,phone desc);④如果不可避免地出现filesort大数据量排序时可以适当增大排序缓冲区sort_buffer_size的大小默认256k。 -- 查看参数sort_buffer_size大小
show variables like sort_buffer_size;-- 设置参数sort_buffer_size大小
set sort_buffer_size自定义的大小;4. group by 分组优化 根据分组字段建立合适的索引来提高效率。 分组操作时多字段通过联合索引排序也是遵循最左前缀法则的。 -- 如何建立合适索引建议使用联合索引可参考上文的order by优化5. limit 分页优化
一个常见又非常头疼的问题就是大数据量的分页如limit2000000,10,此时需要MySQL排序前2000010记录仅仅返回2000000到2000010的记录其他记录丢弃查询排序的代价非常大。 优化策略 一般分页查询时通过建立覆盖索引能够较好提升性能可通过覆盖索引子查询形式进行优化。 -- 优化前
select * from tb_sku limit 2000000,10;-- 优化后
-- 子查询的id字段存在主键索引order by性能得到优化
-- 根据子查询的到的主键字段id进行查询效率高。
select s.* from
tb_sku s,(select id from tb_sku order by id limit 2000000,10) i
where s.id i.id;6. count 优化
count()
count()是一个聚合函数对于返回的结果集一行行地判断如果count()函数的参数不是NULL,累计值就加1否则不加最后返回累计值。只记录不为NULL的记录**用法**count(*)、count(主键)、count(字段)、count(1) ①count(*) InnoDB引擎并不会把全部字段取出来而是专门做了优化不取值服务层直接按行进行累加。 ②count(主键) InnoDB引擎会遍历整张表把每一行的主键id值都取出来返回给服务层。服务层拿到主键后直接按行进行累加主键不可能为NULL)。 ③count(字段) **没有not null约束**InnoDB引擎会遍历整张表把每一行的字段值都取出来返回给服务层服务层判断是否为null,不为null,计数累加。**有not null约束**InnoDB引擎会遍历整张表把每一行的字段值都取出来返回给服务层直接按行进行累加。 ④count(1) lnnoDB引擎遍历整张表但不取值。服务层对于返回的每一行放一个数字“1”进去直接按行进行累加。 7. update 更新优化
需要优化的问题
InnoDB的行锁是针对索引加的锁不是针对记录加的锁更新没有索引的记录或索引失效使用的锁会从行锁变为表锁。使用表锁会使并发性能下降所以应当经可能去更新 使用了索引的字段。
