MYSQL-SQL优化(持续更新ing~)
MYSQL-SQL优化
一、Insert语句优化
1、批量插入
2、手动提交事务
频繁的事务开启和提交也是耗费资源的
start transaction;
insert xxx
insert xxx
commit;
3、主键顺序插入
4、大批量的数据插入,使用Mysql中的load指令插入数据
二、主键优化
1、页分裂
在InnonDB存储引起中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表,主键乱序插入可能会导致“页分裂”
2、页合并
当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。当页中的删除记录达到MEGE_THRESHOLD(合并页的阈值:默认为页的50%),InnonDB会开始寻找靠近的页(前或后)看看是否可以将两个页合并以优化空间使用
3、主键设计原则
满足业务的情况下,尽量降低主键的长度
插入数据时,尽量选择顺序插入。选择Auto_increment这类的自增主键
尽量不要使用UUID做主键或者是其他自然主键,如身份证号。
三、OrderBy 排序优化
1、实现原理:OrderBy有两种实现方式:Using filesort、Using index其中
Using filesort:通过表的索引或全表扫描,读取满足条件的行数据,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回结果的排序都叫Using filesort排序。
Using index:通过有序索引顺序扫描直接返回有序数据,这种情况即为Using index,不需要额外排序,操作效率高
2、可以通过explain查看执行计划,Extra列查排序策略
3、最左前缀法则:排序的时候,如果使用组合索引,执行OrderBy排序语句的字段顺序要与创建索引时的字段顺序保持一致
4、当使用组合索引时,如果可以明确不同字段的排序方式,可以在创建索引的时候就进行指明。如create index_age_phone(age asc, phone desc),否则都默认为升序(ASC)
四、GroupBy 分组优化
五、Limit 分页查询优化
1、索引覆盖 + 子查询
Eg:
-- 查询表student,ID从20000到20010的数据
select * from student limit 20000,10
-- 覆盖索引 + 子查询的方式修改为:
select s.x from student, (select id from student order by id limit 20000,10) a where s.id = a.id
六、Count 计数优化
MyISAM引擎 把一张表的总行数存在了磁盘上,因此在没有where查询条件在执行count(*)的时候,会直接返回这个数
InnonDb引擎在执行count(*)的时候,需要把数据一行一行的从引擎里面读取出来,然后进行累计计数
七、其他
1、避免使用in, not in会导致数据库放弃索引进行全表扫描
SELECT * FROM t WHERE id IN (2,3)
SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)
优化方式:如果是连续数值,可以用between代替。如下:
SELECT * FROM t WHERE id BETWEEN 2 AND 3
如果是子查询,可以用exists代替。如下:
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)
2、避免使用or 会导致数据库引擎放弃索引进行全表扫描 如下
SELECT* FROM WHERE id = 1 or id = 2
优化方式:可以用union on 来代替or 如下:
SELECT * FROM WHERE id = 1
UNION
SELECT * FROM WHERE id = 2
3、尽量避免使用null值判,会导致数据库搜索引擎进行全表扫描
SELECT * FROM WHERE age is null
优化方式:可以给默认值如0 然后对默认值进行判断
SELECT * FROM t WHERE age = 0
4、尽量避免在where条件中等号左侧进行表达式、函数操作,会导致数据库搜索引擎进行全表扫描:
SELECT * FROM WHERE score / 10 = 9
SELECT * FROM WHERE SUBSTR(username,1,2) = 'li'
优化方式:
SELECT * FROM WHERE score = 10 * 9
SELECT * FROM WHERE username LIKE 'li%'
本文来自博客园,作者:skystrivegao,转载请注明原文链接:https://www.cnblogs.com/skystrive/p/18723250
整理不易,如果对您有所帮助 请点赞收藏,谢谢~