MySQL不能启动 Can't start server : Bind on unix socket: Permission denied

mysql突然不能启动,查看最后的启动日志如下:

080825 09:38:04 mysqld started
080825 9:38:04 [ERROR] Can't start server : Bind on unix socket: Permission denied
080825 9:38:04 [ERROR] Do you already have another mysqld server running on socket: /var/mysql.sock ?
080825 9:38:04 [ERROR] Aborting

080825 9:38:04 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

080825 09:38:04 mysqld ended
 

my.cnf配置如下:
[mysqld]
datadir=/usr/local/mysql/data
socket=/var/mysql.sock

[mysql]
socket=/tmp/mysql.sock
[mysql.server]
user=mysql
basedir=/usr/local/mysql

[safe_mysqld]
err-log=/usr/local/mysql/mysqld.log

pid-file=/usr/local/mysql/mysqld.pid
 

根据日志文件显示,首先检查运行权限,再看一下/var/mysql.sock,发现/var/mysql.sock目录下并没有该文件,是否是没有权限 写/var目录?OK,那就用root用户运行一下safe_mysqld,启动正常。使用mysql连接数据库,提示错误,连接不到数据库服务器, /tmp目录下没有mysql.sock这个文件,到这里,基本清楚了Mysql客户端和服务器端在本机的通信方式了,通过一个名为mysql.sock 文件来进行初始化通信的,因此该文件所存放的目录必须双方都有权限可以访问到,而且服务器在启动时需要写该文件,如果my.cnf中这两项配置不正确,就 会出现Permission denied等类似错误。将配置文件修改如下,就没有问题了。

 

[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock

[mysql]
socket=/tmp/mysql.sock
[mysql.server]
user=mysql
basedir=/usr/local/mysql

[safe_mysqld]
err-log=/usr/local/mysql/mysqld.log
pid-file=/usr/local/mysql/mysqld.pid

Fedora 15 安装mysql数据库

用fedora 15自带的yum安装方式:
只要在终端里输入:yum -y install mysql-server ,系统自动下载和安装Mysql的,
chkconfig --add mysqld 在服务清单中添加mysql服务
service mysqld start 服务启动
mysqladmin -u root password 'newpassword' 更改密码
mysql -u root -p

 

Enter password:输入刚才更改的密码

 
 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

以上是登录成功信息

 
 

mysql> show databases; 查询数据库

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use test; 使用test数据库

mysql> show tables; 查询test数据库所有表
Empty set (0.00 sec)

 

要使数据库支持中文,还要修改一个文件:

vi /etc/my.cnf

增加一项:character_set_server=utf8

 

关于MySQL的Float和Decimal精确度比较

float,double容易产生误差,对精确度要求比较高时,建议使用decimal来存,decimal在mysql内存是以字符串存储的,用于定义货币要求精确度高的数据。在数据迁移中,float(M,D)是非标准定义,最好不要这样使用。M为精度,D为标度。

mysql> create table t1(c1 float(10,2), c3 decimal(10,2)); 
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1234567.23, 1234567.23);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------------+------------+
| c1         | c3         |
+------------+------------+
| 1234567.25 | 1234567.23 | 
+------------+------------+
1 row in set (0.02 sec)

mysql> insert into t1 values(9876543.21, 9876543.12);                       
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> select * from t1;                             
+------------+------------+
| c1         | c3         |
+------------+------------+
| 1234567.25 | 1234567.23 | 
| 9876543.00 | 9876543.12 | 
+------------+------------+
2 rows in set (0.00 sec)

不定义fload, double的精度和标度时,存储按给出的数值存储,这于OS和当前的硬件有关。

decimal默认为decimal(10,0)

因为误差问题,在程序中,少用浮点数做=比较,可以做range比较。如果数值比较,最好使用decimal类型。

精度中,符号不算在内:

mysql> insert into t1 values(-98765430.21, -98765430.12);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;                                                              
+--------------+--------------+
| c1           | c3           |
+--------------+--------------+
|   1234567.25 |   1234567.23 | 
|   9876543.00 |   9876543.12 | 
| -98765432.00 | -98765430.12 | 
+--------------+--------------+
3 rows in set (0.00 sec)

float占4个字节,double占8个字节,decimail(M,D)占M+2个字节。

Out of resources when opening file './xxx.MYD' (Errcode: 24) 解决

前两天数据库出现了这个错误,在网上查找到了解决方法。在解决过程中的一些问 题记录下来。首先出现Out of resources when opening file './xxx.MYD' (Errcode: 24)错误是因为打开的文件数超过了my.cnf的--open-files-limit。 open-files-limit选项无法在mysql命令行直接修改,必须在my.cnf中设定,最大值是65536。

 

重新启动以后,发现

mysql> show variables like 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0.00 sec)

 

并没有改变。赶快查看服务器的打开文件数设定的值(用ulimit -n查看),结果发现果然是1024。在修改服务器设置后,也改成了65536,重启服务还是没有改变。需要重新登录服务器再重启数据库服务就OK了。原来这个值会取数据库和服务器设定的最小值。
 
 

mysql> show variables like 'open%';

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

| Variable_name    | Value |

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

| open_files_limit | 65536 |

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


1 row in set (0.00 sec)
 
 
 
 
 

使用ulimit -a 可以查看当前系统的所有限制值,使用ulimit -n 可以查看当前的最大打开文件数。

 

新装的linux默认只有1024,当作负载较大的服务器时,很容易遇到error: too many open files。因此,需要将其改大。

 

使用 ulimit -n 65535 可即时修改,但重启后就无效了。(注ulimit -SHn 65535 等效 ulimit -n 65535,-S指soft,-H指hard)

 

有如下三种修改方式:

 

1.在/etc/rc.local 中增加一行 ulimit -SHn 65535
2.在/etc/profile 中增加一行 ulimit -SHn 65535
3.在/etc/security/limits.conf最后增加如下两行记录
* soft nofile 65535
* hard nofile 65535

 

具体使用哪种,试试哪种有效吧,我在 CentOS中使用第1种方式无效果,使用第3种方式有效果,而在Debian中使用第2种有效果

 

MySQL Replication的实现原理

