mysql笔记

sql语句分为4中
定义语句：drop, create,alter等
操作语句：insert,update,delete等
查询语句：select
控制语句：grant,revoke,commit,rollback等

5.7的新功能
1、支持json
2、性能和扩展行，改进了InnoDB的可扩展性和临时表的性能。实现更快的网络和大数据加载
3、改进复制包括多源复制、多从线程增强、在线GTIDs和增强的半同步复制。
4、性能模式提供能号的视角，增加了许多监控功能减少空间和过载，使用新的SYS模式提高易用性。
5、安全
6、优化，重写了大部分解析器、优化器、和成本模型，提高了可维护性可扩展性和性能。
7、GIS 全新功能，包括InnoDB的空间索引。

InnoDB默认引擎的主要特性：
1、具有提交、回滚、和崩溃恢复能力的事务安全(ACID兼容)。锁定在行级也在select语句中提供一个非锁定读。SQL查询中可以使将InnoDB类型表和其他类型表混合起来同一个查询。
2、InnoDB是处理巨大数据量的最大性能设计。CPU效率是其他任何基于磁盘关系的数据库引擎不能匹敌的。
3、InnoD引擎完全与MYSQL服务器整合，InnoDB为在主内存中缓存数据和索引而维持它自己的缓冲池。（为缓存数据和索引维持自己的缓冲池！！！！！）
它将它的表和索引存在一个逻辑表空间里。表空间可以包含数个文件。InnoDB表可以是任何尺寸，因为它自己有表空间，有数个文件。（将表和索引存在表空间里）
4、InnoDB外键完全约束。表张的数据按照主键顺序存放，没有定义逐渐，会为每一行生成一个6B的ROWID作为主键。
5、InnoDB不用在需要高性能的大性数据库站点上。它不创建目录，在MYSQL数据目录下创建一个ibdata1的大小自动扩展的数据文件，以及两方各日志文件file1 file2

InnoDB 存储限制64TB

支持：

数据缓存
数索引
BTREE索引
事务
外键

不支持：

全文索引
hash索引

创建表命令
create table <表名>
(
字段名，数据类型[列级别约束条件]，[默认值]
)

约束：
主键约束（主键列数据唯一） 单字段主键 和对字段联合主键 primary key 单 id int(11) primary key ; 多 primary key (id,name);
外键约束 建立两个表数据之间的链接，可以是一列或者多列，一个表或者多个表的外键，必须是另外个表的主键值（外键空除外）。对自己表来说可以不是主键，但是对应另外一个表的主键。用来保证一致性和完整性。
非空约束 not null 不能为空
唯一约束 字段名 类型 unique 此字段可以为空，但是只有一个空。 每个数据都是唯一的。
默认约束 字段名 类型 default 1111; 默认为1111

自增：字段名 类型名 auto_increment

desc 查看表结构
show create table \G 查看表详细结构
alter table <旧表名> rename <新表名>

show tables 查看数据库中所有表
use table 使用哪个表
alter table <表名> modify <字段名> <数据类型> modify 修改字段类型
alter table <表名> change <旧字段名> <新字段名> <新数据类型> change 和modify都能改变字段类型。
alter table <表名> add <字段名><数据类型><约束条件 first|after> 添加新字段 first 将字段放在表所有字段最前面， after 将字段放到某字段后面。
alter table <表名> drop <字段名> 删除字段。
alter table <表名> modify <字段名> <数据类型> first|after<字段2> 修改字段的排列位置。
alter table <表名> drop foreing key 删除外键约束 drop foreing key

drop table 表1，表2，表3 删除表

整数类型: 5种分有符号无符号

tinyint 1字节
smallint 2字节
mediumint 3字节
int 4字节
bigint 8字节

小数类型：3种 有符号和无符号

float 4字节
double 8字节
这两种以（M,N）表示，M为精度，N为标度是小数点的位数。

dec（M,D） M+2字节
dec范围和double 一样，但是有限取值范围由M和D决定

