读书笔记:MySQL Troubleshooting–Basics

最近正在看《MySQL Troubleshooting》这本书,对于做MySQL数据库运维的人来说,这是一本很值得看的书。因为是英文的,看的比较慢,如果没找到这本书的电子版,我可以提供(下载)。虽然目前只阅读了一部分,还是不得不感慨作者在MySQL方面丰富的知识和工作经验,更为作者的对待问题的认真与执着的精神锁折服。

第一章的标题是Basics,内容主要讲MySQL使用过程中出现的一些基本问题。这些基本问题覆盖了很多方面,包括错误的语法、错误的查询结果集、SQL慢查询与优化、表结构与索引分析、MySQL服务器操作系统参数设置问题、MySQL配置参数、InnoDB表与MyISAM表损坏问题、还有比较令人头疼的mysql bugs。第一章讲的基本点很多,很多东西也没有很详细的阐述,如果深入这些问题可以把每个点都可以写成一本书。我也没有打算把这些方面也一一俱全的写到,因为很多方面我也很少涉及。就拿SQL优化来说,我真正去剖析它的次数总共加起来也不会超过十次。虽然下面的内容是一种读书笔记的形式,但是也会加入很多我在工作中处理问题的方法与经验。

Troubleshooting,不用说,你一定知道症状symptoms,要不然躺在床上睡懒觉多舒服,是吧^.^。发现了问题和症状,那就可以开始诊断了diagnosiing。有时候我们经常很难发现问题所在,这需要我们有丰富的知识和经验,也需要灵活发散的思维,这些都需要日积月累。如果找到问题所在,处理问题是相对比较简单的部分。MySQL Troubleshooting这本书中经常小看到的一个词是tuning,“调弦,微调,定音”,我感觉这个词太生动了,以至于我在中文里面找不出这么形象的词翻译它,我一般的翻译为“优化,调优”。MySQL很多时候表现的出的性能不是很好,这时候需要我们去tuning,如慢查询、索引与表结构、系统参数与mysql的my.cnf参数等等。下面主要阐述SQL查询语句出错时的解决方案,对于长年使用sql的人来说,也许不值一提。

SQL(structured query language),既然是一种编程开发语言,当然存在的问题也主要是语法错误和逻辑错误。目前还没有比较好的sql调试环境,所以我有时候比较讨厌写存储过程和触发器代码。在写存储过程的时候,一般的只有在上一步结果正确后,才去写下一步的代码。这样可以防止绝大多数的逻辑错误,毕竟我们都比较清楚中间执行结果。

错误语法Incorrect Syntax
Symptoms:语法错误导致SQL执行不成功时的错误提示很明显,因而也很容易发现错误原因所在。如关键字拼写错误时提示“ERROR 1064 (42000), You have an error in your SQL syntax; ”。注意:错误代码:1064,SQL状态值:42000。表名拼写错误,“ERROR 1146 (42S02): Table ‘gcore.GC_Device_User’ doesn’t exist”。
错误语法主要一下几方面:

1)关键字拼写错误,甚至缺少关键字。

2)单引号,双引号,括号不匹配。一般的字符串值使用单引号包围起来,字段值中出现单引号时,注意使用转义符。这个错误大多出现在应用程序中拼接SQL字符串的时候,可以使用开发工具或输出函数码查看改SQL的内容。存在多个条件查询时,最好使用“()”把每个条件包围起来,之间的条件关系也清晰观察。特别是同时存在多个or和and操作的时候,可以很容易检验逻辑错误问题。

3)表名或者字段名的拼写错误。另外,表名和字段名不能与关键字(保留字)相同引起SQL不能执行成功,这个有时候很难发现问题,特别是一些关键字不常用。这个问题一般的出现在创建表的时候会提示创建不成功,除非表名或者字段名使用”“”标识。
create table t4(long int, name varchar(50));long是mysql中的关键字,提示错误“ERROR 1064 (42000): You have an error in your SQL syntax;

mysql> create table t4(`long` int, name varchar(50));执行成功。
查询的时候,也需要使用”“”标识。

4)多表连接查询到的时候,连接的表有相同的字段,且出现在查询结果中,导致SQL解析的时候出现二义性。

mysql> show create table t3;
+——-+——————————————–+
| Table | Create Table |
+——-+——————————————–+
| t3 | CREATE TABLE `t3` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+——-+——————————————–+
1 row in set (0.00 sec)

mysql> show create table t4;
+——-+——————————————–+
| Table | Create Table |
+——-+——————————————–+
| t4 | CREATE TABLE `t4` (
`long` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+——-+——————————————–+
1 row in set (0.00 sec)

如下面的查询结果中的name出现了sql解析二义性。使用表名限定name列来自于那个表即可。
mysql> select name from t3 inner join t4 where t3.id=t4.`long`;
ERROR 1052 (23000): Column ‘name’ in field list is ambiguous

mysql> select t4.name from t3 inner join t4 where t3.id=t4.`long`;  可以执行成功
当查询存在表连接的时,字段最好使用表名限定,即上面的table_name.column_name这种形式。

错误的执行结果(Wrong Results)
当SQL不再有语法错误之后,我们需要验证执行结果是否是正确的,是否与预期的一致。如果不一致,按照下面的步骤操作,可以很快的解决问题。
1)检验查询数据表中的数据,是否与你记忆中的一致,也许有其它开发人员改变了数据集。

2)检验查询条件逻辑,如关系大小比较、OR与AND操作是否符合逻辑,表之间的连接字段等。

3)如果where后面存在多个条件,那么分解查询条件,使用单个条件的select语句检验查询结果集。如果分解的条件查询结果正确,逐渐按照对应的关系合并查询,检验结果,发现问题。

4)如果存在多个子查询或者join操作,先提取出最里面的子查询,或者减少join查询表的个数,逐步验证结果。也可以使用explain extened来查看mysql的执行计划,rows和filtered值可以分别反应该查询的执行过程中将会检查(examine)多少行数据和将会有rows*filtered/100行数据和前一个表进行join操作。另外还要注意连接查询的类型和索引使用的状况。

5)如果是update或者delete操作,一些更新操作的sql可以转换为select操作,这样不必每次去还原数据。验证查询条件逻辑正确之后,改写成update或者delete操作。

此条目发表在mysql分类目录,贴了标签。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

This site uses Akismet to reduce spam. Learn how your comment data is processed.