mysql基础

一、mysql中的sql扩展
1、mysql中的数据库可以有多个，每个库管理自己的表，oracle中一个服务就一个实例，一个实例只有一个库，一个库中有多个表空间，不同表在逻辑上属于不同表空间
2.系统默认几个数据库 information_schema 存储的是元数据信息，如表信息。列信息。权限信息等；
mysql数据库这个是mysql的核心数据库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息；performance_schema主要用于收集数据库服务器的性能参数 ；Sys库是一个简单版的performance_schema
参考 https://blog.csdn.net/myFirstCN/article/details/90510483
3.limit分页查询 从0开始第一行，另外，该函数属于mysql扩展的，其他数据库不具备
4.information_schema 是虚拟数据库，其中的表都是视图，而非物理上的表；常见视图：schemata 存储所有数据库信息，show databases结果取自其中；tables提供所有表对象信息；columns提供所有列信息statistics视图提供索引信息
二、mysql中数据类型
1、decimal的精度确定方式：decimal（m，d）小数位为d位，超过d位之后四舍五入，整数部分正负数都是最多（m-d）位，符号不占位数，整数位超过位数直接整个值保存的是精度范围内最大或者最小值（负数则最小）；另外numeric类型是decimal的同名类型，选择numeric类型最后保存的还是decimal
2.数值类型保存范围

3.数值类型如果实际输入的值超出了该类型的数据范围，则会自动填充最大值；定点类型decimal和浮点类型foat或double在小数点超过定义的精度后，会四舍五入
4.unsigned表示无符号，选择后正常取值下限为0，上线扩大2倍
5.mysql中的auto_increment表示整数自增，一般要求列为主键或者有唯一索引，但是注意，增加的基础是之前该列已存在的最大值，即便该列已删除，仍会在历史最大值的基础上自增，必须使用truncate函数设置为重新计数，原理跟oracle中水位线的原理类似
6.float和double浮点数如果不写精度，则按照实际精度存储，decimal定点数不写精度，则按小数位数为0处理
7.日期时间型
timestamp显示的跟datetime一样，如果要显示数值 则在查询时select timestamp+0 from test; timestamp类型跟系统当前时区相关，如果保存时的时区跟读出数据时系统的时区不同，则会自动显示时区计算之后的时间

8.所有给定整数不符合所选时间类型要求的都会被置为0
9.字符串类型
（1）char和varchar区别：
i.char定长，创建字段时给多少，就多少，存储时不够的字符会以空格弥补；varchar变长，实际长度为所存字符长度+1（保存长度）；
ii.char检索时会将右侧所有空格去掉，这样如果存储时本身就是以空格结尾的，会得不得空格字符；varchar则保留空格检索
iii.定长字段优点保存非常迅速，但是占用空间大，适用于对定长数据列（身份证）；变长字段存储空间小，但是检索时处理较慢，同时恢复时容易出问题
iv.不同存储引擎 对char和verchar使用原则

（2）所有字符类型长度

10.text和blob使用原则
(1)text是保存大量字符数据时用的类型，blob则保存的是二进制字节
（2）text和blob字段的删除操作，会造成空洞，即磁盘碎片，执行了delete之后，磁盘空间并没有被回收，类似于oracle中delete没有使得水位线下降一样，其实类似于delete、rollback这样的命令都会存在空洞问题，需要使用命令回收磁盘空间

OPTIMIZE table testtable

（3）在不必要的时候避免检索text或者blob等大字段 ，会严重影响查询效率以及网络传输效率，可以将大字段单独放进一个表，通过主外键关联
三、mysql中特有运算符
（1）<=> null安全的等号，因为等号和不等号都不能用于null的比较，无法返回正确结果，但是该符合除了具备等号特点外，即使是null也可以正确比较，即也能具备is null的功能
四、mysql中常用的函数总结
（1）字符串函数

（2）数值函数省略 与其他数据库一样
（3）日期和时间函数
（4）mysql中date_format中日期时间格式显示的符号
使用

select DATE_FORMAT(now(),'%Y/%m/%d %h:%i:%s')  from testtable 

（5）流程函数

-- 流程函数
-- if（value，v1，v2）
select if(vote_num>1000,'high','low') from test
-- ifnull(value,v) 将字段中的null替换掉，不是null的则正常显示
select ifnull(datetime,now()) from testtable	
-- case when then
select vote_num,case  when vote_num<=300 then 'low' when vote_num<=1000 then 'middle' 
when vote_num<=1500 then 'high' else 'best' end from test

五、存储引擎的选择
1、存储引擎是mysql中特有，创建新表时如果不指定，则会选择默认存储引擎，5.5version之后默认引擎为InnoDB
2.InnoDB和BDB是事务安全表类型，其他引擎都不是
3.各种存储引擎的区别比较

