一.MySQL架构

连接层:

        处理客户端连接服务,认证授权相关的操作

服务层:

        最核心的一层（核心服务功能）,处理sql,包括sql优化,函数调用....

存储引擎层:

        存储引擎是真正负责来操作数据的（mysql中数据的存储和提取）, mysql中有不同存储引擎,不同的引擎处理技术不同

物理文件层:

       存在电脑硬盘上的表数据,日志文件，负责与存储引擎的数据交互

二.存储引擎

        存储引擎是真正负责来操作数据的,不同的引擎处理技术不同

                 (支持事务,行级锁,外键约束,索引技术)

查看支持的引擎

       SHOWENGINES;

查看表引擎

       SHOWTABLE STATUS LIKE '表名'

修改引擎

       方式1:将mysql.ini 中default-storage-engine=InnoDB，重启服务.

       方式2:建表时指定 CREATETABLE 表名(...)ENGINE=MYISAM;

       方式3:建表后修改 ALTERTABLE 表名 ENGINE=INNODB;

存储引擎主要有：

             1.MyIsam,2.InnoDB,3.Memory,4.Blackhole,5.CSV,6. Performance_Schema, 7. Archive, 8. Federated , 9 Mrg_Myisam

但在实际开发中,需要根据具体的操作,来为表选择合适的引擎

mysql中常用的两种引擎:用MyIsam 和InnoDB

两者的区别：

      innodb:支持事务,有行级锁,外键约束

      myisam: 不支持事务,不支持行锁,支持表锁,不支持外键约束

                   （因此当INSERT或UPDATE数据时即写操作需要锁定整个表）

eg:

   新增,修改,删除操作比较多的表,建议使用innodb引擎

   查询多的表(例如每月话费账单表),只插入一次数据,后面都是查询操作,建议使用myIsam引擎.

注：由于现在表数据量比较大,动辄需要对数据库进行拆分,使用存储过程,视图,自定义函数这些内容移植性差,所以不建议使用了.

三.索引

1.为什么要用索引

      表中存储着大量数据,如果没有索引,当我们需要查询一条数据时,就要从第一条数据开始查询,直到找到我们需要查询的数据,这么查找效率太低,太耗时间和内存,所以需要索引

2.索引的优/缺点

优点:

     ①减少了查询次数, 降低IO成本(与硬盘交互次数),提高了数据检索的效率

     ②索引是已经排好序了的,通过索引给数据排序提高排序效率,减少来cpu的消耗

缺点:

     ①索引的本质是一张表保存了主键与索引字段,并指向实体表的记录,它也是占磁盘空间

     ②索引提高了查询速度,但也降低更新表的速度,每次更新段,都要调整索引信息

  注：数据变化之后索引结构也是需要发生改变, 所以添加索引也是需要有规则的.

3.索引的原理

      索引类似于书的目录,通过目录可以快速的定位到数据的物理位置.

4.什么是索引

      索引是帮助mysql高效获取数据的排好序的快速查找的数据结构

      在向数据库中插入数据时,mysql自动会为主键创建一个索引树,通过主键查询时,先在主键索引树上查询,可以提高查询效率,找到主键后,就可以快速定位到数据.

5.索引分类

①主键索引

   一个表中只能有一个主键, primary key

创建主键索引：

ALTER TABLE 表名 add PRIMARY KEY 表名(列名);

//设定为主键后数据库会自动建立索引

删除主键索引：

ALTER TABLE 表名 drop PRIMARY KEY ;

②单值索引

一个索引只包含单个列，一个表可以有多个单列索引

创建单值索引 ：

CREATE INDEX 索引名 ON 表名(列名);

删除索引：

DROP INDEX 索引名;

③组合索引(符合索引)

一个索引中可以包含多个列(建议)

创建复合索引 ：

CREATE INDEX 索引名 ON 表名(列1,列2...);

删除索引：

DROP INDEX 索引名 ON 表名;

组合索引最左前缀原则

使用组合索引时,需要在查询条件中用到最左侧列,否则索引失效.

eg：      

       列如表中有a,b,c3列,为a,b两列创建组合索引,那么在使用时需要满足最左 侧索引原则.在使用组合索引的列作为条件时,必须要出现最左侧列为条件,否则 组合索引不生效.

          列如   select * from table where a=’’and b=’’     索引生效

                    select * from table where b=’’and a=’’     索引生效

                    select * from table where a=’’and c=’’     索引生效

                    select * from table where b=’’and c=’’     索引不生效

