<![CDATA[BIWEB开源PHP WMS系统创始人ArthurXF肖飞的blog]]>

<![CDATA[BIWEB开源PHP WMS系统创始人ArthurXF肖飞的blog]]> http://www.bizeway.net/index.php zh-cn http://www.bizeway.net/read.php?626 <![CDATA[MySQL笔记之视图的使用详解]]> ArthurXF <arthurxf@gmail.com> Mon, 30 Dec 2013 17:41:31 +0000 http://www.bizeway.net/read.php?626 什么是视图

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。

视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据。

这样，用户可以不用看到整个数据库中的数据，而之关心对自己有用的数据。

数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中。

使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。

视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变。

视图的作用

1.使操作简单化，可以对经常使用的查询定义一个视图，使用户不必为同样的查询操作指定条件

2.增加数据的安全性，通过视图，用户只能查询和修改指定的数据。

3.提高表的逻辑独立性，视图可以屏蔽原有表结构变化带来的影响。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率

参考表：

创建视图的语法

复制代码代码如下:
CREATE [ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名 [(属性清单)]
AS SELECT 语句
[WITH [CASCADED|LOCAL] CHECK OPTION];

ALGORITHM表示视图选择的算法（可选参数）

　　UNDEFINED：MySQL将自动选择所要使用的算法
　　MERGE：将视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分
　　TEMPTABLE：将视图的结果存入临时表，然后使用临时表执行语句

视图名表示要创建的视图的名称

属性清单表示视图中的列名，默认与SELECT查询结果中的列名相同（可选参数）

WITH CHECK OPTION表示更新视图时要保证在该试图的权限范围之内（可选参数）

　　CASCADED：更新视图时要满足所有相关视图和表的条件
　　LOCAL：更新视图时，要满足该视图本身定义的条件即可

tips：创建试图时最好加上WITH CASCADED CHECK OPTION参数，这种方式比较严格

　　可以保证数据的安全性

视图操作
在单表上创建视图

复制代码代码如下:
mysql> CREATE VIEW work_view(ID,NAME,ADDR) AS SELECT id,name,address FROM work;
Query OK, 0 rows affected (0.05 sec)

此处work_view为视图名，后面括号内的参数代表视图中的列

AS表示将后面SELECT 语句中的查询结果赋给前面的视图中

在多表上创建视图

复制代码代码如下:
mysql> CREATE ALGORITHM=MERGE VIEW work_view2(ID,NAME,SALARY)
-> AS SELECT work.id,name,salary FROM work,salary
-> WHERE work.id=salary.id
-> WITH LOCAL CHECK OPTION;
Query OK, 0 rows affected (0.02 sec)

在多表中创建视图需要两表有指定联系，如上面的work.id=salary.id

SELECT查询视图

复制代码代码如下:
mysql> SELECT * FROM work_view;
+----+--------+--------+
| ID | NAME | ADDR |
+----+--------+--------+
| 1 | 张三 | 北京 |
| 2 | 李四 | 上海 |
| 3 | 王五 | 湖南 |
| 4 | 赵六 | 重庆 |
+----+--------+--------+
rows in set (0.00 sec)

此处的SELECT语句用法和其他表中的用法一样

别忘了，视图也是一张表，只不过它是虚拟的

DESCRIBE查看视图基本信息

复制代码代码如下:
mysql> DESCRIBE work_view;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int(10) | NO | | NULL | |
| NAME | varchar(20) | NO | | NULL | |
| ADDR | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)

与以往一样，此处的DESCRIBE可以简写为DESC

SHOW TABLE STATUS查看视图基本信息

复制代码代码如下:
mysql> SHOW TABLE STATUS LIKE 'work_view'\G
*************************** 1. row ***************************
Name: work_view
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
row in set (0.00 sec)

此处大部分信息显示为NULL，更加说明了视图只是一张虚拟表

如果使用SHOW TABLE STATUS查看一张真实表，结果就不会如此

SHOW CREATE VIEW查看视图详细信息

复制代码代码如下:
mysql> SHOW CREATE VIEW work_view\G
*************************** 1. row ***************************
View: work_view
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `work_view` AS select `work`.`id` AS `ID`,`work`.`name` AS `NAME`,`work`.`address` AS `ADDR` from `work`
character_set_client: utf8
collation_connection: utf8_general_ci
row in set (0.00 sec)

尼玛好复杂，这里包含了视图的各个属性

在views表中查看视图详细信息
复制代码代码如下:
mysql> SELECT * FROM information_schema.views\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: person
TABLE_NAME: work_view
VIEW_DEFINITION: select `person`.`work`.`id` AS `ID`,`person`.`work`.`name` AS `NAME`,`person`.`work`.`address` AS `ADDR` from `person`.`work`
CHECK_OPTION: NONE
IS_UPDATABLE: YES
DEFINER: root@localhost
SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: person
TABLE_NAME: work_view2

information_schema.views表内包含了所有的视图定义信息

不过，通常使用SHOW CREATE VIEW 更加方便

这里信息太长，没有完全列举……

修改视图

修改视图是指修改数据库中已存在的表的定义，当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致
CREATE OR REPLACE VIEW语句修改视图

复制代码代码如下:
mysql> CREATE OR REPLACE ALGORITHM=TEMPTABLE
-> VIEW work_view(ID,NAME)
-> AS SELECT id,name FROM work;
Query OK, 0 rows affected (0.03 sec)

话说，CREATE OR REPLACE语句非常灵活

在视图存在的情况下可对视图进行修改，视图不在的情况下可创建视图

其基本用法和CREATE VIEW 几乎一致

ALTER语句修改视图

复制代码代码如下:
mysql> ALTER VIEW work_view2(NAME,SALARY,ADDR)
-> AS SELECT name,salary,address FROM work,salary
-> WHERE work.id=salary.id;
Query OK, 0 rows affected (0.03 sec)

我这把名字、工资和地址当做字段修改了视图

如果是真实的话，对小偷来说极为方便

更新视图

更新视图是指通过视图来插入、更新和删除表中的数据，以为视图是一个虚拟表，其中木有数据

通过视图更新时，都是转换到基本表来更新

复制代码代码如下:
mysql> UPDATE work_view2 SET SALARY=5899.00 WHERE NAME='张三';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

此处语句等价于
复制代码代码如下:
mysql> UPDATE salary SET salary=5899.00 WHERE id=1;

tips:视图中虽然可以更新数据，但是有很多限制

　　一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据

删除视图

删除视图是指删除数据库中已存在的视图，删除视图时，只能删除视图的定义，不会删除数据

复制代码代码如下:
mysql> DROP VIEW IF EXISTS work_view;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP VIEW work_view2;
Query OK, 0 rows affected (0.01 sec)

这里的IF EXIST参数用来判断视图是否存在，也可以不写

Tags - mysql ]]> http://www.bizeway.net/read.php?625 <![CDATA[MySQL笔记之触发器的应用]]> ArthurXF <arthurxf@gmail.com> Mon, 30 Dec 2013 17:40:28 +0000 http://www.bizeway.net/read.php?625 创建触发器

创建只有一个执行语句的触发器

复制代码代码如下:
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW 执行语句

其中，触发器名参数指要创建的触发器的名字

BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后

FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器

复制代码代码如下:
mysql> CREATE TRIGGER trig1 AFTER INSERT
-> ON work FOR EACH ROW
-> INSERT INTO time VALUES(NOW());
Query OK, 0 rows affected (0.09 sec)

上面创建了一个名为trig1的触发器，一旦在work中有插入动作，就会自动往time表里插入当前时间

创建有多个执行语句的触发器

复制代码代码如下:
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
执行语句列表
END

其中，BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开

tips：一般情况下，mysql默认是以 ; 作为结束执行语句，与触发器中需要的分行起冲突

　　为解决此问题可用DELIMITER，如：DELIMITER ||，可以将结束符号变成||

　　当触发器创建完成后，可以用DELIMITER ;来将结束符号变成;

复制代码代码如下:
mysql> DELIMITER ||
mysql> CREATE TRIGGER trig2 BEFORE DELETE
-> ON work FOR EACH ROW
-> BEGIN
-> INSERT INTO time VALUES(NOW());
-> INSERT INTO time VALUES(NOW());
-> END
-> ||
Query OK, 0 rows affected (0.06 sec)

mysql> DELIMITER ;

上面的语句中，开头将结束符号定义为||，中间定义一个触发器，一旦有满足条件的删除操作

就会执行BEGIN和END中的语句，接着使用||结束

最后使用DELIMITER ; 将结束符号还原

查看触发器
SHOW TRIGGERS语句查看触发器信息

复制代码代码如下:
mysql> SHOW TRIGGERS\G;
*************************** 1. row ***************************
Trigger: trig1
Event: INSERT
Table: work
Statement: INSERT INTO time VALUES(NOW())
Timing: AFTER
Created: NULL
sql_mode:
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci

结果会显示所有触发器的基本信息

tips：SHOW TRIGGERS语句无法查询指定的触发器

在triggers表中查看触发器信息

复制代码代码如下:
mysql> SELECT * FROM information_schema.triggers\G
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: person
TRIGGER_NAME: trig1
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: person
EVENT_OBJECT_TABLE: work
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: INSERT INTO time VALUES(NOW())

结果显示了所有触发器的详细信息，同时，该方法可以查询制定触发器的详细信息
复制代码代码如下:
mysql> SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME='trig1'\G
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: person
TRIGGER_NAME: trig1
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: person
EVENT_OBJECT_TABLE: work

tips：所有触发器信息都存储在information_schema数据库下的triggers表中

　　可以使用SELECT语句查询，如果触发器信息过多，最好通过TRIGGER_NAME字段指定查询

删除触发器

复制代码代码如下:
mysql> DROP TRIGGER trig1;
Query OK, 0 rows affected (0.04 sec)

删除触发器之后最好使用上面的方法查看一遍

同时，也可以使用database.trig来指定某个数据库中的触发器

tips：如果不需要某个触发器时一定要将这个触发器删除，以免造成意外操作

Tags - mysql , 触发器 ]]> http://www.bizeway.net/read.php?623 <![CDATA[mysql分表，分区的区别和关系]]> ArthurXF <arthurxf@gmail.com> Sat, 27 Jul 2013 09:40:16 +0000 http://www.bizeway.net/read.php?623
什么是分表，从表面意思上看呢，就是把一张表分成N多个小表，具体请看mysql分表的3种方法

什么是分区，分区呢就是把一张表的数据分成N多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上，具体请参考mysql分区功能详细介绍，以及实例

二，mysql分表和分区有什么区别呢

1，实现方式上

a），mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完正的一张表，都对应三个文件，一个.MYD数据文件，.MYI索引文件，.frm表结构文件。

view sourceprint?1 [root@BlackGhost test]# ls |grep user

2 alluser.MRG

3 alluser.frm

4 user1.MYD

5 user1.MYI

6 user1.frm

7 user2.MYD

8 user2.MYI

9 user2.frm

