mysql里的锁类型
1、行级锁
行级锁是mysql中锁定粒度最细的一种锁，表示只对当前操作行进行加锁
行级锁可以大大减少数据库操作的冲突，但是开销最大(因为会频繁加锁和解锁)，可能会出现死锁
分为共享锁和排他锁
共享锁：共享只读操作，如select语句
排他锁：数据修改操作，例如insert、update、delete，确保同一资源在同一时刻不会多重更新
innodb就支持行级锁
2、表级锁
表级锁是mysql中锁定粒度最大的一种锁，表示对当前操作的整张表进行加锁
实现简单，资源消耗少(不会频繁加锁和解锁)，不会出现死锁，被大部分mysql引擎支持，例如myisam和innodb
分为共享锁和排他锁
3、页面锁
加锁时间和开销处于中间，会出现死锁，并发度一般
4、全局锁
锁的是整个数据库
全局锁
全局锁是对整个数据库实例进行加锁。MySQL 提供了一个加全局读锁的方法，命令是Flush tables with read lock (FTWRL)。 当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞
-对数据的增删改操作语句
-建表、该表结构语句
-更新类事务的提交语句，即增删改
使用场景：全库逻辑备份时使用
在全库逻辑备份期间，假设不加全局锁的场景，看看会出现什么意外的情况。
如果在全库逻辑备份期间，有用户购买了一件商品，一般购买商品的业务逻辑是会涉及到多张数据库表的更细，比如在用户表更新该用户的余额，然后在商品表更新被购买的商品的库存。
那么，有可能出现这样的顺序：
先备份了用户表的数据；
然后有用户发起了购买商品的操作；
接着再备份商品表的数据。
也就是在备份用户表和商品表之间，有用户购买了商品。
这种情况下，备份的结果是用户表中该用户的余额并没有扣除，反而商品表中该商品的库存被减少了，如果后面用这个备份文件恢复数据库数据的话，用户钱没少，而库存少了，等于用户白嫖了一件商品。
该使用场景可能造成一些问题
整个数据库都处于只读状态
-如果在主库上进行备份，那么在备份期间都不能执行更新，业务基本上停止
-如果在从库上进行备份，那么在备份期间不能执行主库同步过来的binlog日志，导致主从同步延迟
innodb什么时候用表锁，什么时候用行级锁
只有通过索引条件(主键索引、唯一索引、普通索引)检索数据，innodb才使用行级锁，否则将使用表锁
但即便在条件中使用了索引，在检索数据时是否使用索引来检索数据是由mysql通过不同的执行计划来决定的
如果mysql觉得全表扫描效率高，例如一些很小的表，那么将不会使用索引
这种情况下使用的是表锁，而不是行级锁
因此在分析锁冲突时，别忘了检查mysql的执行计划依次来检查是否使用了索引
myisam是否会产生死锁
myisam是表锁，意味着一个session在写的时候，另一个session必须等待。要么一次性获得所有锁，要么等待
因此不存在资源抢占问题，不会出现死锁
innodb是否会产生死锁
在innodb中，锁是逐步获得的，因此可能会造成死锁
在mysql中，行级锁并不是锁记录，它锁的是索引，索引分为主索引和非主索引两种
当两个事务同时执行，一个先锁住了主键索引A，然后在等待另一个非主键索引B
而另一个事务锁住非主键索引B，而在等待主键索引A，那么将产生死锁
发生死锁后，innodb一般都可以检测到
让一个事务释放锁回退，另一个事务锁完成事务
mysql如何避免死锁
1）注意程序的逻辑顺序，尽量以相同的顺序访问表
Transaction 1: 更新表A -> 更新表B
Transaction 2: 更新表B -> 更新表A
这样子可能会造成死锁
2）把大事务拆成小事务，大事务更可能发生死锁
3）同一事务尽量一次性获取所有资源，减少死锁的概率
4）尽快提交事务，减少锁的持有时间
mysql表类型
1）myisam
2）innodb
3）isam、
4）BerkeleyDB
5）heap
6）merge
myisam和innodb的区别
1、myisam支持表级锁，innodb支持表级锁和行级锁
2、myisam不支持事务，innodb支持事务
3、myisam不会发生死锁，innodb会发生死锁
4、myisam采用非聚集索引，细分为主索引还是辅助索引，它们的索引文件都仅仅保存数据记录的地址
叶子节点data域是没有完整的数据记录的
inndb采用聚集索引，细分为主索引和辅助索引，主键索引的叶子节点的data域保存了完整的数据记录
但是辅助索引的叶子节点却没有完整的数据记录(行数据)
辅助索引data域存储相应记录主键的值而不是地址
所以辅助索引找数据会有一次回表的查询，首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录
5、myisam存储总行数，innodb不存储总行数
事务是什么
事务是一个拥有多个数据库操作(有序)的单元，该单元里如果有一个操作失败，那么整个单元都会回滚到操作前的状态
若想使用事务那么需要通过ACID测试，即原子性、一致性、隔离性、持久性
原子性：要么执行，要么不执行，不存在中间状态。如果事务中的一个sql执行失败，那么已执行的语句也必须回滚，即数据库退回到事务前的状态
实现原理
回滚日志(undo log)：
InnoDB实现回滚靠的是undo log。当事务对数据库进行修改时，InnoDB会生成对应的undo log。如果事务执行失败或调用了rollback，导致事务需要回滚，便可利用undo log中的信息将数据回滚到修改前
-对于每个insert，回滚时会执行delete
-对于每个 delete，回滚时会执行insert
-对于每个 update，回滚时会执行一个相反的 update，把数据改回去
隔离性：隔离性是指事务内部的操作与其它事务是隔离的，并发执行的各个事务之间不能互相干扰，注意是事务内部的操作，因此脏读还是有可能出现的，一个是事务内部操作一个是事务操作结果
持久性：事务正确提交后，其结果会永久保存在数据库中
实现原理
① redo log
当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘（刷脏页）。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。
一致性：事务的执行会使得数据库从一种正确状态变成另一种正确状态，存储的数据依然满足列的类型，例如行的主键依然存在且唯一，某一列存储的值的类型及长度依然满足表结构设置
实现原理
一致性是事务追求的最终目标。前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性。此外，除了数据库层面的保障，一致性的实现也需要应用层面进行保障。实现一致性的措施包括：
① 保证原子性、持久性和隔离性。如果这些特性无法保证，事务的一致性也无法保证
② 数据库本身提供保障。如不允许向整型列插入字符串值、字符串长度不能超过列的限制等
③ 应用层面进行保障。如转账操作只扣除转账者余额，而未增加接收者余额
举个例子：
你去斗鱼送礼物，扣了你的鱼丸给主播送了礼物，这俩操作必须是绑定在一起的，不然单个任何一个完成了另外一个没完成都会出问题，这里就是一个事务，花钱买礼物-发到库存
同样送礼物，从库存拿走，再送给主播，也必须是一致，需要一起完成的
什么时候需要事务
如果有一个需求需要两个操作保持同步的话，那么就可以使用事务
例如：转账、买礼物
事务有什么好处
当开启事务时，如果能正确执行，那么事务里的所有sql语句都能正确执行
如果中间哪个操作出现问题，那么多条sql语句一起回滚，取消所有操作
事务的特别说明
如果想保持数据的一致性，那么增删改查都需要使用事务
查的时候，假设相同的查询有两条sql且现在没有添加事务，在第一条sql查询之后，外部对该数据进行了修改，然后第二条sql再进行查询，这样会导致前后两条查询的结果不一致，因此在某种特定条件下查也需要添加事务
save和saveAndFlush
save之后只有执行事务提交才会刷新sql到mysql里，而saveAndFlush执行完成之后不需要等待事务提交就可以把sql提交到mysql里
save可能存在数据覆盖的情况，saveAndFlush可以在同步代码块里使用
数据库并发时存在的问题
1、脏读：A事务读取到B事务未提交的数据
即使事务具有隔离性，但依然可能读取到未提交的数据，因为事务的隔离性是针对事务内部的，而不是事务的操作结果
要根据事务隔离级别来加以判断，如果是读取未提交的事务隔离级别，那么是有可能造成脏读的
假设事务 A 更新了一行数据的值为 A 值，此时事务 B 去查询了一下这行数据的值，看到的值是 A 值，接着，事务 B 拿着刚才查询到的 A 值做各种业务处理。但是接着坑爹的事情发生了，事务 A 突然回滚了事务，导致它刚才功能的 A 值没了，此时那行数据的值回滚为 NULL 值。然后事务 B 紧接着此时再次查询那行数据的值，看到的居然是 NULL 值。这就是脏读。它的本质是事务 B 去查询了事务 A 修改过的数据，但是此时事务 A 还没提交，所以事务 A 随时会回滚导致事务 B 再次查询就读不到刚才事务 A 修改的数据了，这就是脏读。
其实总结一句话，因为一个事务去更新或者查询了另外一个还没提交的事务更新过的数据。因为另外一个事务还没提交，所以它随时可能会回滚，那么必然导致你更新的数据就没了，或者你之前查询到的数据就没了，这就是脏读两种场景。
2、不可重复读：前后多次读取，数据内容不一致
假设缓存页里一条数据原来的值是 A 值，此时事务 A 开启之后，第一次查询这条数据，读取到的就是 A 值。接着事务 B 更新了那行数据的值为 B 值，同时事务 B 立马提交了，然后事务 A 此时还没提交。大家注意，此时事务 A 是没提交的，它在事务执行期间第二次查询数据，此时查到的是事务 B 修改过的值，B 值，因为事务 B 已经提交了，所以事务 A 是可以读到的，紧接着事务 C 再次更新数据为 C 值，并且提交事务了，此时事务 A 在还没提交的情况下，第三次查询数据，查到的值为 C 值。如果多次查询数据，都是同样的一个 A 值，它希望这个 A 值是它重复读取的时候一直可以读到的。它希望这行数据的值是可重复读的
3、幻读
你一个事务 A，先发送一条 SQL 语句，里面有一个条件，要查询一批数据出来，如 SELECT * FROM table WHERE id > 10。然后呢，它一开始查询出来了 10 条数据。接着这个时候，别的事务 B往表里插了几条数据，而且事务 B 还提交了，此时多了几行数据。接着事务 A 此时第二次查询，再次按照之前的一模一样的条件执行，由于其他事务插入了几条数据，导致这次它查询出来了 12 条数据。于是事务 A 开始怀疑自己的眼镜了，为什么一模一样的 SQL 语句，第一次查询是 10 条数据，第二次查询是 12 条数据？难道刚才出现幻觉了？这就是「幻读」这个名词的由来
幻读就是你一个事务用一样的 SQL 多次查询，结果每次查询都会发现查到一些之前没看到过的数据。注意，幻读特指的是你查询到了之前查询没看到过的数据。此时说明你是幻读了
4、更新丢失问题
原始数据是1，AB两个事务先后都对数据进行读写操作，A读完，B读，那么A跟B读到的数据都是1，然后A对数据进行修改，把1改成2，而B也对数据进行修改，把1改成3，那么会造成A的写入被覆盖掉，这叫更新丢失
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
事务隔离级别
概念：在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别
①READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
②READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
③REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
④SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
char和varchar的区别
1）char保存的是固定长度的字符串
当字符串长度没到指定长度时，会用空格填充到指定长度
检索时尾随空格会被删除
2）varchar可以保存可变长度的字符串
主键、外键、候选键、索引的区别
主键：每一行的唯一标识，不能有重复且不允许为空，用来保证数据完整性，只能有一个
候选键：可以用于任何外键引用，可以为空，如果不为空则是主键
外键：表的外键是另一表的主键, 外键可以有重复的, 可以是空值，可以用来和其他建立联系，可以有多个
索引：该字段没有重复值，但可以有一个空值，可以提高查询排序的速度，一个表可以有多个唯一索引
如何找到表里面的所有索引
show index from tablename
like声明中的%和_是什么意思
%对应于0个或更多字符，_只是like语句中的一个字符
BLOB和TEXT的区别
BLOB跟TEXT就是一个大小写区别，在对BLOB进行排序时区分大小写，TEXT不区分大小写
MySQL_fetch_array 和 MySQL_fetch_object 的区别
MySQL_fetch_array将结果行作为数组返回
MySQL_fetch_object将结果行作为对象返回
myisam表是存储在哪里的
myisam表将以三种格式存储在磁盘上
“.frm”文件存储表定义
“.MYD”文件存储数据
“.MYI”文件存储索引
mysql如何优化distinct
distinct可以过滤重复的数据只显示一条，但很消耗性能
将distinct转换成group by并与order by语句结合使用
注意：order by出现的字段必须在group by中出现，而group by 中的字段不一定得在order by中出现
select stu_type,sum(score) from students group by id,e,stu_type order by id
mysql中limit的使用
只显示前10行：limit 0,10或limit 10
显示6-15行：limit 5,10
mysql中now函数和current_date函数的区别
now()显示年月日时分秒
current_date显示年月日
mysql的常用函数
avg、count、max、min、sum、concat(合并)、trim(去空格)、replace(字符串替换)、abs(x)(绝对值函数)
mysql里记录货币用的字段类型
使用numeric或decimal
例如：decimal(9,2)，表示存储的总位数是9位，小数位是2位，即该数的范围是：-9999999.99 到9999999.99
mysql有关权限表都有哪些
user：记录允许连接到服务器的用户账号信息，全局权限
db：库级别权限，记录各个帐号在各个数据库上的操作权限
table_priv权限表：记录数据表级的操作权限
columns_priv权限表：记录数据列级的操作权限
列的字符串类型
set、char、num、blob、text
如何运维一个即将每日增幅大的数据库
1、设计良好的表字段型和存储引擎，添加适当的索引
2、找规律进行分表，减少单表的数据量
3、对数据库进行读写分离
4、书写高效率的sql，减少select * 的使用，使用连接查询代替子查询，使用关联查询代替子查询
5、添加缓存机制
锁的优化策略
1、缩小锁范围，利用分段加锁
2、如果某个数据需要频繁加锁和释放锁，更改成一把大锁，效率更好
3、减少锁持有时间
4、多个线程尽量以相同的顺序去获取资源，避免死锁
5、读写分离
索引的底层实现原理和优化
底层原理是b+tree
如果是myisam，那么叶节点的data域存放的是数据记录的地址
无论是主索引还是辅助索引，存储的都是数据记录的地址，只不过主索引的key是唯一的，而辅助索引的key可以重复
myisam上的索引也叫做非聚集索引
如果是innodb，如果是主索引(主键作为索引)，那么data域上存放的数据；
如果是辅助索引，那么data域上存放的是主键
因此如果是通过辅助索引那么需要检索两遍，首先检索辅助索引获得对应的主键，然后再用主键到主索引中检索获得数据
什么情况下设置了索引却无法使用
1、like以%开头会导致索引失效进而转成全表扫描
2、当使用到关键字or时，前后需要一起使用索引，否则索引会失效
3、如果列类型是字符串，那么一定要在条件中将数据用引号括起来，否则会出现隐式转化转成int
这种情况下不会使用索引
4、如果mysql通过不同的执行计划判定全表扫描比使用索引快，那么将不使用索引
如何优化mysql数据库
1、选取最合适的字段属性、引擎
2、使用事务、外键、索引
3、锁定表，防止写操作时出错导致查询崩溃，进而拖慢速度
4、优化查询语句，减少select *的使用
使用连接查询代替子查询，不需要创建临时表
使用关联查询代替子查询
mysql语句的优化
1、使用索引来进行查询
2、在where语句上不要计算和使用函数
3、通过连接查询、联合查询代替子查询
4、多使用limit进行分页查询，不要一次性获取所有数据
索引、主键、唯一索引、联合索引的区别，以及它们对数据库的性能有什么影响(从读写两方面入手)
索引是一种特殊的文件，它们包含着对数据表里所有记录的引用指针
普通索引(由关键字key或者index定义的索引)，它可以加快对数据的访问速度，普通索引允许被索引的数据列包含重复的值
唯一索引，即被索引的数据列的值各不相同，该索引可以保证数据记录的唯一性(在创建索引时用关键字unique定义)
主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字primary key来创建
联合索引，当索引覆盖多个数据列时，如index(columnA,columnB)索引，那么这就是联合索引
索引的目的是什么
1）快速访问数据库表中的特定信息，提高检索速度
2）创建唯一性索引，保证数据库表中每一行数据的唯一性
索引对数据库系统的负面影响
创建索引和维护索引都需要耗费时间和空间，这两个因素都随者数据量的增加而增加
时间：创建索引和维护索引都需要时间
空间：需要物理空间存放索引
影响：索引可以极大提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在写操作时，还需要操作索引文件
创建索引有什么原则
1、对于查询频率高的字段创建索引
2、对频繁需要排序、分组和联合操作的字段创建索引
什么情况下不适合建立索引
1、数据量少的表不要使用索引，改善不大
2、频繁更新的字段不适合建立索引(有索引频繁更新会降低速度)
3、where条件中用不到的字段不适合建立索引
4、唯一性太差的字段不适合建立索引
5、需要经常参与计算的列不适合建立索引
sql注入漏洞产生的原因，如何防止
原因：程序开发过程中不注意规范书写sql和对特殊字符进行过滤，导致客户端可以通过一些post和get来提交一些sql语句进行执行
防止：
1）过滤sql语句中的一些关键词：insert,delete,update,select,*等
2）使用#来获取参数值
3）使用正则表达式过滤传入的参数
4）提高数据库表和字段的命名技巧，字段的命名要根据程序的特点，取不容易被猜到
表中的字段选择合适的数据类型？？
存储时间的字段类型
1、datetime，以YYYY-MM-DD HH:MM:SS格式存储，精确到秒，占用8个字节的存储空间，与时区无关
2、timestamp，以时间戳格式存储，范围在1970.1.1-2038.1.19号，占用4个字节，需要指定时区
3、date，存储月和日，只需要3个字节
4、time，存储时间部分的数据
5、使用int，不如timestamp
交叉连接、外连接、内连接
交叉连接：是指不使用任何条件，返回两个表的笛卡尔积，例如A表4行，B表4行，那么将返回16行数据
内连接、外连接都是在交叉连接的基础上进行的
select *from T_student cross join T_class //cross join 可以省略不写
等于
select *from T_student, T_class
内连接：即是有条件的交叉连接
select * from T_student s inner join T_class c on s.classId = c.classId;
select * from T_student s inner join T_class c on s.classId <> c.classId
外连接：包括左连接、右连接、全连接
左连接：返回左表中的所有行，右表根据条件匹配，如果匹配不到返回null
右连接：返回右表中的所有行，右表根据条件匹配，如果匹配不到返回null
全连接：返回左表和右表中的所有行，当某行在另一表中没有匹配行，则另一表中的列返回空值
select * from T_student s left join T_class c on s.classId = c.classId
select * from T_student s right join T_class c on s.classId = c.classId
select * from T_student s full join T_class c on s.classId = c.classId
全连接
mysql中的事务回滚机制
当同时修改数据中的两个表时，它们是一个事务，当第一个表修改完成，而第二个表修改出现异常而没能修改，那么第一个表和第二表都要回到未修改的状态，这就是事务回滚
什么是锁
加锁可与防止多个事务同时存取同一数据，在事务释放锁之前，其他事务不能对此数据对象进行更新操作
锁类型包括表锁、行锁、页面锁
什么叫视图
视图是由查询结果形成的一张虚拟表，可以对它进行增删改查，对视图的修改不影响基本表
跟临时表最大的区别就是临时表会自动删除，而视图不会
什么叫游标
游标是对查询出来出来的结果集作为一个单元来有效的处理，游标可以定在单元中的特定行，从结果集的当前行检索一行或多行，可以对结果集当前行做修改，当需要逐条处理数据时，游标显得很重要
三个范式
第一范式：1NF 是对属性的原子性约束，要求属性具有原子性，不可再分解；
第二范式：2NF 是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
第三范式：3NF 是对字段冗余性的约束，即任何字段不能由其他字段派生出来，
它要求字段没有冗余。。
范式化设计优缺点:
优点:
可以尽量得减少数据冗余，使得更新快，体积小
缺点:对于查询需要多个表进行关联，减少写得效率增加读得效率，更难进行索引
优化
反范式化:
优点:可以减少表得关联，可以更好得进行索引优化
缺点:数据冗余以及数据异常，数据得修改需要更多的成本
基本表和视图的区别
基本表是本身独立存在的表，在sql中一个关系就对应一个表
视图是从一个或多个基本表导出的表，它本身并部独立存储在数据库中，是一个虚表
视图的优点
1、简化操作，直接把经常用的数据拿出来
2、安全性好，用户只能查询和修改看到的值
3、独立性好，屏蔽真实表的结构带来的影响
用什么来确保表格里的字段只接受特定范围里的值
Check 限制，它在数据库表格里被定义，用来限制输入该列的值。触发器也可以被用来限制数据库表格里的字段能够接受的值，但是这种办法要求触发器在表格里被定义，这可能会在某些情况下影响到性能
drop、delete、truncate的区别
truncate和delete只删除数据， drop则删除整个表(结构和数据)
drop、truncate是立即执行，不能回滚
delete与事务有关，可以回滚，也可以使用触发器
执行速度：drop>truncate>delete
存储过程
在开发的过程中，某些sql语句需要频繁的重复调用，因此我们就把常用的sql存储起来，这就是存储过程
简单来说，存储过程就是一组预先编译好的sql语句集
等到下次要使用的时候，直接使用存储过程就可以，而不需要重新写一遍sql，可以极大提高工作效率
如何使用存储过程
需要先定义存储过程，再使用已经定义好的存储过程
1）无参数的存储过程
create procedure 存储过程名称()
begin
<sql语句> ;
end；
语法里的begin…end用于表示sql语句的开始和结束
语法里面的<sql语句>就是重复使用的sql语句
eg：
create procedure a_stuent1()
begin
select 姓名
from 学生表;
end；
//调用
call a_stuent1();
2）有参数的存储过程
create procedure 存储过程名称(参数1,参数2,...)
begin
<sql语句> ;
end；
eg：
create procedure getNum(num varchar(100))
begin
select 姓名
from 学生表
where 学号=num;
end；
//调用
call getNum(0001);
3）默认参数的存储过程
前面的存储过程名称后面是(参数1,参数2)，括号里面只包含参数的类型和名字，方便调用
存储过程还一种情况是有默认参数，是下面的语法
-in 输入参数:参数初始值在存储过程前被指定为默认值，在存储过程中修改该参数的值不能被返回
-out输出参数:参数初始值为空，该值可在存储过程内部被改变，并可返回
-inout输入输出参数：参数初始值在存储过程前被指定为默认值，并且可在存储过程中被改变和在调用完毕后可被返回
存储过程使用的注意事项
-定义存储过程语法里的SQL语句代码块必须是完整的sql语句，必须用“;”结尾
create procedure 存储过程名称(参数1,参数2,...)
begin
<sql语句> ;
end；
-定义不同的存储过程，要使用不同的存储过程名称，相同的存储过程的名字会引起系统报错
存储过程的作用
-提高重用性，减少冗余
-减少了sql的编译次数，减少了与服务器连接交互的次数
preparedStatement和statement的区别与联系
PreparedStatement 接口继承 Statement
Statement为每条sql语句都创建一条执行计划，而PreparedStatement 实例包含已编译的 SQL 语句，所以PreparedStatement 执行速度要快于 Statement 对象
不仅如此，PreparedStatement因为可以预编译，因此比Statement安全，可以防止sql注入
Statement sta=con.createStatement();
//execute、 executeQuery 和 executeUpdate
ResultSet rst=sta.executeQuery("select * from book");
PreparedStatement pst=con.prepareStatement("select * from book");
//execute、 executeQuery 和 executeUpdate
ResultSet rst=pst.executeQuery();
Mysql日志
二进制日志（bin log）、重做日志（redo log）、回滚日志（undo log）
二进制日志
binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。可以简单理解为记录的就是sql语句，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志
binlog使用场景
在实际应用中， binlog 的主要使用场景有两个，分别是 主从复制 和 数据恢复
-主从复制：在 Master 端开启 binlog ，然后将 binlog发送到各个 Slave 端， Slave 端重放 binlog 从而达到主从数据同步
-数据恢复：通过使用mysqlbinlog工具来实现数据库基于时间点的还原
刷盘时机
对于 InnoDB 存储引擎而言，只有在事务提交时才会记录biglog ，此时记录还在内存中，那么 biglog是什么时候刷到磁盘中的呢？mysql 通过 sync_binlog 参数控制 biglog 的刷盘时机，取值范围是 0-N：
-sync_binlog=0：不去强制要求，由系统自行判断何时写入磁盘
-sync_binlog=1：每次 commit 的时候都要将 binlog 写入磁盘
-sync_binlog=N(N>1)：每N个事务，才会将 binlog 写入磁盘
从上面可以看出， sync_binlog 最安全的是设置是 1 ，这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能
重做日志(redo log)
**重做日志是InnoDB引擎层日志，用来记录事务操作引起数据变化，记录的是数据页的物理修改。**包括两部分：
-内存中的日志缓冲(redo log buffer)
-磁盘上的日志文件(redo logfile)
MySQL 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file，种先写日志再写磁盘的技术就是 WAL(Write-Ahead Logging) 技术。之后MySQL会在合适的时候将操作记录 flush 到磁盘上面，flush 的条件可能是系统比较空闲，或 redo log 空间不足时。redo log 文件的大小是固定的，如可以是由4个1GB文件组成的集合。
WAL(Write-Ahead Logging)
WAL（Write-Ahead Logging，预写式日志）是一种数据安全写入机制，即先写日志，再写磁盘，这样保证数据的安全性。先在内存中提交事务，然后写日志(在InnoDB中就是Redo Log，日志是为了防止宕机导致内存数据丢失)，然后再后台任务中把内存中的数据异步刷到磁盘。
作用
-确保事务的持久性
-防止发生故障时，尚有脏页未写入磁盘。在重启mysql服务时，根据redo log进行重做，从而达到事务的持久性特性
写入流程
为了控制 redo log 的写入策略，innodb_flush_log_at_trx_commit 会有下面 3 中取值：
-0：每次提交事务只写在 redo log buffer 中
-1：每次提交事务持久化到磁盘
-2：每次提交事务写到 文件系统的 page cache 中
刷盘场景
redo log 实际的触发 fsync 操作写盘包含以下几个场景：
后台每隔 1 秒钟的线程轮询
innodb_flush_log_at_trx_commit 设置成 1 时，事务提交时触发
innodb_log_buffer_size 是设置 redo log 大小的参数。当 redo log buffer 达到 innodb_log_buffer_size / 2 时，也会触发一次 fsync
回滚日志(undo log)
回滚日志（undo log）主要存储的是数据的逻辑变化日志，用于回滚操作。比如我们要 insert 一条数据，那么 undo log 就会生成一条对应的 delete 日志。所以当需要回滚时，只需要利用undo log 就可以恢复都修改前的数据。
作用
-提供回滚操作。undo log实现事务的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚
-提供多版本并发控制（MVCC）下的读。也即非锁定读
分表
水平分表（Sharding）：它是将同一个表中的记录拆分到多个结构相同的表中。当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力
水平分表的策略：
-哈希取模：hash(key)%N
-范围：根据id范围或者时间范围等等
-映射表：使用单独一个数据库来存储映射关系
水平存在的问题：
-事务问题，需要使用分布式事务
-连接，将原来的连接分解成多个单表查询
-唯一性，使用全局唯一id，为每一个分片创建一个id范围
垂直分表：是将一张表按列分成多个表，通常是按照列的关系密集程度进行切分，也可以将经常被使用的列和不经常被使用的列切分到不同的表中。在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不通的库中，例如将原来电商数据部署库垂直切分称商品数据库、用户数据库等
慢查询日志
1、慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中，默认值是10s，可以自己设置
sql的优化步骤
第一步：通过慢查询日志定位那些执行效率较低的sql语句
第二步：通过explain分析sql的执行计划
重点需要关注type、rows、filtered、extra
type：由上至下，效率越来越高
-all：全表扫描
select * from school
-index：需要扫描索引上的所有数据，比全表扫描快一点
-range：索引范围扫描，常用有<、<=、>=、between、in等操作，
-ref：使用非唯一索引或唯一索引前缀(最左匹配原则)，常用于关联查询，对于前表的每一行(row)，后表可能有多于一行的数据被扫描，这里的id都是普通索引
-eq_ref：使用主键或者唯一索引，常用于关联查询，这里id都是主键或者唯一索引，对于前表的每一行(row)，后表只有一行被扫描
-const：通过一个常量值直接命中主键或者唯一索引，最多只返回一条数据
eg：explain select * from user where id=1
-system：从系统库mysql的系统表time_zone里查询数据，扫码类型为system，这些数据已经加载到内存里，不需要进行磁盘IO，这是最快的
Extra
-Using filesort：表示mysql无法使用索引直接完成排序，用缓冲空间来进行排序。（order by 的字段为非索引）MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行
-Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化
-Using index：表示相应的 select 操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错
-Using where：表示搜到记录后进行了后过滤，如果查询未使用索引，作用只是提醒mysql使用了where过滤结果集
第三步：show profile 分析
了解SQL执行的线程的状态及消耗的时间
第四步：通过trace文件跟踪分析mysql是如何选择执行计划的，为什么会选择A计划，而不是B计划
第五步：确定问题并采用相应的措施
-优化索引
-优化SQL语句：修改SQL、IN 查询分段、时间查询分段、基于上一次数据过滤
-改用其他实现方式：ES、数仓等
-数据碎片处理
MySQL批量插入，如何不插入重复数据？
1、insert ignore into，插入时忽略重复的记录，例如主键重复冲突时，则只会插入一条，但如果没有主键或者主键不冲突，那么则会重复插入
2、on duplicate key update，根据主键id或唯一键来判断当前插入是否已存在，如果记录已存在，只会更新on duplicate key update之后指定的字段；如果同时传递了主键和唯一键，以主键为判断存在依据，唯一键字段内容可以被修改
具体的例子：https://blog.csdn.net/lxw1844912514/article/details/125240966
3、insert … select … where not exist，根据select的条件判断是否插入，可以不光通过primary和unique来判断，也可通过其它条件
INSERT INTO user (name) SELECT 'telami' FROM dual WHERE NOT EXISTS (SELECT id FROM user WHERE id = 1)
这种方法其实就是使用了MySQL的一个临时表的方式，但是里面使用到了子查询，效率也会有一点点影响，如果能使用上面的就不使用这个
4、replace into，如果存在primary or unique相同的记录，则先删除掉。再插入新记录
REPLACE INTO user SELECT 1, 'telami' FROM books
大分页
create_time建表时被设置为普通索引
select * from t_trade_order where create_time between '2019-10-17' and '2019-10-25'
limit 1000000, 10;
这时候的查询是在create_time索引树上找到create_time=‘2019-10-17’的记录，取其id，然后再到主索引树查到对于id的记录，就这样重复1000010次，然后再把放弃前1000000，返回10行，每次都会进行回表，每次回表都会进行一次IO，性能浪费很严重
优化后：
select t1.* from t_trade_order t1,
(
select id from t_trade_order
where create_time between '2019-10-17' and '2019-10-25'
limit 1000000, 10
) t2
where t1.id = t2.id
由于普通索引上存储都是对应的主键，直接拿相应的范围主键然后再去查一遍，即可减少mysql回表的次数，也避免了在原表上查询需要丢弃的行数
in走不走索引
in通常是走索引的，当in后面的数据在数据表中超过30%（上面的例子的匹配数据大约6000/16000 = 37.5%）的匹配时，会走全表扫描，即不走索引，因此in走不走索引和后面的数据有关系
mysql复制原理
基于语句的复制：基于语句的复制模式下，主库会记录那些造成数据更改的sql，当备库读取并重放这些事件时，实际上只把主库上执行过的SQL再执行一遍
优点：最明显的好处是实现相当简单。理论上讲，简单地记录和执行这些语句，能够让备库保持同步。另外好处是binlog日志里的事件更加紧凑，所以相对而言，基于语句的模式不会使用太多带宽。一条更新好几兆数据的语句在二进制日志里可能只占用几十字节
缺点：有些数据更新语句，可能依赖其他因素。例如，同一条sql在主库和备库上执行的时间可能稍微或很不相同，因此在传输的binlog日志中，除了查询语句，还包括一些元数据信息，如当前的时间戳
基于行的复制
这种方式会将实际数据记录在二进制日志中，跟其他数据库的实现比较相像
优点：最大的好处是可以正确的复制每一行，一些语句可以被更加有效地复制。由于无需重放更新主库数据的查询，使用基于行的复制模式能够更高效地复制数据。重放一些查询的代价会很高
缺点：全表更行，使用基于行复制开销会大很多，因为每一行数据都会呗记录到二进制日志中，这使得二进制日志时间非常庞大
高可用
在考虑mysql数据库高可用架构时，主要考虑到数据库的可用性以及一致性
-可用性，如果数据库发生了宕机或者意外中断等故障，尽可能的减少停机时间，保证业务不会因为数据库的故障而中断
-一致性，主节点的数据跟从节点的数据必须实时或者最终保持一致
Mysql主备
正常情况下，客户端访问的都是主库，然后主库将数据再同步给备用库
但主库由于某些原因，无法正常响应客户端的请求
mysql怎么做？
-完成主备切换
-将客户端的读写对象改成备库，此时备库升级成新主库
Mysql主从同步原理
mysql主从同步过程：
mysql的主从复制主要有三个线程：master：二进制日志转储线程(binlog dump thread)、slave：I/O线程(I/O thread)和SQL线程(SQL thread).master一条线程和slave中的两条线程
主节点binlog，主从复制的基础是主库将数据库的所有变更记录到binlog，实际上主从同步的原理就是基于binlog进行数据同步的
-主节点二进制日志转储线程(log dump)，当binlog有变动时，log dump线程读取其内容并发送给从节点
-从节点I/O线程，接受binlog内容（这里是数据更新的部分），并将其拷贝到本地形成中继日志(Relay log)
-从节点SQL线程读取relay log 文件内容对数据更新进行重放，最终保证主从数据库一致性
注：主节点使用binlog文件 + position偏移量来定义主从同步的位置，从节点会保存其已接受到的偏移量，如果从节点发生宕机重启，则会自动从position的位置发起同步
由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败，这时候从库升级为主库后，日志就丢失了
全同步复制：主库写入binlog后强制同步日志到从库，所有的从库执行完成之后长才返回给客户端，但是这样性能就会收到严重影响。
半同步复制：和全同步复制不同的是，半同步复制是从库写入日志成功返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成
半同步复制优化
半同步复制机制是可靠的。如果半同步复制一直是生效的，那么可以认为数据是一致的。但是由于网络波动等一些客观原因，导致半同步复制发生超时而切换为异步复制，这时便不能保证数据的一致性。所以尽可能的保证半同步复制，就可以提高数据的一致性。
该方案同样使用双节点架构，但是在原有半同复制的基础上做了功能上的优化，使半同步复制的机制变得更加可靠。可参考的优化方案如下：双通道复制
主从延迟
根据主从复制的原理可以看出，两者之间是存在一定时间的数据不一致，也就是所谓的主从延迟。导致主从延迟的时间点：
-主库 A 执行完成一个事务，写入 binlog，该时刻记为T1
-传给从库B，从库接受完这个binlog的时刻记为T2
-从库B执行完这个事务，该时刻记为T3
那么所谓主从延迟，就是同一个事务，从库执行完成的时间和主库执行完成的时间之间的差值，即T3-T1。我们也可以通过在从库执行show slave status，返回结果会显示seconds_behind_master，表示当前从库延迟了多少秒
为什么会主从延迟？
正常情况下，如果网络不延迟，那么日志从主库传给从库的时间是相当短，所以T2-T1可以基本忽略。最直接的影响就是从库消费中转日志（relaylog）的时间段，而造成原因一般是以下几种：
-从库的机器性能比主库要差
比如将20台主库放在4台机器，把从库放在一台机器。这个时候进行更新操作，由于更新时会触发大量读操作，导致从库机器上的多个从库争夺资源，导致主从延迟。不过，目前大部分部署都是采取主从使用相同规格的机器部署
-从库的压力大
按照正常的策略，读写分离，主库提供写能力，从库提供读能力。将进行大量查询放在从库上，结果导致从库上耗费了大量的CPU资源，进而影响了同步速度，造成主从延迟。对于这种情况，可以通过一主多从，分担读压力；也可以采取binlog输出到外部系统，比如Hadoop，让外部系统提供查询能力
-大事务的执行
一旦执行大事务，那么主库必须要等到事务完成之后才会写入binlog。比如主库执行了一条insert … select非常大的插入操作，该操作产生了近几百G的binlog文件传输到只读节点，进而导致了只读节点出现应用binlog延迟。因此，DBA经常会提醒开发，不要一次性地试用delete语句删除大量数据，尽可能控制数量，分批进行
怎么减少主从延迟？
主从同步问题永远都是一致性和性能的权衡，得看实际的应用场景，若想要减少主从延迟的时间，可以采取下面的办法：
-降低多线程大事务并发的概率，优化业务逻辑
-优化SQL，避免慢SQL，减少批量操作，建议写脚本以update-sleep这样的形式完成
-提高从库机器的配置，减少主库写binlog和从库读binlog的效率差
-尽量采用短的链路，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输的网络延时
-实时性要求的业务读强制走主库，从库只做灾备，备份
1288
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
