Mysql基础知识培训

1、整理课件知识培训2015年8月20日星期四宋小龙2021-12-12整理课件2目录整理课件Mysql 的安装及初始化配置整理课件1.1 安装包内容Mysql5.6版本for linux安装包内容,通常我们选择安装server,client和devel三个安装包。p Mysql安装包内容：安装包内容：Mysql-server 服务端程序。Mysql-client 是连接服务端的客户端工具。 在windows下和服务端是一起安装的。Mysql-devel lib库文件，用来编译其他的客户端程序，比如包含perl模块。Mysql-embedded Mysql的嵌入式版本。Mysql-shared 共

2、享库。Mysql-shared-dompt 兼容老版本的共享库。Mysql-test 测试组件。1整理课件1.2 安装路径Mysql在源码编译安装时可以自定义安装路径，使用rpm安装时，不能直接指定安装路径。p Rpm安装需要安装需要root权限，安装后的默认路径如下：权限，安装后的默认路径如下：数据库目录 /var/lib/mysql配置文件 /usr/share/mysql相关命令 /usr/bin启动脚本 /etc/rc.d/init.d参数文件 /etc/f2整理课件1.3 参数文件 Mysql按照顺序搜索读取，参数按顺序覆盖。/usr/f 为默认参数文件，可以copy到/etc/f。

3、参数读取顺序使用下面命令查看mysqld -verbose -help | grep -A 1 Default options几个重要的参数：几个重要的参数：Autocommit set autocommit=0|1设定MySQL事务是否自动提交，1表示立即提交，0表示需要显式提交。lower_case_table_namesMysql默认大小写敏感（与操作系统有关，windows系统不区分），0表示敏感，1表示不敏感。字段名称不敏感。Wait_timeout和和Interactive_timeout 默认28800s。Ttransaction_isolation隔离级别，默认为repeata

4、ble-read。建议使用read-committed。3整理课件1.4 初始密码修改 安装Mysql数据库后初始root密码保存在文件里。/root/.mysql_secret服务器初始化随机密码的位置Mysql_secret里记录了里记录了root的初始化随机密码，更改密码的方式为：的初始化随机密码，更改密码的方式为：SET PASSWORD = PASSWORD(Kecan); 或者直接更新数据字典Update user set password=Kecan;4整理课件1.5 数据库启动方式 数据库启动及客户端工具连接。启动命令：可使用如下命令启动MySQL： service mysql

5、 start关闭命令：Service mysql stop数据库服务端登录：Mysql uroot h P3306 p5整理课件1.:6 数据库启动方式 权限分为认证和授权两部分。Mysql的权限系统围绕着两个概念：1、认证：确定用户是否容许连接数据库服务器。2、授权：确定用户是否有足够的权限执行数据库操作。两个权限相关的表：user 和 dbuser表在某种程度上是独一无二的，因为它是唯一一个在权限请求的认证和授权阶段都起作用的表，也是唯一一个保存MySQL服务器相关权限的权限表。在认证阶段，它只是负责为用户授权访问MySQL服务器，确定用户每小时的最大连接数和最大并发数；

6、在授权阶段，user确定允许访问服务器的用户是否被赋予了操作数据库的全局权限，确定用户每小时的最大查询数和更新数。 db表用于为每个用户针对每个数据库赋予权限。具体的可以查看db的字段。6整理课件1.7 修改Mysql的数据存储路径 可以根据实际情况修改存储路径，甚至异地复制创建。Mysql默认安装路径datadir为/var/lib/mysql,为了方便管理，我们需要修改到我们的指定路径下，需要如下步骤：1、修改/etc/sysconfig/selinux文件关闭强制访问控制：#SELINUX=enforcingSELINUX=disabled2、关闭Mysql数据库，service mys

7、ql stop。3、创建新的目录并授权。比如：mkdir p /mysqldata/mysqlChown -R mysql:mysql /mysqldata 4、拷贝/var/lib/mysql下所有的文件到新建的目录中5、修改f参数配置文件，指定路径：Datadir=/mysqldata/mysqlSocket=/mysqldata/mysql/mysql.sock6、重启mysql生效。7整理课件Mysql特性整理课件2.1 Mysql特性-Mysql的原理架构图Mysql是由SQL接口，解析器，优化器，缓存，存储引擎组成的。整理课件2.2 Mysql特性-索引组织表对于理解InnoDB最