简单说明一下，上面的分表呢是利用了merge存储引擎（分表的一种），alluser是总表，下面有二个分表，user1，user2。他们二个都是独立的表，取数据的时候，我们可以通过总表来取。这里总表是没有.MYD,.MYI这二个文件的，也就是说，总表他不是一张表，没有数据，数据都放在分表里面。我们来看看.MRG到底是什么东西

view sourceprint?1 [root@BlackGhost test]# cat alluser.MRG |more

2 user1

3 user2

4 #INSERT_METHOD=LAST

从上面我们可以看出，alluser.MRG里面就存了一些分表的关系，以及插入数据的方式。可以把总表理解成一个外壳，或者是联接池。

b），分区不一样，一张大表进行分区后，他还是一张表，不会变成二张表，但是他存放数据的区块变多了。

view sourceprint?1 [root@BlackGhost test]# ls |grep aa

2 aa#P#p1.MYD

3 aa#P#p1.MYI

4 aa#P#p3.MYD

5 aa#P#p3.MYI

6 aa.frm

7 aa.par

从上面我们可以看出，aa这张表，分为二个区，p1和p3，本来是三个区，被我删了一个区。我们都知道一张表对应三个文件.MYD,.MYI,.frm。分区呢根据一定的规则把数据文件和索引文件进行了分割，还多出了一个.par文件，打开.par文件后你可以看出他记录了，这张表的分区信息，根分表中的.MRG有点像。分区后，还是一张，而不是多张表。

2，数据处理上

a），分表后，数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表里面。看下面的例子：

select * from alluser where id=’12′表面上看，是对表alluser进行操作的，其实不是的。是对alluser里面的分表进行了操作。

b），分区呢，不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的表呢，还是一张表。数据处理还是由自己来完成。

3，提高性能上

a），分表后，单表的并发能力提高了，磁盘I/O性能也提高了。并发能力为什么提高了呢，因为查寻一次所花的时间变短了，如果出现高并发的话，总表可以根据不同的查询，将并发压力分到不同的小表里面。磁盘I/O性能怎么搞高了呢，本来一个非常大的.MYD文件现在也分摊到各个小表的.MYD中去了。

b），mysql提出了分区的概念，我觉得就想突破磁盘I/O瓶颈，想提高磁盘的读写能力，来增加mysql性能。

在这一点上，分区和分表的测重点不同，分表重点是存取数据时，如何提高mysql并发能力上；而分区呢，如何突破磁盘的读写能力，从而达到提高mysql性能的目的。

4），实现的难易度上

a），分表的方法有很多，用merge来分表，是最简单的一种方式。这种方式根分区难易度差不多，并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。

b），分区实现是比较简单的，建立分区表，根建平常的表没什么区别，并且对开代码端来说是透明的。

三，mysql分表和分区有什么联系呢

1，都能提高mysql的性高，在高并发状态下都有一个良好的表面。

2，分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。

另附一段评价：
在以前，一种解决方案就是使用 MERGE

类型，这是一个非常方便的做饭。架构和程序基本上不用做改动，不过，它的缺点是显见的：

只能在相同结构的 MyISAM 表上使用
无法享受到 MyISAM 的全部功能，例如无法在 MERGE 类型上执行 FULLTEXT 搜索
它需要使用更多的文件描述符
读取索引更慢
这个时候，MySQL 5.1 中新增的分区(Partition)功能的优势也就很明显了：

与单个磁盘或文件系统分区相比，可以存储更多的数据
很容易就能删除不用或者过时的数据
一些查询可以得到极大的优化
涉及到 SUM()/COUNT() 等聚合函数时，可以并行进行
IO吞吐量更大
分区允许可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。

Tags - mysql , 分表 , 分区 ]]> http://www.bizeway.net/read.php?622 <![CDATA[mysql分区功能详细介绍以及实例]]> ArthurXF <arthurxf@gmail.com> Sat, 27 Jul 2013 09:39:30 +0000 http://www.bizeway.net/read.php?622 前段时间写过一篇关于mysql分表的的文章，下面来说一下什么是数据库分区，以mysql为例。mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。

备注说明：上面是只对myisam存储引擎的，下面是innodb
innodb的数据库的物理文件结构为：

.frm文件

.ibd文件和.ibdata文件：

这两种文件都是存放innodb数据的文件，之所以用两种文件来存放innodb的数据，是因为innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。

独享表空间存储方式使用.ibd文件，并且每个表一个ibd文件

共享表空间存储方式使用.ibdata文件，所有表共同使用一个ibdata文件

分区的二种方式
1，横向分区
什么是横向分区呢？就是横着来分区了，举例来说明一下，假如有100W条数据，分成十份，前10W条数据放到第一个分区，第二个10W条数据放到第二个分区，依此类推。也就是把表分成了十分，根用merge来分表，有点像哦。取出一条数据的时候，这条数据包含了表结构中的所有字段，也就是说横向分区，并没有改变表的结构。

2，纵向分区
什么是纵向分区呢？就是竖来分区了，举例来说明，在设计用户表的时候，开始的时候没有考虑好，而把个人的所有信息都放到了一张表里面去，这样这个表里面就会有比较大的字段，如个人简介，而这些简介呢，也许不会有好多人去看，所以等到有人要看的时候，在去查找，分表的时候，可以把这样的大字段，分开来。

感觉数据库的分区好像是切苹果，到底是横着切呢，还是竖着切，根据个人喜好了，mysql提供的分区属于第一种，横向分区，并且细分成很多种方式。下面将举例说明一下。

二，mysql的分区
我觉着吧，mysql的分区只有一种方式，只不过运用不同的算法，規则将数据分配到不同的区块中而已。

1，mysql5.1及以上支持分区功能
安装安装的时候，我们就可以查看一下

view sourceprint?1 [root@BlackGhost mysql-5.1.50]# ./configure --help |grep -A 3 Partition

2 === Partition Support ===

3 Plugin Name: partition

4 Description: MySQL Partitioning Support

5 Supports build: static

6 Configurations: max, max-no-ndb

查看一下，如果发现有上面这个东西，说明他是支持分区的，默认是打开的。如果你已经安装过了mysql的话

view sourceprint?1 mysql> show variables like "%part%";

2 +-------------------+-------+

3 | Variable_name | Value |

4 +-------------------+-------+

5 | have_partitioning | YES |

6 +-------------------+-------+

7 1 row in set (0.00 sec)

查看一下变量，如果支持的话，会有上面的提示的。

2，range分区
按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行

view sourceprint?1 //创建range分区表

2 mysql> CREATE TABLE IF NOT EXISTS `user` (

3 -> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',

4 -> `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',

5 -> `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女',

6 -> PRIMARY KEY (`id`)

7 -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

8 -> PARTITION BY RANGE (id) (

9 -> PARTITION p0 VALUES LESS THAN (3),

10 -> PARTITION p1 VALUES LESS THAN (6),

11 -> PARTITION p2 VALUES LESS THAN (9),

12 -> PARTITION p3 VALUES LESS THAN (12),

13 -> PARTITION p4 VALUES LESS THAN MAXVALUE

14 -> );

15 Query OK, 0 rows affected (0.13 sec)

16

17 //插入一些数据

18 mysql> INSERT INTO `test`.`user` (`name` ,`sex`)VALUES ('tank', '0')

19 -> ,('zhang',1),('ying',1),('张',1),('映',0),('test1',1),('tank2',1)

20 -> ,('tank1',1),('test2',1),('test3',1),('test4',1),('test5',1),('tank3',1)

21 -> ,('tank4',1),('tank5',1),('tank6',1),('tank7',1),('tank8',1),('tank9',1)

22 -> ,('tank10',1),('tank11',1),('tank12',1),('tank13',1),('tank21',1),('tank42',1);

23 Query OK, 25 rows affected (0.05 sec)

24 Records: 25 Duplicates: 0 Warnings: 0

25

26 //到存放数据库表文件的地方看一下，my.cnf里面有配置，datadir后面就是

27 [root@BlackGhost test]# ls |grep user |xargs du -sh

28 4.0K user#P#p0.MYD

29 4.0K user#P#p0.MYI

30 4.0K user#P#p1.MYD

31 4.0K user#P#p1.MYI

32 4.0K user#P#p2.MYD

33 4.0K user#P#p2.MYI

34 4.0K user#P#p3.MYD

35 4.0K user#P#p3.MYI

36 4.0K user#P#p4.MYD

37 4.0K user#P#p4.MYI

38 12K user.frm

39 4.0K user.par

40

41 //取出数据

42 mysql> select count(id) as count from user;

43 +-------+

44 | count |

45 +-------+

46 | 25 |

47 +-------+

48 1 row in set (0.00 sec)

49

50 //删除第四个分区

51 mysql> alter table user drop partition p4;

52 Query OK, 0 rows affected (0.11 sec)

53 Records: 0 Duplicates: 0 Warnings: 0

54

55 /**存放在分区里面的数据丢失了，第四个分区里面有14条数据，剩下的3个分区

56 只有11条数据，但是统计出来的文件大小都是4.0K，从这儿我们可以看出分区的

57 最小区块是4K

58 */

59 mysql> select count(id) as count from user;

60 +-------+

61 | count |

62 +-------+

63 | 11 |

64 +-------+

65 1 row in set (0.00 sec)

66

67 //第四个区块已删除

68 [root@BlackGhost test]# ls |grep user |xargs du -sh

69 4.0K user#P#p0.MYD

70 4.0K user#P#p0.MYI

71 4.0K user#P#p1.MYD

72 4.0K user#P#p1.MYI

73 4.0K user#P#p2.MYD

74 4.0K user#P#p2.MYI

75 4.0K user#P#p3.MYD

76 4.0K user#P#p3.MYI

77 12K user.frm

78 4.0K user.par

79

80 /*可以对现有表进行分区,并且会按規则自动的将表中的数据分配相应的分区

81 中，这样就比较好了，可以省去很多事情，看下面的操作*/

82 mysql> alter table aa partition by RANGE(id)

83 -> (PARTITION p1 VALUES less than (1),

84 -> PARTITION p2 VALUES less than (5),

85 -> PARTITION p3 VALUES less than MAXVALUE);

86 Query OK, 15 rows affected (0.21 sec) //对15数据进行分区

87 Records: 15 Duplicates: 0 Warnings: 0

88

89 //总共有15条

90 mysql> select count(*) from aa;

91 +----------+

92 | count(*) |

93 +----------+

94 | 15 |

95 +----------+

96 1 row in set (0.00 sec)

97

98 //删除一个分区

99 mysql> alter table aa drop partition p2;

100 Query OK, 0 rows affected (0.30 sec)

101 Records: 0 Duplicates: 0 Warnings: 0

102

103 //只有11条了，说明对现有的表分区成功了

104 mysql> select count(*) from aa;

105 +----------+

106 | count(*) |

107 +----------+

108 | 11 |

109 +----------+

110 1 row in set (0.00 sec)

3，list分区
LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。

view sourceprint?1 //这种方式失败

2 mysql> CREATE TABLE IF NOT EXISTS `list_part` (

3 -> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',

4 -> `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省',

5 -> `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',

6 -> `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女',

7 -> PRIMARY KEY (`id`)

8 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

9 -> PARTITION BY LIST (province_id) (

10 -> PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),

11 -> PARTITION p1 VALUES IN (9,10,11,12,16,21),

12 -> PARTITION p2 VALUES IN (13,14,15,19),

13 -> PARTITION p3 VALUES IN (17,18,20,22,23,24)

14 -> );

15 ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

16

17 //这种方式成功

18 mysql> CREATE TABLE IF NOT EXISTS `list_part` (

19 -> `id` int(11) NOT NULL COMMENT '用户ID',

20 -> `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省',

21 -> `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',

22 -> `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女'

23 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8

24 -> PARTITION BY LIST (province_id) (

25 -> PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),

26 -> PARTITION p1 VALUES IN (9,10,11,12,16,21),

27 -> PARTITION p2 VALUES IN (13,14,15,19),

28 -> PARTITION p3 VALUES IN (17,18,20,22,23,24)

29 -> );

30 Query OK, 0 rows affected (0.33 sec)

上面的这个创建list分区时，如果有主銉的话，分区时主键必须在其中，不然就会报错。如果我不用主键，分区就创建成功了，一般情况下，一个张表肯定会有一个主键，这算是一个分区的局限性吧。

如果对数据进行测试，请参考range分区的测试来操作

4，hash分区
HASH分区主要用来确保数据在预先确定数目的分区中平均分布，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

view sourceprint?1 mysql> CREATE TABLE IF NOT EXISTS `hash_part` (

2 -> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论ID',

3 -> `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT '评论',

4 -> `ip` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',

5 -> PRIMARY KEY (`id`)

6 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

7 -> PARTITION BY HASH(id)

8 -> PARTITIONS 3;

9 Query OK, 0 rows affected (0.06 sec)

测试请参考range分区的操作

5，key分区
按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 服务器提供。

view sourceprint?1 mysql> CREATE TABLE IF NOT EXISTS `key_part` (

2 -> `news_id` int(11) NOT NULL COMMENT '新闻ID',

3 -> `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',

4 -> `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',

5 -> `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'

6 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8

7 -> PARTITION BY LINEAR HASH(YEAR(create_time))

8 -> PARTITIONS 3;

9 Query OK, 0 rows affected (0.07 sec)

测试请参考range分区的操作

6，子分区
子分区是分区表中每个分区的再次分割，子分区既可以使用HASH希分区，也可以使用KEY分区。这也被称为复合分区（composite partitioning）。

1，如果一个分区中创建了子分区，其他分区也要有子分区

2，如果创建了了分区，每个分区中的子分区数必有相同

3，同一分区内的子分区，名字不相同，不同分区内的子分区名子可以相同（5.1.50不适用）

view sourceprint?1 mysql> CREATE TABLE IF NOT EXISTS `sub_part` (

2 -> `news_id` int(11) NOT NULL COMMENT '新闻ID',

3 -> `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',

4 -> `u_id` int(11) NOT NULL DEFAULT 0s COMMENT '来源IP',

5 -> `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'

6 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8

7 -> PARTITION BY RANGE(YEAR(create_time))

8 -> SUBPARTITION BY HASH(TO_DAYS(create_time))(

9 -> PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2),

10 -> PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good),

11 -> PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3)

12 -> );

