MySQL

优化

如何进行慢SQL优化

开启慢sql日志功能(在mysql中默认关闭),查找那些sql执行效率低

查看慢sql日志是否打开：show variables like 'slow_query_log';
开启慢sql日志功能：set global slow_query_log=1
	执行时间超过10s时，默认视为慢sql，会将该sql记录到日志文件中。
查询慢sql日志设置的时间：show global variables like 'long_query_time'
如果需要，可以修改设置时间（阈值时间）： set global long_query_time=2
查看日志文件： show variables  like 'slow_query_log_file'

注意点：对慢sql日志功能设置完成，需要重启数据库服务器，才能生效
慢sql日志文件在数据库安装路径中的data目录下（前提：打开慢sql日志功能）

具体优化措施:

1. 看慢sql是否使用了*,若是,则改为具体的字段
2. 看慢sql是否使用了嵌套查询,此时是否可以将嵌套查询转换为联查,若可以,则使用联查,因为联查的效率高于嵌套的.
3. 检查查询条件部分的字段是否需要使用索引,若需要,确定查询条件字段是否使用了索引,若没有,则添加索引
4. 检查查询条件部分的字段是否添加了索引,若添加了索引,检查此时对字段进行条件查询的操作是否导致索引失效

如何优化数据库?

• 使用读写分离,主从复制,集群,分库分表

锁

锁的分类

行锁 表锁

共享锁 排它锁

按照锁的粒度分:   表锁    行锁
按照锁的类型分:   共享锁  排它锁
	共享锁:也叫做share锁/S锁
		特点:可以给表加,也可以给行数据加,其特点为: 给目标数据加上share锁之后允许其他事务继续对该数据加share锁,不允许其他事务对该数据加排它锁;通常读取数据时使用
				
	排他锁/独占锁:也叫X锁
		特点:给数据加排它锁,不允许其他事务继续给该数据加排它锁,同时不允许其他事务给该行数据加共享锁,适用于写操作
		  
    增删改操作默认给操作的行数据加排它锁
    select操作默认不加任何锁
    
如何在查询时加共享锁/排它锁
	select ......lock in share mode;     //查询加共享锁
	select.....for update;  //查询加排它锁	
模糊查询会触发表锁
select...from ..where like ''  --表锁

mysql的存储引擎：
  mysql5.5开始存储引擎变成InnoDB，特点：支持行锁 

悲观锁和乐观锁

• 是两种思想

• 悲观锁:

  当多事务/多线程并发执行时,事务总是悲观的认为,在自己访问数据期间,其他事务一定会并发执行,此时会产生线程安全问题
  所以为了保证线程安全,这个事务在访问数据时,立即给数据加锁,从而保证线程安全.
  特点:可以保证线程安全,但是并发执行效率低下
  synchronized   排它锁都是悲观锁的应用

• 乐观锁:

  在多线程/多事务并发执行中,某个事务总是乐观的认为,在自己执行期间,没有其他事务与之并发,认为不会产生线程安全问题
  所以不会给数据加锁;但是确实存在其他事务与之并发执行的情况,确实存在线程安全问题,为了保证线程安全,通过版本号机制或CAS来保证线程安全.
  CAS:compare and swap  比较并交换

死锁

数据库中出现死锁，数据库是如何解决的？

clientA:
	1. setautocommit=off
	2. begin;
	3. update student set sname=xx where sno=1
	4. delete from course where cno=1
	
clientB:
	1. setautocommit=off
	2. begin;
	3. update course set..where cno=1
	4. delete from student where sno=1
	
mariadb对死锁的处理:检测到死锁后,让一端的事务回滚
并提示DeadLock,让另一端的事务执行成功.

事务

什么是事务

事务是数据库中执行操作的最小执行单元，不可再分，要么全都执行成功，要么全都执行失败。

事务的四大特性

原子性   一致性   隔离性   持久性

如何管理事务

查看事务的自动提交是否开启：
	show variables like 'autocommit'
		on  off
如何关闭事务自动提交：
	set autocommit=off;
事务管理：
	开启事务： begin
	提交事务： commit
	回滚事务： rollback
	
对数据库的增删改操作默认开启事务，而select不涉及事务

当业务方法涉及到多步增删改操作时，且想要他们保证要成功全成功
但凡有一个操作失败，则整个操作应该全部失败
此时就应该为这个业务方法开启事务管理。

事务隔离级别

• 读未提交
• • 事务可以读取到其他事务未提交/未回滚前的数据>>>会产生脏读问题
• 读已提交
• • 事务只能读取到其他事务提交/回滚后的数据>>>会产生不可重复度问题
• 可重复读
• • 给行数据加锁>>>会产生幻读问题
• 可串行化
• • 给表加锁>>>效率最低

脏读:由于事务读取到了其他事务未提交/未回滚前的数据
	导致读取的数据最终是不存在的,这个现象就叫做脏读.
不可重复读:在事务A执行期间,其他事务对事务A访问的数据进行修改操作
	导致事务A中前后两次读取相同的数据的结果是不一致的这个现象就叫做不可重复读
幻读: 在事务A访问数据期间,其他事务执行了插入操作
	导致事务A前后两次读取到的数据总量不一致,这个现象就叫做幻读.

数据库默认的隔离级别

oracle和sql server 默认的隔离级别为 读已提交
mysql的 默认隔离级别为 -- 可重复读

mysql默认的隔离级别可重复读是如何实现的?

存储引擎为Innodb的mysql,其隔离级别可重复读的实现是通过MVCC实现的

MVCC（Multi-Version Concurrency Control）- 多版本并发控制

多版本并发控制解决了并发安全问题,且并发执行效率高很多.
    