8、核心的事情是：凡事都是索引。所有的表都是IOT索引组织表。p 每个表都有每个表都有一一个主键。如果建表时没有指定，个主键。如果建表时没有指定，则会使用则会使用第第1个非空唯一键；如果也没个非空唯一键；如果也没有，那么就会自动在表中有，那么就会自动在表中生成一生成一个个6字节字节(48bit)的隐含的隐含“Row ID”字段作为主键。字段作为主键。隐含主键对你是无用的但是却浪费了每条记录隐含主键对你是无用的但是却浪费了每条记录6字节的空间字节的空间。p 行记录数据（非主键字段）存储在主键索引结构中，也被称为行记录数据（非主键字段）存储在主键索引结构中，也被称为“聚集键聚集键”。此索引结。此索引结

9、构是基于主键字段的，行数据就是依附于这个键的值构是基于主键字段的，行数据就是依附于这个键的值 (也包含用于也包含用于MVCC的一些额外的一些额外字段字段事物号、回滚指针等事物号、回滚指针等)。p 除了主键的聚集索引除了主键的聚集索引,其他索引其他索引(普通索引普通索引)中不会保存行的物理位置中不会保存行的物理位置,而是保存主键的而是保存主键的值值,所以通过所以通过二级索引二级索引进行查找是先找到主键进行查找是先找到主键,再找到行再找到行,要进行二次索引查找。要进行二次索引查找。整理课件2.3 Mysql特性-主键主键对Mysql来说是非常重要的。p 如果一个表没有主键及唯一键，如果一个表没有主

10、键及唯一键，InnoDB将自动添加将自动添加1个个6字节整数字段字节整数字段(ROW_ID)到到表上，并按这个字段来聚集数据。这个字段不能被任何查询或应用表上，并按这个字段来聚集数据。这个字段不能被任何查询或应用(比如基于行的复比如基于行的复制制)访问。所有使用访问。所有使用ROW_ID字段的表共享一个共同的全局字段的表共享一个共同的全局sequence计数器，这个是计数器，这个是数据字典的一部分。数据字典的一部分。ROW IDS的最大值被存储在系统表空间的最大值被存储在系统表空间(ibdata1)的的7号页号页(type SYS)，在数据字典头部，在数据字典头部(DICT_HDR_ROW_I

11、D)。p 任何使用隐含聚集键的表在删除表等其他不相干的操作任何使用隐含聚集键的表在删除表等其他不相干的操作时时，都可能导致插入停顿。，都可能导致插入停顿。p 并行插入到多个使用隐含键的表性能会受到明显影响并行插入到多个使用隐含键的表性能会受到明显影响；p 每每256个个ROWID值的产生将导致值的产生将导致1次次SYS页的日志写页的日志写(flush)，而不管事物是否提交。，而不管事物是否提交。整理课件2.4 Mysql特性-隔离级别Mysql Innodb默认的隔离级别是默认的隔离级别是repeat able,这和这和oracle的的read commited有较大有较大差异。差异。p RE

12、PEATABLE READ 默认的默认的MySQL/InnoDB隔离级别。事务启动后会创建一个隔离级别。事务启动后会创建一个读视图读视图,并且此读视图会用于事务中的所有语句并且此读视图会用于事务中的所有语句,实现了语句间的数据库一致性视图。实现了语句间的数据库一致性视图。也就是说，在事务中，读取数据是也就是说，在事务中，读取数据是“可重复的可重复的”。p READ COMMITTED 每条语句使用一个新读视图，基于语句启动时的最大当前提每条语句使用一个新读视图，基于语句启动时的最大当前提交事物交事物ID。在语句中读取或返回的记录互相之间保持一致性，但是语句和语句之间会。在语句中读取或返回的记录

13、互相之间保持一致性，但是语句和语句之间会看到新数据。看到新数据。p 另外另外MySQL/InnoDB也支持额外的一种隔离级别，称为可序列化也支持额外的一种隔离级别，称为可序列化(SERIALIZABLE), 但这种隔离级别比较与但这种隔离级别比较与REPEATABLE READ，主要是锁定上的不同，并不是事务可，主要是锁定上的不同，并不是事务可见性见性。整理课件2.5 Mysql特性-隔离级别下的锁Mysql Innodb默认的隔离级别是默认的隔离级别是repeatable read,这和这和oracle的的read commited有有较大差异。较大差异。p RC隔离等级下，只有隔离等级下，

