MySQL数据库简介

一.MySQL数据库简介

1.MySQL数据库是一款免费、高效、灵活的易用的RDBMS。The world's most popular open source database。

2.全球有超过1500万台运行着MySQL的服务；全球十大Web站点中有九个使用MySQL；全球十大ISV（ISV意为“独立软件开发商”，特指专门从事软件的开发、生产、销售和服务的企业，如微软、甲骨文等）中有八个使用MySQL；最大的云端数据库；80%的Hadoop大数据平台与MySQL集成。

3.MySQL主流分支有：Oracle官方版本、Percona 、MariaDB、Drizzle等。

4.MySQL结构层次

5.MySQL访问路径

　　当客户端链接上mysql服务端时，系统为其分配一个链接描述符thd，用以描述客户端的所有信息，将作为参数在各个模块之间传递。一个典型的客户端查询在MySQL的主要模块之间的调用关系如图所示。

6.存储引擎机制

　　MySQL提供一个抽象层，允许不同的存储应请使用相同的API对表进行访问。该接口通过一个被称为Handler的抽象类来实现，该处理器提供了一些可实现基本操作的方法，入打开和关闭表，连续扫描记录，按照键值检索记录，存储记录以及删除记录。

　　每个存储引擎都执行处理器的一个子类以实现接口方法，以便将处理器操作转化为特定存储引擎的此层次存储/检索API调用

　　MySQL的主要存储引擎有：MyISAM(非事务引擎)/InnoDB(事务引擎)/Archive(归档引擎)/Memory(内存引擎)/NDB(集群引擎)/memcache(内存)/TukoDB(事务)

　　其他特殊引擎：Infobirght(数据仓库引擎)

 

二.MySQL高级特性

1.存储过程

　　官方文档：http://dev.mysql.com/doc/refman/5.1/zh/stored-procedures.html

　　变量定义顺序必须是：存储函数变量、游标定义、游标异常、程序主体

　　定义务必加上授权： CREATE DEFINER=‘root’@‘localhost’ PROCEDURE sp();

　　存储过程相关权限：被操作表的相关权限及EXECUTE(执行权限)，ALTER ROUTINE(修改权限)，CREATE ROUTINE(创建权限)。

　　不能使用动态游标， CURSOR中不能有动态的表名。

　　查看创建存储过程/函数的语句：SHOW CREATE PROCEDURE/FUNCTION ps;

　　查看所有存储过程/函数：SHOW PROCEDURE/FUNCTION STATUS [LIKE ps];

　　调用存储过程：CALL sp();

　　调用存储函数：SELECT sp();

 

2.触发器

        官方文档：http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html

        定义务必加上授权： CREATE DEFINER=`root`@`localhost` Trigger tgr();

        定义语句：CREATE DEFINER=`root`@`localhost` Trigger tgr()

                          AFTER/BEFORE  INSERT/UPDATE/DELETE ON table FOR EACH ROW;

        数据调用：NEW.*（更新后数据） OLD.*（更新前数据）

        行级触发器，每一行都会触发动作

        内部可以调用存储过程和函数

        每种类型的Trigger在一张表上只能建立一个

 

3.分区表

        官方文档：http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html

        分区类型：

        a.RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

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

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

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

        e.子分区：子分区是分区表中每个分区的再次分割。

 

三.SQL最佳实践

        1.尽量将子查询转化为链接查询，除非子查询只返回极少的记录。

        2.不要在条件里写不必要的条件。

        3.没有必要排序的分组采用ORDER BY null指定不排序。

        4.OR条件若不能正确的走索引，则拆分为两条SQL。

        5.不在乎重复的情况下，UNION ALL可以避免UNION要做的排序。

        6.没必要的时候不要做外连接，内连接效率比外连接高。

 

四.索引优化

        1.MySQL只在认为走索引可以筛去85%以上数据的时候，才使用索引，如果达不到这个标准，索引是无意义的。

        2.前缀索引：MySQL可以利用索引的前向部分，但不可以利用后向部分，不支持反向索引。例如：SELECT col2 FROM table WHERE col1=1;可以使用索引(col1,col2)，但不可以使用(col2,col1)。

        3.不等比较的优化：如果索引的前向部分在WHERE中是等于，那么可以使用索引，如果索引的前向部分在WHERE中是不等比较，那么不可以为后面的等于比较使用索引。例如：SELECT col1 FROM table WHERE col2=1 AND col3 >10;可以完全使用索引(col2,col3)，但不可以使用(col3,col2)的前缀。

        4.排序的优化：可以在条件是等于的时候继续使用索引排序，或者条件中的不等于字段就是排序字段。例如：SELECT col1 FROM table WHERE col2=1 AND col3>0 ORDER BY col3，可以完全使用索引(col2,col3)避免排序。但条件是col2<1 AND col3=0 ORDER BY col3时则只能使用col2排除记录，不可以用来排序。GROUP BY/DISTINCT也是以排序为基础，优化同上。

 

五.MySQL的高可用

1.MySQL复制技术

        MySQL Replication主要通过将binlog传输到从库中并进行apply，主要的线程为IO_thread和SQL_thread。

2.级联复制

3.MySQL半同步

4.复制过滤

5.常见的MySQL复制架构

基于MySQL Replication，结合其他技术实现高，如：Lvs、Keepalived、 HeartBeat、 Mysql Porxy、HAproxy、DBproxy、DRBD、MMM/MHA 等，主要就是实现负载均衡和故障转移

a.一主一从

b.一主多从

c.级联复制

d.双主复制

e.

        注意：红色线标识需要DBA去修复的同步,红色边缘的机器是需要DBA去处理的机器。

        对于主库Crash，Standy接管较快，不用crash恢复的过程　

        对于Slave在发生切换后，可以正常的同步

        数据从一份变成了两份

6.目前常见的高可用技术架构

        a.基于Master/Slave构建的主从

        b.基于MMM/MHA构建的多主自动切换的主从

        c.基于LVS/Haproxy，Percona-Cluster/MariaDB　Galera Cluster，Master/Slave构建的主从

        d.基于域名系统加Master/Slave, Percona-Cluster/MariaDB　Galera Cluster构建的数据库托管系统

        e.基于Proxy系统构建的代理拆分系统

        f.基于DRBD＋Keepalive构建的集群

        g.基于共享存储构建的集群

        h.官方的NDBCluster

 

六.数据0丢失

1.方案一

        INNODB_FLUSH_LOG_AT_TRX_COMMIT=1

        sync_binlog=1 innodb_flush_method=O_DIRECT + 

        半同步

2.方案二

        说明：两台master上的my.cnf配置一样，存储被活跃的节点挂载

        Standy机器不挂载磁盘，启动MySQL事例。在这个结构遇到最严重问题：主库crash了，Standy接管后，也需要很长时间的恢复过程。如果利用Percona-Server或是innodb-plugin，这个也算是一个好的架构。