要想用好一个系统,理解其实现原理是非常重要的事情,只有理解了其实现原理,我们才能够扬长避短,合理的利用,才能够搭建出最适合我们自己应用环境 的系统,才能够在系统实施之后更好的维护他。MySQL Replication 可以说是在目前 MySQL 数据库的实际应用场景中最为常见的Scale Out 手段了,这里就针对其实现原理做一个简单的分析。
 

  一、 Replication 线程

   Mysql的 Replication 是一个异步的复制过程,从一个 Mysql instace(我们称之为 Master)复制到另一个 Mysql instance(我们称之 Slave)。在 Master 与 Slave 之间的实现整个复制过程主要由三个线程来完成,其中两个线程(Sql线程和IO线程)在 Slave 端,另外一个线程(IO线程)在 Master 端。

 

  要实现 MySQL 的 Replication ,首先必须打开 Master 端的Binary Log(mysql-bin.xxxxxx)功能,否则无法实现。因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全 顺序的执行日志中所记录的各种操作。打开 MySQL 的 Binary Log 可以通过在启动 MySQL Server 的过程中使用 “—log-bin” 参数选项,或者在 my.cnf 配置文件中的 mysqld 参数组([mysqld]标识后的参数部分)增加 “log-bin” 参数项。

 

  MySQL 复制的基本过程如下:

 

  1. Slave 上面的IO线程连接上 Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;

 

   2. Master 接收到来自 Slave 的 IO 线程的请求后,通过负责复制的 IO 线程根据请求信息读取指定日志指定位置之后的日志信息,返回给 Slave 端的 IO 线程。返回信息中除了日志所包含的信息之外,还包括本次返回的信息在 Master 端的 Binary Log 文件的名称以及在 Binary Log 中的位置;

 

  3. Slave 的 IO 线程接收到信息后,将接收到的日志内容依次写入到 Slave 端的Relay Log文件(mysql-relay-bin.xxxxxx)的最末端,并将读取到的Master端的bin-log的文件名和位置记录到master- info文件中,以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪个位置开始往后的日志内容,请发给我”

 

   4. Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后,会马上解析该 Log 文件中的内容成为在 Master 端真实执行时候的那些可执行的 Query 语句,并在自身执行这些 Query。这样,实际上就是在 Master 端和 Slave 端执行了同样的 Query,所以两端的数据是完全一样的。

 
 

  实际上,在老版本中,MySQL 的复制实现在 Slave 端并不是由 SQL 线程和 IO 线程这两个线程共同协作而完成的,而是由单独的一个线程来完成所有的工作。但是 MySQL 的工程师们很快发现,这样做存在很大的风险和性能问题,主要如下:

 

   首先,如果通过一个单一的线程来独立实现这个工作的话,就使复制 Master 端的,Binary Log日志,以及解析这些日志,然后再在自身执行的这个过程成为一个串行的过程,性能自然会受到较大的限制,这种架构下的 Replication 的延迟自然就比较长了。

 

  其次,Slave 端的这个复制线程从 Master 端获取 Binary Log 过来之后,需要接着解析这些内容,还原成 Master 端所执行的原始 Query,然后在自身执行。在这个过程中,Master端很可能又已经产生了大量的变化并生成了大量的 Binary Log 信息。如果在这个阶段 Master 端的存储系统出现了无法修复的故障,那么在这个阶段所产生的所有变更都将永远的丢失,无法再找回来。这种潜在风险在Slave 端压力比较大的时候尤其突出,因为如果 Slave 压力比较大,解析日志以及应用这些日志所花费的时间自然就会更长一些,可能丢失的数据也就会更多。

 

   所以,在后期的改造中,新版本的 MySQL 为了尽量减小这个风险,并提高复制的性能,将 Slave 端的复制改为两个线程来完成,也就是前面所提到的 SQL 线程和 IO 线程。最早提出这个改进方案的是Yahoo!的一位工程师“Jeremy Zawodny”。通过这样的改造,这样既在很大程度上解决了性能问题,缩短了异步的延时时间,同时也减少了潜在的数据丢失量。

 

  当然,即使是换成了现在这样两个线程来协作处理之后,同样也还是存在 Slave 数据延时以及数据丢失的可能性的,毕竟这个复制是异步的。只要数据的更改不是在一个事务中,这些问题都是存在的。

 

  如果要完全避免这些问题,就只能用 MySQL 的 Cluster 来解决了。不过 MySQL的 Cluster 知道笔者写这部分内容的时候,仍然还是一个内存数 据库的解决方案,也就是需要将所有数据包括索引全部都 Load 到内存中,这样就对内存的要求就非常大的大,对于一般的大众化应用来说可实施性并不是太大。当然,在之前与 MySQL 的 CTO David 交流的时候得知,MySQL 现在正在不断改进其 Cluster 的实现,其中非常大的一个改动就是允许数据不用全部 Load 到内存中,而仅仅只是索引全部 Load 到内存中,我想信在完成该项改造之后的 MySQL Cluster 将会更加受人欢迎,可实施性也会更大。

 
 
 
 

二、Replication 实现级别

  由于MySQL Replication 是基于 Binary Log 实现的,所以Replication 的实现级别实际上是由Binary Log 的存储格式所决定。Binary Log 中记录 Eent 的方式可以是基于一条语句(Statement Level),也可以是基于一条记录(Row level),这可以在 MySQL 的配置参数(—binlog-format)中设定这个格式。

 

  1. Row Level:Binary Log 中会记录成每一行数据被修改的形式,然后在 Slave 端再对相同的数据进行修改。

   优点:在 Row Level 模式下,Binary Log 中可以不记录执行的sql语句的上下文相关的信息,仅仅只需要记录那一条记录被修改了,修改成什么样了。所以 Row Level 的日志内容会非常清楚的记录下每一行数据修改的细节,非常容易理解。而且不会出现某些特定情况下的存储过程,或function,以及trigger的调 用和触发无法被正确复制的问题。

  缺点:Row Level下,所有的执行的语句当记录到 Binary Log 中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容,比如有这样一条update语句:UPDATE group_message SET group_id = 1 where group_id = 2,执行之后,日志中记录的不是这条update语句所对应的事件(MySQL以事件的形式来记录 Binary Log 日志),而是这条语句所更新的每一条记录的变化情况,这样就记录成很多条记录被更新的很多个事件。自然,Binary Log 日志的量就会很大。尤其是当执行ALTER TABLE 之类的语句的时候,产生的日志量是惊人的。因为MySQL对于 ALTER TABLE 之类的 DDL 变更语句的处理方式是重建整个表的所有数据,也就是说表中的每一条记录都需要变动,那么该表的每一条记录都会被记录到日志中。

 

  2. Statement Level:每一条会修改数据的 Query 都会记录到 Master的 Binary Log 中。Slave在复制的时候 SQL 线程会解析成和原来 Master 端执行过的相同的 Query 来再次执行。

   优点:Statement Level下的优点首先就是解决了Row Level下的缺点,不需要记录每一行数据的变化,减少 Binary Log 日志量,节约了 IO 成本,提高了性能。因为他只需要记录在Master上所执行的语句的细节,以及执行语句时候的上下文的信息。

  缺 点:由于他是记录的执行语句,所以,为了让这些语句在slave端也能正确执行,那么他还必须记录每条语句在执行的时候的一些相关信息,也就是上下文信 息,以保证所有语句在slave端杯执行的时候能够得到和在master端执行时候相同的结果。另外就是,由于Mysql现在发展比较快,很多的新功能不 断的加入,使mysql得复制遇到了不小的挑战,自然复制的时候涉及到越复杂的内容,bug也就越容易出现。在statement level下,目前已经发现的就有不少情况会造成mysql的复制出现问题,主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现,比 如:sleep()函数在有些版本中就不能真确复制,在存储过程中使用了last_insert_id()函数,可能会使slave和master上得到 不一致的id等等。由于row level是基于每一行来记录的变化,所以不会出现类似的问题。

 

  3. Mixed Level: 从 5.1.8 版本开始,MySQL 提供了除Statement Level和Row Level之外的第三种 Mixed Level,实际上就是前两种模式的结合。在Mixed模式下,MySQL会根据执行的每一条具体的 Query 语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种。除了MySQL认为通过STATEMENT方式可能造成复制过程中 Master与Slave之间产生不一致数据(如特殊Procedure和Function的使用,UUID()函数的使用等特殊情况)的时候MySQL 会选择ROW的模式来记录变更之外,都会使用STATEMENT模式来记录变更。当然,这里需要排除的特殊情况并不仅仅只有上面所描述的这几种,具体请参 考 MySQL 官方的详细手册。

  老版本的 MySQL 一直都只有基于 Statement 的复制模式,直到5.1.5版本的 MySQL 才开始支持Row Level的复制。从5.0开始,MySQL 的复制已经解决了大量老版本中出现的无法正确复制的问题。但是由于存储过程的出现,给 MySQL 的复制又带来了更大的新挑战。另外,看到官方文档说,从5.1.8版本开始,MySQL 开始提供 Mixed Level,新版本中的Statment level还是和以前一样,仅仅记录执行的语句。而新版本的Mysql中队Row Level模式也被做了优化,并不是所有的修改都会以Row Level来记录,像遇到表结构变更的时候就会以statement模式来记录,如果 Query 语句确实就是 UPDATE 或者 DELETE 等修改数据的语句,那么还是会记录所有行的变更。

MySQL主从同步、读写分离配置步骤、问题解决笔记

根据要求配置MySQL主从备份、读写分离,结合网上的文档,对搭建的步骤和出现的问题以及解决的过程做了如下笔记;

现在使用的两台服务器已经安装了MySQL,全是rpm包装的,能正常使用。
 
为了避免不必要的麻烦,主从服务器MySQL版本尽量保持一致;
 
环境
192.168.0.1 (Master)
192.168.0.2 (Slave)
 
MySQL Version:Ver 14.14 Distrib 5.1.48, for pc-linux-gnu (i686) using readline 5.1
 
 

1、登录Master服务器,修改my.cnf,添加如下内容;
server-id = 1   //数据库ID号, 为1时表示为Master,其中master_id必须为1到232–1之间的一个正整数值;
log-bin=mysql-bin   //启用二进制日志;
binlog-do-db=data //需要同步的二进制数据库名;
binlog-ignore-db=mysql   //不同步的二进制数据库名;这个同步后听说很麻烦,我没有同步;
log-bin=/var/log/mysql/updatelog //设定生成的log文件名;
log-slave-updates //把更新的记录写到二进制文件中;

slave-skip-errors //跳过错误,继续执行复制;
 
 

2、建立复制所要使用的用户;

mysql>grant replication slave on *.* to test@192.168.0.2 identified by '********'
 
 

3、重启mysql;
/usr/bin/mysqladmin -uroot shutdown;

/usr/bin/mysql_safe &
 
 

