年末整理十三

########################################################################
读写分离策略：
master/slave
master 负责事务性查询
slave  负责select查询
数据库复制用来把事务性查询导致的变更同步到集群中的从数据库
MySQL Replication 可以将master的数据复制分布到多个slave上，
然后可与利用slave来分担master的读压力。

MySQL Proxy：
那么对于前台应用来说，就要考虑如何将读的压力分布到多个slave上。
如果每个应用都需要来实现读写分离的算法，
一则成本太高，
二来如果slave增加更多的机器，应用就要随之修改。
明显的，如果在应用和数据库间加一个专门用于实现读写分离的中间层，则整个系统的架构拥有更好的扩展性。
MySQL Proxy就是这么一个中间层代理，
简单的说，MySQL Proxy就是一个连接池，负责将前台应用的连接请求转发给后台的数据库，
并且通过使用lua脚本，可以实现复杂的连接控制和过滤，从而实现读写分离和负载平衡。
对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。
当然，这样proxy机器可能成为单点失效，
但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多个proxy的连接参数即可。

Jan Kneschke在《MySQL Proxy learns R/W Splitting》中详细的介绍了这种技巧以及连接池问题
为了实现读写分离我们需要连接池。
我们仅在已打开了到一个后端的一条经过认证的连接的情况下，才切换到该后端。
MySQL协议首先进行握手。
当进入到查询/返回结果的阶段再认证新连接就太晚了。
我们必须保证拥有足够的打开的连接才能保持运作正常。
实现读写分离的LUA脚本：
　　-- 读写分离
　　--
　　-- 发送所有的非事务性Select到一个从数据库

if is_in_transaction == 0 and
　　packet:byte() == proxy.COM_QUERY and
　　packet:sub(2, 7) == "SELECT" then
　　local max_conns = -1
　　local max_conns_ndx = 0
　　for i = 1, #proxy.servers do
　　local s = proxy.servers[i]
　　-- 需要选择一个拥有空闲连接的从数据库
　　if s.type == proxy.BACKEND_TYPE_RO and
　　s.idling_connections > 0 then
　　if max_conns == -1 or
　　s.connected_clients < max_conns then
　　max_conns = s.connected_clients
　　max_conns_ndx = i
　　end
　　end
　　end
　　-- 至此，我们找到了一个拥有空闲连接的从数据库
　　if max_conns_ndx > 0 then
　　proxy.connection.backend_ndx = max_conns_ndx
　　end
　　else
　　-- 发送到主数据库
　　end
　　return proxy.PROXY_SEND_QUERY

 

#####################################################################################
mysql命令
show databases； 显示当前存在所有数据库列表
use databaseName 使用databaseName的数据库
注意，USE，类似QUIT，不需要一个分号。（如果你喜欢，你可以用一个分号终止这样的语句；这无碍
在Unix下，数据库名称是区分大小写的    在Windows下，该限制不适用

CREATE DATABASE menagerie; 创建数据库
SHOW TABLES;
create table tablename（id int ， name varchar(20) ， birth date ） ；
DESCRIBE tablename;
quit  退出

##########命令大全###########
备注:　多行命令输入:注意不能将单词断开;当插入或更改数据时，
   不能将字段的字符串展开到多行里，否则硬回车将被储存到数据中;
   每条语句输入完毕后要在末尾填加分号';'，或者填加'\g'也可以；

