标签：mysql

经常会遇到一些连接数超标或者mysql链接出现异常（比如表被锁等）导致的报错，此时直接重启不仅影响线上的业务而且指标不治本。

我们处理方式一般是不重启服务的前提下处理异常的表列，同时排查是服务器问题、mysql参数配置问题还是程序、或表本身设计的问题。

比如这里连接数：

[root@21yunwei /]# mysql   -uroot  -p123456  -e  "use 21yunwei;show processlist;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+-----------+----------+---------+------+----------+------------------+
| Id | User | Host      | db       | Command | Time | State    | Info             |
+----+------+-----------+----------+---------+------+----------+------------------+
| 27 | root | localhost | 21yunwei | Query   |    0 | starting | show processlist |
+----+------+-----------+----------+---------+------+----------+------------------+

show processlist查看方式：

mysqladmin  -uroot -p123456  processlist;
mysql   -uroot  -p123456  -e  "show processlist;"

说明：

• 如果是以root用户登录，可以查看所有的进程信息。
• 如果是以普通用户登录，只可以查看自己对应的数据库的信息。

如下信息取于酷喃博客：

• 先简单说一下各列的含义和用途。
• id列，不用说了吧，一个标识，你要kill一个语句的时候很有用。
• user列，显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。
• host列，显示这个语句是从哪个ip的哪个端口上发出的。呵呵，可以用来追踪出问题语句的用户。
• db列，显示这个进程目前连接的是哪个数据库。
• command列，显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。
• time列，此这个状态持续的时间，单位是秒。
• state列，显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成。
• info列，显示具体执行的sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：

• Checking table
  正在检查数据表（这是自动的）。
  　
• Closing tables
  正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
  　
• Connect Out
  复制从服务器正在连接主服务器。
  　
• Copying to tmp table on disk
  由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
  　
• Creating tmp table
  正在创建临时表以存放部分查询结果。
  　
• deleting from main table
  服务器正在执行多表删除中的第一部分，刚删除第一个表。
  　
• deleting from reference tables
  服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
  　
• Flushing tables
  正在执行FLUSH TABLES，等待其他线程关闭数据表。
  　
• Killed
  发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
  　
• Locked
  被其他查询锁住了。
  　
• Sending data
  正在处理SELECT查询的记录，同时正在把结果发送给客户端。
  　
• Sorting for group
  正在为GROUP BY做排序。
  　
• Sorting for order
  正在为ORDER BY做排序。
  　
  Opening tables
  这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
  　
• Removing duplicates
  正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
  　
• Reopen table
  获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
  　
• Repair by sorting
  修复指令正在排序以创建索引。
  　
• Repair with keycache
  修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
  　
• Searching rows for update
  正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
  　
• Sleeping
  正在等待客户端发送新请求.
  　
• System lock
  正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加–skip-external-locking参数来禁止外部系统锁。
  　
• Upgrading lock
  INSERT DELAYED正在尝试取得一个锁表以插入新记录。
  　
• Updating
  正在搜索匹配的记录，并且修改它们。
  　
• User Lock
  正在等待GET_LOCK()。
  　
• Waiting for tables
  该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
  　
• waiting for handler insert
  INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。

大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。

参考资料：

mysql手册所有状态说明，链接如下：http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html

mysql show processlist详解 http://coolnull.com/1230.html

数据库事务有四种隔离级别：

• 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据。
• 提交读(Read Committed)：只能读取到已经提交的数据，Oracle等多数数据库默认都是该级别。
• 可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻读。
• 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

上面这样的教科书式定义第一次接触事务隔离概念的朋友看了可能会一脸懵逼，下面我们就通过具体的实例来解释四个隔离级别。

首先我们创建一个user表：

CREATE TABLE user (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE `uniq_name` USING BTREE (name)
) ENGINE=`InnoDB` AUTO_INCREMENT=10 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

读未提交隔离级别

我们先将事务的隔离级别设置为read committed：

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.00 sec)

在下面我们开了两个终端分别用来模拟事务一和事务二，p.s: 操作一和操作二的意思是按照时间顺序来执行的。

事务1

mysql> start transaction; # 操作1
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user(name) values('ziwenxie'); # 操作3
Query OK, 1 row affected (0.05 sec)

