mysql整理

mysql的索引总结：

索引类型：
    hash索引：
    BTree顺序索引：就是普通索引
    唯一索引
    full-text全文索引

    
主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
唯一性索引列允许空值，而主键列不允许为空值。
主键列在创建时，已经默认为非空值 + 唯一索引了。
主键可以被其他表引用为外键，而唯一索引不能。
一个表最多只能创建一个主键，但可以创建多个唯一索引。
主键和唯一索引都可以有多列。（复合主键）
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

    
    
索引不生效的情况：
    并不是建立了索引就在查询语句中生效。
当语句中带有or的时候，索引会失效
当索引的字段使用like查询，并且使用了前通配比如%Siam，索引失效。后通配会生效Siam%
当索引的字段是字符类型，但是储存的值是数字，比如 user_name:’123456’，在查询语句中要 user_name = ‘123456’而不能 user_name = 123456 否则发生类型转换，索引失效，其他类型的字段 比如日期等 也同理
当使用的条件语句，预计结果数量超过全表数据的一定比例时，会转为全表扫描（mysql一般是30%左右）这就是为什么在建立索引的时候要选择维度（区别度）比较高的列，性别这种字段不适合建立索引。
Not in 和<>操作无法使用索引

32. SQL优化顺序：
1）尽量少作计算。
2）尽量少 join。
3）尽量少排序。
4）尽量避免 select *，使用select *会导致索引失效
5）尽量用 join 代替子查询。
6）尽量少 or。
7）尽量用 union all 代替 union。union all不会去重加排序。
8）尽量早过滤。
9）避免类型转换。
10）优先优化高并发的 SQL，而不是执行频率低某些“大”SQL。
11）从全局出发优化，而不是片面调整。
12）尽可能对每一条运行在数据库中的SQL进行 Explain。

explain 分析sql语句
select_type
select 查询的类型，主要是用于区别普通查询，联合查询，嵌套的复杂查询
simple：简单的select 查询，查询中不包含子查询或者union
primary：查询中若包含任何复杂的子查询，最外层查询则被标记为primary
subquery：在select或where 列表中包含了子查询
derived：在from列表中包含的子查询被标记为derived（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
union：若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为：derived
union result：从union表获取结果的select
partitions
表所使用的分区，如果要统计十年公司订单的金额，可以把数据分为十个区，每一年代表一个区。这样可以大大的提高查询效率。
type
这是一个非常重要的参数，连接类型，常见的有：all , index , range , ref , eq_ref , const , system , null 八个级别。
性能从最优到最差的排序：system > const > eq_ref > ref > range > index > all
对java程序员来说，若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。
all：（full table scan）全表扫描无疑是最差，若是百万千万级数据量，全表扫描会非常慢。
index：（full index scan）全索引文件扫描比all好很多，毕竟从索引树中找数据，比从全表中找数据要快。
range：只检索给定范围的行，使用索引来匹配行。范围缩小了，当然比全表扫描和全索引文件扫描要快。sql语句中一般会有between，in，>，< 等查询。
ref：非唯一性索引扫描，本质上也是一种索引访问，返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事，匹配的结果是多个并非唯一值。
eq_ref：唯一性索引扫描，对于每个索引键，表中有一条记录与之匹配。比如查询公司的CEO，匹配的结果只可能是一条记录，
const：表示通过索引一次就可以找到，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快，若将主键至于where列表中，MySQL就能将该查询转换为一个常量。
system：表只有一条记录（等于系统表），这是const类型的特列，平时不会出现，了解即可
possible_keys
显示查询语句可能用到的索引(一个或多个或为null)，不一定被查询实际使用。仅供参考使用。
key
显示查询语句实际使用的索引。若为null，则表示没有使用索引。
key_len
显示索引中使用的字节数，可通过key_len计算查询中使用的索引长度。在不损失精确性的情况下索引长度越短越好。key_len 显示的值为索引字段的最可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。
ref
显示索引的哪一列或常量被用于查找索引列上的值。
rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，值越大越不好。
extra
Using filesort： 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
Using temporary： 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。 出现这个更要立刻优化sql。
Using index： 表示相应的select 操作中使用了覆盖索引（Covering index），避免访问了表的数据行，效果不错！如果同时出现Using where，表明索引被用来执行索引键值的查找。如果没有同时出现Using where，表示索引用来读取数据而非执行查找动作。
覆盖索引（Covering Index） ：也叫索引覆盖，就是select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select 列表中的字段，而不必根据索引再次读取数据文件。
Using index condition： 在5.6版本后加入的新特性，优化器会在索引存在的情况下，通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。
Using where： 表明使用了where 过滤
Using join buffer： 表明使用了连接缓存
impossible where： where 语句的值总是false，不可用，不能用来获取任何元素
distinct： 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