日期类型：5种
year YYYY 1字节 范围1901-2155
time HH:MM:SS 3字节
date YYYY:MM:DD 3字节 1000-01-01~ 9999-12-3
datetime YYYY:MM:DD HH:MM:SS 8字节 1000-01-01~ 00:00:00~ 9999-12-31 23:59:59
timestamp YYYYlMM:DD HH:MM:SS 4字节 1970-01-01~ 00:00:00~ 2038-01-19 23:59:59

文本字符串类型： 8种
char(M) M字节 M<255 固定长
carchar(M) M最大取2的16次 65535 ，实际是L+1个字节，不定长。
tinytext L+1个字节 L<2 8次 256 255最大
text L+2个字节 L<2 16次 65534最大
mediumtext L+3个字节 L<2 24次 16777213最大
longtext L+4分字节 L<2 32次 4294967288最大
enum 1或2个字节 最大数目65535 ，因为2字节是2的16次
set 1，2，3，4，8个字节，取决于集合成员的数量。

查询语句：
select [字段1，字段2，字段3] from [表或视图] where [查询条件]

select字段可以一个或者多个。
from表可以一个或者多个。
where 可选项，有将限定查询条件。
group by<字段> 按照查询的字段分组
limit <偏移，行数> 显示每次查询出来的条数。

select * from 表； 单表查询所有字段。
select a1,a2 from 表，指定查询字段。
where =,!= <, <=,>,>= between两值之间
where a1 = 1; 限定a1 = 1的。
where a1 in (100,199) ;查询a1是100或者100的。
where a1 between 10 and 20 ;查询 a1 是10 - 20的。
where a1 like “b%” ;查询a1 以b开头的。 %可以匹配无数个
where a1 like “_y” ;查询a1 2个字符以y结尾的。_只能匹配一个字符。
where a1 is null ; 查询a1 是空值的。
where a1 is not null ; 查询a1 不是空值的。
where a1 =101 and a2 =102 ;查询 a1=101 并且a2=102的。
where a1 =101 or a2 =102 ;查询 a1=101 或者a2=102的。 也可以用in 实现 比如只查a1 为 101 或 102 的 a1 in (101,102);in速度快于or
select distinct 字段名 from 表名 ; distinct 消除字段重复值。distinct !!! 在select 后面。
order by 字段名1,字段名2 （asc升序 ，desc降序）。 默认升序排序。只有一个字段就只排一个字段，多个字段按照先后顺序排序。 也可以 order by a1 desc, a2 .这样仅对a1降序，a2没排序。
group by a1 having <条件表达式>。按照 a1字段分组。 还要满足having的条件。 where 是分组之前条件，having是分组之后的条件。 group by a1,a2 会先按照a1分组再按a2分组。
group by 后可以接 order by。

select count(*) as num from table ; as 给字段起别名。 返回总行数。最好不用，性能差。
函数count(字段名) 返回字段不为空的总行数。
函数sum(字段名) 返回字段名的总和
函数avg(字段名) 返回平均数
函数max(字段名) 返回最大值
函数min(字段名) 返回最小值

select table1.id,A,B from table2 inner join table1 on table2.id = table1.id; inner join 后接 on . 和where的指定条件相同。 where 有时会影响性能，on是ANSI SQL的标准规范。

外连接：
左连接 返回左表所有的行和右表符合的行 A left outer join B ,A为左表全展示
右连接 返回右表所有的行和左表符合的行 A right outer join B ,B为右表全展示

any some all exists子查询
select num1 from t1 where num1 > any(select num1 from t2); 查询t1中num1字段任何一个大于t2 num1 字段中的数据。
select num1 from t1 where num1 > all(select num1 from t2); 查询t1中num1字段中比t2 num1 字段中全部数据都大的数据。
select num1 from t1 where exists> (select num1 from t2); 如果有子查询有值，exists将返回true，外层语句将查询。否则返回false，外层语句将不会查询。

union 合并同样数据类型和列的多表查询:
select a1 from t1
union
select a1 form t2;

字符串匹配
where 后面接 regexp 接正则表达式 比如 where a1 regexp ‘^b’;查询以a1字段以b开头的。
^开头
$结尾
.单个字符
*多个字符
+匹配前面字符至少1次 'ba+'匹配至少一次a
‘in’ 指定字符串。
[^] 不匹配里面任意字符
字符串{n} 至少匹配前面字符串n次
字符串{n,m} 匹配前面字符串n到m次

