首页 > 代码库 > MySQL7-性能优化

MySQL7-性能优化

一、建表
1、id:每个表要设置一个无意义的id,最好是int(unsigned),比varchar性能好很多;设置成自增更好。外键情况例外。
 
2、字段类型
(1)尽量不用null,尤其是用作比较的列(where/distinct/……)。但是需要主要的是,有时候将列设置成not null,使用ORM进行插入时,必须保证每个字段非null,否则会异常;在数据库中指定默认值是没有用的。
(2)越简单、越小的数据类型越好。如果可以,用整型、enum、日期类型代替字符串(enum内部实现是tinyint),用定长字符串代替变长字符串。
(3)IP地址使用unsigned int,而不要使用字符串;unsigned int占空间小,定长,而且便于比较;使用inet_aton()和inet_ntoa()转化方便。
(4)固定长度的表:如果表中没有varchar、blob和text字段,则表是固定长度的,操作会很快。

 

3、使用索引:见《key与index》,注意事项如下。
(1)不仅可以优化where,还可以优化distinct、join on、order by、group by等
(2)索引字段类型应满足上述要求。
(3)Btree索引左匹配,Hash索引整值匹配且只能等值比较(=/in等)。
(4)索引也不是越多越好,索引过多会导致insert/update效率低。
 
4、使用分区/分表,见《分区与分表》
 
5、垂直分割:将表按照列分成几个。
(1)例如,一个表中有很多字段,其中有一些相对独立,且不怎么使用;如users中的家庭住址信息,可以单列一个表。
(2)例如,用户表中有个last_login字段经常修改,其他的极少改,则可以把last_login分出来,这样就避免查询缓存频繁被清理。
(3)例如,一个表将不常用的varchar/blob/text分出来,可以变成固定长度的表。
(4)但是,如果分出来的表与原表要经常进行join操作,那么千万不要分割,得不偿失。
 
6、合理选取存储引擎,最常用的是以下两个
Innodb:支持事务处理;使用行锁,update等语句比MyIsam快;查询相对较慢;不支持全文本搜索
MyIsam:不支持事务处理;使用表锁,update等语句很慢;查询很快,适用于大量查询的应用;支持全文本搜索
 
 
 
二、操作数据(尤其是查询)
1、避免select *,明确指明字段。
2、对于索引字段,尽量不要使用计算表达式;因为会对索引进行全表扫描。
SELECT COUNT(*) FROM ta_info_cad_andr WHERE ctime > ‘2016-10-01‘ AND ctime < ‘2016-10-31‘;#7s
SELECT COUNT(*) FROM ta_info_cad_andr WHERE DAY(ctime) = ‘10‘;#34s
3、对于索引字段,尽量不要使用!=或<>比较符,MySQL会对索引表进行全表扫描。
4、如果明确知道查询结果数量,或者只需要返回指定数量,使用limit;这样当服务器找到指定数量后,便立即返回。最典型的例子是limit 1。
5、当使用join时,除了前述两个表中的join字段都要索引外,还要保证两个字段的类型相同,如果是字符串,还要保证字符串字符集相同。
 
6、善用查询缓存:大多数的MySQL服务器都开启了查询缓存,但是当查询语句中出现curdate()、now()等返回值易变的函数时,服务器不会开启查询缓存,此时可以使用变量代替。但是应该注意,使用参数,可能会影响索引的使用,从而影响查询速度。
SELECT * FROM ta_project WHERE DATE(create_date) = DATE(NOW())
//优化
SET @today = ‘2016-12-14‘;
SELECT * FROM ta_project WHERE DATE(create_date) = @today;
 
7、返回随机一行:千万不要使用order by rand(),即便跟了limit 1也不行;服务器会执行很多次rand(),而且还要记录并进行排序。
select username from user order by rand() limit 1;
//优化
select count(*) into @count from user;
set @rand = rand(0,@count - 1);
select username from user limit @rand,1;
 
8、where子句中,如果对索引值使用参数,会影响查询速度,具体原因不清楚;用explain分析,发现两种方式都使用了索引,且rows相同。
SELECT COUNT(*) FROM ta_info_cad_andr WHERE ctime > ‘2016-10-01‘ AND ctime < ‘2016-10-31‘;#7s
SET @startTime = ‘2016-10-01‘;
SET @endTime = ‘2016-10-31‘;
SELECT COUNT(*) FROM ta_info_cad_andr WHERE ctime > @startTime AND ctime < @endTime;#15s
网上说使用强制索引可以改善查询速度,经过试验并不能。
SELECT COUNT(*) FROM ta_info_cad_andr FORCE INDEX(ctime) WHERE ctime > @startTime AND ctime < @endTime;#15s
 
9、拆分大的delete/update语句:因为这两个操作会锁表,可能导致其他线程无法访问,甚至是MySQL服务器崩溃。
 
 
10、查询分区数据时,如果数据都在一个分区,可以显示的把分区语句加上。如下所示,语句1耗时7s,语句2耗时25s。
SELECT SQL_NO_CACHE COUNT(DISTINCT deviceid) FROM ta_info_cad_andr_test2 PARTITION(p201610);
SELECT SQL_NO_CACHE COUNT(DISTINCT deviceid) FROM ta_info_cad_andr_test2 WHERE ctime > ‘2016-10-01 00:00:00‘ AND ctime < ‘2016-11-01 00:00:00‘;
 
 
 