1. 启动服务: net start mySql
2. 进入: mysql -hlocalhost -uroot -proot databasename;
3. 列出表: show tables;
4. 显示表格列的属性: show columns from tablename;
5. 建立数据库: source db.sql;
6. 匹配字符:  _ 代表任何一个字符 , % 代表任何字符串;
7. 增加一个字段: alter table tablename add column fieldname datatype ;
8. 增加多个字段: alter table tname add column fieldname datatype , add column fname datatype ;
9. 增加一个管理员帐户：grant all on *.* to user@localhost identified by "password";
10. 查询时间：select now();
11. 查询当前用户：select user();
12. 查询数据库版本：select version();
13. 查询当前使用的数据库：select database();
14. 删除student_course数据库中的students数据表：
　　rm -f student_course/students.*
15. 备份数据库：(将数据库test备份)
　　mysqldump -u root -p test>c:\test.txt
　　备份表格：(备份test数据库下的mytable表格)
　　mysqldump -u root -p test mytable>c:\test.txt
　　将备份数据导入到数据库：(导回test数据库)
　　mysql -u root -p test
16. 创建临时表：（建立临时表zengchao）
  create temporary table zengchao（name varchar(10)） ；
17. 创建表时先判断表是否存在
  create table if not exists students （） ；
18. 从已经有的表中复制表的结构：
  create table tablename select * from table2 where 1<> 1
19. 复制表： create table tablename select * from table2
20. 对表重新命名：alert table table1 rename as table2
21. 修改列的类型：
  alter table table1 modify id int unsigned;//修改列id的类型为int unsigned
　　alter table table1 change id sid int unsigned;//修改列id的名字为sid，而且把属性修改为int unsigned
22. 创建索引：
  alter table table1 add index ind_id (id);
　　create index ind_id on table1 (id);
　　create unique index ind_id on table1 (id);//建立唯一性索引
23. 删除索引：
  drop index idx_id on table1;
　　alter table table1 drop index ind_id;
24. 联合字符或者多个列：（将列id与“.”和列name和=链接）
  select concat (id,'.',name,'=') form students ;
25. limit(选出10到20条)<第一个记录集的编号是0>
  select * from students order by id limit 9,10 ;
26. MySQL不支持的功能:
  事务 视图  外键 引用完整性 存储过程 和 触发器
27. MySQL会使用索引的操作符号:
  < <= >= >  =  between  in 不带%或者_开头的like
28. 使用索引的缺点:
  1) 减慢增删改数据的速度
  2) 占用磁盘空间 ;
  3) 增加查询优化器的负担
  当查询优化器生成执行计划时,会考虑索引,太多的索引会给
  查询优化器增加工作量,导致无法选择最优的查询方案;
  #####################