——————————————————————————————————————————————-
划重点 索引！！！！！！：
索引是对数据库表中一列或者多列数据进行排序的一种结构。
索引单独的，存储再磁盘上的数据库结构。包含对数据表里记录的引用指针。用于快速找出某个或多个列中有特定值的行。

优点：
1、唯一索引保证每行数据唯一性
2、加快查询速度
3、加速表与表的连接
4、分组和排序语句中显著加快查询中分组和排序的时间。

缺点：
1、创建和维护索引要耗费时间，数据量增加会耗费更多
2、索引需要占磁盘空间，除了数据表占数据空间外，每一个索引要占一定物理空间，如果有大量索引，索引文件可能比数据文件更快岛打最大文件尺寸，比如InnoDB 64T
3、对表中的数据增删改时，索引也要动态维护，降低了数据的维护速度。

分类：
1、普通索引和唯一索引：普通索引允许在定义索引列中插入重复值和空值；唯一索引列数据必须唯一，允许有1个空值，如果是组合唯一索引，组合必须唯一。 主键索引不允许有空值。
2、单列索引和组合索引：组合索引指在多个字段上建立的索引，只有查询条件中使用了这些字段的左边字段时，才会被使用。组合索引遵循最左前缀集合。

索引建立原则：
1、避免对经常更新的表进行过多索引，索引列尽量少。对经常查询的要建立索引，但避免不必要的字段。
2、小表不要使用索引，表小比遍历索引查询时间可能更快。
3、不同值多的建立索引，不同值少的避免建。
4、唯一索引是数据特征是应该建立。
5、在频繁进行分组排序的列上建立索引，如果排序列有多个，在多个上进行组合索引。

mysql 体系结构和存储引擎：
mysql体系结构分为两层1、MYSQL server层 2、存储引擎层，
server分为1、连接层和2、sql层。
应用层序通过接口连接mysql 最先处理的是连接层（包括通信协议、线程处理、用户密码认真三部分）。
通信协议负责检测客户端版本是否兼容mysql服务端，线程处理指每一个连接请求都会分配一个对应线程相当于一条sql一条线程对应一个逻辑CPU，并会在多个逻辑CPU之间进行切换。用户验证创建的账户密码，及host主机授权是否可以连接MySQL服务器。
sql层包含权限判断、查询缓存、解析器、预处理、查询优化器、缓存、执行计划。
连接->sql->存储引擎层。
权限判断用户有没有某库某表的权限；
查询缓存通过query cache 操作。 如果数据在query cache 中，直接返回给客户端。
查询解析器解析对sql语句进行解析，判断语法正确
预处理器对解析器无法解析的语句进行处理
优化器对sql进行改写和相应的优化，并生成最优执行计划。

连接层（通信协议、线程处理、用户认证）；
sql层（权限判断、查询缓存、查询解析器、预处理器、优化器），最终通过调用层序的API接口，通过引擎层访问数据。

query cache 在生产中建议关闭， 它只缓存静态数据。

InnoDB 支持事务，行锁、支持MVCC多版本并发控制，8。0后MyISAM废弃。
InnoDB 数据和缓存都存储在.Ibd文件里，并且缓存在内存里。
mysql 数据库是个单进程多线程模型的数据库。
操作数据的时候是在数据库实例中进行的，也就是内存加进程。
InnoDB体系结构实际上是由内存结构、线程、磁盘文件三层组成。
InnoDB存储单元分为 表空间->段->区->页

InnoDB引擎表中所有数据都是存储在表空间里面，表空间分为系统表空间，独立表空间，共享表空间

系统表空间：安装数据库狮虎就会创建一个ibdata1系统表空间文件，存储所有的数据的信息以及回滚段信息。 5.6之后，回滚段可以通过参数单独设置存储位置。从ibdata1中独立出来了。
Innodb_data_file_path负责定义系统表空间的路径、初始化大小、自动扩展策略。默认自动扩展大小是64M.ibdata1默认大小是10M。高并发事务的时候建议调整到1GB大小。

