MySQL--》快速提高查询效率：SQL语句优化技巧与实践

目录插入数据order by与group by优化limit、count、update优化插入数据在对数据库当中进行插入数据操作通常我们都会使用insert进行插入数据可由于每次insert都会和数据库建立连接频繁的插入数据就会导致效率上的降低这里我们就需要对insert插入数据进行一定程度上的优化insert优化对insert优化主要可以从以下几个方面入手-- 批量插入 insert into tb_test values (1, Tom), (2, Jerry), (3, Bob); -- 手动提交事务 start transaction insert into tb_test values (1, Tom), (2, Jerry), (3, Bob); insert into tb_test values (4, Tom4), (5, Jerry5), (6, Bob6); insert into tb_test values (7, Tom7), (8, Jerry8), (9, Bob9); commit; -- 主键顺序插入 主键乱序插入8 1 9 21 88 2 4 15 89 5 7 3 主键顺序插入1 2 3 4 5 7 8 9 15 21 88 89主键优化原理在InnoDB存储引擎中表数据都是根据主键顺序组织存放的这种存储方式的表称为索引组织表。页分裂当我们插入数据的时候页会进行分裂页可以为空页可以填充一半也可以填充100%每一页包含了2-N行数据(如果一行数据过大行会溢出)根据主键排列主键顺序拆入如下所示如果我们想在下面的数据中再插入50的数据由于前两页数据已经满员因此会开辟一个新的分页然后会找到第一个数据页50%的位置23和47是超出了一半了此时会先将这两个数据移动到新开辟的数据页然后将新数据50再插入到这个数据页此时再对链表指针进行一个重新的排布让1号的数据页的下一个数据页为3号3号后一个数据页才是2号这种重新设置排序位置称为”页分裂“所以乱序插入的情况下可能就会发生页分裂现象页合并当删除一行记录时实际上记录并没有被物理删除只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%)InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用2号数据页被删除一半数据之后会检索后面3号数据页是否有合并的可能性如果有就合并到2号数据页那么3号数据页就可以空闲出来了空闲出来的数据页如果后面再插入数据的话就可以直接插入到3号数据页当中就可以了主键设计原则1在满足业务需求的情况下尽量降低主键的长度。2插入数据时尽量选择顺序插入选择使用AUTO_INCREMENT自增主键。3尽量不要使用UUID做主键或者是其他自然主键如身份证号。4业务操作时避免对主键的修改。load如果一次性需要插入大批量数据使用insert语句插入性能较低此时可使用MySQL数据库提供的load指令进行插入可以将复合一定规律的文件通过load操作变成表数据使用load加载数据的主要步骤如下所示(这里主键顺序插入的性能也是高于乱序插入)-- 客户端连接服务器时加上参数 --local-infile mysql --local-infile -u root -p -- 查看是否开启本地文件导入开关 select local_infile; -- 设置全局参数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 ;order by与group by优化order by优化对order by进行优化主要分为以下两种情况1Using filesort通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区sort buffer中完成排序操作所有不是通过索引直接返回排序结果的排序都叫FileSort排序。2Using index通过有序索引顺序扫描直接返回有序数据这种情况即为using index不需要额外排序操作效率高。-- 没有创建索引时根据agephone进行排序 explain select id, age, phone from emp order by age, phone; -- 创建索引 create index idx_emp_age_phone on emp(age, phone); -- 创建索引后根据agephone进行升序排序 explain select id, age, phone from emp order by age, phone; -- 创建索引后根据agephone进行降序排序 explain select id, age, phone from emp order by age desc, phone desc; -- 创建索引 create index idx_emp_age_phone_ad on emp(age asc , phone desc); -- 创建索引后根据agephone进行降序排序 explain select id, age, phone from emp order by age asc, phone desc;order by使用注意1根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则2尽量使用覆盖索引3多字段排序一个升序一个降序此时需要注意联合索引在创建时的规则(ASC/DESC4如果不可避免的出现filesort大数据量排序时可以适当增大排序缓冲区大小sort_buffer_size(默认256k)group by优化在进行分组操作时也可以通过索引来提高效率当然索引的使用也是需要满足最左前缀法则如下所示limit、count、update优化limit优化一个常见又非常头疼的问题就是limit2000000,10此时需要MySQL排序前2000010记录仅仅返回2000000-2000010的记录其他记录丢弃查询排序的代价非常大优化思路一般分页查询时通过创建覆盖索引能够比较好地提高性能可以通过覆盖索引加子查询形式进行优化explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a wheret.id a.id;count优化MISAM引擎把一个表的总行数存在了磁盘上因此执行count(*的时候会直接返回这个数效率很高InnoDB引擎就麻烦了它执行count(*的时候需要把数据一行一行地从引l擎里面读出来然后累积计数优化思路一般是通过自己定义的key和value自己进行计算新增就累加删除就累减少自己计数的方式进行count()是一个聚合函数对于返回的结果集一行行地判断如果count函数的参数不是NULL累计值就加1否则不加最后返回累计值。count用法主要有以下几种count(*)InnoDB引擎并不会把全部字段取出来而是专门做了优化不取值服务层直接按行进行累加。count(主键)InnoDB引擎会遍历整张表把每一行的主键id值都取出来返回给服务层服务层拿到主键后直接按行进行累加(主键不可能为null)。count(字段)没not null约束InnoDB引擎会遍历整张表把每一行的字段值都取出来返回给服务层服务层判断是否为null不为nul计数累加有not null约束InnoDB引擎会遍历整张表把每一行的字段值都取出来返回给服务层直接按行进行累加。count(1)InnoDB引擎遍历整张表但不取值服务层对于返回的每一行放一个数字“1”进去直接按行进行累加。按照效率排序的话count(字段)count(主键id)count(1)~count(*)所以尽量使用count(*)。update优化InnoDB的行锁是针对索引加的锁不是针对记录加的锁并且该索引不能失效否则会从行锁升级为表锁update student set no2000100100where id 1; update student set no2000100105 where name韦一笑;