mysql 知识点

• 设置序列的开始
  1.drop table tbl_nm;
  create table tbl_nm(
  id int unsigned not null auto_increment,
  )engine=innodb auto_increment=100 default charset=utf8;
  2.alter table tbl_nm auto_increment=200;

• 创建临时表
  create TEMPORARY table temp_test
  (
  id int unsigned not null primary key auto_increment,
  name char(20) not null default ‘’
  )engine=innodb auto_increment=200 charset=utf8;

insert into temp_test values(NULL, ‘lisi’);
//可以查询临时表记录，show tables无法查看到临时表
select * from temp_test;
//当前会话接收，自动删除临时表，也可主动删除临时表
drop table temp_test;

• 表复制
  1.create table tbl_nm (select * from tbl_nm)
  表结构和记录会同步，但索引不会被创建
  //表克隆
  1.show create table source_tbl_nm 获取原表Create Table创表sql;
  2.修改sql中表明为目的表名称
  3.原表数据导入到目的表中
  insert into dest_tbl_nm values (‘cl1’,‘cl2’) select * from source_tbl_nm;

• 预处理
  1.定义预处理语句：PREPARE stmt_nm FROM preparable_stmt;
  2.执行预处理语句：EXECUTE stmt_nm [USING @var_nm1 [, @var_nm2]…];
  3.删除或释放定义：DEALLOCATE | DROP PREPARE stmt_nm;

	#案例:
	prepare pre_user from 'select * from user_info where name=?';
	set @name1='张三';
	set @name2='李四';
	execute pre_user using @name1;
	execute pre_user using @name2;
	drop prepare pre_user;

通过max_prepared_stmt_count变量来控制全局最大的存储的预处理语句
show variables like ‘max_prepared%’;

预处理编译sql是占用资源的，在使用后需要及时DEALLOCATE PREPARE 释放资源

• 预编译效率
  客户端通过发送一条sql语句到服务器，需要经过解析器(词法和语法)、
  预处理器(检测解析器生成的解析树，检测表名、列表是否存在、检查名字和别名没有歧义并检测权限)、
  优化器(找出最优的执行路径，生成执行计划)
  查询执行引擎执行查询并返回给客户端

若果需要多次执行insert、update、delete语句，但是每次插入的值不同，mysql服务器
也需要经过解析器、预处理器、优化器，就会浪费太多时间
若果使用预编译功能，sql语句只会一次的校验和编译，效率比较高

实例
prepare stmt_nm from ‘select * from employees where employee_id=?’;
set @var=100;
execute stmt_nm using @var;
set @var=101;
execute stmt_nm using @var;
deallocate prepare stmt_nm;

• 预处理sql注意点
  1.stmt_nm作为preparable_stmt的接收者，唯一标识，不区分大小写
  2.preparable_stmt语句中的?是个占位符，所代表的是一个字符串
  不需要将?用引号包含起来
  3.定义一个已存在的stmt_nm，原有的将被立即释放，相当于变量的重新赋值
  4.prepare stmt_nm的作用域是session级

• 预编译的好处
  1.预编译之后的sql多数情况下可以直接执行，DBMS不需要再次编译
  2.越复杂的SQL，编译的复杂度将越大，预编译阶段可以合并多次操作为一个操作；
  3.相同的预编译sql可以重复利用
  4.可以将这类sql语句中的值用占位符替代，不需要每次编译，可以直接执行
  只需要执行的时候，直接将每次请求的不同值设置到占位符的位置
  5.预编译可以视为将sql语句模块化或者说参数化

隔离级别几种问题

• 脏读： 事务A中可以读到事务B未提交的数据（脏数据） ， 这种现象是脏读

• 不可重复读： 在事务 A 中先后两次读取同一个数据， 两次读取的结果不一样， 这种现象称为不可重复读。 脏读与不可重复读的区别在于： 前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据

• 幻读： 在事务 A 中按照某个条件先后两次查询数据库， 两次查询结果的条数不同，这种现象称为幻读。 不可重复读与幻读的区别可以通俗的理解为： 前者是数据变了， 后者是数据的行数变了
  

• 不可重复读和幻读到底有什么区别呢？
  （1） 不可重复读是读取了其他事务更改的数据， 针对 update 操作
  解决： 使用行级锁， 锁定该行， 事务 A 多次读取操作完成后才释放该锁， 这个时候才允许其他事务更改刚才的数据。
  （2） 幻读是读取了其他事务新增的数据， 针对 insert 与 delete 操作
  解决： 使用表级锁， 锁定整张表， 事务 A 多次读取数据总量之后才释放该锁， 这个时候才允许其他事务新增数据。
  幻读和不可重复读都是指的一个事务范围内的操作受到其他事务的影响了。只不过幻读是重点在插入和删除， 不可重复读重点在修改

