[大数据]MySQL

MySQL特征：

• MySQL是开源的
• 支持大型数据库。可以处理拥有上千万条记录的大型数据库
• MySQL可以用于多个系统，并且支持多种语言
• MySQL支持定制，可以自己修改源码来开发自己的MySQL

MySQL逻辑架构

数据库逻辑结构共分为4层：连接层、业务逻辑处理层、存储引擎层、数据存储层

• 外部程序
• 连接层
• • 提供客户端和连接服务，管理缓冲用户连接、线程处理等需要缓存的需求
• 业务逻辑处理层
• • SQL接口：接受用户的SQL命令，并返回用户需要查询的结果
• • 解析器：SQL命令被解析器解析验证，解析器是一个很长的脚本，将SQL命令分解成数据结构，并将这个结构传递到后续步骤，以后的SQL语句的传递和处理就是基于这个结构，如果在分解过程中不合理就说明这个SQL语句是错误的。
• • 查询优化器：对SQL语句进行查询优化，它使用“选取–投影–连接”策略进行查询。
• • 缓存和缓冲池：查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个而过缓存机制由一系列小缓存组成。比如表缓存、记录缓存、Key缓存、权限缓存等
• 存储引擎层：数据的存储和提取，默认是InnoDB
• 数据存储层：将数据存储到磁盘上，并协同存储引擎对数据进行读写操作。

插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取分离，这种架构可以根据业务的需求来选择合适的存储引擎。

一条SQL语句的执行过程

• 应用程序与数据库服务器建立一个连接，权限校验和身份验证
• SQL接口接收用户的SQL语句
• 解析器解析验证SQL语句
• 查询优化 器对SQL语句进行优化
• 执行器执行语句，从存储引擎返回数据

1. 数据库优势，为啥需要数据库？

数据存储在内存，读取方便，但是不能永久存储。
数据存储在文件，可以永久保存，但是有频繁的IO操作，数据读取不方便。
数据存储在数据库，可以永久保存，SQL语句查询方便效率高，管理数据方便。

2. 数据库的三大范式

1. 第一范式：每一列都不可再拆分
2. 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能依赖主键列的一部分
3. 第三范式：在第二范式的基础上，非主键列只依赖于主键，而不能依赖其他非主键列

2.1 范式的优点和缺点

优点

1. 重复数据很少或者没有，修改的数据也更少
2. 表通常更小，可以更好地放在内存里，执行操作会比较快
3. 很少有多余的数据意味着检索列表时更少的使用DISTINCT或者GROUP

缺点：范式的缺点通常是需要关联

2.2 反范式的优点和缺点

优点：所有的数据都在一张表里，不需要关联
缺点：表比较大，更新数据比较麻烦，会有很多重复的数据，占用的内存会比较多

2.3 混用范式化和反范式化

在不同的表中存储相同的特定列

3. 数据库中有权限的表

user表：记录可以连接这个服务器的用户信息
db表：记录各个用户对于各个数据库的操作权限信息
table_priv表：记录数据表级的操作权限
columns_priv表：记录数据列级的操作权限
host表：和db包配合，对给定服务器上的数据库操作权限做更细致的控制

4. SQL语言

4.1 SQL语句分类

数据定义语句(DDL)：drop，create，alter，truncate
数据操作语言(DML)：insert，delete，update
数据查询语言(DQL)：select
数据控制语言(DCL)：grant，remoke，commit，rollback

4.2 超键，候选键，主键 外键

• 超键：在关系中能唯一标识一个元组的属性集称为超键，超键可以是一个属性，也可以是多个属性组合在一起。超键包含候选键和主键。
• 候选键：最小超键，没有冗余元素的超键
• 主键：唯一且不为空的属性，一个关系中只能有一个主键
• 外键：一个关系中存在另一个表的主键

4.3 关联查询

• 交叉连接（CROSS JOIN）
  SELECT * FROM A,B,C;或者SELECT * FROM A CROSS JOIN B CROSS JOIN C#没有任何关联条件，结果是笛卡尔积，结果集会很大，没有意义。返回的结果是所有表的行数的乘积。
