MySQL服务器

MySQL服务器

一、MySQL原理简介

二、数据库简单操作

1、MySQL数据库安装

2、MySQL字符集设置

3、MySQL数据库密码管理

4、MySQL数据库索引

5、MySQL数据库慢查询

6、MySQL数据库优化

一、MySQL原理简介

MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表格中,而不将所有数据放在同一个大仓库中,这样就增加了速度并提高了灵活性。

RDBMS是将数据组织为相关的行和列的系统,而管理关系数据库的计算机软件就是关系数据库管理系统,常用的关系型数据库软件有:MySQL、MariaDB、Oracle、SQL Server、PostgreSQL、db2等

RDBMS数据库的特点如下:

数据以表格的形式出现

每行记录数据的真实内容

每列记录数据真实内容的数据域

无数的行和列组成一张表

若干的表组成一个数据库

MySQL数据库运行在服务器前,需要选择启动的引擎。

MySQL引擎包括:ISAM、MyISAM、InnoDB、Memory、CSV、BlackHole、Archive、Performance_Schema、Berkeley、Merge、Federated、Cluster/NDB等,其中MyISAM、InnoDB使用最为广泛,以下为MyISAM、BDB、Memory、InnoDB以及Archive之间的引擎功能的对比。

性能总结如下:

MyISAM、MySQL 5.0之前的默认数据库引擎,最为常用。拥有较高的插入、查询速度,但不支持事务。

InnoDB事务型数据库的首选引擎,支持ACID事务,ACID包括原子性、一致性、隔离性、持久性,一个支持事务的数据库,必须具有这四个特性,否则在执行事务的过程中无法保证数据的正确性。

MySQL 5.5之后默认引擎为InnoDB,InnoDB支持行级锁定,支持事务、外键等功能。

BDB源自Berkeley DB,事务型数据库的另一种选择,支持Commit和Rollback等其他事务特性。

Memory所有数据置于内存的存储引擎,拥有极高的插入、更新和查询效率。但是会占用和数据量成正比的内存空间,并且其内容会在MySQL重新启动时丢失。

MySQL常用的两大引擎有MyISAM和InnoDB,那么它们只见的区别是什么?可根据不同的场合该如何进行选择?

MyISAM类型的数据库表强调的是性能,其执行速度比InnoDB类型更快,但不支持事务提供,不支持外键,如果执行大量的select操作,MyISAM是更好的选择,支持表锁。 InnoDB提供事务支持事务、外键、行锁等功能,执行大量的insert或update操作,出于性能方面的考虑,可以使用InnoDB引擎。

二、数据库简单操作

1、MySQL数据库安装

MySQL数据库安装方式有两种,一种是通过yum/rpm安装,还有一种是通过源码软件编译安装,源码编译安装在lnmp这篇博客中有详细过程。

通过yum安装的话:

cetenos6.X 安装yum -y install mysql-server mysql-devel mysql-libs

cetenos7.X 安装yum -y install mariadb-server mariadb mariadb-libs

2、MySQL字符集设置

MySQL数据库在存储数据时,默认编码是latin1,存储中文字符时,在显示或者web调用时会显示为乱码,所以需要修改MySQL默认字符集为UTF-8,有两种方法:

【1】. 编辑vim /etc/my.cnf配置文件,在相应段中加入相应的参数字符集,修改完毕后,重启MySQL服务器即可,具体内容如下:

(1)[client]字段中加入:default-character-set=utf8

(2)[mysqld]字段中加入:character-set-server=utf8

(3)[mysql]字段中加入:default-character-set=utf8

【2】.MySQL命令行中运行如下指令

(1)show variables like '%char%';

(2)SET character_set_client = utf8;

(3)SET character_set_results = utf8;

(4)SET character_set_connection = utf8;

最终显示为以下结果,则表示字符集设置完成

3、MySQL数据库密码管理

MySQL数据库在使用过程中为了加强安全防范,需要设置密码访问

(1) MySQL创建用户及授权

#授权localhost主机通过test用户和lcl密码访问本地的test库的查询、插入、更新、删除权限

MariaDB [(none)]> grant select,insert,update,delete on test.* to test@localhost identified by 'lcl';

Query OK, 0 rows affected (0.00 sec)

#授权所有主机通过test用户和test密码访问本地test库的所有权限

grant all on test.* to test@'%' identified by 'test';

(2) MySQL密码破解方法

在使用MySQL数据库中,偶尔会出现密码忘记的情况。要破解MySQL密码,首先要停止MySQL数据库,以跳过权限的方式启动,然后在shell中执行mysql命令,修改密码,并且重新启动mysql

[root@server1 ~]# systemctl stop mariadb

[root@server1 ~]# mysqld_safe --user=mysql --skip-grant-tables &

[root@server1 ~]# 19:24:38 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.

19:24:38 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@server1 ~]# mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 1