三、工具与技巧
1、explain
 
2、从procedure analyse()获取建议:当表中有实际数据(最好多一些)时,这些建议才更可能有效;建议是针对数据类型的;建议只是建议,决定还得自己根据实际情况做。
SELECT COUNT(DISTINCT deviceid) FROM ta_info_cad_ios WHERE ctime > ‘2016-11-01‘ PROCEDURE analyse();
SELECT DISTINCT deviceid FROM ta_info_cad_ios WHERE ctime > ‘2016-11-01‘ PROCEDURE analyse();
给出建议分别如下:
 
可见这些建议也不是很靠谱。
 
3、慢查询日志
 
4、定期进行analyze table 与 optimize table:由于二者都会对表加锁,谨慎使用。
 
5、其他:Prepared Statements、无缓冲查询
 
6、为了防止服务器缓存对查询效果评估的影响,查询语句中可以加入SQL_NO_QUERY。
SELECT SQL_NO_CACHE COUNT(DISTINCT deviceid) FROM ta_info_cad_andr_test2 WHERE ctime > ‘2016-10-01 00:00:00‘ AND ctime < ‘2016-11-01 00:00:00‘; PARTITION(p201610);
 
 
 
参考:http://www.cnblogs.com/daxian2012/articles/2767989.html
***************************************************************************************************************************
 
《文章-运维角度浅谈MySQL数据库优化》里面讲的很好
 
如果问数据库运行速度的怎么样?我想最直观最直接的方式,是查看慢查询日志,可以使用mysqldumpslow、pt-query-digest或者直接查看。
 
IO、网络、CPU、内存都可能是制约数据库速度的瓶颈,其中IO是瓶颈的时候较多。
 

查询优化、索引优化、库表结构优化,应该齐头并进,一个不落。【这些都是在数据库使用层面;硬件配置、数据库资源使用配置方面也是优化方向】

 
QPS、TPS:questions per second与transactions per second

***************************************************************************************************************************
查看慢(查询)日志的相关状态/参数
show variables like ‘slow%‘ 
slow_query_log:on/off
slow_query_log_file:慢日志文件名
slow_launch_time:注意,这个时间阈值是新建线程消耗时间的阈值,并不是慢查询时间阈值
show variables like ‘%long_query_time%‘
long_query_time:慢日志记录的时间阈值,单位秒;注意,这个时间阈值是查询过程消耗时间的阈值;默认是10s,一般2s居多,但是对于web应用,2s也太长,可以考虑1s。5.6的版本是可以将阈值精度设置到ms级别的。
注意,如果日志中只有以下行,则没有慢查询【以下行是慢查询的表头,每次重启服务器会增加一次(已验证)】
C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe, Version: 5.6.26-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument
【慢速日志的确提供了很多有用的信息,但是不代表出现的查询一定一直都是慢的。如果同样的查询在慢速日志里出现了多次,那么它的确需要优化,但是如果只是出现了偶尔一两次,则有可能是其他客观原因造成的,比如某些锁,I/O磁盘物理性问题,网络问题等等】
【查看慢日志主要有两种方式:1、直接打开文件查看2、使用mysqldumpslow,这是MySQL的一个脚本,后缀是.pl,需要安装pl才能调用】
 
***************************************************************************************************************************
当向MySQL发送一个请求的时候,MySQL到底做了什么?
1、客户端发送一条查询给服务器
2、服务器先检查查询缓存(Query Cache),如果命中缓存,则立即返回存储在缓存中的结果;否则进入下一个阶段
3、服务器生成执行计划,这个过程主要包括解析SQL、预处理和优化SQL执行计划
4、MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询
5、将结果返回给客户端
 
show (full) processlist:查看连接/线程的状态
可以显示数据库服务器的域名、数据库名称、用户名等基本信息,以及以下几个最重要的信息
time:从开始执行到现在执行了多少时间,单位是s
info:执行信息,一般代表执行了什么命令
command:当前状态
sleep:线程正等待客户端发送新的请求
query:线程正在执行查询或者正在将结果发送给客户端
locked:在MySQL服务器层,该线程正在等待表锁。在存储引擎级别实现的锁,如InnoDB的行锁,并不会体现在线程状态中。
analyzing and statistics:线程正在收集存储引擎的统计信息,并生成查询的执行计划。
coping to tmp table [on disk]:线程正在执行查询操作,并且将结果集都复制到一个临时表中,这种状态一般要么是在做group by操作,要么是文件排序操作,或者是union操作。如果有on disk,则正在将一个内存临时表放到磁盘上。
sorting result:线程正在对结果集进行排序
sending data:表示多种情况:线程可能在多个状态之间传送数据(?),或者在生成结果集,或者在向客户端返回数据
 