4、现在备份Master上的数据
锁定后我直接tar.gz data这个库文件;
mysql>FLUSH TABLES WITH READ LOCK;
cd /var/lib/mysql
tar data.tar.gz data
接着直接执行了远程scp;

 
 

5、登录Slave数据库服务器,修改my.cnf;
server-id= 3   //2已经被用在另一个服务器上了,如果以后要再加Slave号接着往后数就OK了;
log-bin=mysql-bin
master-host=   192.168.0.1
master-user =   test
master-password =   ******
master-port= 3306
master-connect-retry=60   //如果发现主服务器断线,重新连接的时间差;
replicate-ignore-db=mysql //不需要备份的数据库;
replicate-do-db=data //需要备份的数据库
log-slave-update
slave-skip-errors

6、解压刚才从Master scp过来的文件,此处不用改权限、属主,默认没有改变,可以根据实际情况进行修改;
 
 

7、上述完成后,可以启动slave了;查看slave状态;
mysql>slave start;
mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: updatelog.000001
Read_Master_Log_Pos: 106
Relay_Log_File: onlinevc-relay-bin.000013
Relay_Log_Pos: 1069
Relay_Master_Log_File: updatelog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: data
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 1681
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

ERROR:
No query specified

8、查看Master上面的状态;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| updatelog.000012 |    15016 | data         | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
由此可见两者的File、Position存在问题,所要要去Slave上设置对应主库的Master_Log_File、Read_Master_Log_Pos;执行如下语句;
mysql>slave stop;
mysql>CHANGE MASTER TO MASTER_HOST='192.168.0.1',MASTER_USER='test', MASTER_PASSWORD='******',MASTER_LOG_FILE='updatelog.000012',MASTER_LOG_POS=15016;
确保 Slave_IO_Running: Yes 、Slave_SQL_Running: Yes都要为YES才能证明Slave的I/O和SQL进行正常。

9、解锁主库表;
UNLOCK TABLES;
到此主从MySQL服务器配置完成,测试结果如下;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| updatelog.000012 |   717039 | data | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: updatelog.000012
Read_Master_Log_Pos: 717039
Relay_Log_File: onlinevc-relay-bin.000013
Relay_Log_Pos: 1222
Relay_Master_Log_File: updatelog.000012
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: data
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 717039
Relay_Log_Space: 1834
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

ERROR:
No query specified
####################################   如下是MySQL数据库读写分离操作步骤 ##########################################
此处使用MySQL自己(Mysql-proxy)的代理实现数据库的读写分离;
所需要安装包如下;
1、check-0.9.8
2、glib-2.18.4
3、libevent-2.0.6-rc
4、lua-5.1.4
      wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
5、pkg-config-0.23
6、mysql-5.0.56
7、mysql-proxy-0.8.0
      http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.0.tar.gz

别的安装包地址当时没有记下载地址,不过大部分都在这个网站上找的;http://sourceforge.net/
&&&&&&&&&& 安装开始 &&&&&&&&
1、tar -zxvf check-0.8.4.tar.gz
    cd check-0.8.4
    ./configure
    make
    make install
2、tar -zxvf glib-2.18.4.tar.gz   //系统rpm包可能版本低出现了问题3;
    ./configure
    make
    make install
3、tar -zxvf libevent-2.0.6-rc.tar.gz
    cd libevent-2.0.6-rc
    ./configure --prefix=/usr/local/libevent
     make && make install
4、tar -zxvf lua-5.1.4.tar.gz
    INSTALL_TOP= /usr/local/lua    //    为了把lua安装到/var/lib/lua下,故要修改其下的Makefile;
或者直接执行:sed -i 's#INSTALL_TOP= /usr/local#INSTALL_TOP= /usr/local/lua#' Makefile
root@testmysql [/software/lua-5.1.4]# make
Please do
   make PLATFORM
where PLATFORM is one of these:
   aix ansi bsd freebsd generic linux macosx mingw posix solaris
See INSTALL for complete instructions.
这处是要你选择服务器所使用的平台;
执行:make linux //此处执行后出现了错误,解决办法在下面问题解决区1处,此处先跳过;
再执行:make install
设置环境变量:
    export LUA_CFLAGS="-I/usr/local/lua/include" LUA_LIBS="-L/usr/local/lua/lib -llua -ldl" LDFLAGS="-L/usr/local/libevent/lib -lm"
    export CPPFLAGS="-I/usr/local/libevent/include"
    export CFLAGS="-I/usr/local/libevent/include"

5、tar -zxvf pkg-config-0.23.tar.gz
    cd pkg-config-0.23
    ./configure
    make
    make install
安装完之后要执行:cp etc/lua.pc /usr/local/lib/pkgconfig/lua5.1.pc      //原因见下面的问题解决区2处;
6、安装MySQL客户端;
因为此服务器系统是默认安装了MySQL,没有安装客户端,我又装了client、devel如下所示已安装的rpm包;
   root@testmysql [/software/lua-5.1.4]# rpm -qa | grep MySQL
MySQL-client-5.1.48-0.glibc23
MySQL-bench-5.0.91-0.glibc23
MySQL-test-5.1.48-0.glibc23
MySQL-shared-5.1.48-0.glibc23
MySQL-server-5.1.48-0.glibc23
MySQL-devel-5.1.48-0.glibc23
此后的Mysql-proxy时总是一直报错,编译不过去,无奈之下用源码包客户端;(此时的rpm包都没有卸载,直接执行了下面的安装)//此处问题见问题解决区4处;
    tar zxvf mysql-5.0.56.tar.gz     //此处我直接使用了mysql的5.0.56的源码包;
    cd mysql-5.0.56
    ./configure --prefix=/usr/local/mysql --without-server
    make && make install

7、tar xvf mysql-proxy-0.8.0.tar.gz
    cd mysql-proxy-0.8.0
    ./configure --prefix=/usr/local/mysql-proxy --with-mysql=/usr/local/mysql --with-lua    //问题解决处4有介绍;
    Make && Make install

8、在/var/lib/bin创建mysql-proxy.sh,内容如下;
     #!/bin/bash
     LUA_PATH="/usr/local/mysql-proxy/lib/mysql-proxy/lua/?.lua" /usr/local/mysql-proxy/bin/mysql-proxy --proxy-backend-addresses=192.168.0.1:3306 --proxy-read-only-backend-addresses=192.168.0.2:3306 --proxy-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/rw-splitting.lua >> /var/log/mysql-proxy.log &
     然后加上可执行权限;
     chmod a+x /var/lib/bin/mysql-proxy.sh
     执行:/var/lib/bin/mysql-proxy.sh 启动服务;

9、验证是否开户了:4040、4041;
    root@testmysql [/usr/local/bin]# netstat -an | grep 404*
    tcp        0      0 0.0.0.0:4040                0.0.0.0:*                   LISTEN     
    tcp        0      0 0.0.0.0:4041                0.0.0.0:*                   LISTEN  
10、测试读写分离,此步略过,所有配置已经完成。

&&&&&&&&&&&&&&&&&&&&&&&&&&&   问题解决区   &&&&&&&&&&&&&&&&&&&&&&&&&&&&
1、在安装的第四步执行make linux时报错如下:
root@testmysql [/software/lua-5.1.4]# make linux
cd src && make linux
make[1]: Entering directory /software/lua-5.1.4/src'
make all MYCFLAGS=-DLUA_USE_LINUX MYLIBS="-Wl,-E -ldl -lreadline -lhistory -lncurses"
make[2]: Entering directory
/software/lua-5.1.4/src'
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lapi.o lapi.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lcode.o lcode.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o ldebug.o ldebug.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o ldo.o ldo.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o ldump.o ldump.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lfunc.o lfunc.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lgc.o lgc.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o llex.o llex.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lmem.o lmem.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lobject.o lobject.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lopcodes.o lopcodes.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lparser.o lparser.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lstate.o lstate.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lstring.o lstring.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o ltable.o ltable.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o ltm.o ltm.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lundump.o lundump.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lvm.o lvm.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lzio.o lzio.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lauxlib.o lauxlib.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lbaselib.o lbaselib.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o ldblib.o ldblib.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o liolib.o liolib.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lmathlib.o lmathlib.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o loslib.o loslib.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o ltablib.o ltablib.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lstrlib.o lstrlib.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o loadlib.o loadlib.c
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o linit.o linit.c
ar rcu liblua.a lapi.o lcode.o ldebug.o ldo.o ldump.o lfunc.o lgc.o llex.o lmem.o lobject.o lopcodes.o lparser.o lstate.o lstring.o ltable.o ltm.o lundump.o lvm.o lzio.o lauxlib.o lbaselib.o ldblib.o liolib.o lmathlib.o loslib.o ltablib.o lstrlib.o loadlib.o linit.o
ranlib liblua.a
gcc -O2 -Wall -DLUA_USE_LINUX   -c -o lua.o lua.c
In file included from lua.h:16,
                 from lua.c:15:
luaconf.h:275:31: error: readline/readline.h: No such file or directory
luaconf.h:276:30: error: readline/history.h: No such file or directory
lua.c: In function 鈥榩ushline鈥?
lua.c:182: warning: implicit declaration of function 鈥榬eadline鈥?
                                                                    lua.c:182: warning: assignment makes pointer from integer without a cast
lua.c: In function 鈥榣oadline鈥?
lua.c:210: warning: implicit declaration of function 鈥榓dd_history鈥?
                                                                       make[2]: *** [lua.o] Error 1
make[2]: Leaving directory /software/lua-5.1.4/src'
make[1]: *** [linux] Error 2
make[1]: Leaving directory
/software/lua-5.1.4/src'
make: *** [linux] Error 2

解决方法:yum install libtermcap-devel
          yum install ncurses-devel                                 
          yum install libevent-devel                                   
          yum install readline-devel
  
2、安装MySQL-proxy时报错:
checking for LUA... configure: error: Package requirements (lua5.1 >= 5.1) were not met:

No package 'lua5.1' found

Consider adjusting the PKG_CONFIG_PATH environment variable if you
installed software in a non-standard prefix.

Alternatively, you may set the environment variables LUA_CFLAGS
and LUA_LIBS to avoid the need to call pkg-config.
See the pkg-config man page for more details.
解决办法:
cp etc/lua.pc /usr/local/lib/pkgconfig/lua5.1.pc

3、安装MySQL-proxy时报错:
checking for GLIB... configure: error: Package requirements (glib-2.0 >= 2.16.0) were not met:

No package 'glib-2.0' found

Consider adjusting the PKG_CONFIG_PATH environment variable if you
installed software in a non-standard prefix.

Alternatively, you may set the environment variables GLIB_CFLAGS
and GLIB_LIBS to avoid the need to call pkg-config.
See the pkg-config man page for more details.
解决办法:
   1、查看系统已经安装glib的rpm包;
    glibc-2.5-49.el5_5.4
    glibc-headers-2.5-49.el5_5.4
    glib2-2.12.3-4.el5_3.1
    glibc-common-2.5-49.el5_5.4
    glibc-devel-2.5-49.el5_5.4
   2、下载安装glib-2.18.4.tar.gz

4、刚开始使用系统里rpm所指定的mysq_config时,mysql-proxy安装报错,信息如下;
    configure: error: mysql_config not exists or not executable, use $ ./configure --with-mysql=/path/to/mysql_config
    看到帮助安装文档里要求如下:
    --with-mysql[=PATH]     Include MySQL support. PATH is the path to 'mysql_config'。
解决办法就是:安装包中的第6步。

解决ubuntu下mysql不能远程连接数据库的问题

Ubuntu10.04上自带的MySQL,执行了
root@ubuntu:~#sudo apt-get install mysql
安装完mysql-server

 

启动mysql
root@ubuntu:~#/etc/init.d/mysql start

 

本地可以连接进入数据库。
root@ubuntu:~#mysql -uroot -p

 

设置了远程访问权限:
mysql> grant all PRIVILEGES on *.* to admin@’%’ identified by ‘123456′;
Query OK, 0 rows affected (0.04 sec)

 

mysql> use information_schema
mysql> select * from user_privileges;
查询到有下面的结果:'admin’@'%',说明mysql已经授权远程连接。

 

在windows下访问Ubuntu的数据库,连接不上,但是Ubuntu上安装的apache可以访问。
用iptalbes添加端口3306后也无法访问。
root@ubuntu:~# iptables -A INPUT -p tcp –dport 3306 -j ACCEPT

 

Ubuntu上查看Mysql网络连接:
root@ubuntu:~# netstat -an |grep 3306
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
本地端口也在监听

 

root@ubuntu:~# ufw status
Firewall not loaded
本地防火墙未打开

 
 

后来在网上找到一个解决办法:
查看/etc/mysql/my.cnf找到bind-address才发现配置的是 127.0.0.1(bind-address=127.0.0.1),直接改为bind-address=192.168.0.xxx(本机ip),然 后再查看3306端口打开了,ok,可以正常连接了

MySQL Replace INTO的使用

今天DST里面有个插件作者问我关于Replace INTO和INSERT INTO的区别,我和他说晚上上我的blog看吧,那时候还在忙,现在从MYSQL手册里找了点东西,MYSQL手册里说REPLACE INTO说的还是比较详细的.

 

REPLACE的运行与INSERT很相像。只有一点除外,如果表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值,则在新记录被插入之前,旧记录被删除。请参见13.2.4节,“INSERT语法”。

 

注意,除非表有一个PRIMARY KEY或UNIQUE索引,否则,使用一个REPLACE语句没有意义。该语句会与INSERT相同,因为没有索引被用于确定是否新行复制了其它的行。

 

所有列的值均取自在REPLACE语句中被指定的值。所有缺失的列被设置为各自的默认值,这和INSERT一样。您不能从当前行中引用值,也不能在 新行中使用值。如果您使用一个例如“SET col_name = col_name + 1”的赋值,则对位于右侧的列名称的引用会被作为DEFAULT(col_name)处理。因此,该赋值相当于SET col_name = DEFAULT(col_name) + 1。

 

为了能够使用REPLACE,您必须同时拥有表的INSERT和DELETE权限。

 

REPLACE语句会返回一个数,来指示受影响的行的数目。该数是被删除和被插入的行数的和。如果对于一个单行REPLACE该数为1,则一行被插 入,同时没有行被删除。如果该数大于1,则在新行被插入前,有一个或多个旧行被删除。如果表包含多个唯一索引,并且新行复制了在不同的唯一索引中的不同旧 行的值,则有可能是一个单一行替换了多个旧行。

 

受影响的行数可以容易地确定是否REPLACE只添加了一行,或者是否REPLACE也替换了其它行:检查该数是否为1(添加)或更大(替换)。

 

如果您正在使用C API,则可以使用mysql_affected_rows()函数获得受影响的行数。

 

目前,您不能在一个子查询中,向一个表中更换,同时从同一个表中选择。

 

以下是所用算法的更详细的说明(该算法也用于LOAD DATA…REPLACE):

 

1. 尝试把新行插入到表中

 

2. 当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时:

 

a. 从表中删除含有重复关键字值的冲突行

 

b. 再次尝试把新行插入到表中

 


 

使用格式:

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},…),(…),…
或:

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, …
或:

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT …

MySQL数据库优化技术之配置篇、索引篇

(一)减少数据库访问

对于可以静态化的页面,尽可能静态化

对一个动态页面中可以静态的局部,采用静态化

部分数据可以生成XML,或者文本文件形式保存

使用数据缓存技术,例如: MemCached

(二)优化的检测方法

1.用户体验检测

2.Mysql状态检测

在Mysql命令行里面使用show status命令,得到当前mysql状态。

主要关注下列属性:

key_read_requests (索引读的请求数)(key_buffer_size设置影响)

key_reads(索引读响应数)

Key_blocks_used

Qcache_*

Open_tables(通过table_cache的设置影响)

Opened_tables

table_locks

3. 第三方工具检测

mysqlreporthttp://hackmysql.com/mysqlreport

mytophttp://jeremy.zawodny.com/mysql/mytop/
系统及Mysql的Log

系统命令: top, sar

Mysql的Log: slow_query.log

(三)硬件方面的优化

硬件方面,最容易成为Mysql瓶颈的部分是磁盘,其次是CPU和内存

磁盘方面

使用更快的磁盘,会对Mysql有很好的帮助

使用更多的硬盘,通过Raid,可以提高单块磁盘速度的问题

对于Raid方式,建议采用Raid 0+1 或者 Raid 1+0

CPU

毫无疑问,更高主频的CPU和更多的CPU数量可以给Mysql更

高的性能
内存

更高的内存,往往可以让Mysql中的更多的数据缓存在内存中,

但是,一个重要的因素是,需要有正确的Mysql的配置
网卡

使用千兆网卡及千兆网络


(四)操作系统方面的优化

1.不使用交换区。如果内存不足,增加更多的内存或配置你的系统使用较少内存
2. 不要使用NFS磁盘
3.增加系统和MySQL服务器的打开文件数量