16、分析索引效率
　　方法：在一般的SQL语句前加上explain；
　　分析结果的含义：
　　1）table：表名；
　　2）type：连接的类型，(ALL/Range/Ref)。其中ref是最理想的；
　　3）possible_keys：查询可以利用的索引名；
　　4）key：实际使用的索引；
　　5）key_len：索引中被使用部分的长度（字节）；
　　6）ref：显示列名字或者"const"（不明白什么意思）；
　　7）rows：显示MySQL认为在找到正确结果之前必须扫描的行数；
　　8）extra：MySQL的建议；
　　
　　17、使用较短的定长列
　　1）尽可能使用较短的数据类型；
　　2）尽可能使用定长数据类型；
　　a）用char代替varchar，固定长度的数据处理比变长的快些；
　　b）对于频繁修改的表，磁盘容易形成碎片，从而影响数据库的整体性能；
　　c）万一出现数据表崩溃，使用固定长度数据行的表更容易重新构造。使用固定长度的数据行，每个记录的开始位置都是固定记录长度的倍数，可以很容易被检测到，但是使用可变长度的数据行就不一定了；
　　d）对于MyISAM类型的数据表，虽然转换成固定长度的数据列可以提高性能，但是占据的空间也大；
　　
　　18、使用not null和enum
　　尽量将列定义为not null，这样可使数据的出来更快，所需的空间更少，而且在查询时，MySQL不需要检查是否存在特例，即null值，从而优化查询；
　　如果一列只含有有限数目的特定值，如性别，是否有效或者入学年份等，在这种情况下应该考虑将其转换为enum列的值，MySQL处理的更快，因为所有的enum值在系统内都是以标识数值来表示的；
　　
　　19、使用optimize table
　　对于经常修改的表，容易产生碎片，使在查询数据库时必须读取更多的磁盘块，降低查询性能。具有可变长的表都存在磁盘碎片问题，这个问题对blob数据类型更为突出，因为其尺寸变化非常大。可以通过使用optimize table来整理碎片，保证数据库性能不下降，优化那些受碎片影响的数据表。 optimize table可以用于MyISAM和BDB类型的数据表。实际上任何碎片整理方法都是用mysqldump来转存数据表，然后使用转存后的文件并重新建数据表；
　　
　　20、使用procedure analyse()
　　可以使用procedure analyse()显示最佳类型的建议，使用很简单，在select语句后面加上procedure analyse()就可以了；例如：
　　select * from students procedure analyse();
　　select * from students procedure analyse(16,256);
　　第二条语句要求procedure analyse()不要建议含有多于16个值，或者含有多于256字节的enum类型，如果没有限制，输出可能会很长；
　　
　　21、使用查询缓存
　　1）查询缓存的工作方式：
　　第一次执行某条select语句时，服务器记住该查询的文本内容和查询结果，存储在缓存中，下次碰到这个语句时，直接从缓存中返回结果；当更新数据表后，该数据表的任何缓存查询都变成无效的，并且会被丢弃。
　　2）配置缓存参数：
　　变量：query_cache _type，查询缓存的操作模式。有3中模式，0：不缓存；1：缓存查询，除非与 select sql_no_cache开头；2：根据需要只缓存那些以select sql_cache开头的查询； query_cache_size：设置查询缓存的最大结果集的大小，比这个值大的不会被缓存。
　　
　　22、调整硬件
　　1）在机器上装更多的内存；
　　2）增加更快的硬盘以减少I/O等待时间；
　　寻道时间是决定性能的主要因素，逐字地移动磁头是最慢的，一旦磁头定位，从磁道读则很快；
　　3）在不同的物理硬盘设备上重新分配磁盘活动；
　　如果可能，应将最繁忙的数据库存放在不同的物理设备上，这跟使用同一物理设备的不同分区是不同的，因为它们将争用相同的物理资源（磁头）。

 

################################################################
mysql 优秀网址：
http://www.mysqlperformanceblog.com/
http://forge.mysql.com
###################################################################
此前有款网络游戏的数据库是采用MySQL。操作系统是CentOS。部分服务器的操作系统又是Windows。
出现了一个小毛病。那就是MySQL大小写的问题。

在CentOS安装的MySQL的配置文件中(/etc/my.cnf)，是没有lower_case_table_names=1这行的。
在Windows安装的MySQL的配置文件中(my.ini)，是有lower_case_table_names=1这行的。

lower_case_table_names=1的用途是让MySQL实现不区分大小写。

所以当时出了些毛病，后来才发现是这个的问题。连忙在CentOS中的my.cnf(/etc/my.cnf)的[mysqld]区段下增加:

################################################
备份：
在mysql的bin目录下运行：
备份单一表： mysqldump -hlocalhost -uroot -proot uchome uchome_album > album.sql
备份数据库： mysqldump -hlocalhost -uroot -proot uchome  > db.sql
备份数据库结构： mysqldump -hlocalhost -uroot -proot -d -- add-drop-table uchome  > nodatadb.sql
-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table
备忘: mysqldump在linux下可直接使用 在windows下有时需要进入mysql/bin中使用；
而且用mysqldump导出的备份 必须先建立数据库才能导入

4.导入数据库
　　常用source 命令
　　进入mysql数据库控制台，
　　如mysql -u root -p
　　mysql>use 数据库
　　然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
　　mysql>source e:\temp\nodatadb.sql

ERROR 1130:####################################################
在使用MySQL-Front连接mysql的时候发生的这个错误

ERROR 1130: Host 192.168.88.160 is not allowed to connect to this MySQL server