***************************************************************************************************************************
explain select的详解
id:sql语句的执行顺序,不重要
table:表名
possible_keys:可能用到哪些索引
key:用到了哪些索引
key_len:使用的索引长度
ref:显示使用哪个列或常数与key一起从表中选择行
rows:执行查询的行数,是查看性能的主要指标
extra:查询的详细信息,比较多而且可以出现不止一个,如using index:通过索引便可以找到,不需要扫描数据行
 
select_type:
simple:简单查询,没有union和子查询
primary:子查询的外层查询,或union的第一个查询
union:union查询中第二个或后面查询
dependent union:union查询中第二个或后面查询
union result:union查询的结果
……
 
type从最优到最差:前5个是比较优的方式
system:仅有一行,是const的特例
const:(1)primary key或unique key(2)全查询,即不能只是最左边(3)=,不能使<>(4)只能是一个表
eq_ref:const的(4)不满足,即两个表primary key/unique key相等
ref:const的(1)或(2)不能满足时,使用ref;因为无论是对于普通key,还是只检索最左边,都不能保证查询结果的唯一
ref_or_null:可以搜索包含null值的行
index_merge
unique_subquery
index_subquery
range:当有索引(B-tree可以,hash索引不行)时,使用<>between、in(in也要特别注意)等条件,range。如果没有索引,是all。
index:扫描全表-索引表,如使用了索引中非最左边的数据。
all:扫描全表-原表,如没有使用索引,或使用hash索引而条件不是=时(未验证)。
需要注意的两点:
(1)在查询时应该注意类型,如有一张表id为普通索引,类型为varchar,如果使用id=‘1000‘,则type为ref;如果使用id=1000,则type为all;切记切记!
(2)对于查询较为频繁的列,能用primary/unique就不用普通索引;能设置为not null,就不要允许null(因为允许null对于索引的性能影响较大)。
 
***************************************************************************************************************************
长连接和短连接:
什么是长连接?
其实长连接是相对于通常的短连接而说的,也就是长时间保持客户端与服务端的连接状态。
通常的短连接操作步骤是:
连接-》数据传输-》关闭连接;
而长连接通常就是:
连接-》数据传输-》保持连接-》数据传输-》保持连接-》…………-》关闭连接;
这就要求长连接在没有数据通信时,定时发送数据包,以维持连接状态,短连接在没有数据传输时直接关闭就行了
 
什么时候用长连接,短连接?
长连接主要用于在少数客户端与服务端的频繁通信,因为这时候如果用短连接频繁通信常会发生Socket出错,并且频繁创建Socket连接也是对资源的浪费。
但是对于服务端来说,长连接也会耗费一定的资源,需要专门的线程(unix下可以用进程管理)来负责维护连接状态。
总之,长连接和短连接的选择要视情况而定。
 
如何设置长连接和短连接?不太清楚
 
Hibernate的连接池中的连接都是长连接。
**************************************************************************************************************************
《MySQL必知必会》对于改善性能的一些建议
硬件方面
1、注意对硬件的要求;一般来说,关键的生产DBMS应该运行在专用服务器上。
2、MySQL是用一系列的默认设置预先设置的;过一段时间以后,可能需要调整内存分配、缓冲区大小等。使用show variables 和 show status 可以查看。
 
3、MySQL是多用户多线程的,经常执行多个任务;如果一个任务执行缓慢,会导致所有任务执行缓慢。如果遇到显著的性能不良,可以用show processlist显示所有活动进程,用kill命令终结某个进程。
 
4、总有不止一种方法编写同一条select语句,应该试验联结、并、子查询等,找出最佳方法。
5、使用explain语句查看如何执行一条select语句。
6、一般来说,存储过程比一条条执行MySQL语句快。
7、应该总是使用正确的数据类型。
8、绝不要检索比需求还要多的数据,慎用select *。
9、有的操作(包括insert)支持delayed关键字,如果使用它,就把控制立即返回给调用程序,并且一旦有可能就实际执行该操作。
10、在导入数据时,应该关闭自动提交。你可能还想删除索引(包括fulltext索引),然后在导入完成后再重建它们。
11、使用索引可以提升检索的性能。同时,会损害插入、删除和更新的性能,如果有一些表收集数据且不经常搜索,建议不使用索引。
12、如果select语句中有一系列复杂的or条件,建议使用多条select语句和连接它们的union语句,性能会大大改善。
13、like很慢,一般来说最好使用fulltext而不是like。
 
14、数据库是不断变化的实体;由于表的使用和内容的更改,理想的优化和配置也会改变。
15、最重要的规则是,每条规则在某些条件下都会被打败。
 
**********************************************************************************************************************************
实践经验
1、联合主键的弊端
有时候在一张表A中,两个字段c1和c2,可以唯一确定一条记录;原理上来说,可以使用c1和c2做A的联合主键,虽然比使用单独的主键减少冗余,但是可能会有下面的问题:
如果表A在其他表中被引用,那么需要同时使用c1和c2作为外键,不但冗余大,操作麻烦,而且如果做索引,对效率的影响就更大了。
 
 

MySQL7-性能优化