全文索引：fulltext  create fulltext index 索引名称 on 表（列名,列名）     创建已有表的索引
          drop index 索引名称 on 表;   删除全局索引
          只能创建在vachar和test类型上面。
          全文索引就相当于es，只不过需要自己创建分词，所以真有这种大文本的类型，还是使用es来存储。
          

muysql的组成部分：
1. 连接池组件
  2. 管理服务 和 工具组件
  3. SQL接口组件
  4. 查询分析器组件
  5. 优化组件
  6. 缓冲组件
  7. 插件式存储引擎
  8. 物理文件

事务的4个特性：（1）原子性：操作这些指令时，要么全部执行成功，要么全部不执行。只要其中一个指令执行失败，所有的指令都执行失败，数据进行回滚，回到执行指令前的数据状态。
               （2）一致性：事务的执行使数据从一个状态转换为另一个状态，但是对于整个数据的完整性保持稳定。中间状态不可见，只能看见数据初始状态和结尾状态。
               （3）持久性：当事务正确完成后，它对于数据的改变是永久性的。
               （4）隔离性：隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
               
               注意：原子性关注状态，要么全部成功，要么全部失败，不存在部分成功的状态。而一致性关注数据的可见性，中间状态的数据对外部不可见，只有最初状态和最终状态的数据对外可见

并发事务导致的问题：
                    （1）脏读：指在一个事务处理过程里读取了另一个未提交的事务中的数据。
                    （2）幻读也叫虚读：第二次查询与第一次查询的数据不一致，比如某些已经删除了的数据依旧出现
                    （3）不可重复读：一个事务两次读取同一行的数据，结果得到不同状态的结果，中间正好另一个事务更新了该数据，两次结果相异，不可被信任。（同一条数据两次读取返回的状态不一致，中间被其他给修改了。

数据库事务的隔离级别：read uncommitted（读未提交） 这是最低级的，任何情况都无法保障，导致脏读、幻读、不可重复读
                      read committed（读已提交） 只能读取已提交的数据。避免了脏读，但是会出现幻读和不可重复读
                      repeated reads  （重复读） 在开始读取数据（事务开启）时，不再允许修改操作。 就是开启事务之后，不能再对该事务的数据进行修改。避免了脏读、不可重复读，但是避免不了幻读，比如用户a插入了一条
                                                 数据，用户b去查询用户a的所有插入数据，当b第一次查询完成之后，a又插入了一条数据，b去再次查询的时候，就发现结果不一致，这就是幻读。
                      serializable（序列化） 最高级别的事务，一般不用。
                      事务的传播行为，@Transaction（value= "PROPAGATION_REQUIRED"）不填，一般默认第一个，支持当前事务，如果不存在，就新创建一个
                    

for update ：select * from 表 where 列名='',列名=''... FOR UPDATE;      行锁：
                                                                            （1）当列名里面包含了主键或者唯一索引的时候，并且数值明确，则是行锁
                                                                        表锁：
                                                                            （1）当列名没有包含主键或者唯一索引的时候，但是能查询到该值，则是表锁
                                                                            （2）明确主键，但是没有具体值，也是表锁
                                                                        无锁：列名查询出来数据没值
                                                                        
innodb才有行锁，myisam只有表锁
innodb与myisam区别：
（1）InnoDB支持事务，MyISAM不支持
（2）InnoDB支持外键，而MyISAM不支持
（3）InnoDB是聚集索引，使用B+Tree作为索引结构，而MyISAM不支持
（4）InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
（5）InnoDB表必须有主键（用户没有指定的话会自己找或生产一个主键），而Myisam可以没有
（6）InnoDB支持外键，而MyISAM不支持
（7）InnoDB支持外键，而MyISAM不支持
（8）InnoDB支持外键，而MyISAM不支持
（9）Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
        Innodb：frm是表定义文件，ibd是数据文件
        Myisam：frm是表定义文件，myd是数据文件，myi是索引文件
                    
                    
innodb：存储引擎
        事务的控制，数据写入，里面比较复杂
        