1.该表法：
更改 mysql 数据库里的 user表里的 host项
localhost改称%

mysql -u root -p

mysql>use mysql;

mysql>update user set host = '%'  where user ='root';

mysql>flush privileges;

mysql>select 'host','user' from user where user='root';

2. 授权法

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' with grant option;
如果你想允许用户myuser从ip为192.168.1.3的主机连接到mysql服务器，并使用mypassword作为密码
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
 
 操作完毕后，记得重新启动mysql命令生效。
Windows下：net stop mysql / net start mysql
Linux下：service mysql restart 或 /etc/init.d/mysql restart

mysql limit的用法####################################################
mysql的limit用法
1.select * from tablename <条件语句> limit 100,15
从100条记录后开始取15条 (实际取取的是第101-115条数据)
2.select * from tablename <条件语句> limit 100,-1
从第100条后开始-最后一条的记录
3.select * from tablename <条件语句> limit 15
相当于limit 0,15   .查询结果取前15条数据

启动和关闭mysql服务：#######################################################
Q：如何启动和关闭Mysql？
A：linux下：比如我的mysql是用源码方式安装在/usr/local/mysql
自动：将/usr/local/mysql/share/mysql/mysql.server拷贝到/etc/rc.d/init.d/下，然后
chkconfig --add mysql.server 就可以开机就自动启动mysql服务了
手动：以root身份执行/usr/local/mysql/bin/mysqld_safe --user=mysql
windwos下：
自动：
用cmd方式，到mysql安装路径下的bin文件夹下，执行：mysqld-nt --install
手动：直接到mysql安装的bin文件件下执行 net start mysql 即可 。
如果不想让mysql在计算机启动时候就启动服务，执行：mysql-nt --remove
也可以在HKEY_LOCAL_MACHINE/SYSTEM/CURRENTCONTROLSET/SERVICES中删除对应服务并重启。
关闭mysql：mysqldamin -uroot -p shutdown
启动mysql：mysqld-nt --install
      net start mysql
      

mysql不能输入汉字####################################################################
mysql问题 Incorrect string value:"\ \ \ \ \"for column'trurname' at row 1
mysql 在填写表的时候不能输入汉字！！！怎么解决呀！
原因：估计安装mysql的时候设置的 编码不正确 设置成utf8 或者gbk 默认是lant
方法：先确保改过来之后重启了服务，然后就在建成的表中用show create table tabblename;
查询一下建表用的字符集是什么形式，就发现了是用的latin1,想想当时是用latin1字符建成的表，
就是现在改了字符集也只对以后的建表有作用，以前建好的表就无能为力了，所以我删除表后重建就好了，
查询一下字符集，果然改回来了。

命令： show create table tabblename; 显示建表时的详细信息。

###########################################################################
select version(); 显示mysql版本
mysql 数据库分区：#####################################################
什么是数据库分区？
    数据库分区是一种物理数据库设计技术，DBA和数据库建模人员对其相当熟悉。
    虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。
    分区主要有两种形式：//这里一定要注意行和列的概念（row是行，column是列）
水平分区（Horizontal Partitioning）
     这种形式分区是对表的行进行分区，
    通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。
    所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。
    举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。
   （朋奕注：这里具体使用的分区方式我们后面再说，可以先说一点，一定要通过某个属性列来分割，譬如这里使用的列就是年份）
垂直分区（Vertical Partitioning）
    这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，
    每个分区都包含了其中的列所对应的行。
    举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，
    这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。

MySQL5.1对水平分区的支持。而且已经支持分区大部分模式：

虽然MySQL 5.1自动实现了水平分区，但在设计数据库的时候不要轻视垂直分区。
虽然要手工去实现垂直分区，但在特定场合下你会收益不少的。