• 内连接（INNER JOIN）
  很少使用内连接（INNER JOIN）SELECT * FROM A,B WHERE A.id=B.id或者SELECT * FROM A INNER JOIN B ON A.id=B.id多表中同时符合某种条件的数据记录的集合，INNER JOIN可以缩写为JOIN
  内连接又分为等值连接，不等值连接和内连接，内连接就是两边的表都是自己
• 外连接(LEFT/RIGHT JOIN)
  外连接分为左连接和右连接
• • 左连接又称为左外连接（LEFT OUT JOIN）。返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值。
• • 右连接又称为右外连接（RIGHT OUT JOIN）。恰与左连接相反，返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值。
• 联合查询（UNION与UNION ALL）

SELECT * FROM A UNION SELECT * FROM B
就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并，UNION ALL不会合并重复的记录，UNION ALL效率更高

• 全连接（FULL JOIN）返回左表和右表中的所有行。当某行在另一表中没有匹配行，则另一表中的列返回空值。

4.4 子查询

• 子查询是单行单列，父查询用=，<，>等运算符
• 子查询是多行单列，父查询用in
• 子查询是多行多列，不能用于where条件，用于select子句中作为子表

-- 1) 查询出2011年以后入职的员工信息
-- 2) 查询所有的部门信息，与上面的虚拟表中的信息比对，找出所有部门ID相等的员工。
select * from dept d,  (select * from employee where join_date > '2011-1-1') e where e.dept_id =  d.id;    

-- 使用表连接：
select d.*, e.* from  dept d inner join employee e on d.id = e.dept_id where e.join_date >  '2011-1-1'  

4.4 表的约束

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY：用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，必须是它指向的那个表中那一列的值之一
• CHECK：用于控制字段的范围

drop，delete，truncate的区别

5. 数据库的数据格式

整数类型

整数类型：tinyint，smallint，mediumint，int，bigint，分别代表1字节，2字节，3字节，4字节和8字节的整数类型，都可以加上unsigned属性。
整数类型可以被指定长度，只会影响显示字符的个数，不会限制值的合法范围
bit:bit[(M)] : 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1
bit字段按ASCII码显示

小数类型

小数类型：float（4字节，存储最多8位十进制数），double（8字节，存储最多18位十进制数），decimal（最大65位数）。
decimal能存储比BIGINT还大的整型，能存储精确的小数。
decimal每4个字节存9位，decimal(18,9)，前面9位用4个字节，小数点用一个字节，小数点后9位用4个字节

日期类型

日期类型：year，date，time，datetime，timestamp
timestamp（4字节）
datetime（8字节）

CHAR和VARCAHR

文本二进制类型：char，varchar，text，blob
char(len)：len最大255
varchar(len)：len最大65535，有1-3个字符用来记录数据长度，有效字节数是65532，在utf8中一个字符占3个字节，最大len= 65532/3=21844（windows实测21842，linux是21844），如果编码是gbk，一个字符占2个字节，65532/2=32766（实测32764）。

varchar与char的区别

char的特点

• char表示定长字符串，长度是固定的；
• 如果插入数据的长度小于char的固定长度时，则用空格填充；
• 因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
• 空格表示占位符
• 对于char来说，最多能存放的字符个数为255，和编码无关

varchar的特点

• varchar表示可变长字符串，长度是可变的；
• 插入的数据是多长，就按照多长来存储；
• varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
• 空格按一个字符存储
• 对于varchar来说，最多能存放的字符个数为65532

总之，结合性能角度（char更快）和节省磁盘空间角度（varchar更小），具体情况还需具体来设计数据库才是妥当的做法。

varchar(50),50的含义

最大存放50个字符，varcahr(50)和varchar(200)中存放“hello”所占空间一样，但后者在排序是会消耗更多内存，因为order by col采用fixed_length计算col长度。早期MySQL版本中50表示字节数，现在表示字符数

文本选择策略

