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%'
posted @ 2025-02-19 11:22  skystrivegao  阅读(29)  评论(0)    收藏  举报