1、MySQL环境搭建

1.1、查看Linux服务器上是否安装过MySQL

#查询处所有的mysql依赖包
rpm -qa | grep -i mysql 
#执行安装服务端与服务端
rpm -ivh 包名
#查看安装是否成功
cat /etc/passwd | grep mysql
cat /etc/group | grep mysql
#启动服务
service mysql start
#关闭服务
service mysql stop
#修改root密码为root
/usr/bin/mysqladmin -u root password root

1.2、修改字符集

进入到mysql数据库并查看字符集
- show variables like 'character%'
- show variables like '%char%'

MySQL配置文是/etc/my.cnf 或者是/etc/mysql/my.cnf，如果字符集不是utf-8，直接进入配置文件进行修改即可
[client]
default-character-set = utf-8

[mysql]
default-character-set = utf-8

[mysqld]
# 设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8'
init_connect='SET collation_connection = utf8_general_ci'

# 数据库默认字符集
character-set-server=utf8

#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server=utf8_general_ci

# 跳过mysql程序起动时的字符参数设置 ，使用服务器端字符集设置
skip-character-set-client-handshake

# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！
skip-name-resolve

注意：安装完成后，第一件事就是修改字符集编码

1.3、配置文件

1.3.1、二进制文件log-bin：主从复制

# my.cnf
# 开启mysql binlog功能
log-bin = mysql-bin

1.3.2、错误日志log-error：默认关闭，记录错误信息

# my.cnf
# 数据库错误日志文件
log-error = error.log

1.3.3、查询日志log：默认关闭，记录查询的sql语句，开启会消耗mysql性能，

# my.cnf
# 慢查询sql日志设置
slow_query_log = 1
slow_query_log_file = slow.log

1.3.4、数据文件

frm文件：存放表结构。
myd文件：存放表数据
myi文件：存放表索引。

# mysql5.7使用.frm文件来存储表结构
# 使用.ibd文件来存储表索引和表数据
-rw-r-----  1 mysql mysql   8988 Jun 25 09:31 pms_category.frm
-rw-r-----  1 mysql mysql 245760 Jul 21 10:01 pms_category.ibd

MySQL5.7的Innodb存储引擎可将所有数据存放于ibdata*的共享表空间，也可将每张表存放于独立的.ibd文件的独立表空间。共享表空间以及独立表空间都是针对数据的存储方式而言的。

共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中.
- 默认这个共享表空间的文件路径在data目录下。
- 默认的文件名为:ibdata1 初始化为10M。
独立表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。
- 其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。
- 在配置文件my.cnf中设置： innodb_file_per_table。

2、mysql逻辑架构

2.1、架构介绍

connectors：指的是不同语言中与SQL的交互。
Connect pool：管理缓冲用户链接，线程处理等需要缓存的需求。MySQL数据库的连接层。
Management Services & utilities：系统管理工具和控制工具。备份、安全、复制、集群等。
SQL Interface：接受用户的SQL命令，并且返回用户需要查询的结果。
Parser：SQL语句解析器
Optimizer：查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求query，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果。
- For Example： select uid,name from user where gender = 1; 这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤；然后根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。最后将这两个查询条件联接起来生成最终查询结果。
Caches & Buffers：查询缓存
Pluggable Storage Engines：存储引擎接口。MySQL区别于其他数据库的最重要的特点就是插件式的表存储引擎（注意：存储引擎是基于表的，而不是数据库）
File System：数据落地到磁盘上，就是文件存储

MySQL数据库和其他数据库相比，MySQL有点与众不同，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需求选择合适的存储引擎。

2.2、逻辑架构分层

链接层：
- 最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层：
- MySQL的核心服务功能层，该层是MySQL的核心，包括查询缓存，解析器，解析树，预处理器，查询优化器。主要进行查询解析、分析、查询缓存、内置函数、存储过程、触发器、视图等，select操作会先检查是否命中查询缓存，命中则直接返回缓存数据，否则解析查询并创建对应的解析树。
引擎层：
- 存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
储存层：
- 数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

3、存储引擎

#查看MySQL支持的存储引擎。默认支持的是InnoDB.
show engines; 
#查看当前数据库正在使用的存储引擎，
show variables like 'default_storage_engine%'

3.1、InnoDB和MyISAM相比

4、SQL性能下降原因

查询语句差
索引失效：索引建了，但没有完全建好
关联查询太多的Join，设计缺陷或者是迫不得已
服务器调优及各个参数的设置（缓冲、线程数等）

5、SQL执行顺序

select...   5

from...     1

where...    2

group by... 3

having...   4

order by... 6

limit...    7

6、七种Join理论

/* 1 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key;

/* 2 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 3 */
SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key;

/* 4 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;

/* 5 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

/* 6 */
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 1+2 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 7 */
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 4+5 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

7、索引

MySQL官方定义：索引是帮助MySQL高效获取数据的数据结构，从而可以知道索引的本质：索引是排好序的快速查找数据结构

7.1、索引的目的

在于提高查询效率，可以类比字典的目录。如果要查mysql这个这个单词，我们肯定要先定位到m字母，然后从上往下找y字母，再找剩下的sql。如果没有索引，那么可能需要a---z，这样全字典扫描，如果我想找Java开头的单词呢？如果我想找Oracle开头的单词呢？？？

重点：索引会影响到Mysql查找（WHERE的查询条件）和排序（ORDER BY）两大功能

7.2、索引的结构

除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

我们平时所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种数据结构的索引之外，还有哈希索引（Hash Index）等。

7.3、索引的优势和劣势

优势：
- 查找：类似图书馆的树木索引，提高数据检索的效率，降低数据库的IO成本。
- 排序：通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗
劣势：
- 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以需要占用空间
- 虽然索引大大提高了查询速度，但是同时会降低表的更新速度，例如对表频繁的进行INSERT、UPDATE和DELETE。因为更新表的时候，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加的索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
- 索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立最优秀的索引。

7.4、MySQL索引分类

单值索引：一个索引只包含单个列，一个表可以用多个单列索引。
唯一索引：索引列的值必须唯一，但是允许空值。
复合索引：一个索引包含多个字段。

建议：一张表建的索引最好不要超过5个

#基本语法
#创建
create [unique] index indexName ON tabName(ColumnName(length))
#删除
Drop index [indexName] ON tabName;
#查看索引
show index from tabName \G


#使用ALTER命令创建索引
#该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL
alter table tabName ADD Primary key (column_list)
#该语句创建索引的键值必须是唯一的(除了NULL之外，NULL可能会出现多次)
alter table tabName ADD unique indexName(column_list)
#该语句创建普通索引，索引值可以出现多次
alter table tabName ADD index indexName(column_list)
#该语句指定了索引为FULLTEXT，用于全文检索
alter table tabName ADD FULLText indexName(column_list)

7.5、Mysql索引数据结构

BTree索引
Hash索引
Full-Text全文索引
R-Tree索引

BTree索引原理：

7.6、哪些情况下需要建索引

主键自动建立主键索引（唯一 + 非空）。
频繁作为查询条件的字段应该创建索引。
查询中与其他表关联的字段，外键关系建立索引。
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
查询中统计或者分组字段（group by也和索引有关）。

7.7、哪些情况下不要建索引

记录太少的表。
经常增删改的表。
频繁更新的字段不适合创建索引。
Where条件里用不到的字段不创建索引。
假如一个表有10万行记录，有一个字段A只有true和false两种值，并且每个值的分布概率大约为50%，那么对A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

MySQL进阶一