SQL优化

查看当前数据库 insert、update、select、delete的访问频率，可以根据操作类型的频率做针对性优化

show global status like 'Com_______';

–

针对慢查询开启慢日志记录

# 开启慢查询日志
show_query_log=1

# 设置耗时多久进行记录
long_query_tume=2  # 超过2s进行记录

–

show profiles

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

# 查看是否支持
select @@have_profiling; 

# 查看是否开启
select @@profiling;

# 开启 show profile
set profiling = 1;

–

SOL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

use index: 使用指定索引（建议）

explain select * from tb_user use index(idx_user_pro) where profession='软件工程”

ignore index: 忽略指定索引

explain select * from tb_user ignore index(idx_user_pro) where profession='软件工程'

force index: 必须使用该索引

explain select * from tb_user force index(idx_user_pro) where profession='软件工程”

–

如何分析慢查询？

聚合查询——新增一个临时表去解决 、多表查询——优化sql语句结构 、表数据量过大——添加索引

深度分页查询——覆盖索引+子查询

使用Explain 对sql语句进行分析

• type sql来连接类型 Null(不使用表)、system(系统表)、const(主键)、eq_ref(唯一索引)、ref(普通索引)、range(使用了索引但是需要范围查询)、index(遍历索引树)、all(全盘扫描)
• possible_keys 可能会使用到的索引
• key 当前sql命中的索引
• key_len 索引占用的大小 （key、key_len 判断索引是否失效)
• Extra 额外的优化意见 Using where; Using Index 使用了索引，不需要回表查询 ； Using index condition 使用了索引，但是回表查询了，需要优化

–

SQL优化

• 表的设计优化（阿里开发手册《嵩山版》）
  • 选择合适的数值（tinyint 、int、 bigint）
  • 字符串类型根据情况选择 char、varchar
• 索引优化、索引的创建原则
• SQL语句的优化
  • select指定查询的字段，避免使用select * ，造成回表查询
  • sql语法避免索引失效，避免对where 子句的字段进行表达式操作导致索引失效
  • 尽量使用union all 代替 union，union会多一次过滤，效率低
  • 尽量使用内连接 inner join，如果必须使用left join、right join，必须以小表为驱动（放外边），内连接会默认优化
• 主从同步、读写分离，避免写操作影响读操作
• 分库分表

order by优化

多字段排序,一个升序一个降序，此时需要注意联合索引在创建时的规则，再额外创建一个索引(ASC/DESC)。
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_bufer_size(默认256K)。

update优化

InnoDB的行锁是针对索引加锁，不是针对记录加的锁，如果在更新时没有使用索引/索引失效，行锁会升级到表锁

–

MySQL超大分页优化？

select * from user order by id limit 900000，10；

当数据量过大时，如果使用limit进行分页，越往后效率越慢

可以使用 覆盖索引+ 子查询的方式优化, 创建覆盖索引 能够比较好的提高性能

select * from user ，

		（select id from user order by id limit 900000,10) a    #select id  和  order by id 都是走的覆盖索引

where user.id  = a.id ; 

–

分库分表

• 垂直分库 以表为依据，根据业务将不同的表拆分到不同的库中，如用户信息–用户数据库、订单信息–订单数据库

  • 按照业务对数据分级管理、维护、监控、扩展
  • 在高并发下，提高磁盘IO和数据量连接数

• 垂直分表 以字段为依据，根据字段的属性，将不同的字段拆分到不同的表中（在项目中主要是把一些商品的基本信息和详细信息进行垂直分表，一对一关系，使冷热数据分离）

  • 冷热数据分离
  • 减少IO过渡争抢，两表互不影响

​ 拆分规则：把不常用的字段单独放到一张表中，把text（大文本）、blob（二进制）等大字段拆分出来放到附件表中

• 水平分库 将一个库的数据拆分到多个数据库中（根据路由规则进行操作）（mycat）
  • 解决了单库数据量过大，高并发的性能瓶颈问题
  • 提高了系统的稳定性和可用性

​ 路由规则： 根据id节点取模、根据id范围进行路由等

• 水平分表 表可以在一个数据库中 （mycat）