mysql原理简介、安装设置、索引、慢查询、优化思想

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	BDB	Memory	InnoDB	Archive
批量插入的速度	高	高	高	中	非常高
集群索引	不支持	不支持	不支持	支持	不支持
数据缓存	不支持	不支持	支持	支持	不支持
索引缓存	支持	不支持	支持	支持	不支持
数据可压缩	支持	不支持	不支持	不支持	支持
硬盘空间使用	低	低	null	高	非常低
内存使用	低	低	中等	高	低
外键支持	不支持	不支持	不支持	支持	不支持
存储限制	没有	没有	有	64TB	没有
事务安全	不支持	支持	不支持	支持	不支持
锁机制	表锁	页锁	表锁	行锁	行锁
B树索引	支持	支持	支持	支持	不支持
哈希索引	不支持	不支持	支持	支持	不支持
全文索引	支持	不支持	不支持	不支持	不支持

性能总结如下：

1. MyISAM、MySQL 5.0之前的默认数据库引擎，最为常用。拥有较高的插入、查询速度，但不支持事务。
2. InnoDB事务型数据库的首选引擎，支持ACID事务，ACID包括原子性、一致性、隔离性、持久性，一个支持事务的数据库，必须具有这四个特性，否则在执行事务的过程中无法保证数据的正确性。
3. MySQL 5.5之后默认引擎为InnoDB，InnoDB支持行级锁定，支持事务、外键等功能。
4. BDB源自Berkeley DB，事务型数据库的另一种选择，支持Commit和Rollback等其他事务特性。
5. Memory所有数据置于内存的存储引擎，拥有极高的插入、更新和查询效率。但是会占用和数据量成正比的内存空间，并且其内容会在MySQL重新启动时丢失。

---

MySQL常用的两大引擎有MyISAM和InnoDB，那么它们只见的区别是什么？可根据不同的场合该如何进行选择？
MyISAM类型的数据库表强调的是性能，其执行速度比InnoDB类型更快，但不支持事务提供，不支持外键，如果执行大量的select操作，MyISAM是更好的选择，支持表锁。 InnoDB提供事务支持事务、外键、行锁等功能，执行大量的insert或update操作，出于性能方面的考虑，可以使用InnoDB引擎。

二、数据库简单操作

1、MySQL数据库安装

MySQL数据库安装方式有两种，一种是通过yum/rpm安装，还有一种是通过源码软件编译安装，源码编译安装在lnmp这篇博客中有详细过程。
通过yum安装的话：
rhel6.X 安装yum install mysql-server mysql-devel mysql-libs -y
rhel7.X 安装yum install mariadb-server mariadb mariadb-libs -y

2、MySQL字符集设置

MySQL数据库在存储数据时，默认编码是latin1，存储中文字符时，在显示或者web调用时会显示为乱码，所以需要修改MySQL默认字符集为UTF-8，有两种方法：
1. 编辑vim /etc/my.cnf配置文件，在相应段中加入相应的参数字符集，修改完毕后，重启MySQL服务器即可，具体内容如下：

  [client]字段中加入：default-character-set=utf8
  [mysqld]字段中加入：character-set-server=utf8
  [mysql]字段中加入：default-character-set=utf8

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

  show variables like '%char%';
  SET character_set_client = utf8;
  SET character_set_results = utf8;
  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 &
[1] 1416
[root@server1 ~]# 180805 19:24:38 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
180805 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('123456') where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [mysql]> exit
Bye
[root@server1 ~]# systemctl start mariadb
[root@server1 ~]# mysql -uroot -p123456
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无须扫描全表记录即可快速得到相应的记录行。
MySQL数据库常见索引类型包括：普通索引(normal)，唯一索引(unique)，全文索引(full text)，主键索引(primary key)，组合索引等。

• 普通索引：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%';

1. 编辑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：平均查询时间
• c：计数
• -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的配置文件中设置