4.myIsam和InnoDB的区别 https://www.zhihu.com/question/20596402/answer/211492971
（1）myIsam只支持表级锁，而InnoDB支持行级锁，myIsam引擎的表插入操作不会被排斥，即加上表锁之后仍可插入
（2）InnoDB支持事务和行级锁，支持主外键
（3）数据库文件差异
i.一张myISam表属于堆表，生成三个文件，.frm后缀存储表的定义；.MYD存储表数据;MYI存储表索引；
ii.InnoDBinnodb有两种存储方式，共享表空间存储和多表空间存储,两种存储方式的表结构和myisam一样，以表名开头，扩展名是.frm,如果使用共享表空间，那么所有表的数据文件和索引文件都保存在一个表空间里，一个表空间可以有多个文件，通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字，一般共享表空间的名字叫ibdata1-n;如果使用多表空间，那么每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开头，以.ibd为扩展名。
iii.自增不同；自增列必须都是索引，但是组合索引下Myisam允许自增列不是组合索引最左列；而InnoDB则要求必须最左列
iv.myIsam存储表总数，InnoDB不存储，count(*) myISAM不耗时间，InnoDB耗时间，但是where条件加上之后都一样了
v.InnoDB支持主外键关系，而myIsam不支持
vi.myIsam存储格式中可以选择压缩文件，占用空间较小，InnoDB则需要更多空间；
vii.myIsam数据以文件形式存储，备份恢复等比较容易，Innodb备份相对复杂
5.myslq默认事务提交 **
6.memory引擎
内存表，表结构对应磁盘文件.frm，数据是放在内存中，因此加载非常快，可以做缓存，或者当临时表，服务重启时，表结构还在，但数据会丢失
六、mysql字符集
1.mysql字符集支持服务器、数据库、表以及字段级别设置不同的字符集
i服务器级别修改字符集

ii.数据库的字符集和校验规则，通过语句，或者直接右键相应数据库

alter database `zsx_sql` character set gbk collate gbk_chinese_ci

2.字符集定义mysql存储字符的方式，校验规则定义比较字符的方式，如比较时是否忽略大小写等
七、索引问题
1索引分类：
（1）Btree索引
（2）hash索引只有memeory引擎支持，由于是key -value形式，只有等值查询起作用，范围查询不起作用
（3）Rtree 为myisam一个特殊索引类型，主要用于地理空间数据类型
（4）full-text全文索引，前缀索引是说对字段前面一部分进行索引

3.InnoDB索引原理
（1）.MyIsam中数据和索引是分离的，索引按照btree搜索到叶子节点之后拿到的是地址，去地址读取数据；而InnoDB数据本身就是按照b+tree方式存储的，键就是主键，按照btree找到指定主键之后，后面就跟的对应行数据
（2）.如果InnoDB表中没有设置主键或者合适的唯一索引，InnoDB内部会以一个包含行ID值的合成列生成一个隐藏的聚簇索引。表中的行是按照InnoDB分配的ID排序的。行ID是一个6字节的字段，随着一个新行的插入单调增加。因此，行ID顺序物理上是插入顺序。
（3）.其他字段上的索引叶子节点直接存储的是主键的值，而myIsam索引叶子结点存储的是数据物理地址

b+树
注意：B+树本身会被加载到mysql内存中（磁盘是不存在指针概念的）然后指针指向每一个inode信息，操作系统通过解析inode读到inode对应的磁盘块，然后去进行磁盘IO
索引节点就是数据页，默认16kb，也是mysql默认最小的IO单位

https://www.cnblogs.com/ZhuChangwu/p/14041410.html
组合索引存储原理
本质上也是一颗B+树，只是键值不是一列，而是多列，按照第一列排序，第一列相同才按照第二列排序

（4）.InnoDB聚合索引使得按主键查找十分高效，但是按照辅助索引查找，得需要走两次索引，第一次获得主键，第二次通过主键查找行
（5）.主键值不能过长，否则辅助索引文件会变的比较大，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。另外，主键最好自增，如果不单调的话，每次插入新行，为保持btree特性，都得进行分裂重组，低效
4.联合索引的原理
实际上是按照最左原则，首先对最左边数据列进行排序，然后如果最左列数据一样， 再对第二列排序，依次类推 参考 https://blog.csdn.net/J_mani/article/details/79498148

5.全文索引
（1）创建和使用

#创建语句
ALTER TABLE testengine ADD FULLTEXT INDEX idx_full_text_all(namev,col1)  -- 中文索引加with parser ngram;
#使用，match后必须是加了全文索引的字段
select * from testengine where match(namev,col1) against ('万岁' in  boolean mode)

