MYSQL知识随笔

using()

select * from student a join class b on a.id=b.id 

可以写成 select * from student a join class b using(id)

UNION

用于合并两个或多个select语句的结果集, 并消去表中任何重复的行

注意: union内部的select语句必须具有相同数量的列, 并且列也必须拥有相似的数据类型, 每条select语句中的列的顺序也必须相同

SELECT column_name FROM table1

UNION

SELECT column_name FROM table2

UNION ALL

与上类似 ,但是他不会消除重复的记录 ,需要保留重复记录时使用

事务

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在 MySQL 中，事务支持是在引擎层实现的。

对于同时运行的多个事务（多线程并发）, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.

不可重复读: 对于两个事务 T1, T2, T1 读取了一个字段, 然后 T2 更新并提交了该字段. 之后, T1再次读取同一个字段, 值就不同了.

幻读: 对于两个事务 T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入、删除了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出、少了几行

在谈隔离级别之前，你首先要知道，你隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。下面我逐一为你解释：

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。

读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。

可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

事务隔离的实现

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

事务的启动方式

1.显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。

2.set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中

你可以在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务。

select * from information_schema.innodb_trx where

TIME_TO_SEC(timediff(now(),trx_started))>60

@Transactional

再java中  @Transactional 写在方法上, 在目标方法开始之前创建或者加入一个事务,在执行完目标方法之后根据情况提交或者回滚事务

简而言之该注解可以在代码执行出错得时候能够进行事务得回滚.

在项目中，@Transactional(rollbackFor=Exception.class)，如果类加了这个注解，那么这个类里面的方法抛出异常，就会回滚，数据库里面的数据也会回滚。

在@Transactional注解中如果不配置rollbackFor属性,那么事物只会在遇到RuntimeException的时候才会回滚,加上rollbackFor=Exception.class,可以让事物在遇到非运行时异常时也回滚。

注解失效问题:

1.@Transactional 应用在非 public 修饰的方法上

事务拦截器在目标方法执行前后进行拦截，内部会调用方法来获取Transactional 注解的事务配置信息，调用前会检查目标方法的修饰符是否为 public，不是 public则不会获取@Transactional 的属性配置信息。

2.@Transactional 注解属性 rollbackFor 设置错误

rollbackFor 可以指定能够触发事务回滚的异常类型。Spring默认抛出了未检查unchecked异常（继承自 RuntimeException 的异常）或者 Error才回滚事务；其他异常不会触发回滚事务。如果在事务中抛出其他类型的异常，但却期望 Spring 能够回滚事务，就需要指定rollbackFor属性。

3.同一个类中方法调用，导致@Transactional失效

开发中避免不了会对同一个类里面的方法调用，比如有一个类Test，它的一个方法A，A再调用本类的方法B（不论方法B是用public还是private修饰），但方法A没有声明注解事务，而B方法有。则外部调用方法A之后，方法B的事务是不会起作用的。这也是经常犯错误的一个地方。
那为啥会出现这种情况？其实这还是由于使用Spring AOP代理造成的，因为只有当事务方法被当前类以外的代码调用时，才会由Spring生成的代理对象来管理。

4.异常被你的 catch“吃了”导致@Transactional失效

如果你手动的catch捕获这个异常并进行处理，事务管理器会认为当前事务应该正常commit，就会导致注解失效，如果非要捕获且不失效，就必须在代码块内throw new Exception抛出异常。

5.数据库引擎不支持事务

开启事务的前提就是需要数据库的支持，我们一般使用的Mysql引擎时支持事务的，所以一般不会出现这种问题。

6.开启多线程任务时，事务管理会受到影响

因为线程不属于spring托管，故线程不能够默认使用spring的事务,也不能获取spring注入的bean在被spring声明式事务管理的方法内开启多线程，多线程内的方法不被事务控制。
如下代码，线程内调用insert方法，spring不会把insert方法加入事务就算在insert方法上加入@Transactional注解，也不起作用。

Isolation.READ_UNCOMMITTED  : 读取未提交数据(会出现脏读, 不可重复读) 基本不使用

Isolation.READ_COMMITTED  : 读取已提交数据(会出现不可重复读和幻读)

Isolation.REPEATABLE_READ：可重复读(会出现幻读)
Isolation.SERIALIZABLE：串行化

JPA

  通过 @Modifying 注解可以完成修改(UPDATE或者DELETE)操作（注意：不支持新增）

@Query(value = "select * from tab_wechat_relation_staff t where t.corpId = :corpId and t.wechatId = :wechatId " +
            "order by createdAt desc limit 1", nativeQuery = true)
    WechatRelationStaff findByCorpIdAndWechatId(@Param("corpId") String corpId, @Param("wechatId") String wechatId);

注意更新和删除必须添加@Modifying
nativeQuery表示用原生的sql语句查询  
注意这种形式t.corpId = :corpId   需要对应到@Param("corpId") String corpId 

索引

一句话简单来说，索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

在innoDB引擎中,索引分为聚集索引,二级索引

聚集索引必须有,而且只有一个

二级索引可以存在多个

聚集索引得选取规则

1.如果存在主键,主键索引就是聚集索引

2.如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引

