MYSQL优化技巧
SQL语句慢查询优化思路
-
SQL语句查询慢原因:
-
一种是访问高峰期,查询请求非常多,导致mysql服务器压力很大,导致本来不慢的查询变慢(排队等待SQL执行);
-
MYSQL缓存失效,MYSQL如果对某个表设置了缓存,但是该表频繁更新(shf_goods),那么就会导致缓存失效:
- MySQL缓存机制简单的说就是缓存sql文本及查询结果,如果运行相同的SQL,服务器直接从缓存中取到结果,而不需要再去解析和执行SQL。如果表更改了,那么使用这个表的所有缓存查询将不再有效,查询缓存中值相关条目被清空。这里的更改指的是表中任何数据或是结构发生改变,包括INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE等,也包括那些映射到改变了的表使用MERGE表的查询。显然,这对于频繁更新的表,查询缓存是不适合的,而对于一些不常改变数据且有大量相同SQL查询的表,查询缓存会节约很大的性能。
-
sql语句设计的不好,没有命中索引,或者表结构本身有问题,导致sql很难优化
-
-
查看MYSQL服务器运行的状态值
- 如果是因为系统并发请求数很高导致查询速度变慢,那么就要重点关注当前MYSQL服务器的查询次数“Quries”,线程连接数"Threads_connected"和线程运行数"Threads_running",可以用show status的命令查看,如果当前线程连接数和运行数都很高,要考虑加大MYSQL服务器的连接数,如果连接数已达到单机MYSQL服务器的上限,就要考虑建立MYSQL集群,设置一主多从,将请求压力分摊到多个从库;
-
获取需要优化的SQL语句
-
开启慢查询日志:
-
在配置文件 my.cnf 中的 [mysqld] 一行下边添加两个参数:
-
slow_query_log = 1
-
slow_query_log_file=/var/lib/mysql/slow-query.log
-
long_query_time = 2
-
log_queries_not_using_indexes = 1
-
其中,slow_query_log = 1 表示开启慢查询;slow_query_log_file 表示慢查询日志存放的位置;
long_query_time = 2 表示查询 >=2 秒才记录日志;log_queries_not_using_indexes = 1 记录没有使用索引的 SQL 语句。 -
修改保存文件后,重启 MySQL 服务。在 /var/lib/mysql/ 目录下会创建 slow-query.log 日志文件。连接 MySQL 服务端执行如下命令可以查看配置情况。
-
show variables like ‘slow_query%’;
-
show variables like ‘long_query_time’;
-
打开慢查询日志文件,可以看到慢查询时间大于设定的long_query_time的查询语句都会记录下来:
- select sleep(2);
- [root@localhost mysql]# vim /var/lib/mysql/slow-query.log
-
虽然在慢查询日志中记录查询慢的 SQL 信息,但是日志记录的内容密集且不易查阅。因此,我们需要通过工具将 SQL 筛选出来。
-
MySQL 提供 mysqldumpslow 工具对日志进行分析。我们可以使用 mysqldumpslow --help 查看命令相关用法。
-
-s:排序方式,后边接着如下参数 c:访问次数 l:锁定时间 r:返回记录 t:查询时间 al:平均锁定时间 ar:平均返回记录书 at:平均查询时间 -t:返回前面多少条的数据 -g:翻遍搭配一个正则表达式,大小写不敏感
-
-
-
分析慢查询SQL语句
-
方式一: explain
-
筛选出有问题的 SQL,我们可以使用 MySQL 提供的 explain 查看 SQL 执行计划情况(关联表,表查询顺序、索引使用情况等)。
-
explain select * from category;
-
mysql> explain select * from category;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE category NULL ALL NULL NULL NULL NULL 1 100.00 NULL 1 row in set, 1 warning (0.00 sec)
-
字段解释:
-
id:select 查询序列号。id相同,执行顺序由上至下;id不同,id值越大优先级越高,越先被执行
-
select_type:查询数据的操作类型,其值如下:
- simple:简单查询,不包含子查询或 union
- primary:包含复杂的子查询,最外层查询标记为该值
- subquery:在 select 或 where 包含子查询,被标记为该值
- derived:在 from 列表中包含的子查询被标记为该值,MySQL 会递归执行这些子查询,把结果放在临时表
- union:若第二个 select 出现在 union 之后,则被标记为该值。若 union 包含在 from 的子查询中,外层 select 被标记为 derived
- union result:从 union 表获取结果的 select
-
table:显示该行数据是关于哪张表
-
partitions:匹配的分区
-
type:表的连接类型,其值,性能由高到底排列如下:
- system:表只有一行记录,相当于系统表
- const:通过索引一次就找到,只匹配一行数据
- eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常用于主键或唯一索引扫描
- ref:非唯一性索引扫描,返回匹配某个单独值的所有行。用于=、< 或 > 操作符带索引的列
- range:只检索给定范围的行,使用一个索引来选择行。一般使用between、>、<情况
- index:只遍历索引树
- ALL:全表扫描,性能最差
注:前5种情况都是理想情况的索引使用情况。通常优化至少到range级别,最好能优化到 ref
-
possible_keys:指出 MySQL 使用哪个索引在该表找到行记录。如果该值为 NULL,说明没有使用索引,可以建立索引提高性能
-
key:显示 MySQL 实际使用的索引。如果为 NULL,则没有使用索引查询
-
key_len:表示索引中使用的字节数,通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好
显示的是索引字段的最大长度,并非实际使用长度 -
ref:显示该表的索引字段关联了哪张表的哪个字段
-
rows:根据表统计信息及选用情况,大致估算出找到所需的记录或所需读取的行数,数值越小越好
-
filtered:返回结果的行数占读取行数的百分比,值越大越好
-
extra: 包含不合适在其他列中显示但十分重要的额外信息,常见的值如下:
-
using filesort:说明 MySQL 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。出现该值,应该优化 SQL
-
using temporary:使用了临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by。出现该值,应该优化 SQL
-
using index:表示相应的 select 操作使用了覆盖索引,避免了访问表的数据行,效率不错
-
using where:where 子句用于限制哪一行
-
using join buffer:使用连接缓存
-
distinct:发现第一个匹配后,停止为当前的行组合搜索更多的行
注意:出现前 2 个值,SQL 语句必须要优化。
-
-
-
方式二:profiling
-
使用profiling命令可以了解SQL语句消耗资源的详细信息(每个执行步骤的开销)
-
查看profile开启情况: select @@profiling, 0表示关闭,1表示开启;
-
启用profile: set profiling = 1;
-
查看执行的SQL列表
-
show profiles;
mysql> show profiles; +----------+------------+------------------------------+ | Query_ID | Duration | Query | +----------+------------+------------------------------+ | 1 | 0.00062925 | select @@profiling | | 2 | 0.00094150 | show tables | | 3 | 0.00119125 | show databases | | 4 | 0.00029750 | SELECT DATABASE() | | 5 | 0.00025975 | show databases | | 6 | 0.00023050 | show tables | | 7 | 0.00042000 | show tables | | 8 | 0.00260675 | desc role | | 9 | 0.00074900 | select name,is_key from role | +----------+------------+------------------------------+ 9 rows in set, 1 warning (0.00 sec)
-
该命令执行之前,需要执行其他 SQL 语句才有记录。
-
-
查询指定ID的执行详细信息
- show profile for query Query_ID;
mysql> show profile for query 9; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000207 | | checking permissions | 0.000010 | | Opening tables | 0.000042 | | init | 0.000050 | | System lock | 0.000012 | | optimizing | 0.000003 | | statistics | 0.000011 | | preparing | 0.000011 | | executing | 0.000002 | | Sending data | 0.000362 | | end | 0.000006 | | query end | 0.000006 | | closing tables | 0.000006 | | freeing items | 0.000011 | | cleaning up | 0.000013 | +----------------------+----------+ 15 rows in set, 1 warning (0.00 sec)
-
SQL执行过程中,要重点关注执行时间(executing)和传输数据(sending_data),executing反映sql本身查询的快慢, Sending Data,是从硬盘读取数据,如果这个占比太大,建议通过索引或加上LIMIT,减少需要扫描并且发送给客户端的数据量
-
获取 CPU、 Block IO 等信息
`show profile block io,cpu for query Query_ID;
show profile cpu,block io,memory,swaps,context switches,source for query Query_ID;
show profile all for query Query_ID;`
-
-
-
sql语句优化手段
-
查询优化
- 避免select *,需要什么数据,查询对应字段,根据SQL的profile可以知道,传输数据是占sql执行计划很大一部分的,如果一次查询中只需要几个字段,尽量指名这几个字段进行查询,减少不必要的mysql扫描硬盘时间;
- 小表驱动大表,小的数据集驱动大的数据集。 如:以 A,B 两表为例,两表通过 id 字段进行关联。
- 当 B 表的数据集小于 A 表时,用 in 优化 exist;使用 in ,两表执行顺序是先查 B 表,再查 A 表 select * from A where id in (select id from B)
- 当 A 表的数据集小于 B 表时,用 exist 优化 in;使用 exists,两表执行顺序是先查 A 表,再查 B 表 select * from A where exists (select 1 from B where B.id = A.id)
- 当 B 表的数据集小于 A 表时,用 in 优化 exist;使用 in ,两表执行顺序是先查 B 表,再查 A 表 select * from A where id in (select id from B)
- 一些情况下,可以使用连接代替子查询,因为使用 join,MySQL 不会在内存中创建临时表。
- 适当添加冗余字段,减少表关联,譬如,现在shu_user_profiles表可以加上总发货数,总接单数等统计性字段,减少SELECT COUNT(1)的计算,尽量以静态数据取代动态计算;
- 合理使用索引(下文介绍)。如:为排序、分组字段建立索引,避免 filesort 的出现。
- 以主键id进行查询优化。如果要查询全表获取id,譬如,信用统计任务中,需要查询出全量用户id,不应该写SELECT id FROM shu_users,应该分成两步:第一步,查出shu_users的最大id,max_uid; 第二步,根据max_uid,不断循环,每次查询一万的uid,SELECT id FROM shu_users WHERE is_deleted = 0 AND id >= :start_id AND id < :end_id;
- 在写业务的同时,进行统计。举个例子,需求:每次给司机用户推送货源,需要记录每次推送的信息,也要获取总共推送多少次;那么可以设计两张表,一张表是记录全量信息的,每推送一次,记录一条;另外一张表以司机用户id作为唯一键,记录推送多少次的统计信息;开启mysql的事务锁,每推送一次,同时对两张表进行写入,在写入业务的同时,完成统计任务,到时候查询的时候,就可以直接查询静态数据push_count;
-
索引建立
-
适合建立索引的场景
- 主键自动创建唯一索引
- 频繁作为查询条件的字段,如果多个字段需要高频查询,建议将多个字段联合起来建立索引;
- 查询中与其他表关联的字段
- 查询中排序的字段
- 查询中统计或分组字段
-
不适合建立索引的场景
- 频繁更新的字段,频繁更新的字段,索引会失效,索引的字段最好是很少修改的
- where 条件中用不到的字段
- 表记录太少
- 经常增删改的表
- 字段的值的差异性不大或重复性高
-
索引创建和使用原则
- 单表查询:哪个列作查询条件,就在该列创建索引
- 多表查询:left join 时,索引添加到右表关联字段;right join 时,索引添加到左表关联字段
- 不要对索引列进行任何操作(计算、函数、类型转换)
- 索引列中不要使用 !=,<> 非等于
- 索引列不要为空,且不要使用 is null 或 is not null 判断
- 索引字段是字符串类型,查询条件的值要加’'单引号,避免底层类型自动转换,举个例子: shu_users表的mobile是字符串类型,如果是这条SQL: SELECT id FROM shu_users WHERE mobile = 15917907641,那么执行该sql的时候就不会命中mobile的索引,从而转变为全表扫描,性能很差,所以要改成mobile = ‘15917907641’
- 违背上述原则可能会导致索引失效,具体情况需要使用 explain 命令进行查看
-
索引失效情况
-
除了违背索引创建和使用原则外,如下情况也会导致索引失效:
-
模糊查询时,以 % 开头
-
使用 or 时,如:字段1(非索引)or 字段2(索引)会导致索引失效。
-
使用复合索引时,不使用第一个索引列。index(a,b,c) ,以字段 a,b,c 作为复合索引为例:
` | 语句 | 索引是否生效 | | --------------------------------------- | --------------------------- | | where a = 1 | 是,字段 a 索引生效 | | where a = 1 and b = 2 | 是,字段 a 和 b 索引生效 | | where a = 1 and b = 2 and c = 3 | 是,全部生效 | | where b = 2 或 where c = 3 | 否 | | where a = 1 and c = 3 | 字段 a 生效,字段 c 失效 | | where a = 1 and b > 2 and c = 3 | 字段 a,b 生效,字段 c 失效 | | where a = 1 and b like 'xxx%' and c = 3 | 字段 a,b 生效,字段 c 失效 | `
-
-
-
写入优化
-
上述是查询优化的一些技巧,但是写入也是要考虑优化的,很早之前自己没深入理解过python的pymysql的api,插入语句都是for循环执行的,性能很差,后来深入去看pymysql的源码,发现底层代码会对列表字典的结构的参数进行insert语句优化,将insert语句变成批量执行:
- insert tb () values(); -> insert tb () values(), (),…, ();
-
写入优化的原则就是:一次sql执行,尽可能的批量将数据插入或者更新;
-
insert可以批量执行,那么update能不能批量执行?答案是可以的:
-
mysql更新语句很简单,更新一条数据的某个字段,一般这样写: UPDATE mytable SET myfield = ‘value’ WHERE other_field = ‘other_value’;
-
如果要一次性更新很多条,我以前也是for循环执行,但是这样python让mysql执行的io耗时是和循环次数线性相关的,也很容易造成阻塞;
-
如果要一条sql语句搞点几万的更新,可以利用case when来实现:
要点: 利用唯一key对应的字段进行更新,通常是主键id
UPDATE categories SET display_order = CASE id WHEN 1 THEN 3 WHEN 2 THEN 4 WHEN 3 THEN 5 END, title = CASE id WHEN 1 THEN 'New Title 1' WHEN 2 THEN 'New Title 2' WHEN 3 THEN 'New Title 3' END WHERE id IN (1,2,3)
-
但是上述update的sql语句在批量更新很多字段很多行的时候,性能也会很低,因为同时要执行好几万的判断语句,扫描很多行数据,那么有没有更好的办法?有但是不一定会让人满意。
-
replace into table (field1, field2) values (:value1, :value2),遇到含有重复字段的记录,先删后增; 假设field1是有唯一索引的字段,那么就会删掉原来的记录,并更新field1和field2,但是如果表中有field3或field4或更多字段,那么这些没被更新到的会直接被mysql设为默认值,数据就丢失了,这可能就不是我们想要的,所以replace into 做批量更新,还要将之前field3或更多的字段先查出来再添加进replace语句:
replace into table (field1, field2, field3) values (:value1, :value2, :before_field3) -
insert into …on duplicate key update field1=value1, field2=value2, 遇到重复记录,只更新update语句后的,这个语句执行之前,会先检查之前有没有唯一索引的记录,如果没有,就是普通的insert语句,如果有就update指定的字段,该语句只适合新增或更新单条记录,没办法做批量操作;
-
创建临时表,先更新临时表,然后从临时表中update, 更新表的时候通过临时表和要更的表主键id关联,然后update语句执行批量更新:
create temporary table tmp(id int(4) primary key,dr varchar(50)); insert into tmp values (0,'gone'), (1,'xx'),...(m,'yy'); update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;
-
update批量,replace into批量更新 和创建临时表的方法各有缺陷:
- 虽然update批量语句比较直观,但是性能可能不太好;
- replace into是先删后增,没在语句指定的字段会被设成默认值,并且replace into批量执行,表的id主键自增非常快,如果批量更新的数据量很大,有可能主键id会溢出设定的精度范围int(11);
- 创建临时表,需要当前用户有创建表的权限;
- 这三种批量更新的方式,replace into性能最高,创建临时表的方法次之,然后是update + case when;
-
-
-
-
数据库表结构设计
-
初期的表设计,非常重要,设计的好,后面的查询语句好写性能又高:
- 使用可以存下数据最小的数据类型
- 使用简单的数据类型。int 要比 varchar 类型在mysql处理简单
- 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int,设计表字段的时候,要考虑字段占用的空间大小,如果只是一些状态字段,基本都是tinyint(4)就可以解决,就不要用int,减少表的大小空间;
- 尽可能使用 not null 定义字段,因为 null 占用4字节空间,有默认值的字段在建立索引的时候才能生效;
- 尽量少用 text 类型,非用不可时最好考虑分表
- 尽量使用 timestamp 而非 datetime
- 单表不要有太多字段,建议在 20 以内,现在shf_goods的字段远超20,非常畸形,现在线上新加字段(数据量极大),都会锁表差不多1小时,而这个表也是高频查询,高频插入和高频更新的,不可能锁表一小时,而用户无法正常使用我们的服务,所以现在都是在shf_goods_extras增加新字段,通过goods_id进行关联;
-
表的拆分
- 当数据库中的数据非常大时,查询优化方案也不能解决查询速度慢的问题时,我们可以考虑拆分表,让每张表的数据量变小,从而提高查询效率。
- 垂直拆分:将表中多个列分开放到不同的表中。例如用户表中一些字段经常被访问,将这些字段放在一张表中,另外一些不常用的字段放在另一张表中。插入数据时,使用事务确保两张表的数据一致性。
- 水平拆分:按照行进行拆分。例如用户表中,使用用户ID,对用户ID取10的余数,将用户数据均匀的分配到0~9的10个用户表中。查找时也按照这个规则查询数据,但是这样会导致很多服务的sql语句的修改,一般推荐使用中间件解决,这些数据库中间件,会动态地将select id from shu_users_01 where mobile = "xxx"转换成select id from shu_users where mobile = “xxx”,也就是保持原来的sql语句不变,这样会减少很多因为水平拆分表而带来的很多修改sql语句的表名的操作;
-
读写分离
- 一般情况下对数据库而言都是“读多写少”。换言之,数据库的压力多数是因为大量的读取数据的操作造成的。我们可以采用数据库集群的方案,使用一个库作为主库,负责写入数据;其他库为从库,负责读取数据。这样可以缓解对数据库的访问压力。
-