11月8日 mysql进阶 1

1、开启mariadb之前进行的配置

cd /etc/my.cnf.d/
vim server.cnf ---在server语句块里增加如下三行内容
skip_name_resolve = on  ---跳过名字解析
innodb_file_per_table = on ----每个数据库中的每张表有单独的文件
max_connections = 20000  ---最大并发连接
systemctl start mariadb
my_print_defaults ---可以查看配置文件的生效顺序

2、mariadb常用的存储引擎InnoDB和MyISAM的特点和区别

InnoDB
①数据和索引存储于表空间文件，也就是一个黑盒中，用户看不到空间里面有什么，表空间文件在所创建的数据库目录下的 tbl_name.ibd中，表结构的定义在所创建的数据库目录的tbl_name.frm中
②支持事物
③基于MVCC（Mutli Version Concurrency Control）支持高并发；支持四个隔离级别，默认级别为REPEATABLE-READ；间隙锁以防止幻读；
④使用聚集索引（主键索引），聚集索引指目录和书的内容在同一本书中，找到索引就能找到内容，并且放索引的地方旁边就是内容，非聚集索引指目录和书的内容是分开的，索引一本书，内容放在另外一本书中，要查到内容需要两本书
⑤锁粒度：行级锁；间隙锁
MyISAM
①每个表有三个文件，存储于创建的数据库目录中
tbl_name.frm：表格式文件；
tbl_name.MYD：数据文件；
tbl_name.MYI：索引文件；
②不支持事物
③使用非聚集索引
④锁粒度：表级锁
⑤崩溃无法保证表安全恢复
⑥支持全文索引（FULLTEXT index）、压缩、空间函数（GIS）
适用场景：只读或读多写少的场景、较小的表（以保证崩溃后恢复的时间较短）

MariaDB [hidb]> show engines;  ---查看存储引擎的类型
MariaDB [hidb]> show table status \G  ---查看此数据库中所有表的存储引擎
MariaDB [hidb]> show table status like 'stu%' \G  ----查看此数据库中某个表的存储引擎
MariaDB [hidb]> show table status where engine='innodb' \G  ---查看此数据库中存储引擎为innodb的表
MariaDB [hidb]> create table students2(id int,name char(10)) engine=myisam;  ---创建表并指定存储引擎
MariaDB [hidb]> show table status like 'students2'\G   ---可以查看到这个表的存储引擎

3、并发控制锁Lock

锁类型：
读锁：共享锁，可被多个读操作共享，但不允许写；
写锁：排它锁，独占锁，建立写锁后其他的线程将不能读这张表；
锁粒度：
表锁：在表级别施加锁，并发性较低；
行锁：在行级别施加锁，并发性较高；维持锁状态的成本较大；
锁策略：在锁粒度及数据安全性之间寻求一种平衡机制；
锁类别：
显式锁：用户手动请求的锁；
隐式锁：存储引擎自行根据需要施加的锁；
显式锁的使用：
(1) LOCK TABLES
LOCK TABLES tbl_name read|write, tbl_name read|write, ...
UNLOCK TABLES
(2) FLUSH TABLES
FLUSH TABLES tbl_name,... [WITH READ LOCK];
UNLOCK TABLES;
FLUSH的意思是把表的数据从内存中清除同步到磁盘上，我们在对表进行读写操作时必须先把表的数据读入到内存中才可以，内存中没有此表的数据就无法进行读写操作了，就相当于锁住了这张表，解锁相当于把磁盘数据读到内存中。
设置显式锁示例