锁升级(Lock Escalation)是指将当前锁的粒度降低。举个例子：数据库可以把一个表的1000个行锁升级为一个页锁，或者将页锁升级为表锁。

InnoDB存储引擎不存在锁升级的问题。因为其不是根据每个记录来产生行锁的，相反，其根据每个事务访问的每个页对锁进行管理，采用的是位图的方式。因此不管一个事务锁住页中一个记录还是多个记录，其开销通常都是一致的。

left join：把左表数据全部查询出来，右表条件符合的查询出来。
right join：把右表数据全部查询出来，左表条件符合的查询出来。
inner join：返回两张表中连接字段相等的行。
full join：返回两张表中条件符合的全部查询出来，相当于左连接+右连接
union：查询两张表中相同字段结果集，会排序和去重。
union all ：查询两张表中相同字段结果集，不会排序和去重。

关系型数据库设计的三范式：
                  （1）第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。
                        同一张表中一列不能有多个属性值，有就要分表。比如：课程这个字段有语文和数学，语文和数学的属性就不能在同一张表中，需要分表。（原子性）
                  （2）第二范式（2NF）是指数据库表的每个字段必须和主键有完全关联关系，不然会造成冗余数据。比如：住酒店的情况，人员的属性（年龄、国籍）这些和房间主键没有
                        直接关系，就不能在一张表中，主讲复合主键的情况
                  （3）第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。是第二范式的一个子集。

水平分区：将表分为多个表，每个表包含的列数相同，但是行数更少。创建相同表结构，把数据按照一定规律存在不同的表中。查询所有数据的时候需要union
垂直分区：把数据库不同列分到不同表中，如果一个表中某些列常用，而另外一些列不常用，那么可以采用垂直分割。 缺点：需要冗余列管理，查询所有数据需要join操作。
库表散列：与水平分区相似，但是没有明确的分割点，是利用hash算法，把数据分到不同的表中。

数据库账号创建规则：grant修改用户权限

mybatis：ORM（Object-relative-mapping）对象关系映射，内部封装了JDBC，让我们只需要关心sql语句本身。

Mybatis是否支持延迟加载？支持

mybtis一级缓存：一个SqlSession对象中创建一个本地缓存(local cache)，对于每一次查询，都会尝试根据查询的条件去本地缓存中查找是否在缓存中，如果在缓存中，
                就直接从缓存中取出，然后返回给用户；否则，从数据库读取数据，将查询结果存入缓存并返回给用户。
                

                一级缓存的生命周期：
                                (1)MyBatis在开启一个数据库会话时，会 创建一个新的SqlSession对象，SqlSession对象中会有一个新的Executor对象，Executor对
                                象中持有一个新的PerpetualCache对象；当会话结束时，SqlSession对象及其内部的Executor对象还有PerpetualCache对象也一并释放掉。
                                (2)如果SqlSession调用了close()方法，会释放掉一级缓存PerpetualCache对象，一级缓存将不可用
                                (3)如果SqlSession调用了clearCache()，会清空PerpetualCache对象中的数据，但是该对象仍可使用
                                (4)SqlSession中执行了任何一个update操作(update()、delete()、insert()) ，都会清空PerpetualCache对象的数据，但是该对象可以继续使用
                
SqlSession的一级缓存工作流程：
                            1.对于某个查询，根据statementId,params,rowBounds来构建一个key值，根据这个key值去缓存Cache中取出对应的key值存储的缓存结果；
                            2. 判断从Cache中根据特定的key值取的数据数据是否为空，即是否命中；
                            3. 如果命中，则直接将缓存结果返回；
                            4. 如果没命中：
                                4.1  去数据库中查询数据，得到查询结果；
                                4.2  将key和查询到的结果分别作为key,value对存储到Cache中；
                                4.3. 将查询结果返回；
                            5. 结束。

sqlsession失效的条件：
                    （1）sqlsession不同
                    （2）sqlsession相同，查询条件不同。
                    （3）sqlsession相同，两次相同查询条件中执行过增删改操作。
                    （4）sqlsession相同，手动清除了一次缓存。

    
一级缓存调用条件：
                1. 传入的 statementId 
                2. 查询时要求的结果集中的结果范围 （结果的范围通过rowBounds.offset和rowBounds.limit表示）；
                3. 这次查询所产生的最终要传递给JDBC java.sql.Preparedstatement的Sql语句字符串（boundSql.getSql() ）
                4. 传递给java.sql.Statement要设置的参数值
                
                
                
                
                
