MySQL随机生成ID

随机生成ID
update www_image set user_id=(select round(round(rand(),4)*30000));

MySQL调优

1, 查看MySQL服务器配置信息
mysql> show variables;

2, 查看MySQL服务器运行的各种状态值
mysql> show global status;

3, 慢查询
mysql> show variables like '%slow%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| log_slow_queries | OFF |
| slow_launch_time | 2 |
+------------------+-------+
mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 279 |
+---------------------+-------+

配置中关闭了记录慢查询（最好是打开，方便优化），超过2秒即为慢查询，一共有279条慢查询
4, 连接数
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 500 |
+-----------------+-------+

mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 498 |
+----------------------+-------+
设置的最大连接数是500，而响应的连接数是498
max_used_connections / max_connections * 100% = 99.6% （理想值 ≈ 85%）

5, key_buffer_size
key_buffer_size是对MyISAM表性能影响最大的一个参数, 不过数据库中多为Innodb
mysql> show variables like 'key_buffer_size';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 67108864 |
+-----------------+----------+

mysql> show global status like 'key_read%';
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| Key_read_requests | 25629497 |
| Key_reads | 66071 |
+-------------------+----------+
一共有25629497个索引读取请求，有66071个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：
key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =0.27%
需要适当加大key_buffer_size
mysql> show global status like 'key_blocks_u%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Key_blocks_unused | 10285 |
| Key_blocks_used | 47705 |
+-------------------+-------+
Key_blocks_unused表示未使用的缓存簇(blocks)数，Key_blocks_used表示曾经用到的最大的blocks数
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 18% （理想值 ≈ 80%）

优化MySQL语句的十个建议

1.他的力气没使对地方

我们要遵循的一个准则就是如果你要优化代码时，应该先找出瓶颈在哪。然而Silverton先生的力气没有用对地方。我认为60%的优化是基于清楚理解SQL和数据库基础的。你需要知道join和子查询的区别，列索引，以及如何将数据规范化等等。另外的35%的优化是需要清楚数据库选择时的性能表现，例如COUNT(*)可能很快也可能很慢，要看你选用什么数据库引擎。还有一些其他要考虑的因素，例如数据库在什么时候不用缓存，什么时候存在硬盘上而不存在内存中，什么时候数据库创建临时表等等。剩下的5%就很少会有人碰到了，但Silverton先生恰好在这上面花了大量的时间。我从来就没用过SQL_SAMLL_RESULT。

2.很好的问题，但是很糟糕的解决方法

Silverton先生提出了一些很好的问题。MySQL针对长度可变的列如TEXT或BLOB，将会使用动态行格式(dynamic row format)，这意味着排序将在硬盘上进行。我们的方法不是要回避这些数据类型，而是将这些数据类型从原来的表中分离开，放入另外一个表中。下面的schema可以说明这个想法：

CREATE TABLE posts (
id int UNSIGNED NOT NULL AUTO_INCREMENT,
author_id int UNSIGNED NOT NULL,
created timestamp NOT NULL,
PRIMARY KEY(id)
);

CREATE TABLE posts_data (
post_id int UNSIGNED NOT NULL.
body text,
PRIMARY KEY(post_id)
);

3. 有点匪夷所思……

他的许多建议都是让人非常吃惊的，譬如“移除不必要的括号”。你这样写SELECT * FROM posts WHERE (author_id = 5 AND published = 1)，还是这样写SELECT * FROM posts WHERE author_id = 5 AND published = 1 ，都不重要。任何比较好的DBMS都会自动进行识别做出处理。这种细节就好像C语言中是i++快些还是++i快些。真的，如果你把精力都花在这上面了，那就不用写代码了。

MySQL regex_replace Function

[cc lang='mysql' ]

