多看下这个文章：
LAMP 系统性能调优，第 3 部分: MySQL 服务器调优 http://www.ibm.com/developerworks/cn/linux/l-tune-lamp-3.html
------------------------------------------------------
一些查看数据库状态的sql语句：
1、显示非临时表的状态
mysql> SHOW TABLE STATUS/G;
*************************** 1. row ***************************
Name: admin_users
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 34
Data_length: 68
Max_data_length: 281474976710655
Index_length: 3072
Data_free: 0
Auto_increment: 3
Create_time: 2009-12-02 17:23:07
Update_time: 2009-12-17 10:55:45
Check_time: 2009-12-02 17:23:07
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
2、经常查看 help show
// 初始建立的语句
SHOW CREATE DATABASE db_name // 建立db时候的语句
SHOW CREATE TABLE tbl_name // 建立表时候的语句
// 引擎相关
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
// 权限相关
SHOW GRANTS FOR 'root'@'localhost';
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------+
| Grants for user@ip |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'ip' IDENTIFIED BY PASSWORD 'pwd' |
| GRANT ALL PRIVILEGES ON `alert`.* TO 'user'@'ip' |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
// 当前table cache中的table
SHOW OPEN TABLES
mysql> show open tables;
+----------+---------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+---------------+--------+-------------+
| alert | search_log | 0 | 0 |
// 当前运行的线程
SHOW [FULL] PROCESSLIST
mysql> show full processlist/G;
*************************** 1. row ***************************
Id: 3825933
User: mysql_repl
Host: 124.238.254.97:23539
db: NULL
Command: Binlog Dump
Time: 85359
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
// 查看性能
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
要查看性能，首先需要打开profiling：
set profilng = 1;
然后执行一些sql句子： select count(1) from users;
查看：
mysql> show profiles;
+----------+------------+----------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------+
| 1 | 0.00149350 | show databases |
| 2 | 0.00001350 | SELECT DATABASE() |
| 3 | 0.00179650 | show tables |
| 4 | 0.00153575 | select count(1) from user |
| 5 | 0.02050550 | select count(1) from users |
| 6 | 0.00057975 | help 'show' |
+----------+------------+----------------------------+
mysql> show profile ;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000114 |
| System lock | 0.000033 |
| Table lock | 0.000252 |
| query end | 0.000032 |
| freeing items | 0.000092 |
| logging slow query | 0.000030 |
| cleaning up | 0.000027 |
+--------------------+----------+
// 查看服务器状态
SHOW STATUS
mysql> SHOW STATUS LIKE 'Key%';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| Key_blocks_used | 14955 |
| Key_read_requests | 96854827 |
| Key_reads | 162040 |
| Key_write_requests | 7589728 |
| Key_writes | 3813196 |
+--------------------+----------+
mysql> show status like 'key%';
+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 1 |
| Key_blocks_used | 231960 |
| Key_read_requests | 385670920 |
| Key_reads | 852799 |
| Key_write_requests | 11466878 |
| Key_writes | 6729688 |
+------------------------+-----------+
7 rows in set (0.00 sec)
缓存命中率可以用key_reads/key_read_requests计算
mysql> show variables like '%_cache%';
+------------------------------+------------+
| Variable_name | Value |
+------------------------------+------------+
| binlog_cache_size | 32768 |
| have_query_cache | YES |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_binlog_cache_size | 4294963200 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| table_cache | 256 |
| thread_cache_size | 8 |
+------------------------------+------------+
------------------------------------------------
1、从文件装载数据到表中
你可以创建一个文本文件“ pet.txt ” ，每行包含一个记录，用定位符(tab) 把值分开，并且以CREATE TABLE 语句中列出的列次序给出。对于丢失的值( 例如未知的性别，或仍然活着的动物的死亡日期) ，你可以使用NULL 值。为了在你的文本文件中表示这些内容，使用/N （反斜线，字母N ） 。例如，Whistler 鸟的记录应为( 这里值之间的空白是一个定位符) ：
| name | owner | species | sex | birth | death |
| Whistler | Gwen | bird | /N | 1997-12-09 | /N |
要想将文本文件 “ pet.txt ” 装载到 pet 表中，使用这个命令：
mysql>
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
请注意如果用Windows 中的编辑器（使用/r/n 做为行的结束符） 创建文件，应使用：
mysql>
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
->
LINES TERMINATED BY '/r/n';
（在运行OS X 的Apple 机上，应使用行结束符'/r' 。）
为了生成适合导入的数据：
select * from pet into outfile "/home/chengyi/tmp.txt";
可以通过查看load data的帮助：
help load data;
2、按多个列进行排序
mysql>
SELECT name, species, birth FROM pet
->
ORDER BY species ASC, birth DESC;
3、NULL值
mysql>
SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
mysql>
SELECT 1 IS NULL, 1 IS NOT NULL;
mysql>
SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
实际上，NULL值不能拿来直接比较，没有别的值和它相等。如要测试某个值非NULL，应该：
WHERE death IS NOT NULL
4、查询最新的AUTO_INCREMENT 值
可以使用LAST_INSERT_ID() SQL 函数或mysql_insert_id() C API 函数。这些函数与具体连接有关，因此其返回值不会被其它执行插入功能的连接影响。
在php中有对应的函数mysql_insert_id，但是需要注意的是：
mysql_insert_id() will convert the return type of the native MySQL C API function mysql_insert_id() to a type of long (named int in PHP). If your AUTO_INCREMENT column has a column type of BIGINT (64 bits) the conversion may result in an incorrect value. Instead, use the internal MySQL SQL function LAST_INSERT_ID() in an SQL query. For more information about PHP's maximum integer values, please see the integer documentation.
如果插入多行，则返回插入的第一行的AUTO_INCREMENT 值。
5、storage engines
mysql> show engines;
+------------+---------+----------------------------------------------------------------+
| Engine | Support | Comment |
+------------+---------+----------------------------------------------------------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys |
| BerkeleyDB | NO | Supports transactions and page-level locking |
| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE | NO | Example storage engine |
| ARCHIVE | NO | Archive storage engine |
| CSV | NO | CSV storage engine |
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| FEDERATED | NO | Federated MySQL storage engine |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| ISAM | NO | Obsolete storage engine |
+------------+---------+----------------------------------------------------------------+
12 rows in set (0.00 sec)
MySQL数据表支持六种类型 ，分 别是：BDB、HEAP、ISAM、MERGE、MYISAM、InnoBDB，这六种又分为两类，单独一类是BDB，称为"事务安全型" (transaction-safe)，其余都属于第二类，称为“非事务安全型"(non-transaction-safe)。下面详细介绍这些表:
事务安全型
BDB 全称是"Brekeley DB",它是Mysql具有事务能力的表的类型，由Sleepycat Software （http://www.sleepycat.com ）开发。它提供了事务控制能力功能，它确保一组命令全部执行成功，或者当任何一个命令出现错误时所有命令的结果都被回退，可以想像在电子银行中事务控制能力是非常重要的。
非事务安全型
HEAP
HEAP表是MySQL表中访问最快的表，主要是由于这类表使用保存期在内存中的散列索引，但必须注意的是，如果MySQL或者服务器崩溃，表中数据全部丢失。
ISAM
ISAM表是MyISAM类型出现之前，MySQL表使用的默认类型，建议改用MyISAM。
MEREG
MEREG是一种值得关注的新式表，它是由一组MyISAM表组成，之所合并主要出于性能上考虑，因为它能够提高搜索速度，提高修复效率，节省磁盘空间。
MyISAM
MyISAM是MySQL表默认的类型，它是基于ISAM类型，但它增加了许多有用的扩展，下面是MyISAM一些优点：
1.比ISAM表更小，所占资源更少
2.可以在不同平台间二进制移植
表的类型在创建表时指定。在下面这个例子中我们创建了一个HEAP类型的表：
CODE
InnoBDB
这是最近加入的新表，具有以下特性：
事务处理机制
崩溃后能立即恢复
支持外键功能，级联删除
支持并发能力
在硬盘上的存储方式：InnoBDB frm
数据表的可移植性
通用方法：把数据表的内容导出到一个文本文件中，然后拷贝到硬盘上，导入数据库里面。就文件层次的操作来说，某些数据表是可以单独拷贝的看表
ISAM No
MyIASM Yes
BDB No
InnoBDB Yes
- >email char(55) NOT NULL,
- >name char(30) NOT NULL,
- >PRIMARY KEY(email) );
6、mysql数据库提供的程序
MySQL AB 提供了几种类型的程序：
· MYSQL 服务器和服务器启动脚本：
o mysqld 是MySQL 服务器
o mysqld_safe 、mysql.server 和mysqld_multi 是服务器启动脚本
o mysql_install_db 初始化数据目录和初始数据库
· 访问服务器的客户程序：
o mysql 是一个命令行客户程序，用于交互式或以批处理模式执行SQL 语句。
o mysqladmin 是用于管理功能的客户程序。
o mysqlcheck 执行表维护操作。
o mysqldump 和mysqlhotcopy 负责数据库备份。
o mysqlimport 导入数据文件。
o mysqlshow 显示信息数据库和表的相关信息。
在第8章：客户端和实用工具程序 中详细讨论了这些程序。
· 独立于服务器操作的工具程序：
o myisamchk 执行表维护操作。
o myisampack 产生压缩、只读的表。
o mysqlbinlog 是处理二进制日志文件的实用工具。
o perror 显示错误代码的含义。
以上程序支持的参数：
要想禁用列名，可以使用下面的形式来指定选项：
--disable-column-names
--skip-column-names
--column-names=0
--disable 和--skip 前缀与=0 后缀的效果相同：它们均关闭选项。
可以用下述方法“启用 ”选项：
--column-names
--enable-column-names
--column-names=1
如果选项有前缀--loose ，如果程序未识别出选项不会提示错误退出，但是会发出一条警告：
shell>
mysql --loose-no-such-option
mysql: WARNING: unknown option '--no-such-option'
比如：mysql --auto-rehash -u root -p -S /tmp/mysql.sock，这里就打开了auto-rehash。可以在mysql --help里查看支持的column的名字。
7、linux下mysql配置读取
在Unix
中，MySQL
程序从下面的文件读取启动选项：
文件名 | 目的 |
/etc/my.cnf | 全局选项 |
$MYSQL_HOME/my.cnf | 服务器相关选项 |
defaults-extra-file | 用--defaults-extra-file=path 指定的文件，如果有 |
~/.my.cnf | 用户相关选项 |
我们的开发机上mysql：
flykobe@105 quick $ ps aux|grep mysql
root 5297 0.0 0.0 5920 1152 ? S Jul16 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe
mysql 5320 0.3 0.5 271836 10404 ? Sl Jul16 538:17 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/tj1clnxweb0004.pid --skip-external-locking --port=3306 --socket=/tmp/mysql.sock
8、一些有用的统计信息命令
mysql> show variables; 会打印出当前数据库的配置信息，比如basedir、datadir等等。
9、mysql的log
| 日志文件 | 记入文件中的信息类型 |
| 错误日志 | 记录启动、运行或停止mysqld 时出 现的问题。 |
| 查询日志 | 记录建立的客户端连接和执行的语句。 |
| 更新日志 | 记录更改数据的语句。不赞成使用该日志。 |
| 二进制日志 | 记录所有更改数据的语句。还用于复制。 |
| 慢日志 | 记录所有执行时间超过long_query_time 秒的所有查询或不使用索引的查询。 |
可以通过查看mysql启动命令，找出log的位置；或者通过查看my.cnf；或者就是默认值了（datadir下）。
yicheng@tj1cksolodb01 /var/log/mysql $ ps axu|grep mysql
mysql 16771 0.1 0.6 329984 26728 ? Ssl Feb11 551:10 /usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf --basedir=/usr --datadir=/ms/var/mysqldata --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock
慢日志：还可以通过my.cnf中的配置，来指定那些sql语句属于“慢语句”
log-slow-queries
long_query_time = 3
10、mysql的查询缓存
http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#query-cache
查询缓存不返回旧的数据。当表更改后，查询缓存值的相关条目被清空。
如果你有许多mysqld 服务器更新相同的MyISAM 表，在这种情况下查询缓存不起作用。
但是有很多情况都会造成sql语句不被缓存，比如使用到类似curdate()之类的函数、使用系统表临时表、或者c代码中“被作为编写好的语句，即使没有使用占位符”。
通过以下命令查看cache相关的情况：
mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'query_cache_%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
5 rows in set (0.00 sec)
可以看到以上设置了query_cache_size为0，也就是禁用了缓存。默认情况下，是禁用的。
查看缓存使用情况：
mysql> show status like 'qcache%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
+-------------------------+-------+
8 rows in set (0.01 sec)
可以使用FLUSH QUERY CACHE 语句来清理查询缓存碎片以提高内存使用性能。该语句不从缓存中移出任何查询。
RESET QUERY CACHE 语句从查询缓存中移出所有查询。FLUSH TABLES 语句也执行同样的工作。
但是，从公司服务器上看到的结果是，我们没有使用mysql的高速缓存，为什么呢？
11、查看上一条sql句子的错误情况
mysql> SHOW WARNINGS;
12、mysql基准套件
在mysql源码的sql-bench目录下，有一些脚本，可以用来测试数据库性能等。但是我还没用过呢！
13、sql语句的优化方法
使用benchmark函数来查看多次运行一个sql句子的时间：BENCHMARK(loop_count ,expression )
mysql> select benchmark(1000000, 1+1);
+-------------------------+
| benchmark(1000000, 1+1) |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.02 sec)
991
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
