1.简介
MySQL是一个开放源码的小型关联式数据库管理系统，最初的开发者为瑞典MySQL AB公司。在2008年1月16号该公司被Sun公司收购，而2009年,SUN又被Oracal收购，因此现在的MySQL由Oracle公司管理和维护，但依然开放源代码。MySQL由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多互联网企业选择了MySQL来管理自己的数据，如Google、Facebook，百度，阿里巴巴（去IOE运动）、网易等。 
2.MySQL的命名机制
MySQL的命名机制使用由3个数字和一个后缀组成的版本号。例如，像mysql-5.6.14-beta的版本号这样解释：·第1个数字(5)是主版本号，描述了文件格式。所有版本5的发行都有相同的文件格式。
·第2个数字(6)是发行级别。主版本号和发行级别组合到一起便构成了发行序列号。
·第3个数字(14)是在此发行系列的版本号，随每个新分发版递增。
后缀显示发行的稳定性级别。通过一系列后缀显示如何改进稳定性。可能的后缀有：
·alpha表明发行包含大量未被彻底测试的新代码。
·beta意味着该版本功能是完整的，并且所有的新代码被测试了，没有增加重要的新特征，应该没有已知的缺陷。
·rc是发布代表；是一个发行了一段时间的beta版本，看起来应该运行正常。
·GA表明该版本已经在很多地方运行一段时间了，而且没有非平台特定的缺陷报告。只增加了关键漏洞修复修复。这就是我们称为一个产品（稳定）或“通用”版本的东西。
3.体系架构概述
先看一张图：
Server层包含连接池组件、管理服务和工具组件、SQL接口组件、分析器组件、优化器组件、缓冲组件等；存储引擎层是底层物理架构的实现，每个存储引擎开发者都可以按照自己的意愿开发。常用的存储引擎包括MyISAM、InnoDB、Memory等，这种插件式存储引擎是MySQL最大的特色。
4.MySQL的特性
（1）使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性 （2）支持AIX、FreeBSD、HP-UX、Linux、Mac OS、Novell Netware、OpenBSD、OS/2 Wrap、Solaris、Windows等多种操作系统
（3）为多种编程语言提供了API。这些编程语言包括C、C++、Eiffel、Java、Perl、PHP、Python、Ruby和Tcl等。
（4）支持多线程，充分利用CPU资源
（5）优化的SQL查询算法，有效地提高查询速度
（6）既能够作为一个单独的应用程序应用在客户端服务器网络环境中，也能够作为一个库而嵌入到其他的软件中提供多语言支持，常见的编码如中文的GB 2312、BIG5，日文的Shift_JIS等都可以用作数据表名和数据列名
（7）提供TCP/IP、ODBC和JDBC等多种数据库连接途径
（8）提供用于管理、检查、优化数据库操作的管理工具
（9）可以处理拥有上千万条记录的大型数据库
（10）支持多种存储引擎
5.MySQL的主要应用场景
（1）Web网站系统（2）日志记录系统
（3）数据仓库系统
（4）嵌入式系统
6.MySQL学习资料推荐
（1）《小辉作品：MySQL零基础入门系列培训40讲全集》该视频教程可以从网上找到下载，也可以到播布客的网站（http://www.boobooke.com/）去购买或者在线观看，个人认为讲的很不错，比较适合入门。
（2）《高性能MySQL（第三版）》英文版或者中文版，卓越、京东、当当上都有。此书为MySQL的经典书籍。
（3）《MySQL技术内幕:InnoDB存储引擎(第2版)》，国内的一个DBA写的。
（4）《深入理解MySQL》《深入理解MySQL核心技术 》，涉及到MySQL的一些源码修改和架构剖析。
1.MySQL基本命令
（1）启动或者重启MySQL·使用 service 启动：service mysql start（restart）如果不行就用service mysqld start（restart）
·使用 mysqld 脚本启动：/etc/init.d/mysql start（restart）如果不行就用/etc/init.d/mysqld start（restart）
·使用 safe_mysqld 启动：safe_mysqld &（推荐，安全启动，如果mysqld停止了，这种启动方式会自动重启）
（2）关闭MySQL服务
·使用 service 启动：service mysql stop如果不行就用service mysqld stop
·使用 mysqld 脚本启动：/etc/inint.d/mysql stop如果不行就用/etc/inint.d/mysqld stop
·# mysqladmin shutdown
（3）登录MySQL
语法： mysql -h机器IP -u用户名 -p用户密码
注意，如果是连接到另外的机器上，则需要加入一个参数-h机器IP，如若是本地机器，则直接键入命令mysql -uroot -p， 回车后提示你输入密码，输完密码之后回车即可进入到mysql中了，mysql的提示符是：
mysql>
（4）退出MySQL
语法：exit或者quit
（5）获得当前MySQL的状态
语法：status
（6）查看当前MySQL的版本
语法：select version();
（7）执行系统命令
语法：system 系统命令，如mysql>system clear;该命令清屏
（8）显示数据库列表
语法：show databases;
（9）选择某个数据库
语法：use 数据库名;
（10）显示库中的数据表
语法：show tables;该命令必须选择了某个数据库（use dbname）才能执行有效。
（11）显示数据表的结构
语法：desc 表名;或者show [full] columns from 表名
（12）创建和删除数据库
语法：create database 数据库名;drop database 数据库名;
（13）创建和删除表
语法：create table 表名(字段列表);drop table 表名;
（14）清空表
语法：delete from 表名;truncate table 表名;
（15）查看当前数据库
语法：select database();
（16）更新表中内容
语法：update 表名 set 列名 = XXX where 条件;
（17）添加数据
语法：insert into 表名（列1，列2，……） values(值1，值2，……);注意：列和值必须对应
（18）MySQL备份
语法：#mysqldump -u 用户名 -p 密码 数据库名 表名 > 文件名.sql
若要备份整个数据库使用：mysqldump -u 用户名 -p 密码 --all-database > 文件名.sql
（19）MySQL回复
语法：source 文件名.sql
（20）查看和修改变量
语法：show [session | global] variables like '%test%' ,单引号内可以使用通配符
语法：set [session | global] 变量名 = XXX;
（21）查看MySQL启用的存储引擎
语法：show engines;
（22）查看表状态
语法： show table status from 数据库名 where name='表名';
2、MySQL数据类型
MySQL的数据类型包括四种：数值类型，字符串类型，时间和日期类型，复合类型。数值类型包括整数类型和浮点数类型，其中整数类型有：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT，浮点数类型有：FLOAT、DOUBLE、DECIMAL。字符串类型包括：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB。时间和日期类型包括：Date、DateTime、TimeStamp、Time、Year。
复合类型主要包括：ENUM和SET。
数值类型
MySQL 的数值数据类型可以大致划分为两个类别，一个是整数，另一个是浮点数或小数。许多不同的子类型对这些类别中的每一个都是可用的，每个子类型支持不同大小的数据，并且 MySQL 允许我们指定数值字段中的值是否有正负之分或者用零填补。
表列出了各种数值类型以及它们的允许范围和占用的内存空间。
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
| TINYINT | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 字节 | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 字节 | (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 字节 | (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
INT 类型
在 MySQL 中支持的 5 个主要整数类型是 TINYINT，SMALLINT，MEDIUMINT，INT 和 BIGINT。这些类型在很大程度上是相同的，只有它们存储的值的大小是不相同的。
MySQL 以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展，这样当从数据库检索一个值时，可以把这个值加长到指定的长度。例如，指定一个字段的类型为 INT(6)，就可以保证所包含数字少于 6 个的值从数据库中检索出来时能够自动地用空格填充。需要注意的是，使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。
万一我们需要对一个字段存储一个超出许可范围的数字，MySQL 会根据允许范围最接近它的一端截短后再进行存储。还有一个比较特别的地方是，MySQL 会在不合规定的值插入表前自动修改为 0。
UNSIGNED 修饰符规定字段只保存正值。因为不需要保存数字的正、负符号，可以在储时节约一个“位”的空间。从而增大这个字段可以存储的值的范围。
ZEROFILL 修饰符规定 0（不是空格）可以用来真补输出的值。使用这个修饰符可以阻止 MySQL 数据库存储负值。
浮点类型
MySQL 支持的三个浮点类型是 FLOAT、DOUBLE 和 DECIMAL 类型。FLOAT 数值类型用于表示单精度浮点数值，而 DOUBLE 数值类型用于表示双精度浮点数值。
与整数一样，这些类型也带有附加参数：一个显示宽度指示器和一个小数点指示器。比如语句 FLOAT(7,3) 规定显示的值不会超过 7 位数字，小数点后面带有 3 位数字。
对于小数点后面的位数超过允许范围的值，MySQL 会自动将它四舍五入为最接近它的值，再插入它。
DECIMAL 数据类型用于精度要求非常高的计算中，这种类型允许指定数值的精度和计数方法作为选择参数。精度在这里指为这个值保存的有效数字的总个数，而计数方法表示小数点后数字的位数。比如语句 DECIMAL(7,3) 规定了存储的值不会超过 7 位数字，并且小数点后不超过 3 位。
忽略 DECIMAL 数据类型的精度和计数方法修饰符将会使 MySQL 数据库把所有标识为这个数据类型的字段精度设置为 10，计算方法设置为 0。
UNSIGNED 和 ZEROFILL 修饰符也可以被 FLOAT、DOUBLE 和 DECIMAL 数据类型使用。并且效果与 INT 数据类型相同。
字符串类型
MySQL 提供了 8 个基本的字符串类型，可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据。
| 类型 | 大小 | 用途 |
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-255字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LOGNGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
CHAR 和 VARCHAR 类型
CHAR 类型用于定长字符串，并且必须在圆括号内用一个大小修饰符来定义。这个大小修饰符的范围从 0-255。比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补。
CHAR 类型可以使用 BINARY 修饰符。当用于比较运算时，这个修饰符使 CHAR 以二进制方式参于运算，而不是以传统的区分大小写的方式。
CHAR 类型的一个变体是 VARCHAR 类型。它是一种可变长度的字符串类型，并且也必须带有一个范围在 0-255 之间的指示器。CHAR 和 VARCHGAR 不同之处在于 MuSQL 数据库处理这个指示器的方式：CHAR 把这个大小视为值的大小，不长度不足的情况下就用空格补足。而 VARCHAR 类型把它视为最大值并且只使用存储字符串实际需要的长度（增加一个额外字节来存储字符串本身的长度）来存储值。所以短于指示器长度的 VARCHAR 类型不会被空格填补，但长于指示器的值仍然会被截短。
因为 VARCHAR 类型可以根据实际内容动态改变存储值的长度，所以在不能确定字段需要多少字符时使用 VARCHAR 类型可以大大地节约磁盘空间、提高存储效率。
VARCHAR 类型在使用 BINARY 修饰符时与 CHAR 类型完全相同。
TEXT 和 BLOB 类型
对于字段长度要求超过 255 个的情况下，MySQL 提供了 TEXT 和 BLOB 两种类型。根据存储数据的大小，它们都有不同的子类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型。
TEXT 和 BLOB 类型在分类和比较上存在区别。BLOB 类型区分大小写，而 TEXT 不区分大小写。大小修饰符不用于各种 BLOB 和 TEXT 子类型。比指定类型支持的最大范围大的值将被自动截短。
日期和时间类型
在处理日期和时间类型的值时，MySQL 带有 5 个不同的数据类型可供选择。它们可以被分成简单的日期、时间类型，和混合日期、时间类型。根据要求的精度，子类型在每个分类型中都可以使用，并且 MySQL 带有内置功能可以把多样化的输入格式变为一个标准格式。
| 类型 | 大小 (字节) | 范围 | 格式 | 用途 |
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 8 | 1970-01-01 00:00:00/2037 年某时 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
DATE、TIME 和 TEAR 类型
MySQL 用 DATE 和 TEAR 类型存储简单的日期值，使用 TIME 类型存储时间值。这些类型可以描述为字符串或不带分隔符的整数序列。如果描述为字符串，DATE 类型的值应该使用连字号作为分隔符分开，而 TIME 类型的值应该使用冒号作为分隔符分开。
需要注意的是，没有冒号分隔符的 TIME 类型值，将会被 MySQL 理解为持续的时间，而不是时间戳。
MySQL 还对日期的年份中的两个数字的值，或是 SQL 语句中为 TEAR 类型输入的两个数字进行最大限度的通译。因为所有 TEAR 类型的值必须用 4 个数字存储。MySQL 试图将 2 个数字的年份转换为 4 个数字的值。把在 00-69 范围内的值转换到 2000-2069 范围内。把 70-99 范围内的值转换到 1970-1979 之内。如果 MySQL 自动转换后的值并不符合我们的需要，请输入 4 个数字表示的年份。
DATEYIME 和 TIMESTAMP 类型
除了日期和时间数据类型，MySQL 还支持 DATEYIME 和 TIMESTAMP 这两种混合类型。它们可以把日期和时间作为单个的值进行存储。这两种类型通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。
如果我们对 TIMESTAMP 类型的字段没有明确赋值，或是被赋与了 null 值。MySQL 会自动使用系统当前的日期和时间来填充它。
复合类型
MySQL 还支持两种复合数据类型 ENUM 和 SET，它们扩展了 SQL 规范。虽然这些类型在技术上是字符串类型，但是可以被视为不同的数据类型。一个 ENUM 类型只允许从一个集合中取得一个值；而 SET 类型允许从一个集合中取得任意多个值。
ENUM 类型
ENUM 类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排拆的数据时容易让人理解，比如人类的性别。ENUM 类型字段可以从集合中取得一个值或使用 null 值，除此之外的输入将会使 MySQL 在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配，MySQL 会自动使用插入值的大小写转换成与集合中大小写一致的值。
ENUM 类型在系统内部可以存储为数字，并且从 1 开始用数字做索引。一个 ENUM 类型最多可以包含 65536 个元素，其中一个元素被 MySQL 保留，用来存储错误信息，这个错误值用索引 0 或者一个空字符串表示。
MySQL 认为 ENUM 类型集合中出现的值是合法输入，除此之外其它任何输入都将失败。这说明通过搜索包含空字符串或对应数字索引为 0 的行就可以很容易地找到错误记录的位置。
SET 类型
SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。并且与 ENUM 类型相同的是任何试图在 SET 类型字段中插入非预定义的值都会使 MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录，MySQL 将会保留合法的元素，除去非法的元素。
一个 SET 类型最多可以包含 64 项元素。在 SET 元素中值被存储为一个分离的“位”序列，这些“位”表示与它相对应的元素。“位”是创建有序元素集合的一种简单而有效的方式。并且它还去除了重复的元素，所以 SET 类型中不可能包含两个相同的元素。希望从 SET 类型字段中找出非法的记录只需查找包含空字符串或二进制值为 0 的行。
MySQL的日志是记录和系统相关的一些操作信息，其有不同类型的日志文件(各自存储了不同类型的日志)，从它们当中可以查询到MYSQL里都做了些什么，对于MYSQL的管理工作，这些日志文件是不可缺少的，主要包括错误日志、慢查询日志、二进制日志和InnoDB的redo日志等。
一、错误日志(Error Log)
错误日志，顾名思义，当然就是记录错误信息的日志，不过MySQL的错误日志文件，并不仅仅是记录错误信息，MySQL服务进程启动/关闭的信息也会被记录进来，也不是说什么错误都会记录，只有服务进程运行过程中发生的关键(critical)错误会被记录，另外mysqld进程发现某些表需要自动检查或修复的话，也会抛出相关信息到该日志文件。在某些操作系统上运行的mysql崩溃时，会将堆栈的跟踪信息(stack trace)抛出到错误日志文件中，这些跟踪信息比较有利于故障排查。
启用mysqld时附加--log-error参数(或配置log-error系统环境变量)，指定错误日志的路径及文件名，如不指定的话，默认文件名为[host_name].err，保存在mysql的data文件夹下。执行FLUSH LOGS命令后，mysql会将当前错误日志文件附加-old保存，而且创建一个新的空错误日志文件(仅限指定--log-error的情况下会自动创建新文件)。--log-warnings参数(或log-warnings系统环境变量)用来控制警告信息是否记录，默认值为1即启用，指定为0时表示禁用。如果指定该参数值大于1，则连接失败的消息也会写入错误日志。
使用mysqld_safe命令启动mysqld，mysqld_safe会将错误消息写到日志文件或者系统日志，在5.1.20版本之前，mysqld_safe的操作记录到文件；从5.1.20版本开始，mysqld_safe提供了两个新增错误日志选项：--syslog和--skip-syslog。从5.1.21版本开始，默认的不记录日志选项为--skip-syslog，该选项兼容5.1.20版本之前的错误日志行为。要明确指定输出的错误日志文件，就通过mysqld_safe启动服务时附加--log-error[=file_name]参数，如果要使用syslog，则指定--syslog参数。 对于记录到syslog的消息，来自mysqld_safe和mysqld的消息会分别打上"mysqld_safe"或"mysqld"的标签，从5.1.21版本开始，还可以通过--syslog-tag=[tag]的方式指定标签的名称，修改后实际记录的标签形式会变成"mysql_safe-[tag]"和"mysqld-[tag]"。
如果是使用mysqld_safe命令启动mysqld进程，并且mysqld意外崩溃，则mysqld_safe将尝试重新启动mysqld进程，并且向错误日志中记录"restarted mysqld"信息。
查看错误日志的位置：
- mysql> show variables like 'log_error';
- +---------------+----------------------------+
- | Variable_name | Value |
- +---------------+----------------------------+
- | log_error | /var/lib/mysql/sl62-vm.err |
- +---------------+----------------------------+
- 1 row in set (0.00 sec)
- # errot log path
- log_error=/var/lib/mysql/umt.err
此时再次查看错误日志：
- mysql> show variables like 'log_error';
- +---------------+------------------------+
- | Variable_name | Value |
- +---------------+------------------------+
- | log_error | /var/lib/mysql/umt.err |
- +---------------+------------------------+
- 1 row in set (0.00 sec)
二、慢查询日志（Slow Query Log）
慢查询日志用来记录所有执行时间超过参数long_query_time的SQL语句的日志文件，获得初始表锁定的时间不算作执行时间。慢查询日志可以用来找到执行时间长的查询，可以用于优化，通过命令 show variables like 'long_query_time';来查看参数long_query_time的值：
- mysql> show variables like 'long_query_time';
- +-----------------+-----------+
- | Variable_name | Value |
- +-----------------+-----------+
- | long_query_time | 10.000000 |
- +-----------------+-----------+
- 1 row in set (0.00 sec)
- mysql> set long_query_time = 1;
- Query OK, 0 rows affected (0.00 sec)
- 再次查询：
- mysql> show variables like 'long_query_time';
- +-----------------+----------+
- | Variable_name | Value |
- +-----------------+----------+
- | long_query_time | 1.000000 |
- +-----------------+----------+
- 1 row in set (0.00 sec)
使用选项--slow_query_log={ON|OFF}来停止或者启动慢查询日志，系统默认是不启动该日志的，可以使用命令show variables like 'log_slow_queries';来查看慢查询日志是否启动：
- mysql> show variables like 'log_slow_queries';
- +------------------+-------+
- | Variable_name | Value |
- +------------------+-------+
- | log_slow_queries | OFF |
- +------------------+-------+
- 1 row in set (0.00 sec)
- 开启慢查询日志
- mysql> set global log_slow_queries = ON;
- Query OK, 0 rows affected, 1 warning (0.02 sec)
- 由于log_slow_queries是全局变量，所以要加上global关键字
- 再次查询：
- mysql> show variables like 'log_slow_queries';
- +------------------+-------+
- | Variable_name | Value |
- +------------------+-------+
- | log_slow_queries | ON |
- +------------------+-------+
- 1 row in set (0.00 sec)
语句执行完并且所有锁释放后记入慢查询日志。记录顺序可以与执行顺序不相同。
慢查询日志可以用来找到执行时间长的查询语句，并对其进行优化，可以使用mysqldumpslow命令获得日志中显示的查询摘要来处理慢查询日志。
慢查询日志中，不使用索引的慢查询同使用索引的查询一样记录。通过选项--log-queries-not-using-indexes，可以防止不使用索引的慢查询记入慢查询日志。通过选项--log-slow-admin-statements，可以请求将慢管理语句，例如OPTIMIZE TABLE、ANALYZE TABLE和 ALTER TABLE写入慢查询日志。通过选项--log-slow-slave-statements，可以将复制查询的语句记入慢查询日志。
mysql prepare 存储过程使用
语法
- PREPARE statement_name FROM sql_text /*定义*/
- EXECUTE statement_name [USING variable [,variable...]] /*执行预处理语句*/
- DEALLOCATE PREPARE statement_name /*删除定义*/
例
- mysql> PREPARE prod FROM "INSERT INTO examlple VALUES(?,?)";
- mysql> SET @p='1';
- mysql> SET @q='2';
- mysql> EXECUTE prod USING @p,@q;
- mysql> SET @name='3';
- mysql> EXECUTE prod USING @p,@name;
- mysql> DEALLOCATE PREPARE prod;
1.用变量做表名： 简单的用set或者declare语句定义变量，然后直接作为sql的表名是不行的，mysql会把变量名当作表名。在其他的sql数据库中也是如 此，mssql的解决方法是将整条sql语句作为变量，其中穿插变量作为表名，然后用sp_executesql调用该语句。 这在mysql5.0之前是不行的，5.0之后引入了一个全新的语句，可以达到类似sp_executesql的功能（仅对procedure有 效，function不支持动态查询）：
PREPARE stmt_name FROM preparable_stmt;
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
{DEALLOCATE | DROP} PREPARE stmt_name;
为了有一个感性的认识，下面先给几个小例子：
- mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
- mysql> SET @a = 3;
- mysql> SET @b = 4;
- mysql> EXECUTE stmt1 USING @a, @b;
- +------------+
- | hypotenuse |
- +------------+
- | 5 |
- +------------+
- mysql> DEALLOCATE PREPARE stmt1;
- mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
- mysql> PREPARE stmt2 FROM @s;
- mysql> SET @a = 6;
- mysql> SET @b = 8;
- mysql> EXECUTE stmt2 USING @a, @b;
- +------------+
- | hypotenuse |
- +------------+
- | 10 |
- +------------+
- mysql> DEALLOCATE PREPARE stmt2;
如果你的MySQL 版本是 5.0.7 或者更高的，你还可以在 LIMIT 子句中使用它，示例如下：
- mysql> SET @a=1;
- mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";
- mysql> EXECUTE STMT USING @a;
- mysql> SET @skip=1; SET @numrows=5;
- mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";
- mysql> EXECUTE STMT USING @skip, @numrows;
使用 PREPARE 的几个注意点：
A：PREPARE stmt_name FROM preparable_stmt;预定义一个语句，并将它赋给 stmt_name ，tmt_name 是不区分大小写的。
B： 即使 preparable_stmt 语句中的 ? 所代表的是一个字符串，你也不需要将 ? 用引号包含起来。
C： 如果新的 PREPARE 语句使用了一个已存在的 stmt_name ，那么原有的将被立即释放！ 即使这个新的 PREPARE 语句因为错误而不能被正确执行。
D： PREPARE stmt_name 的作用域是当前客户端连接会话可见。
E： 要释放一个预定义语句的资源，可以使用 DEALLOCATE PREPARE 句法。
F： EXECUTE stmt_name 句法中，如果 stmt_name 不存在，将会引发一个错误。
G： 如果在终止客户端连接会话时，没有显式地调用 DEALLOCATE PREPARE 句法释放资源，服务器端会自己动释放它。
H： 在预定义语句中，CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, 和大部分的 SHOW 句法被支持。
I： PREPARE 语句不可以用于存储过程，自定义函数！但从 MySQL 5.0.13 开始，它可以被用于存储过程，仍不支持在函数中使用！
下面给个示例：
- CREATE PROCEDURE `p1`(IN id INT UNSIGNED,IN name VARCHAR(11))
- BEGIN lable_exit:
- BEGIN
- SET @SqlCmd = 'SELECT * FROM tA ';
- IF id IS NOT NULL THEN
- SET @SqlCmd = CONCAT(@SqlCmd , 'WHERE id=?');
- PREPARE stmt FROM @SqlCmd;
- SET @a = id;
- EXECUTE stmt USING @a;
- LEAVE lable_exit;
- END IF;
- IF name IS NOT NULL THEN
- SET @SqlCmd = CONCAT(@SqlCmd , 'WHERE name LIKE ?');
- PREPARE stmt FROM @SqlCmd;
- SET @a = CONCAT(name, '%');
- EXECUTE stmt USING @a;
- LEAVE lable_exit;
- END IF;
- END lable_exit;
- END;
- CALL `p1`(1,NULL);
- CALL `p1`(NULL,'QQ');
- DROP PROCEDURE `p1`;
了解了PREPARE的用法，再用变量做表名就很容易了。不过在实际操作过程中还发现其他一些问题，比如变量定义，declare变量和set @var=value变量的用法以及参数传入的变量。
测试后发现，set @var=value这样定义的变量直接写在字符串中就会被当作变量转换，declare的变量和参数传入的变量则必须用CONCAT来连接。具体的原理没有研究。
EXECUTE stmt USING @a;这样的语句USING后面的变量也只能用set @var=value这种，declare和参数传入的变量不行。
mysql存储过程详解
1. 存储过程简介
我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。
存储过程通常有以下优点：
(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
(4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。
(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。
2. 关于MySQL的存储过程
存储过程是数据库存储的一个重要的功能，但是MySQL在5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。
3. MySQL存储过程的创建
(1). 格式
MySQL存储过程创建的格式：CREATE PROCEDURE 过程名 ([过程参数[,...]])
[特性 ...] 过程体
这里先举个例子：
- mysql> DELIMITER //
- mysql> CREATE PROCEDURE proc1(OUT s int)
- -> BEGIN
- -> SELECT COUNT(*) INTO s FROM user;
- -> END
- -> //
- mysql> DELIMITER ;
（1）这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
（2）存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用","分割开。
（3）过程体的开始与结束使用BEGIN与END进行标识。
这样，我们的一个MySQL存储过程就完成了，是不是很容易呢?看不懂也没关系，接下来，我们详细的讲解。
(2). 声明分割符
其实，关于声明分割符，上面的注解已经写得很清楚，不需要多说，只是稍微要注意一点的是：如果是用MySQL的Administrator管理工具时，可以直接创建，不再需要声明。
(3). 参数
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...])
IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数:该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数:调用时指定，并且可被改变和返回
Ⅰ. IN参数例子
创建:
- mysql > DELIMITER //
- mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)
- -> BEGIN
- -> SELECT p_in;
- -> SET p_in=2;
- -> SELECT p_in;
- -> END;
- -> //
- mysql > DELIMITER ;
执行结果:
- mysql > SET @p_in=1;
- mysql > CALL demo_in_parameter(@p_in);
- +------+
- | p_in |
- +------+
- | 1 |
- +------+
- +------+
- | p_in |
- +------+
- | 2 |
- +------+
- mysql> SELECT @p_in;
- +-------+
- | @p_in |
- +-------+
- | 1 |
- +-------+
以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值
Ⅱ.OUT参数例子
创建:
- mysql > DELIMITER //
- mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)
- -> BEGIN
- -> SELECT p_out;
- -> SET p_out=2;
- -> SELECT p_out;
- -> END;
- -> //
- mysql > DELIMITER ;
执行结果:
- mysql > SET @p_out=1;
- mysql > CALL sp_demo_out_parameter(@p_out);
- +-------+
- | p_out |
- +-------+
- | NULL |
- +-------+
- +-------+
- | p_out |
- +-------+
- | 2 |
- +-------+
- mysql> SELECT @p_out;
- +-------+
- | p_out |
- +-------+
- | 2 |
- +-------+
Ⅲ. INOUT参数例子
创建:
- mysql > DELIMITER //
- mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)
- -> BEGIN
- -> SELECT p_inout;
- -> SET p_inout=2;
- -> SELECT p_inout;
- -> END;
- -> //
- mysql > DELIMITER ;
执行结果 :
- mysql > SET @p_inout=1;
- mysql > CALL demo_inout_parameter(@p_inout) ;
- +---------+
- | p_inout |
- +---------+
- | 1 |
- +---------+
- +---------+
- | p_inout |
- +---------+
- | 2 |
- +---------+
- mysql > SELECT @p_inout;
- +----------+
- | @p_inout |
- +----------+
- | 2 |
- +----------+
(4). 变量
Ⅰ. 变量定义
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
其中，datatype为MySQL的数据类型，如:int, float, date, varchar(length)
例如:
- DECLARE l_int int unsigned default 4000000;
- DECLARE l_numeric number(8,2) DEFAULT 9.95;
- DECLARE l_date date DEFAULT '1999-12-31';
- DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';
- DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';
Ⅱ. 变量赋值
SET 变量名 = 表达式值 [,variable_name = expression ...]
Ⅲ. 用户变量
ⅰ. 在MySQL客户端使用用户变量
- mysql > SELECT 'Hello World' into @x;
- mysql > SELECT @x;
- +-------------+
- | @x |
- +-------------+
- | Hello World |
- +-------------+
- mysql > SET @y='Goodbye Cruel World';
- mysql > SELECT @y;
- +---------------------+
- | @y |
- +---------------------+
- | Goodbye Cruel World |
- +---------------------+
- mysql > SET @z=1+2+3;
- mysql > SELECT @z;
- +------+
- | @z |
- +------+
- | 6 |
- +------+
ⅱ. 在存储过程中使用用户变量
- mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
- mysql > SET @greeting='Hello';
- mysql > CALL GreetWorld( );
- +----------------------------+
- | CONCAT(@greeting,' World') |
- +----------------------------+
- | Hello World |
- +----------------------------+
ⅲ . 在存储过程间传递全局范围的用户变量
- mysql> CREATE PROCEDURE p1() SET @last_procedure='p1';
- mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc);
- mysql> CALL p1( );
- mysql> CALL p2( );
- +-----------------------------------------------+
- | CONCAT('Last procedure was ',@last_proc |
- +-----------------------------------------------+
- | Last procedure was p1 |
- +-----------------------------------------------+
注意:
①用户变量名一般以@开头
②滥用用户变量会导致程序难以理解及管理
(5). 注释
MySQL存储过程可使用两种风格的注释
双模杠：--
该风格一般用于单行注释
c风格： 一般用于多行注释
例如：
- mysql > DELIMITER //
- mysql > CREATE PROCEDURE proc1 --name存储过程名
- -> (IN parameter1 INTEGER)
- -> BEGIN
- -> DECLARE variable1 CHAR(10);
- -> IF parameter1 = 17 THEN
- -> SET variable1 = 'birds';
- -> ELSE
- -> SET variable1 = 'beasts';
- -> END IF;
- -> INSERT INTO table1 VALUES (variable1);
- -> END
- -> //
- mysql > DELIMITER ;
4. MySQL存储过程的调用
用call和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。具体的调用方法可以参看上面的例子。
5. MySQL存储过程的查询
我们像知道一个数据库下面有那些表，我们一般采用show tables;进行查看。那么我们要查看某个数据库下面的存储过程，是否也可以采用呢？答案是，我们可以查看某个数据库下面的存储过程，但是是令一钟方式。
我们可以用
select name from mysql.proc where db=’数据库名’;
或者
select routine_name from information_schema.routines where routine_schema='数据库名';
或者
show procedure status where db='数据库名';
进行查询。
如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用describe 表名进行查看呢？
答案是：我们可以查看存储过程的详细，但是需要用另一种方法：
SHOW CREATE PROCEDURE 数据库.存储过程名;
就可以查看当前存储过程的详细。
6. MySQL存储过程的修改
ALTER PROCEDURE
更改用CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。
7. MySQL存储过程的删除
删除一个存储过程比较简单，和删除表一样：
DROP PROCEDURE
从MySQL的表格中删除一个或多个存储过程。
8. MySQL存储过程的控制语句
(1). 变量作用域
内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储
过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派
给会话变量来保存其值。
- mysql > DELIMITER //
- mysql > CREATE PROCEDURE proc3()
- -> begin
- -> declare x1 varchar(5) default 'outer';
- -> begin
- -> declare x1 varchar(5) default 'inner';
- -> select x1;
- -> end;
- -> select x1;
- -> end;
- -> //
- mysql > DELIMITER ;
(2). 条件语句
Ⅰ. if-then -else语句
- mysql > DELIMITER //
- mysql > CREATE PROCEDURE proc2(IN parameter int)
- -> begin
- -> declare var int;
- -> set var=parameter+1;
- -> if var=0 then
- -> insert into t values(17);
- -> end if;
- -> if parameter=0 then
- -> update t set s1=s1+1;
- -> else
- -> update t set s1=s1+2;
- -> end if;
- -> end;
- -> //
- mysql > DELIMITER ;
Ⅱ. case语句：
- mysql > DELIMITER //
- mysql > CREATE PROCEDURE proc3 (in parameter int)
- -> begin
- -> declare var int;
- -> set var=parameter+1;
- -> case var
- -> when 0 then
- -> insert into t values(17);
- -> when 1 then
- -> insert into t values(18);
- -> else
- -> insert into t values(19);
- -> end case;
- -> end;
- -> //
- mysql > DELIMITER ;
(3). 循环语句
Ⅰ. while ···· end while：
- mysql > DELIMITER //
- mysql > CREATE PROCEDURE proc4()
- -> begin
- -> declare var int;
- -> set var=0;
- -> while var<6 do
- -> insert into t values(var);
- -> set var=var+1;
- -> end while;
- -> end;
- -> //
- mysql > DELIMITER ;
Ⅱ. repeat···· end repeat：
它在执行操作后检查结果，而while则是执行前进行检查。
- mysql > DELIMITER //
- mysql > CREATE PROCEDURE proc5 ()
- -> begin
- -> declare v int;
- -> set v=0;
- -> repeat
- -> insert into t values(v);
- -> set v=v+1;
- -> until v>=5
- -> end repeat;
- -> end;
- -> //
- mysql > DELIMITER ;
Ⅲ. loop ·····end loop:
loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。
- mysql > DELIMITER //
- mysql > CREATE PROCEDURE proc6 ()
- -> begin
- -> declare v int;
- -> set v=0;
- -> LOOP_LABLE:loop
- -> insert into t values(v);
- -> set v=v+1;
- -> if v >=5 then
- -> leave LOOP_LABLE;
- -> end if;
- -> end loop;
- -> end;
- -> //
- mysql > DELIMITER ;
Ⅳ. LABLES 标号：
标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。
(4). ITERATE迭代
Ⅰ. ITERATE:
通过引用复合语句的标号,来从新开始复合语句
- mysql > DELIMITER //
- mysql > CREATE PROCEDURE proc10 ()
- -> begin
- -> declare v int;
- -> set v=0;
- -> LOOP_LABLE:loop
- -> if v=3 then
- -> set v=v+1;
- -> ITERATE LOOP_LABLE;
- -> end if;
- -> insert into t values(v);
- -> set v=v+1;
- -> if v>=5 then
- -> leave LOOP_LABLE;
- -> end if;
- -> end loop;
- -> end;
- -> //
- mysql > DELIMITER ;
9. MySQL存储过程的基本函数
(1).字符串类
CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1
- mysql> select substring('abcd',0,2);
- +-----------------------+
- | substring('abcd',0,2) |
- +-----------------------+
- | |
- +-----------------------+
- 1 row in set (0.00 sec)
- mysql> select substring('abcd',1,2);
- +-----------------------+
- | substring('abcd',1,2) |
- +-----------------------+
- | ab |
- +-----------------------+
- 1 row in set (0.02 sec)
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格
(2).数学类
ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
注：返回类型并非均为整数，如：
(1)默认变为整形值
- mysql> select round(1.23);
- +-------------+
- | round(1.23) |
- +-------------+
- | 1 |
- +-------------+
- 1 row in set (0.00 sec)
- mysql> select round(1.56);
- +-------------+
- | round(1.56) |
- +-------------+
- | 2 |
- +-------------+
- 1 row in set (0.00 sec)
(2)可以设定小数位数，返回浮点型数据
- mysql> select round(1.567,2);
- +----------------+
- | round(1.567,2) |
- +----------------+
- | 1.57 |
- +----------------+
- 1 row in set (0.00 sec)
SIGN (number2 ) //
(3).日期时间类
ADDTIME (date2 ,time_interval ) // 将 time_interval 加到 date2CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) // 转换时区
CURRENT_DATE ( ) // 当前日期
CURRENT_TIME ( ) // 当前时间
CURRENT_TIMESTAMP ( ) // 当前时间戳
DATE (datetime ) // 返回 datetime 的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) // 在 date2 中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) // 使用 formatcodes 格式显示 datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) // 在 date2 上减去一个时间
DATEDIFF (date1 ,date2 ) // 两个日期差
DAY (date ) // 返回日期的天
DAYNAME (date ) // 英文星期
DAYOFWEEK (date ) // 星期 (1-7) ,1 为星期天
DAYOFYEAR (date ) // 一年中的第几天
EXTRACT (interval_name FROM date ) // 从 date 中提取日期的指定部分
MAKEDATE (year ,day ) // 给出年及年中的第几天 , 生成日期串
MAKETIME (hour ,minute ,second ) // 生成时间串
MONTHNAME (date ) // 英文月份名
NOW ( ) // 当前时间
SEC_TO_TIME (seconds ) // 秒数转成时间
STR_TO_DATE (string ,format ) // 字串转成时间 , 以 format 格式显示
TIMEDIFF (datetime1 ,datetime2 ) // 两个时间差
TIME_TO_SEC (time ) // 时间转秒数 ]
WEEK (date_time [,start_of_week ]) // 第几周
YEAR (datetime ) // 年份
DAYOFMONTH(datetime) // 月的第几天
HOUR(datetime) // 小时
LAST_DAY(date) //date 的月的最后日期
MICROSECOND(datetime) // 微秒
MONTH(datetime) // 月
MINUTE(datetime) // 分 返回符号 , 正负或 0
SQRT(number2) //开平方
mysql数据库导入方法
1）.mysqlimport的语法介绍：
mysqlimport位于mysql/bin目录中，是mysql的一个载入（或者说导入）数据的一个非常有效的工具。这是一个命令行工具。有两个参数 以及大量的选项可供选择。这个工具把一个文本文件（text file）导入到你指定的数据库和表中。比方说我们要从文件Customers.txt中把数据导入到数据库Meet_A_Geek中的表 Custermers中：
mysqlimport Meet_A_Geek Customers.txt
注意：这里Customers.txt是我们要导入数据的文本文件，而Meet_A_Geek是我们要操作的数据库，数据库中的表名是Customers，这里文本文件的数据格式必须与Customers表中的记录格式一致，否则mysqlimport命令将会出错。
其中表的名字是导入文件的第一个句号（.）前面文件字符串，另外一个例子：
mysqlimport Meet_A_Geek Cus.to.mers.txt
那么我们将把文件中的内容导入到数据库Meet_A_Geek 中的Cus表中。
上面的例子中，都只用到两个参数，并没有用到更多的选项，下面介绍mysqlimport的选项
2）.mysqlimport的常用选项介绍:
选项 功能
-d or --delete 新数据导入数据表中之前删除数据数据表中的所有信息
-f or --force 不管是否遇到错误，mysqlimport将强制继续插入数据
-i or --ignore mysqlimport跳过或者忽略那些有相同唯一
关键字的行， 导入文件中的数据将被忽略。
-l or -lock-tables 数据被插入之前锁住表，这样就防止了，
你在更新数据库时，用户的查询和更新受到影响。
-r or -replace 这个选项与－i选项的作用相反；此选项将替代
表中有相同唯一关键字的记录。
--fields-enclosed- by= char
指定文本文件中数据的记录时以什么括起的， 很多情况下
数据以双引号括起。 默认的情况下数据是没有被字符括起的。
--fields-terminated- by=char
指定各个数据的值之间的分隔符，在句号分隔的文件中，
分隔符是句号。您可以用此选项指定数据之间的分隔符。
默认的分隔符是跳格符（Tab）
--lines-terminated- by=str
此选项指定文本文件中行与行之间数据的分隔字符串
或者字符。 默认的情况下mysqlimport以newline为行分隔符。
您可以选择用一个字符串来替代一个单个的字符：
一个新行或者一个回车。
mysqlimport命令常用的选项还有-v 显示版本（version）， -p 提示输入密码（password）等。
3）.例子：导入一个以逗号为分隔符的文件
文件中行的记录格式是这样的：
"1", "ORD89876", "1 Dozen Roses", "19991226"
我们的任务是要把这个文件里面的数据导入到数据库Meet_A_Geek中的表格Orders中，
我们使用这个命令:
bin/mysqlimport –prl –fields-enclosed-by=" –fields-terminated-by=, Meet_A_Geek Orders.txt
这个命令可能看起来很不爽，不过当你熟悉了之后，这是非常简单的。
第一部分，bin/mysqlimport ,告诉操作系统你要运行的命令是mysql/bin目录下的mysqlimport，选项p是要求输入密码，这样就要求你在改动数据库之前输入密码，操作 起来会更安全。我们用了r选项是因为我们想要把表中的唯一关键字与文件记录中有重复唯一关键字的记录替换成文件中的数据。我们表单中的数据不是最新的，需 要用文件中的数据去更新，因而就用r这个选项，替代数据库中已经有的记录。l选项的作用是在我们插入数据的时候锁住表，这样就阻止了用户在我们更新表的时 候对表进行查询或者更改的操作。
mysql导入数据命令之（二）：使用批处理和LOAD
批处理导入文件，从sql文件导入数据到数据库中 ，批处理是一种非交互式运行mysql程序的方法，如同您在mysql中使用的命令一样，你仍然将使用这些命令。
为了实现批处理，您重定向一个文件到mysql程序中，首先我们需要一个文本文件，这个文本文件包含有与我们在mysql中输入的命令相同的文本。
比如我们要插入一些数据，使用包含下面文本的文件(文件名为New_Data.sql,当然我们也可以取名为New_Data.txt及任何其他的合法名字，并不一定要以后缀sql结尾)：
USE Meet_A_Geek;
INSERT INTO Customers (Customer_ID, Last_Name) VALUES(NULL, "Block");
INSERT INTO Customers (Customer_ID, Last_Name) VALUES(NULL, "Newton");
INSERT INTO Customers (Customer_ID, Last_Name) VALUES(NULL, "Simmons");
注意上面的这些句子的语法都必须是正确的，并且每个句子以分号结束。 上面的USE命令选择数据库，INSERT命令插入数据。
下面我们要把上面的文件导入到数据库中，导入之前要确认数据库已经在运行，即是mysqld进程（或者说服务，Windows NT下面称为”服务“，unix下面为”进程“）已经在运行。
然后运行下面的命令：
bin/mysql –p < /home/mark/New_Data.sql
接着按提示输入密码，如果上面的文件中的语句没有错误，那么这些数据就被导入到了数据库中。
命令行中使用LOAD DATA INFILE 从文件中导入数据到数据库:
现在您可能会问自己，"究竟为什么我要输入所有的这些SQL语句到文件中，然后通过程序运行它们呢？” 这样看起来好像需要大量的工作。很好，你这样想很可能就对了。但是假如你有从所有这些命令中产生的log记录呢？现在这样就很棒，嗯，大多数数据库都会自 动产生数据库中的事件记录的log。而大部分log都包含有用过的原始的SQL命令。因此，如果您不能从您现在的数据库中导出数据到新的mysql数据库 中使用，那么您可以使用log和mysql的批处理特性，来快速且方便地导入您地数据。当然，这样就省去了打字的麻烦。
LOAD DATA INFILE
这是我们要介绍的最后一个导入数据到MySQL数据库中的方法。这个命令与mysqlimport非常相似，但这个方法可以在mysql命令行中使用。也 就是说您可以在所有使用API的程序中使用这个命令。使用这种方法，您就可以在应用程序中导入您想要导入的数据。使用这个命令之前，mysqld进程（服 务）必须已经在运行。
启动mysql命令行：
bin/mysql –p
按提示输入密码，成功进入mysql命令行之后，输入下面的命令：
USE Meet_A_Geek;
LOAD DATA INFILE "/home/mark/data.sql" INTO TABLE Orders;
简单的讲，这样将会把文件data.sql中的内容导入到表Orders中，如mysqlimport工具一样，这个命令也有一些可以选择的参数。比如您需要把自己的电脑上的数据导入到远程的数据库服务器中，您可以使用下面的命令：
LOAD DATA LOCAL INFILE "C:/MyDocs/SQL.txt" INTO TABLE Orders;
上面的LOCAL参数表示文件是本地的文件，服务器是您所登陆的服务器。 这样就省去了使用ftp来上传文件到服务器，MySQL替你完成了. 您也可以设置插入语句的优先级，如果您要把它标记为低优先级（LOW_PRIORITY），那么MySQL将会等到没有其他人读这个表的时候，才把插入数 据。可以使用如下的命令：
LOAD DATA LOW_PRIORITY INFILE "/home/mark/data.sql" INTO TABLE Orders;
您也可以指定是否在插入数据的时候，取代或者忽略文件与数据表中重复的键值。替代重复的键值的语法：
LOAD DATA LOW_PRIORITY INFILE "/home/mark/data.sql" REPLACE INTO TABLE Orders;
上面的句子看起来有点笨拙，但却把关键字放在了让您的剖析器可以理解的地方。
下面的一对选项描述了文件的记录格式，这些选项也是在mysqlimport工具中可以用的。他们在这里看起来有点不同。首先，要用到FIELDS关键字，如果用到这个关键字，MySQL剖析器希望看到至少有下面的一个选项：
TERMINATED BY character
ENCLOSED BY character
ESCAPED BY character
这些关键字与它们的参数跟mysqlimport中的用法是一样的. The TERMINATED BY 描述字段的分隔符，默认情况下是tab字符（/t）
ENCLOSED BY描述的是字段的括起字符。比方以引号括起每一个字段。
ESCAPED BY 描述的转义字符。默认的是反些杠（backslash：/ ）.
下面仍然使用前面的mysqlimport命令的例子，用LOAD DATA INFILE语句把同样的文件导入到数据库中:
LOAD DATA INFILE "/home/mark/Orders.txt" REPLACE INTO TABLE Orders FIELDS TERMINATED BY ',' ENCLOSED BY '"';
LOAD DATA INFILE语句中有一个mysqlimport工具中没有特点：
LOAD DATA INFILE 可以按指定的列把文件导入到数据库中。当我们要把数据的一部分内容导入的时候，这个特点就很重要。比方说，我们要从Access数据库升级到MySQL数 据库的时候，需要加入一些栏目（列/字段/field）到MySQL数据库中，以适应一些额外的需要。
这个时候，我们的Access数据库中的数据仍然是可用的，但是因为这些数据的栏目(field)与MySQL中的不再匹配，因此而无法再使用 mysqlimport工具。尽管如此，我们仍然可以使用LOAD DATA INFILE，下面的例子显示了如何向指定的栏目(field)中导入数据：
LOAD DATA INFILE "/home/Order.txt" INTO TABLE Orders(Order_Number, Order_Date, Customer_ID);
如您所见，我们可以指定需要的栏目（fields）。这些指定的字段依然是以括号括起，由逗号分隔的，如果您遗漏了其中任何一个，MySQL将会提醒您^_^
1. 为查询缓存优化你的查询
大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表而直接访问缓存结果了。
这里最主要的问题是，对于程序员来说，这个事情是很容易被忽略的。因为，我们某些查询语句会让MySQL不使用缓存。请看下面的示例：
|
1
2
3
4
5
6
|
// 查询缓存不开启
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// 开启查询缓存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
|
上面两条SQL语句的差别就是 CURDATE() ，MySQL的查询缓存对这个函数不起作用。所以，像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而开启缓存。
2. EXPLAIN 你的 SELECT 查询
使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。
EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的……等等，等等。
挑一个你的SELECT语句（推荐挑选那个最复杂的，有多表联接的），把关键字EXPLAIN加到前面。你可以使用phpmyadmin来做这个事。然后，你会看到一张表格。下面的这个示例中，我们忘记加上了group_id索引，并且有表联接：
当我们为 group_id 字段加上索引后：
我们可以看到，前一个结果显示搜索了 7883 行，而后一个只是搜索了两个表的 9 和 16 行。查看rows列可以让我们找到潜在的性能问题。
3. 当只要一行数据时使用 LIMIT 1
当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。
在这种情况下，加上 LIMIT 1 可以增加性能。这样一样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。
下面的示例，只是为了找一下是否有“中国”的用户，很明显，后面的会比前面的更有效率。（请注意，第一条中是Select *，第二条是Select 1）
|
1
2
3
4
5
6
7
8
9
10
11
|
// 没有效率的：
$r = mysql_query("SELECT * FROM user WHERE country = 'China'");
if (mysql_num_rows($r) > 0) {
// ...
}
// 有效率的：
$r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
if (mysql_num_rows($r) > 0) {
// ...
}
|
4. 为搜索字段建索引
索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧。
从上图你可以看到那个搜索字串 “last_name LIKE ‘a%'”，一个是建了索引，一个是没有索引，性能差了4倍左右。
另外，你应该也需要知道什么样的搜索是不能使用正常的索引的。例如，当你需要在一篇大的文章中搜索一个词时，如： “WHERE post_content LIKE ‘%apple%'”，索引可能是没有意义的。你可能需要使用MySQL全文索引 或是自己做一个索引（比如说：搜索关键词或是Tag什么的）
5. 在Join表的时候使用相当类型的例，并将其索引
如果你的应用程序有很多 JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。
而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）
|
1
2
3
4
5
6
|
// 在state中查找company
$r = mysql_query("SELECT company_name FROM users
LEFT JOIN companies ON (users.state = companies.state)
WHERE users.id = $user_id");
// 两个 state 字段应该是被建过索引的，而且应该是相当的类型，相同的字符集。
|
6. 千万不要 ORDER BY RAND()
想打乱返回的数据行？随机挑一个数据？真不知道谁发明了这种用法，但很多新手很喜欢这样用。但你确不了解这样做有多么可怕的性能问题。
如果你真的想把返回的数据行打乱了，你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是：MySQL会不得不去执行RAND()函数（很耗CPU时间），而且这是为了每一行记录去记行，然后再对其排序。就算是你用了Limit 1也无济于事（因为要排序）
下面的示例是随机挑一条记录
|
1
2
3
4
5
6
7
8
9
|
// 千万不要这样做：
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// 这要会更好：
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");
|
7. 避免 SELECT *
从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。
所以，你应该养成一个需要什么就取什么的好的习惯。
|
1
2
3
4
5
6
7
8
9
|
// 不推荐
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// 推荐
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
|
8. 永远为每张表设置一个ID
我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。
就算是你 users 表有一个主键叫 “email”的字段，你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。
而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……
在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID和课程ID叫“外键”其共同组成主键。
9. 使用 ENUM 而不是 VARCHAR
ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。
如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。
MySQL也有一个“建议”（见第十条）告诉你怎么去重新组织你的表结构。当你有一个 VARCHAR 字段时，这个建议会告诉你把其改成 ENUM 类型。使用 PROCEDURE ANALYSE() 你可以得到相关的建议。
10. 从 PROCEDURE ANALYSE() 取得建议
PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。
例如，如果你创建了一个 INT 字段作为你的主键，然而并没有太多的数据，那么，PROCEDURE ANALYSE()会建议你把这个字段的类型改成 MEDIUMINT 。或是你使用了一个 VARCHAR 字段，因为数据不多，你可能会得到一个让你把它改成 ENUM 的建议。这些建议，都是可能因为数据不够多，所以决策做得就不够准。
在phpmyadmin里，你可以在查看表时，点击 “Propose table structure” 来查看这些建议
一定要注意，这些只是建议，只有当你的表里的数据越来越多时，这些建议才会变得准确。一定要记住，你才是最终做决定的人。
11. 尽可能的使用 NOT NULL
除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议，请往下看。
首先，问问你自己“Empty”和“NULL”有多大的区别（如果是INT，那就是0和NULL）？如果你觉得它们之间没有什么区别，那么你就不要使用NULL。（你知道吗？在 Oracle 里，NULL 和 Empty 的字符串是一样的！)
不要以为 NULL 不需要空间，其需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。 当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。
下面摘自MySQL自己的文档：
“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”
12. Prepared Statements
Prepared Statements很像存储过程，是一种运行在后台的SQL语句集合，我们可以从使用 prepared statements 获得很多好处，无论是性能问题还是安全问题。
Prepared Statements 可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入式”攻击。当然，你也可以手动地检查你的这些变量，然而，手动的检查容易出问题，而且很经常会被程序员忘了。当我们使用一些framework或是ORM的时候，这样的问题会好一些。
在性能方面，当一个相同的查询被使用多次的时候，这会为你带来可观的性能优势。你可以给这些Prepared Statements定义一些参数，而MySQL只会解析一次。
虽然最新版本的MySQL在传输Prepared Statements是使用二进制形势，所以这会使得网络传输非常有效率。
当然，也有一些情况下，我们需要避免使用Prepared Statements，因为其不支持查询缓存。但据说版本5.1后支持了。
在PHP中要使用prepared statements，你可以查看其使用手册：mysqli 扩展 或是使用数据库抽象层，如：PDO.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
// 创建 prepared statement
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {
// 绑定参数
$stmt->bind_param("s", $state);
// 执行
$stmt->execute();
// 绑定结果
$stmt->bind_result($username);
// 移动游标
$stmt->fetch();
printf("%s is from %s\n", $username, $state);
$stmt->close();
}
|
13. 无缓冲的查询
正常的情况下，当你在当你在你的脚本中执行一个SQL语句的时候，你的程序会停在那里直到没这个SQL语句返回，然后你的程序再往下继续执行。你可以使用无缓冲查询来改变这个行为。
关于这个事情，在PHP的文档中有一个非常不错的说明： mysql_unbuffered_query() 函数：
“mysql_unbuffered_query() sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don’t have to wait until the complete SQL query has been performed.”
上面那句话翻译过来是说，mysql_unbuffered_query() 发送一个SQL语句到MySQL而并不像mysql_query()一样去自动fethch和缓存结果。这会相当节约很多可观的内存，尤其是那些会产生大量结果的查询语句，并且，你不需要等到所有的结果都返回，只需要第一行数据返回的时候，你就可以开始马上开始工作于查询结果了。
然而，这会有一些限制。因为你要么把所有行都读走，或是你要在进行下一次的查询前调用mysql_free_result() 清除结果。而且， mysql_num_rows() 或 mysql_data_seek() 将无法使用。所以，是否使用无缓冲的查询你需要仔细考虑。
14. 把IP地址存成 UNSIGNED INT
很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的WHERE条件：IP between ip1 and ip2。
我们必需要使用UNSIGNED INT，因为 IP地址会使用整个32位的无符号整形。
而你的查询，你可以使用 INET_ATON() 来把一个字符串IP转成一个整形，并使用 INET_NTOA() 把一个整形转成一个字符串IP。在PHP中，也有这样的函数 ip2long() 和 long2ip()。
|
1
|
$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
|
15. 固定长度的表会更快
如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。
固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。
并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。
使用“垂直分割”技术（见下一条），你可以分割你的表成为两个一个是定长的，一个则是不定长的。
16. 垂直分割
“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。（以前，在银行做过项目，见过一张表有100多个字段，很恐怖）
示例一：在Users表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢？ 这样会让你的表有更好的性能，大家想想是不是，大量的时候，我对于用户表来说，只有用户ID，用户名，口令，用户角色等会被经常使用。小一点的表总是会有好的性能。
示例二： 你有一个叫 “last_login” 的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中，这样就不会影响你对用户ID，用户名，用户角色的不停地读取了，因为查询缓存会帮你增加很多性能。
另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去Join他们，不然的话，这样的性能会比不分割时还要差，而且，会是极数级的下降。
17. 拆分大的 DELETE 或 INSERT 语句
如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。
Apache 会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。
如果你把你的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让你泊WEB服务Crash，还可能会让你的整台服务器马上掛了。
所以，如果你有一个大的处理，你定你一定把其拆分，使用 LIMIT 条件是一个好的方法。下面是一个示例：
|
1
2
3
4
5
6
7
8
9
10
|
while (1) {
//每次只做1000条
mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
if (mysql_affected_rows() == 0) {
// 没得可删了，退出！
break;
}
// 每次都要休息一会儿
usleep(50000);
}
|
18. 越小的列会越快
对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈。所以，把你的数据变得紧凑会对这种情况非常有帮助，因为这减少了对硬盘的访问。
参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。
如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。
当然，你也需要留够足够的扩展空间，不然，你日后来干这个事，你会死的很难看，参看Slashdot的例子（2009年11月06日），一个简单的ALTER TABLE语句花了3个多小时，因为里面有一千六百万条数据。
19. 选择正确的存储引擎
在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。酷壳以前文章《MySQL: InnoDB 还是 MyISAM?》讨论和这个事情。
MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。
InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。
下面是MySQL的手册
20. 使用一个对象关系映射器（Object Relational Mapper）
使用 ORM (Object Relational Mapper)，你能够获得可靠的性能增涨。一个ORM可以做的所有事情，也能被手动的编写出来。但是，这需要一个高级专家。
ORM 的最重要的是“Lazy Loading”，也就是说，只有在需要的去取值的时候才会去真正的去做。但你也需要小心这种机制的副作用，因为这很有可能会因为要去创建很多很多小的查询反而会降低性能。
ORM 还可以把你的SQL语句打包成一个事务，这会比单独执行他们快得多得多。
目前，个人最喜欢的PHP的ORM是：Doctrine。
21. 小心“永久链接”
“永久链接”的目的是用来减少重新创建MySQL链接的次数。当一个链接被创建了，它会永远处在连接的状态，就算是数据库操作已经结束了。而且，自从我们的Apache开始重用它的子进程后——也就是说，下一次的HTTP请求会重用Apache的子进程，并重用相同的 MySQL 链接。
在理论上来说，这听起来非常的不错。但是从个人经验（也是大多数人的）上来说，这个功能制造出来的麻烦事更多。因为，你只有有限的链接数，内存问题，文件句柄数，等等。
而且，Apache 运行在极端并行的环境中，会创建很多很多的了进程。这就是为什么这种“永久链接”的机制工作地不好的原因。在你决定要使用“永久链接”之前，你需要好好地考虑一下你的整个系统的架构
提问：如何设计或优化千万级别的大表？此外无其他信息，个人觉得这个话题有点范，就只好简单说下该如何做，对于一个存储设计，必须考虑业务特点，收集的信息如下：1.数据的容量：1-3年内会大概多少条数据，每条数据大概多少字节；
2.数据项：是否有大字段，那些字段的值是否经常被更新；
3.数据查询SQL条件：哪些数据项的列名称经常出现在WHERE、GROUP BY、ORDER BY子句中等；
4.数据更新类SQL条件：有多少列经常出现UPDATE或DELETE 的WHERE子句中；
5.SQL量的统计比，如：SELECT：UPDATE+DELETE：INSERT=多少？
6.预计大表及相关联的SQL，每天总的执行量在何数量级？
7.表中的数据：更新为主的业务 还是 查询为主的业务
8.打算采用什么数据库物理服务器，以及数据库服务器架构？
9.并发如何？
10.存储引擎选择InnoDB还是MyISAM？
大致明白以上10个问题，至于如何设计此类的大表，应该什么都清楚了！
至于优化若是指创建好的表，不能变动表结构的话，那建议InnoDB引擎，多利用点内存，减轻磁盘IO负载，因为IO往往是数据库服务器的瓶颈
另外对优化索引结构去解决性能问题的话，建议优先考虑修改类SQL语句，使他们更快些，不得已只靠索引组织结构的方式，当然此话前提是，
索引已经创建的非常好，若是读为主，可以考虑打开query_cache，
以及调整一些参数值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size
一、优化概述
二、查询与索引优化分析
1性能瓶颈定位
Show命令
慢查询日志
explain分析查询
profiling分析查询
2索引及查询优化
三、配置优化
1) max_connections
2) back_log
3) interactive_timeout
4) key_buffer_size
5) query_cache_size
6) record_buffer_size
7) read_rnd_buffer_size
8) sort_buffer_size
9) join_buffer_size
10) table_cache
11) max_heap_table_size
12) tmp_table_size
13) thread_cache_size
14) thread_concurrency
15) wait_timeout
一、 优化概述
MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈，CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上，我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。
除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能，通常有三种：使用索引，使用EXPLAIN分析查询以及调整MySQL的内部配置。
二、查询与索引优化分析
在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。
1 性能瓶颈定位
Show命令
我们可以通过show命令查看MySQL状态及变量，找到系统的瓶颈：
Mysql> show status ——显示状态信息（扩展show status like ‘XXX’）
Mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’）
Mysql> show innodb status ——显示InnoDB存储引擎的状态
Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等
Shell> mysqladmin variables -u username -p password——显示系统变量
Shell> mysqladmin extended-status -u username -p password——显示状态信息
查看状态变量及帮助：
Shell> mysqld –verbose –help [|more #逐行显示]
比较全的Show命令的使用可参考： http://blog.phpbean.com/a.cn/18/
慢查询日志
慢查询日志开启：
在配置文件my.cnf或my.ini中在[mysqld]一行下面加入两个配置参数
log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=2
注：log-slow-queries参数为慢查询日志存放的位置，一般这个目录要有mysql的运行帐号的可写权限，一般都将这个目录设置为mysql的数据存放目录；
long_query_time=2中的2表示查询超过两秒才记录；
在my.cnf或者my.ini中添加log-queries-not-using-indexes参数，表示记录下没有使用索引的查询。
log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=10
log-queries-not-using-indexes
慢查询日志开启方法二：
我们可以通过命令行设置变量来即时启动慢日志查询。由下图可知慢日志没有打开，slow_launch_time=# 表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加
设置慢日志开启
MySQL后可以查询long_query_time 的值 。
为了方便测试，可以将修改慢查询时间为5秒。
慢查询分析mysqldumpslow
我们可以通过打开log文件查看得知哪些SQL执行效率低下
[root@localhost mysql]# more slow-query.log
# Time: 081026 19:46:34
# User@Host: root[root] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 6552961
select count(*) from t_user;
从日志中，可以发现查询时间超过5 秒的SQL，而小于5秒的没有出现在此日志中。
如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。
进入log的存放目录，运行
[root@mysql_data]#mysqldumpslow slow-query.log
Reading mysql slow query log from slow-query.log
Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysql
select count(N) from t_user;
mysqldumpslow命令
/path/mysqldumpslow -s c -t 10 /database/mysql/slow-query.log
这会输出记录次数最多的10条SQL语句，其中：
-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
-t, 是top n的意思，即为返回前面多少条的数据；
-g, 后边可以写一个正则匹配模式，大小写不敏感的；
例如：
/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log
得到返回记录集最多的10个查询。
/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log
得到按照时间排序的前10条里面含有左连接的查询语句。
使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。
explain分析查询
使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:
– 表的读取顺序
– 数据读取操作的操作类型
– 哪些索引可以使用
– 哪些索引被实际使用
– 表之间的引用
– 每张表有多少行被优化器查询
EXPLAIN字段：
ØTable：显示这一行的数据是关于哪张表的
Øpossible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
Økey：实际使用的索引。如果为NULL，则没有使用索引。MYSQL很少会选择优化不足的索引，此时可以在SELECT语句中使用USE INDEX（index）来强制使用一个索引或者用IGNORE INDEX（index）来强制忽略索引
Økey_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
Øref：显示索引的哪一列被使用了，如果可能的话，是一个常数
Ørows：MySQL认为必须检索的用来返回请求数据的行数
Øtype：这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL
nsystem、const：可以将查询的变量转为常量. 如id=1; id为 主键或唯一键.
neq_ref：访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或惟一键)
nref：访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生
nrange：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西，并且该字段上建有索引时发生的情况(注:不一定好于index)
nindex：以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描
nALL：全表扫描，应该尽量避免
ØExtra：关于MYSQL如何解析查询的额外信息，主要有以下几种
nusing index：只用到索引,可以避免访问表.
nusing where：使用到where来过虑数据. 不是所有的where clause都要显示using where. 如以=方式访问索引.
nusing tmporary：用到临时表
nusing filesort：用到额外的排序. (当使用order by v1,而没用到索引时,就会使用额外的排序)
nrange checked for eache record(index map:N)：没有好的索引.
profiling分析查询
通过慢日志查询可以知道哪些SQL语句执行效率低下，通过explain我们可以得知SQL语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。
如果觉得explain的信息不够详细，可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。
profiling默认是关闭的。可以通过以下语句查看
打开功能： mysql>set profiling=1; 执行需要测试的sql 语句：
mysql> show profiles\G; 可以得到被执行的SQL语句的时间和ID
mysql>show profile for query 1; 得到对应SQL语句执行的详细信息
Show Profile命令格式：
SHOW PROFILE [type [, type] … ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type:
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS
以上的16rows是针对非常简单的select语句的资源信息，对于较复杂的SQL语句，会有更多的行和字段，比如converting HEAP to MyISAM 、Copying to tmp table等等，由于以上的SQL语句不存在复杂的表操作，所以未显示这些字段。通过profiling资源耗费信息，我们可以采取针对性的优化措施。
测试完毕以后 ，关闭参数：mysql> set profiling=0
2 索引及查询优化
索引的类型
Ø 普通索引：这是最基本的索引类型，没唯一性之类的限制。
Ø 唯一性索引：和普通索引基本相同，但所有的索引列值保持唯一性。
Ø 主键：主键是一种唯一索引，但必须指定为”PRIMARY KEY”。
Ø 全文索引：MYSQL从3.23.23开始支持全文索引和全文检索。在MYSQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。
大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用B树中存储。空间列类型的索引使用R-树，MEMORY表支持hash索引。
单列索引和多列索引（复合索引）
索引可以是单列索引，也可以是多列索引。对相关的列使用索引是提高SELECT操作性能的最佳途径之一。
多列索引：
MySQL可以为多个列创建索引。一个索引可以包括15个列。对于某些列类型，可以索引列的左前缀，列的顺序非常重要。
多列索引可以视为包含通过连接索引列的值而创建的值的排序的数组。一般来说，即使是限制最严格的单列索引，它的限制能力也远远低于多列索引。
最左前缀
多列索引有一个特点，即最左前缀（Leftmost Prefixing）。假如有一个多列索引为key(firstname lastname age)，当搜索条件是以下各种列的组合和顺序时，MySQL将使用该多列索引：
firstname，lastname，age
firstname，lastname
firstname
也就是说，相当于还建立了key(firstname lastname)和key(firstname)。
索引主要用于下面的操作：
Ø 快速找出匹配一个WHERE子句的行。
Ø 删除行。当执行联接时，从其它表检索行。
Ø 对具体有索引的列key_col找出MAX()或MIN()值。由预处理器进行优化，检查是否对索引中在key_col之前发生所有关键字元素使用了WHERE key_part_# = constant。在这种情况下，MySQL为每个MIN()或MAX()表达式执行一次关键字查找，并用常数替换它。如果所有表达式替换为常量，查询立即返回。例如：
SELECT MIN(key2), MAX (key2) FROM tb WHERE key1=10;
Ø 如果对一个可用关键字的最左面的前缀进行了排序或分组(例如，ORDER BY key_part_1,key_part_2)，排序或分组一个表。如果所有关键字元素后面有DESC，关键字以倒序被读取。
Ø 在一些情况中，可以对一个查询进行优化以便不用查询数据行即可以检索值。如果查询只使用来自某个表的数字型并且构成某些关键字的最左面前缀的列，为了更快，可以从索引树检索出值。
SELECT key_part3 FROM tb WHERE key_part1=1
有时MySQL不使用索引，即使有可用的索引。一种情形是当优化器估计到使用索引将需要MySQL访问表中的大部分行时。(在这种情况下，表扫描可能会更快些）。然而，如果此类查询使用LIMIT只搜索部分行，MySQL则使用索引，因为它可以更快地找到几行并在结果中返回。例如：
合理的建立索引的建议：
(1) 越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
(2) 简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
(3) 尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值
这部分是关于索引和写SQL语句时应当注意的一些琐碎建议和注意点。
1. 当结果集只有一行数据时使用LIMIT 1
2. 避免SELECT *，始终指定你需要的列
从表中读取越多的数据，查询会变得更慢。他增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。
3. 使用连接（JOIN）来代替子查询(Sub-Queries)
连接（JOIN）.. 之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
4. 使用ENUM、CHAR 而不是VARCHAR，使用合理的字段属性长度
5. 尽可能的使用NOT NULL
6. 固定长度的表会更快
7. 拆分大的DELETE 或INSERT 语句
8. 查询的列越小越快
Where条件
在查询中，WHERE条件也是一个比较重要的因素，尽量少并且是合理的where条件是很重要的，尽量在多个条件的时候，把会提取尽量少数据量的条件放在前面，减少后一个where条件的查询时间。
有些where条件会导致索引无效：
Ø where子句的查询条件里有！=，MySQL将无法使用索引。
Ø where子句使用了Mysql函数的时候，索引将无效，比如：select * from tb where left(name, 4) = ‘xxx’
Ø 使用LIKE进行搜索匹配的时候，这样索引是有效的：select * from tbl1 where name like ‘xxx%’，而like ‘%xxx%’ 时索引无效
三、 配置优化
安装MySQL后，配置文件my.cnf在 /MySQL安装目录/share/mysql目录中，该目录中还包含多个配置文件可供参考，有my-large.cnf ，my-huge.cnf， my-medium.cnf，my-small.cnf，分别对应大中小型数据库应用的配置。win环境下即存在于MySQL安装目录中的.ini文件。
下面列出了对性能优化影响较大的主要变量，主要分为连接请求的变量和缓冲区变量。
1. 连接请求的变量：
1) max_connections
MySQL的最大连接数，增加该值增加mysqld 要求的文件描述符的数量。如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。
数值过小会经常出现ERROR 1040: Too many connections错误，可以过’conn%’通配符查看当前状态的连接数量，以定夺该值的大小。
show variables like ‘max_connections’ 最大连接数
show status like ‘max_used_connections’响应的连接数
如下：
mysql> show variables like ‘max_connections‘;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| max_connections | 256 |
+———————–+——-+
mysql> show status like ‘max%connections‘;
+———————–+——-+
| Variable_name | Value |
+—————————-+——-+
| max_used_connections | 256|
+—————————-+——-+
max_used_connections / max_connections * 100% （理想值≈ 85%）
如果max_used_connections跟max_connections相同 那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。
2) back_log
MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用。如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。
back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。
当观察你主机进程列表（mysql> show full processlist），发现大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大back_log 的值了。
默认数值是50，可调优为128，对于Linux系统设置范围为小于512的整数。
3) interactive_timeout
一个交互连接在被服务器在关闭前等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE 选项的客户。
默认数值是28800，可调优为7200。
2. 缓冲区变量
全局缓冲：
4) key_buffer_size
key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
举例如下：
mysql> show variables like ‘key_buffer_size‘;
+——————-+————+
| Variable_name | Value |
+———————+————+
| key_buffer_size | 536870912 |
+———— ———-+————+
key_buffer_size为512MB，我们再看一下key_buffer_size的使用情况：
mysql> show global status like ‘key_read%‘;
+————————+————-+
| Variable_name | Value |
+————————+————-+
| Key_read_requests| 27813678764 |
| Key_reads | 6798830 |
+————————+————-+
一共有27813678764个索引读取请求，有6798830个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：
key_cache_miss_rate ＝Key_reads / Key_read_requests * 100%，设置在1/1000左右较好
默认配置数值是8388600(8M)，主机有4GB内存，可以调优值为268435456(256MB)。
5) query_cache_size
使用查询缓冲，MySQL将查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。
通过检查状态值Qcache_*，可以知道query_cache_size设置是否合理（上述状态值可以使用SHOW STATUS LIKE ‘Qcache%’获得）。如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。
与查询缓冲有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。
query_cache_type指定是否使用查询缓冲，可以设置为0、1、2，该变量是SESSION级的变量。
query_cache_limit指定单个查询能够使用的缓冲区大小，缺省为1M。
query_cache_min_res_unit是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。检查状态值Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小query_cache_min_res_unit。
举例如下：
mysql> show global status like ‘qcache%‘;
+——————————-+—————–+
| Variable_name | Value |
+——————————-+—————–+
| Qcache_free_blocks | 22756 |
| Qcache_free_memory | 76764704 |
| Qcache_hits | 213028692 |
| Qcache_inserts | 208894227 |
| Qcache_lowmem_prunes | 4010916 |
| Qcache_not_cached | 13385031 |
| Qcache_queries_in_cache | 43560 |
| Qcache_total_blocks | 111212 |
+——————————-+—————–+
mysql> show variables like ‘query_cache%‘;
+————————————–+————–+
| Variable_name | Value |
+————————————–+———–+
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 203423744 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+————————————–+—————+
查询缓存碎片率= Qcache_free_blocks / Qcache_total_blocks * 100%
如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
查询缓存利用率= (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。
查询缓存命中率= (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
示例服务器查询缓存碎片率＝20.46％，查询缓存利用率＝62.26％，查询缓存命中率＝1.94％，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。
每个连接的缓冲
6) record_buffer_size
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。
默认数值是131072(128K)，可改为16773120 (16M)
7) read_rnd_buffer_size
随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
一般可设置为16M
8) sort_buffer_size
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。
默认数值是2097144(2M)，可改为16777208 (16M)。
9) join_buffer_size
联合查询操作所能使用的缓冲区大小
record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size为每个线程独占，也就是说，如果有100个线程连接，则占用为16M*100
10) table_cache
表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
1G内存机器，推荐值是128－256。内存在4GB左右的服务器该参数可设置为256M或384M。
11) max_heap_table_size
用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变，即set @max_heap_table_size=#
这个变量和tmp_table_size一起限制了内部内存表的大小。如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。
12) tmp_table_size
通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级GROUP BY操作生成的临时表。如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。
mysql> show global status like ‘created_tmp%‘;
+——————————–+———+
| Variable_name | Value |
+———————————-+———+
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files | 58 |
| Created_tmp_tables | 1771587 |
+——————————–+———–+
每次创建临时表，Created_tmp_tables增加，如果临时表大小超过tmp_table_size，则是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数，比较理想的配置是：
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%，应该相当好了
默认为16M，可调到64-256最佳，线程独占，太大可能内存不够I/O堵塞
13) thread_cache_size
可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。
通过比较 Connections和Threads_created状态的变量，可以看到这个变量的作用。
默认值为110，可调优为80。
14) thread_concurrency
推荐设置为服务器 CPU核数的2倍，例如双核的CPU, 那么thread_concurrency的应该为4；2个双核的cpu, thread_concurrency的值应为8。默认为8
15) wait_timeout
指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。
3. 配置InnoDB的几个变量
innodb_buffer_pool_size
对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。
根据MySQL手册，对于2G内存的机器，推荐值是1G（50%）。
innodb_flush_log_at_trx_commit
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；1，则在每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；设置为2，每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘操作。
实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。
根据MySQL手册，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。
innodb_log_buffer_size
log缓存大小，一般为1-8M，默认为1M，对于较大的事务，可以增大缓存大小。
可设置为4M或8M。
innodb_additional_mem_pool_size
该参数指定InnoDB用来存储数据字典和其他内部数据结构的内存池大小。缺省值是1M。通常不用太大，只要够用就行，应该与表结构的复杂度有关系。如果不够用，MySQL会在错误日志中写入一条警告信息。
根据MySQL手册，对于2G内存的机器，推荐值是20M，可适当增加。
innodb_thread_concurrency=8
推荐设置为 2*(NumCPUs+NumDisks)，默认一般为8
mysql表修复
[mysql@webdb01 ~]$myisamchk -r mytable.MYI
myisamchk [OPTIONS] tables[.MYI]
-o, --safe-recover Uses old recovery method; Slower than '-r' but can
handle a couple of cases where '-r' reports that it
can't fix the data file.
1.2命令mysqlcheck(mysql服务可以处于运行状态)。
[mysql@webdb01 ~]$mysqlcheck mydatabase mytable
Usage:
mysqlcheck [OPTIONS] database [tables]
OR mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
OR mysqlcheck [OPTIONS] --all-databases
2.修复表。
mysql> REPAIR TABLE mytable;
Table_locks_immediate表示立即释放表锁数
Table_locks_immediate表示立即释放表锁数，Table_locks_waited表示需要等待的表锁数，
如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，
因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些。
mysql分区
mysql的表(myisam)的物理存储是分为三个文件：frm文件是表定义，MYD是数据文件，MYI是索引文件，如果当一个表达到一定的数量级的时候，那自然相应的物理文件也会很庞大，即使索引的物理文件也会变得比较臃肿。
分区就是为了解决这种大数据量而产生的，应该注意的是：分区不是分表，分区之后表还是一个表，它是把存储文件进行拆分，那数据库在读取物理文件的时候，每次读入的是切分后的数据量较小的存储文件，这样自然会提高效率，而且多个小的存储文件之间还可以并行。
分区应该注意的事项：
1、 做分区时，要么不定义主键，要么把分区字段加入到主键中。
2、 分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量NOT NULL
分区类型：
1、 RANGE分区：按照一个连续范围(列值)分区，把多行分配给分区。
2、 LIST分区：类似于RANGE分区，不过他的这个范围不是连续的，是按照业务逻辑定的，一个离散值集合。
3、 HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
4、 KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
RANGE分区：
建立分区：
create table fenqu(
id int not null auto_increment,
tid int not null,
add_time date,
primary key(id,tid)
)
partition by range(tid)
(
partition p0 values less than (5),
partition p1 values less than (10),
partition p2 values less than (15),
partition p3 values less than MAXVALUE
);
这是在建表的时候直接建立分区，共分成四个分区。曾经看到有人在问主键的问题，如果非要定义主键，那就选择把分区字段加入到主键中primary key(id,tid)。
如果想在已经建好的表上进行分区，如果使用alter添加分区的话，mysql会提示错误：
ERROR 1505 <HY000> Partition management on a not partitioned table is not possible
正确的方法是新建一个具有分区的表，结构一致，然后用insert into 分区表 select * from 原始表;
LIST分区:
建立分区采用手册上的实例：
CREATE TABLE employees (id INT NOT NULL,fname VARCHAR(30),lname VARCHAR(30),hired DATE NOT NULL DEFAULT '1970-01-01',separated DATE NOT NULL DEFAULT '9999-12-31',job_code INT,store_id INT)PARTITION BY LIST(store_id)PARTITION pNorth VALUES IN (3,5,6,9,17),PARTITION pEast VALUES IN (1,2,10,11,19,20),PARTITION pWest VALUES IN (4,12,13,14,18),PARTITION pCentral VALUES IN (7,8,15,16))；
网店store_id是分属于不同经销权的地区的，所以按照不同地区进行切分，确定每个地区的store_id.(具体请参看mysql手册)。 LIST分区也有自身的限制，那就是如果已经确按照各网店进行切分，那如果要插入新数据时，网店编号是分区是没有的，那插入会失败。
INSERT INTO employees VALUES(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);
想上面这条语句就会执行失败。
HASH分区：
Hash分区是希望能够将数据平均的分布到每个分区上，除了基本的hash分区外，还支持线性hash，个人觉得有线性hash分区，也就是为了追求“y=nx”这种线性分布，手册上有介绍，在此就不多做赘述了。
KEY分区：
按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的 哈希函数是由MySQL服务器提供。 MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的 哈希函数，这些函数是基于与 PASSWORD()一样的运算法则。（手册定义）
分区管理：
ALTER TABLE table_name (DROP|ADD) PARTITION #####;
当然还有一些合并分区啊，子分区的概念，在此就不一一介绍了，手册上都有
======================
当 MySQL的总记录数超过了100万后，会出现性能的大幅度下降吗？答案是肯定的，但是，性 能下降>的比率不一而同，要看系统的架构、应用程序、还有>包括索引、服务器硬件等多种因素而定。当有网友问我这个问题的时候，我最常见的回 答>就是：分表，可以根据id区间或者时间先后顺序等多种规则来分表。分表很容易，然而由此所带来的应用程序甚至是架构方面的改动工作却不> 容小觑，还包括将来的扩展性等。
在以前，一种解决方案就是使用 MERGE
类型，这是一个非常方便的做饭。架构和程序基本上不用做改动，不过，它的缺点是显见的：
- 只能在相同结构的 MyISAM 表上使用
- 无法享受到 MyISAM 的全部功能，例如无法在 MERGE 类型上执行 FULLTEXT 搜索
- 它需要使用更多的文件描述符
- 读取索引更慢
这个时候，MySQL 5.1 中新增的分区(Partition)功能的优势也就很明显了：
- 与单个磁盘或文件系统分区相比，可以存储更多的数据
- 很容易就能删除不用或者过时的数据
- 一些查询可以得到极大的优化
- 涉及到 SUM()/COUNT() 等聚合函数时，可以并行进行
- IO吞吐量更大
分区允许可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。
分区应该注意的事项：
1、 做分区时，要么不定义主键，要么把分区字段加入到主键中。
2、 分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量NOT NULL
二、分区的类型
- RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
- LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
- HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包>含MySQL中有效的、产生非负整数值的任何表达式。
- KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含>整数值。
可以通过使用SHOW VARIABLES命令来确定MySQL是否支持分区，例如：
mysql> SHOW VARIABLES LIKE '%partition%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| have_partition_engine | YES |+-----------------------+-------+1 row in set (0.00 sec)1 。range分区id int(11),
money int(11) unsigned not null,
date datetime
)partition by range(year(date))(
partition p2007 values less than (2008),
partition p2008 values less than (2009),
partition p2009 values less than (2010)
partition p2010 values less than maxvalue
)；
2.list分区
| create table t_list( a int(11), b int(11) )(partition by list (b) partition p0 values in (1,3,5,7,9), partition p1 values in (2,4,6,8,0) ); 对于innodb和myisam引擎，一条语句插入多条记录的时候，如果中间有值不能插入，innodb会全部回滚，myisam在错误值之前的数据可以插入到表中。 |
hash分区的目的是将数据均匀的分布到预先定义的各个分区中，保证各分区的数据量大致一致。
| create table t_hash( a int(11), b datetime )partition by hash (YEAR(b) partitions 4; |
hash的分区函数页需要返回一个整数值。partitions子句中的值是一个非负整数，不加的partitions子句的话，默认为分区数为1。
4.key分区key分区和hash分区相似，不同在于hash分区是用户自定义函数进行 分区，key分区使用mysql数据库提供的函数进行分区，NDB cluster使用MD5函数来分区，对于其他存储引擎mysql使用内部的hash函数，这些函数基于password()一样的算法。
| create table t_key( a int(11), b datetime) partition by key (b) partitions 4; |
上面的RANGE、LIST、HASH、KEY四种分区中，分区的条件必须是整形，如果不是整形需要通过函数将其转换为整形。
mysql-5.5开始支持COLUMNS分区，可视为RANGE和LIST分区的进化，COLUMNS分区可以直接使用非整形数据进行分区。COLUMNS分区支持以下数据类型：
所有整形，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL则不支持。
日期类型，如DATE和DATETIME。其余日期类型不支持。
字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不支持。
COLUMNS可以使用多个列进行分区。
新增分区
mysql> ALTER TABLE sale_data
-> ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除分区
--当删除了一个分区，也同时删除了该分区中所有的数据。
mysql> ALTER TABLE sale_data DROP PARTITION p201010;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
分区的合并
下面的SQL，将p201001 - p201009 合并为3个分区p2010Q1 - p2010Q3
mysql> ALTER TABLE sale_data
-> REORGANIZE PARTITION p201001,p201002,p201003,
-> p201004,p201005,p201006,
-> p201007,p201008,p201009 INTO
-> (
-> PARTITION p2010Q1 VALUES LESS THAN (201004),
-> PARTITION p2010Q2 VALUES LESS THAN (201007),
-> PARTITION p2010Q3 VALUES LESS THAN (201010)
-> );
Query OK, 0 rows affected (1.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
InnoDB 引擎独立表空间 innodb_file_per_table
MySQL的同学，刚开始接触最多的莫过于MyISAM表引擎了，这种引擎的数据库会分别创建三个文件：表结构、表索引、表数据空间。我们可以将某个数据库目录直接迁移到其他数据库也可以正常工作。然而当你使用InnoDB的时候，一切都变了。
InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：ibdata1，这样就感觉不爽，增删数据库的时候，ibdata1文件不会自动收 缩，单个数据库的备份也将成为问题。通常只能将数据使用mysqldump 导出，然后再导入解决这个问题。
在MySQL的配置文件[mysqld]部分，增加innodb_file_per_table参数。
可以修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间。
独立表空间：
优点：
1． 每个表都有自已独立的表空间。
2． 每个表的数据和索引都会存在自已的表空间中。
3． 可以实现单表在不同的数据库中移动。
4． 空间可以回收（除drop table操作处，表空不能自已回收）
a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
b) 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
c) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。
缺点：
单表增加过大，如超过100个G。
结论：
共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整一 下：innodb_open_files 。
InnoDB Hot Backup（冷备）的表空间cp不会面对很多无用的copy了。而且利用innodb hot backup及表空间的管理命令可以实现单现移动。
1.innodb_file_per_table设置.开启方法：
在my.cnf中[mysqld]下设置
innodb_file_per_table=1
2.查看是否开启：
mysql> show variables like ‘%per_table%’;
3.关闭独享表空间
innodb_file_per_table=0关闭独立的表空间
mysql> show variables like ‘%per_table%’;
更多Mysql Innodb 引擎优化 http://linux.chinaunix.net/techdoc/database/2009/04/28/1109193.shtml
mysql中select * for update锁表的问题
先前介绍过SELECT ... FOR UPDATE的用法，不过锁定(Lock)的资料是判别就得要注意一下了。由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。
举个例子:
假设有个表单products ，里面有id跟name二个栏位，id是主键。
例1: (明确指定主键，并且有此笔资料，row lock)
SELECT * FROM products WHERE id='3' FOR UPDATE;
例2: (明确指定主键，若查无此笔资料，无lock)
SELECT * FROM products WHERE id='-1' FOR UPDATE;
例2: (无主键，table lock)
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
例3: (主键不明确，table lock )
SELECT * FROM products WHERE id<>'3' FOR UPDATE;
例4: ( 主键不明确，table lock )
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;
注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。
查看造成mysql死锁的事务
登录到MySQL，来看下有哪些MySQL事务
可以看到一个时间持续了比较久的事务，现在时间是13点了，而这个事务的开始时间是2012-11-09 12:15:14，显然是不正常的，我们看这个事务对应的mysql的线程ID（trx_mysql_thread_id）是82230715，就是这个事务导致的
13:01:48pm> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
+-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout |
+-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| 31868CED0 | RUNNING | 2012-11-09 12:15:14 | NULL | NULL | 2 | 82230715 | NULL | NULL | 0 | 0 | 2 | 376 | 1 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 10000 |
+-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
我们登录到mysql把它kill掉
13:01:55pm> kill 82230715;
这样，就可以查询了
其他
查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