DELIMITER $$
CREATE FUNCTION  `regex_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000))

RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN 
 DECLARE temp VARCHAR(1000);
 DECLARE ch VARCHAR(1);
 DECLARE i INT;
 SET i = 1;
 SET temp = '';
 IF original REGEXP pattern THEN 
  loop_label: LOOP 
   IF i>CHAR_LENGTH(original) THEN
    LEAVE loop_label;
   END IF;
   SET ch = SUBSTRING(original,i,1);
   IF NOT ch REGEXP pattern THEN
    SET temp = CONCAT(temp,ch);
   ELSE
    SET temp = CONCAT(temp,replacement);
   END IF;
   SET i=i+1;
  END LOOP;
 ELSE
  SET temp = original;
 END IF;
 RETURN temp;
END$$
DELIMITER ;

[/cc]

一天总结

MySQL

[cc lang='text' line_numbers='false']
mysql> CREATE VIEW test.v AS SELECT * FROM t;
[/cc]

Exim4

[cc lang='text' line_numbers='false']sudo dpkg-reconfigure exim4-config[/cc]

Exim user forward filter how to:

http://www.exim.org/exim-html-4.40/doc/html/filter_3.html#SECT3.26

MySQL锁表机制分析

为了给高并发情况下的mysql进行更好的优化，有必要了解一下mysql查询更新时的锁表机制。
一、概述
MySQL有三种锁的级别：页级、表级、行级。
MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level
locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

HOWTO: configure MySQL’s my.cnf file

[cc lang="text"]
[mysqld]
user=mysql
bind-address=127.0.0.1
datadir=/var/lib/mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysql/mysql.sock
port=3306
tmpdir=/tmp
language=/usr/share/mysql/english
skip-external-locking
query_cache_limit=64M
query_cache_size=32M
query_cache_type=1
max_connections=15
max_user_connections=300
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_stack=128K
thread_cache_size=128
myisam-recover=BACKUP
key_buffer=64M
join_buffer=1M
max_allowed_packet=32M
table_cache=512M
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
max_connect_errors=10
thread_concurrency=4
myisam_sort_buffer_size=32M
skip-locking
skip-bdb
expire_logs_days=10
max_binlog_size=100M
server-id=1
[mysql.server]
user=mysql
basedir=/usr
[safe_mysqld]
bind-address=127.0.0.1
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192
SAFE_MYSQLD_OPTIONS=”–defaults-file=/etc/my.cnf –log-slow-queries=/var/log/slow-queries.log”
[mysql]
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
max_heap_table_size = 64 M
tmp_table_size = 64 M
!includedir /etc/mysql/conf.d/
[/cc]

MySQL性能优化21 - 使用随机函数产生采样

RAND() 函数是 MySQL 提供的产生随机数的函数。没有指定参数时，每次执行会返回 0-1 的浮点数：

[cc lang="text"]mysql> select rand();

+------------------+
| rand() |
+------------------+
| 0.56110724207117 |
+------------------+

1 row in set (0.02 sec)

mysql> select rand();

+-----------------+
| rand() |
+-----------------+
| 0.3343874880433 |
+-----------------+

1 row in set (0.00 sec)[/cc]

MySQL性能优化20 - ORDER BY 操作的优化

在某些情况中， MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。 where 条件和 order by 使用相同的索引，并且 order by 的顺序和索引顺序相同，并且 order by 的字段都是升序或者都是降序。

例如：下列 sql 可以使用索引。

[cc lang="mysql"]SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;[/cc]

但是以下情况不使用索引：

[cc lang="mysql"]SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC ；[/cc]

--order by 的字段混合 ASC 和 DESC

[cc lang="mysql"]SELECT * FROM t1 WHERE key2=constant ORDER BY key1 ；[/cc]

-- 用于查询行的关键字与 ORDER BY 中所使用的不相同

[cc lang="mysql"]SELECT * FROM t1 ORDER BY key1, key2 ；[/cc]

-- 对不同的关键字使用 ORDER BY 。

MySQL性能优化19 - GROUP BY 操作的优化

默认情况下， MySQL 在执行 [cci lang="mysql"]GROUP BY col1 ， col2....[/cci] 操作的时候，会按照 [cci lang="mysql"]GROUP BY[/cci] 字段的顺序进行排序。如果显式包括一个包含相同的列的 [cci lang="mysql"]ORDER BY[/cci] 子句，则对 MySQL 的实际执行性能没有什么额外的影响。

如果查询包括 [cci lang="mysql"]GROUP BY[/cci] 操作， 但是不需要对结果进行排序，或者对默认的排序结果不满意，希望获得结果后再由程序进一步处理的时候，可以指定 [cci lang="mysql"]ORDER BY NULL[/cci] 禁止排序，从而避免排序结果的消耗。

MySQL性能优化18 - 使用 GROUP BY WITH ROLLUP 改善统计性能

使用 GROUP BY 的 WITH ROLLUP 字句可以检索出更多的分组聚合信息，它不仅仅能像一般的 GROUP BY 语句那样检索出各组的聚合信息，还能检索出本组类的整体聚合信息。

MySQL性能优化17 - INSERT操作的优化

执行[cci lang='mysql' ] INSERT[/cci] 操作的时候，可以考虑使用以下的方式优化 SQL 的执行效率：

• 如果你同时从同一客户插入很多行，使用多个值表的 INSERT 语句。这比使用分开 INSERT 语句快。
  [cc lang='mysql' ]Insert into test values(1,2),(1,3),(1,4) …[/cc]

• 如果你从不同客户插入很多行，能通过使用 [cci lang='mysql' ]INSERT DELAYED[/cci] 语句得到更高的速度。[cci lang='mysql' ] Delayed [/cci]的含义是让 INSERT 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘；这比每条语句分别插入要快的多； [cci lang='mysql' ]LOW_PRIORITY[/cci] 刚好相反，在所有其他用户对表的读写完后才进行插入；


• 将索引文件和数据文件分在不同的磁盘上存放；


• 如果进行批量插入，可以增加 bulk_insert_buffer_size 变量值的方法来提高速度，但是，这只能对 MyISAM 表使用；


• 当从一个文本文件装载一个表时，使用[cci lang='mysql' ] LOAD DATA INFILE [/cci]。这通常比使用很多 INSERT 语句快 20 倍；


• 根据应用情况使用 [cci lang='mysql' ]REPLACE[/cci] 语句代替[cci lang='mysql' ] INSERT[/cci] ；


• 根据应用情况使用 [cci lang='mysql' ]IGNORE[/cci] 关键字忽略重复记录。

MySQL性能优化16 - InnoDB 存储引擎

对于 InnoDB 类型的表，这种方式并不能提高导入数据的效率。对于 InnoDB 类型的表，我们有以下几种方式可以提高导入的效率：

因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果 InnoDB 表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高导入数据的效率。

在导入数据前执行[cci lang='mysql' ] SET UNIQUE_CHECKS=0[/cci] ，关闭唯一性校验，在导入结束后执行 [cci lang='mysql' ]SET UNIQUE_CHECKS=1[/cci] ，恢复唯一性校验，可以提高导入的效率。

如果应用使用自动提交的方式，建议在导入前执行 [cci lang='mysql' ]SET AUTOCOMMIT=0[/cci] ，关闭自动提交，导入结束后再执行[cci lang='mysql' ] SET AUTOCOMMIT=1 [/cci]，打开自动提交，也可以提高导入的效率。

MySQL性能优化15 - MyISAM 存储引擎

对于 MyISAM 类型的表，可以通过以下方式快速的导入大量的数据。

[cc lang='mysql' ]ALTER TABLE tblname DISABLE KEYS;[/cc]

loading the data

[cc lang='mysql' ]ALTER TABLE tblname ENABLE KEYS;[/cc]

这两个命令用来打开或者关闭 MyISAM 表非唯一索引的更新。在导入大量的数据到一个非空的 MyISAM 表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的 MyISAM 表，默认就是先导入数据然后才创建索引的，所以不用进行设置。

MySQL性能优化14 - HIGH_PRIORITY/LOW_PRIORITY/INSERT DELAYED

MySQL 还允许改变语句调度的优先级，它可以使来自多个客户端的查询更好地协作，这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。

我们首先应该确定应用的类型，判断应用是以查询为主还是以更新为主的，是确保查询效率还是确保更新的效率，决定是查询优先还是更新优先。

下面我们提到的改变调度策略的方法主要是针对 MyISAM 存储引擎的，对于 InnoDB 存储引擎，语句的执行是由获得行锁的顺序决定的。

MySQL 的默认的调度策略可用总结如下：

• 写入操作优先于读取操作。


• 对某张数据表的写入操作某一时刻只能发生一次，写入请求按照它们到达的次序来处理。


• 对某张数据表的多个读取操作可以同时地进行。

MySQL 提供了几个语句调节符，允许你修改它的调度策略：

• [cci lang='mysql' ]LOW_PRIORITY[/cci] 关键字应用于 [cci lang='mysql' ]DELETE[/cci] 、[cci lang='mysql' ] INSERT [/cci]、[cci lang='mysql' ] LOAD DATA[/cci] 、 [cci lang='mysql' ]REPLACE[/cci] 和 [cci lang='mysql' ]UPDATE[/cci] 。


• [cci lang='mysql' ]HIGH_PRIORITY[/cci] 关键字应用于[cci lang='mysql' ] SELECT[/cci] 和[cci lang='mysql' ] INSERT [/cci]语句。


• [cci lang='mysql' ]DELAYED[/cci] 关键字应用于 [cci lang='mysql' ]INSERT[/cci] 和 [cci lang='mysql' ]REPLACE[/cci] 语句。

如果写入操作是一个 [cci lang='mysql' ]LOW_PRIORITY [/cci]（低优先级）请求，那么系统就不会认为它的优先级高于读取操作。在这种情况下，如果写入者在等待的时候，第二个读取者到达了，那么就允许第二个读取者插到写入者之前。只有在没有其它的读取者的时候，才允许写入者开始操作。这种调度修改可能存在 [cci lang='mysql' ]LOW_PRIORITY[/cci] 写入操作永远被阻塞的情况。

[cci lang='mysql' ]SELECT 查询的 HIGH_PRIORITY[/cci] （高优先级）关键字也类似。它允许[cci lang='mysql' ] SELECT [/cci]插入正在等待的写入操作之前，即使在正常情况下写入操作的优先级更高。另外一种影响是，高优先级的 [cci lang='mysql' ]SELECT[/cci] 在正常的[cci lang='mysql' ] SELECT [/cci]语句之前执行，因为这些语句会被写入操作阻塞。

如果你希望所有支持 [cci lang='mysql' ]LOW_PRIORITY [/cci]选项的语句都默认地按照低优先级来处理，那么请使用 [cci lang='mysql' ]--low-priority-updates[/cci] 选项来启动服务器。通过使用 [cci lang='mysql' ]INSERT HIGH_PRIORITY[/cci] 来把 INSERT[/cci] 语句提高到正常的写入优先级，可以消除该选项对单个[cci lang='mysql' ] INSERT[/cci] 语句的影响。

MySQL性能优化13 - SQL_NO_CACHE/SQL_CACHE

可以在 [cci lang="mysql"]SELECT[/cci] 语句中指定查询缓存的选项，对于那些肯定要实时的从表中获取数据的查询，或者对于那些一天只执行一次的查询，我们都可以指定不进行查询缓存，使用 [cci lang="mysql"]SQL_NO_CACHE[/cci] 选项。

对于那些变化不频繁的表，查询操作很固定，我们可以将该查询操作缓存起来，这样每次执行的时候不实际访问表和执行查询，只是从缓存获得结果，可以有效地改善查询的性能，使用 [cci lang="mysql"]SQL_CACHE[/cci] 选项。

下面是使用 [cci lang="mysql"]SQL_NO_CACHE[/cci] 和 [cci lang="mysql"]SQL_CACHE[/cci] 的例子：

[cc lang="mysql"]mysql> select sql_no_cache id,name from test3 where id < 2;
mysql> select sql_cache id,name from test3 where id < 2;[/cc]

注意：查询缓存的使用还需要配合相应得服务器参数的设置，相关情况请参考服务器优化的章节。

MySQL性能优化12 - FORCE INDEX/IGNORE INDEX

[cci lang="mysql"]FORCE INDEX[/cci] 通常用来对查询强制使用一个或者多个索引。 MySQL 通常会根据统计信息选择正确的索引，但是当查询优化器选择了错误的索引或者根本没有使用索引的时候，这个提示将非常有用。

[cci lang="mysql"]IGNORE INDEX[/cci] 提示会禁止查询优化器使用指定的索引。在具有多个索引的查询时，可以用来指定不需要优化器使用的那个索引，还可以在删除不必要的索引之前在查询中禁止使用该索引。

MySQL性能优化11 - 使用 Hint

很多数据库都提供了 Hint （提示）的概念，使用 Hint 可以在数据库为 SQL 产生执行计划的时候提供额外的信息，从而使 SQL 按照我们预定的执行计划执行。 Hint 的使用并不是必须的，大部分情况下，都不需要使用 Hint 来影响执行计划，只有在少数情况下，我们才需要使用。下面我们介绍的是 MySQL 中使用的比较多的几个 Hint 。（待续）

MySQL性能优化10 - OPTIMIZE

[cci lang="mysql"]OPTIMIZE TABLE[/cci] 是指对表进行优化。如果已经删除了表的一大部分数据，或者如果已经对含有可变长度行的表（含有 VARCHAR 、 BLOB 或 TEXT 列的表）进行了很多更改，就应该使用 [cci lang="mysql"]OPTIMIZE TABLE [/cci]命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费。 [cci lang="mysql"]OPTIMIZE TABLE[/cci] 命令只对 MyISAM 、 BDB 和 InnoDB 表起作用。表优化的工作可以每周或者每月定期执行，对提高表的访问效率有一定的好处，但是需要注意的是，优化表期间会锁定表，所以一定要安排在空闲时段进行。

MySQL性能优化9 - ANALYZE 和 CHECK

ANALYZE TABLE 和 CHECK TABLE 分别用来进行表分析和表检查。表分析主要用来获得关键字的分布情况，对执行计划的产生有帮助，而表检查主要用来检查表或者视图是否存在错误。

ANALYZE TABLE 用来分析和存储表的关键字的分布，使得系统获得准确的统计信息，影响 SQL 的执行计划的生成。对于数据基本没有发生变化的表，是不需要经常进行表分析的。但是如果表的数据量变化很明显，用户感觉实际的执行计划和预期的执行计划不同的时候，执行一次表分析可能有助于产生预期的执行计划。