• 对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。
• 对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。因为VARCAHR还要一个记录长度的额外字节。
• 使用时要注意只分配需要的空间，更长的列在排序或操作时会消耗更多内存。因为MySQL通常会分配固定大小的内存块来保存内部值。
• 尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。会使用MyISAM磁盘临时表，如果不得不使用BLOB，有一个技巧就是在用到BLOB字段的地方都使用SUBSTRING(column,length)将列值转换成字符串，这样就可以使用内存临时表，但是截取的长度不能过长，超过了某个限制会将内存临时表转换为MyISAM磁盘临时表

ENUM枚举

ENUM用来 代替常用的字符串类型，根据列表值的数量压缩到1到2个字节中，在内部会将每个值再列表中的位置保存为整数，在.frm文件中保存“数字–字符串”的映射关系。排序会根据内部存储的整数来排序。

MySQL存储IP地址

create table IP(addr int unsigned);
insert into IP values(INET_ATON(‘192.168.0.1’));
select INET_NTOA(addr) from IP;

索引

索引是一种数据结构，实现索引的数据结构有B树，B+树，hash表。InnoDB默认使用B+树。它将数据库所有记录的引用指针以文件的形式存储在磁盘上。
索引每次从磁盘读取16K，读取16K就是32个扇区，扇区512字节，索引键值对（id int（8字节），地址（8字节）），512/16=32，每一个扇区可以存储32个键值对，
假设一次磁盘IO，1个根节点，31个第二层节点，31*32=992 行记录，还要考虑聚簇索引。

索引尽量少建，浪费空间（多一个B+树），降低性能（插入数据的时候需要去维护索引）
索引尽量建主键索引，普通索引回进行回表查询，除非发生了索引覆盖。
索引在int还是char？如果char比较小，可以在char上建，一般在int上建索引，B+树的每个非叶子节点都要存储这个字段，如果字段越大，一次IO读取的节点接越少，那么这个B+树就越深，而且每个节点占的内存空间也会比较大，如果索引比较大的话会增加IO次数

B+树在满足聚簇索引和覆盖索引时不需要进行回表查询

聚集索引和主键索引很相似，是因为

1. 在InnoDB中，如果主键被定义了，这个主键索引就被作为聚簇索引，
2. 没有主键时会选择第一个唯一非空索引来建立聚簇索引，
3. 没有合适的唯一索引时，InnoDB内部会生成一个隐藏主键作为聚集索引，这个隐藏主键是一个6字节的列，随数据插入而自增
4. 自增主键会把数据自动向后插入，避免了插入过程中的聚簇索引排序问题。聚簇索引的排序必然会导致大范围数据的物理移动，此处带来的IO性能消耗很大。如果聚簇索引可以修改，也会导致物理磁盘的移动，于是会出现page分裂，表碎片横生。所以也不应该修改聚簇索引。

如何避免回表查询

联合索引

联合索引是索引覆盖的一种落地方案

alter table table_name add index ‘idx_name_age’ (name,age);

索引覆盖
如果要查询的每个字段都建立了索引，那么引擎会直接在索引表中查询而不会访问原始数据，只要有一个字段没有建立索引也会进行全表扫描，这就叫索引索引覆盖。在select后只写必要的查询字段，增加索引覆盖的概率。

索引的数据结构

B树可以在内部节点存放键和值，将频繁访问的节点放在靠近根节点处可以大大提高热点数据的访问效率。

B+树索引

每个非叶子节点存储多少个元素，由一次IO读取多少的数据量以及每个节点占用的空间大小决定
B+树的高度有限制，一般也就3-4层

B+树相对B树的优点：

• B树的每个节点都存储了键和值，而B+树非叶子节点只存放了键，值都放在了叶子节点里面。而索引一般都是很大的，不可能一次性将所有的索引全部读取到内存中，要分批次的读取，而B+树的非叶子节点只存储了键，一次性就能读取更多的索引，减少了IO读写次数，而IO读写次数是影响索引检索效率的最大因素。
• B树适合随机检索，B+树适合随机检索和顺序检索，B树元素遍历效率低下，而B+树叶子节点有指针连起来，只遍历叶子节点就可以实现整棵树的遍历
• B+树的查询效率更加稳定，每次查询都需要从根节点走到叶子节点，查询效率相当。B树的查询相当于做一次二分查找，可能找到非叶子节点就返回了
• 增删记录时B+树仙侣更高，B+树叶子节点包含所有关键字，并且以有序链表结构存储，这样可以很好的提高增删效率

