搜索
知识培训2015年8月20日星期四宋小龙Mysql的架构设计Mysql开发应用Mysql的监控与优化Mysql的特性Mysql的安装及配置目录2020/3/192Mysql的安装及初始化配置1、Mysql的安装及初始化配置2、Mysql特性3、Mysql的监控与优化4、Mysql开发应用5、Mysql的架构设计1.1安装包内容Mysql5.6版本forlinux安装包内容,通常我们选择安装server,client和devel三个安装包。Mysql安装包内容：Mysql-server服务端程序。Mysql-client是连接服务端的客户端工具。在windows下和服务端是一起安装的。Mysql-devellib库文件，用来编译其他的客户端程序，比如包含perl模块。Mysql-embeddedMysql的嵌入式版本。Mysql-shared共享库。Mysql-shared-dompt兼容老版本的共享库。Mysql-test测试组件。11.2安装路径Mysql在源码编译安装时可以自定义安装路径，使用rpm安装时，不能直接指定安装路径。Rpm安装需要root权限，安装后的默认路径如下：数据库目录/var/lib/mysql配置文件/usr/share/mysql相关命令/usr/bin启动脚本/etc/rc.d/init.d参数文件/etc/my.cnf21.3参数文件Mysql按照顺序搜索读取，参数按顺序覆盖。/usr/my.cnf为默认参数文件，可以copy到/etc/my.cnf。参数读取顺序使用下面命令查看mysqld--verbose--help|grep-A1‘Defaultoptions’几个重要的参数：Autocommitsetautocommit={0|1}设定MySQL事务是否自动提交，1表示立即提交，0表示需要显式提交。lower_case_table_namesMysql默认大小写敏感（与操作系统有关，windows系统不区分），0表示敏感，1表示不敏感。字段名称不敏感。Wait_timeout和Interactive_timeout默认28800s。Ttransaction_isolation隔离级别，默认为repeatable-read。建议使用read-committed。31.4初始密码修改安装Mysql数据库后初始root密码保存在文件里。/root/.mysql_secret服务器初始化随机密码的位置Mysql_secret里记录了root的初始化随机密码，更改密码的方式为：SETPASSWORD=PASSWORD('Kecan');或者直接更新数据字典Updateusersetpassword=‘Kecan’;41.5数据库启动方式数据库启动及客户端工具连接。启动命令：可使用如下命令启动MySQL：servicemysqlstart关闭命令：Servicemysqlstop数据库服务端登录：Mysql–uroot–h127.0.0.1–P3306–p51.:6数据库启动方式权限分为认证和授权两部分。Mysql的权限系统围绕着两个概念：1、认证：确定用户是否容许连接数据库服务器。2、授权：确定用户是否有足够的权限执行数据库操作。两个权限相关的表：user和dbuser表在某种程度上是独一无二的，因为它是唯一一个在权限请求的认证和授权阶段都起作用的表，也是唯一一个保存MySQL服务器相关权限的权限表。在认证阶段，它只是负责为用户授权访问MySQL服务器，确定用户每小时的最大连接数和最大并发数；在授权阶段，user确定允许访问服务器的用户是否被赋予了操作数据库的全局权限，确定用户每小时的最大查询数和更新数。db表用于为每个用户针对每个数据库赋予权限。具体的可以查看db的字段。61.7修改Mysql的数据存储路径可以根据实际情况修改存储路径，甚至异地复制创建。Mysql默认安装路径datadir为/var/lib/mysql,为了方便管理，我们需要修改到我们的指定路径下，需要如下步骤：1、修改/etc/sysconfig/selinux文件关闭强制访问控制：#SELINUX=enforcingSELINUX=disabled2、关闭Mysql数据库，servicemysqlstop。3、创建新的目录并授权。比如：mkdir–p/mysqldata/mysqlChown-Rmysql:mysql/mysqldata4、拷贝/var/lib/mysql下所有的文件到新建的目录中5、修改my.cnf参数配置文件，指定路径：Datadir=/mysqldata/mysqlSocket=/mysqldata/mysql/mysql.sock6、重启mysql生效。7Mysql特性1、Mysql的安装及初始化配置2、Mysql特性3、Mysql的监控与优化4、Mysql开发应用5、Mysql的架构设计2.1Mysql特性-Mysql的原理架构图Mysql是由SQL接口，解析器，优化器，缓存，存储引擎组成的。2.2Mysql特性-索引组织表对于理解InnoDB最核心的事情是：凡事都是索引。所有的表都是IOT索引组织表。每个表都有一个主键。如果建表时没有指定，则会使用第1个非空唯一键；如果也没有，那么就会自动在表中生成一个6字节(48bit)的隐含“RowID”字段作为主键。隐含主键对你是无用的但是却浪费了每条记录6字节的空间。行记录数据（非主键字段）存储在主键索引结构中，也被称为“聚集键”。此索引结构是基于主键字段的，行数据就是依附于这个键的值(也包含用于MVCC的一些额外字段——事物号、回滚指针等)。除了主键的聚集索引,其他索引(普通索引)中不会保存行的物理位置,而是保存主键的值,所以通过二级索引进行查找是先找到主键,再找到行,要进行二次索引查找。2.3Mysql特性-主键主键对Mysql来说是非常重要的。如果一个表没有主键及唯一键，InnoDB将自动添加1个6字节整数字段(ROW_ID)到表上，并按这个字段来聚集数据。这个字段不能被任何查询或应用(比如基于行的复制)访问。所有使用ROW_ID字段的表共享一个共同的全局sequence计数器，这个是数据字典的一部分。ROWIDS的最大值被存储在系统表空间(ibdata1)的7号页(typeSYS)，在数据字典头部(DICT_HDR_ROW_ID)。任何使用隐含聚集键的表在删除表等其他不相干的操作时，都可能导致插入停顿。并行插入到多个使用隐含键的表性能会受到明显影响；每256个ROWID值的产生将导致1次SYS页的日志写(flush)，而不管事物是否提交。2.4Mysql特性-隔离级别MysqlInnodb默认的隔离级别是repeatable,这和oracle的readcommited有较大差异。REPEATABLEREAD—默认的MySQL/InnoDB隔离级别。事务启动后会创建一个读视图,并且此读视图会用于事务中的所有语句,实现了语句间的数据库一致性视图。也就是说，在事务中，读取数据是“可重复的”。READCOMMITTED—每条语句使用一个新读视图，基于语句启动时的最大当前提交事物ID。在语句中读取或返回的记录互相之间保持一致性，但是语句和语句之间会看到新数据。另外MySQL/InnoDB也支持额外的一种隔离级别，称为可序列化(SERIALIZABLE),但这种隔离级别比较与REPEATABLEREAD，主要是锁定上的不同，并不是事务可见性。2.5Mysql特性-隔离级别下的锁MysqlInnodb默认的隔离级别是repeatableread,这和oracle的readcommited有较大差异。RC隔离等级下，只有recordlock，不存在gaplock和next-keylock。RR隔离等级下，InnoDB的行锁算法是next-keylock算法，当执行计划走主键索引或者二级索引时，只对条件中的索引记录加next-keylock,当执行计划走全表时，将对全部索引记录加next-keylock,达到类似“表锁”效果。RR隔离等级下，当执行计划走的索引带有唯一属性时（主键或有唯一性非空索引时），next-keylock索引将降级为recordlock，只锁定单条索引记录。RR隔离等级下，更新某条不存在的索引记录时，为了防止产生幻读，仍然会加gaplock。RR隔离等级下，当执行计划走二级索引时，行锁不单只会加在二级索引上，还包括聚集索引。RR隔离等级下，插入操作也会造成死锁，RC隔离等级下不会。2.6Mysql特性-隔离级别下的锁recordlock是加在索引记录上的锁，只对单条索引记录生效。gaplock有两类，一类是在两条索引记录之间的范围，另一类是在最前索引记录之前的范围或者最后一条索引记录之后，如区间（20，40），（无穷小，20）或者（40，无穷大）。因为gaplock是锁定索引记录之间的空隙（范围），所以其只针对插入操作。Next-keylock是结合了gaplock和recordlock的一种锁定算法，会锁定索引记录本身和之前的一段gap，即锁定范围是一个下界开，上界闭的半开半闭区间。InnoDB在默认隔离级别RR下对于行的查询都是采用这种锁定算法。注意，在RC隔离级别下，只有recordlock一种行锁算法。假如一个索引的行有10,11,13,20，那么可能的next-keylock的范围包括：(无穷小,10](10,11](11,13](13,20](20,无穷大)由于InnoDB中的记录都是索引记录,所以InnoDB行锁是通过给索引上的索引项加锁来实现的。当InnoDB搜索或扫描表的索引之时，它对遇到的索引记录设置共享或独占锁定。因此，行级锁定事实上是索引记录锁定。这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用“表锁”（不是真正的表锁，是recordlock+gaplock达到的表锁效果）。2.7Mysql特性-自增列Mysql无独立的sequence对象，一般通过自增列实现序列功能。InnoDB的auto_increment具有自增特性，类似oracle的sequence序列。Auto_increment必须为非空索引列,一旦分配就不能随着相应的sql回滚或回退。当InnoDB表创建auto_increment列时，其数据字典会分配一个auto-increment计数器，在内存中操作维护，第一次向表插入数据时，InnoDB会执行如下语句查看列当前最大值：Selectmax(ai_col)fromsq_tabforupdate。自增列有两个系统参数来控制自增属性（auto_increment_increment和auto_increment_offset），在最大值上增加auto_increment_increment(默认为1)并赋予新插入的ai_col列，若表为空则初始值由auto_increment_offset(默认为1)决定；也可以显式的为ai_col列赋值，当其大于当前计数器值时则重置计数器为此值，若为Null或0则照旧使用计数器。2.8Mysql特性-自增列创建独立的自增列表作为序列基表。CREATETABLEsq_tab2(sq_idint(10)NOTNULLAUTO_INCREMENT,PRIMARYKEY(sq_id))使用如下函数实现nextval的取值。DELIMITER$$CREATEFUNCTIONTEST_CURRVAL()RETURNSINT(11)BEGINDECLARECURRENT_VALUEINT;SETCURRENT_VALUE=(SELECTIDFROMSQ_TAB1ORDERBYIDDESCLIMIT1);RETURNCURRENT_VALUE;END$$DELIMITER