MVCC的实现由三部分配合实现:
	1. undolog  
	2. 隐藏字段(mysql中的表里边每个表都有隐藏的三个字段)
	3. ReadView

• 隐藏字段

row_id       -- Innodb存储引擎提供的隐藏主键 -- 当表中没有主键时自动生成 -- 隐藏主键
DB_trx_id    -- 事务的id  --  该列中保存的id值为最后操作该数据的事务id
DB_roll_ptr  -- 数据回滚指针,保存要回滚到的数据的地址

• ReadView

事务执行操作时,会生成当前事务的ReadView,ReadView保存当前事务之前活跃的所有事务id

ReadView有四个字段:
m_ids: 截止到当前事务id之前,所有活跃的事务id
min_trx_id: 记录以上活跃事务id中的最小值
max_trx_id: 保存当前事务结束后应分配的下一个id值
creator_trx_id: 保存创建ReadView的当前事务id

• 三者配合实现mysql的隔离级别
  

索引

什么是索引

索引是作用于列上,用于对该列的值进行排序,形成一个目录
从而提高该字段的查询效率的,索引适用于数据量大的表中.

• 索引底层是B+Tree
• • 索引岁B+Tree进行了优化,在叶子数据块中保存的元素不是一个元素值,而是key-value

索引的分类:

聚簇索引(聚集索引):给主键id添加的索引就叫做聚簇索引
非聚簇索引(非聚集索引):给非主键字段添加的索引叫做非聚簇索引

聚簇索引

聚簇索引是Innodb存储引擎默认添加的.无需我们添加
聚簇索引中的key和value分别保存什么?
    key: 主键-id
    value: 主键对应的行数据
    
聚簇索引中,根据id就可以直接找到对应的行数据

非聚簇索引

是需要我们添加的,其key和value分别为:
 key:保存添加了索引的那列的值
 value: 这行数据对应的id(主键)
     
非聚簇索引中,根据添加了索引的那列的值,可以快速的找到对应的id,此时再根据id到聚簇索引中,可以快速查询到对应的行数据,这个操作叫做回表操作.

索引操作

1. 创建索引
	create index index_name on table_name(col)
	案例: 给字段添加unique约束,验证是否Innodb默认给添加了索引
		添加unique约束:
			alter  table  t_name add col  type  unique;
			create table t_name(id int primary key,name varchar(20) unique)
2. 查询索引
	show index from table_name
3. 删除索引
	drop index index_name on table_name

索引使用的场景

1. 表中的数据量大时,应该使用索引.表中数据量不大,不要使用索引,因为建立索引也是需要时间的.
2. 通常会给作为查询条件的字段添加索引
3. 当某字段的值会被频繁修改时,不要给该字段添加索引,因为每次修改都会改变元素的排序,从而导致索引重构,耗费时间
4. 在一个表中,索引并不是越多越好,通常情况下,一个表中的索引不要超过6个

索引失效的场景

索引失效是指：因为一些不当操作，导致进行全表扫描，而不使用索引，这种情况我们叫做索引失效。

使用索引时sql语句要避免的情况：
1.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
       where name is not null  /is null
2.应尽量避免在 where 子句中使用!=操作符，否则将引擎放弃使用索引而进行全表扫描
3.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
     where name=xx or age=xx or col=xx  若其中一个字段没有索引,其他有索引的字段也不会走索引
4.not in 也要慎用，否则会导致全表扫描，in并不会导致索引失效
     where ..not in(xx,xx,xx)
     适用in 会不会适用索引? -- 会
5.尽量避免在where子句中对字段使用like左侧模糊查询(like '_%')，会导致全表扫描
	where xx like '%xx'  /like '_x'  
6.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
		eg: select...from user where age+4>12
7.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
		eg：select...from ...where round(score)=....

使用索引注意事项：
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率
因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定
一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

视图

什么是视图

视图是虚拟表,用于展示结果集,其中并不保存数据,其数据来源于真实表中.视图实质上是用于封装sql的,后续若想再次执行相同的sql,直接调用视图名称即可.
    
场景:
	在数据库中若要多次展示同样的数据,其数据来源于4表,一样的sql写多次,此时出现了sql重复问题
	数据库如何解决这个问题?
        将上述的sql封装起来,给这个sql起一个名字,后续若想再次执行改sql,直接调用名字即可
        
       create view view_name as select....from A join B join c join D .........    

操作视图

创建视图
	create view view_name as select.......
	create view view_name(col1,col2,col3,col4) as select......

调用视图 :因为视图是虚拟表,所以对视图的操作和对表的操作是一样的
	select ...from  table_name
	select ...from view_name
	
	desc table_name;
	desc view_name;

删除视图   
	drop table table_name
	drop view view_name

数据库五大约束

主键约束  -- 要求数据非空且唯一
唯一性约束 -- unique   要求数据唯一
外键约束 -- foreign key   特点: 若主键的值正在作为外键被使用,则不能删除该条数据
非空约束 -- not null  要求数据不能为空
检查约束  -- check(age between 18 and 20)   要求插入的数据中的age必须在18-20之间

数据库三大范式

1NF是指所有的列必须是原子性的
2NF是非码属性必须完全依赖于候选码
3NF是非主属性不能依赖于其他非主属性

存储引擎

Innodb

从mysql5.5开始存储引擎换为Innodb,该存储引擎有以下的特点:
1. Innodb支持事务和行锁
2. 默认会给表的主键添加聚簇索引;若表中没有提供主键,此时Innodb会自动给表添加隐藏主键,类型为long,长度为6,Innodb会给该主键添加聚簇索引
3. 除聚簇索引外,Innodb默认还会给添加了unique约束以及外键约束的字段添加索引.