hash

• hash等值访问很快，但是不支持范围查询和顺序查询
• hash每次都必须进行回表查询
• 当某个键值有大量重复时，会产生hash冲突，降低效率
• hash不能进行模糊匹配以及多列索引的最左前缀匹配，hash函数的不可预测性

索引分类

• 主键索引
  保证唯一性，字段应该短

创建方式：

1. 创建表的时候在字段的最后直接指定primary key
2. 创建表的时候在最后primary key(字段名)
3. alter table 表名 add primary key(字段名)

主键索引特点：一张表只能有一个主键索引，非空不可重复，一般设置为自增型id

• 唯一索引

创建方式：和主键索引创建的方式类似，只是把Primary key 换成unique
唯一索引特点：一张表可以有多个唯一索引，不可重复但是可以为空，如果指定了not null属性可以等价于主键

• 普通索引

创建方式：

1. 在表的最后加上index(字段名)
2. alter table 表名 add index(字段名)
3. create index index_name on table_name(字段名)

唯一索引和普通索引的字段名可以多个，建立联合索引。
CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）

• 全文索引
  在MySQL中使用全文索引，存储引擎必须使用MyISAM，但是不支持中文，如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)

alter table table_name add fulltext(字段名)

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=MyISAM;

SELECT * FROM articles WHERE MATCH (title,body) AGAINST (‘database’);

InnoDB和MyISAM的区别

适用范围

没有特别的需求，一般默认innodb。
MyISAM：效率快，适用于小型应用，跨平台性能好（表被存储成文件的形式），大量的select情况下（高速存储和检索能力还有全文索引，保证查询速度足够快），读写插入操作多
InnoDB：支持事务，如果update delete这些操作比较多，并发量高，保证数据完整性

innodb引擎的四大特性

• 插入缓冲
• 二次写
• 自适应hash索引
• 预读

事务

事务是一串DML语句组成，这些语句在逻辑上有一定的相关性，这一组DML语句要么成功，要么失败，是一个整体。

事务的ACID属性

• 原子性 Atomicity，事务是应用最小的执行单位，不可分割，事务的执行结果要么成功要么失败
• 一致性 Consistency，事务执行的结果必须从一个一致性状态转换到另一个一致性结果。当数据库只包含事务提交成功的状态时，数据库处于一致性状态。如果系统运行发生中断，导致某个事务尚未完成而被迫中断，该未完成的事务结果提交到数据库中，此时的数据库就处于一种不正确的（不一致）的状态，因此数据库的一致性是由原子性保证的。
• 隔离性 Isolation，各个事务的执行互不干扰，任何事务内部的操作对于其他事务来说都是隔离的
• 持久性 Durability，事务一旦成功提交，对数据库所做的改变都要记录到永久存储中

事务无隔离性的问题

脏读

事务1更新了数据，事务2读取了更新后的数据，但事务1由于某些原因回滚了，事务2读取的数据就是脏读

不可重复读

事务1读取了数据，此时可能有其他事务来更新了这个数据，事务1再次读取的时候发现数据已经不一样了

幻读

比如事务1对数据进行了统计或者全局性的更新，事务2此时来增加了一条新的数据，事务1再次来查看的时候就会发现统计结果不一样或者还有一条数据没有被更新，就像出现了幻觉一样

事务的隔离机制

锁

在数据库有并发事务的时候，用来维护访问数据次序的机制。

按照锁的粒度分

表级锁
页级锁
行级锁

SQL优化

如何定位低效sql语句，对可能低效的原因做排查，先从索引着手，如果索引没有问题，考虑数据访问，长难查询句，还有一些特定类型优化的问题

执行计划