（2）两种查询模式
i.IN NATURAL language MODE 自然语言模式，缺省值
特征：，该模式如果返回行数超过50%则认为没有匹配，布尔模式不限制；
对屏蔽字符列表中的字符串进行过滤
in boolean mode 布尔模式
匹配花样更多，如规定某个词必须出现，某个词必须不出现等
+表示必须存在（and），-表示必须不存在（not），没有表示可有可无（or）

SELECT * FROM testengine where match(namev) against('+wefw -this' in boolean mode)

（3）一些注意事项
i.屏蔽表（可设置）中的词会被忽略因为太常用（eg：this，hello），即搜不到结果
英文全文索引 分词符号为两个’,句号，空格，逗号等
ii.自然语言模式下无论中英文全文索引返回行数超过50%，都匹配不出结果
iii相关参数sql

#查看英文最小查询字符
SHOW VARIABLES LIKE 'ft_min_word_len'

#中文默认分词大小为2字符  ，分词大小为2时，ngram解析器将字符串"缠绵悱恻"解析为： 缠绵,绵悱,悱恻。可以使用ngram_token_size 来配置分词大小，最小值为１，最大值为10
#查询正好等于ngram_token_size的词，速度会更快，但是查询比它更长的词或短语，则会变慢。
配置ngram_token_size需要在my.cnf中，修改这种分词或者最小查询字符等，需要重新建立索引
show variables like '%ngram%'

八、存储过程
1、简单的存储过程

--告诉解释器以下代码从美元开始到美元结束再执行
DELIMITER $$

DROP PROCEDURE IF EXISTS `zsx_sql`.`protest`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `protest`()
BEGIN
-- 定义变量
declare i int default 54; 
declare j int default 0;
while(i<=100) do
insert into zsx_sql.testtable 
	(id, namec, gender, passwordv, money, datetime, year, time, date, timestamp)
	values
	(i, concat('zsx',i), j, concat('pwd',i),  23.33, now(), year(CURDATE()), CURTIME(), CURDATE(), UNIX_TIMESTAMP(curdate()));
set i =i+1;
if(i%2=0) then
set j =1;
else
set j = 0;
end if;
end while;
    END$$
--告诉解释器继续遇到分号执行
DELIMITER ;