事务2

mysql> start transaction; # 操作2
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user; # 操作4
+----+----------+
| id | name     |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)

从上面的执行结果可以和清晰的看出来，在read uncommited级别下面我们在事务一中可能会读取到事务二中没有commit的数据，这就是脏读。

读提交隔离级别

通过设置隔离级别为committed可以解决上面的脏读问题。

mysql> set session transaction isolation level read committed;

事务一

mysql> start transaction; # 操作一
Query OK, 0 rows affected (0.00 sec)


mysql> select * from user; # 操作三
+----+----------+
| id | name     |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)

mysql> select * from user; # 操作五，操作四的修改并没有影响到事务一
+----+----------+
| id | name     |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)

mysql> select * from user; # 操作七

+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)

mysql> commit; # 操作八
Query OK, 0 rows affected (0.00 sec)

事务二

mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)

mysql> update user set name='lisi' where id=10; # 操作四
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit; # 操作六
Query OK, 0 rows affected (0.08 sec)

虽然脏读的问题解决了，但是注意在事务一的操作七中，事务二在操作六commit后会造成事务一在同一个transaction中两次读取到的数据不同，这就是不可重复读问题，使用第三个事务隔离级别repeatable read可以解决这个问题。

可重复读隔离级别

MySQL的Innodb存储引擎默认的事务隔离级别就是可重复读隔离级别，所以我们不用进行多余的设置。

事务一

mysql> start tansactoin; # 操作一

mysql> select * from user; # 操作五
+----+----------+
| id | name     |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)

mysql> commit; # 操作六
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user; # 操作七
+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)

事务二

mysql> start tansactoin; # 操作二

mysql> update user set name='lisi' where id=10; # 操作三
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit; # 操作四

在事务一的操作五中我们并没有读取到事务二在操作三中的update，只有在commit之后才能读到更新后的数据。

Innodb解决了幻读么

实际上RR级别是可能产生幻读，InnoDB引擎官方称中利用MVCC多版本并发控制解决了这个问题，下面我们验证一下Innodb真的解决了幻读了么？

为了方便展示，我修改了一下上面的user表：

