# 数据库
## 数据库简单的分类：
（1）关系型数据库：MySQL和Oracle、Postgresql
（2）非关系型数据库：Memcached和Redis
（3）消息队列中间件
（4）搜索引擎数据库：ElasticSearch
（5）时间序列数据库：InfluxDB
（6）列数据库：Hbase
（7）图形数据库：MongoDB
系型数据库 SQL数据库
数据和数据之间是有关系的，通过关系能够将一系列数据都提取出来。关系型 数据库可以通过一条数据关联出一堆数据，如此一来，方便数据的检索和查询，提高开发人员的查询效率，但是会拖累数据库，因此关系型数据库不支持太高并发。
非关系型数据库 NoSQL数据库（Not only SQL）
数据与数据之间是没有关系的。
关系型数据库MySQL和Oracle的区别
使用方式上没有太多区别，都是就与SQL查询方式的数据库，但是Oracle是闭源的（收费），出现问题有人负责，运维人员不需要花费太多精力在Oracle中（在企业中学习）。
MySQL是开源的（免费），需要运维人员来维护，通常来说，真正的数据库维护人员职位叫做DBA（database administrator），并非广泛意义上的运维，只是数据库专业运维。
MySQL数据库最早是开源的，现在已近干杯Oracle公司（SUN）收购了，为避免SUN公司对MySQL进行闭源操作，因此他们自主在MySQL最有y一个开源版本的基础上，开发出来了一个分支数据库，叫做MariaDB，关系型数据库。其实他几乎和MySQL一样。
数据库基本概念
（1）数据
以“记录”形式统一的格式进行储存
（2）表
将不同的记录阻止在一起，形成了“表”，用来存储具体数据
|姓名|基础信息表
|--|--|
| 小红 |
|姓名|基础成绩表|
|--|--|
| 小红 |
两个数据库的表中相同的字段称为主键（表中小红就是主键）
（3）数据库
表的集合，是存储数据的仓库
（4）数据库管理系统（Database Management System DBMS）
实现对数据库资源有效组织，管理和存取的系统软件，具有以下功能：数据库的建立和维护功能，数据定义功能，数据操纵功能，运行管理功能，通信功能。
（5）数据库系统（DBS）
是一个人—机系统，由硬件、操作系统、数据库、DSMS、应用软件和数据库用户组成。用户可以通过DBMS操作数据库，也可以通过应用程序操作数据库
关系型数据系统是基于关系模型的数据库系统，它的基本概念来自于关系模型。关系模型建立在关系代数的理论基础上，数据结构使用简单易懂的二位数据表，可以用简单的“实体—关系“（E-R）图来直接表示。E-R图中包含了的实体（数据对象）、关系和属性三个要素。
## MySQL数据库介绍
### 特点
1. 性能更卓越、服务稳定
2. 开源、无版权限制、成本低
3. 多线程、多用户
4. 基于C/S（Client/Server）架构
5. 安全可靠
== B/S（Web/Server）：用户通过web浏览器打开输入域名就能访问服务器Server的方式，用户不需要安装任何东西。==
==C/S(Client/Server）：客户端Client在用户的电脑里是需要下载并安装的。 ==
## 编译安装MySQL
1. 准备工作
卸载rpm方式安装的mysql-server、mysql
```
[root@localhost ~]# rpm -qa | grep mysql
mysql-libs-5.1.73-5.el6_6.x86_64
```
若存在，用rpm -e mysql-server mysql --nodeps命令卸载
安装ncurses-devel 与cmake包
```
[root@localhost ~]# ls
anaconda-ks.cfg cmake-2.8.6.tar.gz install.log install.log.syslog mysql-5.5.22.tar.gz
[root@localhost cmake-2.8.6]# yum -y install gcc gcc-c++ make autoconf
[root@localhost ~]# yum -y install ncurses-devel
[root@localhost ~]# tar xf cmake-2.8.6.tar.gz -C /usr/src/
[root@localhost ~]# cd /usr/src/cmake-2.8.6/
[root@localhost cmake-2.8.6]# ./configure && gmake && gmake install
```
2. 源码编译安装
增加程序用户mysql
```
[root@localhost ~]# useradd -M -s /sbin/nologin mysql
```
解压 mysql-5.5.22.tar.gz
```
[root@localhost ~]# tar xf mysql-5.5.22.tar.gz -C /usr/src/
[root@localhost ~]# cd /usr/src/mysql-5.5.22/
```
cmake配置，编译及安装
```
[root@localhost mysql-5.5.22]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all && make && make install
```
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql 主程序安装目录
-DSYSCONFDIR=/etc 配置文件存放目录
-DDEFAULT_CHARSET=utf8 默认字符集为中文
-DDEFAULT_COLLATION=utf8_general_ci 默认的字符集校对规则
-DWITH_EXTRA_CHARSETS=all 安装所有字符集
3. 安装后优化操作
修改MySQL安装目录的属主与属组
```
[root@localhost local]# chown -R mysql:root /usr/local/mysql/
```
查看mysql下的目录
bin命令目录
data 数据目录
support-files 提供了许多模板（启动脚本、配置文件）
scripts 脚本（负责MySQL初始化）
创建修改my.cnf配置文件
```
[root@localhost local]# /bin/cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf
# 使用cp的绝对路径，强制覆盖，无需提醒
```
my.cnf配置文件相关介绍，在mysql解压路径support-files/下提供的模板：
my-huge.cnf 巨大数据量
my-innodb-heavy-4G.cnf innodb引擎
my-large.cnf 大数据量
my-medium.cnf 测试使用
my-small.cnf 小数据量
MySQL数据库存储引擎：
myisam
innodb
4. 添加系统服务
```
[root@localhost mysql-5.5.22]# /bin/cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost local]# chmod +x /etc/init.d/mysqld
[root@localhost local]# chkconfig mysqld --add
[root@localhost local]# chkconfig mysqld --list
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
```
5. 添加MySQL命令执行的路径到PATH环境变量（或者创建该路径的软链接）
```
[root@localhost local]# echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@localhost local]# ./etc/profile
[root@localhost local]# echo $PATH
或
[root@localhost local]# ln -s /usr/local/mysql/bin/* /usr/local/bin/
```
6.执行mysql_install_db脚本初始化数据库
数据表以文件的形式展现，每个小库的库名就是子目录的名字，子目录里面装着对应的数据文件
```
[root@localhost local]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
```
--user=mysql 指定用户身份
--basedir=/usr/local/mysql/ 指定安装目录（产品目录）
--datadir=/usr/local/mysql/data/ 指定数据目录
7. 启动mysql服务，并查看运行状态
```
[root@localhost local]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
[root@localhost local]# netstat -antup | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1750/mysqld
```
== MySQL默认端口号3306 ==
8. mysql的连接并登录操作环境
mysql -u 指定用户名
-p 指定密码
-h 指定主机（远程登录）
-P 指定端口
登录mysql
```
[root@localhost local]# mysql
或
[root@localhost local]# mysql -uroot -p
```
退出登录
quit、exit 或 Ctrl+D
设置数据库用户名密码
MySQL的账号有两部分组成：
账户名@登陆的IP地址
（远程登录用户名时，账户名和IP 地址均为远程主机的用户名和IP地址）
```
[root@localhost local]# mysqladmin -uroot password '123456'
[root@localhost local]# mysql -uroot -p123456 # 免交互式设置密码
```
强力MySQL超级用户的解决的方法
方法一：
1. 暂停mysql服务
```
[root@localhost ~]# /etc/init.d/mysqld stop
```
2. 跳过grant表授权，进入安全模式，并在后台运行
```
[root@localhost ~]# mysqld_safe --skip-grant-tables &
[root@localhost ~]# jobs
[1]+ Running mysqld_safe --skip-grant-tables &
```
3. 进入安全模式修改密码
```
[root@localhost ~]# mysql
mysql> use mysql
Database changed
mysql> update user set Password=password('123456')where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 4 Changed: 3 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
```
4. 重启mysql服务，尝试用新密码登录
```
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.181117 09:11:00 mysqld_safe mysqld from pid file /usr/local/mysql/data/localhost.localdomain.pid ended
SUCCESS!
Starting MySQL.. SUCCESS!
[1]+ Done mysqld_safe --skip-grant-tables
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> quit
Bye
```
方法二：
```
[root@localhost ~]# vim /etc/my.cnf
26 [mysqld]
27 port = 3306
28 socket = /tmp/mysql.sock
29 skip-external-locking
30 skip_grant_tables
31 key_buffer_size = 16M
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> update user set Password=password('123456')where user='root';
ERROR 1046 (3D000): No database selected
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
```
修改/etc/my.cnf配置文件,把skip_grant_tables行删掉，重启MySQL服务
```
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.22-log Source distribution
```
在MySQL数据库中开启使用tab键补全功能
1. 修改主配置文件/etc/my.cnf
```
[root@localhost ~]# vim /etc/my.cnf
[mysql]
#no-auto-rehash
auto-rehash
```
2. 重启mysql服务，登录测试
```
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22-log Source distribution
```
3. 临时支持tab键
```
[root@localhost ~]# mysql -uroot -p123456 --auto-rehash
```
使MySQL数据库支持简体中文
1. 临时支持简体中文
```
mysql> charset utf8;
```
2. 永久支持简体中文（yum方式安装的mysqld服务）
```
[root@localhost ~]# vim /etc/my.cnf
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[root@localhost ~]# /etc/init.d/mysqld restart
```
#### MySQL基础命令
create、drop对库和表进行操作
insert、updata、delete、select对表里数据进行操作（增删改查）
==查看数据库列表信息==
```
mysql> show databases;
```
==查看数据库中的数据表信息（切换数据库）==
use 数据库名；
show tables；
```
mysql> use yunjisuan;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
```
==显示数据表的结构（字段信息）==
describe 空间名.表名;
desc 空间名.表名;
```
mysql> describe yunjisuan.users;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| user_name | char(20) | NO | PRI | NULL | |
| user_passwd | char(30) | YES | | | |
+-------------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```
==create 创建新库、创建新表==
格式：create database 数据库名;
create table 表名（定义字段）;
```
mysql> create database yunjisuan;
#创建一个名为yunjisuan的数据库
mysql> use yunjisuan;
#使用yunjisuan这个数据库，将当前对象指定为yunjisuan这个库
mysql> create table users (user_name char(20) not null, user_passwd char(30) default '', primary key(user_name));
#创建表users，并添加信息，创建名为users的表，表内两个字段user_name和user_passwd，user_name 最多20个字节 不能为空，user_passwd
最多30个字节 默认为空，索引关键字（主键） user_name
```
==drop 删除库、删除表==
格式：drop table 空间名.表名;
drop database 数据库名；
```
mysql> drop database yunjisuan;
#删除一个叫做yunjisuan的库
```
```
mysql> drop table yunjisuan.users;
#删除一个yunjisuan库的users表
```
==insert 插入新数据==
格式：insert into 空间名.表名 （字段1，字段2） values（'值1'，'值2'）;
针对某个库名、表名，把某个值插入某个字段，必须一一对应
方法一：指定字段
```
mysql> use yunjisuan;
Database changed
mysql> describe yunjisuan.users;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| user_name | char(20) | NO | PRI | NULL | |
| user_passwd | char(30) | YES | | | |
+-------------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into users(user_name,user_passwd) value('daisy','123456');
mysql> insert into users (user_name,user_passwd) values('小花','123412');
mysql> insert into users(user_name) values('小然');
```
方法二：不指定字段（有几个字段写几个值，对应插入到字段内，不能少，可以指定空）
```
mysql> insert into users values('绿小花','123123');
mysql> select * from users;
+-----------+-------------+
| user_name | user_passwd |
+-----------+-------------+
| daisy | 123456 |
| 小然 | |
| 小花 | 123412 |
| 绿小花 | 123123 |
+-----------+-------------+
4 rows in set (0.00 sec)
```
==update 更新数据==
格式：update 空间名.表名 set 字段名='值' where 对应行的字段名='值';
```
mysql> select * from users;
+-----------+-------------+
| user_name | user_passwd |
+-----------+-------------+
| daisy | 123456 |
| 小然 | |
| 小花 | 123412 |
| 绿小花 | 123123 |
+-----------+-------------+
4 rows in set (0.00 sec)
mysql> update yunjisuan.users set user_passwd ='533223' where user_name='小然';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from users;
+-----------+-------------+
| user_name | user_passwd |
+-----------+-------------+
| daisy | 123456 |
| 小然 | 533223 |
| 小花 | 123412 |
| 绿小花 | 123123 |
+-----------+-------------+
4 rows in set (0.00 sec)
```
==对账户设置加密密码==
```
mysql> update yunjisuan.users set user_passwd =password('533223') where user_name='小然';
```
多个条件，并且“and”，或者“or”，不等于“！=”，
==delete 删除不需要的数据==
格式：delete 空间名.表名 where 字段1='值1';
```
mysql> delete from yunjisuan.users where user_name='小花';
Query OK, 1 row affected (0.00 sec)
mysql> select * from users;
+-----------+-------------+
| user_name | user_passwd |
+-----------+-------------+
| daisy | 123456 |
| 小然 | 533223 |
| 绿小花 | 123123 |
+-----------+-------------+
3 rows in set (0.00 sec)
```
==select 查看数据==
格式：select 字段名 from 空间名.表名；
select 字段名 from 空间名.表名 where 条件表达式；
（空间名就是小库名 *代表所有字段）
查看mysql.user表里的user，host，password字段的所有数据
```
mysql> select user,host,password from mysql.user;
```
将横向显示变为纵向\G
```
mysql> select * from mysql.user\G
```
查看yunjisuan.users表里的所有信息
```
mysql> select * from yunjisuan.users;
+-----------+-------------+
| user_name | user_passwd |
+-----------+-------------+
| daisy | 123456 |
| 小然 | 533223 |
| 绿小花 | 123123 |
+-----------+-------------+
3 rows in set (0.00 sec)
```
查看yunjisuan.users表里的user_name的信息
```
mysql> select user_name from yunjisuan.users;
+-----------+
| user_name |
+-----------+
| daisy |
| 小然 |
| 绿小花 |
+-----------+
3 rows in set (0.00 sec)
```
查看yunjisuan.users表里的user_name为daisy的所有信息
```
mysql> select * from yunjisuan.users where user_name='daisy';
+-----------+-------------+
| user_name | user_passwd |
+-----------+-------------+
| daisy | 123456 |
+-----------+-------------+
1 row in set (0.00 sec)
```
==添加列、删除列==
删除列， 以下两种方式都可以
alter table 表名 drop column 删除的列名;
alter table 表名 drop 删除的列名;
添加列，必须指定列的类型
alter table 表名 add 列名 varchar(10);
==grand 用户权限设置==
1. 设置用户权限（用户不存在时，则新建用户）
```
mysql> grant all on *.* to 'yunjisuan'@'192.168.214.149' identified by '123456';
mysql> grant select on mysql.user to 'daisy'@'192.168.214.150' identified by '123456';
```
grant all on 授予所有权限（select、delete、update、insert等）
all指查看的类型；
第一个*是指所有的数据库；
第二个是指所有的表
'root'@'192.168.124.234' 账号
123456 密码
grand 创建在server端
MySQL的client需要安装mysql安装包（包为mysql，用于客户端的安装）
排错：
```
mysql> grant all on *.* to 'yunjisuan'@'192.168.214.149' identified by '123456';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'yunjisuan'@'192.168.214.149' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+-----------+-----------------------+
| user | host |
+-----------+-----------------------+
| root | 127.0.0.1 |
| yunjisuan | 192.168.214.149 |
| root | ::1 |
| | localhost |
| root | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+-----------+-----------------------+
7 rows in set (0.00 sec)
```
==查看用户权限==
```
mysql> show grants;
mysql> show grants for 'daisy'@'192.168.214.150';
mysql> show grants for 'root'@'localhost';
```
远程登录 'daisy'@'192.168.214.150'账户
```
[root@localhost ~]# mysql -u daisy -p 123456 -h192.168.214.150;
```
==撤销用户权限==
```
mysql> mysql> revoke select on mysql.user from 'daisy'@'192.168.214.150';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
```
MySQL通配符：
_ 任意单个字符
% 任意长度的任意字符
```
mysql> grant select on *.* to 'daisy'@'%' identified by '123456';
mysql> flush privileges;
```
==显示广泛的服务器状态信息==
show status；
==显示创建特定数据库或表==
help create database;
help create tables;
==显示授权用户的安全权限==
show grants；
==显示服务器错误或警告信息==
show errors;
show warnings;
==显示当前连接用户==
mysql> select user();
==显示当前时间==
mysql> select now();
==显示当前用户及时间==
mysql> select user(),now();
mysql> select CURRENT_USER(),CURRENT_TIMESTAMP;
==显示当前数据库==
mysql> select database();
==显示服务器状态==
mysql> status；
==数据库的备份与恢复==
1. 可直接备份目录 /var/local/mysql/var
2. 使用专用备份工具 mysqldump
==备份操作==
mysqldump -u 用户名 -p 密码 选项 空间名 表名 > /备份路径/备份文件名
常见选项：
-all-databases
--opt
```
[root@localhost ~]# mysqldump -u root -p mysql user > mysql-user.sql
Enter password:
[root@localhost ~]# mysqldump -u root -p --databases daisy > daisy.sql
Enter password:
[root@localhost ~]# mysqldump -u root -p --opt --all-databases > all-data.sql
Enter password:
```
恢复操作
mysql -u root -p 空间名 < /备份路径/备份文件名
```
[root@localhost ~]# mysql -u root -p daisy < mysql-user.sql
Enter password:
```
## 数据库索引
1. 数据库索引
在数据库中，索引使数据库程序无需对整个表进行扫描，就可可以在其中找到所有数据。数据库的索引是某个表中一列或者若干列值的集合，以及物理标识这些值的数据页的逻辑指针清单。
2. 作用
（1）设置了合适的索引之后，数据库利用各种快速的定位技术，能够大大加快查询速率，特别是当表很大时，或者查询涉及到多个表时，使用索引可是查询加快成千倍。
（2）可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。
（3）通过创建唯一性索引保证数据表数据的唯一性，可以加快表与表之间的连接。
（4）在使用分组和排序时，可大大减少分组和排序时间。
4. 分类
（1）普通索引
这是最基本的索引类型，而且没有唯一性之类的限制
（2）唯一性索引
与普通索引基本相同，区别于：索引列的所有值都只能出现一次，即必须唯一，但可以为空。
（3）主键
是一种特殊的唯一索引，必须指定为“PRIMARY KEY”，既有唯一性的同时不能为空
（4）全文索引
在MySQL中，全文索引的类型为FULLTEXT，全文索引可以在VARCHAR或者TEXT类型的列上创建。贴吧的文本内容，和一些小型的网站网页内容，存放在数据库中即为全文索引模式。
（5）单列索引与多列索引
索引可以是单列上创建的索引，也可以是多列上创建的索引。
==创建普通索引==
格式：create index <索引的名字> on 表名(字段);
```
mysql> create index salary/-index on IT_salary(薪资);
```
==创建唯一性索引==
格式：create unique index <索引的名字> on 表名(字段);
```
mysql> create unique index salary/-index on IT_salary(薪资);
```
==创建主键索引==
格式：create table 表名([...],primary key(字段));
若在新建表时忘记创建主键，使用如下命令：
alter table 表名 add primary key(字段));
```
mysql> create table users (user_name char(20) not null, user_passwd char(30) default '', primary key(user_name));
#创建表users，并添加信息，创建名为users的表，表内两个字段user_name和user_passwd，user_name 最多20个字节 不能为空，user_passwd
最多30个字节 默认为空，索引关键字（主键）
```
==查看索引信息==
格式： show index from 表名;
show key from 表名;
```
mysql> show index from IT_salary \G;
或
mysql> show key from IT_salary \G;
```
==删除索引==
格式：drop index <索引的名字> on 表名;
alter table 表名 drop index <索引的名字>；
alter table 表名 drop primary key;
```
mysql> drop index salary/-index on IT_salary;
```
### 事务
1. 事务时一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库要么都执行，要么不都执行。
事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务时最小的控制单元。
事务适合于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。
2. 事务的ACID特点
事务既有四个属性：ACID
（1）原子性（Atomicity）
事务是一个完整的操作，事务的各元素是不可分的（原子的），事务的所有元素必须做为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。
（2）一致性 （Consistency）
当事务完成时，数据必须处于一致状态：在事务开始之前，数据库汇总存储的数据处于一致状态；在正在进行的事务中，数据可能处于不一致的状态；当事务完成时，数据必须再次回到一致的一致状态。
（3）隔离性（Isolation）
对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应该以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。
（4）持久性（Durability）
事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。一旦事务被提交，事物的效果会被永久地保留在数据库中。
==事务处理命令==
begin 开始一个事务（交互式数据存在内存里）
commit 提交一个事务（在内存的数据提交到硬盘，非交互式能显示数据）
rollback 回滚一个事务（撤销），从begin开始的所有命令都被撤销（将在内存中的数据返回到源位置，不显示在硬盘，结束事务）
交互式命令保留在内存里，未写入硬盘可以撤销，等待事务
非交互式命令保留在硬盘里
==查看系统内变量autocommit的信息==
```
mysql> show variables like ‘autocommit’;
```
关闭后台提交，禁止自动提交（临时生效）
```
mysql> set autocommit=0;
```
开启自动提交（临时生效）
```
mysql> set autocommit=1;
```
### MySQL数据库存储引擎
##### 存储引擎概念介绍
MySQL中的数据用各种不同的技术存储在文件系统中，每一种技术都是用不同的存储机制、索引技巧，锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎。
存储引擎就是MySQL将数据存出在文件系统中的存储方式或者存储格式
目前MySQL常用的两种存储引擎：MyISAM、InnoDB
MySQL存储引擎是MySQL数据库服务器中的组件，负责为数据库执行实际的数据I/O操作，使用特殊存储引擎的主要优点之一在于，仅需提供特殊应用所需要的特性，数据库中的系统开销较小，具有更有效和更高的数据库性能。
MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储。
#### MyISAM介绍
1. MyISAM的概述
MyISAM存储引擎是MySQL关系数据库系统5.5版本之前默认的存储引擎，前身是ISAM。
ISAM是一个定义明确且精力时间考验的数据表格管理方法，在设计只是就考虑到数据库被查询的次数要远大于更新的次数。
ISAM的特点：ISAM执行读取操作的速度很快，而且占用不大量的内存和存储资源，它不支持事无出路，不能够容错。
MyISAM管理非事务表，是ISAM的扩展格式，提供ISAM里所没有的索引和字段管理的大量功能。
MyISAM使用一种表格锁定的机制，已优化多个并发的读写操作。MyISAM提供高速存储和检索，以及全文搜索能力，受到web开发的青睐。
2. MyISAM的特点（==读取数据，对服务器资源占用低==）
（1）==不支持事务==（不支持复杂功能）
（2）==表级锁定形式==，数据在更新时锁定整个表（一旦写入数据，引擎直接对数据表进行表级锁定，其他用户不能再写也不能再读，==不支持并发写==）（==读支持好==）
（3）数据库在读写过程中相互阻塞（读的时候不能写）
（4）可以通过key_buffer_size来设置（写）==缓存索引==，提高访问性能，减少磁盘IO的压力，但缓存只会缓存索引文件，==不会缓存数据==（可以缓存索引，但是不缓存数据，内存占用小）
（5）采用MyISAM存储引擎数据单独写入或读取，速度过程较快而且占用资源相对较少。
（6）MyISAM存储引擎不支持外键约束，只支持全文索引
（7）每个MyISAM在磁盘上存储成三个文件，每一个文件的名字以表的名字开始，扩展名指出文件类型。
.frm 文件存储表定义
.MYD 文件存储数据（MYData）
.MYI 文件存储索引文件（MYIndex）
3. MyISAM使用的生成场景
（1）公司业务不需要事务支持
（2）一旦单方读取数据比较多的业务，或单方面写入数据较多的业务，如：www.blog，图片信息数据库，用户数据库，商品库等业务，MyISAM存储引擎读写都比较频繁的场景不合适。
（3）对数据业务一致性要求不是非常高的业务
（4）使用读写并发访问相对较低的业务
（5）数据修改相对较少的业务
（6）服务器硬件资源相对比较差
#### InnoDB介绍
1. InnoDB的特点（==写入数据，对服务器资源占用高==）
（1）==支持事务==：支持4个事务隔离级别
（2）==写入数据行级锁定==，但是全表扫描仍然会是表级锁定，单表可以==支持并发写==（写支持好）
（3）读写阻塞与事务隔离级别相关
（4）既有非常高效的缓存特性：==能缓存索引，也能缓存数据==（占用内存大，对服务器要求高）
（5）表与主键以簇的方式存储
（6）支持分区、表空间，类似Oracle数据库
（7）支持外检约束，5.5以前不支持全文索引，5.5版本以后指出全文索引
（8）对硬件资源要求比较高
2. InnoDB使用的生成场景
（1）业务要求事务的支持
（2）行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引来完成
（3）业务数据更新较为频繁的场景，如：论坛，微博
（4）业务数据一致性要求较高，如：银行业务
（5）硬件设备内存较大，利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO的压力。
==主从架构：从数据库：MyISAM可以用来读，主数据库：InnoDB也已用来写==
#### 企业选择存储引擎的依据
1. 需要考虑每个存储引擎提供了哪些不同的核心功能及应用场景
2. 支持的字段和数据类型
所有引擎都支持通用的数据类型，但不是所有引擎都支持其他的字段类型，如二进制对象。
3. 锁定类型：不同的村粗引擎指出不同级别的锁定
表锁定：MyISAM、MEMORY
行锁定：InnoDB
页锁定：BDB
4. 索引的支持
建立索引在搜索和恢复数据库中的数据的时候能显著提高性能
不同的存储引擎提供不同的制作索引的技术
有些存储引擎根本不支持索引
5. 事务处理的支持
事务处理功能通过提供在向表中更新和插入信息期间的可靠性
可根据企业业务是否要支持事务选择存储引擎
#### 配置合适的存储引擎
==查看数据库可配置的存储引擎==
```
mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)
```
==查看表正在使用的存储引擎==
（1）查看当前MySQL的默认引擎
（2）查看表使用的存储引擎
方法一：show table status from 库名 where name='表名';
```
mysql> show table status from yunjisuan where name='users'\G;
*************************** 1. row ***************************
Name: users
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 9437184
Auto_increment: NULL
Create_time: 2018-11-19 14:15:01
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
# 非系统用户的表的引擎为InnoDB
mysql> show table status from mysql where name='user'\G;
*************************** 1. row ***************************
Name: user
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 11
Avg_row_length: 92
Data_length: 1012
Max_data_length: 281474976710655
Index_length: 2048
Data_free: 0
Auto_increment: NULL
Create_time: 2018-11-19 14:15:01
Update_time: 2018-11-19 14:15:01
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: Users and global privileges
1 row in set (0.00 sec)
# MySQL系统表mysql.user的引擎为MyISAM引擎
```
方法二：show create table 表名;
（查看建表时的命令）
```
mysql> show create table yunjisuan.users \G;
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`user_name` char(20) NOT NULL,
`user_passwd` char(30) DEFAULT '',
PRIMARY KEY (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
```
3. 配置存储引擎为所选择的类型
方法一：alter table 表名 engine=引擎;
```
mysql> alter table yunjisuan.users engine=myisam;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table yunjisuan.users \G;
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`user_name` char(20) NOT NULL,
`user_passwd` char(30) DEFAULT '',
PRIMARY KEY (`user_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
```
方法二：修改my.cnf的default-storage-engine为引擎
```
[root@localhost ~]# vim /etc/my.cnf
[mysql]
#no-auto-rehash
auto-rehash
default-storage-engine = InnoDB
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@localhost ~]# mysql -uroot -p123456 -e 'show engines\G;'
```
方法三：create table 建立表时使用 engine=引擎
```
mysql> create table daisy(id int) engine=myisam;
mysql> show create table yunjisuan.daisy;
+-------+-----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------+
| daisy | CREATE TABLE `daisy` (
`id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
方法四：mysql_convert_table_format --user=root --password=密码 --sock=文件路径/mysql.sock --engine=引擎 空间名 表名
### MMM实现MySQL高可用
#### MMM介绍
MMM（Masterr-Master Replication Manager for MySQL）MySQL主主复制管理器。MMM是一套灵活的脚本程序，基于perl实现，用来对mysql replication进行监控和故障迁移，并能管理mysql Master-Master复制的配置（同一时间只有一个节点时可写的）
MMM的监管端是会人提供多个虚拟IP（VIP），包括一个可写VIP，多个可读VIP，通过监管的管理。这些IP会绑定在可用mysql之上，当某一台mysql宕机时，会将VIP迁移至其他mysql。
mmm_mond：监控进程，负责所有的监控工作，决定和处理所有节点角色活动。因此脚本需要在监管机上运行。
mmm_agentd：运行在每个mysql故武器上的代理进程，完成监控的探针工作和执行简单的远端服务设置。此脚本需要在被监管机上运行。
mmm_control：一个简单的脚本，提供管理mmm_mond进程的命令。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
