MySQL高级DBA核心项目

MySQL高级DBA目录TOC\o"1-4"\h\z\u1.mysql第一天课程 61.1.mysql数据库介绍 61.2.mysql三种安装方式区别和平台支持更新 71.3.项目1：WindowsServer搭建mysql数据库 81.4.项目2：Linux搭建mysql单实例数据库（生产环境规划） 91.5.项目3：Linux搭建mysql多实例数据库（生产环境规划） 101.6.mysql4种启动的方式 111.7.mysql常用维护命令 121.8.mysql数据类型 142.mysql第二天课程 162.1.mysqlDDL语法官方介绍 162.2.mysql数据库管理案例 162.3.mysql约束介绍以及分类 182.4.mysql数据表管理以及约束使用案例 192.5.mysql索引介绍 242.6.mysqlB+Tree索引深入解析（b树和b+树） 252.7.mysql主键索引和普通索引的区别 322.8.InnoDB一级索引和二级索引的关系：回表 322.9.堆组织表(HOT)和索引组织表(IOT)的区别 342.10.mysqlB-Tree索引使用案例 363.mysql第三天课程 383.1.mysqlDML语言官方介绍 383.2.mysqlDML语言-insertinto 383.3.mysqlDML语言-update 403.4.mysqlDML语言-delete 403.5.mysqlDML语言-select 413.6.mysqlDML语言-select高级查询连接查询和子查询 483.7.mysql体系架构-物理文件 543.8.mysql体系架构-文件类型 543.9.mysql体系架构-数据库与实例的关系 563.10.mysql体系架构-存储引擎 573.11.mysql体系架构-InnoDB逻辑存储结构 583.12.mysql体系架构-InnoDB存储引擎体系架构 603.13.InnoDB存储引擎的常用参数配置 663.14.InnoDBBufferPool原理与配置 693.15.InnoDB表空间介绍以及管理 733.16.深入分析InnoDB内部结构 773.17.mysql常用空间信息查询SQL 774.mysql第四天课程 794.1.mysql数据库事务相关概念理解 794.2.mysqlinnodb事务管理 824.3.mysql锁分类介绍 874.4.mysql各种锁的名词 894.5.mysql查看锁状态、锁相关参数、解决死锁故障 914.6.mysqlMVCC多版本并发控制 934.7.mysqlMVCC的实现原理 954.8.mysql权限系统介绍 1004.9.MySQL权限详解 1034.10.MySQL用户权限信息管理 1054.11.mysql安全之密码强度验证 1084.12.mysql安全之SSL认证 1124.13.mysql安全之重置ROOT密码 1165.mysql第五天课程 1185.1.mysqlbinlog二进制日志概述 1185.2.mysqlbinlog二进制日志解析 1185.3.mysqlBinlog二进制日志参数配置 1195.4.mysqlBinlog二进制日志刷盘原理 1215.5.mysqlBinlog二进制格式解析 1225.6.开启mysqlBinlog日志、插入测试数据 1235.7.mysqlBinlog日志的常用命令 1245.8.mysqlBinlog日志查看工具及使用案例 1255.9.mysql通过binlog日志恢复数据-误删除的表 1275.10.mysql针对binlog日志对表的增删改统计分析 1275.11.mysql逻辑备份工具mysqldump 1285.12.mysql逻辑备份工具mysqldump生产备份案例 1305.13.mysql物理备份工具xtrabackup 1315.14.mysql物理备份工具xtrabackup生产备份案例 1356.mysql第六天课程 1366.1.InnodbonlineDDL 1366.2.mysql复制技术的介绍 1416.3.mysqlinnodb二阶段日志提交机制和组提交解析 1436.4.mysql主从复制技术之同步方式 1446.5.mysql主从复制技术之GTID特性 1486.6.mysql主从复制技术之常用配置参数 1506.7.主从项目1：搭建传统异步主从复制（使用mysqldump） 1526.8.主从项目2：搭建GTID无损同步主从复制（使用xtrabackup） 1537.mysql第七天课程 1567.1.MaxScale读写分离中间件介绍 1567.2.MaxScale读写分离架构图介绍 1567.3.MaxScale功能介绍 1587.4.MaxScale项目1：一主两从复制实现读写分离（一组复制） 1587.5.MaxScale项目2：一主两从复制实现读写分离（两组复制） 1597.6.企业级项目：mysql+keepalived双主热备故障切换案例 1608.mysql第八天课程 1618.1.mysql复制技术之MGR介绍 1618.2.mysql大型高可用集群架构(GroupReplication)拓扑图 1628.3.mysql复制技术之MGR特点 1638.4.mysql复制技术之MGR模式 1638.5.mysqlMGR(Multi-primary)模式的限制 1638.6.mysql主从复制技术之MGR限制汇总 1648.7.mysqlGroupReplication复制参数解析 1658.8.项目一：mysqlGroupReplication多主模式项目实施 1668.9.项目二：mysqlGroupReplication单主模式项目实施 1678.10.mysqlGroupReplication节点状态转移 1688.11.项目三：在线修改Single-Maste为Multi-Master模式 1688.12.项目四：mysqlGroupReplication在线増加节点 1689.mysql第九天课程 1709.1.GaleraClusterr(PXC)技术方案介绍 1709.2.GaleraClusterr(PXC)大型高可用方案 1709.3.PXC集群的原理 1719.4.PXC集群的特点 1729.5.PXC集群的缺点 1739.6.PXC集群的使用限制 1739.7.PXC集群使用到的端口 1749.8.PXC集群的使用总结 1759.9.PXC集群参数详解 1759.10.项目一：PXC集群数据库项目实施 1759.11.PXC集群gcache、grastate.dat、gvwstate.dat文件介绍 1769.12.PXC集群数据库部署检查和相关参数 1779.13.项目二：PXC增加与删除集群节点案例 18010.mysql第10天课程 18110.1.集群技术的分类 18110.2.常用的负载均衡 18110.3.keepalived介绍 18210.4.HAProxy软件介绍 18210.5.HAProxy软件优点 18310.6.关于网站架构比较合理主流的架构方案 18310.7.Keepalived+HAproxy+PXC集群拓扑图 18410.8.Keepalived+HAproxy+PXC项目环境介绍与实施 18411.mysql第11天课程 18711.1.mysql优化概述和优化方案 18711.2.操作系统优化调整 18911.3.mysql数据库层面常用优化指标分析 19211.4.SQL分析和优化 19611.5.mysql性能优化之执行计划Explain 19711.6.mysql性能优化之收集统计信息 19811.7.mysql索引优化经验与SQLAdvisor 20011.8.mysql索引优化案例和索引使用经验 20211.9.Pt-Query-Digest工具介绍 20312.mysql第12天课程 20512.1.MyCat产品特性与分表分库功能介绍 20512.2.mysql高并发业务系统架构优化方案（分库-业务拆分） 20812.3.mysql高并发业务系统架构优化方案（分表） 21012.4.mysql高并发业务系统架构优化方案（分库分表） 21212.5.MyCat安装与启动 21312.6.mycat配置系统介绍 21412.7.MyCat分库分表功能与分片规则功能 21412.8.MyCat多租户应用讲解 21812.9.MyCat分表分库分片项目实战-垂直分库 21912.10.MyCat分库分表分片项目实战-按全国省份分片枚举 22112.11.MyCat分表分库分片项目实战-水平分库-主键范围 222mysql第一天课程mysql数据库介绍mysql是一款安全、跨平台、高效的，并与PHP、Java等主流编程语言紧密结合的数据库系统。该数据库系统是由瑞典的mysqlAB公司开发、发布并支持，由mysql的初始开发人员DavidAxmark和MichaelMontyWidenius于1995年建立的。目前mysql被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，使得很多公司都采用mysql数据库以降低成本。mysql数据库可以称得上是目前运行速度最快的SQL语言数据库之一。除了具有许多其他数据库所不具备的功能外，mysql数据库还是一种完全免费的产品，用户可以直接通过网络下载mysql数据库，而不必支付任何费用。2009年4月20日，Oracle并购了Sun，这也意味着mysql归属到甲骨文的旗下,Oracle公司发布两种mysql的版本,企业版和社区版:1.社区版的免费，出问题mysql公司概不负责，是企业版的测试版，功能却没有企业版功能完善。2.企业版的收费，并且价格不便宜，标准版2000美元，企业版5000美元，高级集群版10000美元（6万人民币），但是只包含稳定的功能。目前mysql最稳定，最好用的是5.7版本，相当于Oracle11G版本，这也是所有互联网公司使用得最多的一个版本，所以本次文档将以mysql5.7.20以上的版本来展开培训。mysql三种安装方式区别和平台支持更新二进制安装（生产推荐）linux下二进制格式的软件是指事先已经在各种平台编译安装好相关的软件，然后压缩打包，在安装时只需要解压或者执行安装可执行文件即可;源码编译安装（开发/测试推荐）使用make和makefile工具可以简单快速地解决各个源文件之间复杂的依赖关系，同时，make工具还可以自动完成所有源码文件的编译工作，并且可以只对上次编译后修改过的文件进行增量编译;rpm/yum方式安装（学习推荐）在mysql官网下载rmp二进制安装包使用rpm-ivh进行安装;mysql不同版本平台支持更新/support/supportedplatforms/database.html项目1：WindowsServer搭建mysql数据库环境规划虚拟机版本： VMware®Workstation15Pro系统版本： WindowsServer2012R2网络连接方式： 桥接主机名： dbos110GateWay: IP: 10CPU: 8核内存： 16Gmysql版本： mysql5.7.20社区版数据库安装过程安装过程请看实战操作。项目2：Linux搭建mysql单实例数据库（生产环境规划）环境规划虚拟机版本： VMware®Workstation15Pro系统版本： RedHatEnterpriseLinuxServerrelease7.6网络连接方式： 桥接mysql版本： mysql5.7.29社区版主机名： dbos111GateWay: IP: 11mysqlport： 3306CPU: 8核内存： 16G硬盘： sda 150G 挂载/目录sdb 100G LVM逻辑卷挂载/mysqlsdc 100G LVM逻辑卷挂载/mysqlsdd100G LVM逻辑卷挂载/mysqlLinux环境安装过程RHEL7.6安装过程请看实战操作。mysql数据库搭建过程mysql5.7.29安装部署过程请看实战操作。项目3：Linux搭建mysql多实例数据库（生产环境规划）环境规划虚拟机版本： VMware®Workstation15Pro系统版本： RedHatEnterpriseLinuxServerrelease7.6网络连接方式： 桥接mysql版本： mysql5.7.29社区版主机名： dbos111GateWay: IP: 11mysqlport： 3307CPU: 8核内存： 16G硬盘： sda 150G 挂载/目录sdb 100G LVM逻辑卷挂载/mysql sdc 100G LVM逻辑卷挂载/mysqlsdd100G LVM逻辑卷挂载/mysqlLinux环境安装过程使用项目2：Linux搭建mysql单实例数据库项目的环境。mysql数据库搭建过程mysql5.7.29安装部署过程请看实战操作，再增加个新的mysql（3307端口）实例。mysql4种启动的方式大家都知道Oracle是多进程的数据库，而mysql是一种单进程多线程的数据库，但是为什么使用psaux|grep"mysql"这条命令的时候会出现两个关于mysql的进程，这里就涉及到mysql的4种启动方式：mysqld: 存放在$basedir/bin目录下，是使用C/C++语言编写编译出来的二进制可执行文件，是计算机进程，mysql数据库的主程序,可以直接通过以下命令，指定参数文件和用户来启动mysql主进程，但是一般不建议这样启动;./mysqld--defaults-file=/etc/f--user=mysql& 可以通过以下命令去查看mysqld执行文件的所有参数以及变量;./mysqld--verbose--helpmysqld_safe: 是个shell脚本,脚本位置在$basedir/bin路径下，默认去调用mysqld服务器程序,进行服务器的监听，如果mysqld进程掉了，mysqld_safe会自动启动mysqld程序;可以通过以下命令去查看mysqld_safe脚本文件的所有启动参数;./mysqld_safe--helpmysql.server 是mysql启动/停止的shell脚本,脚本位置在mysql安装目录的support-files路径下，默认去调用mysqld_safe脚本文件启动mysql进程,生产环境强烈建议使用这个脚本进行启动与停止mysql进程;mysqld_multi 是mysql多实例启动/停止的shell脚本,脚本位置在$basedir/bin路径下，默认去调用mysqld_safe脚本文件启动mysql进程,但是一般不会使用这个脚本来进行mysql多实例管理;mysql常用维护命令查看数据库版本mysql>showvariableslike'%version%';切换数据库、列出所有的数据库、查看数据库信息mysql>showdatabases;mysql>use<database_name>;mysql>showcreatedatabases<database_name>;查看数据库存储引擎状态mysql>showengines;mysql>showengineinnodbstatus\G查看数据库使用的插件mysql>showplugins;查看数据库的状态mysql>showstatus;查看数据库主库、从库状态mysql>showmasterstatus;mysql>showslavestatus\G查看触发器和存储过程mysql>showtriggers;mysql>showprocedurestatus;查看mysql数据库当前线程mysql>showprocesslist;查看某个用户的权限mysql>showgrantsforroot@'%';mysql>showgrantsforroot@'localhost'; 10)查看数据库下的表和索引mysql>showtables;mysql>showcreatetable