方法一：
MariaDB [hidb]> lock tables students write;   ---对students这张表设置为写锁
用复制的方式打开另外一个会话，在此会话中进行如下操作
mysql
MariaDB [(none)]> use hidb;
MariaDB [hidb]> show processlist;  ---查看线程发现有两个线程
MariaDB [hidb]> select * from students;   ---发现无法查看此表，说明此表被锁定
在第一个会话中进行如下设置
MariaDB [hidb]> unlock tables;   ---解锁
在第二个会话中进行如下操作
MariaDB [hidb]> select * from students;   ---发现可以查看此表
在第一个会话中进行如下设置
MariaDB [hidb]> lock tables students read;   ---设置读锁
在第二个会话中的操作
MariaDB [hidb]> select * from students;   ---发现可以查看此表
总结：说明写锁是独占锁，不允许其他的进程进行读和写操作，而读锁是共享锁，允许其他的进程进行读操作，但不允许写操作
方法二：
在第一个会话中设置读锁
MariaDB [hidb]> flush tables with read lock; ---注意这种方式只能设置读锁
在第二个会话中的操作
MariaDB [hidb]> select * from students;   ---发现可以读此表
MariaDB [hidb]> insert into students values(1,'yangguo',18,'M','anranxiaohun');   ---但不能写，必须解除锁才可以

4、事物

事务：一组原子性的SQL查询、或者是一个或多个SQL语句组成的独立工作单元；
事务日志：事物在内存中执行时先保存到事物日志中，然后再同步到数据文件中，事物日志也在磁盘上，只不过事物日志在记录的时候是顺序写而不是随机，所以写的速度比较快，事物之所以能进行回滚操作就是因为事先已经将老版本记录到事物日志中了。

事物日志的参数设置
innodb_log_files_in_group  ---指明一组事物内有几个事物日志文件，为了冗余，一般一组内要设置两个
innodb_log_group_home_dir ---事物日志存储的家目录
innodb_log_file_size    ---每个日志文件的大小，默认是5M
innodb_mirrored_log_groups  ---日志组的镜像有几个，设置成1表示只有一组日志文件
MariaDB [hidb]> show variables \G ---查看mysql的变量参数，这些参数都是可以设定的，用于定义mysql的工作特性
MariaDB [hidb]> show global variables \G    ----查看全局的变量的所有参数，是默认值，对所有连接的会话都有效，注意后面没有分号，否则会报错
MariaDB [hidb]> show session variables \G    ---查看当前会话的所有变量参数，只对当前连接的会话有效
MariaDB [hidb]> show session variables like 'innodb_file_per_table';    ---可以过滤的查看设定的变量参数
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

MariaDB [hidb]> select @@global.innodb_file_per_table;   ---也可以这样查看
+--------------------------------+
| @@global.innodb_file_per_table |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.00 sec)
MariaDB [hidb]> set @@global.innodb_file_per_table=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hidb]> select @@global.innodb_file_per_table;
+--------------------------------+
| @@global.innodb_file_per_table |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (0.00 sec)

注意：session表示的是只对当前连接的会话有效，通过复制创建的会话属于另外一个连接，所以通过设置session变量参数，只对当前连接的有效，对复制的会话是无效的，但global是属于全局变量，通过设置global变量参数对全局都有效，对复制的会话也是有效的，这样的设置只是在运行时修改，重启后就会失效，要想永久有效就要写到配置文件里，就像刚刚开启mariadb时设置的变量参数一样，写到配置文件里就可以了

ACID测试：
A：AUTOMICITY，原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚；
C：CONSISTENCY，一致性；数据库总是应该从一个一致性状态转为另一个一致性状态；
I：ISOLATION，隔离性；一个事务所做出的操作在提交之前，是否能为其它事务可见；出于保证并发操作之目的，隔离有多种级别；
D：DURABILITY，持久性；事务一旦提交，其所做出的修改会永久保存；
自动提交：单语句事务
mysql> SELECT @@autocommit;
+------------------------+
| @@autocommit |
+------------------------+
| 1 |
+------------------------+
mysql> SET @@session.autocommit=0；
手动控制事务：
启动：START TRANSACTION
提交：COMMIT
回滚：ROLLBACK
事务支持savepoints：
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
事务隔离级别：
READ-UNCOMMITTED：读未提交 --> 脏读；别人在一个会话中修改了，即使没有提交，我在另外一个会话中也会看到修改的数据
READ-COMMITTED：读提交--> 不可重复读；别人在一个会话中修改了，只有提交了，我在另外一个会话中才能看到修改的数据，但不可重复读，只能看到别人提交的数据
REPEATABLE-READ：可重复读 --> 幻读；别人在一个会话中修改了，也提交了，你也看不到修改的数据，出现幻读
SERIALIZABLE：串行化；别人在一个会话中修改了，要么回滚，要么提交，否则我在另外一个会话中会阻塞，直到别人进行回滚或者提交操作我才能看到数据
mysql> SELECT @@session.tx_isolation;
+----------------------------------+
| @@session.tx_isolation |
+----------------------------------+
| REPEATABLE-READ |
+----------------------------------+
查看InnoDB存储引擎的状态信息：
SHOW ENGINE innodb STATUS;

