msyql基础及优化

msyql基础：
sql概述：
Sql:（Structured Query Language），是关系型数据库的标准语言，它的特点是：简单、灵活、功能强大。它包含6个部分：
1、数据查询语言（DQL）：data query language
其语句，也成为“数据检索语句”，用以从表中获取数据。确定数据怎样在应用程序中给出。保留字select 是DQL。常用的DQL保留字有：where、order by，group by，having
2、数据操作语言（DML）：data manage language
称为动作查询语言，insert ，update，delete。它们分别用于添加，修改，删除表中的行。
3、事务处理语言（TPL）：java概念
它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。
事务：对于一组操作 要么全部成功 要么全部失败
4、数据控制语言（DCL）：
它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
5、数据定义语言（DDL）
其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
6、指针控制语言（CCL）
它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作
数据库的优化技巧：
单机：
3NF：列不可分割；表中的记录有唯一标识；表中尽量不要有冗余数据，能够推导出来尽量分开
合适引擎:
索引：
分表：
SQL优化：
多机：
集群：读写分离
分布式：把不同的业务分给不同的集群处理
其他方案：
redis缓存，页面静态化，ES全文检索，doris分布式查询。
.psc：备份文件.
msyql的执行流程：
客户端—》连接器—》分析器----》优化器----》执行器—》存储引擎
客户端—》查询缓存—》命中后直接返回结果 (8.0之后直接干掉了缓存)
连接器：主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作
分析器：分析器主要是用来分析SQL语句是来干嘛的。第一步，词法分析，一条SQL语句有多个字符串组成，首先要提取关键字，比如select，提出查询的表，提出字段名，提出查询条件等等第二步，语法分析，主要就是判断你输入的sql是否正确，是否符合mysql的语法。
优化器：优化器的作用就是它认为的最优的执行方案去执行（虽然有时候也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。
执行器：当选择了执行方案后，mysql就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用存储引擎的接口，返回接口执行的结果。
存储引擎：主要负责数的存储和读取。INnoDB5.5.5版本作为默认引擎。
查询缓存：连接建立后，执行查询语句的时候，会先查询缓存，Mysql会先校验这个sql是否执行过，以Key-Value的形式缓存在内存中，Key是查询预计，Value是结果集。如果缓存key被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用

#查看mysql运行时间
show status like 'uptime'
#查询数据库各种操作的次数
show status like '%com_%'
show status like '%com_delete%'
show status like '%com_insert%'
#查看所有的连接数
show status like 'connections'
#查看最大的连接数，my.ini
show status like 'max_user_connections'
#查看sql执行情况
show processlist
#查询慢查询sql，因为mysql默认的慢查询时间是10s
show status like 'slow_queries'
#查看数据库默认的慢查询时间
show variables like 'long_query_time'
#修改数据库默认的慢查询时间(只对当前会话有效)
set long_query_time = 0.5
#修改数据库默认的慢查询时间(只对当前服务有效，重启之后失效)
set global long_query_time = 5
#查看慢查询日志路径,off表示没有开启慢查询
show variables like '%slow_query_log%'
#开启慢查询
set global slow_query_log =1
#设置慢查询日志输出到表中
set global log_output= 'TABLE'
#查询表
select * from mysql.slow_log
#explain 分析sql查询
#分析慢查询，tapy all 全表扫描， possible_keys 可以查看是否创建索引  key查看实际使用的索引  因为有可能索引失效没有使用到索引 rows 查看扫描的行数
explain SELECT * from emp where empno = 290688
#查看sql执行流程
set profiling =1; #开启执行查看
SELECT * from emp limit 1,10   #查询语句
show profiles;
show profile cpu,block io query 273;
set profiling =0；  #关闭

PowerDesigner 数据库设计软件
存储引擎:
myIsam 和innodb,mamory
优缺点:
事务安全:myIsam不支持事务,innodb支持事务
查询和添加速度:myisam数度快,innodb速读慢
支持全文索引:myisam支持,innodb不支持
锁机制:myisam表锁,innodb行锁
外键:mysiam不支持外键约束,innodb支持外键(通常不设置外键,通常程序中保证数据一直)

索引(index) :是帮助数据库高效获取数据的数据结构,索引是为了对加速对表中的数据进行查询
主键分类:
普通索引(normal)： 允许重复的值出现，可以在任何字段上面添加
唯一索引(unique):l 除了不能有重复的记录外，其它和普通索引一样，可以在值是唯一的字段添加（用户名、手机号码、身份证、email，QQ），可以为null,并且可以有多个null
主键索引：是随着设定主键而创建的，也就是把某个列设为主键的时候，数据库就会給改列创建索引。这就是主键索引.唯一且没有null值,
全文索引：l 用来对表中的文本域(char，varchar，text)进行索引， 全文索引针对MyISAM有用InnoDB不支持全文索引，所以一般不用,默认只支持英文. -使用ES,Lucene代替就ok
索引的方式:BTree和hash
hash方式 使用的是hash算法,
hash结构的索引不支持排序,innodb和myisam都不支持hash
hash只能进行等值查询(= ,in),不能进行范围查询(<,>,between)
列的重复值过多会出现大量的hash冲突
innodb不支持hash方式,memory存储引擎可以做到

