怎样分析mysql的性能并优化

第一优化你的sql和索引；

第二加缓存，memcached,redis；

第三以上都做了后，还是慢，就做主从复制或主主复制，读写分离，可以在应用层做，效率高，也可以用三方工具，第三方工具推荐360的atlas,其它的要么效率不高，要么没人维护；

第四如果以上都做了还是慢，不要想着去做切分，mysql自带分区表，先试试这个，对你的应用是透明的，无需更改代码,但是sql语句是需要针对分区表做优化的，sql条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，另外分区表还有一些坑，在这里就不多说了；

第五如果以上都做了，那就先做垂直拆分，其实就是根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；

第六才是水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key,为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

mysql数据库一般都是按照这个步骤去演化的，成本也是由低到高；

这个问题可深可浅，MySQL的写效率一直被人诟病。

简单的来说你可以用批量插入， insert into table (key1,key2) values(1,2),(1,2)

当然你可能已经用这种方法了，但结果还是慢，那就先查看你用的是MyISAM还是Innodb。

如果你使用的是innodb,修改innodb缓存innodb_buffer_pool_size一般是你内存的60%， innodb_flush_log_at_trx_commit = 0或2， 提高日志更新效率； innodb_thread_concurrency 根据你CPU的核数来调整。

到最后你可能发现这些东西都不能真正的提高insert的速度，这就是被人诟病的mysql写性能。 你智能用load data infile的方式，这是一种快速的终极方法，但代价是不能保证事物处理的原子性，而且不允许在存储过程中使用。

我以前在这个问题上挣扎了很久，最终的方法是修改业务策略，数据存放尽量精简，减少对Mysql的读写，这才是终极的解决之道。我曾经头痛的难题通过修改业务逻辑把更新速度从47分钟减少到002秒，只要结果一样就不用去折腾Mysql了

1）调整服务器的性能参数：key_buffer_size、Innodb_buffer_pool_size进行合理的配置

2）建立合适的索引

3）写查询语句用explain分析一下执行过程，核实一下执行计划，是否按照自己的意愿执行。

索引使要注意的地方：

1)索引不会包含有NULL值的列(使用索引的列设需要置默认值)2)使用短索引 3)不要在列上进行运算，即操作符号左端（使用函数）4) like语句操作5)不使用NOT IN和<>操作6)复合索引的建立7)选择自己使用的索引： USE INDEX  ， IGNORE INDEX  ， FORCE INDEX  8) where子句中已经使用了索引的话，那么order by中的列是不会使用索引的（使用复合索引解决）

表扫描要注意的地方：

1)数据表很小，全表扫描比做索引键的查找来得快。当表的记录总数小于10且比较短时通常这么做。

2)没有合适用于 ON 或 WHERE 分句的索引字段。

3)让索引字段和常量值比较，MySQL已经计算（基于索引树）到常量覆盖了数据表的很大部分。

4)通过其他字段使用了一个基数很小（很多记录匹配索引键值）的索引键。这种情况下，MySQL认为使用索引键需要大量查找，还不如全表扫描来得更快。

5)使用合适的索引可以解决表扫描

6) 使用Limit有时候也可以解决表扫描

优化的地方太多了，一一列举不完，你可以去这里看一下，这里面关于优化的知识有很多

http://wwwquzixicom/forum-2-2html，如果觉得说的有用就给个好评，写这么多怪不容易的，用了我一刻钟的时间呀

几方面：

硬件，软件，以及语言

硬件，是不是抗不住，

软件，mysql是不是没有设置好，数据库设计方面等，

语言，SQL语句写法。

下面是一些优化技巧。

1对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

3应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

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

5in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3

6下面的查询也将导致全表扫描：select id from t where name like '李%'若要提高效率，可以考虑全文检索。

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=1002

9应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)='abc' ，name以abc开头的id

应改为:

select id from t where name like 'abc%'

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=anum)

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尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

1存储引擎的选择如果数据表需要事务处理，应该考虑使用InnoDB，因为它完全符合ACID特性。如果不需要事务处理，使用默认存储引擎MyISAM是比较明智的。并且不要尝试同时使用这两个存储引擎。思考一下：在一个事务处理中，一些数据表使用InnoDB，而其余的使用MyISAM结果呢？整个subject将被取消，只有那些在事务处理中的被带回到原始状态，其余的被提交的数据转存，这将导致整个数据库的冲突。然而存在一个简单的方法可以同时利用两个存储引擎的优势。目前大多数MySQL套件中包括InnoDB、编译器和链表，但如果你选择MyISAM，你仍然可以单独下载InnoDB，并把它作为一个插件。很简单的方法，不是吗？