13 Query OK, 0 rows affected (0.07 sec)

三，分区管理
1，删除分区

mysql> alter table user drop partition p4;
2，新增分区

view sourceprint?1 //range添加新分区

2 mysql> alter table user add partition(partition p4 values less than MAXVALUE);

3 Query OK, 0 rows affected (0.06 sec)

4 Records: 0 Duplicates: 0 Warnings: 0

5

6 //list添加新分区

7 mysql> alter table list_part add partition(partition p4 values in (25,26,28));

8 Query OK, 0 rows affected (0.01 sec)

9 Records: 0 Duplicates: 0 Warnings: 0

10

11 //hash重新分区

12 mysql> alter table hash_part add partition partitions 4;

13 Query OK, 0 rows affected (0.12 sec)

14 Records: 0 Duplicates: 0 Warnings: 0

15

16 //key重新分区

17 mysql> alter table key_part add partition partitions 4;

18 Query OK, 1 row affected (0.06 sec) //有数据也会被重新分配

19 Records: 1 Duplicates: 0 Warnings: 0

20

21 //子分区添加新分区，虽然我没有指定子分区，但是系统会给子分区命名的

22 mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE);

23 Query OK, 0 rows affected (0.02 sec)

24 Records: 0 Duplicates: 0 Warnings: 0

25

26 mysql> show create table sub1_partG;

27 *************************** 1. row ***************************

28 Table: sub1_part

29 Create Table: CREATE TABLE `sub1_part` (

30 `news_id` int(11) NOT NULL COMMENT '新闻ID',

31 `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',

32 `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',

33 `create_time` date NOT NULL DEFAULT '0000-00-00' COMMENT '时间'

34 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

35 !50100 PARTITION BY RANGE (YEAR(create_time))

36 SUBPARTITION BY HASH (TO_DAYS(create_time))

37 (PARTITION p0 VALUES LESS THAN (1990)

38 (SUBPARTITION s0 ENGINE = InnoDB,

39 SUBPARTITION s1 ENGINE = InnoDB,

40 SUBPARTITION s2 ENGINE = InnoDB),

41 PARTITION p1 VALUES LESS THAN (2000)

42 (SUBPARTITION s3 ENGINE = InnoDB,

43 SUBPARTITION s4 ENGINE = InnoDB,

44 SUBPARTITION good ENGINE = InnoDB),

45 PARTITION p2 VALUES LESS THAN (3000)

46 (SUBPARTITION tank0 ENGINE = InnoDB,

47 SUBPARTITION tank1 ENGINE = InnoDB,

48 SUBPARTITION tank3 ENGINE = InnoDB),

49 PARTITION p3 VALUES LESS THAN MAXVALUE

50 (SUBPARTITION p3sp0 ENGINE = InnoDB, //子分区的名子是自动生成的

51 SUBPARTITION p3sp1 ENGINE = InnoDB,

52 SUBPARTITION p3sp2 ENGINE = InnoDB))

53 1 row in set (0.00 sec)

3，重新分区

view sourceprint?1 //range重新分区

2 mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);

3 Query OK, 11 rows affected (0.08 sec)

4 Records: 11 Duplicates: 0 Warnings: 0

5

6 //list重新分区

7 mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));

8 Query OK, 0 rows affected (0.28 sec)

9 Records: 0 Duplicates: 0 Warnings: 0

10

11 //hash和key分区不能用REORGANIZE，官方网站说的很清楚

12 mysql> ALTER TABLE key_part REORGANIZE PARTITION COALESCE PARTITION 9;

13 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION 9' at line 1

四，分区优点
1，分区可以分在多个磁盘，存储更大一点

2，根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了

3，进行大数据搜索时可以进行并行处理。

4，跨多个磁盘来分散数据查询，来获得更大的查询吞吐量

课外阅读
：http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html

Tags - mysql , 分区 ]]> http://www.bizeway.net/read.php?621 <![CDATA[mysql分表的3种方法]]> ArthurXF <arthurxf@gmail.com> Sat, 27 Jul 2013 09:38:40 +0000 http://www.bizeway.net/read.php?621 当一张的数据达到几百万时，你查询一次所花的时间会变多，如果有联合查询的话，我想有可能会死在那儿了。分表的目的就在于此，减小数据库的负担，缩短查询时间。

根据个人经验，mysql执行一个sql的过程如下：
1,接收到sql;

2,把sql放到排队队列中 ;

3,执行sql;

4,返回执行结果。

在这个执行过程中最花时间在什么地方呢？第一，是排队等待的时间，第二，sql的执行时间。其实这二个是一回事，等待的同时，肯定有sql在执行。所以我们要缩短sql的执行时间。

mysql中有一种机制是表锁定和行锁定，为什么要出现这种机制，是为了保证数据的完整性，我举个例子来说吧，如果有二个sql都要修改同一张表的同一条数据，这个时候怎么办呢，是不是二个sql都可以同时修改这条数据呢？很显然mysql对这种情况的处理是，一种是表锁定（myisam存储引擎），一个是行锁定（innodb存储引擎）。表锁定表示你们都不能对这张表进行操作，必须等我对表操作完才行。行锁定也一样，别的sql必须等我对这条数据操作完了，才能对这条数据进行操作。如果数据太多，一次执行的时间太长，等待的时间就越长，这也是我们为什么要分表的原因。

二，分表
1，做mysql集群，例如：利用mysql cluster ，mysql proxy，mysql replication，drdb等等
有人会问mysql集群，根分表有什么关系吗？虽然它不是实际意义上的分表，但是它启到了分表的作用，做集群的意义是什么呢？为一个数据库减轻负担，说白了就是减少sql排队队列中的sql的数量，举个例子：有10个sql请求，如果放在一个数据库服务器的排队队列中，他要等很长时间，如果把这10个sql请求，分配到5个数据库服务器的排队队列中，一个数据库服务器的队列中只有2个，这样等待时间是不是大大的缩短了呢？这已经很明显了。所以我把它列到了分表的范围以内

优点：扩展性好，没有多个分表后的复杂操作（php代码）

缺点：单个表的数据量还是没有变，一次操作所花的时间还是那么多，硬件开销大。

2，预先估计会出现大数据量并且访问频繁的表，将其分为若干个表
这种预估大差不差的，论坛里面发表帖子的表，时间长了这张表肯定很大，几十万，几百万都有可能。聊天室里面信息表，几十个人在一起一聊一个晚上，时间长了，这张表的数据肯定很大。像这样的情况很多。所以这种能预估出来的大数据量表，我们就事先分出个N个表，这个N是多少，根据实际情况而定。以聊天信息表为例：

我事先建100个这样的表，message_00,message_01,message_02……….message_98,message_99.然后根据用户的ID来判断这个用户的聊天信息放到哪张表里面，你可以用hash的方式来获得，可以用求余的方式来获得，方法很多，各人想各人的吧。下面用hash的方法来获得表名：

view sourceprint?1
2 function get_hash_table($table,$userid) {

3 $str = crc32($userid);

4 if($str<0){

5 $hash = "0".substr(abs($str), 0, 1);

6 }else{

7 $hash = substr($str, 0, 2);

8 }

9

10 return $table."_".$hash;

11 }

12

13 echo get_hash_table('message','user18991'); //结果为message_10

14 echo get_hash_table('message','user34523'); //结果为message_13

15 ?>

说明一下，上面的这个方法，告诉我们user18991这个用户的消息都记录在message_10这张表里，user34523这个用户的消息都记录在message_13这张表里，读取的时候，只要从各自的表中读取就行了。

优点：避免一张表出现几百万条数据，缩短了一条sql的执行时间

