一.mariadb的安装与配置
(1)mariadb的安装
1.虚拟机上的base源yum安装
yum install mariadb-server
2.在www.mariadb.org绿色安装gz格式的包，然后解压即可
现在常用5版本的mariadb
(2)mariadb的相关配置
- 首先通过命令my_print_defaults --verbose来查看对应加载时读取文件配置的顺序
-
在开启mariadb服务之前先对其配置文件进行简单的配置与定义
vim /etc/my.cnf.d/server.cnf
在[server]下定义
skip_name_reslove = ON——跳过域名解释，节省时间
innodb_file_per_table = ON——每个表使用单独的表空间文件
max_connection = 20000——最大并发连接数的设置
搜狗截图20171109090500.png
创建的数据库的数据一般多会放在/var/lib/mysql下
例如在数据库中创建一个名为hellodb的数据库时，cd /var/lib/mysql中就会看见一个名为hellodb的数据库
搜狗截图20171109091037.png
-
配置好后开启数据库服务
systemctl start mariadb——对应监听端口为3306
通过mysql命令进入数据库，SHOW ENGINES;来查看存储引擎的类型，默认为innodb类型。
搜狗截图20171109095740.png
或是SHOW ENGINE INNODB STATUS;来查看引擎的状态
注意：在centos7版本以后当使用SHOW ENGINE MYISAM STATUS命令时，虽然可以查看但是只能看见一个空的值
搜狗截图20171109091653.png
二.有关innodb存储引擎的介绍
(1)数据存储于“表空间(table space)"中：
(2) 所有数据库中的所有类型为InnoDB的表的数据和索引存储于同一个表空间中；
表空间文件：datadir定义的目录中
文件：ibdata1, ibdata2, ...
(3) innodb_file_per_table=ON，意味着每表使用单独的表空间文件；
每表的数据文件（数据和索引，存储于数据库目录）存储于自己专用的表空间文件中，并存储于数据库目录下: tbl_name.ibd
表结构的定义：在数据库目录，tbl_name.frm
在数据库库中创建一个数据库
create database hellodb character set 'utf8';
use hellodb
create table mydb1(id int ,name char(20)) engine=innodb;
创建成功后在/var/lib/mysql中cd 到hellodb中查看
搜狗截图20171109100500.png
事务型存储引擎，适合对事务要求较高的场景中；但较适用于处理大量短期事务；
基于MVCC（Mutli Version Concurrency Control）支持高并发；支持四个隔离级别，默认级别为REPEATABLE-READ；间隙锁以防止幻读；
使用聚集索引（主键索引）；
支持”自适应Hash索引“；
(4)锁粒度：
1.行级锁——当用户在修改某一行的时候，加上行级锁，其他用户不能读也不能写，当用户在读某一行的时候，其他用户只能读不能写
2.间隙锁——是其他用户不能再两行之间加入新的行
(5)总结：
数据存储：表空间；
并发：MVCC，间隙锁，行级锁；
索引：聚集索引、辅助索引；
性能：预读操作、内存数据缓冲、内存索引缓存、自适应Hash索引、插入操作缓存区；
备份：支持热备；
(6)表创建后的查看
进入hellodb数据库
show table status/G——查看所有表
show table status like 'my'\G——查看my开头的表
show table status where engine = 'innodb'\G——查看存储引擎为innodb的表
(7)myisam存储引擎的介绍
1.支持全文索引（FULLTEXT index）、压缩、空间函数（GIS）；
2.不支持事务
3.锁粒度：表级锁
4.崩溃无法保证表安全恢复
5.适用场景：只读或读多写少的场景、较小的表（以保证崩溃后恢复的时间较短）；
6.文件：每个表有三个文件，存储于数据库目录中
tbl_name.frm：表格式定义；
tbl_name.MYD：数据文件；
tbl_name.MYI：索引文件；
在mysql中创建一个数据库来查看
create database myisamdb;
use myisamdb;
create table myuser(id int,name char(30)) engine='MYISAM';
在/var/lib/mysql/myisamdb查看
搜狗截图20171109102324.png
7.特性：
加锁和并发：表级锁；——当用户修改表中的某个行的时候整个表其他用户都不读和写
修复：手动或自动修复、但可能会丢失数据；
索引：非聚集索引；
延迟索引更新；
表压缩；
三.有关锁机制介绍
(1)锁类型
读锁：共享锁，可被多个读操作共享——能读，不能写
写锁：排它锁，独占锁——其他用户既不能读也不能写
(2)锁粒度
表锁：在表级别施加锁，并发性较低
行锁：在行级别施加锁，并发性高，维持锁状态的成本较大
(3)锁策略
在锁粒度级数据安全性之间寻求一种平衡机制
存储引擎：级别以及何时施加或释放锁由存储引擎自行决定
Mysql Server:表级别，可自行决定，也允许显示请求
(4)锁类别
显示锁：用户手动请求的锁
隐式锁：存储引擎自行根据需要施加的锁
(5)显示锁的使用
- LOCK TABLES
示例：
use hellodb;
DESC mydb1;
搜狗截图20171109110852.png
LOCK TABLES mydb1 write;——将表mydb1设置写锁，属于排他锁，自己可以读，但不能写，而其他用户不能写也不能读
搜狗截图20171109111344.png
执行unlock tables mydb1;对方才能执行读写命令搜狗截图20171109111503.png
LOCK TABLES mydb1 read ;-设置读锁
搜狗截图20171109111653.png
解锁后才可以执行搜狗截图20171109111755.png
- FLUSH LOCK
直接将hellodb中所有的表都做了锁机制
例： FLUSH TABLES WITH READ LOCK;
搜狗截图20171109112701.png
unlock tables ;
搜狗截图20171109112748.png
四.事务
(1)事务：一组原子性的SQL的查询或是一个多个SQL语句组成的独立的工作单元
(2)事务日志
innodb_log_files_in_group
innodb_log_group_home_dir
innodb_log_file_size
innodb_mirrored_log_groups
(3)ACID测试
A：AUTOMICITY，原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚；
C：CONSISTENCY，一致性；数据库总是应该从一个一致性状态转为另一个一致性状态；
I：ISOLATION，隔离性；一个事务所做出的操作在提交之前，是否能为其它事务可见；出于保证并发操作之目的，隔离有多种级别；
D：DURABILITY，持久性；事务一旦提交，其所做出的修改会永久保存；
(4)事务的自动提交以及手动控制
1.自动提交事务：
select @@autocommit;——设置为自动提交
如果想关掉自动提交服务就要设置为
set @@session.autocommit=0;
2.手动控制事务
启动：START TRANSACTION
提交：COMMIT
回滚：ROLLBACK
事务特性：支持savepionts——类似做快照，建立存储点
3.事务隔离级别
READ-UNCOMMITTED:读未提交——脏读；
READ-COMMITTED:读可提交——不可重复读；
REPEATABLE-READ:可重复读——幻读；
SERIALIZABLE:串行化
以上命令通过实验来进行验证以便理解
(1)进入hellodb数据库中
(2)关闭自动提交 set @@session.autocommit=0;
(3)设置隔离级别 set @@session.tx_isolation="READ-UNCOMMITTED";
(4)开启事务 start transaction;
(5)以上命令在两个虚拟机上的数据库做相同操作，交叉式开启事务
在第一个hellodb中执行delete from mydb1 where id=2;
搜狗截图20171109161748.png
搜狗截图20171109161913.png
提交后执行，该事务执行完毕
重新设置隔离级别为set @@session.tx_isolation="READ-COMMITTED"
重新开启事务 start transaction;
执行 inset into mydb1 values(id=2,name=catty);
搜狗截图20171109163041.png
搜狗截图20171109163142.png
在第一个数据中执行commit;
此时在第二个数据库中则可以看见，这就是read-committed级别，只有提交后才能看见最新的修改内容搜狗截图20171109163605.png
但是该级别一旦提交就无办法重复读
为此引入第三个级别方式 REPEATABLE-READ
依然要先set @@session.tx_isolation="REPEATABLE-READ";
开启事务：START TRANSACTION;
在第一个数据库中执行insert into mydb1 values(7,"mayun");
select * from mydb1;可以看见新增加的用户
但是在另一个数据库中无法看见新加的内容，即使第一个数据库执行commit命令第二台数据库中也无法看见，这就是可重复读，但属于幻读。
幻读：在第一个虚拟机上的数据库能看见新加的用户，但是在第二个数据库中就无法看见，此时如果在第二个表中增加id为7的用户，就无法增加成功，这就是幻读带来的影响
搜狗截图20171109165753.png
第四个级别：SERIALIZABLE
set @@session.tx_isolation="SERIALIZABLE";
START TRANSACTION;
第二个数据库做相同操作
搜狗截图20171109170942.png
搜狗截图20171109170600.png
(4)事务支持savepoints：
该设置类似建立还原点
搜狗截图20171109171528.png
此时设置一个还原点
SAVEPOINT first;
delete from mydb1 where id=2;
搜狗截图20171109172521.png
rollback to first;
搜狗截图20171109172616.png
当建立多个savepoint时，可以直接执行rollback直接回到最初的状态
五.MYSQL索引
1.索引的概念：提取索引的创建在的表上字段中的数据，构建出一个独特的数据结构
2.索引的作用：
加速查询操作
副作用：降低写操作性能；
表中数据子集：把表中某个或某些字段的数据提取出来另存为一个特定数据结构组织的数据；
某个字段或某些字段：WHERE子句中用到的字段；
3.索引的类型
(1)B+ TREE(balance tree)
B+ TREE：顺序存储，每一个叶子结点到根结点的距离相同；左前缀索引，适合于范围类型的数据查询；
适用于B+ TREE索引的查询类型：全键值、键值范围或键前缀；
全值匹配：精确匹配某个值；
WHERE COLUMN = 'value';
匹配最左前缀：只精确匹配起头的部分；
WEHRE COLUMN LIKE 'PREFIX%';
匹配范围值：
精确匹配某一列，范围匹配另一列；——select name from students where name='wang' and age > 30;
只用访问索引的查询：覆盖索引；
index(Name)
SELECT Name FROM students WHERE Name LIKE 'L%';
不适用B+ TREE索引：
如果查询条件不是从最左侧列开始，索引无效；
index(age,Fname), WHERE Fname='Jerry';——索引不是从左侧匹配，所以索引无效 ,
WHERE age>30 AND Fname='Smith';——这是正确的索引方式
不能跳过索引中的某列；
index(name,age,gender)
WHERE name='black' and age > 30;——可以不索引最后一列
WHERE name='black' AND gender='F';——但是不能跳过中间的列
如果查询中的某个列是为范围查询，那么其右侧的列都无法再使用索引优化查询；
WHERE age>30 AND Fname='Smith';
(2) Hash索引：基于哈希表实现，特别适用于值的精确匹配查询；
适用场景：只支持等值比较查询，例如=, IN(), <=>
不用场景：
所有非精确值查询；MySQL仅对memory存储引擎支持显式的hash索引；
4.通过操作来演示索引的效果
步骤：
(1)首先执行commit命令，提交
(2)选中一个数据例如hellodb,进入后创建一个表 create table students1(id int unsigned auto_increment primary key,name char(30) not null ,age tinyint unsigned,gender enum('F', 'M'),major varchar(200));
insert into students values(1,"yangguo",20,"m","jianfa");
insert into students values(2,"yangbuhui",18,"f","suibian");
搜狗截图20171109212725.png
(3)批量创建用户
生成数组gender=('F' 'M')
echo ${gender[0]}
echo ${gender[1]}
搜狗截图20171109213136.png
for i in {1..100};do mysql -uroot -pmagedu -e "insert into hellodb.students(name,age,gender) values('stu$i','$[RANDOM%80+18]','${gender[$RANDOM%2]}');";done
创建成功后，在数据库中select * from students;
验证创建成功
(4)使用explain来查看查询逻辑
搜狗截图20171109213938.png
(5)创建索引
create index age ON students(age);
explain select name from students where age > 10;
通过age索引来查询内容
create index age_and_name ON students(name ,age);
会采用age_and_name来进行索引，属于覆盖索引，不会再使用age索引来进行查询，因为这样做效率相对低效
我们也可以将studnets(name,age)顺序调换一下
首先drop index age_and_name ON students;
然后创建新的索引
create index age_and_name ON students(name,age);
explain select name from students where age >90;
此时将会使用age索引而不是age_and_name索引，因为索引支持左侧匹配索引机制
所以我们可以使用explain select name from students where name='stu1%' and age >20;来进行索引查询，此时查询索引方式为age_and_name
扩展：一般情况默认为simple简单查询方式，但是也可以根据条件来实现其他查询方式
例：select avg(age) from students;计算平均年龄
再通过select name ,age from students where age > (select avg(age) from students);来查询大于平均年龄的人
通过explain语句分析使用的查询方式
六.mysql用户和权限管理
首先一般情况下要设置跳过数据库域名解析的问题，否则会很耽误时间，skip_name_resolve=ON
(1)用户账号
user@host
user：账户名称；
host：此账户可通过哪些客户端主机请求创建连接线程；
%：任意长度的任意字符；
_：任意单个字符；
(2)MySQL权限类别：
库级别：
CREATE，ALTER，DROP
INDEX
CREATE VIEW
SHOW VIEW
GRANT：能够把自己获得的权限生成一个副本转赠给其它用户；——不建议转增权限
OPTION
表级别：
CREATE，ALTER，DROP ，INDEX
CREATE VIEW
SHOW VIEW
GRANT：能够把自己获得的权限生成一个副本转赠给其它用户；——不建议转增权限
OPTION
字段级别：
管理类：
CREATE USER
RELOAD
LOCK TABLES
REPLICATION CLIENT， REPLICATION SLAVE
SHUTDOWN
FILE
SHOW DATABASES
PROCESS
SUPER
程序类：
FUNCTION，PROCEDURE，TRIGGER
操作：CREATE，ALTER，DROP，EXECUTE
(3)修改用户密码
1.set password for 'root'@localhost = password(centos);
- UPDATE mysql.user SET Password=PASSWORD('centos') WHERE User='root' AND Host='localhost';
-
mysqladmin -uroot -h127.0..0.1 -p password 'centos';
所以如果当我们忘记密码时，例如修改密码后忘记后我们可以采用的方法是
首先关掉mariadb.server
然后 vim /usr/lib/systemd/system/mariadb.server
搜狗截图20171109223335.png
修改后 systemctl daemon-reload
再开启mariadb.server服务
执行mysql ;进入数据库
使用update命令来修改新的密码
UPDATE mysql.user SET Password=PASSWORD('centos') WHERE User='root' ;
再退出
关闭mariadb
vim /usr/lib/system/systemd/mariadb.server
将之前加的命令删除后再执行systemctl daemon-reload;
执行完毕
网友评论