<table_name>;mysql>showindexfrom<table_name>;mysql数据类型mysql支持的多种数据类型主要有：数值数据类型、日期/时间类型、字符串类型。数值类型-整型浮点数&定点数注：定点数以字符串形式存储，对精度要求高时使用decimal较好；尽量避免对浮点数进行减法和比较运算。

时间/日期类型字符串类型mysql数据类型总结mysql常见的数据类型有int、decimal、date、datetime、varchar、text，对于数据类型这块内容的熟练、精通，是开发要做的工作，我们作为mysqlDBA，更多的只是熟悉，了解即可。mysql第二天课程mysqlDDL语法官方介绍DataDefinitionStatements（DDL）语言介绍其语句包括create和drop，在数据库中创建新表或删除表createtable或droptable，为表加入索引等。DDL包括许多与数据库目录中获得数据有关的保留字。它也是动作查询的一部分。DataDefinitionStatements数据库定义语言官方文档地址/doc/refman/5.7/en/sql-data-definition-statements.htmlmysql数据库管理案例创建一个tkjy数据库CREATEDATABASEtkjy;CREATEDATABASEIFNOTEXISTStkjy; #defaultcharacterset:默认字符集CREATEDATABASEIFNOTEXISTStkjydefaultcharactersetUTF8;#defaultcollate：默认排序规格#utf8_general_ci：不区分大小写#utf8_general_cs：区分大小写 CREATEDATABASEIFNOTEXISTStkjydefaultcharactersetUTF8 defaultcollateutf8_general_ci;切换数据库usetkjy;查询数据库showdatabases;#如果有很多数据库，模糊查询某个数据库 showdatabaseslike'%tkjy%';#查询创建数据库的语句showcreatedatabasetkjy;#更新数据库选项信息（操作需要谨慎） alterdatabasetkjycharactersetgbk;删除数据库dropdatabasetkjy;dropdatabaseifexiststkjy;使用mysqladmin工具创建、删除数据库mysqladmincreatetkjy-uroot-p123456mysqladmindroptkjy-uroot-p123456mysql约束介绍以及分类mysql约束作用主要是防止非法数据进入到表中,确保数据的正确性和一致性(统称数据完整性)约束也可以防止一个表被删除，主要有非空约束、唯一约束、主键约束、外键约束、检查约束。mysql约束对应的五大关键词NOTNULL： 如果在列上定义了notnull,那么当插入数据时,必须为列提供数据。UNIQUE： 当定义了唯一约束后,该列值是不能重复的,但是可以为null。PrimaryKey：用于唯一的标识表行的数据,当定义主键约東后,该列不但不能重复而且不能 为NULL。一张表最多只能有一个主键,但是可以由多个unique约束。ForeignKey：用于定义主表和从表之间的关系,外键约束要定义在从表上,主要则必须具有 主键约束或是uniques约束,当定义外键约束后,要求外键列数据必须在主表的主键列存 在或是为NULL。CHECK： 用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在 1000~2000之间,如果不在1000~2000之间就会提示出错，mysql里面的check约束是有， 但是所有存储引擎都不生效，是个幌子，如果想实现类似于check约束的功能，可以通过 触发器（TRIGGER）或者枚举（ENUM）数据类型来实现，前面没有讲到枚举数据类型，这 里带过一下，mysql数据库在程序设计过程中是非常不推荐使用触发器和枚举数据类 型，会极大影响数据库运行性能。mysql数据表管理以及约束使用案例在tkjy数据库下面创建一张student学生表usetkjy;createtableifnotexistsstudent( idint(5)unsignedauto_incrementprimarykeycomment'学生表主键', namevarchar(20)notnullcomment'学生姓名', agetinyintnotnullcomment'学生年龄', admission_timedatetimecomment'入学时间', genderenum('男','女','保密')comment'学生性别', student_idint(10)UNIQUEcomment'学生编号')engine=innodbdefaultcharset=utf8comment'学生表';#auto_increment： 主键自增（可选操作）#engine： 表使用存储引擎（可选操作） #comment： 注释（可选操作）#defaultcharset： 表的字符集（可选操作）使用like关键字通过旧表创建新表(包括旧表的结构+信息+索引)createtable<table_name>like<old_table_name>;使用as关键字通过旧表创建新表(包括旧表的结构+信息)createtable<table_name>asselect*from<old_table_name>;查看表、表结构、表创建语句showtables;showtablesfromtkjy;showtableslike'%stud%';descstudent;showcreatetablestudent\G;删除表droptablestudent;重命名表(注意：切勿在生产环境上直接rename表)renametablestudenttonew_student;截断表truncatetablenew_student;修改表结构#altertable<table_name>add/drop/modify/change#增加列、增加主键altertablenew_studentaddstudent_fromvarchar(10)notnull;altertablenew_studentadd(phoneintuniquenotnull,emailvarchar(20));altertablenew_studentaddprimarykey(id);#删除列、删除主键约束、删除自增的主键约束altertablenew_studentdropemail;altertablenew_studentdropprimarykey;altertablenew_studentchangeididint;altertablenew_studentdropprimarykey;#重命名列altertablenew_studentchangestudent_fromst_fromvarchar(10);#修改表字段属性altertablenew_studentmodifyst_fromvarchar(15)unique;#修改字符集,有数据不能改altertablenew_studentcharactersetgbk;altertablenew_studentcharactersetutf8; 在tkjy数据库创建包含外键的员工表（YG）和工资表（gz）#创建工资表createtableifnotexiststkjy.gz( idint(5)primarykeycomment'工资表主键', salaryint(7)notnullcomment'薪水', jobvarchar(10)notnullcomment'工作岗位', departmentvarchar(5)notnullcomment'工作部门')engine=innodbdefaultcharset=utf8comment'员工表';#工资表插入数据insertintotkjy.gzvalues(1,10000,'销售','销售部');insertintotkjy.gzvalues(2,15000,'OracleDBA','技术部');insertintotkjy.gzvalues(3,20000,'mysqlDBA','技术部');insertintotkjy.gzvalues(4,18000,'java','研发中心');insertintotkjy.gzvalues(5,30000,'C++','研发中心');insertintotkjy.gzvalues(6,16000,'python','研发中心');commit;#查看工资select*fromtkjy.gz;#创建员工表createtableifnotexiststkjy.yg( idint(5)unsignedauto_incrementprimarykeycomment'员工表主键', namevarchar(7)notnullcomment'员工姓名', agetinyintnotnullcomment'员工年龄', entry_timeyearcomment'入职时间', genderenum('男','女','保密')comment'员工性别', gz_idint(5)notnull, foreignkey(gz_id)referencestkjy.gz(id)) engine=innodbdefaultcharset=utf8comment'员工表';#外键必须是主表的主键或者唯一键,如果是另外一张表主键的话，该表主键不允许带有auto_increment自增长属性。#主表记录删除时ondeletecascade/更新时的动作onupdatecascade#创建表以后再增加外键也可以#altertabletkjy.ygaddforeignkey(gz_id)referencestkjy.gz(id);#插入数据测试（在主表间键值内成功）insertintotkjy.ygvalues(1,'春野樱',18,2015,'女',1);insertintotkjy.ygvalues(2,'漩涡鸣人',18,2016,'男',2);insertintotkjy.ygvalues(3,'宇智波佐助',18,2017,'男',2);insertintotkjy.ygvalues(4,'第一代火影',65,1970,'男',3);insertintotkjy.ygvalues(5,'第二代火影',60,1975,'男',4);insertintotkjy.ygvalues(6,'第三代火影',38,2000,'男',5);insertintotkjy.ygvalues(7,'第四代火影',38,2000,'男',6);commit;select*fromtkjy.yg;#插入数据测试（在主表间键值外失败）insertintotkjy.ygvalues(8,'宇智波斑',28,2020,'男',7);外键总结：mysql数据库不建议使用外键、会极大影响数据库运行性能（并发访问）。 mysql索引介绍什么是索引?在关系数据库中，索引是一种单独的、物理的数对数据库表中一列或多列的值进行排序的一种存储结构。 它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。索引的优点通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。在使用分组和排序子句进行数据查询时,可以减少查询中分组和排序的时间。通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。索引的缺点创建索引和维护索引要耗费时,这种时间随着数据量的增加而增加。索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。索引的分类B+树索引(主要,默认索引)：mysqlinnodb默认的索引类型就是B+tree索引(B树,B-树,B+树,B*树),BinaryTree,就是一个二叉树。hash索引：Hash索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位,不像B-Tree索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以Hash索引的查询效率要远高于B+Tree索引，仅仅能满足"=""IN"和"<=>"查询,不能使用范围查询。只有Memory存储引擎显示支持hash索引。FULLTEXT索引(全文检索,MYISAM和INNODB引擎都支持了)。R-tree索引(用于对GIS数据类型创建SPATIAL空间索引)索引按逻辑的分类主键索引普通索引or单列索引多列索引(复合索引)唯一索引或者非唯一索引空间索引mysqlB+Tree索引深入解析（b树和b+树）在这里首先我抛出四个问题?为什么需要B-树?有了B-树，为什么需要B+树？/B+树演变的需求。B-树和B+树有什么区别?为什么MongoDB使用B-树，而MySQL使用B+树?B-树由来定义：B-树是一类树，包括B-树、B+树、B*树等，是一棵自平衡的搜索树，它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。树是专门为外部存储器设计的，如磁盘，它对于读取和写入大块数据有良好的性能，所以一般被用在文件系统及数据库中。先来看看为什么会出现B-树这类数据结构。传统用来搜索的平衡二叉树有很多，如AVL树（平衡二叉搜索树），红黑树等。这些树在一般情况下查询性能非常好，但当数据非常大的时候它们就无能为力了。原因当数据量非常大时，内存不够用，大部分数据只能存放在磁盘上，只有需要的数据才加载到内存中。一般而言内存访问的时间约为50ns（纳秒），而磁盘在10ms（毫秒）左右。速度相差了近5个数量级，磁盘读取时间远远超过了数据在内存中比较的时间。这说明程序大部分时间会阻塞在磁盘IO上。那么我们如何提高程序性能？减少磁盘IO次数，像AVL树，红黑树这类平衡二叉树从设计上无法“迎合”磁盘。关于磁盘可参考浅谈计算机中的存储模型（四）磁盘</wwh578867817/article/details/49005675>上图是一颗简单的平衡二叉树，平衡二叉树是通过旋转来保持平衡的，而旋转是对整棵树的操作，若部分加载到内存中则无法完成旋转操作。其次平衡二叉树的高度相对较大为logn（底数为2），这样逻辑上很近的节点实际可能非常远，无法很好的利用磁盘预读（局部性原理），所以这类平衡二叉树在数据库和文件系统上的选择就被pass了。空间局部性原理：如果一个存储器的某个位置被访问，那么将它附近的位置也会被访问。我们从“迎合”磁盘的角度来看看B-树的设计。索引的效率依赖与磁盘IO的次数，快速索引需要有效的减少磁盘IO次数，如何快速索引呢？索引的原理其实是不断的缩小查找范围，就如我们平时用字典查单词一样，先找首字母缩小范围，再第二个字母等等。平衡二叉树是每次将范围分割为两个区间。为了更快，B-树每次将范围分割为多个区间，区间越多，定位数据越快越精确。那么如果节点为区间范围，每个节点就较大了。所以新建节点时，直接申请页大小的空间（磁盘是按block分的，一般为512Byte。磁盘IO一次读取若干个block，我们称为一页，具体大小和操作系统有关，一般为4k，8k或16k），计算机内存分配是按页对齐的，这样就实现了一个节点只需要一次IO。上图是一棵简化的B-树，多叉的好处非常明显，有效的降低了B-树的高度，为底数很大的logn，底数大小与节点的子节点数目有关，一般一棵B-树的高度在3层左右。层数低，每个节点区确定的范围更精确，范围缩小的速度越快。上面说了一个节点需要进行一次IO，那么总IO的次数就缩减为了logn次。B-树的每个节点是n个有序的序列(a1,a2,a3…an)，并将该节点的子节点分割成n+1个区间来进行索引(X1<a1,a2<X2<a3,…,an+1<Xn<anXn+1>an)。上图是一颗B-树，B-树的每个节点有d~2d个key，这个因子指明了树的分裂及合并的规则，这个规则维持了B-树的平衡。B-树的插入和删除就不具体介绍了，很多资料都描述了这一过程。在普通平衡二叉树中，插入删除后若不满足平衡条件则进行旋转操作，而在B-树中，插入删除后不满足条件则进行分裂及合并操作。简单叙述下分裂及合并操作。分裂：如果有一个节点有2d个key，增加一个后为2d+1个key，不符合上述规则B-树的每个节点有d~2d个key，大于2d，则将该节点进行分裂，分裂为两个d个key的节点并将中值key归还给父节点。合并：如果有一个节点有d个key，删除一个后为d-1个key，不符合上述规则B-树的每个节点有d~2d个key，小于d，则将该节点进行合并，合并后若满足条件则合并完成，不满足则均分为两个节点。B-树的查找，我们来看看B-树的查找，假设每个节点有n个key值，被分割为n+1个区间，注意，每个key值紧跟着data域，这说明B-树的key和data是聚合在一起的。一般而言，根节点都在内存中，B-树以每个节点为一次磁盘IO，比如上图中，若搜索key为25节点的data，首先在根节点进行二分查找（因为keys有序，二分最快），判断key25小于key50，所以定位到最左侧的节点，此时进行一次磁盘IO，将该节点从磁盘读入内存，接着继续进行上述过程，直到找到该key为止。B-树由来B+树是B-树的变种，它与B-树的不同之处在于：在B+树中，key的副本存储在内部节点，真正的key和data存储在叶子节点上。n个key值的节点指针域为n而不是n+1。如下图为一颗B+树：因为内节点并不存储data，所以一般B+树的叶节点和内节点大小不同，而B-树的每个节点大小一般是相同的，为一页。为了增加区间访问性，一般会对B+树做一些优化。如下图带顺序访问的B+树。B-树和B+树的区别B+树内节点不存储数据，所有data存储在叶节点导致查询时间复杂度固定为log(n)。而B-树查询时间复杂度不固定，与key在树中的位置有关，最好为O(1)。B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等，而B-树每个节点key和data在一起，则无法区间查找。B+树更适合外部存储。由于内节点无data域，每个节点能索引的范围更大更精确在数据结构上：B树为有序数组+平衡多叉树，而B+树为有序数组链表+平衡多叉树为什么MongoDB索引选择B-树，而Mysql索引选择B+树这些内容了解后，我们来看为什么MongoDB索引选择B-树，而Mysql（InooDB引擎）索引选择B+树。Mysql大家应该比较熟悉，传统的关系型数据库，下面介绍下MongoDB。来看下wiki百科上MongoDB的定义：这段话的大致意思是MongoDB是文档型的数据库，是一种nosql，它使用类Json格式保存数据。文档型数据库和我们常见的关系型数据库不同，一般使用XML或Json格式来保存数据，归属于聚合型数据库。聚合型数据库存储模型：用类似Json的格式表示如下：相对于Mysql关系型数据库，MongoDB这类nosql适用于数据模型简单，性能要求高的场合。什么MongoDB使用B-树，MongoDB是一种nosql，也存储在磁盘上，被设计用在数据模型简单，性能要求高的场合。性能要求高，看看B/B+树的区别第一点。我们说过，尽可能少的磁盘IO是提高性能的有效手段。MongoDB是聚合型数据库，而B-树恰好key和data域聚合在一起。为什么Mysql使用B+树?Mysql是一种关系型数据库，区间访问是常见的一种情况，而B-树并不支持区间访问（可参见上图），而B+树由于数据全部存储在叶子节点，并且通过指针串在一起，这样就很容易的进行区间遍历甚至全部遍历。B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等，而B-树每个节点key和data在一起，则无法区间查找B+树的查询效率更加稳定，数据全部存储在叶子节点，查询时间复杂度固定为O(logn)B+树更适合外部存储。由于内节点无data域，每个节点能索引的范围更大更精确mysql主键索引和普通索引的区别主键索引（primarykey）主键索引（PK）也称为聚集索引(clusteredindex)。索引和数据存储在一起，都存储在同一个B+tree中的叶子节点,一般主键索引都是一级索引。普通索引（secondaryindex）普通索引（secondaryindex）也称为二级索引。二级索引树的叶子节点存储的是主键而不是数据。也就是说，在找到索引后，得到对应的主键，再回到一级索引中找主键对应的数据记录。InnoDB聚集索引和普通索引有什么差异？如果表定义了PK，则PK就是聚集索引。如果表没有定义PK，则第一个notNULLunique列是聚集索引。否则，InnoDB会创建一个隐藏的row-id作为聚集索引。InnoDB普通索引的叶子节点存储主键值，所以PK查询非常快，直接定位行记录。InnoDB一级索引和二级索引的关系：回表一级索引可以单独存在，二级索引不能单独存在，必须依附于一级索引，这叫做“回表”。在这里我给大家举个案例，有一张名为t的表，表结构为：t(idPK,nameKEY,sex,flag);id是聚集索引，name是普通索引。t表中有四条记录：1,shenjian, m, A

