存档

文章标签 ‘mysql’

详细讲解MySQL数据库中Show命令的用法

2009年3月9日 没有评论

Show命令的用法：

1. show tables或show tables from database_name; — 显示当前数据库中所有表的名称。

2. show databases; — 显示mysql中所有数据库的名称。

3. show columns from table_name from database_name; 或show columns from database_name.table_name; — 显示表中列名称。

4. show grants for user_name; — 显示一个用户的权限，显示结果类似于grant 命令。

5. show index from table_name; — 显示表的索引。

6. show status; — 显示一些系统特定资源的信息，例如，正在运行的线程数量。

7. show variables; — 显示系统变量的名称和值。

8. show processlist; — 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。

9. show table status; — 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。

10. show privileges; — 显示服务器所支持的不同权限。

11. show create database database_name; — 显示create database 语句是否能够创建指定的数据库。

12. show create table table_name; — 显示create database 语句是否能够创建指定的数据库。

13. show engies; — 显示安装以后可用的存储引擎和默认引擎。

14. show innodb status; — 显示innoDB存储引擎的状态。

15. show logs; — 显示BDB存储引擎的日志。

16. show warnings; — 显示最后一个执行的语句所产生的错误、警告和通知。

17. show errors; — 只显示最后一个执行语句所产生的错误。

18. show [storage] engines; –显示安装后的可用存储引擎和默认引擎。

分类: MySql 标签: ,

mysqldump的几个主要选项探究

2009年3月6日 没有评论

作/译者：叶金荣（Email: ），来源：http://imysql.cn，转载请注明作/译者和出处，并且不能用于商业用途，违者必究。

0、前言

本文主要探讨 mysqldump 的几种主要工作方式，并且比较一下和 mk-parralel-dump 的一些差异，为备份方式的选择提供更多的帮助。

1、mysqldump

首先来看下 mysqldump 的几个主要参数的实际工作方式。

mysqldump 几个主要选项
1. -q
很简单，什么都不做，只是导出时加了一个 SQL_NO_CACHE 来确保不会读取缓存里的数据。

081022 17:39:33       7 Connect     root@localhost on
7 Query       /*!40100 SET @@SQL_MODE='' */
7 Init DB     yejr
7 Query       SHOW TABLES LIKE 'yejr'
7 Query       LOCK TABLES `yejr` READ /*!32311 LOCAL */
7 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=1
7 Query       show create table `yejr`
7 Query       show fields from `yejr`
7 Query       show table status like 'yejr'
7 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`
7 Query       UNLOCK TABLES
7 Quit

2. –lock-tables
跟上面类似，不过多加了一个 READ LOCAL LOCK，该锁不会阻止读，也不会阻止新的数据插入。

081022 17:36:21       5 Connect     root@localhost on
5 Query       /*!40100 SET @@SQL_MODE='' */
5 Init DB     yejr
5 Query       SHOW TABLES LIKE 'yejr'
5 Query       LOCK TABLES `yejr` READ /*!32311 LOCAL */
5 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=1
5 Query       show create table `yejr`
5 Query       show fields from `yejr`
5 Query       show table status like 'yejr'
5 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`
5 Query       UNLOCK TABLES
5 Quit

3. –lock-all-tables
这个就有点不太一样了，它请求发起一个全局的读锁，会阻止对所有表的写入操作，以此来确保数据的一致性。备份完成后，该会话断开，会自动解锁。

081022 17:36:55       6 Connect     root@localhost on
6 Query       /*!40100 SET @@SQL_MODE='' */
6 Query       FLUSH TABLES
6 Query       FLUSH TABLES WITH READ LOCK
6 Init DB     yejr
6 Query       SHOW TABLES LIKE 'yejr'
6 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=1
6 Query       show create table `yejr`
6 Query       show fields from `yejr`
6 Query       show table status like 'yejr'
6 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`
6 Quit

4. –master-data
除了和刚才的 –lock-all-tables 多了个 SHOW MASTER STATUS 之外，没有别的变化。

081022 17:59:02       1 Connect     root@localhost on
1 Query       /*!40100 SET @@SQL_MODE='' */
1 Query       FLUSH TABLES
1 Query       FLUSH TABLES WITH READ LOCK
1 Query       SHOW MASTER STATUS
1 Init DB     yejr
1 Query       SHOW TABLES LIKE 'yejr'
1 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=1
1 Query       show create table `yejr`
1 Query       show fields from `yejr`
1 Query       show table status like 'yejr'
1 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`
1 Quit

5. –single-transaction