2计数问题如果数据表采用的存储引擎支持事务处理（如InnoDB），你就不应使用COUNT（）计算数据表中的行数。这是因为在产品类数据库使用COUNT（），最多返回一个近似值，因为在某个特定时间，总有一些事务处理正在运行。如果使用COUNT（）显然会产生bug，出现这种错误结果。

3反复测试查询查询最棘手的问题并不是无论怎样小心总会出现错误，并导致bug出现。恰恰相反，问题是在大多数情况下bug出现时，应用程序或数据库已经上线。的确不存在针对该问题切实可行的解决方法，除非将测试样本在应用程序或数据库上运行。任何数据库查询只有经过上千个记录的大量样本测试，才能被认可。

4避免全表扫描通常情况下，如果MySQL（或者其他关系数据库模型）需要在数据表中搜索或扫描任意特定记录时，就会用到全表扫描。此外，通常最简单的方法是使用索引表，以解决全表扫描引起的低效能问题。然而，正如我们在随后的问题中看到的，这存在错误部分。

5使用“EXPLAIN”进行查询当需要调试时，EXPLAIN是一个很好的命令，下面将对EXPLAIN进行深入探讨。

1：建索引根据索引查询 2：在后台做数据处理、是千万条数据总不能一次显示出来吧、可以根据一次显示或者用到多少条就查询多少条、不用一次性都查询出来、这样效率是很高的。3：如果有多表联合查询的话就用虚拟表查询、少用in等关键字、耗性能、4、后台代码尽量避免使用循环！

Linux上MySQL优化提升性能，可以优化关闭NUMA特性如下：

这些其实都源于CPU最新的技术：节能模式。操作系统和CPU硬件配合，系统不繁忙的时候，为了节约电能和降低温度，它会将CPU降频。

为了保证MySQL能够充分利用CPU的资源，建议设置CPU为最大性能模式。这个设置可以在BIOS和操作系统中设置，当然，在BIOS中设置该选项更好，更彻底。

然后我们看看内存方面，我们有哪些可以优化的。

i)

我们先看看numa

非一致存储访问结构

(NUMA

：

Non-Uniform

Memory

Access)

也是最新的内存管理技术。它和对称多处理器结构

(SMP

：

Symmetric

Multi-Processor)

是对应的。

我们可以直观的看到：SMP访问内存的都是代价都是一样的;但是在NUMA架构下，本地内存的访问和非

本地内存的访问代价是不一样的。对应的根据这个特性，操作系统上，我们可以设置进程的内存分配方式。目前支持的方式包括：

--interleave=nodes

--membind=nodes

--cpunodebind=nodes

--physcpubind=cpus

--localalloc

--preferred=node

简而言之，就是说，你可以指定内存在本地分配，在某几个CPU节点分配或者轮询分配。除非

是设置为--interleave=nodes轮询分配方式，即内存可以在任意NUMA节点上分配这种方式以外。其他的方式就算其他NUMA节点上还有内

存剩余，Linux也不会把剩余的内存分配给这个进程，而是采用SWAP的方式来获得内存。

所以最简单的方法，还是关闭掉这个特性。

关闭特性的方法，分别有：可以从BIOS，操作系统，启动进程时临时关闭这个特性。

a)

由于各种BIOS类型的区别，如何关闭NUMA千差万别，我们这里就不具体展示怎么设置了。

b)

在操作系统中关闭，可以直接在/etc/grubconf的kernel行最后添加numa=off，如下所示：

kernel

/vmlinuz-2632-220el6x86_64

ro

root=/dev/mapper/VolGroup-root

rd_NO_LUKSUTF-8

rd_LVM_LV=VolGroup/root

rd_NO_MD

quiet

SYSFONT=latarcyrheb-sun16

rhgb

crashkernel=auto

rd_LVM_LV=VolGroup/swap

rhgb

crashkernel=auto

quiet

KEYBOARDTYPE=pc

KEYTABLE=us

rd_NO_DM

numa=off

另外可以设置

vmzone_reclaim_mode=0尽量回收内存。

c)

启动MySQL的时候，关闭NUMA特性：

numactl

--interleave=all

mysqld

当然，最好的方式是在BIOS中关闭。

ii)

我们再看看vmswappiness。

vmswappiness是操作系统控制物理内存交换出去的策略。它允许的值是一个百分比的值，最小为0，最大运行100，该值默认为60。vmswappiness设置为0表示尽量少swap，100表示尽量将inactive的内存页交换出去。

具体的说：当内存基本用满的时候，系统会根据这个参数来判断是把内存中很少用到的inactive

内存交换出去，还是释放数据的cache。