MySQL7-性能优化

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL7-性能优化，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9226字，纯文字阅读大概需要14分钟。

内容图文

网上说使用强制索引可以改善查询速度，经过试验并不能。 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-性能优化

标签：需要   表头   定时   服务端   curd   let   ogr   大量   lan   

本文系统来源：http://www.cnblogs.com/kismetv/p/6947680.html

内容总结

以上是互联网集市为您收集整理的MySQL7-性能优化全部内容，希望文章能够帮你解决MySQL7-性能优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---