缺点：当一种规则确定时，打破这条规则会很麻烦，上面的例子中我用的hash算法是crc32，如果我现在不想用这个算法了，改用md5后，会使同一个用户的消息被存储到不同的表中，这样数据乱套了。扩展性很差。

—————PHP10086:根据userid，或者时间拆分，建立路由规则调度，确定聚合查询的时候麻烦。

3，利用merge存储引擎来实现分表
我觉得这种方法比较适合，那些没有事先考虑，而已经出现了得，数据查询慢的情况。这个时候如果要把已有的大数据量表分开比较痛苦，最痛苦的事就是改代码，因为程序里面的sql语句已经写好了，现在一张表要分成几十张表，甚至上百张表，这样sql语句是不是要重写呢？举个例子，我很喜欢举子

mysql>show engines;的时候你会发现mrg_myisam其实就是merge。

view sourceprint?1 mysql> CREATE TABLE IF NOT EXISTS `user1` (

2 -> `id` int(11) NOT NULL AUTO_INCREMENT,

3 -> `name` varchar(50) DEFAULT NULL,

4 -> `sex` int(1) NOT NULL DEFAULT '0',

5 -> PRIMARY KEY (`id`)

6 -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

7 Query OK, 0 rows affected (0.05 sec)

8

9 mysql> CREATE TABLE IF NOT EXISTS `user2` (

10 -> `id` int(11) NOT NULL AUTO_INCREMENT,

11 -> `name` varchar(50) DEFAULT NULL,

12 -> `sex` int(1) NOT NULL DEFAULT '0',

13 -> PRIMARY KEY (`id`)

14 -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

15 Query OK, 0 rows affected (0.01 sec)

16

17 mysql> INSERT INTO `user1` (`name`, `sex`) VALUES('张映', 0);

18 Query OK, 1 row affected (0.00 sec)

19

20 mysql> INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1);

21 Query OK, 1 row affected (0.00 sec)

22

23 mysql> CREATE TABLE IF NOT EXISTS `alluser` (

24 -> `id` int(11) NOT NULL AUTO_INCREMENT,

25 -> `name` varchar(50) DEFAULT NULL,

26 -> `sex` int(1) NOT NULL DEFAULT '0',

27 -> INDEX(id)

28 -> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ;

29 Query OK, 0 rows affected, 1 warning (0.00 sec)

30

31 mysql> select id,name,sex from alluser;

32 +----+--------+-----+

33 | id | name | sex |

34 +----+--------+-----+

35 | 1 | 张映 | 0 |

36 | 1 | tank | 1 |

37 +----+--------+-----+

38 2 rows in set (0.00 sec)

39

40 mysql> INSERT INTO `alluser` (`name`, `sex`) VALUES('tank2', 0);

41 Query OK, 1 row affected (0.00 sec)

42

43 mysql> select id,name,sex from user2

44 -> ;

45 +----+-------+-----+

46 | id | name | sex |

47 +----+-------+-----+

48 | 1 | tank | 1 |

49 | 2 | tank2 | 0 |

50 +----+-------+-----+

51 2 rows in set (0.00 sec)

从上面的操作中，我不知道你有没有发现点什么？假如我有一张用户表user，有50W条数据，现在要拆成二张表user1和user2，每张表25W条数据，

INSERT INTO user1(user1.id,user1.name,user1.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id <= 250000

INSERT INTO user2(user2.id,user2.name,user2.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id > 250000

这样我就成功的将一张user表，分成了二个表，这个时候有一个问题，代码中的sql语句怎么办，以前是一张表，现在变成二张表了，代码改动很大，这样给程序员带来了很大的工作量，有没有好的办法解决这一点呢？办法是把以前的user表备份一下，然后删除掉，上面的操作中我建立了一个alluser表，只把这个alluser表的表名改成user就行了。但是，不是所有的mysql操作都能用的

a，如果你使用 alter table 来把 merge 表变为其它表类型，到底层表的映射就被丢失了。取而代之的，来自底层 myisam 表的行被复制到已更换的表中，该表随后被指定新类型。

b，网上看到一些说replace不起作用，我试了一下可以起作用的。晕一个先

view sourceprint?1 mysql> UPDATE alluser SET sex=REPLACE(sex, 0, 1) where id=2;

2

3 Query OK, 1 row affected (0.00 sec)

4

5 Rows matched: 1 Changed: 1 Warnings: 0

6

7

8

9 mysql> select * from alluser;

10

11 +----+--------+-----+

12

13 | id | name | sex |

14

15 +----+--------+-----+

16

17 | 1 | 张映 | 0 |

18

19 | 1 | tank | 1 |

20

21 | 2 | tank2 | 1 |

22

23 +----+--------+-----+

24

25 3 rows in set (0.00 sec)

c，一个 merge 表不能在整个表上维持 unique 约束。当你执行一个 insert，数据进入第一个或者最后一个 myisam 表（取决于 insert_method 选项的值）。mysql 确保唯一键值在那个 myisam 表里保持唯一，但不是跨集合里所有的表。

d,当你创建一个 merge 表之时，没有检查去确保底层表的存在以及有相同的机构。当 merge 表被使用之时，mysql 检查每个被映射的表的记录长度是否相等，但这并不十分可靠。如果你从不相似的 myisam 表创建一个 merge 表，你非常有可能撞见奇怪的问题。

好困睡觉了，c和d在网上看到的，没有测试，大家试一下吧。

优点：扩展性好，并且程序代码改动的不是很大

缺点：这种方法的效果比第二种要差一点

三，总结一下

上面提到的三种方法，我实际做过二种，第一种和第二种。第三种没有做过，所以说的细一点。哈哈。做什么事都有一个度，超过个度就过变得很差，不能一味的做数据库服务器集群，硬件是要花钱买的，也不要一味的分表，分出来1000表，mysql的存储归根到底还以文件的形势存在硬盘上面，一张表对应三个文件，1000个分表就是对应3000个文件，这样检索起来也会变的很慢。我的建议是

方法1和方法2结合的方式来进行分表

方法1和方法3结合的方式来进行分表

我的二个建议适合不同的情况，根据个人情况而定，我觉得会有很多人选择方法1和方法3结合的方式

—–PHP10086手记:
关于merge 表分表：
聚合表alluser 要指定DEFAULT CHARSET=utf8，统一编码不然报错。
子表需要是MyISAM引擎，仅仅适合与myisam引擎的表
每个子表的结构必须一致，主表和子表的结构需要一致
每个子表的索引在merge表中都会存在，所以在merge表中不能根据该索引进行唯一性检索
REPLACE在merge表中不会工作
AUTO_INCREMENT 不会按照你所期望的方式工作-
创建Mysql Merge表的参数 INSERT_METHOD有几个参数：

LAST 如果你执行insert 指令来操作merge表时，插入操作会把数据添加到最后一个子表中。

FIRST 同理，执行插入数据时会把数据添加到第一个子表中。

关于merge删除
如果删除mrg表，那么各个子表间将不会有联系。但是如果删除其中的任一子表，对于GNU／LINUX来说，merge表结构及数据仍然存在。

Tags - mysql , 分表 ]]> http://www.bizeway.net/read.php?610 <![CDATA[MySQL效能监控工具mysqlreport安装和中文说明 (ubuntu) ]]> ArthurXF <arthurxf@gmail.com> Fri, 26 Oct 2012 03:26:26 +0000 http://www.bizeway.net/read.php?610
官方网站： http://hackmysql.com/
软件下载： http://hackmysql.com/mysqlreport

mysqlreport以很友好的方式显示 MySQL状态变。事实上，它几乎报告了所有的状态。不像 SHOW STATUS 只是在显示了100多个状态值，mysqlreport 则以人性化的方式阐释和格式化了这些状态值，大大增加了其可读性。可以点击这里查看mysqlreport的例子。

mysqlreport 的好处是可以快速的查看各种状态参数组，从而了解服务器的运行状态情况，而无需从 SHOW STATUS 的结果中人工计算。例如索引读取比率是个重要的参数，但是 SHOW STATUS 中并没有显示；它是一个推断值(key_reads 和 key_read_requests 的比值)。

mysql命令行中精彩使用下面的指令来获取当前数据库的实时状态：

mysql>show status;
mysql>show innodb status;

但是他们的显示结果不太友好，我们需要更好的更加人性化的分析结果，而不是堆出来一堆数字。mysqlreport是一个第三方的Mysql状态报告工具，它把mysql的show status 和 show innodb status的结果进行一系列的后期处理，让可读性更强，更友好。下面是mysqlreport的安装过程：

MySQLReport 是用perl语言编写，所以想要运行它首先需要安装perl环境；它还要与MySQL数据库连接，所以还需要安装数据库接口 DBI 和数据库驱动 DBD-MySQL 。

# perl -v //如果显示perl版本说明perl环境已经安装

安装DBI

# wget http://ftp.cuhk.edu.hk/pub/packages/perl/CPAN/authors/id/T/TI/TIMB/DBI-1.616.tar.gz
# tar zxvf DBI-1.616.tar.gz
# cd DBI-1.616
# perl Makefile.PL
# make
#make test
# make
# make install

安装DBD-mysql

我看了DBD::mysql的README，原来ubuntu是不需要安装DBD::mysql

下载mysqlreport

# cd /usr/local/src/
# wget http://hackmysql.com/scripts/mysqlreport-3.5.tgz

解压mysqlreport

# tar zxvf mysqlreport-3.5.tgz
# cd mysqlreport-3.5
# mysqlreport --help

mysqlreport v3.5 Apr 16 2008
mysqlreport makes an easy-to-read report of important MySQL status values.

Command line options (abbreviations work):
--user USER Connect to MySQL as USER
--password PASS Use PASS or prompt for MySQL user's password
--host ADDRESS Connect to MySQL at ADDRESS
--port PORT Connect to MySQL at PORT
--socket SOCKET Connect to MySQL at SOCKET
--no-mycnf Don't read ~/.my.cnf
--infile FILE Read status values from FILE instead of MySQL
--outfile FILE Write report to FILE
--email ADDRESS Email report to ADDRESS (doesn't work on Windows)
--flush-status Issue FLUSH STATUS; after getting current values
--relative X Generate relative reports. If X is an integer,
reports are live from the MySQL server X seconds apart.
If X is a list of infiles (file1 file2 etc.),
reports are generated from the infiles in the order
that they are given.
--report-count N Collect N number of live relative reports (default 1)
--detach Fork and detach from terminal (run in background)
--help Prints this
--debug Print debugging information

Visit http://hackmysql.com/mysqlreport for more information.

使用mysqlreport

# mysqlreport -user *** -password ***

以下是mysqlreport的命令选项

命令行选项的格式是 --选项，不过 -选项的格式也可以。所有的选项都有其缩写，只要是唯一的。例如，选项 --host 可以缩写成 --ho，不过不能写成 --h，因为 --h 有歧义，可能是 --host 或者 --help。

选项列表

--user USER
--password
--host ADDRESS
--port PORT
--socket SOCKET
--no-mycnf

--help 这些选项是模拟其他标准应用程序的。从 2.3 版本开始，--password 可以在后面加上参数，如 "--password FOO"。如果命令行中只指定了选项

--password 则会提示输入密码。--no-mycnf 告诉 mysqlreport 不要读取 ~/.my.cnf，默认会去读取这个文件。--user 和 --password 总是覆盖从 ~/.my.cnf 中取得的结果。

--infile FILE 直接从文件中读取状态文件，而不是从MySQL的 SHOW STATUS 中读取。文件内容通常是从 SHOW STATUS 的结果中取得，并且包含格式化字符(|, +, -)。mysqlreport 认为这样的文件"状态名数值"格式的，状态包含字符和下划线(A-Z 和 _)，数值则是非负整数。在状态名和数值之间的任何内容都会被忽略。mysqlreport 也需要以下MySQL服务器系统变量：version, table_cache, max_connections, key_buffer_size, query_cache_size, thread_cache_size。

INFILE 的格式也可以是 "名字 = 数值"这样的，名字可以使是上面提到的各种变量名，数值是非负整数，可能后面带有M或者其他单位(根据版本不同而定)。例如，想要指定 18M 的 key_buffer_size：key_buffer_size = 18M。或者，256 个 table_cache：table_cache = 256。M 指兆字节，而非百万。因此 18M 是 18,874,368，而非 18,000,000。如果这些服务器变量没有指定，则使用默认以下默认值：0.0.0, 64, 100, 8M, 0, 0，就可能会让报告结果看起来很奇怪。

注意：MySQL 服务器版本在 5.1.3 或更新时，尽管系统变量 table_cache 改成了 table_open_cache，但是读取本地文件时仍采用 table_cache。

--outfile FILE 在屏幕显示完报告结果后，将结果写入文件中。mysqlreport 的内部机制总是先将结果写入临时文件中。然后将该临时文件里的内容打印到屏幕上。然后，如果指定了 --outfile 选项，则将临时文件拷贝成 OUTFILE。如果指定选项 --email，则会删除临时文件。

--email ADDRESS 在屏幕显示完结果后，将结果发送到邮件地址 ADDRESS 中去。欲该选项，需要在 /usr/sbin/ 目录下有 sendmail 程序，因此无法在 windows 平台下使用。/usr/sbin/sendmail 可以符号链接到 qmail，或者任何其他能模拟 sendmail -t 方式的 MTA 程序。邮件来源是：mysqlreport，主题是：MySQL status report on HOST，HOST 是 mysqlreport所在的主机名，可能是读取到的 --host 值，默认是 localhost。

--flush-status 显示完报告后，执行 "FLUSH STATUS;" 语句。如果没有权限，则 DBD::mysql 会显示返回值。

--relative (-r) X mysqlreport 通常情况下报告的是自从 MySQL 服务器启动以来的状态信息。--relative 选项则是令 mysqlreport 产生一份自从上次报告以来的相关报告。

如果 --relative X 的 X 值是一个整数，则 mysqlreport 会在隔 X 秒后再次产生一份 MySQL 服务器的状态报告。产生报告的次数是由 --report-count 选项来控制的。默认是产生 1 份相关的报告。例如，指定 --relative 的值为 60，则会产生 2 份报告：第一份会马上生成，第二份会在 60 秒后再次生成。第二份报告中的数值会和前面的那份相关。例如，前面那份中总共有 10.00k 次查询，在这 60 秒的间隔时间里接受了新的 1.00k 次查询，则第二份的报告中的总查询次数是 1.00k 而非 11.00k 次。

如果 --relative 选项的值也可以是本地文件(类似 --infile 选项的用法)，那么 mysqlreport 会按照参数值中文件的顺序来依次产生状态报告。因此，根据这些文件产生的时间来指定选项的值非常重要：较早产生的文件放在参数的前面。第一个文件中必须有手工添加的系统变量，例如：key_buffer_size、table_cache 等。每个文件中可以有多组 "SHOW STATUS" 的结果。注意：通过 "mysqladmin -r -i N extended" 产生的状态文件无法使用，因为 mysqladmin 的 -r 参数已经令其产生了具有相对性的状态值了。

由于 mysqlreport 首先会把状态报告写到临时文件中，如果 --relative 的值是整数(而非本地文件)时，mysqlreport 会显示它把文件写到哪了。那么就可以直接通过查看这些文件内容来观察服务器的状况了。

--report-count (-c) N 生成 N 份相关的报告。本选项只有在同时启用 --relative 选项后才有效。mysqlreport 会自动产生 N+1 份报告：第一份基本报告，以及后面的 N 份相关报告。

--detach 若指定本选项，则 mysqlreport 会派生出进程来，不只是在屏幕显示结果，还会转入后台继续运行。派生新进程后，mysqlreport 会报告它把结果写入哪个临时文件了。本选项还可以指定 --outfile 或 --email 的一个。如果没有指定 --outfile 或 --email 的值，则产生的临时文件会被删除，因为 mysqlreport 派生出新进程后，无法再将结果打印到终端屏幕上了。本选项如果和 --relative 一起使用的话就更有意义了，这样 mysqlreport 就能定时报告信息，而无需人工登录等方式在中断执行了。使用如下的命令，就能让 mysqlrepot 隔一个小时再次产生一次报告，并将结果发送到自己的信箱中去：

# mysqlreport -r 3600 -detach -email host@domain.com

一个小时候后，mysqlreport 通过email发送报告，删除临时文件，并且干净地终止。

--debug 显示调试信息。

--dtq (Questions 报告的 Total 部分中) 显示所有的查询分布报告。这些查询主要包括以下四部分：DMS (见下面)、COM_ (见下面)、COM_QUIT(见 COM_QUIT and Questions)、以及其他未知。每部分根据其总数倒序显示。

--dms (Questions 报告的 DMS 部分中) 显示所有的数据维护语句(DMS)报告。DMS是下面文档 13.2. Data Manipulation Statements 中提到的那些(当前主要有：SELECT, INSERT, REPLACE, UPDATE, and DELETE)。每个 DMS 根据其总数倒序显示。

--com N (Questions 报告之后) 以降序显示最多 N 个非DMS Com_ 状态值。如果没有指定 N 的值，则默认是 3。所谓的非DMS Com_ 状态值，包括：Com_change_db、Com_show_tables、Com_rollback 等。

--sas (Questions 报告之后) 显示所有的 Select_ 和 Sort_ 报告。详情请看 MySQL Select and Sort Status Variables。

--qcache 如果打开查询缓存的话，则显示查询缓存状态报告

--tab (Create Temp 报告之后) 显示线程、放弃的、流量等状态报告。从 mysqlreport v2.3 开始，线程状态是从 Threads_ 状态值读取。

--innodb 显示 InnoDB 状态报告，包括MySQL 5.0.2以后才支持的InnoDB 缓冲池，以及5.0.3以后才支持的InnoDB锁状态报告。

--innodb-only 只显示 InnoDB 报告；不显示其他报告。

--dpr 显示 InnoDB 数据，页，行报告。

--all 如果可能，则显示所有的状态报告。一些报告，比如查询缓存、InnoDB等需要特定版本的MySQL或者其他特性才能支持。例如，尽管服务器支持查询缓存，但是它被禁用了，则不管是否指定了 --qcache 或者 --all，都不会显示查询缓存的报告。

Tags - mysql ]]> http://www.bizeway.net/read.php?597 <![CDATA[Mysql长连接]]> ArthurXF <arthurxf@gmail.com> Fri, 30 Dec 2011 10:52:51 +0000 http://www.bizeway.net/read.php?597
不同于mysql_connect的短连接，mysql_pconnect持久连接的时候，将先尝试寻找一个在同一个主机上用同样的用户名和密码已经打开的（持久）连接，如果找到，则返回此连接标识而不打开新连接。
当执行完毕后，到 mysql 服务器的持久连接不会被关闭，此连接将保持打开以备以后使用，即mysql_close() 不会关闭由 mysql_pconnect() 建立的连接。

Apache与长连接管理

PHP本身并没有数据库连接池的概念，但是Apache有进程池的概念, 一个Apache子进程结束后会被放回进程池, 这也就使得用mysql_pconnect打开的的那个mysql连接资源可以不被释放，而是依附在相应的Apache子进程上保存到了进程池中。于是在下一个连接请求时它就可以被复用。但是在Apache并发访问量大的时候，如果使用mysql_pconnect，会由于之前的Apache子进程占用的MySQL连接没有close, 很快使MySQL达到最大连接数，使得之后的请求可能得不到响应。
当然，高并发情况下也不能怪罪pconnect，用短连接频繁连接mysql，也一样有问题。在没有连接池的情况下，用apache做连接池管理是比较好的选择。

Tags - mysql , pconnect ]]> http://www.bizeway.net/read.php?594 <![CDATA[在服务器上mysql 导入导出 sql文件]]> ArthurXF <arthurxf@gmail.com> Mon, 17 Oct 2011 04:16:47 +0000 http://www.bizeway.net/read.php?594
mysqldump -u 用户名 -p 数据库名 > 导出的文件名

