Mysql基础知识培训

知识培训 2015年8月20日星期四 宋小龙 2020 4 23 2 目录 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测试组件 1 1 2安装路径 Mysql在源码编译安装时可以自定义安装路径 使用rpm安装时 不能直接指定安装路径 Rpm安装需要root权限 安装后的默认路径如下 数据库目录 var lib mysql配置文件 usr share mysql相关命令 usr bin启动脚本 etc rc d init d参数文件 etc f 2 1 3参数文件 Mysql按照顺序搜索读取 参数按顺序覆盖 usr f为默认参数文件 可以copy到 etc f 参数读取顺序使用下面命令查看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 3 1 4初始密码修改 安装Mysql数据库后初始root密码保存在文件里 root mysql secret服务器初始化随机密码的位置Mysql secret里记录了root的初始化随机密码 更改密码的方式为 SETPASSWORD PASSWORD Kecan 或者直接更新数据字典Updateusersetpassword Kecan 4 1 5数据库启动方式 数据库启动及客户端工具连接 启动命令 可使用如下命令启动MySQL servicemysqlstart关闭命令 Servicemysqlstop数据库服务端登录 Mysql uroot h127 0 0 1 P3306 p 5 1 6数据库启动方式 权限分为认证和授权两部分 Mysql的权限系统围绕着两个概念 1 认证 确定用户是否容许连接数据库服务器 2 授权 确定用户是否有足够的权限执行数据库操作 两个权限相关的表 user和dbuser表在某种程度上是独一无二的 因为它是唯一一个在权限请求的认证和授权阶段都起作用的表 也是唯一一个保存MySQL服务器相关权限的权限表 在认证阶段 它只是负责为用户授权访问MySQL服务器 确定用户每小时的最大连接数和最大并发数 在授权阶段 user确定允许访问服务器的用户是否被赋予了操作数据库的全局权限 确定用户每小时的最大查询数和更新数 db表用于为每个用户针对每个数据库赋予权限 具体的可以查看db的字段 6 1 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 修改f参数配置文件 指定路径 Datadir mysqldata mysqlSocket mysqldata mysql mysql sock6 重启mysql生效 7 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 2 9Mysql特性 临时表 Mysql临时表是指使用createtemprarytable创建的临时表 临时表可以使用任何存储引擎 临时表只在单个连接中可见 当连接断开时 临时表也会消失 MySQL最初会将临时表创建在内存中 当数据变的太大后 就会转储到磁盘上 tmp table size参数限定单个临时表在内存中的大小 超过将转到磁盘上 1 不同会话创建的表的名字可以一样 2 表结构和数据都放在内存中 磁盘上不创建frm文件 3 会话消失表结构和数据都消失 4 可以创建索引 删除索引 5 主库创建的表 备库查不到 6 showtables看不到表 7 在同一个查询中只能查找一次临时表 2 10Mysql特性 内存表 Mysql内存表是使用memory存储引擎的表 表结构定义保存在磁盘中 数据保存在内存中 内存表可以限定表的大小 使用参数max heap table size 但不会转存到磁盘上 Delete操作不会回收内存 只有drop才能回收内存 1 不同session 创建表的名字不能一样 2 一个session创建会话后 对其他session也是可见的 3 表结构放在磁盘上 数据放在内存中 4 mysql重启或者关闭后内存表里的数据会丢失 但是表结构仍然存在 5 可以创建索引 删除索引 支持唯一索引 6 不影响主备 主库上插入的数据 备库也可以查到 7 showtables看得到表 Mysql的监控与优化 3 1Mysql的日志文件 对mysql进行性能监控优化 首先我们要了解mysql的各种日志 Mysql的日志有5种类型 分别是 errorlog generalquerylog binarylog relaylog和slowquerylog 在默认情况下 mysql不会启动任何log类型的log 所有日志默认都会存放在统一路径下 var lib mysql 可以通过flushlog命令强制关闭日志服务后重新开启日志 当执行flushlog命令时 会刷新日志 执行mysqladmin命令也可以达到同样的效果 对于binarylog 当日志大小达到max binlog size时 会自动触发对binlog的flash操作 在5种类型日志中 只有generalquerylog和slowquerylog可以在线动态启用和关闭 并能修改日志文件名称路径 以及可以修改日志的存放模式 日志文件和数据表 Mysql对generllog和slowlog的控制比较灵活 可以根据需要设定日志存放模式 也可以两种模式同时使用 但是日志以数据表的方式进行存储时 分别对应mysql general log和mysql slow log 会对性能有影响 建议采用日志文件的形式进行保存日志 3 2慢日志 参数slow launch time的值代表着捕获所有执行时间超过2秒的查询 slowlog可以记录没有使用索引的查询 也能记录执行速度比较慢的管理命令 开启log queries not using indexes 将会记录没有使用索引的查询到slow日志里 可以使用MySQL自带的mysqldumpslow工具来对慢日志进行简单分析 参数说明如下 s 排序方式 c t l r表示记录次数 时间 查询时间的多少 返回的记录数排序 ac at al ar表示相应的倒叙 t 返回前面多少条的数据 g 参数后面可以模糊查询相应字符串的语句 用双引号进行分隔 字符串内容大小写不敏感 例如 slow记录最多的10个语句 mysqldumpslow sr t10 slowquery log 慢日志 slowlog 是我们平时进行性能监控的最重要的日志 3 2常用监控数据字典表 Select frominformation schema processlist 类似oracle的v session 表中记录了当前所有连接的数据库线程 包含连接时长 状态 sql语句等信息 也可使用showprocesslist命令查看 showstatuslike Slow queries 超过慢日志设置时长的记录数 showstatuslike Select full join 没有主键的join操作记录数 Select frominformation schema innodb trx 记录当前innodb存储引擎下正在运行的事物 可以通过trx mysql thread id与information schema processlist表的id相关联 Information schema innodb locks和information schema innodb lock waits记录innodb的锁信息Showengineinnodbstatus命令会输出innodb内部性能相关的计数器 统计 事务处理信息等 3 3Mysql优化 表的设计 字段尽量设置notnull mysql的null值和空字符串不一样 null值占用空间 空字符串不占用空间 Null值占用空间 比较时会参与比较 但b树索引不会存储null值 因此当索引的字段包含null值 效率会下降 一般没有业务因素影响 采用数值型的自增长列作为主键 Mysql的字段设计需要集约化 越小的占用存储空间越好 能数值不字符 能日期不字符 注意Mysql的varchar类型要计算好长度 因为mysql在表被查询后会把表信息缓存到内存中 在内存中申请是按照最大长度申请 将不常用的字段或大字段从主表拆分出来放到子表 通过主键关联访问 对于核心访问频度极高的表 需要反范式设计 增加冗余字段 减少与其它表的join操作 3 4Mysql优化 索引的设计 主键字段尽量短 数值型是较好的选择 Mysqlinnodb单列索引默认情况下限制长度767 使用utf8字符集 长度限制为255 当查询包含的条件和字段很少时 可以创建覆盖索引 这样数据可以全部在索引中获得 将不再回表 当需要在长文本字段上创建索引时 可以考虑增加一个虚拟hash列 采用crc32计算一个hash值 针对这个列创建索引 只用这个索引做等值查询 索引的空间占会小很多 组合索引在多列上创建 单列索引在一个列上创建 查询使用索引的条件不同一般组合索引需要按照 最左前缀 来执行查询 并不是每个列都需要覆盖 只是从左边的列开始组合 例如有索引key a b c wherea xxandb xxandc xxx此语句可以用到索引whereb xxanda xxandc xxx同上 顺序没有关系 同样能用到索引wherea xxandb xx可以用到索引wherea xxandc xx可以用到索引whereb xxandc xx用不到索引whereb xx用不到索引wherec xx用不到索引 3 5Mysql的ddl操作 在5 5版本以前的Mysql在做表的ddl操作时 与oracle只是维护数据字典不同 由于是索引组织表 是通过中间表来实现ddl操作 大致操作流程如下 A 对表加S共享锁 表此时只读 B 复制原表物理结构C 修改表的物理结构D 把原表数据导入中间表中 数据同步完后 锁定中间表 并删除原表E rename中间表为原表F 刷新数据字典 并释放锁通过上面的过程可以了解到 表的数据量越大 这个ddl维护过程越耗时间 锁表时间越长 MySQL5 6系列在DDL操作上做了新的更改 由原来的新建临时表 再把数据插入 再rename表的方式改变成直接在原表alter 最后时刻加锁 加快执行时间 不影响其他update delete select insert操作 主要受old alter table控制 默认情况下参数为OFF Mysql的开发应用 4 1Mysql程序开发 Mysql存储过程的应用 Mysql的视图效率很差 特别是多表关联子查询很容易不走索引 Mysql的存储过程和oracle不同 过程的语句可以只是查询语句 执行后能返回查询的结果 我们可以通过存储过程来间接实现视图的功能 下面我们实现一个简单的过程 DELIMITER CREATEPROCEDURE sayhello BEGINSELECT sayhello END DELIMITER CALLsayhello 将会返回select sayhello 的结果 这样 我们将可以利用存储过程的优势 来优化我们的sql语句 核心就是 数据路由 4 3Mysql程序开发 Mysql存储过程的应用 存储过程可以动态执行ddl语句 创建临时表 并创建索引 我们可以把中间结果放入临时表 减少sql语句的复杂度 避免多表的关联 实现简单sql 并通过if判断条件进行数据路由 分情况进行语句查询 不再需要用一条复杂sql语句实现查询需求 比如brt问题管理系统的待办问题页面 原来的后台语句是一个根据登录权限不同 使用多个查询union结果的复杂sql语句 根据实际需求 拆分成若干个简单sql Mysql的架构设计 5 1Mysql的分布式存储技术 读写分离 主从复制 是构建基于MySQL的大规模 高性能应用的基础 通过为服务器配置一个或多个备库的方式来进行数据同步 读写分离技术将write和read请求分散在不同的数据库服务器上进行处理 降低单台数据库服务器的处理压力 从而提升整体数据库的并发负载能力 一般由master服务器处理数据的增 删 改请求 slave服务器负责数据的查询请求 主从复制 分布式管理 采用不同的切分维度 对数据进行分片 将数据存储到不同的数据库或者或同一数据库的不同表上 而缓解单一数据库的性能问题 采用心跳检查机制实时检查服务器状态 实现宕机自动下线 上线 提升系统的稳定性与可用性 分库分表 5 1 1主从复制技术 整体上来说 复制有3个步骤 1 master将数据的变更记录到二进制日志 binarylog 中 这些记录叫做二进制日志事件 binarylogevents 2 slave将master的binarylogevents拷贝到它的中继日志 relaylog 3 slave重做中继日志中的事件 将改变反映到它自己的数据 完成主从数据同步 5 1 2Mysql的读写分离及分布式管理 通过主从复制功能实现了数据库系统的高可用性和数据安全 并可以通过中间层代理软件 atlas 实现读写分离来达到部分的性能提升 两个Master节点 其中一个设置为ReadOnly 并在该节点上挂一个或多个Slave MySQL本身没有提供replicationfailover的解决方案 需要通过代理层软件实现 代理软件通过心跳检查机制 对数据库的状态进行检查 对宕机的服务器自动上下线 从而提升系统的稳定性和可用性 Repl Write Read Repl Repl Write Read Read Read 5 1 3Mysql主从复制常用架构 在实际应用场景中 MySQL复制80 以上都是一个Master复制到一个或者多个Slave的架构模式 用于解决读压力比较大的数据库 是一种廉价扩展解决方案 适合主从服务器压力较小 对从库的数据读取不需要严格准确的业务应用 Master Slav