Server version: 5.5.44-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [mysql]> update user set password=password('') where user='root';

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [mysql]> exit

[root@server1 ~]# systemctl start mariadb

[root@server1 ~]# mysql -uroot -p

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 3

Server version: 5.5.44-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

4、MySQL数据库索引

MySQL数据库索引可以用来快速寻找某些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果MySQL没有索引,执行select时会从第一个记录开始扫描整个表的所有记录,直到找到符合要求的记录。如果在需要搜索条件的列上创建了索引,MySQL无须扫描全表记录即可快速得到相应的记录行。

普通索引:normal,使用最为广泛

唯一索引:unique,不允许重复的索引,允许有空值

全文索引:full text,只能用于MyISAM,full text主要用于大量的内容检索

主键索引:primary key又称为特殊的唯一索引,不允许有空值

组合索引:为提高MySQL效率可建立组合索引

MySQL数据库创建、查看、删除各个索引命令(都以t1表为例)

创建索引:

主键索引:ALTER TABLE t1 ADD PRIMARY KEY('column');

唯一索引:ALTER TABLE t1 ADD UNIQUE('column');

普通索引:ALTER TABLE t1 ADD INDEX index_name('column');

全文索引:ALTER TABLE t1 ADD FULETEXT('column');

组合索引:ALTER TABLE t1 ADD INDEX index_name(column1,column2);

查看表索引

show index from t1;

show keys from t1;

删除索引

DROP INDEX index_name ON t1;

ALTER TABLE t1 DROP INDEX index_name;

ALTER TABLE t1 DROP PRIMARY KEY;

MySQL数据库索引的缺点:

MySQL数据库索引虽然能提高数据库的查询速度,但同时会降低更新、删除、插入表的速度,例如对表进行insert,update,delete时,update表MySQL不仅要保存数据,还要保存更新索引;

建立索引会占用磁盘空间,大表上创建了多种索引组合,索引文件就会占用大量的空间。

5、MySQL数据库慢查询

MySQL数据库慢查询主要用于跟踪异常的SQL语句,可以分析出当前程序里哪些SQL语句比较耗费资源,慢查询日志则用来记录MySQL中响应时间超过阈值的语句,具体指运行时间超过long_query_time值的SQL语句,会被记录到慢查询日志中。

MySQL数据库默认没有开启慢查询日志功能,需要手动在配置文件或者MySQL命令行中开启,慢查询日志默认写入磁盘中的文件,也可以将慢查询日志写入到数据库表中。

查看数据库是否开启慢查询,命令如下:

show variables like '%slow%';

show variables like '%long_query%';

MySQL慢查询参数详解

log_show_queries:关闭慢查询日志功能

long_query_time:慢查询超时时间,默认为10s,MySQL 5.55以上可以设置微秒

slow_query_log:关闭慢查询日志

show_query_log_file:慢查询日志文件

slow_launch_time:thread create时间,单位为秒,如果thread create时间超过了这个值,该变量slow_launch_time值会加1

log-queries-not-using-indexes:记录未添加索引的SQL语句

开启MySQL慢查询日志方法有以下两种

1. MySQL数据库命令行执行命令

set global slow_query_log = on;

show variables like '%slow%';

编辑my.cnf配置文件

log-slow-queries = /data/mysql/slow.log

log_query_time = 0.01

log-queries-using-indexes

慢查询功能开启之后,数据库会自动将执行时间超过设定时间的SQL语句添加到慢查询日志文件中,可以通过慢查询日志文件定位到执行慢的SQL,从而对其优化,可以通过mysqldumpslow命令行工具分析日志

执行命令mysqldumpslow -h可以查看命令帮助信息,主要参数包括-s 和 -t,其中 - s是排序参数,可选项详解如下

l:查询锁的总时间

r:返回记录数

t:查询总时间排序

al:平均锁定时间

ar:平均返回记录数

at:平均查询时间

-t n :显示头n条记录

-g :过滤

MySQL慢查询按照返回的行数从大到小,查看前两行:

mysqldumpslow -s r -t 2 slow.log

按照查询总时间从大到小,查看前5行,同时过滤select的SQL语句

mysqldumpslow -s t -t 5 -g "select" slow.log

6、MySQL数据库优化

MySQL数据库具体优化包括:配置文件的优化、SQL语句的优化、表结构的优化、索引的优化

而配置的优化包括:系统内核、硬件资源、内存、CPU、MySQL本身配置文件的优化

硬件上的优化有两种方式:一种是增加内存和提高磁盘读写速度,进而提高MySQL数据库的查询、更新的速度;另一种提高MySQL性能的方式是使用多块磁盘来存储数据,可以从多块磁盘上并行读取数据,进而提高读取数据的速度

MySQL参数的优化,内存中会为MySQL保留部分的缓冲区,这些缓冲区可以提高MySQL的速度,缓冲区的大小可以在MySQL的配置文件中设置。