对于查询语句，最重要的优化方式就是使用索引，执行计划可以显示数据库引擎对于SQL语句的执行的详细情况

• id：表示一个查询中各个子查询的执行顺序，id越大越先被执行，id相同时执行顺序从上往下；id为null时表示一个结果集，不需要使用它查询，长出现在UNION等查询语句中
• select_type：每个子查询的查询类型
• type：访问字段
• • ALL 扫描全表
• • index 遍历索引，索引物理文件全扫描
• • range 索引范围查找
• • ref 使用非唯一索引查找数据，普通索引
• • consts：单表中最多一个匹配行
• • ea_ref在join查询中使用PRIMARY KEY 或者是UNIQUE NOT NULL索引关联
• possible_keys：可能使用到的索引
• key：实际使用的索引
• key_length：索引长度，在不损失精度的情况下，越小越好
• rows：表示估算的结果集数目，并不是一个准确的值
• ref
• extra
• • using index
• • using where
• • using where,using index
• • using index

利用延迟关联或者子查询优化超多分页场景。

SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

慢查询日志

使用

show variables like ‘slov_query_log’

查看慢查询日志是否开启，如果是OFF，可以使用

set GLOBAL slow_query_log = on

来开启，它会在datadir下产生一个xxx-slow.log的文件

修改临界时间，单位秒

set long_query_time=0.5

实操时应该从长时间设置到短的时间，即将最慢的SQL优化掉

查看日志，一旦SQL超过了我们设置的临界时间就会被记录到xxx-slow.log中

慢查询的优化

慢查询的优化首先要搞明白慢的原因是什么，是没有命中索引？load了不需要的数据？还是数据量太大？

• 首先分析语句，是否多写了其他不必要的字段
• 分析语句的执行计划，查看语句使用索引的情况，之后再修改语句或者修改索引
• 如果语句已经无法进行优化，就可以考虑是不是数据量太大，从而考虑分表的问题
• • 水平分表
• • 垂直分表

为什么要设置主键，自增id还是uuid

主键是数据行在整张表中唯一性的保障，设定主键后，在后续的删改查的时候可能更加快速。

推荐使用自增ID，在innodb中，主键索引是作为聚簇索引存在的，主键索引的叶子结点上按顺序存储了主键和全部数据。自增ID的话，插入数据只需要不断地向后排列；uuid的话，由于到来的ID与原来的ID大小不确定，会造成大范围的数据物理移动，page分裂，表碎片横生的情况，最终就是插入性能下降。

not null

null值会占有更多的内存

MVCC

事务ACID和undo log，redo log

修改数据库

alter语句，对数据库的修改主要指的是修改数据库的字符集，校验规则。

alter database mytest charset=gbk;

删除数据库

drop database db_name;

执行删除之后的结果:

• 数据库内部看不到对应的数据库
• 对应的数据库文件夹被删除，级联删除，里面的数据表全部被删

备份与恢复

查看连接情况

表

show processlist

可以用来查看有哪些用户连接了数据库，如果有异常用户可能代表着你的数据库被入侵了

查看表结构

desc table_name;

创建表

create table users (
id int,
name varchar(20) comment ‘用户名’,
password char(32) comment ‘密码是32位的md5值’,
birthday date comment ‘生日’
) character set utf8 engine MyISAM;

在表中插入记录

insert into users values(1,‘a’,‘b’,‘1982-01-04’),(2,‘b’,‘c’,‘1984-01-04’);
假设主键冲突，可以使用更新操作：

insert into 表名 values(值列表) on duplicate key update 字段1=值, 字段2=值

mysql中常用的三种插入数据的语句:

insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
replace into表示插入替换数据，需求表中有PrimaryKey，
或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；

添加字段

alter table users add assets varchar(100) comment ‘图片路径’ after birthday;

修改表中是name，将其长度改为60

alter table users modify name varchar(60);

删除表中某个字段

alter table users drop password;

修改表名

alter table users rename to emploee;

表中属性改名

alter table emploee change name xingming varchar(60);
新字段需要完整定义

删除表

drop table emploee;