解决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性能优化的技术。

WebOS的优缺点

现在在网上有很多关于WEBOS的讨论,普遍的业内人士认为WEBOS将会是以后的趋势,于是也有很多的关于WebOs的网站开张了,还有一些人甚至大胆的预言国际搜索巨头google在以后的日子一定会涉足到操作系统的领域,而且发展的方向就是WEBOS,我对此事一直保持怀疑的态度,因为我个人认为,如果WebOs真正的可以替代传统的操作系统的话至少还有10到20年的时间,一次来发展为此匹配的软硬件。

一味的猜想是无谓的,现在就WebOs的一些优略和大家展开讨论。

WebOs的优势

全新的用户体验。

WebOs在基于web的开发方式的情况下,开发出了华丽而且有趣的web应用,与以往的使用web应用程序的不同的使用方式,更多的有了基于桌面应用的操作方式,是使用人员在一般的网页上也体验到了只有在桌面应用程序一样的方便的操作模式,在刚开始的时候,用户是非常德兴奋得,因为这是他们在使用网页时候的新的体验,他们非常的感兴趣。

时时保存个人信息。
 一般的作WebOs不会是把所有的程序都一次性的下载到客户端,那样的话就会花费更长的时间,用户的满意度就会降低,所以一般采用的方式是使用Web2.0的Ajax方式,当用户有新的请求的话就去服务器那里现在新的js文件,当然也不局限于js文件的,还可以使falsh或者是applet,这些程序为了保存数据的不丢失,一般会在很短的时间内把你所做的修改发送到服务器上,这样,即便是有特殊的情况,比如断电,或者是电脑死机,只要我们下一次的再重新登陆,我们上一次的操作还保留着,即使丢失也是在不影响你的正常的使用的情况下的,通常丢失的信息是很少的。

摆脱空间的限制,随处都可以使用自己的系统。

如果你是经常的出差,但是苦于没有自己的个人便携式电脑,而且你还要经常的使用电脑来处理一些文档或者是记录一些东西,或者是收发电子邮件,那么使用 webOs就相当的方便,你完全可以脱离你的文件仅仅只可以保存在一天电脑上,外出的时候就不能访问的缺陷,地域空间的限制你就完全的感受不到了。你可以在该系统上定制你自己的一些个性化的服务,然后就放到哪里不用再去理会,只要你再一次的登陆,就可以享受到你的定制,而且可以在不同的计算机上使用,这就好像你有了一台属于自己的便携式的个人电脑一样,非常的方便,而且都是自己非常的喜欢的专门为自己服务的一些个性化的特点的你自己的系统,非常德棒。

不需要安装大量的客户端软件,只需要有浏览器。

伴随着计算机的发展,人们使用计算机的地方越来越多,使用的领域在不断的扩大,为了解决不同领域的问题和个人的文档或者是数据的记录的要求,需要很多的软件来进行日常的操作,为此,当你有了自己的电脑之后你发现,现在的电脑还不能满足我的需求,或者是我是一个记者,我发现我还需要word来处理我的稿件,但是现在没有,我还要安装微软的办公软件,或者是其他的办公软件,但是身边没有,我还不能开始我的写作,这是让人很沮丧的一件事情,当然这只是一方面,现实世界的需求会更多,为此我们要经常地来为满足自己的需求而安装大量的软件,这是一件非常的简单但是麻烦的事情。现在有了WebOs,你就不需要再为此而烦恼了,因为不管你有什么样的应用,只要在对应的WebOs的服务器上有着一项服务,你就可以直接的登陆到WebOs系统进而直接的使用,不需要安装,这是如此的方便,是使用人员远离了那些繁琐的安装软件或者是更新软件的烦恼当中。

尽管WebOs有如此多的好处,但是依然不可以掩盖他的瑕疵,有些得瑕疵甚至直接的影响到他以后的发展方向,下边我就WebOs的不足来和他家讨论一下:

处理数据的速度不过快

WebOs是一种基于ajax的web应用,它是把客户的请求通过异步的传输发送到web服务器,然后再返回数据。我们在使用的时候,如果说你使用 WebOs来处理文档,或者是其他的,也许你会感觉到速度很慢,问其原因,一般不是什么电脑配置的原因,而是网络的延迟,造成的感觉很慢。我说到这里也许有人要骂我了,现在的宽带已经非常的快难道还会出现这样的事情吗?实施上则是很正常的,我们有了足够的带宽,但是造成网络延迟的原因也许还别别的阿,我们为了一个应用也学需要大量的js文件,然后要不断地向服务器发送数据,无形中就增加了网络的负担,具体的变相就是我们双击了某个服务,但是登了好半天才看到我们想看到的界面,你是不是会觉得不爽那!,一般人是会的,除非你是神人。

处理大型程序性能