mysqldump -u root -p biweb> biweb.sql

2.导出一个表

mysqldump -u 用户名 -p 数据库名表名> 导出的文件名

mysqldump -u root -p biweb users> biweb_users.sql

3.导出一个数据库结构

mysqldump -u root -p -d --add-drop-table biweb>biweb.sql

-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table

4.导入数据库

mysql -u 用户名 -p 数据库名 < 存放位置
mysql -u root -p biwebtuan
Tags - mysql ]]> http://www.bizeway.net/read.php?588 <![CDATA[用bin日志中恢复MySQL数据库]]> ArthurXF <arthurxf@gmail.com> Mon, 18 Apr 2011 08:57:14 +0000 http://www.bizeway.net/read.php?588
要想从二进制日志恢复数据，你需要知道当前二进制日志文件的路径和文件名。一般可以从选项文件(即my.cnf or my.ini，取决于你的系统)中找到路径。如果未包含在选项文件中，当服务器启动时，可以在命令行中以选项的形式给出。启用二进制日志的选项为–log-bin。要想确定当前的二进制日志文件的文件名，输入下面的MySQL语句：

SHOW BINLOG EVENTS G

你还可以从命令行输入下面的内容：

mysql –user=root -pmy_pwd -e ‘SHOW BINLOG EVENTS G’

将密码my_pwd替换为服务器的root密码。

1. 指定恢复时间

对于MySQL 4.1.4，可以在mysqlbinlog语句中通过–start-date和–stop-date选项指定DATETIME格式的起止时间。举例说明，假设在今天上午10:00(今天是2006年4月20日)，执行SQL语句来删除一个大表。要想恢复表和数据，你可以恢复前晚上的备份，并输入：

mysqlbinlog –stop-date=”2005-04-20 9:59:59″ /var/log/mysql/bin.123456

| mysql -u root -pmypwd

该命令将恢复截止到在–stop-date选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句，可能你想要恢复后面发生的活动。根据这些，你可以用起使日期和时间再次运行mysqlbinlog：

