MySQL优化

一：SQL性能分析

SQL执行频率

对于MySQL的优化，主要是对其查询语句进行优化。我们可以先使用以下指令查看一下 SQL 的执行频率，可以查看到当前数据库的 insert、update、delete、select的访问次数。

show global status like 'com_______'；   #(7个下划线)

结果：

mysql> show global status like 'com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_insert    | 0     |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 11    |
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+
10 rows in set (0.00 sec)

每使用一条语句 value 值都会加1。

慢查询日志

慢查询日志是记录了所有执行时间超过指定参数(long_query_time,单位：秒，默认为10秒)的所有SQL语句的日志。当语句执行时间超过了指定时间，会被认定为为慢查询。MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置。
查看开启情况

show variables like 'slow_query_log';

执行结果

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)

开启慢查询日志
在文件 my.cnf 中配置

slow_query_log=1  #开启 MySQL 慢日志查询
long_query_time=2  #设置慢日志时间为2秒

profile详情

使用show profiles 指令能在做SQL优化时帮助我们了解时间都耗费到哪里去了。
查看MySQL是否支持profile操作

select @@have_profiling;

执行结果

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set (0.01 sec)

profiles默认是关闭的，开启指令：

set profiling=1;

执行一系列的业务SQL的操作，通过以下指令查看指令的执行耗时。

show profiles;  #查看每一条SQL的耗时基本情况

show profile for query query_id; #查看指定 query_id 的SQL语句各个阶段的耗时情况

show profile cpu for query query_id; #查看指定query_id的SQL语句CPU的使用情况

explain执行计划

使用 explain 指令可以查看SQL的执行计划

explain select * from tb_user;

image
首先需要了解一下各个字段的含义：

  • id：
    select查询的序列号，表示查询中执行select字句或者是操作表的顺序(id相同，执行顺序从上到下，id不同，值越大，越先执行)。因为可能是多表查询，所以会造成 id 相同。
  • select_type：
    表示查询的类型，分别有 simple(简单表，即不使用表连接或子查询)，primary(主查询，即外层的查询)，union(union中的第二个或者后面的查询语句)，subquery(select/where之后包含了子查询)。
  • type：
    表示索引类型，对type优化的前提是有索引。性能由好到差得到连接类型为：NULL>system>const>eq_ref>ref>range>index>all
    NULL：除非不涉及表查询，否则不会出现
    system：查询系统表的时候
    const：访问主键或者唯一索引
    eq_ref：唯一性索引，返回匹配唯一行数据，常用于唯一索引和主键索引
    ref：使用非唯一性的索引访问，对于每个索引键的查询，返回匹配的所有行
    range：检索指定范围的行
    index：使用了索引，但是扫描整个索引树
    all：全表扫面
  • prossible_key：
    显示可能应用在这张表上的索引，一个或多个，NULL则为没有用索引。
  • key：
    实际使用的索引，如果为NULL，则没有使用索引
  • Key_len：
    表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际长度，在不损失精确性的前提下，长度越短越好。
  • ref：
    指明当前表所参照的字段，必须两个都有索引才会指明，否则为null或者const
  • rows：
    MySQL认为必须要执行查询的行数，在InnoDB引擎表中，是一个估计值，可能并不总是准确的。
  • extra：
    1.Using filesort：性能消耗大，需要额外的一次排序(查询)，常见于 order by。通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer中完成排序操作，对于单索引，如果排序和查找不是同一个字段时出现
    2.Using index：性能提示：索引覆盖。不读取原文件，只从索引文件中获取数据，不需要回表查询。通过有序索引顺序扫描直接返回有序数据，这种情况即为Using index，不需要额外的排序，操作效率高。

二：优化

对主要对以下常见的语句进行优化分析。

  • 插入优化
  • 主键优化
  • order by优化
  • group by优化
  • count 优化
  • limit 优化
  • update 优化

插入优化

通常我们执行插入语句都是一行一行执行进行，由于每条语句都要与数据库进行连接进行网络传输，效率是比较低的。

