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

推荐阅读更多精彩内容