3.如果表没有主键,或没有合适得唯一索引,则innoDB会自动生成一个rowid作为隐藏得聚集索引

聚集索引和二级索引都是采用B+树得数据结构,不过给B+树加了一个指针,原本得单向链表变为双向链表.  

聚集索引得叶子结点存放的是整行数据, 二级索引得叶子结点存放得是该二级索引数据对应得聚集索引得数据     查询全部 如果是二级索引  会先通过二级索引找到这个叶子结点得聚集索引数据  再通过该聚集索引查询数据

如下图上面是聚集索引  下面是二级索引

 创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...);

主键不需要创建索引, 系统会自动给他创建一个唯一索引

查看索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX index_name ON table_name;

SQL性能分析

1.sql执行频率

show [session|global] status   可以查看当前数据库得insert  update  delete  select的访问频次

SHOW GLOBAL STATUS LIKE 'Com_______';

2.慢查询日志

show variables like 'slow_query_log';

配置/etc/my.cnf

slow_query_log =1

long_query_time = 2  默认10

tail -f localhost-slow.log  实时查询慢日志尾部

3.profile详情

show profiles  能够在做sql优化时帮助我们了解时间都耗费在哪里了 通过have_profiling参数, 能够看到当前Mysql是否支持profile操作;

select @@have_profiling;   查看支不支持 

select @@ profiling   查看有没有开启   

SET profiling = 1;    就开启了

show profiles;  查看每一条sql的耗时情况

show profile for query query_id;  查看指定query_id的sql语句各个阶段的耗时情况

show profile cpu for query query_id;  查看指定query_id的sql语句cpu的使用情况

4.explain

EXPLAIN或者DESC命令获取mysql如何执行select语句的信息, 包括在select语句执行过程中表如何连接和连接的顺序

语法:

直接在select语句之前加上关键字explain/desc

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

explain执行计划

EXPLAIN执行计划各字段含义

id:

select查询的序列号, 表示查询中执行select子句或者时操作表的顺序(id相同,执行顺序从上到下;id不同,值越大,越先执行)

select_type:

表示select的类型,常见的取值有SIMPLE(简单表,即不使用表连接或者子查询)  PRIMARY(主查询,即外层的查询) UNION(UNION中的第二个或者后面的查询语句) SUBQUERY(SELECT/WHERE之后包含了子查询)等.

type:

表示连接的类型,性能有好到差的连接类型为 NULL,system,const,eq_ref,ref,range,index,all

possible_key

显示可能应用在这张表上的索引, 一个或多个

key:

实际使用的索引  如果为null 就是没有使用索引

key_len:

表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,再不损失精确性的前提下,长度越短越好.

rows:

执行查询的行数 预估值 可能不准

filtered:

表示返回结果的行数占需读取行数的百分比,filtered的值越大越好

索引使用

1.最左前缀法则

如果索引了多列(联合索引),要遵守最左前缀法则.  最左前缀法则指的是查询从索引得最左列开始,并且不跳过索引中得列.

如果跳过某一列,索引将部分失效(后面得字段索引失效)   

最左前缀法则不是必须在左边 而是有最左边得就行 

例如索引  index_companyId_name_staus

select * from table where companyId = ? and name=? and status=? 和下面是一样得

select * from table where name=? and status=? and companyId = ?  都会走索引

2.范围查询

联合索引中,出现范围查询(>,<), 范围查询右侧得列索引失效

select * from tb_user where profession ='软件工程'  and age>30 and status = '0' ;

idx_pro_age_sta  索引会在status失效  因为前面是范围查询

>=  或 <= 不会影响     所以业务允许尽量使用>= / <=.

3.索引列运算

不要在索引列上进行运算操作, 否则索引将失效.

substring(s,10,2)  用函数  失效

4.字符串不加引号

不加引号 失效

5.模糊查询

如果仅仅是尾部模糊匹配,索引不会失效,如果是头部模糊匹配,索引失效.

6.or连接的条件

用or分隔开的条件, 如果or前的条件中的列有索引, 而后面的列中没有索引, 那么涉及的索引都不会被用到.

7.数据分布影响

如果mysql评估使用索引比走全表扫描还慢,  那就不走索引

8.sql提示

SQL提示, 是优化数据库的一个重要手段, 简单来说, 就是在SQL语句中加入一些人为的提示来达到优化操作的目的, 

use index:

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

ignore index:

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

force index:

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

9.覆盖索引

尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到) ,减少select*

10.前缀索引

当字段类型为字符串(varchar,text等)时, 有时候需要索引很长的字符串, 这会让索引变得很大, 查询时, 浪费大量的磁盘IO,影响查询效率.此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间, 从而提高索引效率.

语法:

create index idx_xxx on table_name (column(n));   n代表要提前该字符串的前几个字符构建索引

前缀长度

可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的.

select count(distinct email)/count(*) from tb_user;

根据前缀找到聚集索引 在从该前缀中查看完整数据一不一样  一样就返回整行

11.单列索引与联合索引

单列索引,一个索引只包含单个列

联合索引,一个索引包含多个列

在业务场景中,建立使用联合索引,而非单列索引  如果使用得当 覆盖索引 是可以避免回表查询的