• 存储引擎
  show engines;
  查看默认存储引擎
  show variables like ‘%storage_engine’
  设置存储引擎
  1.create table (
  id int not null primary key auto_increment
  )engine=innodb(不写默认innodb)
  2.配置文件修改：default-storage-engine = engine
  3.alter命令修改：alter table tbl_nm engine=myisam;

mysql优化
1.sql语句和索引的优化
2.数据库表结构的优化
3.系统配置的优化
4.硬件的优化

sql优化
1.对查询进行优化， 应尽量避免全表扫描， 首先应考虑在 where 及 order by 涉及的列上
建立索引。
2.应尽量避免在 where 子句中使用!=或<>操作符， 否则将引擎放弃使用索引而进行全表扫
描。
3.应尽量避免在 where 子句中对字段进行 null 值判断， 否则将导致引擎放弃使用索引而
进行全表扫描

select id from t where num is null
可以在 num 上设置默认值 0， 确保表中 num 列没有 null 值， 然后这样查询：
select id from t where num=0
4.应尽量避免在 where 子句中使用 or 来连接条件， 否则将导致引擎放弃使用索引而进行
全表扫描， 如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
5.下面的查询也将导致全表扫描：
select id from t where name like ‘%abc%’
若要提高效率， 可以考虑全文检索。
6.in 和 not in 也要慎用， 否则会导致全表扫描， 如：
select id from t where num in(1,2,3)
对于连续的数值， 能用 between 就不要用 in 了：
select id from t where num between 1 and 3
7.如果在 where 子句中使用参数， 也会导致全表扫描。 因为 SQL 只有在运行时才会解析局
部变量， 但优化程序不能将访问计划的选择推迟到运行时； 它必须在编译时进行选择。 然而，
如果在编译时建立访问计划， 变量的值还是未知的， 因而无法作为索引选择的输入项。 如下
面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num
8.应尽量避免在 where 子句中对字段进行表达式操作， 这将导致引擎放弃使用索引而进行
全表扫描。 如：
select id from t where num/2=100
应改为:
select id from t where num=100*2
9.应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表
扫描。 如：
select id from t where substring(name,1,3)=‘abc’–name 以 abc 开头的 id
select id from t where datediff(day,createdate,‘2005-11-30’)=0–'2005-11-30’生成
的 id
应改为:
select id from t where name like ‘abc%’
select id from t where createdate>=‘2005-11-30’ and createdate<‘2005-12-1’
10.不要在 where 子句中的“=” 左边进行函数、 算术运算或其他表达式运算， 否则系统将
可能无法正确使用索引。
11.在使用索引字段作为条件时， 如果该索引是复合索引， 那么必须使用到该索引中的第一
个字段作为条件时才能保证系统使用该索引， 否则该索引将不会被使用， 并且应尽可能的让
字段顺序与索引顺序相一致。
12.不要写一些没有意义的查询， 如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集， 但是会消耗系统资源的， 应改成这样：
create table #t(…)
13.很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
14.并不是所有索引对查询都有效， SQL 是根据表中数据来进行查询优化的， 当索引列有大
量数据重复时， SQL 查询可能不会去利用索引， 如一表中有字段 sex， male、 female 几乎各
一半， 那么即使在 sex 上建了索引也对查询效率起不了作用。
15.索引并不是越多越好， 索引固然可以提高相应的 select 的效率， 但同时也降低了
insert 及 update 的效率， 因为 insert 或 update 时有可能会重建索引， 所以怎样建索
引需要慎重考虑， 视具体情况而定。 一个表的索引数最好不要超过 6 个， 若太多则应考虑一
些不常使用到的列上建的索引是否有必要。
16.应尽可能的避免更新 clustered 索引数据列， 因为 clustered 索引数据列的顺序就是
表记录的物理存储顺序， 一旦该列值改变将导致整个表记录的顺序的调整， 会耗费相当大的
资源。 若应用系统需要频繁更新 clustered 索引数据列， 那么需要考虑是否应将该索引建
为 clustered 索引。
17.尽量使用数字型字段， 若只含数值信息的字段尽量不要设计为字符型， 这会降低查询和
连接的性能， 并会增加存储开销。 这是因为引擎在处理查询和连接时会逐个比较字符串中每
一个字符， 而对于数字型而言只需要比较一次就够了。
18.尽可能的使用 varchar/nvarchar 代替 char/nchar ， 因为首先变长字段存储空间小，
可以节省存储空间， 其次对于查询来说， 在一个相对较小的字段内搜索效率显然要高些。
19.任何地方都不要使用 select * from t ， 用具体的字段列表代替“*” ， 不要返回用不
到的任何字段。
20.尽量使用表变量来代替临时表。 如果表变量包含大量数据， 请注意索引非常有限（只有
主键索引） 。
21.避免频繁创建和删除临时表， 以减少系统表资源的消耗。
22.临时表并不是不可使用， 适当地使用它们可以使某些例程更有效， 例如， 当需要重复引
用大型表或常用表中的某个数据集时。 但是， 对于一次性事件， 最好使用导出表。
23.在新建临时表时， 如果一次性插入数据量很大， 那么可以使用 select into 代替 create
table， 避免造成大量 log ， 以提高速度； 如果数据量不大， 为了缓和系统表的资源， 应先
create table， 然后 insert。
24.如果使用到了临时表， 在存储过程的最后务必将所有的临时表显式删除， 先 truncate
table ， 然后 drop table ， 这样可以避免系统表的较长时间锁定。
25.尽量避免使用游标， 因为游标的效率较差， 如果游标操作的数据超过 1 万行， 那么就应
该考虑改写。
26.使用基于游标的方法或临时表方法之前， 应先寻找基于集的解决方案来解决问题， 基于
集的方法通常更有效。
27.与临时表一样， 游标并不是不可使用。 对小型数据集使用 FAST_FORWARD 游标通常要优
于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合
计” 的例程通常要比使用游标执行的速度快。 如果开发时间允许， 基于游标的方法和基于集
的方法都可以尝试一下， 看哪一种方法的效果更好。
28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ， 在结束时设置 SET NOCOUNT
OFF 。 无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29.尽量避免向客户端返回大数据量， 若数据量过大， 应该考虑相应需求是否合理。
30.尽量避免大事务操作， 提高系统并发能力

