开源的互联网

如还没有注册使用过监控宝的请先注册：http://www.jiankongbao.com 。
mysql的缓存对mysql的高效查询有很大的作用，其原理是将 SELECT 语句和语句的结果做了一个 HASH 映射关系然后保存在内存区域中。在mysql默认配置中，查询缓存是打开的。
在监控宝的服务监控中创建mysql监控，填写一些必要的信息，这并不会有什么安全问题。
添加完成后便能看到监控到的各项mysql数据指标，这里只说缓存。
首先说控制mysql缓存的配置参数：

1
2
3
4
5

query_cache_size：Cache所使用的内存大小，默认值为0，可以带单位MB
query_cache_limit：Cache 的单条Query结果集的最大容量，默认是1MB，超过此参数设置的查询结果集将不会被缓存
query_cache_min_res_unit：Cache中每次分配内存的最小空间，也就是每个Query的Cache最小占用的内存空间大小
query_cache_type：可选值为0(OFF),1(ON)和2(DEMAND)三种，分别指关闭Cache；开启Cache；开启Cache，但是只有当SELECT语句中使用了SQL_CACHE 提示后，才使用Cache
query_cache_wlock_invalidate：控制当有写锁定发生在表上的时刻是否先失效该表相关的Cache

在监控宝监控页面将能看到query_cache_size，query_cache_limit等配置中设定的值，然后通过图表能得到缓存的使用空间情况，命中率，缓存查询次数等，这些都能帮你很好的分析mysql缓存的使用情况，命中率的情况直接影响到你mysql服务器的查询效率。
cache的单位是select语句，当然也包括各种复杂的select，如子查询，join，union等。
cache的效率高是因为使用cache得到结果之前只是进行了权限验证即可，不需要select的优化和分析，当然因为cache查询语句的保存是通过hash的，所以select必须完全一样才能命中缓存。
通过图表，你能随时关注mysql的缓存使用情况，如空间大小，命中率等直接体现查询性能，另外查询缓存不适合写密集型的mysql，每次写都需要更新缓存，这样反而影响整体性能。
当然只凭缓存来判断mysql的运行情况是不够的，可以查看其它监控视图，如连接，流量等，稍后再介绍。

今天发现一台mysql的slave不能同步，竟然报错是说system user没有“ SUPER PRIVILEGE”,
system user怎么会没有对这个库的权限呢？而且以前也没出现过，后来发现竟然是trigger搞
的鬼，在mysql里面触发器原来创建的用户不是system user，同步下来的时候会执行到触发器，
所以报错没有super privilege，删除触发器，同步成功，或者将触发器的创建用户改为system user。
当然本来应该不让slave有触发器这种可能会造成master slave不同步的操作，可以让触发器在master，
slave只是执行触发器产生的sql就行了。在master端也需要注意trigger的用户问题。

一般在关系型数据库有4个隔离级别，分别为：

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

查看全局的隔离级别：

1
2
3
4
5
6

mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+

查看当前会话的隔离级别：

1
2
3
4
5
6

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

修改隔离级别：

1
2

SET transaction isolation level READ committed; #会话型
SET global transaction isolation level READ committed; #全局型

InnoDB的可重复读隔离级别和其他数据库的可重复读是有区别的，不会造成幻象读（phantom read），所谓幻象读，就是同一个事务内，多次select，可以读取到其他session insert并已经commit的数据。下面是一个小的测试，证明InnoDB的可重复读隔离级别不会造成幻象读。测试涉及两个session，分别为session 1和session 2，隔离级别都是repeateable read，关闭autocommit:(如果您想要对于一个单一系列的语句禁用autocommit模式，则您可以使用START TRANSACTION语句)

1
2
3
4
5
6
7
8
9
10

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row IN SET (0.00 sec)
 
mysql> SET autocommit=off;
Query OK, 0 rows affected (0.00 sec)

session 1 创建表并插入测试数据

1
2
3
4

mysql> CREATE TABLE test(i int) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO test VALUES(1);

session 2 查询，没有数据，正常，session1没有提交，不允许脏读

1
2

mysql> SELECT * FROM test;
Empty SET (0.00 sec)

session 1 提交事务

1
2

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

session 2 查询，还是没有数据，没有产生幻象读

1
2

mysql> SELECT * FROM test;
Empty SET (0.00 sec)

也尝试了几种级别在自增id的情况下的区别：
未提交读(Read Uncommitted)：脏读

1
2
3
4
5

SET transaction isolation level READ Uncommitted; 
 
INSERT test (i) value (2);
 
SELECT LAST_INSERT_ID() FROM test;

可以读到未提交的数据, id 增

-----------------------------------------------------------------
Read Committed

1
2
3
4
5

SET transaction isolation level READ Committed; 
 
INSERT test (i) value (2);
 
SELECT LAST_INSERT_ID() FROM test;

不可以读到未提交的数据, id 增

-------------------------------------------------
REPEATABLE READ

1
2
3
4
5

SET transaction isolation level REPEATABLE READ; 
 
INSERT test (i) value (2);
 
SELECT LAST_INSERT_ID() FROM test;

不可以读到未提交的数据, id 增

-------------------------------------------------

以上试验版本：4.x-community-nt-log

知道timestamp能自动更新，但是一开始试一下发现在update的时候无法更新到当前时间，

仔细的查看文档发现：

TIMESTAMP[(M)]

时间戳。范围是'1970-01-01 00:00:00'到2037年。

TIMESTAMP列用于INSERT或UPDATE操作时记录日期和时间。如果你不分配一个值，表中的第一个TIMESTAMP列自动设置为最近操作的日期和时间。也可以通过分配一个NULL值，将TIMESTAMP列设置为当前的日期和时间。