mysqlbinlog –start-date=”2005-04-20 10:01:00″ /var/log/mysql/bin.123456

| mysql -u root -pmypwd

在该行中，从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。

2. 指定恢复位置

也可以不指定日期和时间，而使用mysqlbinlog的选项–start-position和–stop-position来指定日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。操作方法为：

mysqlbinlog –start-date=”2005-04-20 9:55:00″ –stop-date=”2005-04-20 10:05:00″

/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

该命令将在/tmp目录创建小的文本文件，将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件，寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作，应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后，你应从命令行输入下面内容：

mysqlbinlog –stop-position=”368312″ /var/log/mysql/bin.123456

mysqlbinlog –start-position=”368315″ /var/log/mysql/bin.123456

上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句，恢复的数据和相关MySQL日志将反应事务执行的原时间

3.指定数据库名
如果一台服务器上有多个库，指定数据库名将不会干扰其他的数据库。格式如下：
mysqlbinlog --database="5217u" --start-date="2012-04-01 00:00:00" --stop-date="2012-05-01 08:00:00" mysql-bin.000088 > restore.sql

切记database是库名不是表名。

4.整理干净的sql文档
虽然我们可以通过上面种种条件指定恢复相应的数据，不过mysalbinlog生成的sql文件会有很多其他的代码。造成sql文件过大，打开缓慢甚至打不开。
我这里给出下列vi命令，来精简sql文件。

删除所有#号开头的行
:%s/^#.*//g
删除所有SET开头的行
:%s/^SET.*//g
删除所有BEGIN的行
:%s/^BEGIN//g
删除所有COMMIT的行
:%s/^COMMIT//g
删除所有的/*!*/;的行
:%s/\/\*\!\*\/;//g
删除/*!..*/的行
:%s/\/\*\!.*//g
删除空行
:g/^$/d

下面是我清除问题数据留底的，读者不能直接使用，不过可以借鉴里面的，跨行多行删除的正则表达式写法
注意：跨行多行的写法只支持vim,vi是不支持的
:%s/^REPLACE INTO biweb_d\_.\{-})$//g
:%s/^REPLACE INTO biweb_u\_.\{-})$//g
:%s/^REPLACE INTO biweb_m\_.\{-})$//g
:%s/^REPLACE INTO biweb_n\_.\{-})$//g
:%s/^REPLACE INTO biweb_a\_.\{-})$//g
:%s/^REPLACE INTO biweb_f\_.\{-})$//g
:%s/^REPLACE INTO biweb_task\_.\{-})$//g
:%s/^UPDATE biweb_d\_.\{-}'$//g
:%s/^UPDATE biweb_a\_.\{-}'$//g
:%s/^UPDATE biweb_u\_.\{-}'$//g
:%s/^UPDATE biweb_m\_.\{-}'$//g
:%s/^DELETE FROM biweb_m.*//g
:%s/^DELETE FROM biweb_d.*//g
:%s/^DELETE FROM biweb_a.*//g
:%s/^UPDATE biweb_task.*//g
:%s/^UPDATE biweb_n\_.\{-})$//g
:%s/^UPDATE biweb_n.*//g
:%s/^ALTER TABLE `biweb_a.*//g
:%s/^UPDATE `5217u`.`biweb_a.*//g
:%s/^UPDATE `5217u`.`biweb_u.*//g
:%s/^UPDATE `5217u`.`biweb_m.*//g
:g/^$/d

Tags - mysql , bin ]]> http://www.bizeway.net/read.php?571 <![CDATA[phpMyAdmin一个用户只能管理自己数据库的设置方法]]> ArthurXF <arthurxf@gmail.com> Tue, 12 Oct 2010 03:38:59 +0000 http://www.bizeway.net/read.php?571
其实方法很简单，跟着我ArthurXF学习即可。
1.先用root登录phpMyAdmin
2.在phpMyAdmin首页面右边左部部导航处找到权限栏目。
3.打开权限栏目后，新增一个用户，输入用户名，主机写localhost，再输入密码，切记不要点击旁边的生成密码。
4.Database for user处可以选建立和用户名同名的数据库，当然前提是你要开一个跟用户名同名的数据库给用户。
5.下面的权限可以不选，直接点执行，数据库会默认设定权限的。
6.退出，用新用户名和密码登录phpMyAdmin，一切就OK了。

本文由ArthurXF倾情奉献，转载请保留本人小名，谢谢。
Tags - phpmyadmin ]]> http://www.bizeway.net/read.php?565 <![CDATA[肖飞：导出mysql某张表中部分数据]]> ArthurXF <arthurxf@gmail.com> Wed, 11 Aug 2010 08:05:39 +0000 http://www.bizeway.net/read.php?565 select `FullName`,`Company`,`Address`,`ZipCode`,`Phone`,`Fax`,`Email` from tb_member order by `MemberID` desc limit 0,3000 into outfile '5217u.txt'
这个语句在phpMyAdmin中就可以执行，执行成功后，5217U.txt文件会存在你数据库的文件夹下。当然你可以指定保存路径。
例如：
select `FullName`,`Company`,`Address`,`Phone`,`Fax`,`Email` from tb_member limit 0,3000 into outfile '/user/home/mysql/tbname.txt' ;
这么保存一定切记要mysql可以有写的权限才能成功。

这个方法可以解决一些GBK和utf8在phpMyAdmin中看到乱码的数据导出。
本文肖飞倾情奉献，转载请注明出处，谢谢。
Tags - mysql , 导出数据 ]]> http://www.bizeway.net/read.php?554 <![CDATA[mysql latin1转utf8 的两种方法]]> ArthurXF <arthurxf@gmail.com> Thu, 27 May 2010 11:03:27 +0000 http://www.bizeway.net/read.php?554
方法一：

步骤一命令行执行：mysqldump --opt -hlocalhost -uroot -p*** --default-character-set=latin1 dbname > /usr/local/dbname.sql

步骤二将 dbname.sql文件中的create table语句的CHARSET=latin1改为CHARSET=utf8

步骤三在dbname.sql文件中的insert语句之前加一条'set names utf8;'

步骤四将dbname.sql转码为utf-8格式，建议使用UltraEditor，可以直接使用该编辑器的'转换->ASCII到UTF-8(Unicode编辑)'，或者将文件另存为UTF-8(无BOM)格式

步骤五命令行执行：mysql -hlocalhost -uroot -p*** --default-character-set=utf8 new_dbname < /usr/local/dbname.sql

总结：这种方法有个致命之处就是当数据中有大量中文字符和其他特殊符号字符时，很有可能导致在[步骤五]时报错导致无法正常导入数据，如果数据库比较大可以分别对每张表执行上述步骤

方法二（推荐大家使用）：

为了解决第一种方法中总结时说到的问题，在网上苦苦查找了一天资料才东拼西凑的搞出一个比较稳妥的解决方法

步骤一将待导出的数据表的表结构导出（可以用Phpmyadmin、mysqldump等，很简单就不说了），然后将导出的create table语句的CHARSET=latin1改为CHARSET=utf8，在目标库newdbname中执行该create table语句把表结构建好，接下来开始导出-导入数据

步骤二命令行：进入mysql命令行下，mysql -hlocalhost -uroot -p*** dbname

步骤三执行SQL select * from tbname into outfile '/usr/local/tbname.sql';

步骤四将tbname.sql转码为utf-8格式，建议使用UltraEditor，可以直接使用该编辑器的'转换->ASCII到UTF-8(Unicode编辑)'，或者将文件另存为UTF-8(无BOM)格式

步骤五在mysql命令行下执行语句 set character_set_database=utf8; 注：设置mysql的环境变量，这样mysql在下一步读取sql文件时将以utf8的形式去解释该文件内容

步骤六在mysql命令行下执行语句 load data infile 'tbname.sql' into table newdbname.tbname;

注意：千万不要忘了第四步

采用第二种方法，所有数据均正常导入，且格式转换成功没有乱码

Tags - mysql , 编码 ]]> http://www.bizeway.net/read.php?545 <![CDATA[MySQL server has gone away解决办法]]> ArthurXF <arthurxf@gmail.com> Fri, 07 May 2010 21:04:27 +0000 http://www.bizeway.net/read.php?545 解决方案：
在my.cnf文件中添加或者修改以下两个变量：
wait_timeout=2880000
interactive_timeout = 2880000
关于两个变量的具体说明可以google或者看官方手册。如果不能修改my.cnf，则可以在连接数据库的时候设置CLIENT_INTERACTIVE，比如：
sql = "set interactive_timeout=24*3600";
mysql_real_query(...)

2、执行一个SQL，但SQL语句过大或者语句中含有BLOB或者longblob字段。比如，图片数据的处理
解决方案：
在my.cnf文件中添加或者修改以下变量：
max_allowed_packet = 10M(也可以设置自己需要的大小)
max_allowed_packet 参数的作用是，用来控制其通信缓冲区的最大长度。

Tags - mysql , gone , away ]]> http://www.bizeway.net/read.php?449 <![CDATA[MYSQL性能优化设置]]> ArthurXF <arthurxf@gmail.com> Wed, 24 Jun 2009 02:47:01 +0000 http://www.bizeway.net/read.php?449 My favorite question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server straight after installation, assuming it was installed with default settings.

在面试MySQL DBA或者那些打算做MySQL性能优化的人时，我最喜欢问题是：MySQL服务器按照默认设置安装完之后，应该做哪些方面的调节呢？

I’m surprised how many people fail to provide any reasonable answer to this question, and how many servers are where in wild which are running with default settings.

令我很惊讶的是，有多少人对这个问题无法给出合理的答案，又有多少服务器都运行在默认的设置下。

Even though you can tune quite a lot of variables in MySQL Servers only few of them are really important for most common workload. After you get these settings right other changes will most commonly offer only incremental performance improvements.

尽管你可以调节很多MySQL服务器上的变量，但是在大多数通常的工作负载下，只有少数几个才真正重要。如果你把这些变量设置正确了，那么修改其他变量最多只能对系统性能改善有一定提升。

key_buffer_size - Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload - remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time - it is not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use few MyISAM tables you’ll want to keep it lower but still at least 16-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.

key_buffer_size - 这对MyISAM表来说非常重要。如果只是使用MyISAM表，可以把它设置为可用内存的 30-40%。合理的值取决于索引大小、数据量以及负载 -- 记住，MyISAM表会使用操作系统的缓存来缓存数据，因此需要留出部分内存给它们，很多情况下数据比索引大多了。尽管如此，需要总是检查是否所有的 key_buffer 都被利用了 -- .MYI 文件只有 1GB，而 key_buffer 却设置为 4GB 的情况是非常少的。这么做太浪费了。如果你很少使用MyISAM表，那么也保留低于 16-32MB 的 key_buffer_size 以适应给予磁盘的临时表索引所需。

innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply - if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.

innodb_buffer_pool_size - 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以，然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来，无需留给操作系统太多的内存，因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 -- 如果你的数据量不大，并且不会暴增，那么无需把 innodb_buffer_pool_size 设置的太大了。

