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

MySQL7-性能优化

2024-10-01 14:17:01   219人阅读

一、建表
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-性能优化


联系
我们