insert into tb_user(id,name) value(1,'zhangsan');
insert into tb_user(id,name) value(2,'lisi');
insert into tb_user(id,name) value(3,'wangwu');

优化：
1.建议批量插入，最好数量在500-1000条之间

insert into tb_user(id,name) value(1,'zhangsan'),(2,'lisi'),(3,'wangwu');

2.手动提交事务 start transaction//开启事务 commit//提交

start transaction;
insert into tb_user(id,name) value(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
insert into tb_user(id,name) value(4,'zhangsan'),(5,'lisi'),(6,'wangwu');
insert into tb_user(id,name) value(7,'zhangsan'),(8,'lisi'),(9,'wangwu');
commit;

3.主键顺序插入

主键乱序插入：8 1 9 21 88 2 4 15 ...
主键顺序插入：1 2 4 8 9 15 21 88 ...

一次性插入大批量数据时，使用insert语句插入性能较低，可以使用MySQL数据库提供的 load 指令进行插入。

#客户端连接服务器时，加上参数 --local-infile
mysql --local-infile -u root -p;

#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile=1;

#执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql/log' into table 'tb_user' fileds terminated by ',' lines terminated by '\n';

主键优化

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。

  • 页分裂
    当插入数据时，会发生页分裂，InnoDB中使用页来存储数据，页也可以为空，也可以填充一半，也可以全部填充。每个页包含了2-N行数据，根据主键排列。
  • 页合并
    当删除数据时，实际上记录并没有被物理删除，只是记录被标记为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到(默认为页的50%)，InnoDB会开始寻找最靠近的页看看是否可以将两个页合并以优化空间使用。
  • 优化操作：
    1.在满足业务需求的情况下，尽量降低主键的长度
    2.插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
    3.尽量不要使用UUID做主键或者是其他自然主键，如身份证号
    4.业务操作时，避免对主键的修改

order by优化

  • 优化操作：
    1.根据排序字段建立合适的索引多字段排序时，也遵循最左前缀法则。
    2.尽量使用覆盖索引。
    3.多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(asc.desc)。
    4.如不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认为256K）。
#创建索引
create index idx_user_age_phone_aa on tb_user(age,phone);

#创建索引后，根据age，phone进行升序排序
explain select id,age,phone from tb_user order by age,phone;

#创建索引后，根据age，phone进行降序排序
explain select id,age,phone from tb_user order by age desc,phone desc;

group by 优化

  • 优化操作
    1.分组操作时，可以通过索引来提高效率。
    2.分组操作时，索引的使用也是满足最左前缀法则的。

limit 优化

当数据量非常大的时候，一个常见的问题是 limit 10000000,10，此时需要MySQL排序前10000010条记录，仅仅返回10000000-10000010的记录，其他记录丢弃，查询排序代价非常大。

  • 优化操作
    1.一般分页查询时，通过创建覆盖索引能够比较好的提高性能，可以通过覆盖索引加子查询形式进行优化。
explain select * from tb_user t,(select id from tb_user order by id limit 10000000,10) a where t.id = a.id;

count 优化

count()是一个聚合函数，对于返回的结果集，会一行行的判断，如果count函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值。
count的几种用法：
1.count(主键)
InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加。
2.count(字段)
没有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接进行累加。
3.count(1)
InnoDB引擎遍历整张表，但不取值，服务层对于返回的每一行，放一个数字 ”1“ 进去，直接按行进行累加。
4.count(*)
InnoDB引擎并不会把全部字段取出来，而是做了专门的优化，不取值，服务层直接按行进行累加。
按效率排序：count(字段) < count(主键id) < count(1) < count()，所以尽量使用count()

update 优化

InnoDB引擎的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引元素不能失效，否则会从行锁升级为表锁

#id 默认使用了主键索引
update tb_user set name = 'lisi' where id = 1;

update tb_user set name = 'zhangsan' where id = 'lisi';
posted @ 2022-03-17 01:26  爱慕6  阅读(54)  评论(0)    收藏  举报