2、delimiter定界符的使用
参考 https://www.cnblogs.com/kenshinobiy/p/9583921.html
默认情况下 mysql遇到逗号就要执行的，在存储过程中这样肯定有问题，因此需要告诉mysql解释器 存储过程的代码要从什么地方开始执行，从哪里结束，delimiter $$(//)就是告诉解释器从美元符开始，从美元符结束之后执行，最终执行完又要告诉解释器继续以;为执行分隔；定界符经测试基本可以为任意不冲突符号，比如两逗号等
八、sql_mode
1.运行在不同的sql mode下定义了 mysql支持的不同的语法和数据校验，不同的sql mode可以完成不同的数据校验，同时 在不同数据库之间迁移时可以设置一些模式 ，更好的完成迁移
2.常见的sql_mode

九、mysql分区
1、分区表各分区必须使用统一存储引擎
2.mysql中分区包括 ：range分区，list分区,hash分区和key分区；range范围分区与oracle中相同，list则是基于枚举值，比如：任务类型有5个，则分区的话可以按照任务类型分 该分区即为list，range分区则是连续的值分区
3.mysql中如果表中有主键或者唯一键，并且要分区，那主键或者唯一键字段必须包括分区字段，除非没有主键和唯一键；包括的意思是说每个唯一键中都必须包括分区字段；而oracle中是没有这种要求，mysql中这样的要求对于日期范围分区来说，日期必须做处理，比如保存日期的毫秒数等
4.Range范围分区
（1）分区采用小于号，表中的分区字段值不能大于等于分区的最大值，否则无法插入或更新

（2）range分区特别适合两种场景
i.删除某一阶段历史数据，直接删除分区就行
alter table testpartition drop partition p20190414
ii.where 条件上加上了分区字段范围条件，则直接扫描对应分区，很大程度减少数据块的扫描
5.list分区
（1）分区如果是字符串，则values in（zfc）的字符串是区分大小写的
6.其他分区
Hash分区
hash分区其实是字段根据一定的hash算法得到分区值，存入相应的分区，分为两种类型，常规分区用的是取模算法，另一种是线性hash分区，使用的是一个线性的2的幂，取模算法 如分4个区，mod(id,4)=2这样 这条数据就在第2个分区里
key分区类似hash分区
子分区即复合分区，在已经range或者list分区的基础上再进行二次分区，再分区的字段可以与第一次分区的字段不相同，另外，二次分区只能是hash分区或者key分区
7.mysql分区处理null的方式
（1）range范围分区会将null值作为最小值
（2）list枚举分区如果分区键上有null值，则要求分区是，null必须出现在枚举列表里
（3）hash/key分区null会被当成0处理
十、sql优化
1、show status了解不同sql的执行频率

（1）查看语句的执行频率，通常只关注CRUD,status前面可session 表示本次对话的统计，
global表示自上次服务重启的统计结果
（2）以下结果时对所有存储引擎结果的累加,返回的是操作次数，批量操作如批量insert等也累加一次
show  status like 'Com_select'; 
show  status like 'Com_delete'; 
show  status like 'Com_insert'; 
show  status like 'Com_update'; 
（3）以下结果返回针对innoDB引擎返回的行数，更新操作回滚和提交都会计数
show status like 'InnoDB_rows_%'
（4）查看提交和回滚的次数，回滚次数太多的话，可能应用设计有问题
show status like'Com_commit'
show status like 'Com_rollback' 
（5）慢查询的次数
show status like'Slow_queries'

2.定位执行效率低的sql语句
（1）慢查询日志

可查看相关语句variables中的变量设置统一使用 set global  变量名=值
查看慢查询日志是否启用
show  variables like 'slow_query_log'
查看慢查询日志生成时间
show  variables like 'long_query_time'
设置慢查询记录的时间
set long_query_time=5
查看慢查询日志文件
show  variables like 'slow_query_log_file'
将慢查询日志存到表中
set global log_output='TABLE,FILE'
show  variables like 'log_output'

慢查询记录表
select * from mysql.slow_log
默认情况下 未使用上索引导致的慢查询以及管理语句（eg：optimize，DDL等）不会记录到日志，可设置
set global log_queries_not_using_indexes=on
show  variables like 'log_queries_not_using_indexes'

（2）实时查看运行线程 show processlist
3.EXPLAIN分析sql结果说明
mysql的Expain执行计划查看
（1）参考.https://mp.weixin.qq.com/s/Zk23yayxJoOzugQFsbaCfA
（2）id列
数值越大，越先执行，同样的id执行顺序由优化器决定
即有相同id，也有不同id时，相同id为一组，不同组的id值越大，越优先执行
（3）select_type
表示的是查询类型，simple：最简单的语句，不包括任务子查询连接查询等复杂语句
primary：连接查询或者子查询中的外层查询
subquery：子查询
DEPENDENT SUBQUERY ：在select 列表中的子查询
unoin等
（4）table列
表名可能是真实的表名也可能是别名
（5）partitions 分区情况
（6）type列
查询使用了何种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
const：等值查询命中主键或者唯一键，效率非常高
eq_ref，等值查询，关联查询 使用主键或者唯一索引
ref 等值查询时使用非唯一性索引
ref_or_null 与ref区别在于，非唯一性索引中有null值，当对null搜索时type为此
eg： pid索引为普通索引
explain select * from city where pid =1 or pid is null
index_merge 同时使用到了两个索引，进行了索引合并优化
eg：pid和cid都有普通索引
explain select * from city where pid =1 and cid = 3
range 索引范围检索
index 索引全扫，即查询是索引覆盖查询，数据全在索引上
all 全表扫描
（7）key_len索引长度，所选择的索引的长度是多少字节，就是索引字段类型长度越短约好
一般来说 key_len等于索引列类型的默认长度，如int为4，如果是允许为null则len还得加1
参考：https://www.cnblogs.com/ajianbeyourself/p/6676480.html
（8）ref ref：常见的有：const，func，null，字段名
等值查询显示const，关联查询显示关联字段 ， 函数表达式或者隐式转换在条件列上时可能展示func
（9）rows即优化器需要扫描的行数，row越小越好
（10）filtered列表示由表条件过滤的表行的估计百分比。最大值为100，这意味着没有对行进行筛选。值从100减少表示过滤量增加。rows显示检查的估计行数，rows×filtered显示与下表联接的行数。例如，如果行数为1000，而筛选的行数为50.00（50%），则要与下表联接的行数为1000×50%=500。
（11） extra表示不适合再其他列展示的额外的信息

Using index 表示查询的字段都是索引，不用先查出主键再查出数据
eg:select pid from city  ；pid是索引列
Using where 表示没有使用索引，而是通过全表扫再where条件过滤的
using temporary  因为创建了临时表来存储结果，通常是group by和order by导致的
Using filesort order by后面的字段不是索引或者是索引字段，但是因为查询的列不仅仅是索引字段时会显示，如果查询列只有索引字段则不会出现filesort，
order by的优化见官网：
https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html
eg: explain  select  cid  from city  order  by cid
Using join buffer : 表连接时，连接条件不是索引字段，则需要连接缓冲区存储中间结果，就会出现这个
Impossible where  当where条件恒不成立，出现此提示
eg：explain  select  cid  from city   where 1=null

ps：mysql 官方文档：
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_extra

十一、mysql优化特例
1、匹配列前缀，对列前几个字符做索引，可以使得like操作左前完全匹配走索引
create index ind_title on film_text (title(10))
2.mysql中索引是存储null值的，且放在btree的最左边，即当做一列的最小值处理，索引 is null ，is not null是完全走索引的，这一点跟oracle不同，oracle的索引中不会存放null值
3.myslq中的不等值 !=操作也是可以走索引的，一般都是范围扫描
4.mysql中order by有两种排序方式
（1）通过有序索引直接返回数据，这种的extra字段值显示using index，可以在查全表时试着加上 order by 主键
（2）对返回的数据进行排序，extra中显示Filesort，这种排序可能是在内存中，也可能是在磁盘文件中
5.mysql中有索引但是索引无法被用到的情况
（1）%开头的like，类型转化（数字写成字符的话，问题不大，优化器仍可以走索引），最左原则没有满足，返回数量比例较大，即优化器认为全表扫描快（同oracle）
（2）or前面有索引，后面没索引的情况，由于后面一定会全表扫描，因此总体不会走索引
6.group by的优化
（1）mysql中group by选择列中可以是任意列，对于非分组列和聚合函数以外的列，统统显示第一行值，oracle中则不同
（2）如果group by之后走的全表扫描效率太低，则可以考虑将group by后字段加索引，另外，如果确实还是走全表，那么一般由于分组默认加上排序，extra中会出现using filtersort字段值，这个过程很耗费资源。可以通过 order by null禁止其自动排序

7.查看优化器优化之后真正执行的sql，执行完explain之后 执行show warnings
8.分页显示的优化方案

id主键递增
select * from table t  where  id>=（ select id from table where create_time<xxx  limit 1000000,1） and  create_time <xxx  limit 100

方案2;
id主键连续等步长递增，即数据没有被删除过
使用分页计算出id范围 直接使用主键查询
select * from table t  where  id < xx and id>oo and  create_time <xxx 

9.中间表经常在统计查询中使用，创建同意结构的临时表，将需要统计的数据insert进去临时表，在临时表上进行统计
十二、其他
1、数据库名、表名大小写敏感问题
mysql中数据是保存成文件的，因此敏感与否 取决于操作系统本身，window中默认是不敏感的，但linux系统是敏感的，因此最好统一使用小写
2、列、索引、存储过程和触发器等默认任何系统都不敏感。
3、表别名在linux中敏感，但是window中不敏感，因此最好不要使用同一字母的别名
4.设置如何保存和使用表名和数据库名的参数 lower_case_tables_name

十三、锁
1、myIsam的表锁在select之前会加读锁，在update、insert和delete之前会自动加写锁
2.myisam中的锁有个concurrent_insert可控制在读锁情况下是否能够并发在尾部插入，0表示不允许，1表示如果myIsam表没有空洞，则可以插入，这也是默认配置；2表示可以无条件允许并发尾部插入
3.myisam默认的锁调度是写进程先获得写锁，之后读进程才能获得读锁，即便是写进程先进入请求队列，造成的问题是myIsam不适合大量更新和删除的操作，因为会长时间堵塞查询操作；这种优先级可以通过参数改变
4.对于读写冲突的解决还可以通过将查询的额内容做成中间表来避免对同一张表的锁竞争，或者安排复杂查询在数据库空闲时间段进行
5InnoDB的锁问题，事务的一致性是说，在同一事务中任何时刻同一条查询结果应该是一样，脏读、不可重复读等就是事务不一致的表现，因为在同一个事务中相同查询结果不一样；不可重复读是说查出的数据量不变，但是数据内容变了，幻读则是同一个查询结果数据量发生了变化
6.ISO定义的事务隔离级别如下

7.InnoDB行锁模式及加锁方法
x表示排他锁，ix表示意向排他锁，s共享锁，is意向共享锁；意向锁都是InnoDB自动加的，而对于DML语句Innodb会自动加X（DML），select语句不加锁，Record Locks
A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

排他锁加锁：select * from table where …for update
共享锁加锁：select * from table where lock in share mode
8.InnoDB实现行锁的方式是通过给索引项上加锁来实现的，如果没有索引，那么就会通过隐藏的聚合索引对记录进行加锁，也就是说如果记录不是通过索引项检索出来的，那么innodb实际上会加表锁；该特性在实际中应该注意，否则会出现大量锁冲突，以下为常见情况：
（1）不通过索引检索时，innodb直接上表锁，这种情况下，如果事务未提交，整个表将只读；
（2）如果where条件中的索引项是一样的，由于加上了其他条件，导致会话中更新的行不同的时候，也会发生锁冲突，即只要条件中索引项值一致，就会发生锁冲突，不管是否是同一行数据，因为InnoDB锁是通过加在索引项上实现的
（3）不同索引如果定位到同一行的话也会发生锁冲突
（4）条件查询中即便有索引，但是如果优化器如果认为扫描全表效率更高，或者其他原因导致索引没用上，那么仍是锁全表
9.commit;rollback 不能释放掉手动加锁Lock tables name;
10.SLEEP(duration) 单位是秒； 表示当前会话休眠多少秒，使用：

old_account中有3条记录，实际会休眠3秒
select SLEEP(1) from old_account;

11.死锁和锁等待是不同的事件
死锁是因为对数据加锁的顺序不一样导致的相互等待锁；
锁等待则是上一个事务对行锁没释放，第二个事务又更新同一个行时要进行等待锁
示例：
死锁：

事务1：
 start TRANSACTION;
update balance_table_b set  balance =1024 where id = 111;
select SLEEP(5) from  dual ;
update  balance_table_b set balance = 1003 where id = 112;
commit;
事务2：
 start TRANSACTION;
update balance_table_b set  balance =1024 where id = 112;
select SLEEP(5) from  dual ;
update  balance_table_b set balance = 1003 where id = 111;
commit;
执行结果： 出现了相互等待
[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

锁等待：

事务1：
 start TRANSACTION;
update balance_table_b set  balance =1024 where id = 111;
事务2：
 start TRANSACTION;
update balance_table_b set  balance =1024 where id = 111;
执行结果：等待直到超时报错：
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

ps：INNODB锁补充
（1）锁分类

（2）锁退化，行锁是通过给索引上的索引项加锁，如果没有索引或者未将索引项作为更新条件，或者索引没有生效，则退化成表锁

eg：
session1：
update mcc_user set   realName = 'liu34fei' where id=237  # userName='刘亦菲'
session2：
 update mcc_user set   realName = 'liufei' where userName='xiaoxi'

id加索引，两个会话并非同一个行数据，但是session2会阻塞 

（3）自增锁 针对自增列自增的特殊的表锁，innodb_autoic_lock_mode 默认值1 ，表示事务未提交则id永远丢失
（4）间隙锁，锁住一个索引前后间隙以防止插入，如 索引列2,4,8,当id=4 for update时，会在（2,4）（4,8）之间加间隙锁gap lock。防止在该区间插入；注意不同事务之间可同时持有同一个间隙锁
间隙锁，锁住一个间隙以防止插入，主要是为了防止幻读（ 防止间隙内有新数据被插入 防止已存在的数据，更新成间隙内的数据，变成间隙内数据时，就相当于会移动到间隙内，
防止幻读例子
eg：select * from locktable where id=1 for update ,连续查两次，如果没有间隙锁，那另一个session就可以再插入一个id=1的记录，在当前session中两次查询数据量就不一致，加上间隙锁就可以避免另一个session插入
注意：
，那记录（id=6，number=5）与（id=8，number=5）之间也有间隙
（id=6，number=5）与（id=8，number=5）之间可以插入记录（id=7，number=5），因此（id=6,number=5）与（id=8,number=5）之间有间隙的
案例详见：
https://www.cnblogs.com/crazylqy/p/7821481.html
eg：

id为普通index
T1:
set autocommit = 0;
update  mcc_user set realName='test1r23' where id=8 # 获得的是（5，8）gay key+ 8的行锁
T2:  #阻塞，插入时持有意向插入锁，等待5,8之间的gap key  
INSERT INTO `mcc_user`(`id`,`realName`,`userName`,`password`)VALUES(6,'zsx','zsx','zsx');

又如：  表数据如上图2
id主键 number 普通index
t1：

set  autocommit =0 ;
update news set number=44 where number=4;
t2：
set autocommit=0;
rollback
插入操作会持有意向插入锁
insert into news value(2,3);#（阻塞）
insert into news value(2,2);#（阻塞）
insert into news value(4,4);#（阻塞）
insert into news value(4,6);#（阻塞）
insert into news value(8,2);#（阻塞）
insert into news value(10,7);#（执行成功）
insert into news value(4,7);#（执行成功）

再如：更新一个不存在的行，会将该行相邻的左右间隔锁定
t1:
update news set number=44 where number=5;
t2：
insert into news value(4,4);#（阻塞）
insert into news value(4,6);#（阻塞）

注意：
i.如果索引列是唯一索引，且条件为范围查询，而非单一行，那么只会锁住这条记录(只加行锁)，而不会锁住间隙。
ii.对于联合索引且是唯一索引，如果 where 条件只包括联合索引的一部分，那么依然会加间隙锁
多个事务可以同时持有间隙锁
间隙锁是性能和并发性之间权衡
(6)next-key 锁是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合。next-key 锁是一个索引记录锁加上一个在索引记录之前的间隙上的间隙锁
InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读

（7）INNODB中insert操作并不是表锁，但是要注意间隙锁的影响
（8）意向插入锁
由INSERT行插入之前的操作设置的间隙锁 。此锁表示插入意图，如果插入同一索引间隙的多个事务未插入间隙内的相同位置，则它们无需相互等待。假设存在值为 4 和 7 的索引记录。 分别尝试插入值 5 和 6 的单独事务，在获得插入行的排他锁之前，每个事务都使用插入意向锁锁定 4 和 7 之间的间隙，但不要相互阻塞，因为行是不冲突的

(9)锁模式兼容矩阵：
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks

eg： 间隙锁和意向插入锁相互等待导致死锁的案例
不存在的记录也会占用间隙锁

T1:
set autocommit = 0;
更新不存在的记录仍然会获得间隙锁
update  mcc_user set realName='test1r23' where id=7 # 获得的是（5，8）gay key+ 8的行锁
插入7，持有了意向插入锁，会等待t2持有的间隙锁
INSERT INTO `mcc_user`(`id`,`realName`,`userName`,`password`)VALUES(6,'zsx','zsx','zsx');
T2:  
更新不存在的记录，共享同一个间隙锁 （5,8）
update  mcc_user set realName='test1r23' where id=6
插入6 持有了意向插入锁（由于插入的行与t1不同，因此意向 ）
INSERT INTO `mcc_user`(`id`,`realName`,`userName`,`password`)VALUES(6,'zsx','zsx','zsx');

死锁案例：https://www.jianshu.com/p/4d47261d3e6b
(9)避免死锁的措施：
调整业务逻辑 SQL 执行顺序， 避免 update/delete 长时间持有锁的 SQL 在事务前面
固定的顺序访问表和行
避免长事务
尽量按照主键唯一键去查找，减少间隙锁持有
12.mysql中null与任何值比较都是null，而不是boolean类型，要注意
13.对于空值的t填充处理用ISNULL方法和if搭配

select  if(ISNULL(balance),0,balance)  from balance_table 

十四、使用
1.linux下执行sql的shell脚本
参考 https://www.jb51.net/article/56944.htm

#!/bin/bash
if [ ! $# -eq  2 ]
then

	echo "usage :$0 username password";
else
	mysql -u$1 -p$2 -e "
	use scada_edge;
	show tables;
       CREATE TABLE edge_sql_version (
 	id int NOT NULL AUTO_INCREMENT COMMENT '主键',
	real_version INT NOT NULL COMMENT '真实版本号',
	update_time DATETIME NULL,
 	CONSTRAINT edge_sql_version_pk PRIMARY KEY (id)
	)
    ENGINE=MyISAM
    DEFAULT CHARSET=utf8
    COLLATE=utf8_general_ci
    COMMENT='边缘数据脚本版本记录表';
	show tables;
	quit"

echo "init success"
fi;

2.mysql的dump导入的 命令：

mysql.exe  --protocol=tcp --host=10.11.111.112  --user=scada -p --port=3306 --default-character-set=utf8 --comments --database=quartz <"d:\\user\\01398017\\desktop\\fsdownload\\d2.sql"

ps：结尾不需要分号，密码会密文输入，结构和数据分开导，如果是视图和存储过程，最好直接刷sql，dump会有各种问题
3.导出命令，一般可使用客户端导出

mysqldump.exe  --user=test --host=10.203.15.113 --protocol=tcp --port=3306 --default-character-set=utf8  -p  "sbm_scada_sit">sbm.sql

3⦁ mysql查询sql慢的常用命令
⦁ （1）show full processlist 查询当前正在执行的sql以及执行的状态
i.关于执行状态的官网解释：https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html
ii.状态：waiting for … lock

元数据锁或者表锁的释放：
		某张表上的事务或者会话未结束之前，即在表上的查询结束之前，如果另一个会	话尝试DDL或者写锁操作，将阻塞，直到事务结束释放元数据，同样的行为也使用于表锁

iii.creating sort index 是线程正在处理 SELECT使用内部临时表解析的,而不是在创建索引
iv.一般而言状态改变都比较快，如果某个状态持续太久，那就说明sql有问题，卡在了这个状态中

⦁ （2）慢查询文件的位置
⦁ show variables like ‘%slow_query_log%’;
⦁ （3）最大连接查询
⦁ show varibles like ‘%connection%’
⦁ （4）profile查看sql具体执行过程
⦁ show variables like ‘%profile%’ 结果是0则关闭，1则开启
⦁ 启用：set [global] profiling=1
⦁ 查询所有sql执行时间以及query_id :
⦁ show profiles
⦁

⦁ 查询具体sql的具体过程
⦁ show profile for query 1[具体query_id]，
ps:官网将 show profile将来会被替代，用下面

SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
       FROM performance_schema.events_statements_history_long

4.查分区信息以及在创建的表上加分区

查询分区存储情况
select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name='test';
可以查看表具有bai哪几个分区、分du区的方法、分区中数据的记录数等信息

ps：创建分区
已创建的表修改分区
参考 https://dev.mysql.com/doc/refman/5.7/en/partitioning-range.html
分区常见方案：
基于数字eg：

ALTER TABLE city
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

基于时间，主要逻辑也是转为基于数字

year(report_time)、 month(report_time)、 day(report_time)转化成数字，eg：
    
ALTER TABLE city 
    PARTITION BY RANGE (day(createtime)) (  
    PARTITION p20201101 VALUES LESS THAN (1),
    PARTITION p20210201 VALUES LESS THAN (5),
    PARTITION p20211101 VALUES LESS THAN (10),
    PARTITION pother VALUES LESS THAN (maxvalue)
        );
        

对于类型为timestamp只能如下处理，其他方式报错：

UNIX_TIMESTAMP(report_updated) 时间戳
ALTER TABLE city 
    PARTITION BY RANGE (UNIX_TIMESTAMP(createtime)) (  
  PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') )

range columns 是range语法的变种，只支持DATE和DATETIME列类型，不支持其他列类型

createtime为datetime类型
ALTER TABLE city 
    PARTITION BY RANGE columns (createtime) (  
  PARTITION p0 VALUES LESS THAN ( '2008-01-01 00:00:00' ),
  
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
        );

5.mysql中日期与字符串比较会将字符串转换成时间戳进行比较，同时，时间戳可表示的范围是1970-1938，不再范围内的表示为000
官网：
https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html
6. undo log 、 redo log、 binlog
binlog 即binary log 二进制的方式记录了所有的写操作，包括没有数据改变的写操作，两种用途： 主备，主将自己的binlog发送给备；数据恢复，在所有备分数据恢复之后，binlog中在backup之后的动作将从其中再次执行
redo log 刷入过程

三种刷写方式参数可选：innodb_flush_log_at_trx_commit

73. 分布式锁的三种实现方式
（1）redis的set方法，setnx的话还要设置过期时间，不能原子执行,在redis基础中记录
（2）基于数据库的锁

	
 #（1） lock, 先调用重入（3），不存在时，则insert不存在没有过期的key，则insert，如果有过期的（即线程没有删除自己的锁），则key会重复，此时直接更新过期时间即可 
  INSERT INTO lock_table(`key`, uuid_thread, expire_time,times) 
  SELECT 'orderNo', 'uuidthread', now()+30, 1  FROM DUAL   //默认插入时30s过期
  WHERE NOT EXISTS(SELECT id FROM lock_table WHERE `key` = 'orderNo'  and expire_time>current_timestamp()) 

 
 #（2）续期 则异步，每1/3 leaseTime执行一次，防止业务执行时间过长，而提前释放， new Thread(){ update lock_table set expire_time=now()+30 where key='orderNo' and thead_id='uuidthread' and expire_time>now() }
 

 #（3）重入则需要按照threadid查一下 （这里是没问题的，不可能存在查的时候还有，查出来就被删了，毕竟一个线程内只能流程化作业，）存在则直接返回，要求threadid也始终唯一，可以uuid+threadid
	重入锁，update lock_table set times=times+1 where uuid_thread='uuidthhread' and  key="orderNo" and expire_time>current_timestamp()
 #（4）解锁则times递减，当为0时直接按照threadid删除
	     int result = update lock_table set times=times-1 where and times>0 uuid_thread='uuidthhread' and  key="orderNo" and expire_time>current_timestamp()
		if(result){
			delet from lock_table where  uuid_thread='uuidthhread' and  key="orderNo" and expire_time>current_timestamp()
	}

MVCC
https://segmentfault.com/a/1190000040763929
虚拟字段
UNDO 多版本控制链

​ read view是mvcc机制的实现一个关键组件，是mysql基于undo log多版本链条实现的，在一个事务开启的时候，默认会为当前事务生生成一个read view表，这个表在事务开启的时候所有的参数都确定，并且在事务结束的时候才会销毁。

​ 一个read view当中里面包含了下面的内容，包含4个重要的基本字段，其中最重要的是m_ids以及max_trx_id 以及 min_trx_id这三个字段。