1. sql_mode模式 mysql数据库的中有一个环境变量sql_mode,定义了mysql应该支持的sql语法，数据校验等！我们可以通过以下方式查看当前数据库使用的sql_mode： mysql> select @@sql_mode; +—————————————————————-+ | @@sql_mode | +—————————————————————-+ | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +—————————————————————-+ 或者通过查看系统变量方式: mysql> show variables like 'sql_mode%'\G; *************************** 1. row *************************** Variable_name: sql_mode Value: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION mysql5.0以上版本支持三种sql_mode模式：ANSI、TRADITIONAL和STRICT_TRANS_TABLES。 ANSI模式：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。 TRADITIONAL模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。 STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。 1.2 ANSI模式 mysql> set @@sql_mode=ANSI; Query OK, 0 rows affected (0.00 sec) mysql> create table test(name varchar(4), pass varchar(4)); Query OK, 0 rows affected (0.03 sec) mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb'); Query OK, 2 rows affected, 2 warnings (0.02 sec) Records: 2 Duplicates: 0 Warnings: 2 mysql> show warnings; +———+——+——————————————-+ | Level | Code | Message | +———+——+——————————————-+ | Warning | 1265 | Data truncated for column 'name' at row 1 | | Warning | 1265 | Data truncated for column 'pass' at row 1 | +———+——+——————————————-+ 2 rows in set (0.00 sec) mysql> select * from test; +——+——+ | name | pass | +——+——+ | aaaa | aaaa | | bbbb | bbbb | +——+——+ 2 rows in set (0.00 sec) 我们可以看到，在ANSI模式下，当我们插入数据时，未满足列长度要求时，数据同样会插入成功，但是对超出列长度的字段进行截断，同时报告warning警告。 1.3 STRICT_TRANS_TABLES模式 mysql> set @@sql_mode=STRICT_TRANS_TABLES; Query OK, 0 rows affected (0.00 sec) mysql> create table test(name varchar(4), pass varchar(4)); Query OK, 0 rows affected (0.02 sec) mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb'); ERROR 1406 (22001): Data too long for column 'name' at row 1 mysql> show errors; +——-+——+——————————————+ | Level | Code | Message | +——-+——+——————————————+ | Error | 1406 | Data too long for column 'name' at row 1 | +——-+——+——————————————+ 1 row in set (0.00 sec) mysql> select * from test; Empty set (0.00 sec) 我们可以看到，在STRICT_TRANS_TABLES模式下，当我们插入数据时，mysql会严格的进行数据的校验，当发现插入列值未满足要求，直接报告error错误，保证了错误数据无法插入到数据库中。 1.3 TRADITIONAL模式 mysql> set @@sql_mode=TRADITIONAL; Query OK, 0 rows affected (0.00 sec) mysql> create table test(name varchar(4), pass varchar(4)); Query OK, 0 rows affected (0.02 sec) mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb'); ERROR 1406 (22001): Data too long for column 'name' at row 1 mysql> show errors; +——-+——+——————————————+ | Level | Code | Message | +——-+——+——————————————+ | Error | 1406 | Data too long for column 'name' at row 1 | +——-+——+——————————————+ 1 row in set (0.00 sec) mysql> select * from test; Empty set (0.00 sec) TRADITIONAL模式与STRICT_TRANS_TABLES模式执行的结果，在这种情况下一致。 mysql> select @@sql_mode\G; *************************** 1. row *************************** @@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,E RROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER 1 row in set (0.00 sec) 看一下TRADITIONAL模式，我们发现在TRADITIONAL模式下，对所有的事务存储引擎，非事务存储引擎检查，日期类型中的月和日部分不能包含0，不能有0这样的日期(0000-00-00),数据不能除0，禁止grant自动创建新用户等一些校验。 注意：我们这里设置的sql_mode都是session级别的。另外，可以直接修改my.ini文件，找到sql_mode，然后设置新的模式即可！ 例如： […]
View Details在Apache, PHP, mysql的体系架构中，MySQL对于性能的影响最大，也是关键的核心部分。对于Discuz!论坛程序也是如此，MySQL的设置是否合理优化，直接 影响到论坛的速度和承载量！同时，MySQL也是优化难度最大的一个部分，不但需要理解一些MySQL专业知识，同时还需要长时间的观察统计并且根据经验 进行判断，然后设置合理的参数。 下面我们了解一下MySQL优化的一些基础，MySQL的优化我分为两个部分，一是服务器物理硬件的优化，二是MySQL自身(my.cnf)的优化。 一、服务器硬件对MySQL性能的影响 ① 磁盘寻道能力（磁盘I/O）,以目前高转速SCSI硬盘(7200转/秒)为例，这种硬盘理论上每秒寻道7200次，这是物理特性决定的，没有办法改变。 MySQL每秒钟都在进行大量、复杂的查询操作，对磁盘的读写量可想而知。所以，通常认为磁盘I/O是制约MySQL性能的最大因素之一，对于日均访问量 在100万PV以上的Discuz!论坛，由于磁盘I/O的制约，MySQL的性能会非常低下！解决这一制约因素可以考虑以下几种解决方案： 使用RAID-0+1磁盘阵列，注意不要尝试使用RAID-5，MySQL在RAID-5磁盘阵列上的效率不会像你期待的那样快。 ②CPU 对于MySQL应用，推荐使用S.M.P.架构的多路对称CPU，例如：可以使用两颗Intel Xeon 3.6GHz的CPU，现在我较推荐用4U的服务器来专门做数据库服务器，不仅仅是针对于mysql。 ③物理内存对于一台使用MySQL的Database Server来说，服务器内存建议不要小于2GB，推荐使用4GB以上的物理内存，不过内存对于现在的服务器而言可以说是一个可以忽略的问题，工作中遇到了高端服务器基本上内存都超过了16G。 二、 MySQL自身因素 当解决了上述服务器硬件制约因素后，让我们看看MySQL自身的优化是如何操作的。对MySQL自身的优化主要是对其配置文件 my.cnf中的各项参数进行优化调整。下面我们介绍一些对性能影响较大的参数。 由于my.cnf文件的优化设置是与服务器硬件配置息息相关的，因而我们指定一个假想的服务器硬件环境： 下面，我们根据以上硬件配置结合一份已经优化好的my.cnf进行说明： #vim /etc/my.cnf以下只列出my.cnf文件中[mysqld]段落中的内容，其他段落内容对MySQL运行性能影响甚微，因而姑且忽略。 代码如下 [mysqld] port = 3306 serverid = 1 socket = /tmp/mysql.sock skip-locking #避免MySQL的外部锁定，减少出错几率增强稳定性。 skip-name-resolve #禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！ back_log = 384 #back_log 参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。 如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自 己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。 key_buffer_size = 256M #key_buffer_size指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。注意：该参数值设置的过大反而会是服务器整体效率降低！ max_allowed_packet = 4M thread_stack = 256K table_cache = 128K sort_buffer_size = 6M #查询排序时所能使用的缓冲区大小。注意：该参数对应的分配内存是每连接独占，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。 read_buffer_size = 4M #读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。 join_buffer_size = 8M #联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。 myisam_sort_buffer_size = 64M table_cache = 512 thread_cache_size = 64 query_cache_size […]
View Details