使用ulimit –n 65535
4.增加系统的进程和线程数量。
5.关闭不必要的应用,优化硬盘参数,使用hdparm测试

(五)应用级的优化

1.使用多服务器负载均衡(多台读和写,用复制技术进行数据同步)
2.表的分区 (自定义分区,mysql5.1开始支持自带分区功能)
3.使用数据缓存技术memcached

(六)Mysql配置的优化

1.key_buffer(=512):索引缓冲使用的内存数量

这对MyISAM表来说非常重要,设定在可用内存的25%-30%较好,通过检查状态值 Key_read_requests和 Key_reads,

可以知道key_buffer设置是否合理。比例key_reads / key_read_requests应该尽可能的低,至少是1:100,1:1000更好 ,否则说明 key_buffer 设置有点偏小
2.innodb_buffer_pool_size(= 512):索引缓冲使用的内存数量

3.table_cache (=1024):数据表缓存区的尺寸

每当 MySQL 访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。

通过检查运行峰值时间的 Open_tables 和 Opened_tables 状态值,可以决定是否需要调整 table_cache 的值。

如果你发现 open_tables 的值等于 table_cache,并且发现 opened_tables 状态值在不断增长,那么你就需要增加 table_cache 参数值了,

也不能盲目地把 table_cache 参数设置成很大的值,如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。
4.sort_buffer_size (=256):指定排序用缓冲区的长度

该参数对应的分配内存是每连接独占!如果有100个连接,那么实际分配的总共排序缓冲区大小为100 × 6 = 600MB。

所以,对于内存在4GB左右的服务器推荐设置为6-8M
5.join_buffer_size :关联查询用缓冲区的长度

4G内存以上,建议大于32M,该参数对应的分配内存也是每连接独享!

6.max_connections (=1024):可以复用的线程数量

允许同时连接MySQL服务器的客户数量 ,可以观察和估计系统在峰值最大的并发连接数来设置
7.thread_cache(=*):可以复用的线程数量

一般设置为CPU数×2
8.innodb_buffer_pool_size(= 512):innodb表缓存池大小

这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以,

然而Innodb在默认的innodb_buffer_pool_size 设置下却跟蜗牛似的。

由于Innodb把数据和索引都缓存起来,无需留给操作系统太多的内存,因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。

一些应用于 key_buffer 的规则有 — 如果你的数据量不大,并且不会暴增,那么无需把innodb_buffer_pool_size 设置的太大了.

9.innodb_flush_logs_at_trx_commit(=1) :事务提交后的日志刷新模式

是否为Innodb比MyISAM慢1000倍而头大?看来也许你忘了修改这个参数了。默认值是 1,这意味着每次提交的更新事务(或者每个事务之外的语句)都会刷新到磁盘中,

而这相当耗费资源,尤其是没有电池备用缓存时。很多应用程序,尤其是从 MyISAM转变过来的那些,把它的值设置为 2 就可以了,也就是不把日志刷新到磁盘上,

而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去,因此通常不会丢失每秒1-2次更新的消耗。如果设置为0就快很多了,不过也相对不安全了,

MySQL服务器崩溃时就会丢失一些事务。设置为2指挥丢失刷新到操作系统缓存的那部分事务.

(七)表的优化

1. 选择合适的数据引擎

MyISAM:适用于大量的读操作的表

InnoDB:适用于大量的写读作的表

2.选择合适的列类型

使用 SELECT * FROM TB_TEST PROCEDURE ANALYSE()可以对这个表的每一个字段进行分析,给出优化列类型建议

3.对于不保存NULL值的列使用NOT NULL,这对你想索引的列尤其重要

4.建立合适的索引

5.使用定长字段,速度比变长要快

(八)建立索引原则

1.合理使用索引

一个Table在一次query中只能使用一个索引,使用EXPLAIN语句来检验优化程序的操作情况

使用analyze帮助优化程序对索引的使用效果做出更准确的预测

2.索引应该创建在搜索、排序、归组等操作所涉及的数据列上

3.尽量将索引建立在重复数据少的数据列中,唯一所以最好

例如:生日列,可以建立索引,但性别列不要建立索引

4.尽量对比较短的值进行索引

降低磁盘IO操作,索引缓冲区中可以容纳更多的键值,提高命中率

如果对一个长的字符串建立索引,可以指定一个前缀长度

5.合理使用多列索引

如果多个条件经常需要组合起来查询,则要使用多列索引(因为一个表一次查询只能使用一个索引,建立多个单列索引也只能使用一个)

6.充分利用最左前缀

也就是要合理安排多列索引中各列的顺序,将最常用的排在前面

7.不要建立过多的索引

只有经常应用于where,order by,group by中的字段需要建立索引.

8.利用慢查询日志查找出慢查询(log-slow-queries, long_query_time)

(九)充分利用索引

1.尽量比较数据类型相同的数据列

2.尽可能地让索引列在比较表达式中独立, WHERE mycol < 4 / 2 使用索引,而WHERE mycol * 2 < 4不使用

3.尽可能不对查询字段加函数,

如WHERE YEAR(date_col) < 1990改造成WHERE date_col < ’1990-01-01’

WHERE TO_DAYS(date_col) – TO_DAYS(CURDATE()) < cutoff 改造成WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)

4.在LIKE模式的开头不要使用通配符

5.使用straight join可以强制优化器按照FROM子句的次序来进行联结,可以select straight join,强制所有联结,也可以select * from a straight join b强制两个表的顺序.

6.使用force index强制使用指定的索引.如 select * from song_lib force index(song_name) order by song_name比不用force index效率高

7.尽量避免使用MySQL自动类型转换,否则将不能使用索引.如将int型的num_col用where num_col=‘5’

(十)SQL语句的优化

1.创建合适的统计中间结果表,降低从大表查询数据的几率

2.尽量避免使用子查询,而改用连接的方式.例如:

SELECT a.id, (SELECT MAX(created) FROM posts WHERE author_id = a.id) AS latest_post

FROM authors a

可以改成:

SELECT a.id, MAX(p.created) AS latest_post

FROM authors AS a

INNER JOIN posts p ON (a.id = p.author_id)

GROUP BY a.id

select song_id from song_lib where singer_id in

(select singer_id from singer_lib

where first_char=’A’

) limit 2000改成:

select song_id from song_lib a

inner join singer_lib b on a.singer_id=b.singer_id and first_char=’A’ limit 2000

3.插入判断重复键时,使用ON DUPLICATE KEY UPDATE :

insert into db_action.action_today(user_id,song_id,action_count) values(1,1,1) ON DUPLICATE KEY UPDATE action_count=action_count+1;

4.避免使用游标

游标的运行效率极低,可以通过增加临时表,运用多表查询,多表更新等方式完成任务,不要使用游标.

(十一)使用Explain分析SQL语句使用索引的情况

当 你在一条SELECT语句前放上关键词EXPLAIN,MySQL解释它将如何处理SELECT,提供有关表如何联结和以什么次序联结的信息,借助于 EXPLAIN,可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT,你也能知道优化器是否以一个最佳次序联结表。为了 强制优化器对一个SELECT语句使用一个特定联结次序,增加一个STRAIGHT_JOIN子句。 。

EXPLAIN命令的一般语法是:EXPLAIN <SQL命令> 如:explain select * from a inner join b on a.id=b.id

EXPLAIN的分析结果参数详解:

1.table:这是表的名字。

2.type:连接操作的类型。

system:表中仅有一条记录(实际应用很少只有一条资料的表)

const:表最多有一个匹配行,用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时,

如:select * from song_lib where song_id=2(song_id为表的primary key)

eq_ref:对于每个来自于前面的表的行组合,从该表中用UNIQUE或PRIMARY KEY的索引读取一行,

如:select * from song_lib a inner join singer_lib b on a.singer_id=b.singer_id(b的type值为eq_ref)

ref:对于每个来自于前面的表的行组合,从该表中用非UNIQUE或PRIMARY KEY的索引读取一行

如:select * from song_lib a inner join singer_lib b on a.singer_name=b.singer_name和

select * from singer_lib b where singer_name=‘ccc’ (b的type值为ref,因为b.singer_name是普通索引)

ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行,

如:select * from singer_lib where singer_name=‘ccc’ or singer_name is null

index_merge:该联接类型表示使用了索引合并优化方法

Key: 它显示了MySQL实际使用的索引的名字。如果它为空(或NULL),则MySQL不使用索引。

key_len: 索引中被使用部分的长度,以字节计。

3.ref:ref列显示使用哪个列或常数与key一起从表中选择行

4.rows: MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然,这里最理想的数字就是1。