3,zhangsan, m, A

5,lisi, m, A

9,wangwu, f, B两个B+树索引分别如上图：（1）id为PK，聚集索引，叶子节点存储行记录；（2）name为KEY，普通索引，叶子节点存储PK值，即id；既然从普通索引无法直接定位行记录，那普通索引的查询过程是怎么样的呢？通常情况下，需要扫码两遍索引树。例如：select*fromtwherename='lisi';是如何执行的呢？

如粉红色路径，需要扫码两遍索引树：（1）先通过普通索引定位到主键值id=5；（2）在通过聚集索引定位到行记录；这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。堆组织表(HOT)和索引组织表(IOT)的区别堆表数据存放在数据里面,索引存放在索引里。堆就是无序数据的集合,索引就是将数据变得有序,在索引中键值有序,数据还是无序的堆表中,主键索引和普通索引一样的,叶子节点存放的是指向堆表中数据的指针（可以是一个页编号加偏移量）,指向物理地址,没有回表的说法堆表中,主键和普通索引基本上没区别,和非空的唯一索引没区别索引组织表mysql使用的是innodb存储引擎,所以堆表一带而过,重点关注索引组织表。对于主键的索引,页子节点存放了一整行所有数据,其他索引称为辅助索引(二级索引),它的页子节点只是存放了键值和主键值主键包含了一张表的所有数据,因为主键索引的页子节点中保存了每一行的完整记录,包括所有列。如果没有主键,MySQL会自动帮你加一个主键,但是对用户不可见innodb中数据存放在聚集索引中,换言之,按照主键的方式来组织数据的其他索引(唯一索引,普通索引)的页子节点存放该索引列的键值和主键值不管是什么索引非页子节点存放的存放的就是键值和指针,不存数据,这个指针在innodb中是6个bit,键值就看数据大小了为什么主键查询比二级索引查询来的快主键索引里面包含了每一行完整的数据,只要找到那个主键就是找到那个记录,二级索引,如果查询的列不是索引列,走那个列的索引找到后还要去根据索引里保存的主键去找查询列的内容,这里多了一步,这种查找叫书签查找或者回表,如果一个高度为3的树,本来查只要查三个页,走二级索引就要查六个页。mysqlB-Tree索引使用案例单列索引使用管理#语法： createindexindex_nameontable_name(col_name); altertabletable_nameaddindexindex_name(col_name); #案例： useyzjtestdb; createindexyzjtest_m1_inx_nameonyzjtestdb.yzjtest_m1(NAME); altertableyzjtestdb.yzjtest_m1addindexyzjtest_m1_inx_tel(TEL); #查看已创建索引： showindexfromyzjtestdb.yzjtest_m1; #索引使用： explainselect*fromyzjtestdb.yzjtest_m1wherename='yzjtest30000010'; explainselect*fromyzjtestdb.yzjtest_m1wheretel=; #索引删除：dropindexyzjtest_m1_inx_nameonyzjtest_m1;dropindexyzjtest_m1_inx_telonyzjtest_m1;复合索引使用管理#语法： createindexindex_nameontable_name(col_name1,col_name2);; altertabletable_nameaddindexindex_name(col_name1,col_name2); #案例： useyzjtestdb; createindexyzjtest_m1_inx_name_telonyzjtestdb.yzjtest_m1(NAME,TEL); altertableyzjtestdb.yzjtest_m1addindexyzjtest_m1_inx_name_tel(NAME,TEL); #查看已创建索引： showindexfromyzjtestdb.yzjtest_m1; #索引使用： explainselect*fromyzjtestdb.yzjtest_m1wherename='yzjtest30000010' andtel=; #索引删除：dropindexyzjtest_m1_inx_name_telonyzjtest_m1;mysql第三天课程mysqlDML语言官方介绍DataManipulationStatements（DML）语言介绍其语句包括动词select、insert、update、delete。它们分别用于查询、添加、修改和删除，也称为动作查询语言。DataManipulationStatements数据操作语言官方文档地址/doc/refman/5.7/en/sql-data-manipulation-statements.htmlmysqlDML语言-insertintoinsertinto语句语法#语法一：INSERTINTOtable_name(field1,field2,...fieldN)VALUES(value1,value2,...valueN);#语法二：INSERTINTOtable_name(field1,field2,...fieldN)VALUES(value1,value2,...valueN),(value1,value2,...valueN),(value1,value2,...valueN);#语法三：INSERTINTOtable_nameVALUES(value1,value2,...valueN);insertinto语句案例#创建一张学生表：useyzjtestdb;createtableifnotexistsyzjtestdb.students(idint(5)primarykeycomment'学生学号',namevarchar(10)notnullcomment'学生姓名',agetinyintnotnullcomment'学生年龄',genderenum('男','女','保密')comment'学生性别') engine=innodbdefaultcharset=utf8comment'员工表';#插入数据：insertintoyzjtestdb.studentsvalues(1,'漩涡鸣人',16,'男');commit;insertintoyzjtestdb.students(id,name,age)values(2,'宇智波佐助',16);commit;insertintoyzjtestdb.studentsvalues(3,'卡卡西',25,'男'),(4,'李洛克',16,'男'),(5,'宇智波斑',100,'男'),(6,'春野樱',16,'女');commit;mysqlDML语言-updateupdate语句语法UPDATEtable_nameSETfield1=new-value1[,field2=new-value2][WHEREClause]update语句案例#将名字为漩涡鸣人的列数据修改成宇智波鼬：UPDATEyzjtestdb.studentsSETname='宇智波鼬'wherename='漩涡鸣人';commit;#将性别为男的学员年龄都+10：UPDATEyzjtestdb.studentsSETage=age+10wheregender='男'andage<50;commit;select*fromyzjtestdb.students;#修改性别为NULL的学员的名字和年龄：UPDATEyzjtestdb.studentsSETname='大蛇丸',age=127wheregenderisnull;mysqlDML语言-deletedelete语句语法DELETEFROMtable_name[WHEREClause]delete语句案例#删除性别为null的学生,年龄大于100的学生：deletefromyzjtestdb.studentswheregenderisnullandage>100;commit;select*fromyzjtestdb.students;#删除性别为男的学生或者年龄大于50的学生：deletefromyzjtestdb.studentswheregender='男'orage>50;commit;select*fromyzjtestdb.students;mysqlDML语言-selectselect语句语法、基本语法select字段/表达式from表名/视图名where查询条件;#selct子句:where 查询条件groupby 在字段内不同的数据分一组having 分组以后对于数据的过滤limit 对于返回数据行数的限制orderby[asc|desc] 排序asc:升序，默认desc:降序#select表达式:变量 select10+20; --查询运算 select10*20; select10-20; select10/20; selectnow(); --查询当前时间 from子句 select*fromyzjtestdb.yg; --全列 selectemailfromyzjtestdb.yg; --单列 selectemail,job_idfromyzjtestdb.yg; --多列 select*fromrwxx,ssgj; --多表 select*fromrwxxx1,ssgjj1; --别名 select*fromrwxxx1,ssgjj1where=; --连接查询列连接 selectname,concat(name,'-',country,'-',skills)fromssgj; selectname,concat(name,'-',country,'-',skills)"合并显示"fromssgj; 虚拟表dual,提高兼容性 select1+1fromdual;selectnow()fromdual; SQL语句编写规则 大小写不敏感;SQL语句可以占用多行,以分号结束，关键字不能拆成多行;SQL运算符等于= ：id=15;大于> ：id>15;小于<：id<15;大于等于>=：id>=15;小于等于<=：id<=15;不等于<>：id<>15;不等于!=：id!=15;是空：isnull;非空 ：isnotnull;模糊查询[not]like：字段like"%北京市%";范围内[not]between ：字段between1and15;在什么范围值内[not]in:字段in('范围值1','范围值2','范围值3');#案例:usenaruto;select*fromrwxxwhereage=16;select*fromrwxxwhereage>16;select*fromrwxxwhereage<16;select*fromrwxxwhereage<>16;select*fromrwxxwhereage>=16;select*fromrwxxwhereage<=16;select*fromrwxxwhereagebetween16and25;select*fromrwxxwhereagenotbetween16and25;select*fromrwxxwhereage>=16andage<=25;select*fromrwxxwhereage>=16&&age<=25;select*fromrwxxwhereagein(16,17,28,100);select*fromrwxxwhereagenotin(16,17,28,100);select*fromrwxxwherenamelike"%宇智波%";select*fromrwxxwherenamenotlike"%宇智波%";逻辑运算非：not与：and,&&或：or#组合查询案例:usenaruto;select*fromrwxxwhereage=16andgender='男';select*fromrwxxwhereage=16orname='卡卡西';select*fromssgjwhereskillsisnull;select*fromssgjwhereskillsisnotnull;模糊查询like精确查询：=模糊查询：likeA% ：A开头 %A：A结尾%A%：包含A%A%C%：包含A和CA%C：A开头C结尾#模糊查询案例:selecttelfromyzjtestdb.yzjtest_ygwheretellike'137%';selectJOBTITLEfromyzjtestdb.yzjtest_ygwhereJOBTITLElike'销售%';selectregionfromyzjtestdb.yzjtest_ygwhereregionlike'%海%';查询分组与排序groupby :分组orderby :排序#groupby分组groupby列{asc升序|desc降序},{withrollup}组内聚合计算#分组常用到的函数:max : 最大值min : 最小值avg : 平均值sum : 列/表达式总和count: 行数总和#分组案例:案例1：统计（yzjtest_sales销售表）本月每个（商品消费分类、GOODS）销量单数--不同类别商品分组selectcount(*),GOODSfromyzjtest_salesgroupbygoods;--默认升序selectcount(*),GOODSfromyzjtest_salesgroupby2orderby1; --降序 selectcount(*),GOODSfromyzjtest_salesgroupby2orderby1desc;--限制输出一行 selectcount(*),GOODSfromyzjtest_salesgroupby2orderby1desclimit1;--案例2：统计（yg员工表）本月共发了多少工资（工资、SALARY）selectsum(SALARY)fromyg;--案例3：统计（yzjtest_yg员工表）不同地区（REGION）、不同部门（DEPT）共发了多少工资（SALARY）selectREGION,DEPT,sum(SALARY)fromyzjtest_yggroupby1,2;--案例4：统计（yzjtest_yg员工表）不同地区（REGION）、共发了多少工资（SALARY）selectREGION,sum(SALARY)fromyzjtest_yggroupby1;--案例5：withrollup利用组合条件进行排序后，再次统计selectREGION,DEPT,sum(SALARY)fromyzjtest_yggroupby1,2withrollup;--案例6：统计（yzjtest_yg员工表）不同地区（REGION）、发了总数工资（SALARY）超过两百五十万selectsum(SALARY),REGIONfromyzjtest_yggroupbyREGIONhavingsum(SALARY)>2500000;限制行数limit :限制行数#limit案例:案例1：查询YG表限制输出5行select*fromyzjtestdb.yglimit5;案例2：查询YG表从第3行开始，显示4行select*fromyzjtestdb.yglimit3,4;去除重复记录distinct :去除重复记录#distinct案例:案例1：去除yzjtest_yg表sex字段重复记录selectsexfromyzjtest_yg; --先查看重复记录selectdistinctsexfromyzjtest_yg; --sex字段去重union和unionallunion :无重并集，把多个结果组合并后去重unionall:有重并集，把多个结果组合并不去重#union案例:案例1：查询rwxx表、ssgj表的name字段合并去重selectnamefromnaruto.rwxxunionselectnamefromnaruto.ssgj;#unionall案例:案例1：查询rwxx表、ssgj表的name字段合并不去重selectnamefromnaruto.rwxxunionallselectnamefromnaruto.ssgj;forupdateforupdate :锁表，悲观锁,生产环境切勿使用这条语句案例1：将rwxx表锁住select*fromnaruto.rwxxforupdate;mysqlDML语言-select高级查询连接查询和子查询select高级查询之连接查询joinSQLJOIN子句用于把来自两个或多个表的行结合起来返回数据，基于这些表之间的共同字段。join连接分为3大类：自然连接NATURALJOIN：自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。内连接INNERJOIN：如果表中有至少一个匹配，则返回行外连接左外连接LEFTJOIN：即使右表中没有匹配，也从左表返回所有的行右外连接RIGHTJOIN：即使左表中没有匹配，也从右表返回所有的行全外连接FULLJOIN：只要其中一个表中存在匹配，则返回行（mysql不支持）#SQLjoin案例:案例1：自然连接NATURALJOINselect*fromnaruto.rwxxnaturaljoinnaruto.ssgj;案例2：内连接INNERJOINselect*fromnaruto.rwxxainnerjoinnaruto.ssgjbonname=; --写法1select*fromnaruto.rwxxa,naruto.ssgjbwhere=; --写法2select*fromnaruto.rwxxainnerjoinnaruto.ssgjbusing(name); --写法3select,a.age,b.country,b.skillsfromnaruto.rwxxainnerjoinnaruto.ssgjbon=; 案例3：左外连接leftjoin/leftouterjoinselect*fromnaruto.rwxxrleftouterjoinnaruto.ssgjson=;案例4：又外连接rightjoin/rightouterjoinselect*fromnaruto.rwxxrrightouterjoinnaruto.ssgjson=;案例5：交叉连接crossjoinselect*fromnaruto.rwxxrcrossjoinnaruto.ssgjs;select高级查询之子查询selct子查询是将一个查询语句嵌套在另一个查询语句中，在特