示例1：事物回滚
MariaDB [hidb]> select @@autocommit;   ---查看自动提交功能是否打开
MariaMariaDB [hidb]> set @@session.autocommit=0;  ----关闭自动提交功能
DB [hidb]> select @@autocommit   ---查看自动提交功能是否关闭
MariaDB [hidb]> start transaction;  ---启动一个事物
MariaDB [hidb]> insert into students values(2,'xiaolongnv',22,'F','zuoyouhubo');   ---在表中插入数据
MariaDB [hidb]> roback;   ---回滚
MariaDB [hidb]> select * from students;---发现又回滚到原来的，前面插入的数据也没有了
MariaDB [hidb]> insert into students values(2,'xiaolongnv',22,'F','zuoyouhubo');   ---插入数据
MariaDB [hidb]> savepoint first;   ---设置一个保存点位first
MariaDB [hidb]> rollback to first;    ---回滚到第一个保存点
MariaDB [hidb]> select * from students;   ----发现刚刚插入的数据还在
MariaDB [hidb]> insert into students values(3,'guojing',40,'M','xianglongshibazhang');
MariaDB [hidb]> savepoint second;
MariaDB [hidb]> rollback to sencond;   ---设置保存点后就相当于把数据保存了，回滚到指定的保存点后数据就不会丢失
MariaDB [hidb]> commit;  ----提交
MariaDB [hidb]> rollback;   ---全部回滚，发现回滚不回去了，说明事物一旦提交就无法回滚了
MariaDB [hidb]> select * from students;
示例2：事物隔离级别
MariaDB [hidb]> select @@session.autocommit;    查看是否已经设置为手动提交事务，0表示关闭自动提交，因为设置的是只对当前连接的会话有效，通过复制打开的多个会话都是无效的
+----------------------+
| @@session.autocommit |
+----------------------+
|                    0 |
+----------------------+
MariaDB [hidb]> select @@global.autocommit;   ---对于全局的还是自动提交事务的。因为只是设置的对当前会话有效
+---------------------+
| @@global.autocommit |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [hidb]> select @@session.tx_isolation;   ---查看事物的隔离级别，默认是重复可读
MariaDB [hidb]> set @@session.tx_isolation='read-uncommitted';   ---在一个会话中设置事物隔离级别为读未提交
用复制的方式打开另外一个会话，也设置为读未提交
然后在两个会话中都开启一个事物
在其中一个会话中进行如下操作
MariaDB [hidb]> select * from students2;
+------+------------+
| id   | name       |
+------+------------+
|    1 | yangguo    |
|    2 | xiaolongnv |
|    3 | ouyangfeng |
|    4 | hongqi     |
+------+------------+
4 rows in set (0.00 sec)

MariaDB [hidb]> delete from students2 where id=3;   ---删除一个字段
Query OK, 1 row affected (0.01 sec)

MariaDB [hidb]> select * from students2;
+------+------------+
| id   | name       |
+------+------------+
|    1 | yangguo    |
|    2 | xiaolongnv |
|    4 | hongqi     |
+------+------------+
3 rows in set (0.00 sec)
在另外一个会话中查询发现也会缺少删除的字段，说明隔离级别
设置成读未提交后，即使不提交，在另外一个会话中也会看到修
改的数据，这样会造成脏读，也就是别人可能将数据回滚后再提
交，这时候你就会发现缺少的字段又回来了，不精准，会看到别
人未提交的数据

5、MySQL的索引

可以参考：http://www.cnblogs.com/linhaifeng/articles/7274563.html