innodb索引物理结构:
MySQL如果使用InnoDB存储引擎，数据库文件类型就包括.frm、ibdata1，默认存储到“C:\ProgramData\MySQL\MySQL Server 5.5\data”目录下。InnoDB使用了b+tree作为索引结构，在InnoDB中索引和数据在同一个文件ibdata1，所以数据会存储在索引结构中。
innodb索引 B+ Tree
b+tree是一个多路树,每次查询都到叶子节点,查询效率稳定;
非叶子节点不存完整数据,而是存键值key,和树节点的引用,可以存储更多的key,充分利用了每个节点的存储空间16kb,减少了节点数,树高变矮,IO次数变少,性能更高;
叶子节点存储完整的数据,叶子节点是有序的,每个叶子节点指向了下一节点的应用,形成一个双向链表,适合范围查询.

innodb主键索引和辅助索引:
回表:辅助索引根据查到的数据的主键索引返回主键索引获取完整的数据,这叫回表;
覆盖索引:如果select name查询到的列正好包含在辅助索引的节点的键值中,它就不需要在扫描主键索引了,这个叫覆盖索引.所以尽量不要select *操作.避免回标.
辅助索引中的叶子节点存放的是数据的值以及对应的主键索引的值

为什么不使用其他数据结构，比如数组？链表？AVL-Tree，B-Tree 而要使用B+Tree：

1、 有序数组：在查询的时候性能很高，可以二分查找，但是修改删除数据的时候会移动数组下标，性能比较差，这种结构只适合静态数据
2、 链表：链表的删除，修改性能高，打算查询性能极差
3、综合上面的优势，有没有支持二分查找的链表结构呢？有：就是二叉查找树。
4、二叉查找树：它的查询性能和树高有关系，二叉树树高越高，查询越慢，而且在子树极端不平衡的情况下二叉树可能会变成链表，性能比较差。
5、 AVL-Tree 平衡二叉树结构：平衡二叉查找树，如果每个节点放一个键值，数据地址，子树的引用 ，InnoDB节点默认最大存储为16KB，那AVL-Tree平衡二叉树的节点存储数据是远远达不到16K的，浪费了大量的存储空间。如果数据量大，就意味着树高非常高，查找一个KEY就需要遍历很多的节点，时间复杂度大，I/O次数非常高,所以查询是比较慢的。

MyISAM主键索引：
myisam的数据和索引是分开的，所以树的节点指向的是数据的地址。数据存储在 course.MYD文件，索引存储在course.MYI文件中，coursr.frm是表结构定义文件，所以Myiasm的索引不存储数据，而是存储数据的磁盘地址。
【注意】MyIsam的辅助索引的叶子节点没有指向主键索引的键值，而是直接指向的数据的磁盘地址
聚集索引与非聚集索引:
innodb的主键索引就是聚集索引,myisam的主键索引和辅助索引都是非聚集索引
如果表中没有主键怎么办?
1.如果没有主键,mysql会自动选择第一个不包含null的唯一索引作为主键索引.
2.如果不满足条件一,那么会选择一个隐藏的rowid作为主键索引(select rowid from 表名)

联合索引中的最左匹配原则:
最左匹配原则:第一部分的索引必须要使用,对于创建的多列索引(符合索引),不是使用的第一部分就不会使用索引
创建索引的原则:
索引是用空间换时间提高查询效率.
经常被查询的字段,经常用来排序的字段,
更新非常频繁的字段不适合建索引

使用强制索引
select xxx from 表名 force 索引名 where xxx= xxxx

sql优化小技巧
1.避免全表扫描
2.避免使用!=
3.避免or连接
4.is null可以使用索引，is not null无法使用索引
5、like以通配符%**开头索引失效
6、数据库和表的字符集统一使用utf8mb4
统一使用utf8mb4( 5.5.3 版本以上支持 ) 兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。
7、计算、函数、类型转换(自动或手动)**导致索引失效
等等。。。。
https://www.cnblogs.com/codingmode/p/15561098.html

mybatis有哪些执行器:
simpleExecutor:每一次执行update和select,就开启一个statement,用完立刻关闭.
batchExecutor:执行update(没有select,jdbc批处理不支持select),将所有的sql都添加到批处理中(addBatch(),executeBatch()),他缓存了多个statement对象,每个statement对象都是addbatch()完毕之后,等待j逐一执行executBatch()批处理.与dbc批处理相同.
ReuseExecutor:执行update或者select,以sql作为key查找statement对象,如果存在就使用,如果不存在就创建,用完之后不关闭statement对象,而是放置于map当中,供下一次使用.简而言之就是重复使用statement对象