表结构优化
1.使用可以存下你的数据的最小的数据类型
2.使用简单的数据类型，int要比varchar类型
在mysql处理上简单
3.尽可能的使用not null定义字段
4.尽量少用text类型，非用不可时最好考虑分表

表垂直拆分：吧很多列的表拆分成多个表，解决表的宽度问题
1.把不常用的字段单独存放到一个表中
2.把大字段独立存放到一个表中
3.把经常一起使用的字段放到一起

水平拆分：解决单表的数据量过大问题，水平拆分的
每个表结构一致

mysql分为server层和存储引擎层（innodb,myisam,memory等插件）
mysql连接请求到服务器经过哪些过程
1.经过连接器(对用户密码进行验证，权限表进行权限查询)
2.缓存(若查询的sql语句命中key_value键值对，则直接将结果进行返回)
数据量大，表更新频繁则缓冲不建议开启，因为每次更新表后，缓存中的数据就
失效并被清空，并将下次查询的数据重新添加到缓存中，这样缓存频繁清空和添加影响性能；
是对一些静态经常查询的表可以开启；
3.若缓存没有命中，则会经过分析器
词法语法分析，select insert等关键字是否正确，是否符合mysql语法
4.优化器，执行计划生产，索引选择
表中有多个索引的时候，决定使用哪个索引；在一个语句有多
表关联(join)的时候，决定各个表连接的顺序。
5.执行器，操作引擎，返回结果
先查看执行查询的权限，没有权限就返回权限错误
后根据引擎接口进行查询

防止数据库突然宕机，怎么恢复数据
用到redo log日志，redo log是固定大小，比如配置一组4个文件，每个文件大小为1GB
每次对数据更新操作时都会先写到redo log日志文件中
buff中会有write point用于将sql写入到buff中，checkpoint中用于将检查点中数据更新到文件后删除数据并向后推移，直到write point位置后停止删除；
日志sync过程是每次commit会先提交到用户态log buff中，根据同步标识会到内核态
os buffer，最后到log file中；

redo log和binlog有什么区别

1. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可
   以使用。
2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，
   记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
3. redo log是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指
   binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

redo log和binlog数据更新执行流程

1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2
   这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内
   存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的
   一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo
   log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状
   态，更新完成
   

索引
hash索引：只支持等值查询，不支持分组和范围查询，
有序数组索引：支持等值查询和范围查询，但更新时会会导致更新点后面元素向后移动
适用于静态存储引擎表，一般是历史数据，不会再更改了
二分查找查询时间复杂度是O(logN)

二叉树索引：更新和查询时间复杂度是O(logN)，访问效率高，跟数的深度有关
树的深度越大，IO操作次数就多，效率就低，所以最好使用B+树索引