14、只有record lock，不存在，不存在gap lock和和 next-key lock。p RR隔离等级下，隔离等级下，InnoDB的行锁算法是的行锁算法是next-key lock算法，当执行计划走主键索引算法，当执行计划走主键索引或者二级索引时，只对条件中的索引记录加或者二级索引时，只对条件中的索引记录加next-key lock,当执行计划走全表时，将当执行计划走全表时，将对全部索引记录加对全部索引记录加next-key lock,达到类似达到类似“表锁表锁”效果。效果。p RR隔离等级下，当执行计划走的索引带有唯一属性时（主键或有唯一性非空索引隔离等级下，当执行计划走的索引带有唯一

15、属性时（主键或有唯一性非空索引时），时），next-key lock索引将降级为索引将降级为record lock，只锁定单条索引记录。，只锁定单条索引记录。p RR隔离等级下，更新某条不存在的索引记录时，为了防止产生幻读，仍然会加隔离等级下，更新某条不存在的索引记录时，为了防止产生幻读，仍然会加gap lock。p RR隔离等级下，当执行计划走二级索引时，行锁不单只会加在二级索引上，还包括隔离等级下，当执行计划走二级索引时，行锁不单只会加在二级索引上，还包括聚集索引。聚集索引。p RR隔离等级下，插入操作也会造成死锁，隔离等级下，插入操作也会造成死锁，RC隔离等级下不会。隔离等级下不会。整理

16、课件2.6 Mysql特性-隔离级别下的锁p record lock 是加在索引记录上的锁，只对单条索引记录生效。是加在索引记录上的锁，只对单条索引记录生效。p gap lock有两类，一类是在两条索引记录之间的范围，另一类是在最前索引记录之前的范围或者有两类，一类是在两条索引记录之间的范围，另一类是在最前索引记录之前的范围或者最后一条索引记录之后，如区间（最后一条索引记录之后，如区间（20，40） ，（无穷小，（无穷小，20）或者（）或者（40，无穷大）。因为，无穷大）。因为gap lock 是锁定索引记录之间的空隙（范围），所以其只针对插入操作。是锁定索引记录之间的空隙（范围），所以其只针

17、对插入操作。p Next-key lock 是结合了是结合了gap lock 和和 record lock的一种锁定算法，会锁定索引记录本身和之前的一种锁定算法，会锁定索引记录本身和之前的一段的一段gap，即锁定范围是一个下界开，上界闭的半开半闭区间。，即锁定范围是一个下界开，上界闭的半开半闭区间。InnoDB在默认隔离级别在默认隔离级别RR下下对于行的查询都是采用这种锁定算法。注意，在对于行的查询都是采用这种锁定算法。注意，在RC隔离级别下，只有隔离级别下，只有record lock一种行锁算法。一种行锁算法。p 假如一个索引的行有假如一个索引的行有10,11,13,20，那么可能的，那么可