mysql的几种锁机制：
    1.表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

    2.行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

    3.页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。        
                
                
mysql一共有5种引擎：
共有5种类型的表格：MyISAM、Heap、Merge、INNODB、ISAM

两种存储引擎有什么区别：innodb和myisam
MyISAM：

不支持事务，但是每次查询都是原子的；

支持表级锁，即每次操作是对整个表加锁；

存储表的总行数；

一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；

采用非聚集索引，索引文件的数据域存储指向数据文件的指针。

索引基本一致，但是辅索引不用保证唯一性。

InnoDb：

支持ACID的事务，支持事务的四种隔离级别；

支持行级锁及外键约束：因此可以支持写并发；

不存储总行数；

一个InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制；

主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。                
                
                
Java事务的类型有三种：JDBC事务、JTA（Java Transaction API）事务、容器事务。    
                    JDBC优缺点：1.冗长、重复     2.显示事务控制     3.每个步骤不可获取    4.显示处理受检查异常
                    JTA(Java Transaction API)提供了跨数据库连接（或其他JTA资源）的事务管理能力。JTA事务管理则由JTA容器实现，J2ee框架中事务管理器与应用程序，资源管理器，以及应用服务器之间的事务通讯。
                    JTA的优点很明显，就是提供了分布式事务的解决方案，严格的ACID。但是，标准的JTA方式的事务管理在日常开发中并不常用。
                    JTA的缺点是实现复杂，通常情况下，JTA UserTransaction需要从JNDI获取。这意味着，如果我们使用JTA，就需要同时使用JTA和JNDI。
                    JTA本身就是个笨重的API，通常JTA只能在应用服务器环境下使用，因此使用JTA会限制代码的复用性。
                    spring容器的事务

    
                
@Transactional只能被应用到public方法上,对于其它非public的方法,如果标记了@Transactional也不会报错,但方法没有事务功能.
默认情况下,一个有事务方法, 遇到RuntimeException 时会回滚 . 遇到 受检查的异常 是不会回滚 的. 要想所有异常都回滚,要加上 @Transactional( rollbackFor={Exception.class,其它异常}) 
            
事务的传播行为：
REQUIRED：没有事务就新建一个，有的话就用原来那一个    -----默认就是他
SUPPORTS
MANDATORY
REQUIRES_NEW：    没有事务就新建一个，有的话就把原来那一个挂起     
NOT_SUPPORTED
NEVER
NESTED
        

（1）@Transactional是来自org.springframework.transaction.annotation包的。

（2）@Transactional不仅可以注解在方法上，也可以注解在类上。当注解在类上时，意味着此类的所有public方法都是开启事务的。
    如果类级别和方法级别同时使用了@Transactional注解，则使用在类级别的注解会重载方法级别的注解。

                
grant all privileges on *.* to 'mysql'@'%' identified by 'mysql123';   这是新增一个用户，并且赋予权限和密码，也适用于root

scp -r  /home/helpteach/project/mallupload/1509681299449.png wasadmin@10.127.40.25:/home/test/test.png    这里的-r是如果是文件夹的情况，就需要

 

/home/helpteach/project/mallupload/1509681299449.png：要传输的文件

wasadmin：目标服务器ssh账号名

@后面：目标服务器的IP地址

：后面：要传输到目标服务器的文件保存目录

delete、drop、truncate区别
truncate 和 delete只删除数据，不删除表结构 ,drop删除表结构，并且释放所占的空间。
删除数据的速度，drop> truncate > delete
delete属于DML语言，需要事务管理，commit之后才能生效。drop和truncate属于DDL语言，操作立刻生效，不可回滚。
使用场合：
当你不再需要该表时， 用 drop;
当你仍要保留该表，但要删除所有记录时， 用 truncate;
当你要删除部分记录时（always with a where clause), 用 delete.
 
注意： 对于有主外键关系的表，不能使用truncate而应该使用不带where子句的delete语句，由于truncate不记录在日志中，不能够激活触发器

DML与DDL区别，都是数据库语言，第一个DML有事务控制，第二个DDL没有

sql语句分类：
DDL：数据定义语言（create drop）
DML：数据操作语句（insert update delete）
DQL：数据查询语句（select ）
DCL：数据控制语句，进行授权和权限回收（grant revoke）
TPL：数据事务语句（commit collback savapoint）