5.Extra:这里可能出现许多不同的选项,其中大多数将对查询产生负面影响。一般有:

using where:表示使用了where条件

using filesort: 表示使用了文件排序,也就是使用了order by子句,并且没有用到order by 里字段的索引,从而需要

额外的排序开销,所以如果出现using filesort就表示排序的效率很低,需要进行优化,比如采用强制索引

的方法(force index)

===============================================

mysql 优化 (show variables, show status)。

安装好mysql后,配制文件应该在 /usr/local/mysql/share/mysql目录中,配制文件有几个,有my- huge.cnf my-medium.cnf my-large.cnf my-small.cnf,不同的流量的网站和不同配制的服务器环境,当然需要有不同的配制文件了。

一般的情 况下,my-medium.cnf这个配制文件就能满足我们的大多需要;一般我们会把配置文件拷贝到/etc/my.cnf 只需要修改这个配置文件就可以了,使用mysqladmin variables extended-status –u root –p 可以看到目前的参数,有3个配置参数是最重要的,即key_buffer_size,query_cache_size,table_cache。

key_buffer_size只对MyISAM表起作用,

key_buffer_size 指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。一般我们设为16M,实际上稍微大一点的站点 这个数字是远远不够的,通过检查状态值 Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。 或者如果你装了phpmyadmin 可以通过服务器运行状态看到,笔者推荐用phpmyadmin管理mysql,以下的状态值都是本人通过phpmyadmin获得的实例分析:

这个服务器已经运行了20天

key_buffer_size – 128M
key_read_requests – 650759289
key_reads – 79112

比例接近1:8000 健康状况非常好

另外一个估计key_buffer_size的办法 把你网站数据库的每个表的索引所占空间大小加起来看看以此服务器为例:比较大的几个表索引加起来大概125M 这个数字会随着表变大而变大。

从4.0.1开始,MySQL提供了查询缓冲机制。使用查询缓冲,MySQL将SELECT语句和查询结果存放在缓冲区中,今后对于同样的SELECT语句(区分大小写),将直接从缓冲区中读取结果。根据MySQL用户手册,使用查询缓冲最多可以达到238%的效率。

通过调节以下几个参数可以知道query_cache_size设置得是否合理

Qcache inserts
Qcache hits
Qcache lowmem prunes
Qcache free blocks
Qcache total blocks

Qcache_lowmem_prunes 的值非常大,则表明经常出现缓冲不够的情况,同时Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小 Qcache_hits的值不大,则表明你的查询重复率很低,这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲。此外,在SELECT语 句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。

Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多query_cache_type指定是否使用查询缓冲

我设置:

query_cache_size = 32M
query_cache_type= 1

得到如下状态值:

Qcache queries in cache 12737 表明目前缓存的条数
Qcache inserts 20649006
Qcache hits 79060095  看来重复查询率还挺高的
Qcache lowmem prunes 617913 有这么多次出现缓存过低的情况
Qcache not cached 189896
Qcache free memory 18573912  目前剩余缓存空间
Qcache free blocks 5328 这个数字似乎有点大 碎片不少
Qcache total blocks 30953

如果内存允许32M应该要往上加点

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。

笔者设置table_cache = 256

得到以下状态:

Open tables 256
Opened tables 9046

虽 然open_tables已经等于table_cache,但是相对于服务器运行时间来说,已经运行了20天,opened_tables的值也非常低。 因此,增加table_cache的值应该用处不大。如果运行了6个小时就出现上述值 那就要考虑增大table_cache。

如果你不 需要记录2进制log 就把这个功能关掉,注意关掉以后就不能恢复出问题前的数据了,需要您手动备份,二进制日志包含所有更新数据的语句,其目的是在恢复数据库时用它来把数据尽 可能恢复到最后的状态。另外,如果做同步复制( Replication )的话,也需要使用二进制日志传送修改情况。

log_bin指 定日志文件,如果不提供文件名,MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字引,每次启动服务时,都会重新生成一个新的二进制 文件。此外,使用log-bin-index可以指定索引文件;使用binlog-do-db可以指定记录的数据库;使用binlog-ignore- db可以指定不记录的数据库。注意的是:binlog-do-db和binlog-ignore-db一次只指定一个数据库,指定多个数据库需要多个语 句。而且,MySQL会将所有的数据库名称改成小写,在指定数据库时必须全部使用小写名字,否则不会起作用。

关掉这个功能只需要在他前面加上#号

#log-bin

开启慢查询日志( slow query log ) 慢查询日志对于跟踪有问题的查询非常有用。它记录所有查过long_query_time的查询,如果需要,还可以记录不使用索引的记录。下面是一个慢查询日志的例子:

开启慢查询日志,需要设置参数log_slow_queries、long_query_times、log-queries-not-using-indexes。

log_slow_queries 指定日志文件,如果不提供文件名,MySQL将自己产生缺省文件名。long_query_times指定慢查询的阈值,缺省是10秒。log- queries-not-using-indexes是4.1.0以后引入的参数,它指示记录不使用索引的查询。笔者设置 long_query_time=10

笔者设置:

sort_buffer_size = 1M
max_connections=120
wait_timeout =120
back_log=100
read_buffer_size = 1M
thread_cache=32
interactive_timeout=120
thread_concurrency = 4

参数说明:

back_log

要 求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短) 检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时 间内有很多连接,你需要增加它,换句话说,这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 Unix listen(2)系统调用的手册页应该有更多的细节。检查你的OS文档找出这个变量的最大值。试图设定back_log高于你的操作系统的限制将是无效 的。

max_connections

并发连接数目最大,120 超过这个值就会自动恢复,出了问题能自动解决

thread_cache

没找到具体说明,不过设置为32后 20天才创建了400多个线程 而以前一天就创建了上千个线程 所以还是有用的

thread_concurrency

#设置为你的cpu数目x2,例如,只有一个cpu,那么thread_concurrency=2
#有2个cpu,那么thread_concurrency=4
skip-innodb
#去掉innodb支持

