主从复制

主从复制原理

slave 会从master 读取binlog来进行数据同步；

MySQL复制过程分成三步：

master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；

slave将master的binary log events拷贝到它的中继日志（relay log）；

slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的；

复制的基本原则：

每个slave只有一个master; 每个slave只能有一个唯一的服务器ID; 每个master可以有多个salve; 复制的问题： 延时性

主从复制步骤

 mysql版本一致且后台以服务运行；
 主从都配置在[mysqld]结点下，都是小写；
 主机修改my.ini配置文件；
 从机修改my.cnf配置文件；
 主机从机都重启后台mysql服务；
 主机从机都关闭防火墙；---》service iptables stop；
 在主机上建立帐户并授权slave；
 在从机上配置需要复制的主机；
 主机insert从机复制；
 停止从服务复制功能；--》stop slave;

【3】主服务器修改mysql配置文件；

 1 [ 必须] 主服务器唯一 ID
  server-id=1
 2 [ 必须] 启用二进制日志
 log-bin= 自己本地的路径 /mysqlbin
 例如：
 log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin
 3[ 可选] 启用错误日志
 log-err= 自己本地的路径 /mysqlerr
 例如：
 log-err=D:/devSoft/MySQLServer5.5/data/mysqlerr
 4[ 可选] 根目录
 basedir=" 自己本地路径 "
 例如：
 basedir="D:/devSoft/MySQLServer5.5/"
 5[ 可选] 临时目录
 tmpdir=" 自己本地路径 "
 例如：
 tmpdir="D:/devSoft/MySQLServer5.5/"
 6[ 可选] 数据目录
 datadir=" 自己本地路径 /Data/"
 datadir="D:/devSoft/MySQLServer5.5/Data/"
 7
 read-only=0
 8[ 可选] 设置不要复制的数据库
 binlog-ignore-db=mysql
 9[ 可选] 设置需要复制的数据库
 binlog-do-db= 需要复制的主数据库名字
 例如
 binlog-do-db= db01

栗子：

 server_id = 1 #主数据库端ID号
 log-bin = mysql-bin #开启二进制日志
 binlog-do-db = mytest #需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
 auto_increment_offset = 1 #这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
 auto_increment_increment = 1 #这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
 log_bin_trust_function_creators = 1 #将函数复制到slave

【4】从机修改mysql配置文件；

[ 必须] 从服务器唯一 ID

[ 可选] 启用二进制日志

栗子：

 server_id = 2 #从节点的ID号,不能和主节点的一样
 relay_log = relay-log #开启中继日志
 read_only = ON #只读开启,实现读写分离

【7】在主机上建立帐户并授权 slave

 GRANT REPLICATION SLAVE ON *.* TO 'slave'@'从机器数据库IP' IDENTIFIED BY '123456';
 flush privileges;
 show master status;

记录下 File 和Position 的值

执行完此步骤后不要再操作主服务器 MYSQL ，防止主服务器状态值变化

【8】在从机上配置需要复制的主机

栗子：

 CHANGE MASTER TOMASTER_HOST='10.100.100.8',MASTER_USER='slave',MASTER_PASSWORD='slave',MASTER_LOG_FILE='File名字',MASTER_LOG_POS='Position数字';

启动从服务器复制功能

 start slave;
 show slave status\G

下面两个参数都是 Yes ，则说明主从配置成功！

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

索引

概述

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引(hash index)等。

优势

通过索引， 提高数据检索的效率，降低数据库的 IO成本

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

索引分类

单值索引：一个索引只包含单个列，一个表可以有多个单列索引。

唯一索引：索引列的值必须唯一，可以为null。

复合索引：一个索引包含多个列。

mysql还支持前缀索引，全文索引（fullText）。全文索引可用于全文的搜索。

覆盖索引，查询数据在索引列。

创建索引

 CREATE [UNIQUE ] INDEX indexName ON tableName(columnName(length));

【如果是 CHAR，VARCHAR 类型，length可以小于字段实际长度；如果是 BLOB和TEXT 类型，必须指定 length。指定lenght的索引又称前缀索引】

CREATE INDEX PersonIndex ON Person (LastName, FirstName)

修改索引

ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))

删除索引

DROP INDEX [indexName] ON mytable;

查看索引

 SHOW INDEX FROM table_name；

有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);#该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);#这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
ALTER TABLE tbl_name ADD INDEX index_name (column_list);# 添加普通索引，索引值可出现多次
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);#该语句指定了索引为 FULLTEXT ，用于全文索引

创建索引的原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效地使用索引。

• 搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。
• 使用唯一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。例 如，存放出生日期的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“ M”和“F”，则对此列进行索引没有多大用处，因为不管搜索哪个值，都会得出大约一半的行。 ?
• 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，如果有一个 CHAR(200)列，如果在前 10 个或 20 个字符内，多数值是唯一的，那么就不要对整个列进行索引。对前10个或20个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘 IO 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL 也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。 ?
• 利用最左前缀。在创建一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。 ? 不要过度索引。
• 不要以为索引“越多越好”，什么东西都用索引是错误的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。 ?
• 对于 InnoDB 存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的，所以 InnoDB 表尽量自己指定主键，当表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为访问条件的列作为 主键，提高查询的效率。另外，还需要注意，InnoDB 表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存效果。

业务模型挑选合适的数据类型

char 和varchar

• 都可以保存字符串，前者固定长度，需要单独处理空格，后者可变长度，存储长度可变。
• CHAR 是固定长度的，所以它的处理速度比 VARCHAR 快得多，但是其缺点是浪费存储空间，程序需要对行尾空格进行处理，所以对于那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用 CHAR 类型来存储。
• 另外，随着MySQL版本的不断升级，VARCHAR数据类型的性能也在不断改进并提高，所以在许多的应用中，VARCHAR 类型被更多地使用。

