Mysql 常见问题

---

MySQL连接时快时慢的问题解决

my.ini里的那个tmp_table_size的值.原来tmp_table_size=16M.我改为32M
然后重启MYSQL还是没解决问题.
只好重启服务器试试了,结果重启后CPU正常了.不过还是没找出是什么原因导致MYSQL占用那么多CPU资源.
=====================================
修改hosts文件，加入一行
172.16.59.12 abc
修改成功以后ping abc应该有返回
以后直接在程序中用abc连接数据库，不要用172.16.59.12
=====================================
my.cnf里修改配置，禁用DNS反向解析：
[mysqld]
skip-name-resolve
skip-grant-tables
补充--skip-name-resolve这个选项的作用：
mysql客户端每次访问db，mysql就会试图去解析来访问的机器的hostname，并缓存到hostname cache，如果这时解析不了，等一段时间会失败，数据才能被取过来。
若使用–skip-grant-tables系统将对任何用户的访问不做任何访问控制，但可以用 mysqladmin flush-privileges或mysqladmin reload来开启访问控制;默认情况是show databases语句对所有用户开放，
如果mysql服务器没有开远程帐户，就在my.ini里面加上skip-grant-tables
=====================================
在高并发高流量的情况下，基于数据库驱动的应用系统很容易出现瓶颈，这个瓶颈首先就是max_connections，即数据库的同时最大连接数.在MySQL安装的时候默认只有100个。增大这个连接数能马上起到效果。但是并不是能无限量增加，我在window服务器下和linux服务器下分别做了实验。
=====================================
修改my.cnf
1. 注释掉 # skip-networking
2. 给MYSQL绑定IP bind-address = 123.45.67.89 # server IP address
=====================================
问题的出现是这样的，我的一个web应用中有一个列表页面，遇到了n+1问题，就是打开这个页面，列表中有n条信息，就会产生n+1条sql。打开这个页面很慢，需要1分钟多。然而，这个问题只在测试环境出现，生产环境和本地开发环境均为出现此问题。
经过跟踪和分析，发现问题出在获取连接的时候，由于没有使用连接池，这n+1条sql，经常就会遇到获取连接慢的。如下为日志：
2009-11-23 13:41:20,679 INFO [Test] - -----------config------------
2009-11-23 13:41:20,695 INFO [Test] - url: jdbc:mysql://88.88.88.88:3306/xxxdatabase?useUnicode=true&characterEncoding=UTF-8
2009-11-23 13:41:20,695 INFO [Test] - BEGIN: select * from xxxtable where id = 1
2009-11-23 13:41:21,476 INFO [Test] - END: 765|16
2009-11-23 13:41:21,476 INFO [Test] - BEGIN: select * from xxxtable where id = 2
2009-11-23 13:41:21,523 INFO [Test] - END: 47|0
2009-11-23 13:41:21,523 INFO [Test] - BEGIN: select * from xxxtable where id = 3
2009-11-23 13:41:21,585 INFO [Test] - END: 62|0
2009-11-23 13:41:21,585 INFO [Test] - BEGIN: select * from xxxtable where id = 4
2009-11-23 13:41:21,617 INFO [Test] - END: 32|0
2009-11-23 13:41:21,788 INFO [Test] - END: 15|0
2009-11-23 13:41:21,788 INFO [Test] - BEGIN: select * from xxxtable where id = 12
2009-11-23 13:41:26,819 INFO [Test] - END: 5031 |0
2009-11-23 13:41:26,819 INFO [Test] - BEGIN: select * from xxxtable where id = 13
2009-11-23 13:41:26,850 INFO [Test] - END: 31|0
2009-11-23 13:41:27,038 INFO [Test] - END: 0|16
2009-11-23 13:41:27,038 INFO [Test] - BEGIN: select * from xxxtable where id = 19
2009-11-23 13:41:32,069 INFO [Test] - END: 5015|0
日志说明：BEGIN是要执行的sql，END中第一个数字是获取连接所消耗的时间，第二个数字是执行sql所消耗的时间。
请注意上面标红的sql，和别的不同，它们获取连接的时候特别的慢，平均需要5秒。这就是页面打开慢的原因。
通过百度和google，对此问题进行搜索，但是没有找到相关材料。
尝试一： 在本地的hosts中添加该ip地址的域名解析，用域名访问该数据库。
失败。
尝试二： 用show status和show processlist还有top等等查看服务器的运行状态是否存在异常。
未发现异常。
尝试三： 直接用localhost访问。
成功。
准备使用的尝试四： 修改 my.cnf
[mysqld]
skip-name-resolve #Don’t resolve hostnames
重启。
总结： 该问题，困扰了我3天，虽然终于解决了，但是没有找到根本原因。只是消除了症状，还希望明白人指点，谢谢非常。
至于一开始为什么不用localhost而用ip，是因为公司给我的那个用户只有用ip访问的权限。

---

解决一个 MySQL 服务器进程 CPU 占用 100%的技术笔记

