Mysql基础知识培训

知识培训,2015年8月20日星期四,宋小龙,目录,2020/4/27,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-A1Defaultoptions几个重要的参数：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数据库服务端登录：MysqluroothP3306p,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、创建新的目录并授权。比如：mkdirp/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_cesslist;类似oracle的v$session，表中记录了当前所有连接的数据库线程，包含连接时长、状态、sql语句等信息。也可使用showprocesslist命令查看。showstatuslikeSlow_queries;超过慢日志设置时长的记录数。showstatuslikeSelect_full_join;没有主键的join操作记录数。Select*frominformation_schema.innodb_trx;记录当前innodb存储引擎下正在运行的事物。可以通过trx_mysql_thread_id与information_cesslist表的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$CREATEPROCEDUREsayhello()BEGINSELECTsayhello!;END$DELIMITER;CALLsayhello()将会返回selectsayhello!的结果。这样，我们将可以利用存储过程的优势，来优化我们的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的架构模式，用于解决读压力比较大的数据库，是一种廉价扩展解决方案。适合主从服务器压力较小，对从库的数据读取不需要严格准确的业务应用。,MasterSlaves