###########################################################
如何进行实际分区
看看分区的实际效果吧。我们建立几个同样的MyISAM引擎的表，包含日期敏感的数据，但只对其中一个分区。
分区的表（表名为part_tab）我们采用Range范围分区模式，通过年份进行分区：
mysql> CREATE TABLE part_tab
    ->      ( c1 int default NULL,
    -> c2 varchar(30) default NULL,
    -> c3 date default NULL
    ->
    ->      ) engine=myisam
    ->      PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
    ->      PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
    ->      PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
    ->      PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
    ->      PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
    ->      PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
    ->      PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.00 sec)
注意到了这里的最后一行吗？这里把不属于前面年度划分的年份范围都包含了，这样才能保证数据不会出错，
大家以后要记住啊，不然数据库无缘无故出错你就爽了。那下面我们建立没有分区的表（表名为no_part_tab）：

mysql> select count(*) from part_tab where
    -> c3 > date '1995-01-01' and c3 < date '1995-12-31';

######################################################2009/12-17 mysql 字符character的设置#########
MySql中涉及的几个字符

character-set-server/default-character-set：服务器字符集，默认情况下所采用的。
character-set-database：数据库字符集。
character-set-table：数据库表字符集。
优先级依次增加。所以一般情况下只需要设置character-set-server，
而在创建数据库和表时不特别指定字符集，这样统一采用character-set-server字符集。

character-set-client：客户端的字符集。客户端默认字符集。当客户端向服务器发送请求时，请求以该字符集进行编码。
character-set-results：结果字符集。服务器向客户端返回结果或者信息时，结果以该字符集进行编码。
在客户端，如果没有定义character-set-results，则采用character-set-client字符集作为默认的字符集。
所以只需要设置character-set-client字符集。

要处理中文，则可以将character-set-server和character-set-client均设置为GB2312，
如果要同时处理多国语言，则设置为UTF8。

关于UTF-8 GBK GB2312：
UTF-8：Unicode Transformation Format-8bit，允许含BOM，但通常不含BOM。
是用以解决国际上字符的一种多字节编码，它对英文使用8位（即一个字节），中文使用24为（三个字节）来编码。
UTF-8包含全世界所有国家需要用到的字符，是国际编码，通用性强。UTF-8编码的文字可以在各国支持UTF8字符集的浏览器上显示。如，如果是UTF8编码，则在外国人的英文IE上也能显示中文，他们无需下载IE的中文语言支持包。

GBK是国家标准GB2312基础上扩容后兼容GB2312的标准。GBK的文字编码是用双字节来表示的，
即不论中、英文字符均使用双字节来表示，为了区分中文，将其最高位都设定成1。GBK包含全部中文字符，是国家编码，
通用性比UTF8差，不过UTF8占用的数据库比GBD大。

GBK、GB2312等与UTF8之间都必须通过Unicode编码才能相互转换：
GBK、GB2312－－Unicode－－UTF8
UTF8－－Unicode－－GBK、GB2312

GB2312是GBK的子集，GBK是GB18030的子集
GBK是包括中日韩字符的大字符集合
如果是中文的网站 推荐GB2312 GBK有时还是有点问题
gb2312是简体中文的码
gbk支持简体中文及繁体中文
big5支持繁体中文
utf-8支持几乎所有字符

######################查看mysql的字符情况###########
我们先在mysql 命令行下输入
show variables like '%char%';
查看mysql 字符集设置情况:

在登录数据库时，我们用：
mysql --default-character-set=字符集-u root -p 进行连接，这时我们
再用show variables like '%char%';命令查看字符集设置情况，可以发现客户端、数据库连接、
查询结果的字符集已经设置成登录时选择的字符集了

如果是已经登录了，可以使用set names 字符集;命令来实现上述效果，等同于下面的命令：
set character_set_client = 字符集
set character_set_connection = 字符集
set character_set_results = 字符集
如果是通过JDBC 连接数据库，可以这样写URL：
URL=jdbc:mysql://localhost:3306/abs?useUnicode=true&characterEncoding=字符集