blob和text

• 都可以保存较大文本，前者保存2进制，比如图片，后者只能保存字符串数据。BLOB 和 TEXT 值会引起一些性能问题，特别是在执行了大量的删除操作时。删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。可以使用合成的（Synthetic）索引来提高大文本字段（BLOB 或 TEXT）的查询性能。简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。但是，要注意这种技术只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符是没有用处的）。可以使用 MD5()函数生成散列值，也可以使用 SHA1()或 CRC32()，或者使用自己的应用程序逻辑来计算散列值。请记住数值型散列值可以很高效率地存储。同样，如果散列算法生成的字符串带有尾部空格，就不要把它们存储在 CHAR 或 VARCHAR 列中，它们会受到尾部空格去除的影响。合成的散列索引对于那些 BLOB 或 TEXT 数据列特别有用。用散列标识符值查找的速度比搜索BLOB 列本身的速度快很多。
• 模糊查询，%不要放在最前面，否则将导致索引失效。
• 在不必要的时候避免检索text或者blob类型数据。
• 把text或者blob类型数据放到其他表中。

浮点类型和定点类型

• 浮点数一般用于表示含有小数部分的数值。当一个字段被定义为浮点类型后，如果插入数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程不会报错。在 MySQL 中 float、double（或 real）用来表示浮点数。
• 定点数不同于浮点数，定点数实际上是以字符串形式存放的，所以定点数可以更加精确地保存数据。如果实际插入的数值精度大于实际定义的精度，则 MySQL 会进行警告（默认的 SQLMode 下），但是数据按照实际精度四舍五入后插入；如果 SQLMode 是在 TRADITIONAL（传统模式）下，则系统会直接报错，导致数据无法插入。在 MySQL 中，decimal（或 numberic）用来表示定点数。

MySQL日期类型

• 如果应用只需要记录“年份”，那么用 1 个字节来存储的 YEAR 类型完全可以满足，而不需要用 4 个字节来存储的 DATE 类型。这样不仅仅能节约存储，更能够提高表的操作效率。
• 如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用 DATETIME，而不要使用 TIMESTAMP。因为 TIMESTAMP 表示的日期范围比 DATETIME 要短得多。
• 如果记录的日期需要让不同时区的用户使用，那么最好使用TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。

字符集

mysql支持的字符集

• show character set;
• MySQL 的字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念。字符集是用来定义 MySQL 存储字符串的方式，校对规则则是定义了比较字符串的方式。字符集和校对规则是一对多的关系，MySQL 支持 30 多种字符集的 70 多种校对规则。
• 每个字符集至少对应一个校对规则。可以用“SHOW COLLATION LIKE '***';”命令或者查看 information_schema.COLLATIONS,查看相关字符集的校对规则。

MySQL的字符集和校对规则有4个级别的默认设置：服务器级、数据库级、表级和字段级。

服务器字符集和校对，在 MySQL 服务启动的时候确定。

可以在 my.cnf 中设置：

[mysqld]
default-character-set=gbk
或者在启动选项中指定：
mysqld --default-character-set=gbk
或者在编译的时候指定：
./configure --with-charset=gbk

• 查询当前服务器的字符集和校对规则show variables like 'character_set_server';show variables like 'collation_server'

数据库字符集

数据库的字符集和校对规则在创建数据库的时候指定，也可以在创建完数据库后通过“alter database”命令进行修改。需要注意的是，如果数据库里已经存在数据，因为修改字符集并不能将已有的数据按照新的字符集进行存放，所以不能通过修改数据库的字符集直接修改数据的内容。 设置数据库字符集的规则是： 如果指定了字符集和校对规则，则使用指定的字符集和校对规则； 如果指定了字符集没有指定校对规则，则使用指定字符集的默认校对规则； 如果没有指定字符集和校对规则，则使用服务器字符集和校对规则作为数据库的字符集和校对规则。

要显示当前数据库的字符集和校对规则 ， 可以使用命令查询。

• show variables like 'character_set_database';
• show variables like 'collation_database';

表的字符集和校对规

表的字符集和校对规则在创建表的时候指定，可以通过 alter table 命令进行修改，同样，如果表中已有记录，修改字符集对原有的记录并没有影响，不会按照新的字符集进行存放。表的字段仍然使用原来的字符集。 设置表的字符集的规则和上面基本类似： 如果指定了字符集和校对规则，使用指定的字符集和校对规则； 如果指定了字符集没有指定校对规则，使用指定字符集的默认校对规则； 如果没有指定字符集和校对规则，使用数据库字符集和校对规则作为表的字符集和校对规则。 推荐在创建表的时候明确指定字符集和校对规则，避免受到默认值的影响。

列字符集和校对规则

MySQL 可以定义列级别的字符集和校对规则，主要是针对相同的表不同字段需要使用不同的字符集的情况，应该说一般遇到这种情况的几率比较小，这只是 MySQL 提供给我们一个灵活设置的手段。 列字符集和校对规则的定义可以在创建表时指定，或者在修改表时调整，

如果在创建表的时候没有特别指定字符集和校对规则，则默认使用表的字符集和校对规则。

连接字符集和校对规则

对于实际的应用访问来说，还存在客户端和服务器之间交互的字符集和校对规则的设置。对于客户端和服务器的交互操作，MylSQL 提供了 3 个不同的参数：character_set_client、character_set_connection 和 character_set_results，分别代表客户端、连接和返回结果的字符集，通常情况下，这 3 个字符集应该是相同的，才可以确保用户写入的数据可以正确地读出，特别是对于中文字符，不同的写入字符集和返回结果字符集将导致写入的记录不能正确读出。