TIMESTAMP值返回后显示为'YYYY-MM-DD HH:MM:SS'格式的字符串，显示宽度固定为19个字符。如果想要获得数字值，应在TIMESTAMP 列添加+0。

注释：MySQL 4.1以前使用的TIMESTAMP格式在MySQL 5.1中不支持；关于旧格式的信息参见MySQL 4.1 参考手册。

原来是我在默认值里面设置了CURRENT_TIMESTAMP,所以造成update是无法更新时间，看来手册上的东西还是要仔仔细细的看，不注意就想当然了。

还有一个表如果有多个TIMESTAMP字段，只会在第一个字段自动更新，这些都是要注意的。

今天想用mysql的触发器来实现对原来数据表拼音字段的扩展，写法是：

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END
|
DELIMITER ;

一看就能看懂，但是发现mysql触发器有几个弱点，

1，mysql4是没有触发器的

2，触发器不能对本表操作（这个不是缺点，是出于数据安全方面的考虑吧）

看来不得不放弃mysql trigger来出来老数据表的拼音问题了。

接下来写一个delete的例子：
首先要注意一点，在触发器的SQL语句中，你可以关联表中的任意列。但你不能仅使用列的名称去标识，那会使系统混淆，因为那里可能会有列的新名（这可能正是你要修改的，你的动作可能正是要修改列名），还有列的旧名存在。因此你必须用这样的语法来标识： "NEW . column_name"或者"OLD . column_name".

一个我写的delete的例子，用来保存delete后的数据：

DELIMITER $$;

DROP TRIGGER `gbk`.`RECORDDELETE`$$

CREATE TRIGGER `gbk`.`RECORDDELETE` AFTER DELETE on `gbk`.`articles`
FOR EACH ROW BEGIN

DECLARE d_md5 CHAR(32);

SET d_md5 = MD5(CONCAT(OLD.id, '-singer'));

INSERT INTO `gbk`.`delete_record` (`md5`, `key`, `table`, `id`) VALUES (d_md5, OLD.title, 'singer', OLD.id);

END$$

DELIMITER ;$$

希望能提供一个参考

mysqldumpslow 是很好的分析sql查询性能及优化数据库服务器的工具

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

[tanbin@localhost ~]$ mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
 
Parse and summarize the MySQL slow query log. Options are
 
  --verbose    verbose
  --debug      debug
  --help       write this text to standard output
 
  -v           verbose
  -d           debug
  -s ORDER     what to sort by (t, at, l, al, r, ar etc), 'at' is default
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

解释一下各个参数：
-s表示排序的字段 t 执行时间， at 平均执行时间， l lock时间， al 平均lock时间， c 次数， r 返回记录数等；
-r 表示倒序输出；
-t 只显示前多少条，类似limit；
-g 类似grep，查找；
其他可以参数help；

1

mysqldumpslow -s c -t 20 /usr/LOCAL/mysql/DATA/slow-queries.log

很多同学在做分页的时候可能会使用select count(*) as total from ....，然后再limit 。。。这种的方式来做，
其实这样就会造成了两次查询，事实上一条sql语句就可以了，使用SQL_CALC_FOUND_ROWS();
如代码：

        $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 0, 10";

        $softlist = $this->_db->fetchAll($sql);
        $total = $this->_db->fetchRow('SELECT FOUND_ROWS() total');

这样一次查询就能得到分页所要的总数，自然简单效率又更高了。

积累了一些常用的mysql用户及权限管理的命令，以备查。

1
2
3
4
5
6
7
8
9
10
11
12
13

SHOW GRANTS FOR 'root'@'localhost';
SHOW PROCESSLIST;
SHOW TABLE STATUS;
SHOW TRIGGERS;
SHOW VARIABLES;
FLUSH PRIVILEGES;
FLUSH QUERY CACHE;
source filename
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
DROP USER user;
mysqladmin -u user_name -h host_name password "newpwd";
GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

注意，如果发现已经权限但是无法远程登录数据库，请检查my.cnf中bind-address选项。

在ubuntu安装的mysql默认是不允许mysql远程连接的，
远程连接有很多的不安全的因素，可是有时候还是有必要的，
打开远程连接需要做两步:

1.给用户远程连接的权限，即主机为“%”，

GRANT ALL PRIVILEGES ON remote.* TO remote@"%" IDENTIFIED BY "remotepwd";

当然用phpmyadmin这样的工具更方便。

2.修改my.cnf

cp ....
vim ....
#bind-address = 127.0.0.1  //老版本是#skip-networking,注释即可

3.mysqld restart

mysql的函数真的是非常丰富，很多查询处理请先看看mysql是否有函数支持了，特别是在数据批量处理的时候，

今天我把一个冗余的表合并成一个精简的表，还是上sql吧：

INSERT INTO tanbin_krc_fine (`song`, `singer`, `sizes`)  (SELECT `songnamejian`, `artname`, GROUP_CONCAT(`size`) FROM

tanbin_krc  WHERE `locked` = 1 GROUP BY CONCAT(`songnamejian`, `artname`))

GROUP_CONCAT函数会自动把group by里面数字用“，”隔开写成一个字段，这样少去了好多操作，一个sql全搞定了。

再说一个字符串查找函数:

SELECT *, INSTR('++忘情水++', `songname`) check_song, INSTR('刘德华', `singer`) check_singer,
       ABS( 1 - `size` )
 FROM `lrc`

INSTR为用字段数据去keyword里面比较（找关键字可能用得上），对应keyword去字段中找为LOCATE,

NOTES: MYSQL 很好很强大