B+树
索引类型分为主键索引和非主键索引
主键索引的叶子节点存储的是整行数据，在Innodb里，主键索引也被
称为聚簇索引；
非主键索引叶子节点存储的是主键，在Innodb里非主键索引页被
称为二级索引，根据非主键索引查询到叶子上主键值后再根据主键
索引查询B+树一次，获取对应记录(过程称为回表)
非主键索引查询会多扫描 一次索引树

primary key id,
index(k)
select ID from T where k between 3 and 5
回表两次
覆盖索引，索引优化
就是select的数据列只用从索引中就能够取得，不必从数据表中读取(不需要回表)，
换句话说查询列要被所使用索引覆盖。

下面两种索引重建哪个正确？
1.
alter table T drop index k;
alter table T add index(k);
2.
alter table T drop primary key;
alter table T add primary key(id);
上面重建索引哪个正确，答案是第一个
重建索引的原因：索引可能因为删除、页分裂等原因导致数据页有空洞，
重建索引会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高
也就是索引更紧凑，更省空间
重建主键索引：无论是删除主键还是创建主键，都会将整个表重建

索引下推(5.6版本后支持)
index(name,age)
select * from tbl where name like ‘张%’ and age=10;
无索引下推：符合最左前缀，name索引有效，在二级索引树上查询到
'张’后进行回表查询，回表次数是’张’出现的记录数
索引下推:在二级索引树上查询到’张’后，并在内部判断age是否等于10，
是的话，进行回表操作，可以减少回表次数

全局锁（FTWRL-flush tables with read lock）
全局锁就是对整个数据库实例加锁，当需要让整个库处于只读
状态时候，可以使用这个命令，之后其他线程以下语句会被阻塞：
数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构)和
更新类事物的提交语句；

让整个库加锁的话缺点
1.主库上备份，备份期间不能执行更新操作，业务基本上停摆
2.从库上备份，那么备份期间从库不能执行主库同步过来的binlog,会导致主从延迟
可以使用-–single-transaction获取一致性视图
设置只读操作set global readonly=true不可以取，这个标识一般用于主从判断
若客户端连接异常端开连接后，就会一直处于readonly状态，风险高，而FTWRL
mysql会自动是否全局锁，整个库可以回到正常可更新状态；

表锁
1.lock talbes …read/write
使用unlock tables主动释放锁，也可以在客户端断开时候自动释放；
lock tables除了会限制别的线程读写，也限制本线程接下来的操作对象
使用lock tables来锁住整个表控制并发影响太大
2.MDL(metadata lock) MDL会直到事务提交才释放，保证读写正确性
当对一个表做增删改查操作、表结构变更操作都会加MDL写锁
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线
程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

行锁
innodb行级锁是通过锁索引记录实现的。如果update的列没建索引，即使只update一条记 录也会锁定整张表
在innodb事物中，行锁是在需要的时候才加上的，但并不是
不需要了就立即释放，而是等待事物结束时才释放，这个就是两阶段锁；
如果实际事物中需要锁多个行，要把最可能造成锁冲突，最后能影响并发度的锁申请
时机尽量往后放；

行级锁不只是update操作会用到，insert操作也会出现
比如插入两个id相同记录时

死锁和死锁检测
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的
线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁；
比如事物交叉
T1 T2
T2 T1
死锁检测时间复杂度O(n)计算：每个线程在判断有没有死锁过程中，
都会扫描一下所有人，1000个并发线程，则是100万量级死锁检测，
死锁检测就会消耗CPU，实际事物没有几个；

解决死锁
1.直接进入等待，知道超时，innodb_lock_wait_timeout参数设置
2.发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事物
让其他事物得以执行，innodb_deadlock_detect参数为on开启；
若采用第一个策略，等待时间50s的话等超时退出后，其他线程才能继续
执行，对于在线服务来说，等待时间无法接受；
若等待时间过短1s，当出现死锁时很快可以解开，但如果不是死锁而是简单的锁等待
时间设置过短就会出现误伤；
所以正常使用第二种策略，主动死锁检测可以快速发现并处理，但是它有额外的负担；

比如1000个线程同时更新同一条记录，死锁检测操作时100万量级的，虽然最终
检测的结果是没有死锁，但是期间消耗大量的cpu资源，会看到CPU利用率很高
但是每秒却执行不了几个事物；
解决：1.控制客户端并发；2.修改mysql源码，对于相同行的更新，在进入
引擎之前进行排队，innodb内部 就不会有大量死锁检测工作了；3.将一行记录
金额等于10个记录值总和，随机对其中一个记录金额相加，冲突减少1/10
可以减少锁等待个数，也减少死锁检测的cpu消耗