MySQL优化

490 views

SQL 优化

1.插入数据

  • 批量插入数据
insert into table values(col1,col2),(col1,col2);
  • 手动控制事务
start transaction;
...
commit
  • 主键顺序插入
1,2,3,4,5,78,99
  • 大批量插入
-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-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';

2.主键优化

图片

行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过 InnoDB 的逻辑结构图：

图片

在 InnoDB 引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认 16K。那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行 row 在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。

2.1 页分裂

页可以为空，也可以填充一半，也可以填充 100%。每个页包含了 2-N 行数据 (如果一行数据过大，会行溢出)，根据主键排列。

1.插入50

图片

2.过程

图片

3.但是 47 所在的 1# 页，已经写满了，存储不了 50 对应的数据了。 那么此时会开辟一个新的页 3#

图片

4.但是并不会直接将 50 存入 3# 页，而是会将 1# 页后一半的数据，移动到 3# 页，然后在 3# 页，插入 50。

图片

图片

5.调换页

图片

2.2 页合并

1.如果逻辑删除（仅仅标记为删除，不是物理删除），达到阈值，默认页的 50%

图片

2.当页中删除的记录达到 MERGE_THRESHOLD（默认为页的 50%），InnoDB 会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用

图片

图片

知识小贴士：

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

2.3 主键设计原则

1.在满足业务下，使用最低主键长度

2.插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键

3.尽量不要使用 UUID 做主键或者是其他自然主键，如身份证号。

4.业务操作时，避免对主键的修改。

3 order by 优化

MySQL 的排序，有两种方式：

Using filesort

通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sortbuffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

Using index

通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高 。

1.对于没有索引情况下的排序，一定是全表扫描，为 index 级速度 (慢)

2.默认情况下的联合索引都是升序的。也就是

create index index_name on table(age asc,phone asc) #asc可以省略

正例 (索引有效)

select age from table order by age

select age,phone from table order by age,phone

select age from table order by age desc

反例

select * from table order by ... #不管安排什么字段来排序，只有用*一定索引失效。
select age,name from table order by age
# 由于name字段是不包含在idx_age_name索引中所以无法使用索引，此时需要进行文件排序，即Extra: Using filesort
select age,name from table order by age,name desc #一升一降，部分失效

select age from table order by phone #需filesort辅助

总之

  • 大前提，select 中的字段要求在索引中出现。
  • 借联合索引时，要遵循最左匹配原则，当然可以先用 where age 后 order by name，也是遵循的。
  • 借联合索引，order by 的要求字段，要么同时升序要么同时降序。
  • 额外信息中出现 Using index 和 filesort，性能都是不太达标的！

想一升一降的排序，可以这样创建索引

create index index_name on table(age ,name desc)

如此一来，一升一降就不会导致索引 (部分) 失效

4.group by 优化

与 order by 一致，有索引时候会显著提升分组速度。

  • Using temporary：使用临时表来分组，效率低

  • Using index：使用索引分组

同样的，

1.select 应该只出现索引有的字段

2.最左匹配，允许先 where 匹配字段，后 group by

5 limit 优化

select * from table limit 100w,10;

这样的越往后的分页，效率越低。

MySQL 官方建议：

覆盖索引

select id from table order by id limit 100w,10;
  • 如果没有 order by id 默认下是按照物理磁盘的存储顺序来显示数据的！而不是安排自增 id，其实不存在页合并的话，那么查出来的就是 id 升序

使用连表查询出 id 的行数据

select * from tb_sku t , (select id from table order by id limit 100w,10) a where t.id = a.id;

6.count 优化

上结论

count(*)≈count(1)>count(主键)>count(字段)

尽量使用count(*), MySQL专门优化了性能！

7.update 优化

使用 update 会使用到 InnoDB 的锁机制。

锁是针对索引的，如果 where 中没有索引或索引失效，那么将会升级为表锁，性能大大降低

update ... where name='Jack'
#若name没有索引，则是表锁；有索引，则是行锁

另外，如果 name='Jack’有多条记录，这么这多条记录都会施加行锁！

8.汇总

3~7 本质还是针对索引优化，因此掌握索引优化，就掌握了 SQL 优化的 80%！！！

  • 插入数据：批量插入、手动控制事务、主键顺序插入

  • 主键优化：长度短、顺序插入，用 auto_incremrnt 而不是 UUID

  • order by：using index 直接索引返回而不是 Using filesort

  • group by：多字段分组要满足最左前缀匹配

  • limit：覆盖索引 (无需回表)+ 子查询

  • count：count(字段)<count(主键 id) < count(数字) ≈ count(*)

  • update：尽量根据 (where) 主键 / 索引字段进行数据更新