WebOs的使用是离不开浏览器的,他的所有的操作基本上是使用js来控制的,但是js的速度是如此的慢,根据ken的统计,js的运行速度不到sh的 1/10,java的1/50 ,php的1/200,c的1/500,可以说js是一种速度非常的慢的语言,所以在需要一写大型的软件的时候,如果是使用WebOs的话可能就是不肯能的事情,或者是速度慢的让你无法德忍受的程度,加上浏览器的对js的限制,你甚至连做一个担心软件是基于WebOs的项目的信心都没有,仅仅是因为js的速度慢,这台我们这些做技术的人伤心了。更为关键的是,WebOs的载体是浏览器,现在的很多浏览器本身就有很多的问题,如果在承载我们的WebOs也许会造成严重的后果。

不能玩大型的游戏
                电脑之所以走进很多的家庭,主要的原因是游戏,为了玩游戏才购买的电脑,前边我已经提到了WebOs不可以处理大型的程序,比如说现在流行的魔兽世界,我想再过十年也不会在WebOs上出现的,这就不会对广大的游戏爱好者产生吸引力,我设想如果可以玩得话,估计浏览器已经崩过N的N次方了,如果WebOs 可以突破这一到坎的话,我们就有理由相信以后的天下是WebOs的,什么微软或者是Linux都要淘汰,但是事实并不一定总是向着我们期望的方向发展的。

功能
                在开发WebOs的时候,开发人员常常需要考虑更多的问题,比如说网络延迟,文件存盘等,这些看起来很简单,但是在开发的时候要做的甚至是其他桌面开发人员要做的多处很多,使用html语言来作为和用户的交互接口,功能上自然就没有像桌面程序一样自然流畅,功能上,由于所用开发语言和浏览器的原因,功能上远不如桌面程序那么的强大,虽然有一些的开发人员把基于桌面的程序改写成了WebOs的程序,但是在方便性的功能上和一般的桌面程序相差甚远,功能上的不过强大也是造成WebOs不能快速发展的一个关键原因。

安全性
                说到安全性,这就非常的简单了,WebOs是基于HTTP的,所以传输的数据都是明文,任何人都可一截去,然后发现用用信息,造成信息的泄漏或者是伪造你的信息,使你从中受到末方面的损失,具体不多说了,做开发的都知道。

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)算法得到,而不是通过模数算法。

MySQL Proxy – 官方MySQL连接池和代理工具

包括了查询分析(query analysis & query filtering),同时它可以根据分析结果,决定转发方向. 完成了一主多从,或一主(一备主)多从中,写和读操作的完美区分.为replication提供了更多的应用空间,同时还有负载均衡,失败转移等许多高级 功能。至此,我们可以得出结论,它的主要作用就是为我们的MySQL数据库保驾护航

MySQL外键的使用

像MySQL这样的关系型数据库管理系统,它们的基础是在数据库的表之间创建关系的能力。通过方便地在不同表中建立记录到记录的联系,RDBMS可以利用不同的方法分析数据,同时保持数据库以系统的方式、最小的冗余进行组织。

 
 

简单描述:
这些关系基本上依靠外键进行管理,在关系中所有表中具有相同含义的字段作为公共部分来连接不同表中的记录。外键可以是一对一的,一个表的记录只能与另一个表的一条记录连接,或者是一对多的,一个表的记录与另一个表的多条记录连接。

 

MySQL中“键”和“索引”的定义相同, 所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引,但是外键字段必须由用户进行明确的索引。这和一些封建思想比较沉重的家庭是一样的,外来的孩子(儿媳妇,倒插门女婿)一般都是不受重视的。

 
 

低俗示例:

表间一对一关系示例:

有两张表,第一张表是记录公司有多少人,都有谁,也就是员工编号及员工姓名这些基本表。另一张表记录每个月发给用户多少工资,所谓工资表是也。

 

但是工资表里面不能以员工姓名为主键,同样要通过员工id,因为员工的姓名是可能重复的啊。部门经理叫张三,小弟也叫张三,那这俩张三的工资能一样吗?并且员工表里面的每个人都有工资,否则谁也不给你干活,且一个人只能有一份工资,否则老板也不同意了。所以员工表和工资表是通过员工id进行关联的一对一关系

 

不过我们要有一个好的价值观,我们上班不能为了钱,我们是为了学知识,学文化,为早日实现四个现代化(别问我是啥,也别问我到底实现没有)而努力奋斗。所以在工资表里如果没有你也不要乱喊。嗯。

 
/*
建立员工表
*/
create table employees (
id int(5) not null auto_increment ,
name varchar(8) not null,
primary key (id)
)
typeinnodb;

/*
建立工资表
*/

create table payroll(
id int(5) not null,
emp_id int(5) not null,
name varchar(8) not null,
payroll float(4,2) not null,
primary key(id),
index emp_id (emp_id),
foreign key (emp_id) references employees (id)
)
type = innodb;

 

表间一对多关系示例:

有两个表,一个是贪官表,有贪官的id和名字。另有一张贪官情妇表,注意一个贪官不一定只有一个情妇,其有个二三四五奶是很正常的,所以在贪官表里面的一条数据,对应情妇表里可能就有多条记录,这是通过贪官id进行关联的一对多关系

 
 

参照完整性:

当外键与另一个表的字段有关系,而且这种关系是惟一时,这个系统就称为处于参照完整性的状态。也就是说,如果一个字段在所有的表中只出现一次,而且每个表的这个字段的变化都会影响其他表,这就是存在参照完整性。

术语理解上可能不太方便,其实就是说要在有外键的表中保持所有数据的一致性。比如说“张三”离职了,在员工表里面肯定没有这个人了,可是如果在工资表里面还存在这个孩子,那么老大就会很生气的

另外,比如说一个县官,因为一些小政绩,由县官变成了知府,那么他的那些情妇的地位也要调整一下,最起码得从县官二奶改为知府二奶,否则这位二奶也是不会同意的

 
 

MySQL的外键只能在InnoDB表中使用:

当今主流数据库都会自动考虑参照完整性的问题。当你更新或删除数据时,其会把相关联的表中数据也都给你变过来。比如县官张三改名为王二麻子,其情妇的称号就会自动改为王二麻子的情妇。嗯。

MySQL对此一直持观望态度,它允许使用外键,但是为了完整性检验的目的,在除了InnoDB表类型之外的所有表类型中都忽略了这个功能。这可能有些怪异,实际上却非常正常:对于数据库的所有外键的每次插入、更新和删除后,进行完整性检查是一个耗费时间和资源的过程,它可能影响性能,特别是当处理复杂的或者是缠绕的连接树时。因而,用户可以在表的基础上,选择适合于特定需求的最好结合。

所以,如果需要更好的性能,并且不需要完整性检查,可以选择使用MyISAM表类型,如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能,最好选择表结构为innoDB类型。

 
 

MySQL创建外键语法:

创建外键的语法是这样的:FOREIGN KEY (当前表的字段名)… REFERENCES 参照表 (参照表的字段名)

foreign key (emp_id) references employees (id); 的意思就是说当前表的emp_id字段是以employees的id字段为外键的。

 
 

注意事项:

  • 一旦建立外键,MySQL只允许向当前表中加入外键表中已有的数据列。比如说贪官表里有“王二麻子”,那么在情妇表只才能有“王二麻子的情妇”。也就是说只有确认一个人是贪官了,才能把其情妇信息列入此表中,否则是不行滴。
  • 关系中的所有表必须是innoDB表,在非InnoDB表中,MySQL将会忽略FOREIGN KEY…REFERENCES修饰符。
  • 用于外键关系的字段必须在所有的参照表中进行明确地索引,InnoDB不能自动地创建索引。
  • 在外键关系中,字段的数据类型必须相似,这对于大小和符号都必须匹配的整数类型尤其重要。
  • 即使表存在外键约束,MySQL还允许我们删除表,并且不会产生错误(即使这样做可能会破坏更早创建的外键)

删除外键方法:

long long ago,人们只能通过删除表来删除外键。不过现在MySQL(在4.0.13及更高版本中)提供了一种从表中删除外键比较缓和的方法,缓和与否不太清楚,但是至少不再那么无耻。

 

ALTER TABLE table-name DROP FOREIGN KEY key-id;

 

这里有一个概念,这个外键的id是啥玩意?我们可以通过SHOW CREATE TABLE 命令来获得key-id的值。日后我们详细讨论这些内容,大家可以自行演示。

/*
显示建表结构语句,key-id为payroll_ibfk_1
*/
show create table payroll \G
/*
*************************** 1. row ***************************
       Table: payroll
Create Table: CREATE TABLE payroll (
  id int(5) NOT NULL,
  emp_id int(5) NOT NULL,
  name varchar(8) NOT NULL,
  payroll float(4,2) NOT NULL,
  PRIMARY KEY (id),
  KEY emp_id (emp_id),
  CONSTRAINT payroll_ibfk_1 FOREIGN KEY (emp_id) REFERENCES employees (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

*/

 
 
 

自动键更新和删除:

外键可以保证新插入的记录的完整性。但是,如果在REFERENCES从句中从已命名的表删除记录会怎样?在使用同样的值作为外键的辅助表中会发生什么?

很明显,那些记录也应该被删除,否则在数据库中就会有很多无意义的孤立记录。MySQL可能通过向FOREIGN KEY…REFERENCES 修饰符添加一个ON DELETE或ON UPDATE子句简化任务,它告诉了数据库在这种情况如何处理孤立任务。

 

关键字 含义
CASCADE 删除包含与已删除键值有参照关系的所有记录
SET NULL 修改包含与已删除键值有参照关系的所有记录,使用NULL值替换(只能用于已标记为NOT NULL的字段)
RESTRICT 拒绝删除要求,直到使用删除键值的辅助表被手工删除,并且没有参照时(这是默认设置,也是最安全的设置)
NO ACTION 啥也不做

请注意,通过 ON UPDATE 和ON DELETE规则,设置MySQL能够实现自动操作时,如果键的关系没有设置好,可能会导致严重的数据破坏。例如,如果一系列的表通过外键关系和ON DELETE CASCADE 规则连接时,任意一个主表的变化都会导致甚至只和原始删除有一些将要联系的记录在没有警告的情况下被删除。所以,我们在操作之前还是要检查这些规则的,操作之后还要再次检查。