1、 CHINAUNICOM数据库开发基础培训2019年07月About Me项目管理部项目管理部 刘光华刘光华Email:tel:18653176958Oracle Certified Database Administrator Professional 8i (2002.10)Oracle Certified Database Administrator Professional 10g (2008.12)20 years experience with Oracle Develop&DBA technology 10 years experience with MySQL DBA tech2、nology 培训目标了解数据库的基本概念了解数据库的基本概念了解了解MySQL数据库体系结构数据库体系结构了解数据库开发的基础概念了解数据库开发的基础概念培训内容一、数据库基本概念一、数据库基本概念二、二、MySQL数据库基本概念数据库基本概念三、阿里云数据库三、阿里云数据库RDS简介简介四、四、MySQL数据库基础操作数据库基础操作五、五、MySQL数据库应用开发基础数据库应用开发基础数据库数据库基本概念基本概念 数据数据(Data)(Data)数据库数据库(Database)(Database)数据库管理系统数据库管理系统(DBMS)(DBMS)数据库系统数据库系统(DBS)(DBS)数3、据数据 数据数据（Data）的定义：的定义：对客观事物的符号表示，对客观事物的符号表示，如图形符号、数字、字母等，数据是数据库中存储的基本对象。在日常生活中，人们直接用语言来描述事物；在计算机中，为了存储和处理这些事物，就要将事物的特征抽象出来组成一个记录来描述。数据的种类文字、图形、图象、声音 数据的特点数据与其语义是不可分的数据举例 学生档案中的学生记录（张华，男，1984，上海，计算机系，2003）数据的形式不能完全表达其内容 数据的解释语义：学生姓名、性别、出生年月、籍贯、所在系别、入学时间解释：张华是个大学生，1984年出生，上海人，2003年考入计算机系数据库数据库 数据库数据库(4、Database,简称DB)的定义的定义：“按照数据结构来组织、存储和管理数据的仓库”J.Martin给数据库下了一个比较完整的定义：数据库是存储在一起的相关数据的集合，这些数据是结构化的，无有害的或不必要的冗余，并为多种应用服务数据库发展简史1951：Univac系统使用磁带和穿孔卡片作为数据存储。1970：E.F.Codd提出了关系数据库模型。1974年：IBM的Ray Boyce和Don Chamberlin将Codd关系数据库的12条准则的数学定义以简单的关键字语法表现出来，里程碑式地提出了SQL(Structured Query Language)语言。1979：Oracle公司引入5、了第一个商用SQL关系数据库管理系统。1983：IBM 推出了DB2数据库产品。数据库管理系统 数据库管理系统数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，是用于建立、使用和维护数据库，简称DBMS。关系型数据库管理系统称为RDBMS，R指RelationRelation DBMS的作用 它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。DBMS的主要功能For 开发人员数据定义功能：提供数据定义语言(DDL)定义数据库中的数据对象数据操纵功能:提供数据操纵语言(DML)操纵数据实现对数据库的基本操作(查询、插入、删除和修改6、)DBMS的主要功能For数据库管理员数据库的运行管理保证数据的安全性、完整性、多用户对数据的并发使用发生故障后的系统恢复数据库的建立和维护功能(实用程序)数据库数据批量装载数据库转储介质故障恢复数据库的重组织性能监视等常见的RDBMS Oracle DB2 Sybase MS SQL Server MySQL PostGreSQL 曾经流行的DBMS Ingres Informix Rdb/vms Xbase:dBase Foxbase Foxpro 数据库系统 数据库系统（Database System，简称DBS）是一个实际可运行的存储、维护和应用系统提供数据的软件系统 数据库系统构成 7、DBMS DB 应用软件 数据库管理员 用户15数据库系统的使用数据库服务器响应和提供数据应用程序数据库数据库操作和查询16数据库系统的使用培训内容一、数据库基本概念一、数据库基本概念二、二、MySQL数据库基本概念数据库基本概念三、阿里三、阿里RDS简介简介四、四、MySQL数据库安装配置数据库安装配置五、五、MySQL数据库应用开发基础数据库应用开发基础Whats MySQL?什么是MySQL是一个开源数据库管理系统，开发者为瑞典MySQL AB公司。体积小、速度快、总体拥有成本低开始广泛地应用在Internet上的中小型网站中目前广泛应用于企业级应用最新版本8.0，常用版本5.7、5.68、MySQL数据库历史 发展历程2001 2007 开源飞速发展期 MySQL AB2008.01 2009.04 SUN10亿美金收购MySQL SUN MySQL2009.04 now Oracle以74亿美金收购SUN Oracle MySQLMySQL企业级环境应用MySQL企业级环境应用MySQLOracle/DB2/Microsoft SQL Server服务器类型PC机小型机服务器数量多相对较少服务器管理分布式集中式应用类型OLTPOLTP&OLAP行业互联网传统行业MySQL企业级环境应用 总结MySQL已然成为互联网公司“标配”数据库Facebook、Google、Twitte9、r淘宝去IOE，Oracle=MySQLeBay去IOE，Oracle=MySQL传统行业正在逐步将一些应用迁移到MySQLMySQL数据库体系结构 单进程多线程Like Microsoft SQL ServerOracle多进程架构（except Windows）插件式存储引擎架构 多数据库Oracle 单数据库多用户Microsoft SQL Server 多数据库DB2 多数据库MySQL数据库体系结构MySQL数据库体系结构 主要文件配置文件（config file）错误文件（error file）慢查询日志（slow log）二进制日志（binlog）MySQL数据库体系结构 配置文10、件/etc/f-defaults-file mysql help|grep fMySQL数据库体系结构 配置文件/etc/fmysqldskip-slave-startlog_slave_updates =1read_only =1server-id =998bind-address =172.17.1.4port =4331pid-file =/home/ddb/mysql/db-55-1/mysqld.pidsocket =/home/ddb/mysql/db-55-1/mysqld.sockbasedir =/usr/local/mysql/datadir =/home/ddb/mysq11、l/db-55-1innodb_data_home_dir =/home/ddb/mysql/db-55-1innodb_log_group_home_dir =/home/ddb/mysql/db-55-1tmpdir =/home/ddb/mysql/db-55-1log-error =/home/ddb/mysql/db-55-1/mysqld.loglog_slow_queries =/home/ddb/mysql/db-55-1/mysql-slow.loglog_bin =/home/ddb/mysql/db-55-1/mysql-bin.loguser =mysqllanguag12、e =/usr/local/mysql/shareMySQL数据库体系结构 错误文件Like Oracle alert.log故障诊断性能调优 死锁信息 latch信息MySQL数据库体系结构 慢查询日志记录运行超过某个时间阈值的SQL slow_query_log=0|1MySQL 5.1开始以毫秒为单位 long_query_timeMySQL 5.5开始可以存表log_output=FILE|TABLE|NONE-log_output=FILE,TABLEMySQL数据库体系结构 二进制日志逻辑日志 NOT LIKE Oralce redo logmysqlbinlog可以进行解析rep13、lication基于二进制日志可用于point-in-time的恢复安全与审计MySQL数据库体系结构MySQL数据库体系结构 数据文件组织存储引擎介绍 存储引擎分类 InnoDB MyIAM Archive Merge Fedorated Infobright Spider存储引擎介绍 InnoDBMySQL OLTP第一存储引擎 Facebook、Twitter、Google、163、taobaoMySQL 5.5开始成为默认的存储引擎Oracle仅支持InnoDB存储引擎的后续持续开发存储引擎介绍InnoDB 事务支持，Fully ACID 行级锁 Row-level locking 多14、版本并发控制 Multi-version concurrency control（MVCC）外键支持 Foreign key support 自动死锁检测 Automatic deadlock detection 高性能、高扩展、高可用 High performance,high scalability,high availabilityInnoDB physical storage 页（page）最小IO单位 区（extent）空间管理单位 段（segment）对象 表空间数据和索引合并存储一个文件，.ibd,每个表单独存储/ibdata，表定义文件.frm存储引擎介绍 MyISAMMySQ15、L 5.5之前默认的存储引擎不支持事务表锁设计 多读单写数据丢失风险高Oracle MySQL后续不再对其进行开发多核性能表现较差数据和索引单独存储.myi .myd,.frm 存储引擎介绍 AriaMyISAM后续版本由MariaDB进行维护与开发兼容原MyISAM文件支持事务与非事务选项支持提供更好的性能存储引擎介绍 Archive主要用于归档数据的保存使用zlib无损压缩算法仅支持SELECT与INSERT两种操作Oracle不再提供后续开发支持存储引擎介绍 Fedorated Like Oracle Transparent Gateway 仅支持连接MySQL数据库-federated16、 Oracle不再提供后续开发支持存储引擎介绍 Infobright 第三方存储引擎 非开源 面向列的存储方式 OLAP 腾讯、华为 培训内容一、数据库基本概念一、数据库基本概念二、二、MySQL数据库基本概念数据库基本概念三、阿里三、阿里RDS简介简介四、四、MySQL数据库安装配置数据库安装配置五、五、MySQL数据库应用开发基础数据库应用开发基础43RDS 基本概念RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务基于阿里云分布式文件系统和SSD盘高性能存储支持MySQL、SQL Server、PostgreSQL、PPAS和Mar17、iaDB引擎提供了容灾、备份、恢复、监控、迁移等方面的全套解决方案天宫平台RDS 采用MySQL 5.6 引擎44RDS 基本概念45RDS基本概念46RDS 基本概念培训内容一、数据库基本概念一、数据库基本概念二、二、MySQL数据库基本概念数据库基本概念三、阿里三、阿里RDS简介简介四、四、MySQL数据库基本操作数据库基本操作五、五、MySQL数据库应用开发基础数据库应用开发基础MySQL安装配置 下载地址：https:/ windowsfor linux MySQL客户端常用图形客户端工具：SQLyognavicat（可网上下载）命令行方式：mysql-helpMySQL安装配置MyS18、QL基本操作 mysql、mysqladmin和mysqldump MySQL管理工具和用户操作界面mysql命令 mysql命令mysql-h host_name-u user_name ppasswordh：当连接MySQL服务器不在同台主机时，填写主机名或IP地址u：登录MySQL的用户名p：登录MySQL的密码 注意:密码如果写在命令行的时候一定不能有空格。如果使用的系统为linux并且登陆用户名字与MySQL的用户名相同即可不用输入用户名密码，linux默认是以root登陆，windows默认用户是ODBCMySQL常用命令 选定默认数据库：use dbname;显示所有数据库：sh19、ow databases;显示默认数据库中所有表：show tables;放弃正在输入的命令：c 显示命令清单：h 退出mysql程序：q 查看MySQL服务器状态信息：sMySQL常用命令 创建数据库：create database dbname;创建用户：-CREATE USER user1localhost IDENTIFIED BY pass1;-GRANT SELECT,INSERT,UPDATE,DELETE ON*.*TO user1localhost;-GRANT ALL ON*.*TO user1localhost;或者 一个语句完成创建用户、授权GRANT ALL ON*.20、*TO user1localhost IDENTIFIED BY pass1;GRANT ALL ON*.*TO user210.0.23.121 IDENTIFIED BY pass2;GRANT ALL ON*.*TO user3*IDENTIFIED BY pass3;创建表create table t(a int)engine=innodb;修改表存储引擎 alter table t engine=myisam处理SQL文件 mysql命令执行存放在文件里的SQL命令格式：mysql option dbname file.sqlmysql u root p dbname bak.sql21、mysqldump u root p tmpdb backuptmp.sql备份出来的是一个文本文件培训内容一、数据库基本概念一、数据库基本概念二、二、MySQL数据库基本概念数据库基本概念三、阿里云数据库三、阿里云数据库RDS简介简介四、四、MySQL数据库基本操作数据库基本操作五、五、MySQL数据库应用开发基础数据库应用开发基础数据库开发基础事务与并发控制事务与并发控制索引设计与使用索引设计与使用SQL开发的基本原则开发的基本原则事务（Transaction）事务是数据库区别于文件系统的特征之一事务是数据库区别于文件系统的特征之一ACID特性特性原子性：事务不可拆分，要么都发生，要么都不22、发生一致性：事务将数据库从一种一致状态转变为下一种一致状态隔离性：事务未提交前对其他事务不可见持久性：事务提交后，结果是永久保存的事务与并发控制事务与并发控制并发访问有可能发生的三种现象并发访问有可能发生的三种现象脏读(dirty read):读到别人未提交的数剧不可重复读(nonrepeatable read)：已经读到的数据被删除或被修改，无法再次读取到幻象读(phantom read)：由于别人插入新记录导致再次读到的数据多于以前读到的数据事务与并发控制事务与并发控制并发事务隔离级别（并发事务隔离级别（ANSI）隔离度越高，系统并发性越差隔离度越高，系统并发性越差MySQL 默认隔离度为23、默认隔离度为REPEATABLE-READRDS默认隔离度为默认隔离度为READ-COMMITTED隔离级别隔离级别允许脏读允许脏读不可重复读不可重复读幻象读幻象读READ UNCOMMITTED允许允许允许READ COMMITTED不允许允许允许REPEATABLE READ不允许不允许允许SERIALIZABLE不允许不允许不允许事务和并发控制事务隔离度 查看当前会话的事务隔离度-show variables like%tx%-SELECT session.tx_isolation 更改当前会话的提交方式-SET tx_isolation=READ-COMMITTED-set sessi24、on.tx_isolation=READ-COMMITTED事务和并发控制事务管理 自动提交-每个DML都是一个事务，数据更改后不能撤销，默认方式 手工提交-通过commit永久保存数据更改，rollback 撤销数据更改事务和并发控制事务查看当前会话的提交方式-show variables like autocommitON=自动提交，OFF=手动提交-SELECT session.autocommit1=自动提交，0=手动提交更改当前会话的提交方式-set autocommit=OFF-set session.autocommit=0事务与并发控制事务与并发控制锁机制用于管理对共享资源的并25、发访问锁机制用于管理对共享资源的并发访问DML锁，确保任何时刻只有一人修改某行数据锁，确保任何时刻只有一人修改某行数据TX锁锁(事务锁事务锁)每个事务会获得一个每个事务会获得一个TX锁锁TM锁锁 用于确保在修改表的内容时，表结构不会用于确保在修改表的内容时，表结构不会改变改变DDL锁锁,确保确保DDL操作时操作时 对象不会被其他会话修改对象不会被其他会话修改排他排他DDL锁锁 防止结构和数据被其他会话修改防止结构和数据被其他会话修改共享共享DDL 锁锁 防止结构被其他会话修改防止结构被其他会话修改事务和并发控制并发控制通过锁实现并发控制 锁的类型-共享锁-独占锁 锁的粒度-表级锁-页级锁-行级26、锁并发控制与锁并发控制与锁锁定问题锁定问题丢失更新丢失更新悲观锁定悲观锁定乐观锁定乐观锁定阻塞（阻塞（block)死锁死锁数据库开发基础事务与并发控制事务与并发控制索引设计与使用索引设计与使用SQL开发的基本原则开发的基本原则72索引 一种数据库对象 通过指针，数据库服务使用它来提高检索数据的速度 通过快速路径来定位数据，减少磁盘的I/O 独立于其索引的表 被数据库服务自动使用和维护索引索引特点 为提高查询速度而设计 B Tree索引,平衡树,通过索引扫描定位记录 性能基本与记录数无关 INNODB 表都是索引组织表,表数据是按主键顺序排序的 二级索引叶子节点存储索引键值和主键值 INNODB27、表定义必须有主键或唯一索引,如果没有,则系统自动产生隐含主键74索引类型 唯一索引确保在所建列上只有一个唯一值（或值组合）非唯一索引提高检索速度 单列索引只有一列 复合索引有多列75建索引的方式 自动建立当定义主键或者唯一性约束时，系统自动创建唯一索引 手工用户通过建立索引，提高检索记录的速度.索引索引使用为哪些列创建索引？经常充当查询条件的列:where CHNL_CODE=91b02sp表连接中使用到的列:from RES_NUMBER_BATCH join NUMBER_BATCH_DETAIL ON RES_NUMBER_BATCH.BATCH_NO=NUMBER_BATCH_DETA28、IL.BATCH_NO频繁执行的可被索引全覆盖的列:SELECT OCCUPY_PROVINCE_CODE,OCCUPY_AREA_CODE FROM RES_LOCKING_NUMBE排序中经常用到的字段:SELECT.FROM RES_NUM_STORE WHERE PARENT_STORE_ID=17733691AND NOW()=IFNULL(eff_date,NOW()and IFNULL(exp_date,NOW()=NOW()order by STORE_TYPE那些列不建议 建索引经常被更新的列,唯一值很少的列,例如status、type 等类型的字段索引设计原则 单表索引建议29、控制在5个以内 复合索引字段数不建议超过5个 建立复合索引，建议把区分度高的字段放在前面 不建议在频繁更新或者区分度不高的字段上建立索引索引索引使用复合索引,联合索引:多个字段组成的索引只有查询条件中包含索引的第一个字段,索引才会被使用建议将最常用做查询条件的字段作为索引的第一个字段建议将选择性高的字段作为索引的第一个字段索引优化7复应用系统中索引使用常见问题索引缺失索引设计不合理,导致选择错误的索引复合索引设计不合理,导致没有使用索引索引过多,单个表上的索引建议不超过5个索引重复，一个字段上建多个索引在选择性差的字段上建立索引SQL Where 条件中 发生字段类型转换、计算等导致查询无法使30、用索引数据库开发基础事务与并发控制事务与并发控制索引设计与使用索引设计与使用SQL开发的基本原则开发的基本原则81基础原则不建议使用存储过程、视图、触发器每张核心数据表数据量建议控制在1000W以内高并发大数据的业务系统，建议解放数据库CPU，将复杂业务逻辑计算处理转移到服务层不建议在数据库内存储大文件或者大照片避开在业务高峰期批量更新、做复杂查询统计82表设计单表列数目不宜过多表必须有主键建议尽量使用和应用无关的主键，例如自增主键建议选择较短的数据类型做主键不建议用组合索引做主键不建议使用外键（外键完整性约束，由应用程序控制）选择合适的数据类型根据业务要求选择合适类型，避免类型转换根据需求选31、择合适长度如果字段内容为数字，建议用整形不用CHARCHAR和VARCHAR的选择谨慎使用 TEXT、BLOB等大数据类型 选择合适的约束类型NOT NULL的约束，NOT NULL WITH DEFAULT 唯一键约束主键约束建议所有字段定义为NOT NULL,并定义缺省值表设计了解业务开发人员和DBA都需要了解业务情况 区分sql的执行环境，是前台还是后台？评估sql的执行频率，每天千万次查询和每天几千次查询是不一样的 评估sql返回的数据量，关心每条数据还是只关心前面100条？关心所有字段信息，还是只关心真正对用户有意义的字段？评估表的数据量和增量，从而评估sql的执行效率84SQL编写32、原则 避免复杂的SQL语句，每个语句完成特定的功能 尽量避免复杂多表关联，建议尽量分解查询，通过程序传递主键值给下一个表 关联查询 选择用较小的表或结果集做驱动表 简单=高性能,高扩展性 避免不必要的工作 不必要的数据读取，例如 select*语句 不必要的join,尽量减少表之间的关联 不必要的排序Distinct、Group by、Order by、UnionSQL编写原则 select 语句禁止使用*,INSERT 语句必须明确列名 使用表的别名 连接多个表时,把表的别名前缀于每个column上 减少解析的时间以及由column歧义引起的语法错误 使用复合索引的第一个列 拆分大事务为小的事务 整合小事务，同一提交 及时commit释放占用的锁等资源SQL编写原则 Where条件中等号左边不要引用函数 Where条件中对于不同的数据类型要做显示数据转换 NOT,!=,|不会用索引 慎用 not exists 和 exists(MySQL对此类SQL处理很差)用UNION ALL代替UNION(避免排序)谢谢！谢谢！