独立表空间：mysql默认使用独立表空间，就是没个表都有自己的表空间文件。不用存储在ibdata1中。
独立表空间文件对应表的b+树数据、索引、插入缓冲等信息。其余信息还是存储在默认表空间里。独立表空间有两个.frm .ibd文件描述符，单表过长会出现性能问题。

共享表空间的数据和文件放在一起方便管理，但是无法在线回收空间。 综合表现独立表空间好一些。

临时表空间 放临时表的数据。
通用表空间 将多个表放在同一个表空间里。

段：表空间由段组成。通常有数据段、回滚段、索引段。段空间扩展是以区为单位的。
区：每个区由物理分配的连续空间，固定大小是1MB。
页：1个区由64个连续的页组成，页默认大小是16KB。
行：一页至少存2行数据。

mysql内存结构：
1系统全局区
2缓存区

系统全局区：
1、innodb_buff_pool 存放innodb表的数据、索引、插入缓冲、数据字典等
2、innodb_log_buffer 事务在内存的缓冲。redo log buffer大小。
3、innodb_additional_mem_pool_size 保存数据字典信息和其他内部数据结构的内存池大小。 5.7.4 后移除。

缓存区
1、sort_buffer_size sql语句的在内存中的临时排序。
2、join_buffer_size 表连接用
3、read_rnd buffer_size 随机读缓冲区大小。

Buffer状态及其链表结构.
page是磁盘i/o最小单位，对应到内存中就是一个个buffer块。
buffer 分为三种。
free 白纸 空
clean 和磁盘数据一样
dirty 没有刷新到磁盘和磁盘数据不一样。
butter 由chain 链来管理。是双向链表， 三种buffer 衍生出来三条双向链表。
free list free状态的buffer连起来
lru list 将把与磁盘数据一致，最近最少被使用的buffer串联起来。
flush list 把dirty buffer 串联起来，方便刷新线程把脏数据刷到磁盘。 我们把最近最少被弄脏的数据串起来 ，刷新到磁盘，释放出来free buffer 供我们使用。

InnoDB多线程体系：
主循环loop;
后台循环background loop
刷新循环 flush loop
暂停循环
根据数据运行状态在4个循环之间切换

每1s:
1、日志冲刷近磁盘
2、刷新脏页到磁盘
3、执行合并插入缓冲
4、产生chekpoint
5、清除无用的table cache
6、没用用户活动就切换到background loop

每10s：
1、日志冲刷
2、合并插入缓存
3、刷新脏页到磁盘
4、删除无用的undo回滚页
5、产生checkpoint

四大线程 读线程 写线程 日志线程 插入缓冲冲刷到磁盘线程。

数据库文件：/etc/my.cnf -> /etc/mysql/my.cnf/ -> /use/local/mysql/my.cnf -> ~/.my.cnf 有限级别的顺序去读取参数文件。
在默认文件里面分类 client.section（配置客户端参数） 和 server section（配置服务端） 两块。

服务器端server配置优化：
innodb_buffer_pool 该缓冲池位于主内存中，innodb用它来缓存被访问过的表和索引文件，常使用的直接在内存中处理，提升处理速度。5.7可以在线设置大小，50%以上好。
innodb_buffer_pool_instance 理解为Innodb的缓冲区被划分为多少个实例，可以提高并发。 需要上面缓冲池大于1G此参数生效。设置后，每个缓冲区各自管理自己的数据互不干扰。
innodb_data_file_path指定系统表空间文件路径和ibdata1大小。
innodb_buffer_pool_load_at_startup
innodb_buffer_pool_dump_at_shutdown 这两个实例是关闭数据库实例的时候把热数据dump出来，保存到文件中，再次启动实例的时候，快速将元数据加载回内存。
innodb_thread_concurrency InnoDB内核最大并发线程数，0表示无限制。
innodb_flush_method 配置innodb数据文件 、redo log 文件的打开刷写模式。（建议O_DIRECT）
innodb_old_blocks_time
innodb_old_blocks_pct 这两个配置参数用来缓存数据和索引文件，内部由LRU链表管理。 超过time设置的时间未被访问就从young list 移动到old list文件里。 pct设置old pages list占整个列表的百分比。
innodb_open_files 同时打开表空间数，设置为65535最大
innodb_log_buffer_size 日志缓冲大小
innodb_log_file_size 日志文件大小。
max_connections mysql 最大连接数 默认151. 尽量将这个调大，高并发状态，考虑服务器承载。
thread pool 商业版中引入了线程连接池。 可以让连接复用。

