锐客网

  1. 首页 > 生活百科 > it技术 > >

大厂mysql怎么调优 mysql sql 调优工具

mysqlIT技术

mysql 一次插入几万条数据应该怎么做优化关于mysql处理百万级以上的数据时如何提高其查询速度的方法
最近一段时间由于工作需要，开始关注针对Mysql数据库的select查询语句的相关优化方法 。
由于在参与的实际项目中发现当mysql表的数据量达到百万级时，普通SQL查询效率呈直线下降，而且如果where中的查询条件较多时，其查询速度简直无法容忍 。曾经测试对一个包含400多万条记录（有索引）的表执行一条条件查询，其查询时间竟然高达40几秒，相信这么高的查询延时，任何用户都会抓狂 。因此如何提高sql语句查询效率 ， 显得十分重要 。以下是网上流传比较广泛的30种SQL查询语句优化方法：
1、应尽量避免在 where 子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描 。
2、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引 。
3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描 ， 如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值 ， 然后这样查询：
select id from t where num=0
4、尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
5、下面的查询也将导致全表扫描：(不能前置百分号)
select id from t where name like ‘%c%’
若要提高效率，可以考虑全文检索 。
6、in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值 ， 能用 between 就不要用 in 了：
select id from t where num between 1 and 3
7、如果在 where 子句中使用参数，也会导致全表扫描 。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择 。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项 。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num
8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描 。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2
9、应尽量避免在where子句中对字段进行函数操作 ， 这将导致引擎放弃使用索引而进行全表扫描 。如：
select id from t where substring(name,1,3)=’abc’–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id
应改为:
select id from t where name like ‘abc%’
select id from t where createdate=’2005-11-30′ and createdate’2005-12-1′
10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引 。
11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致 。
12、不要写一些没有意义的查询 ， 如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集 ， 但是会消耗系统资源的 ， 应改成这样：
create table #t(…)
13、很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
14、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用 。
15、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定 。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要 。
16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源 。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引 。
17、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销 。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了 。
18、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些 。
19、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段 。
20、尽量使用表变量来代替临时表 。如果表变量包含大量数据，请注意索引非常有限（只有主键索引） 。
21、避免频繁创建和删除临时表，以减少系统表资源的消耗 。
22、临时表并不是不可使用，适当地使用它们可以使某些例程更有效 ， 例如 ， 当需要重复引用大型表或常用表中的某个数据集时 。但是 ， 对于一次性事件，最好使 用导出表 。
23、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table ， 避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert 。
24、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table，这样可以避免系统表的较长时间锁定 。
25、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写 。
26、使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效 。
27、与临时表一样，游标并不是不可使用 。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时 。在结果集中包括“合计”的例程通常要比使用游标执行的速度快 。如果开发时 间允许 ， 基于游标的方法和基于集的方法都可以尝试一下 ， 看哪一种方法的效果更好 。
28、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息 。
29、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理 。
30、尽量避免大事务操作，提高系统并发能力 。
mysql 存储过程执行太慢怎么优化1.当我们请求mysql服务器的时候,MySQL前端会有一个监听,请求到了之后,服务器得到相关的SQL语句,执行之前(虚线部分为执行),还会做权限的判断
2.通过权限之后,SQL就到MySQL内部,他会在查询缓存中,看该SQL有没有执行过,如果有查询过,则把缓存结果返回,说明在MySQL内部,也有一个查询缓存.但是这个查询缓存,默认是不开启的,这个查询缓存,和我们的Hibernate ， Mybatis的查询缓存是一样的,因为查询缓存要求SQL和参数都要一样,所以这个命中率是非常低的（没什么卵用的意思） 。
3.如果我们没有开启查询缓存,或者缓存中没有找到对应的结果,那么就到了解析器,解析器主要对SQL语法进行解析
4.解析结束后就变成一颗解析树,这个解析树其实在Hibernate里面也是有的,大家回忆一下,在以前做过Hibernate项目的时候,是不是有个一个antlr.jar 。这个就是专门做语法解析的工具.因为在Hibernate里面有HQL，它就是通过这个工具转换成SQL的,我们编程语言之所以有很多规范、语法,其实就是为了便于这个解析器解析,这个学过编译原理的应该知道.
5.得到解析树之后,不能马上执行,这还需要对这棵树进行预处理,也就是说,这棵树,我没有经过任何优化的树,预处理器会这这棵树进行一些预处理,比如常量放在什么地方,如果有计算的东西,把计算的结果算出来等等...
6.预处理完毕之后,此时得到一棵比较规范的树,这棵树就是要拿去马上做执行的树,比起之前的那棵树,这棵得到了一些优化
7.查询优化器，是MySQL里面最关键的东西,我们写任何一条SQL,比如SELECT * FROM USER WHERE USERNAME = toby AND PASSWORD = 1,它会怎么去执行?它是先执行username = toby还是password = 1?每一条SQL的执行顺序查询优化器就是根据MySQL对数据统计表的一些信息,比如索引,比如表一共有多少数据,MySQL都是有缓存起来的,在真正执行SQL之前,他会根据自己的这些数据,进行一个综合的判定,判断这一次在多种执行方式里面,到底选哪一种执行方式,可能运行的最快.这一步是MySQL性能中,最关键的核心点,也是我们的优化原则.我们平时所讲的优化SQL,其实说白了,就是想让查询优化器,按照我们的想法,帮我们选择最优的执行方案,因为我们比MySQL更懂我们的数据.MySQL看数据,仅仅只是自己收集到的信息,这些信息可能是不准确的,MySQL根据这些信息选了一个它自认为最优的方案,但是这个方案可能和我们想象的不一样.
8.这里的查询执行计划,也就是MySQL查询中的执行计划,比如要先执行username = toby还是password = 1
9.这个执行计划会传给查询执行引擎,执行引擎选择存储引擎来执行这一份传过来的计划,到磁盘中的文件中去查询,这个时候重点来了,影响这个查询性能最根本的原因是什么?就是硬盘的机械运动,也就是我们平时熟悉的IO,所以一条查询语句是快还是慢,就是根据这个时间的IO来确定的.那怎么执行IO又是什么来确定的?就是传过来的这一份执行计划.(优化就是制定一个我们认为最快的执行方案,最节省IO,和执行最快)
10.如果开了查询缓存,则返回结果给客户端,并且查询缓存也放一份 。
mysql调优技巧 增加线程缓存大小增加线程缓存大小
连接管理器线程处理服务器监听的网络接口上的客户端连接请求 。连接管理器线程将每个客户端连接与专用于它的线程关联，该线程负责处理该连接的身份验证和所有请求处理 。因此，线程和当前连接的客户端之间是一对一的比例 。确保线程缓存足够大以容纳所有传入请求是非常重要的 。
MySQL提供了许多与连接线程相关的服务器变量：
线程缓存大小由thread_cache_size系统变量决定 。默认值为0（无缓存），这将导致为每个新连接设置一个线程，并在连接终止时需要处理该线程 。如果希望服务器每秒接收数百个连接请求，那么应该将thread_cache_size设置的足够高，以便大多数新连接可以使用缓存线程 。可以在服务器启动或运行时设置max_connections的值 。
还应该监视缓存中的线程数（Threads_cached）以及创建了多少个线程，因为无法从缓存中获取线程（Threads_created） 。关于后者，如果Threads_created继续以每分钟多于几个线程的增加，请考虑增加thread_cache_size的值 。
使用MySQL show status命令显示MySQL的变量和状态信息 。这里有几个例子：
Monyog线程缓存监测
Monyog提供了一个监控线程缓存的屏幕 ， 名为“线程” 。与MySQL线程相关的服务器变量映射到以下Monyog指标：
Monyog线程屏幕还包括“线程缓存命中率”指标 。这是一个提示线程缓存命中率的指标 。如果值较低，则应该考虑增加线程缓存 。在状态栏以百分比形式显示该值；它的值越接近100％越好 。
如果这些指标的值等于或超过指定值 ， 则可以将每一个指标配置为发出警告和/或严重警报
mysql 参数调优（11）之innodb_buffer_pool_instances设置多个缓冲池实例MySQL 5.5引入了缓冲实例作为减小内部锁争用来提高MySQL吞吐量大厂mysql怎么调优的手段 。在5.5版本这个对提升吞吐量帮助很小大厂mysql怎么调优，然后在MySQL 5.6版本这个提升就非常大了大厂mysql怎么调优，所以在MySQL5.5中你可能会保守地设置innodb_buffer_pool_instances=4，在MySQL 5.6和5.7中你可以设置为8-16个缓冲池实例 。设置后观察会觉得性能提高不大，但在大多数高负载情况下，它应该会有不错的表现 。对了，不要指望这个设置能减少你单个查询的响应时间 。这个是在高并发负载的服务器上才看得出区别 。比如多个线程同时做许多事情 。
5.7、8.0 下INNODB_BUFFER_POOL_INSTANCES默认为1，若mysql存在高并发和高负载访问 ， 设置为1则会造成大量线程对BUFFER_POOL的单实例互斥锁竞争，这样会消耗一定量的性能的 。
pool_instances 可以设置为cpu核心数，它的作用是：
1）对于缓冲池在数千兆字节范围内的系统，通过减少争用不同线程对缓存页面进行读写的争用，将缓冲池划分为多个单独的实例可以提高并发性 。可以类比为 java中的 ThreadLocal 线程本地变量就是为每个线程维护一个buffer pool实例，这样就不用去争用同一个实例了 。相当于减少高并发下mysql对INNODB_BUFFER缓冲池的争用 。
2）使用散列函数将存储在缓冲池中或从缓冲池读取的每个页面随机分配给其中一个缓冲池实例 。每个缓冲池管理自己的空闲列表，刷新列表，LRU和连接到缓冲池的所有其他数据结构，并受其自己的缓冲池互斥量保护 。
mysql 参数调优(2)之设置重做日志文件的大小 innodb_log_file_size 我们知道redo log包括 buffer和log file的部分，这里的innodb_log_file_size是配置log file的大小的 。
innodb_log_file_size这个选项是设置 redo 日志（重做日志）的大小 。这个值的默认为5M，是远远不够的，在安装完mysql时需要尽快的修改这个值 。如果对 Innodb 数据表有大量的写入操作，那么选择合适的 innodb_log_file_size 值对提升MySQL性能很重要 。然而设置太大了，就会增加恢复的时间，因此在MySQL崩溃或者突然断电等情况会令MySQL服务器花很长时间来恢复 。
由于事务日志相当于一个写缓冲，而小日志文件会很快的被写满 ， 这时候就需要频繁地刷新到硬盘，速度就慢了 。如果产生大量的写操作，MySQL可能就不能足够快地刷新数据，那么写性能将会降低 。
大的日志文件，另一方面，在刷新操作发生之前给你足够的空间来使用 。反过来允许InnoDB填充更多的页面 。对于崩溃恢复 – 大的重做日志意味着在服务器启动前更多的数据需要读取 ， 更多的更改需要重做，这就是为什么崩溃恢复慢了 。
如果不配的后果：默认是5M ， 这是肯定不够的 。
最后，让我们来谈谈如何找出重做日志的正确大小 。
幸运的是 ， 你不需要费力算出正确的大小，这里有一个经验法则：在服务器繁忙期间 ， 检查重做日志的总大小是否够写入1-2小时 。你如何知道InnoDB写入多少，使用下面方法可以统计60秒内地增量数据大?。?
mysql show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 4631632062
...
Log sequence number 4803805448
mysql select (4803805448-4631632062) 60/1024/1024;
--------------------------------------
| (4803805448-4631632062) 60/1024/1024 |
--------------------------------------
|9851.84017181 |
--------------------------------------
1 row in set (0.00 sec)
在这个60s的采样情况下，InnoDB每小时写入9.8GB数据 。所以如果innodb_log_files_in_group没有更改(默认是2 ， 是InnoDB重复日志的最小数字)，然后设置innodb_log_file_size为10G，那么你实际上两个日志文件加起来有20GB，够你写两小时数据了 。
更改innodb_log_file_size的难易程度和能设置多大取决于你现在使用的MySQL版本 。特别地，如果你使用的是5.6之前的版本，你不能仅仅的更改变量，期望服务器会自动重启 。
好了，下面是步骤：
【大厂mysql怎么调优 mysql sql 调优工具】1、在my.cnf更改innodb_log_file_size
2、停止mysql服务器
3、删除旧的日志,通过执行命令rm -f /var/lib/mysql/ib_logfile*
4、启动mysql服务器 – 应该需要比之前长点的时间 ， 因为需要创建新的事务日志 。最后，需要注意的是，有些mysql版本(比如5.6.2)限制了重做日志大小为4GB 。所以在你设置innodb_log_file_size为2G或者更多时，请先检查一下MySQL的版本这方面的限制 。
关于大厂mysql怎么调优和mysql sql 调优工具的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站 。

    推荐阅读


    上一篇：redis 主节点 redis主从节点的区别

    下一篇：如何创建一个地球服务器？ 怎么建立地球服务器