④全文索引

  在mysql中 like 模糊查询导致索引失效,这时就可以使用全文索引来解决这个问题

全局索引使用：

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名)WITH PARSER ngram;

SELECT 结果 FROM 表名 WHEREMATCH(列名)AGAINST(‘搜索词')

查看索引：

SHOWINDEX FROM 表名;

6.索引创建原则

①哪些情况建议添加索引

   主键自动建立唯一索引

   作为查询条件的字段应该创建索引(where 后面的语句)

   尽量使用联合索引，减少单列索引

   针对于数据量较大，且查询比较频繁的表建立索引。

   查询中排序的字段，分组中的字段,若通过索引去访问将大大提高排序速

②哪些情况不应该加索引

    表记录太少

    经常增删改的表

    Where条件里用不到的字段不创建索引

    数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数 据列建立索引，某个数据列包含许多重复的内容，建立索引没有太大实际效果

7.索引数据结构

mysql中索引数据结构使用的是B+树

B+树优点:

     一个节点中可以存储多个数据,

     非叶子节点中不存储表数据,只存储索引数据,这样一个节点中就可以存储更多索引数据

     数据都存储在叶子节点, 通过主键查询,找到了索引,就找打到了数据

     叶子节点之间也是建立了连接, 所以非常适合范围查询

8.回表查询

       在使用非主键索引查询数据时,例如通过学号查询数据,先在学号索引树查询,查询到之后,还需要去主键索引树查询,因为数据都挂载在主键索引树下,需要回表二次查询,实际开发中,尽量减少回表查询.

四.数据库事务

       数据库事务是数据对一次连接过程发送的多条sql执行进行管理,这多条sql要么都执行,要么都不执行.

       当一次连接过程中,所有操作都执行没有问题时,向数据库提交事务,数据库才真正的执行sql.

eg：

      转账: 对用户来说转账是一个整体的操作:包含减钱和加钱, 必须要保证这多条sql要么都成功执行要么都失败

sql1 从A账户减钱

//异常

sql2 向B账户加钱

提交事务

1.事务特性

       事务是必须满足4个条件：原子性（Atomicity，或称不可 分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

      原子性: 不可拆分 要么都执行,要么都不执行

      持久性: 事务一旦提交后,不能撤销,保证数据持久保存

      隔离性: 数据库运行多个事务同时对数据库数据进行操作,操作时给我们提供了4种隔离级别进行选择, 保证操作之间相互之间的关系

      一致性: 数据完整性 经过多次转账操作后, 最终的结果需要和我们预期结果是一致

2.事务隔离级别

查看隔离级别
SELECT @@session.transaction_isolation,@@transaction_isolation

①读 未提交:

      一个事务读到另一个事务还未提交的数据，这会带来脏读，幻读，不可重复读问题

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

②读 已提交:

      一个事务只能读到另一个事务提交后的数据，避免了脏读，仍然存在不可以重复读和幻读问题

     不可重复读问题: 在同 一个事务中,读取相同的数据两次,结果两次结果不一样

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

③可 重复读:

       在同 一个事务中,读取相同的数据两次,结果两次结果一样

       可重复读也称为快照读, 底层在第一次读取的时候,进行拍照, 在同一个事物中,第二次读的时候,直接读取快照,解决不可重复读问题,部分还存在幻读问题

    幻读问题: 在同同一个事物中,查询两次,两次的数量不一样

    可重复读隔离级别 对于普通的查询sql解决了幻读问题，对于查询语句后面添加了for update的语句,仍然存在幻读问题

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

④串行化:

   一次只能允许一个事务 操作(多个事务对同一条数据) 可以解决以上问题,但是效率低

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

五.mysql中的锁机制

1.全局锁

锁住整个数据库, 只允许查询操作

一般在备份数据库时使用.

添加全局锁：

FLUSH TABLES WITH READ LOCK

释放全局锁：

UNLOCK TABLES;

2.表锁

    锁着整表, myisam引擎默认只支持表锁, 一次只允许一个对该表事务进行操作.

3.行级锁

行锁 : 精确的锁定操作的那一行数据

         例如 update test set age = 20 where id = 1; 锁住id=1的这条记录

间隙锁: 锁定一个区间

         例如 update test set age = 20 where id >1 and id < 5 锁定了id=2,3,4的记录

行锁又可以分为:

共享锁:

一般给查询语句添加,

当一条查询语句添加了共享锁后, 允许其他事同时务读, 但是不允许其他事务为该条记录加排他锁.

排他锁:

新增,修改,删除默认加排他锁

查询语句也可以加排他锁, select ..... for update