expire_logs_days binlog 过期时间，单位天
slow_query_log 慢查询日志是否开启
long_query_time 超过设置时间记录到慢查询日志中。
log_queries_not_using_indexes 没有使用到索引会记录到慢查询日志里。
server_id 标识主机 主从结构中。
auto_increment_increment 自增默认量 默认1
auto_increment_offset 表示自增从那里开始 默认1
innodb_io_capacity innodb后台进程最大的I/O性能指标，影响刷新藏页和插入缓冲的数量。默认20，在高转速磁盘下，可以适当提高。
innodb_change_buff_max_size 插入缓冲区占总的缓冲区百分比。

innodb的三大特性：
1、插入缓冲 把普通索引上的DML操作从随机I/O变成顺序I/O。先判断插入的普通索引是否在缓冲池，在就直接插入，不在先放到change_buff中，然后进行change_buff和普通索引的何必跟操作，将多个插入合并到一个操作中。
2、2次写，保证写入的安全性。
3、自适应hash 如果注意到查询可以通过建立哈希索引得到优化，innodb会自动完成。 通过innodb_adaptive_hash_index 参数来控制。
自适应哈希是分区的，每个索引会绑定到特殊的分区行。每个分区都有自己的锁存器来保护。 innodb_adaptive_hash_index_parts 来控制分区， 默认8个，最大可以设置512个分区。

文件:

PID文件记录mysql自己的pid
mysql.sock文件 是服务器与本地客户端进行通信的nunix套接字文件。默认位置在/tem/mysql.sock

表产生碎片 delete时候 并不会把数据文件真实删除，只是将标识位删除，没有整理数据文件，不会彻底释放表空间。
清理碎片：alter table tablename engine=innodb（此方法缺点会给表加写锁，业务高峰不建议使用） 或者备份表数据，删掉表重新导入新表。

b+tree 是由 二叉树->平衡二叉树->B-tree演化而来的。
二叉树， 左子叶比右子叶小，子叶小于根
平衡二叉树，左右子叶高差绝对值不超过1 ，可以左旋，可以右旋。
B+树 所有叶子节点保存数据，而且叶子节点是双向链表！叶子节点又在一层 ，所以访问关键字的时候是顺序的，不会跑到上一层，b-tree会跑到上一层。

聚集索引：一种索引组织形式，索引键值的顺序决定了表数据行的物理存储顺序，聚集索引叶子节点存放表中所有行数据记录的信息。所以 索引 就是 数据。innodb的主动的主键就是聚集索引。
如果一张表没主动创建主键innodb会选择第一个不包含又null值的唯一索引作为主键。如果连唯一索引都没有，innodb会为该表默认生成一个6字节的rowid作为主键。

普通索引在叶子节点上不包含所有行的数据记录，只会存又自己本身的键值和主键的键值。检索时，通过普通索引的主键找到btree树中的聚集索引中的主键获得数据。 普通索引包含主键索引的值。

ICP是检索索引行数据时一种优化方式。 默认开启。
MRR把普通索引的叶子节点上的主键值集合存储到块中，对主键值进行排序。read_rnd_buffer_size 设置 mrr = on；
BKA提高join性能算法。通过optimizer_switch参数设置。

索引总结：
innodb主键索引就是聚集索引。是b+tree 。仅有一个主键索引，可以由表中多个或者一个字段组成。必须唯一,不能null，必须自增。
覆盖到普通索引找到主键索引的值就查到了数据返回。
前缀索引：文本前几个字符建立索引。
联合索引（符合索引）两个或两个以上的列创建的索引。联合索引使用最左原则。 or 无法使用索引!!! 覆盖不了。最左都覆盖不了！用union 多表联合都不要用or

