cms做企业网站建站系统,php 网站 整合 数据库,网页制作与设计书籍,哪些网站可以用来做百科参考一、SQL优化
sql优化是指#xff1a;通过对sql语句和数据库结构的调整#xff0c;来提高数据库查询、插入、更新和删除等操作的性能和效率。
1、插入数据优化
要一次性往数据库表中插入多条记录#xff1a;
insert into tb_test values(1,tom);
insert into tb_tes…一、SQL优化
sql优化是指通过对sql语句和数据库结构的调整来提高数据库查询、插入、更新和删除等操作的性能和效率。
1、插入数据优化
要一次性往数据库表中插入多条记录
insert into tb_test values(1,tom);
insert into tb_test values(2,cat);
insert into tb_test values(3,jerry);.....1.1、优化方案一批量插入数据 Insert into tb_test values(1,Tom),(2,Cat),(3,Jerry);1.2、优化方案二手动控制事务
start transaction;insert into tb_test values(1,Tom),(2,Cat),(3,Jerry);insert into tb_test values(4,Tom),(5,Cat),(6,Jerry);insert into tb_test values(7,Tom),(8,Cat),(9,Jerry);commit;1.3、优化方案三主键顺序插入
主键顺序插入性能要高于乱序插入。
1.4、大批量插入数据
如果一次性需要插入大批量数据(比如: 几百万的记录)使用insert语句插入性能较低此时可以使用MySQL数据库提供的load指令进行插入。
可以执行如下指令将数据脚本文件中的数据加载到表结构中
-- 客户端连接服务端时加上参数 -–local-infilemysql –-local-infile -u root -p-- 设置全局参数local_infile为1开启从本地加载文件导入数据的开关
set global local_infile 1;-- 执行load指令将准备好的数据加载到表结构中
load data local infile /root/sql1.log into table tb_user fields terminated by , lines terminated by \n ;在load时主键顺序插入性能高于乱序插入
2、主键优化
2.1、数据的组织方式
在InnoDB存储引擎中表数据都是根据主键顺序组织存放的这种存储方式的表称为索引组织表。 行数据都是存储在聚集索引的叶子节点上的。 在InnoDB引擎中数据行是记录在逻辑结构 page 页中的而每一个页的大小是固定的默认16K。 那也就意味着一个页中所存储的行也是有限的如果插入的数据行row在该页存储不小将会存储到下一个页中页与页之间会通过指针连接。 2.2、页分裂
页可以为空也可以填充一半也可以填充100%。每个页包含了2-N行数据(如果一行数据过大会行溢出)根据主键排列。MySQL中的页分裂是指当Btree索引中的一个页已经满了再插入新的记录时该页会被分裂成两个页。其中一些记录会被留在原来的页中而其余记录则会被移到一个新的页中。 页分裂是为了避免数据过度集中在一个页上而引起的性能问题。通过将记录分散到更多的页中可以减少索引的深度从而提高查询效率。在MySQL中当一个页满了之后会触发页分裂。具体来说MySQL会将该页中的记录按照顺序依次遍历找到一个位置将其分为两个部分并将后面的记录移到一个新的页中。同时为了保证索引有序性MySQL还会将新页中的第一条记录的键值插入到父节点中并调整父节点中的指针。如果父节点也满了则递归进行分裂操作。 页分裂通常会发生在以下情况 顺序插入当数据按索引顺序插入且最后一页已满会创建一个新的页并在其上继续插入。乱序插入如果数据插入到中间的某个索引位置导致中间的页溢出那么会把一部分数据移动到新页中以便为新数据腾出空间。可能还需要重新设置链表指针
2.3、页合并
当删除一行记录时实际上记录并没有被物理删除只是记录被标记flaged为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到 MERGE_THRESHOLD默认为页的50%InnoDB会开始寻找最靠近的页前或后看看是否可以将两个页合并以优化空间使用。
参数MERGE_THRESHOLD是合并页的阈值可以自己设置在创建表或者创建索引时指定。
2.4、主键的设计原则
满足业务需求的情况下尽量降低主键的长度。插入数据时尽量选择顺序插入选择使用AUTO_INCREMENT自增主键。尽量不要使用UUID做主键或者是其他自然主键如身份证号。业务操作时避免对主键的修改。
3、order by优化 MySQL的排序有两种方式 Using filesort : 通过表的索引或全表扫描读取到满足条件的数据行然后在排序缓冲区sort buffer中完成排序操作所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。Using index : 通过有序索引顺序扫描直接返回有序数据这种情况即为 using index不需要额外排序操作效率高。Using index的性能高而Using filesort的性能低 order by优化原则: A. 根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则。B. 尽量使用覆盖索引。C. 多字段排序, 一个升序一个降序此时需要注意联合索引在创建时的规则ASC/DESC。D. 如果不可避免的出现filesort大数据量排序时可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。
4、group by优化
在分组操作中通过以下两点进行优化以提升性能 A. 在分组操作时可以通过索引来提高效率。没有使用索引进行分组Extra下会出现Using temporate使用临时表这个性能很低的B. 分组操作时索引的使用也是满足最左前缀法则的
5、limit优化
在数据量比较大时如果进行limit分页查询在查询时越往后的数据分页查询效率越低。 优化思路: 一般分页查询时通过创建 覆盖索引 能够比较好地提高性能可以通过覆盖索引加子查询形式进行优化。
6、count优化
如果数据量很大在执行count操作时是非常耗时的。 MyISAM 引擎把一个表的总行数存在了磁盘上因此执行 count(*) 的时候会直接返回这个数效率很高 但是如果是带条件的countMyISAM也慢。InnoDB 引擎就麻烦了它执行 count(*) 的时候需要把数据一行一行地从引擎里面读出来然后累积计数。 要大幅度提升InnoDB表的count效率主要的优化思路自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)
6.1、count函数的用法
count() 是一个聚合函数对于返回的结果集一行行地判断如果 count 函数的参数不是NULL累计值就加 1否则不加最后返回累计值。 用法count*、count主键、count字段、count数字
按照效率排序的话count(字段) count(主键 id) count(1) ≈ count(*)所以尽量使用 count(*)。
7、update优化
主要需要注意一下update语句执行时的注意事项 在更新数据时最好根据索引字段进行更新 否则会出现行锁升级为表锁的问题就是锁住整张表导致并发性能降低
-- id是主键索引
update course set name javaEE where id 1 ;-- 此时执行上面的update语句只会出现行锁
InnoDB的行锁是针对索引加的锁不是针对数据记录加的锁 ,并且该索引不能失效否则会从行锁升级为表锁 。