mysql> alter table user add salary int(11);
Query OK, 0 rows affected (0.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> delete from user;
Query OK, 1 rows affected (0.07 sec)

mysql> insert into user(name, salary) value('ziwenxie', 88888888);
Query OK, 1 row affected (0.07 sec)

mysql> select * from user;
+----+----------+----------+
| id | name     | salary   |
+----+----------+----------+
| 10 | ziwenxie | 88888888 |
+----+----------+----------+
1 row in set (0.00 sec)

事务一

mysql> start transaction;  # 操作一
Query OK, 0 rows affected (0.00 sec)

mysql> update user set salary='4444'; # 操作六，竟然影响了两行，不是说解决了幻读么？
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from user; # 操作七， Innodb并没有完全解决幻读
+----+----------+--------+
| id | name     | salary |
+----+----------+--------+
| 10 | ziwenxie |   4444 |
| 11 | zhangsan |   4444 |
+----+----------+--------+
2 rows in set (0.00 sec)

mysql> commit; # 操作八
Query OK, 0 rows affected (0.04 sec)

事务二

mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user(name, salary) value('zhangsan', '666666'); # 操作四
Query OK, 1 row affected (0.00 sec)

mysql> commit; # 操作五
Query OK, 0 rows affected (0.04 sec)

从上面的例子可以看出，Innodb并没有如官方所说解决幻读，不过上面这样的场景中也不是很常见不用过多的担心。

串行化隔离级别

所有事务串行执行，最高隔离级别，不会出现幻读性能会很差，实际开发中很少使用到。

MySQL是一个强大的开源数据库。随着MySQL上的应用越来越多，MySQL逐渐遇到了瓶颈。这里提供 101 条优化 MySQL 的建议。有些技巧适合特定的安装环境，但是思路是相通的。我已经将它们分成了几类以帮助你理解。

MySQL监控MySQL服务器硬件和OS（操作系统）调优

1、有足够的物理内存，能将整个InnoDB文件加载到内存里 —— 如果访问的文件在内存里，而不是在磁盘上，InnoDB会快很多。

2、全力避免 Swap 操作 — 交换（swapping）是从磁盘读取数据，所以会很慢。

3、使用电池供电的RAM（Battery-Backed RAM）。

4、使用一个高级磁盘阵列 — 最好是 RAID10 或者更高。

5、避免使用RAID5 — 和校验需要确保完整性，开销很高。

6、将你的操作系统和数据分开，不仅仅是逻辑上要分开，物理上也要分开 — 操作系统的读写开销会影响数据库的性能。

7、将临时文件和复制日志与数据文件分开 — 后台的写操作影响数据库从磁盘文件的读写操作。

8、更多的磁盘空间等于更高的速度。

9、磁盘速度越快越好。

10、SAS优于SATA。

11、小磁盘的速度比大磁盘的更快，尤其是在 RAID 中。

12、使用电池供电的缓存 RAID（Battery-Backed Cache RAID）控制器。

13、避免使用软磁盘阵列。

14、考虑使用固态IO卡（不是磁盘）来作为数据分区 — 几乎对所有量级数据，这种卡能够支持 2 GBps 的写操作。

15、在 Linux 系统上，设置 swappiness 的值为0 — 没有理由在数据库服务器上缓存文件，这种方式在Web服务器或桌面应用中用的更多。

16、尽可能使用 noatime 和 nodirtime 来挂载文件系统 — 没有必要为每次访问来更新文件的修改时间。

17、使用 XFS 文件系统 — 一个比ext3更快的、更小的文件系统，拥有更多的日志选项，同时，MySQL在ext3上存在双缓冲区的问题。

18、优化你的 XFS 文件系统日志和缓冲区参数 – -为了获取最大的性能基准。

19、在Linux系统中，使用 NOOP 或 DEADLINE IO 调度器 — CFQ 和 ANTICIPATORY 调度器已经被证明比 NOOP 和 DEADLINE 慢。

20、使用 64 位操作系统 — 有更多的内存能用于寻址和 MySQL 使用。

21、将不用的包和后台程序从服务器上删除 — 减少资源占用。

22、将使用 MySQL 的 host 和 MySQL自身的 host 都配置在一个 host 文件中 — 这样没有 DNS 查找。

23、永远不要强制杀死一个MySQL进程 — 你将损坏数据库，并运行备份。

24、让你的服务器只服务于MySQL — 后台处理程序和其他服务会占用数据库的 CPU 时间。

MySQL 配置

25、使用 innodb_flush_method=O_DIRECT 来避免写的时候出现双缓冲区。

26、避免使用 O_DIRECT 和 EXT3 文件系统 — 这会把所有写入的东西序列化。

27、分配足够 innodb_buffer_pool_size ，来将整个InnoDB 文件加载到内存 — 减少从磁盘上读。

28、不要让 innodb_log_file_size 太大，这样能够更快，也有更多的磁盘空间 — 经常刷新有利降低发生故障时的恢复时间。

29、不要同时使用 innodb_thread_concurrency 和 thread_concurrency 变量 — 这两个值不能兼容。

30、为 max_connections 指定一个小的值 — 太多的连接将耗尽你的RAM，导致整个MySQL服务器被锁定。

31、保持 thread_cache 在一个相对较高的数值，大约是 16 — 防止打开连接时候速度下降。

32、使用 skip-name-resolve — 移除 DNS 查找。

33、如果你的查询重复率比较高，并且你的数据不是经常改变，请使用查询缓存 — 但是，在经常改变的数据上使用查询缓存会对性能有负面影响。

34、增加 temp_table_size — 防止磁盘写。

35、增加 max_heap_table_size — 防止磁盘写。

36、不要将 sort_buffer_size 的值设置的太高 — 可能导致连接很快耗尽所有内存。

37、监控 key_read_requests 和 key_reads，以便确定 key_buffer 的值 — key 的读需求应该比 key_reads 的值更高，否则使用 key_buffer 就没有效率了。

38、设置 innodb_flush_log_at_trx_commit = 0 可以提高性能，但是保持默认值（1）的话，能保证数据的完整性，也能保证复制不会滞后。

39、有一个测试环境，便于测试你的配置，可以经常重启，不会影响生产环境。

MySQL Schema 优化：

40、保证你的数据库的整洁性。

41、归档老数据 — 删除查询中检索或返回的多余的行

42、在数据上加上索引。

43、不要过度使用索引，评估你的查询。

44、压缩 text 和 blob 数据类型 — 为了节省空间，减少从磁盘读数据。

45、UTF 8 和 UTF16 比 latin1 慢。

46、有节制的使用触发器。

47、保持数据最小量的冗余 — 不要复制没必要的数据.

48、使用链接表，而不是扩展行。

49、注意你的数据类型，尽可能的使用最小的。

50、如果其他数据需要经常需要查询，而 blob/text 不需要，则将 blob/text 数据域其他数据分离。

51、经常检查和优化表。

52、经常做重写 InnoDB 表的优化。

53、有时，增加列时，先删除索引，之后在加上索引会更快。

54、为不同的需求选择不同的存储引擎。

55、日志表或审计表使用ARCHIVE存储引擎 — 写的效率更高。

56、将 session 数据存储在 memcache 中，而不是 MySQL 中 — memcache 可以设置自动过期，防止MySQL对临时数据高成本的读写操作。

57、如果字符串的长度是可变的，则使用VARCHAR代替CHAR — 节约空间，因为CHAR是固定长度，而VARCHAR不是（utf8 不受这个影响）。

58、逐步对 schema 做修改 — 一个小的变化将产生的巨大的影响。

59、在开发环境测试所有 schema 变动，而不是在生产环境的镜像上去做。

60、不要随意改变你的配置文件，这可能产生非常大的影响。

61、有时候，少量的配置会更好。

62、质疑使用通用的MySQL配置文件。

查询优化

63、使用慢查询日志，找出执行慢的查询。

64、使用 EXPLAIN 来决定查询功能是否合适。

65、经常测试你的查询，看是否需要做性能优化 — 性能可能会随着时间的变化而变化。

66、避免在整个表上使用count(*) ，它可能会将整个表锁住。

67、保持查询一致，这样后续类似的查询就能使用查询缓存了。

68、如果合适，用 GROUP BY 代替 DISTINCT。

69、在 WHERE、GROUP BY 和 ORDER BY 的列上加上索引。

70、保证索引简单，不要在同一列上加多个索引。

71、有时，MySQL 会选择错误的索引，这种情况使用 USE INDEX。

72、使用 SQL_MODE=STRICT 来检查问题。

73、索引字段少于5个时，UNION 操作用 LIMIT，而不是 OR。

74、使用 INSERT ON DUPLICATE KEY 或 INSERT IGNORE 来代替 UPDATE，避免 UPDATE 前需要先 SELECT。

75、使用索引字段和 ORDER BY 来代替 MAX。

76、避免使用 ORDER BY RAND()。

77、LIMIT M,N 在特定场景下会降低查询效率，有节制使用。

78、使用 UNION 来代替 WHERE 子句中的子查询。

79、对 UPDATE 来说，使用 SHARE MODE 来防止排他锁。

80、重启 MySQL 时，记得预热数据库，确保将数据加载到内存，提高查询效率。

81、使用 DROP TABLE ，然后再 CREATE TABLE ，而不是 DELETE FROM ，以删除表中所有数据。

82、最小化你要查询的数据，只获取你需要的数据，通常来说不要使用 *。

83、考虑持久连接，而不是多次建立连接，已减少资源的消耗。

84、基准查询，包括服务器的负载，有时一个简单的查询会影响其他的查询。

85、当服务器的负载增加时，使用SHOW PROCESSLIST来查看慢的/有问题的查询。

86、在存有生产环境数据副本的开发环境中，测试所有可疑的查询。

MySQL备份过程

87、在二级复制服务器上进行备份。

88、备份过程中停止数据的复制，以防止出现数据依赖和外键约束的不一致。

89、彻底停止MySQL之后，再从数据文件进行备份。

90、如果使用MySQL dump进行备份，请同时备份二进制日志 — 确保复制过程不被中断。

91、不要信任 LVM 快照的备份 — 可能会创建不一致的数据，将来会因此产生问题。

92、为每个表做一个备份，这样更容易实现单表的恢复 — 如果数据与其他表是相互独立的。

93、使用 mysqldump 时，指定 -opt 参数。

94、备份前检测和优化表。

95、临时禁用外键约束，来提高导入的速度。

96、临时禁用唯一性检查，来提高导入的速度。

97、每次备份完后，计算数据库/表数据和索引的大小，监控其增长。

98、使用定时任务（cron）脚本，来监控从库复制的错误和延迟。

99、定期备份数据。

100、定期测试备份的数据。

一、安装

这是同事分享给我的。安装数据库也是一门学问，千万不要被安装的简单性而忽略一些细节。针对于 Win os 服务器而言，MySQL 的安装版可以选则三种不同的服务器类型：

• Developer Machine(开发机器)
  为 MySQL 分配最少分系统资源

• Server Machine（服务器）
  为 MySQL 分配一定比例的系统资源

• Dedicated MySQL Server Machine（专用MySQL服务器）
  为 MySQL 分配所有的系统资源

如果你跟我一样不幸，不仅选择了 Win os 做服务器系统，还选择了 Developer Machine(开发机器)，兄弟抱一个，不要哭，重装。发生这些上述不幸的原因已经不重要，需要做的是必须切换成 Dedicated MySQL Server Machine（专用MySQL服务器）。

重装切换之后，你会发现，之前安装的一定是假的 MySQL。

二、主键

主键或者唯一键可以用作某条记录的唯一标志符。主键生成有两种方式：

• 自增
• UUID

1、自增

自增方式有个重要因素是“步长”，也就是则增的幅度，在单机模式下，一般步长为1。若是在分布式数据库系统下，步长设为节点的数量，这样一来，就可以避免主键重复的情况。建议预估好节点的数量，步长不可小于节点数。

2、UUID

UUID 可以更有效地避免自增主键带来的烦恼，但是它也有不足之处：

• UUID 过长，增大数据库总容量，降低性能
• UUID 无序，插入数据时根据主键寻址费时

针对上述 UUID 的缺点，推特开发了“雪花算法”，并开源。其中心思想是利用时间戳、数据中心码、机器码、序列号组成有规则的 UUID，使其有序降低性能消耗。

我在项目中使用了“自增+步长2”，因为使用了主从，虽然不是分布式，但是双数据源也是两个节点，采用这种方式保险一些。

更多内容请移步我的其他博文：数据库自增长主键与-UUID （http://zhoupq.com/%E6%95%B0%E6%8D%AE%E5%BA%93%E8%87%AA%E5%A2%9E%E9%95%BF%E4%B8%BB%E9%94%AE%E4%B8%8E-UUID/）
　　
推荐 MySQL 使用自增ID主键和UUID 作为主键的优劣比较详细过程（从百万到千万表记录测试） （http://blog.csdn.net/mchdba/article/details/52336203）

三、数据类型

1、长度

我一直秉承杀鸡就用杀鸡刀，宰牛采用宰牛刀的原则。一个“tab_NAME” 的数据类型非要整一个 “VARCHAR(500)”，这是浪费，过多的长度分配会造成空间占用太多，最终造成性能下降。有同事说我杞人忧天，一个库才二十几张表，即使每个字段都设成500，也不过如此嘛。从短期的结果上来看，结果没有受到明显影响。但是别忘了，我们是来解决问题的，如果因为我们的操作违反了约定，造成严重后果，那么我们将背负罪过。“量身定做”的好处不言而喻，列的长度亦是如此。

2、NULL

尽量不将列设为 NULL，从业务角度上看，NULL 是错误的，试想，既然是 NULL，哪有何必存在这个列呢？反之，既然存在这个列，那么 NULL 便失去了意义。让我设计表的时候，我都会给列设置一个初始值，“tab_UPDATETIME” 就设置为 “CURRENT_TIMESTAMP”，“tab_STATUS” 就设置为 “1”或者“0”。

从开发维护的角度看，如果不确定列是否为 NULL，那么在 SQL 中，就必须加上 “AND tab_NAME != NULL AND tab_NAME != ‘’”，很容易被忽略，代码越多，出错的概率就越大。

四、索引

好的索引是一颗仙丹，可以让迟缓的查询得到质的提升，否则，就是一碗毒药。

索引我做了三点优化：

• 勿滥用索引
• 最左前缀索引
• 前缀索引

1、勿滥用索引

索引不是越多越好，因为生成索引需要时间，而且索引占表物理空间。表一大，查询速度多少会受影响。我亲眼看到同事建好表时候，无微不至地为每一个字段都建了索引，或者为每一个条件都建了索引，形如条件“a=1 and b =2 and c=3 and d=4”，为其建了“a”、“b”、“c”、“d”四个索引。浪费！低效！这种情况，应当使用“最左前缀索引”。

2、最左前缀索引

在生成联合索引时会碰到最左前缀索引，什么是最左前缀索引呢？就是在联合索引中，从最左边的索引开始匹配，直到遇上“like”、“>”、“>=”、“<”、“<=”等范围匹配时停止，即使后面有“=”都不再匹配。

简单举例：现有字段“a”、“b”、“c”、“d”组成的联合索引“abcd”，SQL 条件部分为：

a=1 and b =2 and c=3 and d=4
a=1 and b =2 and d=4 and c=3
a=1 and b =2 and c>=3 and d=4

　　
1 用到索引为“abcd”，2 用到的索引为“abd”， （2 同 1）3 用到的索引为“abc”。条件的顺序很重要。跟自拍一样，脸大的站后面。
　　
抱歉，上述第二点同第一点，同样用到的索引为“abcd”。
　　
利用 EXPLAIN 工具分析：

// 建表语句略，已知创建了组合索引 (abcd) 
mysql> EXPLAIN SELECT * FROM test t WHERE t.a = 'q' AND t.b = 'w' AND t.c = 'e' AND t.d = 'f';
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref                     | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | name          | name | 360     | const,const,const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
1 row in set
mysql> EXPLAIN SELECT * FROM test t WHERE t.a = 'q' AND t.b = 'w' AND t.d = 'f' AND t.c = 'e';
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref                     | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | name          | name | 360     | const,const,const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
1 row in set

3、前缀索引

前缀索引是针对某个字段而言，我们知道 MySQL 中，有一个全文索引，“BOLD”、“TEXT” 是不能建全文索引。想想也能理解，这么长的全文索引得占多少空间，这显然是不现实的。最好的办法是为其创建“左前缀索引”，只取字符串的前面一小段作为索引，具体取多少，决定于多长的字符可以尽可能多的确定唯一记录。“varchar”同样受用。

更多内容请移步我的其他博文：MySQL 高性能索引之前缀索引 （http://zhoupq.com/MySQL-%E9%AB%98%E6%80%A7%E8%83%BD%E7%B4%A2%E5%BC%95%E4%B9%8B%E5%89%8D%E7%BC%80%E7%B4%A2%E5%BC%95/）

五、多表联合查询

不管是 Heibernate 在代码中拼 SQL，还是 MyBatis 在 Mapper.xml 中写 SQL，由于数据库范式的规范，导致为完成某项查询，必须联合多表查询。一个 LEFT JOIN 很常见，三四个 LEFT JOIN 呢？

多个 LEFT JOIN 肯定不行，即使有索引，也很容易造成全表扫描，为了减少该情况发生的概率，我一般会采取两种方法：

• 反范式
• 临时表

1、反范式

衡量一个 DBA 的水平有多高，得看他反范式能力有多强。
—— 知乎

比如我要根据 A表 的日期，关联 B表，统计出每个日期下某个属性的数量。我可以在A表中添加一列，用来存储“数量”，虽然违反了范式，但是性能上得到了提升。我觉得这是一笔划算的买卖。

规范化是为了技术服务，而技术是为业务服务。规范化也就是套路，能保证不出错，但是并不能解决特殊问题，特殊问题还需要特殊处理。

2、临时表

当需要联合三张表以上时，轻微的反范式已经不适用了，推荐用临时表，或者物化视图，但是 MySQL 的物化视图实现起来比较困难。事实上，我用的就是临时表，将四张表的部分数据抽离出来，保存在一张临时表中，制定一个“计划”，每天凌晨会自动更新。

好处

• 加快查询速度

缺点

• 会在某一时刻（凌晨）数据库IO过高
• 可能会出现异常，做好事务管理，让其回滚，重新执行，再有问题，就需要人工干预
• 数据准确性会延迟一天，适合非敏感业务

以上是仅针对数据库做的优化，至于缓存（一级缓存、二级缓存），那属于持久层框架的职责，不在此文记录范围之内。