索引有点：
1、提高数据检索效率
2、提高聚合函数效率
3、提高排序效率
4、使用覆盖素银可以避免回表 覆盖索引（直接从索引中获得数据）

索引创建四不要：
1、选择性低的不要创建
2、很少查询的列不要创建
3、大数据类型字段不创建
4、避免 null

使用不到索引的情况：
1、通过索引扫描超过30%会权标扫描
2、联合索引中，第一个查询条件不是最左索引列
3、联合索引中，第一个查询条件使用了范围查询，只能使用到部分索引，<,=,<=,between and
4、联合索引中，第一个查询条件不算最左前缀列
5、模糊查询中最左以%开始 ，可以考虑放到子查询中。
6、两个单列索引，一个用于检索，一个用于排序 ，只能用到一个索引。 以为查询语句最多只能用1个索引。考虑联合索引。
7、查询字段上由索引，但是使用了函数运算。

事务语句begin 或者start transaction 开始 commit 或者rollback结束 ， commit 提交 ，rollback 回滚。
delete可以回滚，truncate 清空自增数据并不能回滚。

mysql 默认事务隔离级别 ：可重复读（在事务结束前都可以读到事务刚开始看到的数据直到事务结束后的更改）

mysql锁分类：
读锁（共享锁）
写锁（排他锁）
意向锁 MDL锁

读锁：一个事务获取了读锁，其他事务能索取对应行的读锁，单不能获得写锁。（一个事务在读一个数据行时，其他事务也可以读，不能改该数据。）
写锁：有限级最高，一个事务获得了写锁后，其他事务不能获取该行其他锁。
意向共享锁：给一个数据行加共享锁之前要先取得意向共享锁
意向排他锁：在给一个数据行加排他锁前要取得意向功能锁
间隙锁：避免幻读，锁定行记录数据的范围，不允许在这个范围插入任何数据。
记录锁：在间隙所之前加记录锁

死锁 innodb会自动回滚死锁

mysql的优化主要是优化表和索引以及查询的使用方式 （原因）
mysql 记录慢查询：
初衷 是在 sql 和 索引建立之前知道哪些查询是需要我们优化的，是有效率问题的。
在mysql中有慢查询日志会把有效率问题的日志记录下来。
show variable like ‘slow_query_log’;查看变量 是否开启慢查日志
set global slow_query_file = ‘’;设置慢查询日志存储的位置
set global log_query_not_using_indexes = on; 是否将没有使用索引的记录到慢查询日志中。 （所以）
set global long_query_time = 1; 查询大于多少秒的记录记录到慢查日志中。大型生产环境0.01秒内。
慢查日志格式：
执行sql的主机信息: root 用户 本机查询
#User@host : root[root] @local[]
SQL的执行信息 查询时间 锁定时间 发送行数 扫描行数
#query_tiome:0.000024 lock_time:0.0000000 rows_sent:0 rows_examined: 0;
执行时间 时间戳记录的执行时间
set timestamp=1400000000;
SQL内容
select CONCAT(“storage engine:”,@@storage_engine) as INFO;
慢查询日志分析工具
1、mysqldumpslow
2、pt-query-digest（总查询时间、最小（查询）时间）、总锁定时间、发送行、扫描行、扫描行过于大于请求行说明索引建立不好）哪些表哪些操作耗时长。具体查询的sql语句
rows -examine 越多 扫描越大 i/o消耗越大
rows -examine 远远大于 rors-send 行数 这个sql 索引命中率不高
explain 接 sql 返回参数：
table 表
type const 常数查找（主键、唯一） eq-reg (范围查找、唯一索引)、ref（常见连接查询 比如一个表基于某个索引查找） range（基于XX范围） index（索引扫描） all（全表扫描）
possible_key ：可以用的索引
key:实际使用的索引
key_len:使用到的索引长度，越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
row：返回的行数。
Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

distinct 去重
group by 最好在一个表 字段（列里进行）
过滤条件最好在一个表里进行不要在外层。 比如在子查询里面先过滤。
limit 建立主键 主键是按照顺序排列的。 然后可以记录上次的id值。 比如第一次 50,5 记录55， between 55 and 60