mysql通用建表语句：

CREATE TABLE IF NOT EXISTS stuinfo (
  id INT PRIMARY KEY AUTO_INCREMENT, #自增,主键非空唯一，会创建主键索引
  stuname VARCHAR (20) NOT NULL,#非空
  gender CHAR(1),
  seat INT UNIQUE,#唯一约束，会建立唯一索引
  age INT DEFAULT 18,
  majorID INT,
  CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id)#外键可以有多个,会自动建立索引
)ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

### `逻辑架构概`

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，
插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
在这里插入图片描述
1.连接层
最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2.服务层
2.1 Management Serveices & Utilities：系统管理和控制工具
2.2 SQL Interface: SQL接口
接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
2.3 Parser: 解析器
SQL命令传递到解析器的时候会被解析器验证和解析。
2.4 Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。
用一个例子就可以理解： select uid,name from user where gender= 1;
优化器来决定先投影还是先过滤。
2.5 Cache和Buffer：查询缓存。
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
3.引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB
4.存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

`mysql查询流程图`

首先，mysql的查询流程大致是：

mysql客户端通过协议与mysql服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析,也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。
语法解析器和预处理：首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；预处理器则根据一些mysql规则进一步检查解析数是否合法。
查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
然后，mysql默认使用的BTREE索引，并且一个大致方向是:无论怎么折腾sql，至少在目前来说，mysql最多只用到表中的一个索引。

`SQL执行顺序`

手写：在这里插入图片描述
机读：随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。下面是经常出现的查询顺序：

`Mysql存储引擎`

1 如何用命令查看
看你的mysql现在已提供什么存储引擎:
mysql> show engines;
在这里插入图片描述
看你的mysql当前默认的存储引擎:
mysql> show variables like ‘%storage_engine%’;

各个存贮引擎的介绍
1、InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

2、MyISAM存储引擎
MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

3、Archive引擎
Archive档案存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。
Archive表适合日志和数据采集类应用。
根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。

4、Blackhole引擎
Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

5、CSV引擎
CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。
CSV引擎可以作为一种数据交换的机制，非常有用。
CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。

6、Memory引擎
如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。

7、Federated引擎
Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

对比项 | MyISAM |范德萨

MyISAM	InnoDB
不支持外键	支持外键
不支持事务	支持事务
表锁不适合并发	行锁适合并发
只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
适合节省资源、消耗少、简单业务	适合并发写、事务、更大资源

`索引优化分析`

性能下降SQL慢执行时间长等待时间长：
数据过多——分库分表
关联了太多的表，太多join——SQL优化
sql语句写的烂，索引失效变全表扫描
没有充分利用到索引——建立索引
服务器调优及各个参数设置——调整my.cnf

Join图
在这里插入图片描述

`索引简介`

MySQL官方对索引的定义为：**索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。**索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要a----z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？
你可以简单理解为“排好序的快速查找数据结构”。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

在这里插入图片描述
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

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

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

`索引的优缺点`

优点：类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本，通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗——排序和和查询快

缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的——本身站存储空间，更新表的同时会更新数据索引

`mysql索引分类`

单值索引:只包含一个列，一个表可以有多个

单独建单值索引：CREATE  INDEX 索引名 ON 表名(表字段); 
删除索引：DROP INDEX 索引名  on 表名;

唯一索引：索引列的值必须唯一，但允许有空值

        单独建唯一索引：CREATE UNIQUE INDEX 索引名 ON 表名(表字段); 
        删除索引：DROP INDEX 索引名  on 表名;

主键索引:设定为主键后数据库会自动建立索引，innodb为聚簇索引

 单独建主键索引：
                ALTER TABLE 表名 add PRIMARY KEY 表名(表字段);  
 删除建主键索引：ALTER TABLE 表名 drop PRIMARY KEY ;

复合索引：即一个索引包含多个列

单独建索引：
CREATE  INDEX 索引名 ON 表名(表字段,表字段); 
删除索引：
DROP INDEX 索引名  on 表名 ;

索引基本语法

创建CREATE  [UNIQUE ]  INDEX [indexName] ON table_name(column)) 
删除：DROP INDEX [indexName] ON mytable; 
查看：SHOW INDEX FROM table_name\G

使用ALTER
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
 
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
 
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
 
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

哪些情况需要创建索引

主键、唯一、外键自动创建索引
频繁查的数据建立索引
where条件建立索引
查询中与其它表关联的字段，外键关系建立索引
组合索引比单指索引好（高并发）
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

哪些情况不要创建索引

表记录太少
经常增删改的表或者字段
Where条件里用不到的字段不创建索引
过滤性不好的不适合建索引，重复字段多的，比如性别

性能分析

MySQL常见瓶颈：

CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态

Explain：分析你的查询语句或是表结构的性能瓶颈

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的应用
每张表有多少行被优化器查询
用法：Explain+SQL语句

- id
表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在

衍生：DERIVED，表示临时表

select_type：查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

SIMPLE：简单的select查询，查询中不包含子查询或者UNION。
PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
SUBQUERY：在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里。
DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）。MySQL会递归执行这些子查询，把结果放在临时表里。
UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED。
UNION RESULT：从UNION表中获取结果的SELECT。

table：显示这一行的数据是关于哪些表的。
type： 显示的是访问类型
结果值从最好到最坏依次是：

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>All
在这里插入图片描述
system>const>eq_ref>ref>range>index>All
1. system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。
2. const：表示通过索引一次就找到了，const用于比较primary key或则unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
5. range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不会扫描全部索引。
6. index：Full Index Scan，index与All区别为index类型只遍历索引树。这通常比All快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
7. all：Full Table Scan，将遍历全表以找到匹配的行。
一般来说，得保证查询至少达到range级别，最好能达到ref。

possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出。但不一定被查询实际使用。
key：实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中，不会出现在possible_keys列表中。（覆盖索引：查询的字段与建立的复合索引的个数一一吻合）
key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。查询中与其它表关联的字段，外键关系建立索引。
rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
Extra：包含不适合在其他列中显示但十分重要的额外信息。
- Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为“文件排序”。
- Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
- Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
- Using where：表明使用了where过滤。
- Using join buffer：使用了连接缓存。
- impossible where：where子句的值总是false，不能用来获取任何元组。（查询语句中where的条件不可能被满足，恒为False）
- select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
- distinct：优化distinct操作，在找到第一匹配的元组后即停止找相同值的动作。

覆盖索引：

在这里插入图片描述

索引优化

在这里插入图片描述

单表

原文链接：https://blog.csdn.net/qq_34800986/article/details/105036581