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%'