innodb_additional_pool_size This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.

innodb_additional_pool_size - 这个选项对性能影响并不太多，至少在有差不多足够内存可分配的操作系统上是这样。不过如果你仍然想设置为 20MB(或者更大)，因此就需要看一下Innodb其他需要分配的内存有多少。

innodb_log_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.

innodb_log_file_size 在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。我经常设置为 64-512MB，跟据服务器大小而异。

innodb_log_buffer_size Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory - it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.

innodb_log_buffer_size 默认的设置在中等强度写入负载以及较短事务的情况下，服务器性能还可以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存 -- 它每秒都会刷新一次，因此无需设置超过1秒所需的内存空间。通常 8-16MB 就足够了。越小的系统它的值越小。

innodb_flush_logs_at_trx_commit Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.

innodb_flush_logs_at_trx_commit 是否为Innodb比MyISAM慢1000倍而头大？看来也许你忘了修改这个参数了。默认值是 1，这意味着每次提交的更新事务（或者每个事务之外的语句）都会刷新到磁盘中，而这相当耗费资源，尤其是没有电池备用缓存时。很多应用程序，尤其是从 MyISAM转变过来的那些，把它的值设置为 2 就可以了，也就是不把日志刷新到磁盘上，而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去，因此通常不会丢失每秒1-2次更新的消耗。如果设置为 0 就快很多了，不过也相对不安全了 -- MySQL服务器崩溃时就会丢失一些事务。设置为 2 指挥丢失刷新到操作系统缓存的那部分事务。

table_cache - Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used.

table_cache -- 打开一个表的开销可能很大。例如MyISAM把MYI文件头标志该表正在使用中。你肯定不希望这种操作太频繁，所以通常要加大缓存数量，使得足以最大限度地缓存打开的表。它需要用到操作系统的资源以及内存，对当前的硬件配置来说当然不是什么问题了。如果你有200多个表的话，那么设置为 1024 也许比较合适（每个线程都需要打开表），如果连接数比较大那么就加大它的值。我曾经见过设置为 100,000 的情况。

thread_cache Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.

thread_cache -- 线程的创建和销毁的开销可能很大，因为每个线程的连接/断开都需要。我通常至少设置为 16。如果应用程序中有大量的跳跃并发连接并且 Threads_Created 的值也比较大，那么我就会加大它的值。它的目的是在通常的操作中无需创建新线程。

query_cache If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.

query_cache -- 如果你的应用程序有大量读，而且没有应用程序级别的缓存，那么这很有用。不要把它设置太大了，因为想要维护它也需要不少开销，这会导致MySQL变慢。通常设置为 32-512Mb。设置完之后最好是跟踪一段时间，查看是否运行良好。在一定的负载压力下，如果缓存命中率太低了，就启用它。

Note: as you can see all of these are global variables. These variables depend on hardware and mix of storage engines, while per session variables are typically workload specific. If you have simple queries there is no reason to increase sort_buffer_size even if you have 64GB of memory to waste. Furthermore doing so may decrease performance.
I normally leave per session variable tuning to second step after I can analyze workload.

注意：就像你看到的上面这些全局表量，它们都是依据硬件配置以及不同的存储引擎而不同，但是会话变量通常是根据不同的负载来设定的。如果你只有一些简单的查询，那么就无需增加 sort_buffer_size 的值了，尽管你有 64GB 的内存。搞不好也许会降低性能。
我通常在分析系统负载后才来设置会话变量。

P.S Note MySQL distribution contains bunch of sample my.cnf files which may be great templates to use. Typically they would already be much better than defaults if you chose correct one.

P.S，MySQL的发行版已经包含了各种 my.cnf 范例文件了，可以作为配置模板使用。通常这比你使用默认设置好的多了。

other:

back_log：
要求 MySQL 能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。
back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。
当你观察你的主机进程列表，发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大 back_log 的值了。默认数值是50，把它改为500。
Tags - mysql ]]> http://www.bizeway.net/read.php?438 <![CDATA[mysql-bin.000001文件是怎么产生的及处理方法]]> ArthurXF <arthurxf@gmail.com> Sat, 06 Jun 2009 16:45:06 +0000 http://www.bizeway.net/read.php?438
这样做主要有以下两个目的：
1：数据恢复
如果你的数据库出问题了，而你之前有过备份，那么可以看日志文件，找出是哪个命令导致你的数据库出问题了，想办法挽回损失。
2：主从服务器之间同步数据
主服务器上所有的操作都在记录日志中，从服务器可以根据该日志来进行，以确保两个同步。

处理方法分两种情况：
1：只有一个mysql服务器，那么可以简单的注释掉这个选项就行了。
vi /etc/my.cnf把里面的log-bin这一行注释掉，重启mysql服务即可。
2：如果你的环境是主从服务器，那么就需要做以下操作了。
A：在每个从属服务器上，使用SHOW SLAVE STATUS来检查它正在读取哪个日志。
B：使用SHOW MASTER LOGS获得主服务器上的一系列日志。
C：在所有的从属服务器中判定最早的日志，这个是目标日志，如果所有的从属服务器是更新的，就是清单上的最后一个日志。
D：清理所有的日志，但是不包括目标日志，因为从服务器还要跟它同步。
清理日志方法为：
登录到msyql的命令行状态下再执行下面的命令
清除指定文件名的日志
PURGE MASTER LOGS TO 'mysql-bin.010';
清除指定日期前的日志
PURGE MASTER LOGS BEFORE '2008-12-19 21:00:00';

如果你确定从服务器已经同步过了，跟主服务器一样了，那么可以直接RESET MASTER将这些文件删除。
Tags - mysql ]]> http://www.bizeway.net/read.php?391 <![CDATA[跟ArthurXF学习解决General error: 2006 MySQL server has gone away错误]]> ArthurXF <arthurxf@gmail.com> Sat, 13 Dec 2008 05:57:04 +0000 http://www.bizeway.net/read.php?391 功夫之王》kungfuman.net游戏服务器故障换了一台服务器，结果启动不久，只要有用户一登陆，马上服务就中止了。根据报错信息得知是General error: 2006 MySQL server has gone away错误。
　　这个错误是主要是发生在query时，请求时间超时了，在phpmyadmin中执行下面的命令验证一下。

引用

show global variables like '%timeout%';
结果例子：
Variable_name Value
connect_timeout 5
delayed_insert_timeout 300
innodb_lock_wait_timeout 50
innodb_rollback_on_timeout OFF
interactive_timeout 120
net_read_timeout 30
net_write_timeout 60
slave_net_timeout 3600
table_lock_wait_timeout 50
wait_timeout 120

我们可以看出wait_timeout的时间很短，建议调高，大家可以去修改my.cnf文件，将文件中的
wait_timeout=28800
interactive_timeout=28800

然后重启mysql后，问题解决。
Tags - mysql , error ]]> http://www.bizeway.net/read.php?326 <![CDATA[跟着ArthurXF学习MYSQL——更新字段值中的部分数据]]> ArthurXF <arthurxf@gmail.com> Wed, 02 Apr 2008 09:40:52 +0000 http://www.bizeway.net/read.php?326 将字段A中的值为1的，全部替换成2.
SQL语句就是这么写：update table set a=2 where a=1;

那么如果我们要讲字段A值中的包含1的，替换成2呢？
例如：a=2211，现在要替换成2222,就是把1换成2
SQl语句这么写：update table set a=REPLACE(a,'1','2');

这个方法还是很有用的，希望对大家有些帮助。

引用

本文由ArthurXF倾情奉献，另ArthurXF受聘于上海非凡进修学院进行PHP等IT课程培训，有意学习者，可以免费试听，联系电话：021-51097877，QQ：29011218，MSN：onenight11@hotmail.com

Tags - mysql , 字段值中的部分数据 ]]> http://www.bizeway.net/read.php?325 <![CDATA[跟着ArthurXF学习MYSQL的字符集——字符集和校对规则]]> ArthurXF <arthurxf@gmail.com> Wed, 02 Apr 2008 04:58:21 +0000 http://www.bizeway.net/read.php?325 　　请大家先理解两个概念：

引用

1.什么叫做字符集
字符集就是一套符号和字符编码的集合。
2.什么叫做校对规则
校对规则就是在字符集内用于比较字符的一套规则。

引用

MYSQL5.1数据库可以做到
· 使用多种字符集来存储字符串
· 使用多种校对规则来比较字符串
· 在同一台服务器、同一个数据库或甚至在同一个表中使用不同字符集或校对规则来混合字符串
· 允许定义任何级别的字符集和校对规则

引用

MYSQL能定义下面4种级别的字符集和校对规则：
1.服务器级别的字符集和校对规则
设置方法：mysqld --default-character-set=latin1 --default-collation=latin1_swedish_ci

2.数据库级别的字符集和校对规则
设置方法：建立库时：CREATE DATABASE db_name DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
修改库时：ALTER DATABASE db_name DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

3.表级别的字符集和校对规则
设置方法：
建表：CREATE TABLE tbl_name (column_list) [DEFAULT CHARACTER SET charset_name [COLLATE collation_name]]
改表：ALTER TABLE tbl_name [DEFAULT CHARACTER SET charset_name] [COLLATE collation_name]

4.列级别的字符集和校对规则
设置方法：col_name {CHAR | VARCHAR | TEXT} (col_length) [CHARACTER SET charset_name [COLLATE collation_name]]

引用

连接字符集和校对规则
整个连接过程包括2个阶段，客户端发送请求，服务器端响应请求。其中涉及了3种连接过程中的字符集和校对规则：
1.当从客户端发送给服务器端的请求属于哪种字符集和校对规则
设置方法：SET character_set_client = x;
2.服务端收到请求后，应该将请求转换为哪种字符集和校对规则
设置方法：SET character_set_results = x;
3.服务端发送结果集或者错误信息给客户端用哪种字符集和校对规则
设置方法：SET character_set_connection = x;

综合设置方式：SET NAMES 'x'
这个语句等价于上面3条语句的集合，也就是3中情况都使用相同的字符集的时候，可以用这一句代替上面3句。

这样大家应该对字符集和校对规则有些了解了吧。
我来解释一下大家为什么会出现乱码的问题。其实也就是3个连接过程中的字符集和校对规则设定不匹配。
例如：数据库定义的字符集为GB2312，可是页面要求显示utf-8的，那么光配置SET NAMES 'utf8'是不能解决问题的。首先从页面将utf-8的数据传给程序，程序拿到以后就要使用utf-8的字符集将数据发送给服务器端。那么就要设置SET character_set_client = ‘utf8’;服务器端要使用GB2312的字符集存储数据或者读取数据，那么就要设置SET character_set_results = ‘GB2312’;之后服务器端再用utf8的字符集将数据返回到客户端，就要设置SET character_set_connection = ‘utf8’;这样就不会出现乱码了。
注：html页面上使用的编码为"utf-8",一般程序中使用的'utf8',这两个写法略有区别。

引用