MYSQL CPU 占用 100% 的现象描述
早上帮朋友一台服务器解决了 Mysql cpu 占用 100% 的问题。稍整理了一下，将经验记录在这篇文章里：《解决一个 MySQL 服务器进程 CPU 占用 100%的技术笔记》 http://www.xiaohui.com/weekly/20070307.htm
朋友主机(Windows 2003 + IIS + PHP + MYSQL )近来 MySQL 服务进程 (mysqld-nt.exe) CPU 占用率总为 100% 高居不下。此主机有10个左右的 database, 分别给十个网站调用。据朋友测试，导致 mysqld-nt.exe cpu 占用奇高的是网站A，一旦在 IIS 中将此网站停止服务，CPU 占用就降下来了。一启用，则马上上升。

MYSQL CPU 占用 100% 的解决过程
全文记录： http://www.xiaohui.com/weekly/20070307.htm
今天早上仔细检查了一下。目前此网站的七日平均日 IP 为2000，PageView 为 3万左右。网站A 用的 database 目前有39个表，记录数 60.1万条，占空间 45MB。按这个数据，MySQL 不可能占用这么高的资源。
于是在服务器上运行命令，将 mysql 当前的环境变量 输出到文件 output.txt：
d:/web/mysql> mysqld.exe --help >output.txt
发现 tmp_table_size 的值是默认的 32M，于是修改 My.ini, 将 tmp_table_size 赋值到 200M:
d:/web/mysql> notepad c:/windows/my.ini
[mysqld]
tmp_table_size=200M
然后重启 MySQL 服务。CPU 占用有轻微下降，以前的CPU 占用波形图是 100% 一根直线，现在则在 97%~100%之间起伏。这表明调整 tmp_table_size 参数对 MYSQL 性能提升有改善作用 。但问题还没有完全解决。
于是进入 mysql 的 shell 命令行，调用 show processlist , 查看当前 mysql 使用频繁的 sql 语句：
mysql> show processlist;
反复调用此命令，发现网站 A 的两个 SQL 语句经常在 process list 中出现，其语法如下：
SELECT t1.pid, t2.userid, t3.count, t1.date
FROM _mydata AS t1
LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid
LEFT JOIN _mydata_body AS t2 ON t1.pid=t3.pid
ORDER BY t1.pid
LIMIT 0,15
调用 show columns 检查这三个表的结构 :
mysql> show columns from _myuser;
mysql> show columns from _mydata;
mysql> show columns from _mydata_body;
终于发现了问题所在：_mydata 表，只根据 pid 建立了一个 primary key，但并没有为 userid 建立索引。而在这个 SQL 语句的第一个 LEFT JOIN ON 子句中：
LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid
_mydata 的 userid 被参与了条件比较运算。于是我为给 _mydata 表根据字段 userid 建立了一个索引：
mysql> ALTER TABLE `_mydata` ADD INDEX ( `userid` )
建立此索引之后，CPU 马上降到了 80% 左右。看到找到了问题所在，于是检查另一个反复出现在 show processlist 中的 sql 语句：
SELECT COUNT(*) FROM _mydata AS t1, _mydata_key AS t2  WHERE t1.pid=t2.pid and t2.keywords = '孔雀'
经检查 _mydata_key 表的结构，发现它只为 pid 建了了 primary key, 没有为 keywords 建立 index。_mydata_key 目前有 33 万条记录，在没有索引的情况下对33万条记录进行文本检索匹配，不耗费大量的 cpu 时间才怪。看来就是针对这个表的检索出问题了。于是同样为 _mydata_key 表根据字段 keywords 加上索引:
mysql> ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` )
建立此索引之后，CPU立刻降了下来，在 50%~70%之间震荡。
再次调用 show prosslist，网站A 的sql 调用就很少出现在结果列表中了。但发现此主机运行了几个 Discuz 的论坛程序， Discuz 论坛的好几个表也存在着这个问题。于是顺手一并解决，cpu占用再次降下来了。(2007.07.09 附注：关于 discuz 论坛的具体优化过程，我后来另写了一篇文章，详见：千万级记录的 Discuz! 论坛导致 MySQL CPU 100% 的 优化笔记 http://www.xiaohui.com/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm)

解决 MYSQL CPU 占用 100% 的经验总结
http://www.xiaohui.com/weekly/20070307.htm
增加 tmp_table_size 值。 mysql 的配置文件中，tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小，MySQL产生一个 The table tbl_name is full 形式的错误，如果你做很多高级 GROUP BY 查询，增加 tmp_table_size 值。
对 WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中的条件判断中用到的字段,应该根据其建立索引 INDEX。

索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000行，这比顺序读取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。
根据 mysql 的开发文档:
索引 index 用于：
   快速找出匹配一个WHERE子句的行
   当执行联结(JOIN)时，从其他表检索行。
   对特定的索引列找出MAX()或MIN()值
   如果排序或分组在一个可用键的最左面前缀上进行(例如，ORDER BY key_part_1,key_part_2)，排序或分组一个表。如果所有键值部分跟随DESC，键以倒序被读取。
   在一些情况中，一个查询能被优化来检索值，不用咨询数据文件。如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀，为了更快，值可以从索引树被检索出来。
      假定你发出下列SELECT语句：
      mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
      如果一个多列索引存在于col1和col2上，适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上，优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。