18、能的next-key lock的范围包括：的范围包括：(无穷小无穷小, 10 (10,11 (11,13 (13,20 (20, 无穷大无穷大)p 由于由于InnoDB中的记录都是索引记录中的记录都是索引记录,所以所以InnoDB行锁是通过给索引上的索引项加锁来实现的。行锁是通过给索引上的索引项加锁来实现的。当当InnoDB搜索或扫描表的索引之时，它对遇到的索引记录设置共享或独占锁定。因此，行级锁搜索或扫描表的索引之时，它对遇到的索引记录设置共享或独占锁定。因此，行级锁定事实上是索引记录锁定。这一点定事实上是索引记录锁定。这一点MySQL与与Oracle不同，后者是通过在数据块中对相应数据行不

19、同，后者是通过在数据块中对相应数据行加锁来实现的。加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用才使用行级锁，否则，行级锁，否则，InnoDB将使用将使用“表锁表锁”（不是真正的表锁，是（不是真正的表锁，是record lock +gap lock 达到的达到的表锁效果）。表锁效果）。 整理课件2.7 Mysql特性-自增列Mysql 无独立的无独立的sequence对象，一般通过自增列实现序列功能。对象，一般通过自增列实现序列功能。p InnoDB的的auto_increment具有自增特性，类

20、似具有自增特性，类似oracle的的sequence序列。序列。p Auto_increment必须为非空索引列必须为非空索引列,一旦分配就不能随着相应的一旦分配就不能随着相应的sql回滚或回退。回滚或回退。p 当当InnoDB表创建表创建auto_increment列时，其数据字典会分配一个列时，其数据字典会分配一个auto-increment计计数器，在内存中操作维护，第一次向表插入数据时，数器，在内存中操作维护，第一次向表插入数据时，InnoDB会执行如下语句查看列会执行如下语句查看列当前最大值：当前最大值：Select max(ai_col) from sq_tab for updat

21、e。自增列有两个系统参。自增列有两个系统参数来控制自增属性（数来控制自增属性（auto_increment_increment和和auto_increment_offset），），在最大值上增加在最大值上增加auto_increment_increment(默认为默认为1)并赋予新插入的并赋予新插入的ai_col列，列，若表为空则初始值由若表为空则初始值由auto_increment_offset(默认为默认为1)决定；也可以显式的为决定；也可以显式的为ai_col列赋值，当其大于当前计数器值时则重置计数器为此值，若为列赋值，当其大于当前计数器值时则重置计数器为此值，若为Null或或0则照旧使

22、用计则照旧使用计数器。数器。整理课件2.8 Mysql特性-自增列p 创建创建独立的自增列表作为序列基表。独立的自增列表作为序列基表。CREATE TABLE sq_tab2 ( sq_id int(10) NOT NULL AUTO_INCREMENT, PRIMARY KEY (sq_id) )p 使用如下函数实现使用如下函数实现nextval的取值。的取值。DELIMITER $CREATE FUNCTION TEST_CURRVAL () RETURNS INT(11)BEGIN DECLARE CURRENT_VALUE INT; SET CURRENT_VALUE=(SELECT

23、ID FROM SQ_TAB1 ORDER BY ID DESC LIMIT 1); RETURN CURRENT_VALUE; END$DELIMITER ;整理课件2.9 Mysql特性-临时表p Mysql临时临时表是指使用表是指使用create temprary table创建的临时创建的临时表，临时表，临时表可以使用任何表可以使用任何存储引擎存储引擎,临时表只在单个连接中可见临时表只在单个连接中可见,当连接断开时当连接断开时,临时表也会临时表也会消失。消失。MySQL最初最初会将临时表创建在内存中会将临时表创建在内存中,当数据变的太大后当数据变的太大后,就会转储到磁盘就会转储到磁盘上

24、。上。tmp_table_size 参参数限定单个临时表在内存中的大小，超过将转到磁盘上数限定单个临时表在内存中的大小，超过将转到磁盘上。 1. 不同会话创建的表的名字可以一样。 2. 表结构和数据都放在内存中，磁盘上不创建frm文件。 3. 会话消失表结构和数据都消失。 4. 可以创建索引,删除索引。 5. 主库创建的表，备库查不到。 6. show tables 看不到表。 7.在同一个查询中只能查找一次临时表。整理课件2.10 Mysql特性-内存表p Mysql内存表是使用内存表是使用memory存储引擎的表，表结构定义保存在磁盘中，数据保存在存储引擎的表，表结构定义保存在磁盘中，数据

25、保存在内存中。内存表可以限定表的大小，内存中。内存表可以限定表的大小，使用参数使用参数max_heap_table_size，但不会转存，但不会转存到磁盘上到磁盘上。Delete操作不会回收内存，只有操作不会回收内存，只有drop才能回收内存。才能回收内存。 1. 不同session，创建表的名字不能一样。2. 一个session创建会话后，对其他session也是可见的。3. 表结构放在磁盘上，数据放在内存中。4. mysql 重启或者关闭后内存表里的数据会丢失，但是表结构仍然存在。5. 可以创建索引,删除索引,支持唯一索引。6. 不影响主备，主库上插入的数据，备库也可以查到。7. show

26、 tables 看得到表。整理课件Mysql的监控与优化整理课件3.1 Mysql的日志文件p 对对mysql进行性能监控优化，首先我们要了解进行性能监控优化，首先我们要了解mysql的各种日志。的各种日志。Mysql的日志有的日志有5种类型，分种类型，分别是：别是：error log,general query log,binary log,relay log和和slow query log。在默认情况下，。在默认情况下，mysql不会启动任何不会启动任何log类型的类型的log。p 所有日志默认都会存放在统一路径下（所有日志默认都会存放在统一路径下（/var/lib/mysql）,可以通过

27、可以通过flush log命令强制关闭日志命令强制关闭日志服务后重新开启日志。当执行服务后重新开启日志。当执行flushlog命令时，会刷新日志，执行命令时，会刷新日志，执行mysqladmin命令也可以达到命令也可以达到同样的效果。对于同样的效果。对于binary log，当日志大小达到，当日志大小达到max_binlog_size时，会自动触发对时，会自动触发对binlog的的flash操作。操作。p 在在5种类型日志中，只有种类型日志中，只有general query log和和slow query log可以在线动态启用和关闭。并能可以在线动态启用和关闭。并能修改日志文件名称路径，以及

28、可以修改日志的存放模式（日志文件和数据表）。修改日志文件名称路径，以及可以修改日志的存放模式（日志文件和数据表）。Mysql对对generl log和和slow log的控制比较灵活，可以根据需要设定日志存放模式，也可以两种模式同时使用，的控制比较灵活，可以根据需要设定日志存放模式，也可以两种模式同时使用，但是日志以数据表的方式进行存储时（分别对应但是日志以数据表的方式进行存储时（分别对应mysql.general_log和和mysql.slow.log），会对），会对性能有影响，建议采用日志文件的形式进行保存日志。性能有影响，建议采用日志文件的形式进行保存日志。整理课件3.2 慢日志p 参数

29、参数slow_launch_time的值代表着捕获所有执行时间超过的值代表着捕获所有执行时间超过2秒的查询秒的查询,slow log可以记录没有使可以记录没有使用索引的查询，也能记录执行速度比较慢的管理命令用索引的查询，也能记录执行速度比较慢的管理命令。p 开启开启log_queries_not_using_indexes，将会记录没有使用索引的查询到，将会记录没有使用索引的查询到slow日志里日志里。p 可以使用可以使用MySQL自带的自带的mysqldumpslow工具来对慢日志进行简单分析。参数说明如下：工具来对慢日志进行简单分析。参数说明如下：-s：排序方式。c , t , l , r

30、 表示记录次数、时间、查询时间的多少、返回的记录数排序，ac , at , al , ar 表示相应的倒叙；-t：返回前面多少条的数据；-g：参数后面可以模糊查询相应字符串的语句，用双引号进行分隔，字符串内容大小写不敏感；例如： #slow记录最多的10个语句。 mysqldumpslow -s r -t 10 /slowquery.log 慢日志（slow log）是我们平时进行性能监控的最重要的日志整理课件3.2 常用监控数据字典表p Select * from information_cesslist; 类似oracle的v$session，表中记录了当前所有连接的数

31、据库线程，包含连接时长、状态、sql语句等信息。也可使用show processlist命令查看。p showstatuslikeSlow_queries;超过慢日志设置时长的记录数。p showstatuslikeSelect_full_join;没有主键的join操作记录数。p Select * from information_schema.innodb_trx;记录当前innodb存储引擎下正在运行的事物。可以通过trx_mysql_thread_id与information_cesslist表的id相关联。p Information_schema.innodb_l

32、ocks和和information_schema.innodb_lock_waits记录innodb的锁信息p Show engine innodb status 命令会输出innodb 内部性能相关的计数器、统计、事务处理信息等。整理课件3.3 Mysql优化-表的设计p 字段尽量设置字段尽量设置not null，mysql的的null值和空字符串不一样，值和空字符串不一样，null值占用空间，空字值占用空间，空字符串不占用空间。符串不占用空间。Null值占用空间，比较时会参与比较，但值占用空间，比较时会参与比较，但b树索引不会存储树索引不会存储null值，值，因此当索引的字段包含因此当索引

33、的字段包含null值，效率会下降。值，效率会下降。p 一般没有业务因素影响，采用数值型的自增长列作为主键。一般没有业务因素影响，采用数值型的自增长列作为主键。p Mysql的字段设计需要集约化，越小的占用存储空间越好，能数值不字符，能日期不的字段设计需要集约化，越小的占用存储空间越好，能数值不字符，能日期不字符。字符。p 注意注意Mysql的的varchar类型要计算好长度，因为类型要计算好长度，因为mysql在表被查询后会把表信息缓存在表被查询后会把表信息缓存到内存中，在内存中申请是按照最大长度申请。到内存中，在内存中申请是按照最大长度申请。p 将不常用的字段或大字段从主表拆分出来放到子表，

34、通过主键关联访问；对于核心访将不常用的字段或大字段从主表拆分出来放到子表，通过主键关联访问；对于核心访问频度极高的表，需要反范式设计，增加冗余字段，减少与其它表的问频度极高的表，需要反范式设计，增加冗余字段，减少与其它表的join操作。操作。整理课件3.4 Mysql优化-索引的设计p 主键字段尽量短，数值型是较好的选择。主键字段尽量短，数值型是较好的选择。Mysql innodb单列索引默认情况下限制长度单列索引默认情况下限制长度767，使，使用用utf8字符集，长度限制为字符集，长度限制为255。p 当查询包含的条件和字段很少时，可以创建覆盖索引。这样数据可以全部在索引中获得，将不再当查询

35、包含的条件和字段很少时，可以创建覆盖索引。这样数据可以全部在索引中获得，将不再回表。回表。p 当需要在长文本字段上创建索引时，可以考虑增加一个虚拟当需要在长文本字段上创建索引时，可以考虑增加一个虚拟hash列，采用列，采用crc32计算一个计算一个hash值，值，针对这个列创建索引。只用这个索引做等值查询针对这个列创建索引。只用这个索引做等值查询，索引的空间占索引的空间占会会小很多。小很多。p 组合索引在多列上创建，单列索引在一个列上创建。组合索引在多列上创建，单列索引在一个列上创建。查询使用索引的条件不同一般组合索引需要按照查询使用索引的条件不同一般组合索引需要按照“最左前缀最左前缀”来执行

36、查询，并不是每个列都需要来执行查询，并不是每个列都需要覆盖，只是从左边的列开始组合。覆盖，只是从左边的列开始组合。 例如有索引key(a,b,c) where a=xx and b=xx and c=xxx 此语句可以用到索引 where b=xx and a=xx and c=xxx 同上，顺序没有关系，同样能用到索引 where a=xx and b=xx 可以用到索引 where a=xx and c=xx 可以用到索引 where b=xx and c=xx 用不到索引 where b=xx 用不到索引 where c=xx 用不到索引整理课件3.5 Mysql的ddl操作p 在在5.

37、5版本以前的版本以前的Mysql在做表的在做表的ddl操作时，与操作时，与oracle只是维护数据字典不同，由于是索引组织只是维护数据字典不同，由于是索引组织表，是通过中间表来实现表，是通过中间表来实现ddl操作。大致操作流程如下：操作。大致操作流程如下：A.对表加S共享锁(表此时只读)B.复制原表物理结构C.修改表的物理结构D.把原表数据导入中间表中，数据同步完后，锁定中间表，并删除原表E.rename中间表为原表F.刷新数据字典，并释放锁通过上面的过程可以了解到，表的数据量越大，这个ddl维护过程越耗时间（锁表时间越长）。p MySQL 5.6系列在系列在DDL操作上做了新的更改，由原来的

38、新建临时表，再把数据插入，再操作上做了新的更改，由原来的新建临时表，再把数据插入，再rename表的方式改变成直接在原表表的方式改变成直接在原表alter，最后时刻加锁，加快执行时间，不影响其他，最后时刻加锁，加快执行时间，不影响其他update，delete，select，insert 操作。主要受操作。主要受old_alter_table控制，默认情况下参数为控制，默认情况下参数为OFF。整理课件Mysql的开发应用整理课件4.1 Mysql程序开发Mysql存储过程的应用p Mysql的视图效率很差，特别是多表关联子查询很容易不走索引。的视图效率很差，特别是多表关联子查询很容易不走索引。

39、 Mysql的存储过程和oracle不同，过程的语句可以只是查询语句，执行后能返回查询的结果，我们可以通过存储过程来间接实现视图的功能。下面我们实现一个简单的过程。DELIMITER $CREATE PROCEDURE sayhello()BEGIN SELECT say hello!;END$DELIMITER ;CALL sayhello()将会返回将会返回 select say hello!的结果。的结果。这样，我们将可以利用存储过程的优势，来优化我们的这样，我们将可以利用存储过程的优势，来优化我们的sql语句，核心就是语句，核心就是“数据路由数据路由”。整理课件4.3 Mysql程序开

40、发Mysql存储过程的应用p 存储过程可以动态执行存储过程可以动态执行ddl语句，创建临时表，并创建索引。我们可以把中间结果放语句，创建临时表，并创建索引。我们可以把中间结果放入临时表，减少入临时表，减少sql语句的复杂度，避免多表的关联，实现简单语句的复杂度，避免多表的关联，实现简单sql。并通过。并通过if判断条判断条件进行数据路由，分情况进行语句查询。不再需要用一条复杂件进行数据路由，分情况进行语句查询。不再需要用一条复杂sql语句实现查询需求。语句实现查询需求。比如brt问题管理系统的待办问题页面，原来的后台语句是一个根据登录权限不同，使用多个查询union结果的复杂sql语句。根据实

41、际需求，拆分成若干个简单sql。整理课件Mysql的架构设计整理课件5.1 Mysql的分布式存储技术读写分离，是构建基于MySQL的大规模、高性能应用的基础。通过为服务器配置一个或多个备库的方式来进行数据同步。读写分离技术将write和read请求分散在不同的数据库服务器上进行处理，降低单台数据库服务器的处理压力，从而提升整体数据库的并发负载能力。一般由master服务器处理数据的增、删、改请求，slave服务器负责数据的查询请求。主从复制分布式管理采用不同的切分维度，对数据进行分片，将数据存储到不同的数据库或者或同一数据库的不同表上，而缓解单一数据库的性能问题。采用心跳检查机制实时检查服务

42、器状态，实现宕机自动下线、上线，提升系统的稳定性与可用性。分库分表整理课件5.1.1 主从复制技术整体上来说，复制有3个步骤： (1) master将数据的变更记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）； (2) slave将master的binary log events拷贝到它的中继日志(relay log)； (3) slave重做中继日志中的事件，将改变反映到它自己的数据，完成主从数据同步。整理课件5.1.2 Mysql的读写分离及分布式管理通过主从复制功能实现了数据库系统的高可用性和数据安全。并可以通过中间层代理软件（

43、atlas）实现读写分离来达到部分的性能提升。两个Master节点，其中一个设置为ReadOnly,并在该节点上挂一个或多个Slave.MySQL本身没有提供replication failover的解决方案，需要通过代理层软件实现。代理软件通过心跳检查机制，对数据库的状态进行检查，对宕机的服务器自动上下线，从而提升系统的稳定性和可用性。ReplWrite/ReadReplReplWrite/ReadReadRead整理课件5.1.3 Mysql主从复制常用架构在实际应用场景中，MySQL复制80%以上都是一个Master复制到一个或者多个Slave的架构模式，用于解决读压力比较大的数据库，是

44、一种廉价扩展解决方案。适合主从服务器压力较小，对从库的数据读取不需要严格准确的业务应用。Master Slaves 架构架构1.Master 传输日志传输日志(binlog)压力较大，压力较大，slave容易出现延迟。容易出现延迟。2.Master 崩溃后，需要从多个崩溃后，需要从多个slave中选出一个中选出一个Master。整理课件5.1.4 Mysql主从复制常用架构双Master就是两个MySQL Server互相将对方作为自己的Master，自己作为对方的Slave来进行复制。这样，任何一方所做的变更，都会通过复制应用到另外一方的数据库中。在MySQL的BinaryLog中记录了当前MySQL的server-id，这个参数在搭建MySQLReplication的时候必须明确指定，而且Master和Slave的server-id参数值必须要不同，才能使MySQLReplication搭建成功。一旦有了server-id的值之后，MySQL就很容易判断某个变更是从哪一个MySQLServer最初产生的，从而避免出现循环复制的情况。如果我们不打开记录Slave的BinaryLog的选项（-log-slave-update），MySQL根本就不会记录复制过程中的变更到Bi