mysql基础知识

soyizi

　　mysql基础知识
　　——以下内容摘自马哥教育
　　===
　　mysql是数据管理服务程序软件，用于管理数据。mysql是关系型数据库管理系统，C/S架构，服务端和客户端之间使用mysql协议进行通信，oracle,memcached也有自己专有的通信协议
　　===
　　mysql的关系模型是表，表由行和列组成的二维结构，是二维关系。
　　表是二维关系，表头为各种字段
　　设计表：遵循规范，建议有留空字段做备用，否则后期数据量大时需要增加一个字段将会变得非常头痛。否则需要在测试服务器上导入导出数据。
　　定义字段：索引
　　字段：字段名，使用同一风格以提高缓存命中率
　　字段的数据类型，
　　修饰符，有修饰字段类型的（比如INT），有修饰数据类型的（比如UNSIGNED）
　　约束：都可以拿来当索引，索引应该创建在经常用作查询条件的字段上，默认B+TREE索引，索引是存储引擎实现的，不同的存储引擎支持不同的索引类型。
　　===
　　索引分类
　　稠密索引和稀疏索引
　　根据索引数据结构：B+索引(默认)，hash索引，R数索引，FULLTEXT索引
　　根据索引和数据的存放位置：聚集索引和非聚集索引
　　简单索引（索引在单个字段上），组合索引（索引在多个字段上）
　　===
　　mysql中还有另一种关系是表间关系，通过外键进行关联。
　　===
　　表与表之间的连接使用笛卡尔乘积的方式，有自然连接，外连接，内连接，自连接。
　　===
　　数据库由表构成，而表内还有其他组件，如下：
　　1、索引：表中可以唯一标识该行的字段。一个表中可以存在多个索引。索引可以加快查表的速度，但减慢写表的速度。
　　2、视图：虚表，其实是一个select语句的查询结果，我们可以对虚表再次查询，类似子查询。
　　3、SQL：结构化查询语言structure query language，它分为两类：
　　DDL:数据定义语言，定义表、索引、视图
　　DML:数据操纵语言，表中的增、删、查、改
　　===
　　为增强SQL的功能，数据库提供商还会提供SQL的编程接口API，API支持过程式编程：选择、循环。用户可以调用API定义存储过程（无返回值），存储函数，触发器定义，事件调度器（定时任务）。还可以编写代码供用户调用
　　开发DBA就是专门编写存储过程，存储函数，供程序开发人员调用的岗位。管理DBA就是维护数据库正常运行，备份维护数据库等工作。
　　===
　　数据库分为3层模型，每一层对应一类用户
　　1、物理层：数据库在文件系统上的表现形式，例如一个表具体表现为一个文件
　　系统工程师需要帮助DBA把数据放在特定的存储设备上，有可能时要采用raid安全磁盘架构、分布式存储等组织形式，还需要手动管理这些文件——这是物理层完成的工作。补充说明的是，数据库借助存储引擎来完成数据在文件系统上的存储，所以使用不同的存储引擎，文件的表现形式是不一样的。
　　2、逻辑层：DBA看到的数据组织形式，如表、索引、视图等
　　而作为DBA，需要设计数据库，设计表，设计索引，优化索引，监控索引运行，设计视图，数据库的备份和恢复——这些都是逻辑层需要完成的工作
　　3、视图层：最终使用用户看到的数据展示形式。如一个库有10张表，我们授权某用户只能看到3张。或者我们授权某些用户只能看到一张表中的指定字段而看不到某些敏感字段。
　　最终用户则可以做增、删、查、改等操作——这是视图层的具体操作。但最终用户通常不会使用SQL语句来完成，而是使用一个简单的用户界面来辅助操作数据库。这个用户界面需要由程序员来完成开发。用户只需要通过点击鼠标便可触发程序通过数据库驱动连接数据库进行增、删、查、改等操作。从广义角度来看，程序员和最终用户都属于视图层。
　　===
　　关系型数据库常见的解决方案：
　　商业方案：oracle，DB2，这些公司不仅出售软件，还会出售软件相关的服务。
　　开源方案：MySQL/MariaDB, PostgreSQL, SQLite（嵌入式场景中使用，比如安卓通讯录）
　　补充：2007年sun公司收购了mysql，而sun公司又被oracle收购了，所以mysql现在由oracle来维护开发，mariadb则引入社区代码和oracle开发mysql时开源的代码。
　　===
　　mariadb的特性
　　1、mariadb借助插件式存储引擎把逻辑层的数据存储到具体的文件系统上，mariadb支持众多的存储引擎。这些存储引擎中，有的是开源的，有的是商业的，同时用户还可以自行开发存储引擎。查看mariadb支持的存储引擎命令是——SHOW ENGINES;
　　另外不同存储引擎拥有不同的特性，所以mariadb是否支持某些功能也是有存储引擎来决定的。比如mariadb是否支持事务。
　　2、mariadb采用单进程，多线程的工作模式，具体而言就是mariadb启动后只有一个进程，内部则启用多个线程分别完成mariadb的不同功能。比如连接线程用来维护用户会话连接，守护线程负责刷写内存数据到磁盘上等等。由于mariadb是单进程模型，所以不能发挥cpu多核心的优势。直到mariadb5.5支持多个cpu了，但是一个select语句也只能在单核上运行，所以复杂查询的解析速度上必然是受到限制的。同时mariadb也不能很好的使用管理大内存空间（?）。
　　===
　　mysql的配置文件
　　mysql是集中式配置文件的形式，配置文件中分为多个段落，能够为mysql的各个应用程序提供配置信息
　　[mysqld]:为mysqld应用程序提供配置
　　[mysqld_safe]:线程安全的mysqld专用配置信息
　　[mysqld_multi]:多实例模型的mysql共享的参数
　　[server]:应用于mysql服务的每个程序
　　[mysql]:mysql客户端的配置段
　　[mysqldump]:mysql备份这个数据导入/导出工具的配置段
　　[client]:对这个客户端应用程序都有效的
　　配置信息采用”parameter = value“模式
　　parameter的参数由多个单词构成，可以使用短连接线/下划线连接，如skip-name-resolve,skip_name_resolve都是可以的，但建议使用统一风格。
　　mysql启动时，支持大量的命令行参数
　　mysqld启动时，其参数可以在配置文件中指明，也可以在命令行参数中指明
　　mysqld启动时会在多个位置依次查找配置文件，顺序是：/etc/my.cnf --> /etc/mysql/my.cnf --> $MYSQL_HOME/my.cnf --> --default-extra-file=/path/to/somedir/my.cnf --> ~/.my.cnf
　　若多个配置文件中出现相同配置项，则后面的配置文件的配置项参数值生效
　　===
　　mysql的安装方法
　　1、os vender：也是rpm包安装，但是企业通常不适用此安装方法，因为企业通常需要定制mysql的某些参数或者需要更新的mysql版本。但rpm包安装方式适合大规模场景部署。
　　2、二进制版本：展开即可使用
　　3、源码编译安装：可以定制某些特性或者修改源码。可以修改源码后再做成rpm包来实现自动化部署。
　　===
　　mysql安装完成后的设定
　　mysql安装完成后需要做相关设定以加强其安全性
　　1、为所有root用户设定密码，方式如下
　　（1）mysql> SET PASSWORD 可以触发重读用户授权表
　　（2）mysql> UPDATE mysql.user SET PASSWORD=PASSWORD('your_password') WHERE clause; 不会触发重读用户授权表，需要手动执行FLUSH PRIVILEGES;
　　（3）# mysqladmin
　　2、删除所有的匿名用户
　　mysql> DROP USER ''@'localhost' ...;
　　以上步骤可以执行命令手动完成：mysql_secure_installation
　　3、建议关闭主机名反解功能
　　严重依赖dns服务的响应速度，可能会导致mysql响应速度慢
　　skip-name-resolve = on
　　===
　　mysql中有众多的元素据，比如mysql内部有哪些库，每个库有哪些表，每张表有哪些字段等等，这些元素据信息保存在mysql表中，里面包含user,host等表
　　===
　　mysql的命令行客户端工具
　　mysql的工作方式是C/S架构的，也就是mysql-->mysqld，客户端可以自行开发程序调用mysql的api来实现，其他很多的nosql也是如此
　　mysql的命令有两类，服务器端命令和客户端命令，客户端命令在客户端执行，服务器端命令需要通过mysql协议发送给服务器执行后返回结果。
　　===
　　mysql的客户端程序
　　1、mysql：交互式的命令行工具
　　2、mysqldump：mysql数据导入导出工具，它的工作原理是，基于mysql协议向mysqld发起查询请求，并把查得的所有数据转换为创建数据库、表和insert语句等写操作保存于文本文件中，所以执行该sql脚本文件即可恢复数据库中的数据。
　　3、mysqladmin：基于mysql协议完成mysqld众多管理功能的管理工具
　　4、mysqlimport：数据导入工具，导入的是被简单格式化为文本的工具
　　===
　　非客户端类的管理工具：不能基于mysql协议远程连接mysqld来进行管理，而只能在mysqld所在的服务器来使用
　　myisamchk：主要用于检查myisam表
　　myisampack：把myisam表打包节约空间存放，打包后的表只能读，可以查询数据，但不能修改数据
　　===
　　查看mysql客户端的当前启动配置
　　# mysql --print-defaults 输出如下内容
　　--port=3306 --socket=/tmp/mysql.sock --no-auto-rehash
　　--no-auto-rehash：表示禁用auto-rehash。启用该项后，mysql会对内部所有的库、表、字段做哈希处理，以实现tab补全。缺陷：每次mysql客户端连接mysql服务器时，mysql服务器都需要扫描内部组件做哈希处理，这会影响mysql客户端的连接速度，并且随着mysql-server组件的增多，mysql的连接速度就会越来越慢。
　　查看mysqld服务端的默认启动配置
　　# mysqld --print-defaults
　　参数比较多，若出现前后相同参数，后面的参数值生效
　　客户端类应用程序的可用选项：
　　-u,--user=
　　-h,--host=
　　-p,--password=
　　-P,--port=
　　--protocol=tcp
　　-S,--socket=，连接mysql有两种方式，连接套接字或者socket文件，不同主机之间可以互联套接字。主机内部才可以连接socket文件，此时-h只能是localhost。
　　===
　　mysql -uroot -p -e "SHOW DATABASES;",不连入mysql_server数据库而直接运行SQL语句
　　===
　　mysql的使用模式
　　交互式模式时，可运行命令有2类
　　客户端命令：如\h,help
　　服务器端命令：sql，需要语句结束符
　　===
　　脚本模式
　　# mysql -uUSERNAME -hHOST -pPASSWORD < /path/from/somefile.sql
　　mysql> source /path/from/somefile.sql
　　说明：mysql用户需要有权限进入指定目录下读somefile.sql
　　===
　　mysqld服务端运行参数有多种定义方式——命令行选项和配置文件参数
　　可以使用命令来获取
　　mysqld --verbose --help
　　显示了很多mysqld的配置参数，命令行参数格式使用方式，配置文件使用方式
　　获取运行中的mysqld程序使用的各个参数及其值：
　　mysql> SHOW GLOBAL VARIABLES;
　　获取全局变量，需要root权限
　　mysql> SHOW SESSION VARIABLES;
　　当前会话变量，只对当前用户的会话连接线程有效
　　在mysqld启动后，有些配置参数可以修改，有些不行（哪些可以的？）
　　mysql支持多用户连接，每个用户连接的授权是不一样的
　　修改服务器参数
　　mysql> help SET
　　全局变量修改：
　　mysql> SET GLOBAL sys_var_name=value;
　　mysql> SET @@global.sys_var_name=value;
　　会话变量修改：
　　mysql> SET SESSION sys_var_name=value;
　　mysql> SET @session.sys_var_name=value;
　　状态变量：保存着mysqld运行中的统计数据，是不能修改的
　　mysql> SHOW GLOBAL STATUS;
　　mysql> SHOW SESSION STATUS;
　　===
　　SQL模式：定义mysqld对约束等的响应行为
　　宽松模式：比如用户插入枚举型中不存在的字符，mysql可以允许插入操作，只是把不存在的字符替换为空而已。或者说，字段只允许5个字符，用户插入10个字符的数据，mysql也可以允许插入操作，但是只会保留前5个字符
　　严格模式：当插入不符合规范的行时，将被拒绝。
　　修改全局的变量不会立刻生效，重新登陆后生效，而且前提是具备相应权限才可以；
　　修改当前会话变量会立刻生效，但是重新登陆后失效
　　以上两者在重启mysql服务后失效，若要重启mysql后生效，需要写进my.cnf或相关配置文件中
　　查看SQL模式
　　SHOW GLOBAL VARIABLES LIKE 'sql_mode'
　　(SHOW GLOBAL VARIABLES LIKE 'sql_%')
　　默认模式为'',所以查询结果为空
　　修改SQL模式
　　mysql> SET GLOBAL sql_mode='MODE'
　　mysql> SET SESSION sql_mode='MODE'
　　说明如下：
　　①MODE可以是TRANITIONAL（事务型，表示？）, STRICT_TRANS_TABLES（对支持事务的严格限制）,STRICT_ALL_TABLES（对全部表格严格限制）
　　②修改全局变量不会立刻生效，而是对新建的会话生效
　　③修改会话变量只对当前会话生效，而对新建的会话无效
　　使用实例
　　CREATE DATABASE mydb;
　　use mydb;
　　CREATE TABLES t1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,name CHAR(5) NOT NULL);
　　INSERT INTO t1 (name) VALUES ('tom'),('Blackmen');
　　说明：Blackmen超出5个字符，但是依然能够正常插入数据，但是只保留前5个字符。如果此时修改sql_mode修改为TRANDITIONAL，那么违反要求的值就不能插入了，而不是截取插入。
　　SET SESSION sql_mode='TRANDITIONAL'
　　INSERT INTO t1 (name) VALUES ('tom'),('Blackmen');
　　说明：此时会报错提示
　　需要进一步学习SQL模式对插入数据的影响——查看官方文档
　　无论是全局修改还是会话修改，在重启mysqld以后都会生效，若想重启后有效，需要定义在配置文件中/或启动时指明参数。