前言
MySQL/MariaDB是一个开放源码的小型关联式数据库管理系统,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL/MariaDB作为网站数据库。
基础架构
MySQL核心组件
连接池:认证、线程重用、连接数限制、内存检查、缓存
SQL接口:DDL, DML, 关系型数据库的基本抽象
parser: 查询转换、对象权限检查
优化器:访问路径,性能相关的统计数据
caches和buffers:与存储引擎自身相关的I/O性能提升工具
存储引擎:MyISAM、InnoDB(变种:XtraDB)、Memory、Merge、Federated、CSV、Archive、Blackholl、Aria、SphinxSE、TokuDB
内部结构
程序类别及命令选项
服务器端程序:启动并监听于套接字上;mysqld, mysqld_safe, mysqld_multi
客户端程序:可通过mysql协议连入服务器并发出请求的;mysql, mysqlbinlog, mysqladmin, mysqldump等
工具程序:运行于服务器进程所在的主机,实现一些管理或维护操作,myisamchk
客户端程序通用选项
-u,--user #指定登陆用户 -h,--host #指定登陆主机 -p,--password #指定登陆密码 --protocol={tcp|socket|memory|pipe} #指定协议 -P,--port #指定端口,默认监听端口:tcp/3306 --socket #指定本地连接的sock文件,相当于--protocol socket --compress #数据传输采用压缩格式 -D,--database #指定连接后默认使用的数据库 -H,--html #指定产生html输出 -X,--xml #指定产生xml输出 --safe-updates #拒绝使用无where子句的update或delete命令
客户端程序命令
交互式模式: #客户端命令 mysql> help #列出所有命令 mysql> \? #相当于help mysql> \c #取消命令执行 mysql> \g #发送命令至服务器端 mysql> \G #发送命令至服务器端,垂直显示结果 mysql> \q #退出 mysql> \! #运行shell命令 mysql> \s #显示服务器端状态信息 mysql> \. /path/to/somefile.sql #批量执行sql mysql> \u #将指定的库设为默认库 #服务器端命令 mysql> help KEYWORD 获取关键字的帮助信息 批模式: mysql < /path/from/somefile.sql
管理工具mysqladmin
格式:mysqladmin [options] command [arg] [command [arg]] ... #常用命令 create DB_Name:创建数据库 drop DB_Name:删除数据库 debug:打开调试日志并记录于error log中 status:显示简要状态信息 --sleep #间隔秒数 --count #显示的次数 extend-status: 显示mysqld的所有服务器状态变量 flush-privileges: 刷新授权表,相当于reload命令 flush-hosts: 清除dns缓存及被拒绝的客户端列表缓存 flush-logs: 滚动日志, 二进制日志和中继日志 flush-status: 重置各状态变量 flush-tables: 关闭当前打开的所有的表文件句柄; flush-treads: 重置线程缓存; password: 设置密码 ping: 测试服务器是否在线 processlist: 显示当前服务器上的所有线程 refresh: 相当于执行flush-hosts和flush-logs shutdown: 关闭服务器进程 ; start-slave, stop-slave: 启动、关闭从服务器线程; variables: 显示服务器变量
功能特性补充
命令行编辑功能
Ctrl+a: 快速移动光标至行首 Ctrl+e: 快速移动光标至行尾 Ctrl+w: 删除光标之前的单词 Ctrl+u: 删除行首至光标处的所有内容 Ctrl+y: 粘贴使用Ctrl+w或Ctrl+u删除的内容
提示符
mysql> #等待输入命令 -> #续行 ‘> #还需补全后半部单引号 “> #还需补全后半部双引号 `> #还需补全后半部反引号 /*> #注释,不执行,需以*/结束注释
SQL语言的组成部分
DDL:数据定义语言
DCL:数据控制语言,如授权
DML:数据操作语言
完整性定义语言:DDL功能性约束(主键、外键、惟一键、条件、非空、事务)
视图定义:虚表,存储下来的select语句
事务控制
数据字典
数据字典:系统编目(system catalog)
保存了数据库服务器上的元数据
元数据
关系的名称 每个关系中各字段的名称 各字段的类型和长度 约束 每个关系上的视图的名字及视图的定义 授权的用户名字 用户的授权和帐户信息 统计类数据 每个关系中字段数; 每个关系中行数; 每个关系的存储方法;
保存元数据的数据库
infomation_schema mysql performance_schema
数据类型及属性修饰符
数据类型的功用
①存储的值类型
②占据的最大存储空间
③定长、变长
④如何被索引和排序
⑤是否能够被索引
数据类型
字符型
CHAR、VARCARH、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOG、LONGBLOB 修饰符: NULL:允许为空 NOT NULL:不允许为空 DEFAULT 'string':默认值,不适用于TEXT CHARACTER SET 'set':设定字符集 SHOW CHARACTER SET;查看可用字符集 COLLATION 'collation':设定字符集排序规则 SHOW COLLATION;查看可用排序规则 DEFAULT:不用于BLOB类型 字符有通配符: %: 匹配任意长度的任意字符 _: 匹配任意单个字符;
整型
TINYINT、SMALLINT、MEDIUMINT 、INT、BININT 修饰符: UNSIGNED:无符号 NULL NOT NULL DEFAULT AUTO_INCREMENT: 自动增长 #特殊要求:非空,且必须是主键或惟一键
浮点型
FLOAT、DOUBLE 修饰符: NOT NULL NULL DEFAULT UNSIGNED
布尔型
没有专用布尔型,其是TINYINT(1)的别名
日期时间型
DATE、TIME、DATETIME、TIMESTAMP、YEAR(2)、YEAR(4) 修饰符: NULL NOT NULL DEFAULT VALUE
内建类型
ENUM:枚举,表示仅能从给出的选项选择其中一个 ENUM('string1','string2') SET:集合, 表示能使用给出的元素组合成字符串 SET('a','b','c') 修饰符: NULL NOT NULL DEFAULT ''
SQL模式及服务器变量
sql模式:用来限定mysqld的工作特性
TRADITIONAL:传统模式
STRICT_TRANS_TABLES:对支持事务的表使用严格模式
STRICT_ALL_TABLES:对所有表使用严格模式
服务器变量的类型
全局:对所有会话都生效
所有的会话在建立时都从全局继承,但继承完成后每个会话独立维护自己会话级变量
修改全局的服务器变量仅对之后建立的会话生效
要求有管理权限
会话:仅对当前会话有效
修改即刻生效
不要求管理权限
修改方式
动态修改: 会话级别,立即生效;全局级别,新建立的会话有效,重启服务会失效
静态修改:要修改配置文件,或修改传递给mysqld的选项的值,重启后有效
注:并非所有的服务器变量都支持动态修改
查看服务器变量
mysql> SHOW {GLOBAL|SESSION} VARIABLES [LIKE clause]; mysql> SELECT @@{GLOBAL|SESSION}.VARIABLE_NAME; mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME=''; mysql> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='';
修改服务器变量
动态修改变量的值: mysql> SET {GLOBAL|SESSION} VARIABLE_NAME='VALUE' 让设置永久有效的方式: [mysqld] sql_mode = 'STRICT_ALL_TABLES'
语句书写大小写说明
①SQL关键字及函数名不区字符大小写
②数据库、表、索引及视图的名称是否区分大小写取决于低层的OS及FS
③存储过程、存储函数及事件调度器不区分字符大小写,但触发器区分
④表别名不区分大小写
⑤字段中字符数据,类型为binary、blog、varbinary时区分大小写,其它的不区分
DDL操作
数据库操作
创建数据库 CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name 删除数据库 DROP {DATABASE | SCHEMA} [IF EXISTS] db_name 修改数据库 ALTER {DATABASE | SCHEMA} [IF EXISTS] db_name
表操作
创建表 CREATE TABLE [IF NOT EXISTS] tb_name (col1_def,col2_def,PRIMARY KEY(col_name,...),UNIQUE (col1,...),INDEX (col1,...)) [table_option] table_option: ENGINE [=] engine_name COMMENT [=] 'string' ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name #复制表数据 [(create_definition,...)] [table_options] select_statement CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name #复制表结构 { LIKE old_tbl_name | (LIKE old_tbl_name) } 删除表 DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE] #cascade会将有依赖关联的表一并删除 修改表 ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...] alter_specification: #插入新字段 ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ] #删除字段 DROP [COLUMN] col_name #修改字段属性 ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} #修改字段名 CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] #修改字段类型及属性等 MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] 修改表名 ALTER TABLE tb_name RENAME TO new_tb_name; RENAME TABLE old_name TO new_name; #指定排序字段 ORDER BY col_name [, col_name] ... #转换字符集及排序规则 CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
表结构之索引
索引:特殊的数据结构,用于快速找到数据记录(行) 注:键可用作索引;并非所有索引都是键 索引类型:B-Tree (B+ Tree) (左前缀), hash (key-value), R-Tree, FullText 索引的优点: 大大减少服务器需要扫描的数据量 索引可以帮助服务器尽量避免进行排序及使用临时表 索引可以将随机I/O转换为顺序I/O 索引可以创建在一个字段,也可是多个字段上:简单索引、组合索引 例子:索引(姓名,性别) where name like 'tom%' where gender = 'female' where familyname = 'tom' and gender = 'female' B-Tree索引的适用场景: 全值匹配:比较操作符 =, <=> 左前缀匹配:LIKE 'tom%' 列前缀匹配 匹配范围值: 组合索引类型中,精确匹配前一列,并范围匹配后一列 只访问索引的查询:覆盖索引,从索引就可以直接得到最终结果 哈希索引适用场景: 哈希索引只包含哈希值和行指针;不是按照索引值顺序存储,无法用于排序;不支持部分索引匹配查找 哈希索引只支持等值比较查询,包含=, IN(), <=> 聚簇索引:索引和数据一起存放; 数据文件:索引顺序文件 非聚簇索引:索引和数据分开存放,而数据记录未必顺序存放;但索引数据一般顺序存放; 数据文件:堆文件 添加索引 ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ... CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_option] ... 删除索引 ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name DROP {INDEX|KEY} index_name DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name 查看索引 SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr] 索引创建的基本法则:基于搜索键来创建索引 SELECT的WHERE子句的查询条件中的字段 索引的使用策略: 1、要使用独立的列:索引列不是表达式的一部分; SELECT Name FROM students WHERE Age + 2 > 32; 2、前缀索引 索引选择性:单独的索引项与数据表中的记录的总数的比值;取值范围:1/n-1; books, book 3、多列索引 查询条件多用AND,则建议使用多列索引; 查询条件多用OR,建议使用多个简单索引; 4、合适的次序:将选择性高的列写在最左侧; (Name, Gender) 5、聚簇索引: 好处:数据访问更快; 缺点:更新聚簇索引列的代价很高; 6、覆盖索引 SELECT Name FROM students WHERE Name LIKE'tom%'; 7、避免冗余索引 name, (name,gender)
The end
MySQL/MariaDB的知识就先说到这里了,后续文章还会继续讲解MySQL/MariaDB的相关知识,有兴趣可以继续关注。以上仅为个人学习整理,如有错漏,大神勿喷~~~
转载于:https://blog.51cto.com/scholar/1642286