InnoDB 表在备份时，通常启用选项 –single-transaction 来保证备份的一致性，实际上它的工作原理是设定本次会话的隔离级别为：REPEATABLE READ，以确保本次会话(dump)时，不会看到其他会话已经提交了的数据。

081022 17:23:35       1 Connect     root@localhost on
1 Query       /*!40100 SET @@SQL_MODE='' */
1 Query       SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
1 Query       BEGIN
1 Query       UNLOCK TABLES
1 Init DB     yejr
1 Query       SHOW TABLES LIKE 'yejr'
1 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=1
1 Query       show create table `yejr`
1 Query       show fields from `yejr`
1 Query       show table status like 'yejr'
1 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`
1 Quit

6. –single-transaction and –master-data
本例中，由于增加了选项 –master-data，因此还需要提交一个快速的全局读锁。在这里，可以看到和上面的不同之处在于少了发起 BEGIN 来显式声明事务的开始。这里采用 START TRANSACTION WITH CONSISTENT SNAPSHOT 来代替 BEGIN 的做法的缘故不是太了解，可以看看源代码来分析下。

081022 17:27:07       2 Connect     root@localhost on
2 Query       /*!40100 SET @@SQL_MODE='' */
2 Query       FLUSH TABLES
2 Query       FLUSH TABLES WITH READ LOCK
2 Query       SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2 Query       START TRANSACTION WITH CONSISTENT SNAPSHOT
2 Query       SHOW MASTER STATUS
2 Query       UNLOCK TABLES
2 Init DB     yejr
2 Query       SHOW TABLES LIKE 'yejr'
2 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=1
2 Query       show create table `yejr`
2 Query       show fields from `yejr`
2 Query       show table status like 'yejr'
2 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr`
2 Quit

关于隔离级别可以看手册 13.2.10.3. InnoDB and TRANSACTION ISOLATION LEVEL，或者本站之前的文章：[InnoDB系列] – 实例解析Innodb的隔离级别以及锁模式。

关于 START TRANSACTION WITH CONSISTENT SNAPSHOT 的说明可以看下手册描述：

The WITH CONSISTENT SNAPSHOT clause starts a consistent read for storage engines that are capable of it. This applies only to InnoDB. The effect is the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB  table. See Section 13.2.10.4, “Consistent Non-Locking Read”. The WITH CONSISTENT SNAPSHOT clause does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that allows consistent read (REPEATABLE READ or SERIALIZABLE).

12.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax

2. mysqldump 和 mk-parralel-dump 的比较

mk-parralel-dump 是开源项目 Maatkit 中的一个工具，主要由 Baron Schwartz 维护。
mk-parralel-dump 是由 perl 开发的，可以实现并发的导出数据表。具体的功能不细说，自己去看相关文档吧。这里只列出在我的环境下和 mysqldump 的对比数据。

2.1 mysqldump 常规使用

#导出耗时
time mysqldump -f --single-transaction -B yejr --tables yejr | gzip > /home/databak/yejr.sql.gz
real    10m15.319s
user    6m47.946s
sys     0m38.496s
#文件大小
608M /home/databak/yejr.sql.gz
#导出期间系统负载
05:00:01 PM       all      0.71      0.00      0.61      7.33     91.36
05:10:02 PM       all     13.93      0.00      2.21      4.64     79.22

2.2 mysqldump + gzip –fast

#导出耗时
time mysqldump -f --single-transaction -B yejr --tables yejr | gzip --fast > /home/databak/yejr_fast.sql.gz
real    9m6.248s
user    4m21.467s
sys     0m37.604s
#文件大小
815M Oct 21 17:33 /home/databak/yejr_fast.sql.gz
#导出期间系统负载
05:20:01 PM       all     11.94      0.00      2.43      5.69     79.94
05:30:01 PM       all      6.46      0.00      1.57      3.95     88.02

2.3 mk-parallel-dump 常规使用

time ./mk-parallel-dump --database yejr --tables yejr --basedir /home/databak/
default:             25 tables,    25 chunks,    25 successes,  0 failures, 404.93 wall-clock time, 613.25 dump time
real    6m48.763s
user    4m20.724s
sys     0m38.125s
#文件大小
819M    /home/databak/default/yejr/
#导出期间系统负载
05:10:02 PM       all     13.93      0.00      2.21      4.64     79.22
05:20:01 PM       all     11.94      0.00      2.43      5.69     79.94

可以看到，mk-parallel-dump 尽快确实实现了并发导出，速度相对快多了，却有个致命伤：那就是它不支持InnoDB的一致性备份，目前已经有人提交相关代码了，不过还没实现，期待中

分类: MySql, 转载 标签: ,

Mysql5字符集编码设置问题

2009年2月26日 没有评论