Tags - mysql , 字符集 , 校对规则 ]]> http://www.bizeway.net/read.php?266 <![CDATA[MYSQL4升级MYSQL5的问题]]> ArthurXF <arthurxf@gmail.com> Tue, 04 Dec 2007 09:35:45 +0000 http://www.bizeway.net/read.php?266 　　这个命令默认使用root用户，但是有很多系统root帐号已经被删除了。就要使用参数了。具体的如下：

引用

mysql_fix_privilege_tables --user=aaa --verbose --password=123

　　我不知道别人的能不能成功，总之我运行上面的命令总是报错。

引用

This script updates all the mysql privilege tables to be usable by
the current version of MySQL

Got a failure from command:
cat /usr/local/share/mysql/mysql_fix_privilege_tables.sql | /usr/local/bin/mysql --no-defaults --force --user=root --host=localhost --database=mysql
Please check the above output and try again.

Running the script with the --verbose option may give you some information
of what went wrong.

If you get an 'Access denied' error, you should run this script again and
give the MySQL root user password as an argument with the --password= option

　　后来研究了一下手册，只要用下面的命令就可以轻松搞定。还让我浪费2小时。希望大家以后不会犯我同样的错误了。

引用

mysql_upgrade --user=aaa --password

　　ArthurXF倾情奉献，希望大家多多支持！

现在ArthurXF本人正在搞PHP等技术培训，如果想学习的人可以跟我联系。另外培训的招生简章在这个网址，想了解的可以去看看。
PHP培训招生简章
Tags - mysql5升级 ]]> http://www.bizeway.net/read.php?183 <![CDATA[MySQL 数据库优化SQL总结]]> ArthurXF <arthurxf@gmail.com> Fri, 24 Aug 2007 03:47:14 +0000 http://www.bizeway.net/read.php?183

为了速度，在确保查询已经最优的前提，就是以牺牲空间来获取速度，比如分表，建立索引

我碰到以下几种情况会导致查询速度慢
1、无索引，必要的索引可以很大地提高速度，但对字符型效果不大，查询涉及关键的类别，尽量用数字，然后建立索引
2、效率低的SQL语句，比如用了大量的 IN ，NOT IN （这个在MYSQL4.x好象没有：））
如果有几个条件，可以选产生结果最小的做为首次查询，因为MYSQL 4。X不支持子查询，可以考虑建立临时表。
3、数据库服务器内存太小，这个你应该知道该如何办：）
4、表太大，可以根据主要分类，把表分解，或者常用的字段合成一个表，不常用的分成一个表
比如全国类型的企业信息，可以按省分，可以按行业分，
按省分后，每个省再分3个表，地址表（名称、地址、邮政编码），企业信息表（行业，规模，。。。），通信表（电话、传真、邮件、主页）

Mysql中有查询高速缓存,使用高速缓存的语句格式:select sql_cache * from tableA where ...

EXPLAIN sql执行语句
mysql>

SELECT COUNT(*) FROM Headline WHERE ExpireTime >= 1112201600;

引用
+----------+

| COUNT(*) |

+----------+

| 3971 |

+----------+

1 row in set (1.04 sec)

mysql>

EXPLAIN SELECT COUNT(*) FROM Headline WHERE ExpireTime >= 1112201600 \G

引用
*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: Headline

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 302116

Extra: Using where

1 row in set (0.00 sec)
The NULL value in the key column of the EXPLAIN output tell us that MySQL won't be using an index for this query. In fact, the NULL value in the possible_keys column tells us that there were no indexes to pick from at all. If this type of query is likely to be common, we can simply add an index and rerun the query (or the EXPLAIN) to verify that MySQL uses it.

1、选取最适用的字段属性

　　MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。

　　另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。

　　对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

2、使用连接（JOIN）来代替子查询(Sub-Queries)

　　MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：

DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

　　使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）.. 替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

　　如果使用连接（JOIN）.. 来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：

SELECT * FROM customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.
CustomerID
WHERE salesinfo.CustomerID IS NULL

连接（JOIN）.. 之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

　　3、使用联合(UNION)来代替手动创建的临时表

　　MySQL 从 4.0 的版本开始支持 UNION 查询，它可以把需要使用临时表的两条或更多的 SELECT 查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用 UNION 来创建查询的时候，我们只需要用 UNION作为关键字把多个 SELECT 语句连接起来就可以了，要注意的是所有 SELECT 语句中的字段数目要想同。下面的例子就演示了一个使用 UNION的查询。

SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product

　　4、事务

　　尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN 关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

BEGIN;
INSERT INTO salesinfo SET CustomerID=14;
UPDATE inventory SET Quantity=11
WHERE item='book';
COMMIT;

　　事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

5、锁定表

　　尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户
来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。

　　其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

LOCK TABLE inventory WRITE
SELECT Quantity FROM inventory
WHEREItem='book';
...
UPDATE inventory SET Quantity=11
WHEREItem='book';
UNLOCK TABLES

　　这里，我们用一个 SELECT 语句取出初始数据，通过一些计算，用 UPDATE 语句将新值更新到表中。包含有 WRITE 关键字的 LOCK TABLE 语句可以保证在 UNLOCK TABLES 命令被执行之前，不会有其它的访问来对 inventory 进行插入、更新或者删除的操作。

　　6、使用外键

　　锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo 表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。

CREATE TABLE customerinfo
(
CustomerID INT NOT NULL ,
PRIMARY KEY ( CustomerID )
) TYPE = INNODB;

CREATE TABLE salesinfo
(
SalesID INT NOT NULL,
CustomerID INT NOT NULL,
PRIMARY KEY(CustomerID, SalesID),
FOREIGN KEY (CustomerID) REFERENCES customerinfo
(CustomerID) ON DELETECASCADE
) TYPE = INNODB;

注意例子中的参数“ON DELETE CASCADE”。该参数保证当 customerinfo 表中的一条客户记录被删除的时候，salesinfo 表中所有与该客户相关的记录也会被自动删除。如果要在 MySQL 中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表 InnoDB类型。该类型不是 MySQL 表的默认类型。定义的方法是在 CREATE TABLE 语句中加上 TYPE=INNODB。如例中所示。

　　7、使用索引

　　索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(), MIN()和ORDERBY这些命令的时候，性能提高更为明显。那该对哪些字段建立索引呢？一般说来，索引应建立在那些将用于JOIN, WHERE判断和ORDER BY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况，例如customerinfo中的“province”.. 字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTER TABLE或CREATE INDEX在以后创建索引。此外，MySQL
从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL 中是一个FULLTEXT类型索引，但仅能用于MyISAM 类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTER TABLE或CREATE INDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。

　　8、优化的查询语句

　　绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。下面是应该注意的几个方面。首先，最好是在相同类型的字段间进行比较的操作。在MySQL 3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。其次，在建有索引的字段上尽量不要使用函数进行操作。

　　例如，在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。

SELECT * FROM order WHERE YEAR(OrderDate)<2001;
SELECT * FROM order WHERE OrderDate<"2001-01-01";

　　同样的情形也会发生在对数值型字段进行计算的时候：

SELECT * FROM inventory WHERE Amount/7<24;
SELECT * FROM inventory WHERE Amount<24*7;

　　上面的两个查询也是返回相同的结果，但后面的查询将比前面的一个快很多。第三，在搜索字符型字段时，我们有时会使用 LIKE 关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。例如下面的查询将会比较表中的每一条记录。

SELECT * FROM books
WHERE name like "MySQL%"

　　但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：..

SELECT * FROM books
WHERE name>="MySQL"and name<"MySQM"

　　最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。

假设我们创建了一个名为people的表：

CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL );

然后，我们完全随机把1000个不同name值插入到people表。下图显示了people表所在数据文件的一小部分：

可以看到，在数据文件中name列没有任何明确的次序。如果我们创建了name列的索引，MySQL将在索引中排序name列：

对于索引中的每一项，MySQL在内部为它保存一个数据文件中实际记录所在位置的“指针”。因此，如果我们要查找name等于“Mike”记录的peopleid（SQL命令为“SELECT peopleid FROM people WHERE name='Mike';”），MySQL能够在name的索引中查找“Mike”值，然后直接转到数据文件中相应的行，准确地返回该行的peopleid（999）。在这个过程中，MySQL只需处理一个行就可以返回结果。如果没有“name”列的索引，MySQL要扫描数据文件中的所有记录，即1000个记录！显然，需要MySQL处理的记录数量越少，则它完成任务的速度就越快。

索引的类型

MySQL提供多种索引类型供选择：

普通索引

这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：

创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表);修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

唯一性索引

这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建：

创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );

主键

主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。

全文索引

MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再涉及全文索引，要了解更多信息，请参见MySQL documentation。

单列索引与多列索引

索引可以是单列索引，也可以是多列索引。下面我们通过具体的例子来说明这两种索引的区别。假设有这样一个people表：

CREATE TABLE people ( peopleid SMALLINT NOT NULL AUTO_INCREMENT, firstname CHAR(50) NOT NULL, lastname CHAR(50) NOT NULL, age SMALLINT NOT NULL, townid SMALLINT NOT NULL, PRIMARY KEY (peopleid) );

下面是我们插入到这个people表的数据：

这个数据片段中有四个名字为“Mikes”的人（其中两个姓Sullivans，两个姓McConnells），有两个年龄为17岁的人，还有一个名字与众不同的Joe Smith。

这个表的主要用途是根据指定的用户姓、名以及年龄返回相应的peopleid。例如，我们可能需要查找姓名为Mike Sullivan、年龄17岁用户的peopleid（SQL命令为SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age=17;）。由于我们不想让MySQL每次执行查询就去扫描整个表，这里需要考虑运用索引。

首先，我们可以考虑在单个列上创建索引，比如firstname、lastname或者age列。如果我们创建firstname列的索引（ALTER TABLE people ADD INDEX firstname (firstname);），MySQL将通过这个索引迅速把搜索范围限制到那些firstname='Mike'的记录，然后再在这个“中间结果集”上进行其他条件的搜索：它首先排除那些lastname不等于“Sullivan”的记录，然后排除那些age不等于17的记录。当记录满足所有搜索条件之后，MySQL就返回最终的搜索结果。

由于建立了firstname列的索引，与执行表的完全扫描相比，MySQL的效率提高了很多，但我们要求MySQL扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除firstname列上的索引，再创建lastname或者age列的索引，但总地看来，不论在哪个列上创建索引搜索效率仍旧相似。

为了提高搜索效率，我们需要考虑运用多列索引。如果为firstname、lastname和age这三个列创建一个多列索引，MySQL只需一次检索就能够找出正确的结果！下面是创建这个多列索引的SQL命令：

ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);

由于索引文件以B-树格式保存，MySQL能够立即转到合适的firstname，然后再转到合适的lastname，最后转到合适的age。在没有扫描数据文件任何一个记录的情况下，MySQL就正确地找出了搜索的目标记录！

那么，如果在firstname、lastname、age这三个列上分别创建单列索引，效果是否和创建一个firstname、lastname、age的多列索引一样呢？答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。

Tags - mysql , sql优化 ]]>