代码:
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
#socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
#socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
net_buffer_length = 16K
myisam_sort_buffer_size = 1M
max_connections=120
#addnew config
wait_timeout =120
back_log=100
read_buffer_size = 1M
thread_cache=32
skip-innodb
skip-bdb
skip-name-resolve
join_buffer_size=512k
query_cache_size = 32M
interactive_timeout=120
long_query_time=10
log_slow_queries= /usr/local/mysql4/logs/slow_query.log
query_cache_type= 1
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 4
[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

补充

优 化table_cachetable_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。

案例1:该案例来自一个不是特别繁忙的服务器 table_cache – 512open_tables – 103 opened_tables – 1273 uptime – 4021421 (measured in seconds)该案例中table_cache似乎设置得太高了。在峰值时间,打开表的数目比table_cache要少得多。

案例 2:该案例来自一台开发服务器。table_cache – 64open_tables – 64opened-tables – 431uptime – 1662790 (measured in seconds)虽然open_tables已经等于table_cache,但是相对于服务器运行时间来说,opened_tables的值也非常低。 因此,增加table_cache的值应该用处不大。
案例3:该案例来自一个upderperforming的服务器table_cache – 64 open_tables – 64 opened_tables – 22423uptime – 19538该案例中table_cache设置得太低了。虽然运行时间不到6小时,open_tables达到了最大值,opened_tables的值 也非常高。这样就需要增加table_cache的值。优化key_buffer_sizekey_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得知详情。对于1G内存的机器,如果不使用 MyISAM表,推荐值是16M(8-64M)。

案例1:健康状况key_buffer_size – 402649088 (384M) key_read_requests – 597579931 key_reads – 56188案例2:警报状态key_buffer_size – 16777216 (16M)key_read_requests – 597579931key_reads – 53832731案例1中比例低于1:10000,是健康的情况;案例2中比例达到1:11,警报已经拉响。

================================

Mysql调优中两个重要参数table_cache和key_buffer

本文根据我自己的一点经验,讨论了Mysql服务器优化中两个非常重要的参数,分别是table_cache,key_buffer_size。

table_cache 指示表高速缓存的大小。当Mysql访问一个表时,如果在Mysql表缓冲区中还有空间,那么这个表就被打开并放入表缓冲区,这样做的好处是可以更快速地 访问表中的内容。一般来说,可以通过查看数据库运行峰值时间的状态值Open_tables和Opened_tables,用以判断是否需要增加 table_cache的值,即如果open_tables接近table_cache的时候,并且Opened_tables这个值在逐步增加,那就要 考虑增加这个值的大小了。

在mysql默认安装情况下,table_cache的值在2G内存以下的机器中的值默认时256到 512,如果机器有4G内存,则默认这个值是2048,但这决意味着机器内存越大,这个值应该越大,因为table_cache加大后,使得mysql对 SQL响应的速度更快了,不可避免的会产生更多的死锁(dead lock),这样反而使得数据库整个一套操作慢了下来,严重影响性能。所以平时维护中还是要根据库的实际情况去作出判断,找到最适合你维护的库的 table_cache值,有人说:“性能优化是一门艺术”,这话一点没错。大凡艺术品,大都是经过千锤百炼,精雕细琢而成。

这里还要说明一个问题,就是table_cache加大后碰到文件描述符不够用的问题,在mysql的配置文件中有这么一段提示

引用
“The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires.
Therefore you have to make sure to set the amount of open files allowed to at least 4096 in the variable “open-files-limit” in” section [mysqld_safe]”
说 的就是要注意这个问题,一想到这里,部分兄弟可能会用ulimit -n 作出调整,但是这个调整实际是不对的,换个终端后,这个值又会回到原始值,所以最好用sysctl或者修改/etc/sysctl.conf文件,同时还 要在配置文件中把open_files_limit这个参数增大,对于4G内存服务器,相信现在购买的服务器都差不多用4G的了,那这个这个 open_files_limit至少要增大到4096,如果没有什么特殊情况,设置成8192就可以了。

下面说说key_buffer_size这个参数,key_buffer_sizeO表示索引缓冲区的大小,严格说是它决定了数据库索引处理的速 度,尤 其是索引读的速度。根据网络一些高手写的文章表示可以检查状态值Key_read_requests和Key_reads,即可知道 key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低,至少是1:100,1:1000更好,虽然我还没有找到理论的依据,但是,我在自己维护的几台实际运 行良好的库做过的测试后表明,这个比值接近1:20000,这从结果证明了他们说这话的正确性,我们不妨用之。

后记:
我前面说过,性能优化是一件细活,影响mysql性能的因素很多,本文中只是选取了其中我认为比较重要的两个参数,期待和网友一起探讨更多mysql性能优化的技术。

MySQL的表分区

相关链接:
 
MySQL手册:http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html
 
MySQL分表与分区的区别:http://www.isoji.org/articles/mysql%E5%88%86%E8%A1%A8%E5%88%86%E5%8C%BA%E7%9A%84%E5%8C%BA%E5%88%AB%E5%92%8C%E8%81%94%E7%B3%BB-71588-1.html
 
 

一、什么是表分区
通俗地讲表分区是将一大表,根据条件分割成若干个小表。mysql5.1开始支持数据表分区了。
如:某用户表的记录超过了600万条,那么就可以根据入库日期将表分区,也可以根据所在地将表分区。当然也可根据其他的条件分区。

 

二、为什么要对表进行分区
为了改善大型表以及具有各种访问模式的表的可伸缩性,可管理性和提高数据库效率。

分区的一些优点包括:
      1)、与单个磁盘或文件系统分区相比,可以存储更多的数据
      2)、 对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。相反地,在某些情况下,添加新数据的过程又可以通过为那 些新数据专门增加一个新的分区,来很方便地实现。通常和分区有关的其他优点包括下面列出的这些。MySQL分区中的这些功能目前还没有实现,但是在我们的 优先级列表中,具有高的优先级;我们希望在5.1的生产版本中,能包括这些功能。
      3)、一些查询可以得到极大的优化,这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内,这样在查找时就不用查找其他剩余的分 区。因为分区可以在创建了分区表后进行修改,所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。
      4)、涉及到例如SUM()和COUNT()这样聚合函数的查询,可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id;”。通过“并行”,这意味着该查询可以在每个分区上同时进行,最终结果只需通过总计所有分区得到的结果。
      5)、通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。


三、分区类型

 

· RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。
· LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
· HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
· KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

  • RANGE分区

       基于属于一个给定连续区间的列值,把多行分配给分区。

       这些区间要连续且不能相互重叠,使用VALUES LESS THAN操作符来进行定义。以下是实例。

 

Sql代码
  1. CREATE TABLE employees (  
  2.     id INT NOT NULL,  
  3.     fname VARCHAR(30),  
  4.     lname VARCHAR(30),  
  5.     hired DATE NOT NULL DEFAULT '1970-01-01',  
  6.     separated DATE NOT NULL DEFAULT '9999-12-31',  
  7.     job_code INT NOT NULL,  
  8.     store_id INT NOT NULL  
  9. )  
  10.   
  11. partition BY RANGE (store_id) (  
  12.     partition p0 VALUES LESS THAN (6),  
  13.     partition p1 VALUES LESS THAN (11),  
  14.     partition p2 VALUES LESS THAN (16),  
  15.     partition p3 VALUES LESS THAN (21)  
  16. );   

       按照这种分区方案,在商店1到5工作的雇员相对应的所有行被保存在分区P0中,商店6到10的雇员保存在P1中,依次类推。注意,每个分区都是按顺序进行定义,从最低到最高。这是PARTITION BY RANGE 语法的要求;在这点上,它类似于C或Java中的“switch ... case”语句。
       对于包含数据(72, 'Michael', 'Widenius', '1998-06-25', NULL, 13)的一个新行,可以很容易地确定它将插入到p2分区中,但是如果增加了一个编号为第21的商店,将会发生什么呢?在这种方案下,由于没有规则把 store_id大于20的商店包含在内,服务器将不知道把该行保存在何处,将会导致错误。 要避免这种错误,可以通过在CREATE TABLE语句中使用一个“catchall” VALUES LESS THAN子句,该子句提供给所有大于明确指定的最高值的值:

Sql代码
  1. CREATE TABLE employees (  
  2.     id INT NOT NULL,  
  3.     fname VARCHAR(30),  
  4.     lname VARCHAR(30),  
  5.     hired DATE NOT NULL DEFAULT '1970-01-01',  
  6.     separated DATE NOT NULL DEFAULT '9999-12-31',  
  7.     job_code INT NOT NULL,  
  8.     store_id INT NOT NULL  
  9. )  
  10.   
  11. PARTITION BY RANGE (store_id) (  
  12.     PARTITION p0 VALUES LESS THAN (6),  
  13.     PARTITION p1 VALUES LESS THAN (11),  
  14.     PARTITION p2 VALUES LESS THAN (16),  
  15.     PARTITION p3 VALUES LESS THAN MAXVALUE  
  16. );  

    MAXVALUE 表示最大的可能的整数值。现在,store_id 列值大于或等于16(定义了的最高值)的所有行都将保存在分区p3中。在将来的某个时候,当商店数已经增长到25, 30, 或更多 ,可以使用ALTER TABLE语句为商店21-25, 26-30,等等增加新的分区。
     在几乎一样的结构中,你还可以基于雇员的工作代码来分割表,也就是说,基于job_code 列值的连续区间。例如——假定2位数字的工作代码用来表示普通(店内的)工人,三个数字代码表示办公室和支持人员,四个数字代码表示管理层,你可以使用下 面的语句创建该分区表:

Sql代码
  1. CREATE TABLE employees (  
  2.     id INT NOT NULL,  
  3.     fname VARCHAR(30),  
  4.     lname VARCHAR(30),  
  5.     hired DATE NOT NULL DEFAULT '1970-01-01',  
  6.     separated DATE NOT NULL DEFAULT '9999-12-31',  
  7.     job_code INT NOT NULL,  
  8.     store_id INT NOT NULL  
  9. )  
  10.   
  11. PARTITION BY RANGE (job_code) (  
  12.     PARTITION p0 VALUES LESS THAN (100),  
  13.     PARTITION p1 VALUES LESS THAN (1000),  
  14.     PARTITION p2 VALUES LESS THAN (10000)  
  15. );  

 
在这个例子中, 店内工人相关的所有行将保存在分区p0中,办公室和支持人员相关的所有行保存在分区p1中,管理层相关的所有行保存在分区p2中。
       在VALUES LESS THAN 子句中使用一个表达式也是可能的。这里最值得注意的限制是MySQL 必须能够计算表达式的返回值作为LESS THAN (<)比较的一部分;因此,表达式的值不能为NULL 。由于这个原因,雇员表的hired, separated, job_code,和store_id列已经被定义为非空(NOT NULL)。
       除了可以根据商店编号分割表数据外,你还可以使用一个基于两个DATE (日期)中的一个的表达式来分割表数据。例如,假定你想基于每个雇员离开公司的年份来分割表,也就是说,YEAR(separated)的值。实现这种分 区模式的CREATE TABLE 语句的一个例子如下所示:

Sql代码
  1. CREATE TABLE employees (  
  2.     id INT NOT NULL,  
  3.     fname VARCHAR(30),  
  4.     lname VARCHAR(30),  
  5.     hired DATE NOT NULL DEFAULT '1970-01-01',  
  6.     separated DATE NOT NULL DEFAULT '9999-12-31',  
  7.     job_code INT,  
  8.     store_id INT  
  9. )  
  10.   
  11. PARTITION BY RANGE (YEAR(separated)) (  
  12.     PARTITION p0 VALUES LESS THAN (1991),  
  13.     PARTITION p1 VALUES LESS THAN (1996),  
  14.     PARTITION p2 VALUES LESS THAN (2001),  
  15.     PARTITION p3 VALUES LESS THAN MAXVALUE  
  16. );  

 在这个方案中,在1991年前雇佣的所有雇员的记录保存在分区p0中,1991年到1995年期间雇佣的所有雇员的记录保存在分区p1中, 1996年到2000年期间雇佣的所有雇员的记录保存在分区p2中,2000年后雇佣的所有工人的信息保存在p3中。

RANGE分区在如下场合特别有用:
      1)、 当需要删除一个分区上的“旧的”数据时,只删除分区即可。 如果你使用上面最近的那个例子给出的分区方案,你只需简单地使用 “ALTER TABLE employees DROP PARTITION p0;”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表,这比运行一个如“DELETE FROM employees WHERE YEAR (separated) <= 1990;”这样的一个DELETE查询要有效得多。
      2)、想要使用一个包含有日期或时间值,或包含有从一些其他级数开始增长的值的列。
      3)、经常运行直接依赖于用于分割表的列的查询。例如,当执行一个如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;”这样的查询时,MySQL可以很迅速地确定只有分区p2需要扫描,这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。

注释:这种优化还没有在MySQL 5.1源程序中启用,但是,有关工作正在进行中。

  • LIST分区

      类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

      LIST分区通过使用“PARTITION BY LIST(expr)”来实现,其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过“VALUES IN (value_list)”的方式来定义每个分区,其中“value_list”是一个通过逗号分隔的整数列表
注释:在MySQL 5.1中,当使用LIST分区时,有可能只能匹配整数列表。

Sql代码
  1. CREATE TABLE employees (  
  2.     id INT NOT NULL,  
  3.     fname VARCHAR(30),  
  4.     lname VARCHAR(30),  
  5.     hired DATE NOT NULL DEFAULT '1970-01-01',  
  6.     separated DATE NOT NULL DEFAULT '9999-12-31',  
  7.     job_code INT,  
  8.     store_id INT  
  9. );  

 
假定有20个音像店,分布在4个有经销权的地区,如下表所示:

====================
地区      商店ID 号

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

北区      3, 5, 6, 9, 17
东区      1, 2, 10, 11, 19, 20
西区      4, 12, 13, 14, 18
中心区   7, 8, 15, 16

====================
要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表,可以使用下面的“CREATE TABLE”语句:

Sql代码
  1. CREATE TABLE employees (  
  2.     id INT NOT NULL,  
  3.     fname VARCHAR(30),  
  4.     lname VARCHAR(30),  
  5.     hired DATE NOT NULL DEFAULT '1970-01-01',  
  6.     separated DATE NOT NULL DEFAULT '9999-12-31',  
  7.     job_code INT,  
  8.     store_id INT  
  9. )  
  10.   
  11. PARTITION BY LIST(store_id)  
  12.     PARTITION pNorth VALUES IN (3,5,6,9,17),  
  13.     PARTITION pEast VALUES IN (1,2,10,11,19,20),  
  14.     PARTITION pWest VALUES IN (4,12,13,14,18),  
  15.     PARTITION pCentral VALUES IN (7,8,15,16)  
  16. );  

 

这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如,假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录 (行)可以使用查询“ALTER TABLE employees DROP PARTITION pWest;”来进行删除,它与具有同样作用的DELETE (删除)查询“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18);”比起来,要有效得多。
【要点】:如果试图插入列值(或分区表达式的返回值)不在分区值列表中的一行时,那么“INSERT”查询将失败并报错。例如,假定LIST分区的采用上面的方案,下面的查询将失败:

Sql代码
  1. INSERT INTO employees VALUES(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);  

 
这是因为“store_id”列值21不能在用于定义分区pNorth, pEast, pWest,或pCentral的值列表中找到。要重点注意的是,LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。

LIST分区除了能和RANGE分区结合起来生成一个复合的子分区,与HASH和KEY分区结合起来生成复合的子分区也是可能的。

  • HASH分区         

       基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

      要使用HASH分区来分割一个表,要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句,其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外,你很可能需要在后面再添加一个“PARTITIONS num”子句,其中num 是一个非负的整数,它表示表将要被分割成分区的数量。

Sql代码
  1. CREATE TABLE employees (  
  2.     id INT NOT NULL,  
  3.     fname VARCHAR(30),  
  4.     lname VARCHAR(30),  
  5.     hired DATE NOT NULL DEFAULT '1970-01-01',  
  6.     separated DATE NOT NULL DEFAULT '9999-12-31',  
  7.     job_code INT,  
  8.     store_id INT  
  9. )  
  10. PARTITION BY HASH(store_id)  
  11. PARTITIONS 4;  

 如果没有包括一个PARTITIONS子句,那么分区的数量将默认为1。 例外: 对于NDB Cluster(簇)表,默认的分区数量将与簇数据节点的数量相同,

这种修正可能是考虑任何MAX_ROWS 设置,以便确保所有的行都能合适地插入到分区中。

  • LINER HASH

MySQL还支持线性哈希功能,它与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-of-two)运算法则,而常规 哈希使用的是求哈希函数值的模数。
线性哈希分区和常规哈希分区在语法上的唯一区别在于,在“PARTITION BY” 子句中添加“LINEAR”关键字。

Sql代码
  1. CREATE TABLE employees (  
  2.     id INT NOT NULL,  
  3.     fname VARCHAR(30),  
  4.     lname VARCHAR(30),  
  5.     hired DATE NOT NULL DEFAULT '1970-01-01',  
  6.     separated DATE NOT NULL DEFAULT '9999-12-31',  
  7.     job_code INT,  
  8.     store_id INT  
  9. )  
  10. PARTITION BY LINEAR HASH(YEAR(hired))  
  11. PARTITIONS 4;  

 

假设一个表达式expr, 当使用线性哈希功能时,记录将要保存到的分区是num 个分区中的分区N,其中N是根据下面的算法得到:
1.    找到下一个大于num.的、2的幂,我们把这个值称为V ,它可以通过下面的公式得到:
2.    V = POWER(2, CEILING(LOG(2, num)))
(例如,假定num是13。那么LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)就是4,则V = POWER(2,4), 即等于16)。
3.    设置 N = F(column_list) & (V - 1).
4.    当 N >= num:
·         设置 V = CEIL(V / 2)
·         设置 N = N & (V - 1)
例如,假设表t1,使用线性哈希分区且有4个分区,是通过下面的语句创建的:
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR HASH( YEAR(col3) )
    PARTITIONS 6;
现在假设要插入两行记录到表t1中,其中一条记录col3列值为'2003-04-14',另一条记录col3列值为'1998-10-19'。第一条记录将要保存到的分区确定如下:
V = POWER(2, CEILING(LOG(2,7))) = 8
N = YEAR('2003-04-14') & (8 - 1)
   = 2003 & 7
   = 3
(3 >= 6 为假(FALSE): 记录将被保存到#3号分区中)
第二条记录将要保存到的分区序号计算如下:
V = 8
N = YEAR('1998-10-19') & (8-1)
  = 1998 & 7
  = 6
(6 >= 4 为真(TRUE): 还需要附加的步骤)
N = 6 & CEILING(5 / 2)
  = 6 & 3
  = 2
 
(2 >= 4 为假(FALSE): 记录将被保存到#2分区中)
按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷,有利于处理含有极其大量(1000吉)数据的表。它的缺点在于,与使用

常规HASH分区得到的数据分布相比,各个分区间数据的分布不大可能均衡。

  • KSY分区

类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

Sql代码
  1. CREATE TABLE tk (  
  2.     col1 INT NOT NULL,  
  3.     col2 CHAR(5),  
  4.     col3 DATE  
  5. )  
  6. PARTITION BY LINEAR KEY (col1)  
  7. PARTITIONS 3;  

 
在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用,分区的编号是通过2的幂(powers-of-two)算法得到,而不是通过模数算法。