湖南省城乡建设厅网站查证,win7网站开发教程,仿站工具在线,angularjs的网站模板1、SQL 优化
1.1、插入数据优化
1.1.1、Insert 优化
1、批量插入
插入多条数据时#xff0c;不建议使用单条的插入语句#xff0c;而是下面的批量插入#xff1a;
INSERT INTO tb_name VALUES (),(),(),...;
批量插入建议一次批量 500~100 条#xff0c;如果数据量比…1、SQL 优化
1.1、插入数据优化
1.1.1、Insert 优化
1、批量插入
插入多条数据时不建议使用单条的插入语句而是下面的批量插入
INSERT INTO tb_name VALUES (),(),(),...;
批量插入建议一次批量 500~100 条如果数据量比较大建议通过多条批量插入语句来插入
2、手动提交事务 MySQL 默认会开启事务但是默认每执行一次插入语句就开启和关闭一次事务所以会有大量的事务启动关闭开销建议使用手动提交事务
START TRANSACTIONS;
INSERT INTO tb_name VALUES (),(),(),...;
INSERT INTO tb_name VALUES (),(),(),...;
INSERT INTO tb_name VALUES (),(),(),...;
...
COMMIT;
3、主键顺序插入
在插入数据时建议尽量顺序插入主键而不是乱序插入
主键顺序插入(高效)1 2 3 4 5 ...主键乱序插入(低效)5 3 4 2 1 ...
1.1.2、load 大批量插入数据
使用 load 指令将本地磁盘文件中的数据插入到数据库表当中
# 客户端连接MySQL服务器时加上参数 --local-infile
mysql --local-infile -uroot -p
# 设置全局参数 local_infile 1
set global local_infile 1;
# 执行 load 指令将准备好的数据加载到表中
load data local infile /path_to_data into table table_name fields terminated by , lines terminated by \n;
这里的 load 指令有点像 HQL 中的 load
LOAD DATA [LOCAL] INPATH /opt/module/data/xxx.txt TO TABLE table_name;
HQL 的 load 命令并不需要指定分隔符因为在建表的时候我们已经在 row format 中设置的文件的分隔符了此外这里想到今天使用 HQL load 命令的一些需要注意的问题
在向分桶表 load 数据的时候不能从 local 直接 load而是得先上传到 hdfs 上再从 hdfs load 到分桶表才行
1.2、主键优化
1.2.1、数据组织方式
在 InnoDB 存储引擎当中表数据都是根据主键顺序组织存放的这种存储方式的表称为索引组织表IOT
之前我们在学习索引的时候知道InnoDB 存储引擎中的索引可以分为聚集索引和二级索引而聚集索引正是由主键构成的一颗BTree它的叶子节点存储的是行数据 接下来我们看一下当我们往数据库表中插入数据的时候它的流程是什么样的
1.2.2、页分裂
page 可以填满也可以为空但在 InnoDB 存储引擎规定在一个 page 中至少包含 2 行数据
主键顺序插入
当主键顺序插入时一切都非常平静 主键乱序插入
下面我们看一下主键乱序插入时的情况 上面我们的主键都是乱序插入的可以看到现在来了一个主键为 50 的 row按道理它是应该放到主键为 47 的前面主键为 23 的后面也就是 page 1的但是显然 page 1 现在已经存满了那么接下来就会发生页分裂 首先page 1 因为主键为 23 的数据当前理应插入到 page 1会把自己 50% 之后的数据移动到一个新的 page 当中然后将要主键为 23 的新数据页添加进去最后将原本页之间的连接断开重新建立页的连接
所以不难想到如果是大量数据的场景下主键乱序插入时会出现频繁的页分裂现象性能很低与新增数据相反的是删除数据删除数据又会引起页合并
1.2.3、页合并
在 MySQL 中当删除一行记录时实际上数据并不会被物理删除只是记录被标记为删除并且它的空间变得允许被其它记录回收使用
当页中被删除的记录达到了 50%InnoDB 会开始寻找最靠近的页前或后看看能否将两个页合并以优化空间使用 1.2.4、主键设置原则
满足业务需求的情况下尽量降低主键的长度。因为在一张表中聚集索引只有一个而二级索引可以有多个如果主键很长二级索引也有很多那么就会在存储时会消耗大量的磁盘空间查询时也会消耗大量的磁盘IO插入数据时尽量选择顺序插入选择使用 AUTO_INCREMENT 自增主键不会出现页分裂现象尽量不要使用 uuid 做主键或者是其它自然主键如身份证号主键长度太长而且无序尽量避免主键的修改
1.3、order by 优化
Using filesort通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区 sort buffer 中完成排序操作所有不是通过索引直接返回排序结果的排序都是 FileSort 排序Using index通过有序索引顺序扫描直接返回有序数据这种情况就是 using index不需要额外排序操作效率高
也就是说我们在优化 order by 的时候尽量优化成 using index下面我们看一条普通的 order by 语句 可以看到在未建立索引时order by 语句默认走的是 filesort下面我们为 age 字段建立一个索引
CREATE INDEX idx_student_no ON student(no); 注意这里搜索时我设置投影的字段是 id 和 no其中 id 是主键no 我们刚创建了索引不能使用 select * 因为只有使用覆盖索引查询使用了索引并且需要返回的列在该索引中全部能够找到对应的值才能命中索引不然索引不生效 可以看到即使是降序排序也是索引也是可以命中优化的 现在我们创建一个联合索引idx_student_no_name做一个测试 可以看到当 order by 的字段顺序和联合索引相反时只有字段 no 能被索引命中name 不可以这是因为违背了最左前缀法则继续 可以看到当 order by 的两个字段分别升序和降序排列时降序的字段无法被索引命中这是因为 这是因为默认创建索引时索引字段都是按照升序进行排列的所以我们可以根据之后的需求在创建索引时根据排序规则进行创建
CREATE INDEX idx_student_no_name_ad ON student(no asc,name desc); 此时再次查看查询计划 可以看到这样就解决了字段排序规则不同的问题 注意
只有联合索引需要注意创建时的排序规则单列索引不需要因为单列索引默认升序反向扫描只需要反向扫描即可。如果不可避免 filesort 大数据排序时可以适当增加排序缓冲区的大小 sort_buffer_size 默认 256 K
1.4、group by 优化
这里我们同样讨论的是索引对于 group by 的影响因为分组也是通过索引来提高查询效率的 可以看到在未建立索引前使用 group by 语句时效率很低临时表但是创建索引后就可以走索引了 可以看到这次当我们用 age 字段 group by 时又出现了 temporary 效率依然不够好这是因为违背了联合索引的最左前缀法则可当我们同时使用 profession 和 age 进行 group by 时索引再次命中
但是有时候业务逻辑不需要我们去 group by 那么多的字段怎么办 其实就像上面这样我们也可以通过前面给联合索引左边的字段加个 where 条件让它满足最左前缀法则即可
1.5、limit 优化
在大数据场景下比如 limit 10000000,10 此时 MySQL 需要排序前 100000010 条记录存储引擎会把这 100000010 条记录返回给服务层的缓存并返回后 10 条记录其它丢弃查询排序的代价非常大这一类问题也叫做深度分页 MySQL 深度分页是指在分页查询数据量比较大的表时需要访问表中的某一段数据而这段数据的位置非常靠后需要通过较大的 offset 来获取目标数据。 阿里巴巴《Java 开发手册》 1.5.1、覆盖索引 子查询 1.5.2、inner join 延迟关联 上面这两种方式没什么区别下面是 inner join上面是笛卡尔积但是因为设置的过滤条件所以等价于一个 inner join对于 limit 的优化原理简单来讲就是控制返回的总页数。 对于上面的 limit 10000000,10 来说它会返回给服务端 10000010 条记录然后再根据 offset 挨个抛弃前 10000000 条记录返回给客户端剩余的 10 条记录。 可以看出当offset非0时server层会从引擎层获取到很多无用的数据而当select后面是*号时就需要拷贝完整的行信息拷贝完整数据跟只拷贝行数据里的其中一两个列字段耗时是不同的这就让原本就耗时的操作变得更加离谱。 因为前面的offset条数据最后都是不要的就算将完整字段都拷贝来了又有什么用呢所以我们可以将sql语句修改成下面这样
select * from tb_user where id (select id from tb_user order by id limit 10000000, 1) order by id limit 10;
上面这条sql语句里面先执行子查询 select id from page order by id limit 6000000, 1, 这个操作其实也是将在innodb中的主键索引中获取到60000001条数据然后server层会抛弃前6000000条只保留最后一条数据的id。
但不同的地方在于在返回server层的过程中只会拷贝数据行内的id这一列而不会拷贝数据行的所有列当数据量较大时这部分的耗时还是比较明显的。
在拿到了上面的id之后假设这个id正好等于10000000那sql就变成了
select * from tb_user where id (10000000) order by id limit 10;
这样 innodb 再走一次主键索引通过B树快速定位到id6000000的行数据时间复杂度是lg(n)然后向后取10条数据。
关于深度分页知乎这篇文章讲的很不错我也是受启发于这篇文章
1.6、count 优化
count 是一个聚合函数对于返回的结果集一行一行进行判断只有不是 NULL 才会计数count(字段)的时候count(*)或者count(id)依然计算null
SELECT COUNT(*) FROM table_name;
上面的 SQL 是查询当前表的总行数不同的存储引擎的效率是不一样的
对于 MyISAM 而言它会把表的总行数存到磁盘上所以不加条件直接查询 count(*) 会直接返回结果O1的时间复杂度而对 InnoDB 而言它就只能遍历整张表了性能很低
可以看到如果 count(*) 的查询语句中包含 where 过滤条件不管是 MYISAM 还是 InnoDB 性能都很差所以我们需要对它进行优化
1.6.1、count 的几种用法
对于 count我们使用的无非就是那几种
count(主键) InnoDB 会遍历整张表把每一行主键取出来返回给服务层服务层拿到主键后直接进行累加主键不可能为空count(字段) InnoDB 会遍历整张表把每一行字段值取出来返回给服务层如果字段有 not null 约束那么就直接按行累加如果没有那么就对非 null 的值进行计数count(1) InnoDB 遍历整张表但是不取值。服务层会对返回的每一行放一个数字 1 进去直接按行进行累加count(*) InnoDB 同样遍历整张表但是不会把字段取出来而是专门做了优化。服务层直接按行进行累加
按照排序效率count(字段) count(主键) count(1) count(*) 所以尽量使用 count(*)因为数据库专门对它做了优化
1.7、update 优化
关于 update 语句需要注意的就是update 的条件一定要是索引字段比如主键因为只有更新条件的字段是索引列才会是行锁否则将是表锁 可以看到当我们的更新条件是 no 字段时不是索引列当另一个客户端去更新数据时直接被阻塞最后甚至超时更新失败
所以当我们在使用 update 语句的时候一定要注意尽量使用索引字段做为更新条件去更新否则就会出现行锁升级为表锁并发性能就会降低因为 InnoDB 的行锁是针对索引加的锁而不是针对记录加的锁
总结
插入数据 inset 语句大数据量分成500~1000的记录批量插入建议使用批量插入而且建议手动事务避免频繁创建销毁事务开销、主键顺序插入避免页分裂顺序插入性能高大批量数据load data local infile主键优化 主键设计应尽量短、顺序插入建议 auto_increment 而不是 uuid比如身份证号不仅长度长而且无序order by 优化 using index直接返回数据不需要再去服务层缓冲区排序性能高using filesort需要将查询返回的结果去服务层缓冲区去排序所以在对 order by 进行优化时其实是使用索引来进行优化的涉及导排序的字段尽量建立索引同时注意创建索引时的升序降序问题尽量使用覆盖索引而不是 select *group by 优化 索引多字段分组时遵循最左前缀法则limit 优化 深度分页性能低使用覆盖索引 子查询count 优化 count(字段) count(主键) count(1) count(*)update 优化 update 的条件字段尽量使用索引字段尽量主键InnoDB 的行锁是针对索引加的锁而不是针对记录加的锁
所以总之我们在做 SQL 优化的时候其实基本都是在针对索引进行优化