索引：索引是创建在表字段上的一个独特的数据结构；
索引的作用：加速查询操作；副作用：降低写操作性能；
索引优点：
降低需要扫描的数据量，减少IO次数；
可以帮助避免排序操作，避免使用临时表；
帮助将随机IO转为顺序IO；
高性能索引策略：
(1) 在WHERE中独立使用列，尽量避免其参与运算；
WHERE age+2 > 32 ;
(2) 左前缀索引：索引构建于字段的最左侧的多少个字符，要通过索引选择性来评估
索引选择性：不重复的索引值和数据表的记录总数的比值；
(3) 多列索引：
AND连接的多个查询条件更适合使用多列索引，而非多个单键索引；
(4) 选择合适的索引列次序：选择性最高的放左侧；
索引类型：B+ TREE，HASH
示例

[root@centos7 ~]#declare -a gender=(F M)    ---定义一个数组
[root@centos7 ~]#for i in {1..1000};do mysql -e "insert into hidb.students(name,age,gender)values('stu$i',$[RANDOM%100],'${gender[$[RANDOM%2]]}')";done   ---mysql -e可以直接在shell里实现对数据库的操作，定义一个脚本，实现在表中增加数据
MariaDB [hidb]> explain select name from students where age>90;   ---用explain可以查看索引是否有用，因为age上没有索引，所以此时查询时没有用到索引
MariaDB [hidb]> create index age on students(age);  ---创建一个索引
MariaDB [hidb]> show indexes from students \G; ---查看索引
MariaDB [hidb]> explain select name from students where age>90;   ---发现查询时用到了索引
MariaDB [hidb]> create index age_and_name on students(age,name);   ---在两个字段创建索引，并且索引名字是相同，一个字段可以有多个索引
MariaDB [hidb]> explain select name,age from students where age>(select avg(age) from students);---- select avg(age) from students表示取age的平均值

6、忘记管理员密码的解决办法

(1) 启动mysqld进程时，使用--skip-grant-tables和--skip-networking选项；
CentOS 7：mariadb.service
CentOS 6：/etc/init.d/mysqld
(2) 通过UPDATE命令修改管理员密码；
(3) 以正常方式启动mysqld进程；
示例

[root@centos7 ~]#systemctl stop mariadb
[root@centos7 ~]#vim /usr/lib/systemd/system/mariadb.service 
ExecStart=/usr/bin/mysqld_safe --basedir=/usr --skip-grant-tables --skip-networking
[root@centos7 ~]#systemctl daemon-reload ---加载一下
[root@centos7 ~]#systemctl start mariadb
[root@centos7 ~]#mysql
MariaDB [(none)]> update mysql.user set password=password('centos') where user='root';  ---修改user表中的root用户的密码
[root@centos7 ~]#systemctl stop mariadb
[root@centos7 ~]#vim /usr/lib/systemd/system/mariadb.service 
将--skip-grant-tables --skip-networking删除
[root@centos7 ~]#systemctl daemon-reload 
[root@centos7 ~]#systemctl start mariadb
[root@centos7 ~]#mysql -uroot -pcentos ---发现可以登录了

centos6的修改方法
vim /etc/my.cnf 
[mysqld]
skip-grant-tables #加上此条，
service mysqld restart
mysql #直接就可以连接到数据库，不需要输入密码
mysql> update user set authentication_string=password('123') where user='root'; 
#5.7版本的mysql数据库password 字段改成authentication_string  ,password函数还是原来的password函数
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
mysql>quit
vim /etc/my.cnf 
[mysqld]
#skip-grant-tables #将此行注释掉
service mysqld restart
mysql -uroot -p123

最后编辑于：2018.07.18 11:34:27

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 217,734评论 6赞 505
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,931评论 3赞 394
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 164,133评论 0赞 354
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,532评论 1赞 293
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,585评论 6赞 392
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,462评论 1赞 302
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,262评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 39,153评论 0赞 276
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,587评论 1赞 314
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,792评论 3赞 336
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,919评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,635评论 5赞 345
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,237评论 3赞 329
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,855评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,983评论 1赞 269
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 48,048评论 3赞 370
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,864评论 2赞 354