写入数据库后再读取数据库内容，在phpMyAdmin显示正常，运行程序结果为乱码。
修改数据库类增加了mysql_query(“SET NAMES ‘utf-8′”); ，但结果还是这样，
之后发现是mysql的utf-8应该改为utf8,修改执行如下语句解决该问题。
mysql_query(“SET NAMES ‘utf8′”);

分类: 杂文 标签: , ,

mysql查询语句的优化

2007年10月23日 没有评论

查询语句的优化
多多利用 “explain” 查询索引使用情况, 以便找出最佳的查询语句写法和索引设置方案
慎用 “select *”, 查询时只选出必须字段
查询使用索引时, 所遍历的索引条数越少, 索引字段长度越小, 查询效率越高 (可使用 “explain” 查询索引使用情况)
避免使用 mysql 函数对查询结果进行处理, 将这些处理交给客户端程序负责
使用 “limit” 时候, 尽量使 “limit” 出的部分位于整个结果集的前部, 这样的查询速度更快, 系统资源开销更低
在 “where” 子句中使用多个字段的 “and” 条件时, 各个字段出现的先后顺序要与多字段索引中的顺序相符
在 “where” 子句 中使用 “like” 时, 只有当通配符不出现在条件的最左端时才会使用索引
在 mysql 4.1 以上版本中, 避免使用子查询, 尽量使用 “内/外连接” 实现此功能
减少函数的使用, 如果可能的话, 尽量用单纯的表达式来代替
避免在 “where” 子句中, 对不同字段进行 “or” 条件查询, 将其拆分成多个单一字段的查询语句效率更高
从数据库结构做起
字段类型的定义时遵循以下规则:
选用字段长度最小
优先使用定长型
尽可能的定义 “NOT NULL”
数值型字段中避免使用 “ZEROFILL”
如果要储存的数据为字符串, 且可能值已知且有限, 优先使用 enum 或 set
索引的优化至关重要(以下如果没有特殊说明, 均指查询密集的情况)
被索引的字段的长度越小, 该索引的效率越高
被索引的字段中, 值的重复越少, 该索引的效率越高
查询语句中, 如果使用了 “group” 子句, 根据其中字段出现的先后顺序建立多字段索引
查询语句中, 如果使用了 “distinct”, 根据其中字段出现的先后顺序建立多字段索引
“where” 子句中, 出现对同一表中多个不同字段的 “and” 条件时, 按照字段出现的先后顺序建立多字段索引
“where” 子句中, 出现对同一表中多个不同字段的 “or” 条件时, 对重复值最少的字段建立单字段索引
进行 “内/外连接” 查询时, 对 “连接字段” 建立索引
对 “主键” 的 “unique” 索引 毫无意义, 不要使用
被索引字段尽可能的使用 “NOT NULL” 属性
对写入密集型表, 尽量减少索引, 尤其是 “多字段索引” 和 “unique” 索引

分类: MySql 标签: ,

mysql 慢查询分析

2007年9月25日 没有评论

mysql 慢查询分析

来源： byglen | inLinux/BSD
mysql有一个功能就是可以log下来运行的比较慢的sql语句，默认是…]]>

mysql有一个功能就是可以log下来运行的比较慢的sql语句，默认是没有这个log的，为了开启这个功能，要修改my.cnf或者在mysql启动的时候加入一些参数。

如果在my.cnf里面修改，需增加如下几行

long_query_time = 1
log-slow-queries =
log-queries-not-using-indexes

long_query_time 是指执行超过多久的sql会被log下来，这里是1秒。
log-slow-queries 设置把日志写在那里，可以为空，系统会给一个缺省的文件log-queries-not-using-indexes 就是纪录没使用索引的sql
分析：
mysqldumpslow –help以下，主要用的是
-s ORDER what to sort by (t, at, l, al, r, ar etc), ‘at’ is default
-t NUM just show the top n queries
-g PATTERN grep: only consider stmts that include this string

-s，是order的顺序，说明写的不够详细，俺用下来，包括看了代码，主要有
c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时倒叙
-t，是top n的意思，即为返回前面多少条的数据
-g，后边可以写一个正则匹配模式，大小写不敏感的

mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -s r -t 20 host-slow.log

上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。
mysqldumpslow -t 10 -s t -g “left join” host-slow.log
这个是按照时间返回前10条里面含有左连接的sql语句。

Time: 060908 22:17:43
# Query_time: 12 Lock_time: 0 Rows_sent: 86345 Rows_examined: 580963
Q:这个是慢查的日志,都是些什么意思?
A:查询用了12妙，返回86345行，一共查了580963行

分类: MySql 标签: ,