JSP 页面等终端也要设置相应的字符集
数据库的字符集可以修改mysql 的启动配置来指定字符集，也可以在create database 时加上
default character set 字符集来强制设置database 的字符集
通过这样的设置，整个数据写入读出流程中都统一了字符集，就不会出现乱码了

#######################################################################
怎么测试你用的服务器支不支持分区呢？在 MySQL console 里输入：
     SHOW VARIABLES LIKE '%partition%';
如果返回 have_partition_engine YES 就表示支持
 如果返回 Empty set 说明你要升级
 ####################################
不使用分区的时候，我们建立一张表，可以不显式的声明使用哪种引擎，
这时如果你设置了服务器为 MyISAM only，则默认使用 MyISAM 引擎（废话），
     如果服务器被设置为支持事务，则默认使用 InnoDB。

分区的类型
常用的分区类型包括： RANGE、 LIST、 HASH、 KEY四种，下面对这四种分区做一些介绍。

RANGE 分区
RANGE 顾名思义就是使用范围分区，就是给数据库划定一个范围，满足范围条件的数据分配到对应的分区。
还是拿刚才的 ws 表来做例子：
CREATE TABLE ws
(
     id INT(11),
     name varchar(8)
)
PARTITION BY RANGE (id)
(
     PARTITION par0 VALUES LESS THAN (1000),
     PARTITION par1 VALUES LESS THAN (2000),
     PARTITION par2 VALUES LESS THAN (3000),
     PARTITION par3 VALUES LESS THAN (4000),
     PARTITION par4 VALUES LESS THAN MAXVALUE
);

LIST 分区
LIST类型个人感觉在日常应用中很少能不被用到，因为丫实在是麻烦。
那位看官说了，怎么个麻烦法啊？您别着急，看看例子就知道了：
CREATE TABLE ws
(
     id INT(11),
     name varchar(8)
)
PARTITION BY LIST(id)
(
     PARTITION par0 VALUES IN (1,2,3,4,5),
     PARTITION par1 VALUES IN (6,7,8,9,10),
     PARTITION par2 VALUES LESS THAN MAXVALUE
)；
列位看官明白了吧，这个LIST就是把分区依据给丫一个一个列出来，只要是括号里的数，就分到对应的区……
当然，我这例子举得不大好，这种方法是不适合以主键为参考列来分的。
但是这种分区很适合给数据分类，比如我很容易把标识为1、3、5的国内客户方par0，2、4、6的海外客户放par1
哪天我不做国内客户了，直接：
ALTER TABLE ws DROP PARTITION par0;
这个世界清静了（数据量庞大时，删除一个分区，比按一定条件删除记录要快N倍）

     RANGE　分区：通过定义某些列的值域来分区

    LIST　分区：与RANGE类似，不过不是值域，而是一些离散值的集合。

    HASH　分区：通过用户对将要插入的行里的字段进行计算后的函数返回值来确定分区。无论函数是怎么样的，返回值不能使负数。

    KEY　分区：类似于hash分区，但是不用函数，而是用多个列来区分，ＭｙＳＱＬ服务器会用自身的ｈａｓｈ函数来处理。但是这些列必须只有整数。

##################################12-22-2009#####################
windows dos窗口下的mysql不是内部或外部命令 
此时需要把mysql的bin目录设置成path（环境变量）
###########################################################
VARCHAR是可变字符数据类型，声明时必须指定它的长度，例如`IP` VARCHAR（10） NOT NULL。

id      INTEGER UNSIGNED NOT NULL AUTO_INCREMENT ：
字面上的意思就是“无符号整数”，就是说该字段的数值都是非负的，这样可以扩大正数的范围，如有符号的TINYINT类型能表示-128至128，而无符号（unsigned）的TINYINT类型就可以表示0至256了。
一个正常大小整数。有符号的范围是-2147483648到2147483647，无符号的范围是0到4294967295。
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
这是INT的一个同义词。