MySql教程

Mysql小结

本文主要是介绍Mysql小结,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

MySQL常用的存储引擎及区别

  • InnoDB

    InnoDB是MySQL的默认存储引擎,支持事务、行锁和外键等操作。

  • MyISAM

    MyISAM是MySQL5.1版本前的默认存储引擎,MyISAM的并发性比较差,不支持事务和外键等操
    作,默认的锁的粒度为表级锁。

InnoDB MyISAM
外键 支持 不支持
事务 支持 不支持
支持表锁和行锁 支持表锁
可恢复性 根据事务日志进行恢复 无事务日志
表结构 数据和索引是集中存储的,.ibd和.frm 数据和索引是分开存储的,数据 .MYD ,索引 .MYI
查询性能 一般情况相比于MyISAM较差 一般情况相比于InnoDB较快
索引 聚簇索引 非聚簇索引

索引

# 1.什么是索引
- 官方定义: 一种帮助mysql提升查询效率的数据结构
- 索引的优势:
		一、大大加快数据查询速度
- 索引的缺点:
		一、维护索引须要耗费数据库资源
		二、索引须要占用磁盘空间
		三、当对表的数据进行增删改的时候,由于要维护索引,速度会受到影响

# 2.索引分类
- InnoDB
- a.主键索引
		设定为主键后数据库会自动创建索引,不能为NULL,一个表只能有一个主键索引

- b.单值索引(普通索引|单列索引)
		即一个索引只包含单个列,一个表能够有多个单列索引  eg: id name index age index bir

- c.唯一索引   
		索引列的值必须唯一,但容许有空值	唯一索引索引列值可以存在Null,但是只能存在一个null

- d.复合索引
		即一个索引包含多个列		eg: id (name age) index bir

- MyISAM
- e.Full Text 全文索引 (My5.7版本以前 只能因为MYISAM引擎)
		全文索引类型为FULLTEXT,在定义索引的列上支持值的全文查找,容许在这些索引列中插入重复值和空值。全文索引能够在CHAR、VARCHAR、EXT类型列上建立。MYSQL只有MYISAM存储引擎支持全文索引

# 3.索引的基本操做
1.主键索引 自动建立

--建表 主键自动建立主键索引
create table t_user(id varchar(20) primary key,name varchar(20));
--查看索引
show index from t_user;

image-20210907193115782

2.单列索引(普通索引|单值索引)
--建表时建立
create table t_user(id varchar(20) primary key,name varchar(20),key(name));  
	'注意:随表一块儿创建的索引索引名同列名一致'
	  
--建表后建立
create index nameindex on t_user(name);

--删除索引
drop index 索引名 on 表名

image-20210907195254943

3.唯一索引
--建表时建立
 create table t_user(id varchar(20) primary key,name varchar(20),unique(name));
 
--建表后建立
	create unique index nameindex on t_user(name);

image-20210907195448022

4.复合索引
---建表时建立
 create table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age));
 
--建表后建立
 create index nameageindex on t_user(name,age);
# 经典面试题
- #1.最左前缀原则 #2.mysql 引擎在查询为了更好地利用索引,在查询过程中会动态调整查询字段顺序以便利用索引
 name age bir 能否利用索引 √
 name bir age 能否利用索引 √
 name age 	  能否利用索引 √
 name bir 	  能否利用索引 √
 age bir	  能否利用索引 ×
 bir age name 能否利用索引 √
 bir age	  能否利用索引 ×

image-20210907195642896

# 4.索引的底层原理
1.思考
---建表
create table t_emp(id int primary key,name varchar(20),age int);

--插入数据
insert into t_emp values(5,'d',22);
insert into t_emp values(6,'d',22);
insert into t_emp values(7,'e',21);
insert into t_emp values(1,'a',23);
insert into t_emp values(2,'b',26);
insert into t_emp values(3,'c',27);
insert into t_emp values(4,'a',32);
insert into t_emp values(8,'f',53);
insert into t_emp values(9,'v',13);

--查询
select * from t_emp;

image-20210907195918423

# 5.为何上面数据明明没有按顺序插入,为何查询时倒是有顺序呢?
- 缘由是:mysql底层为主键自动建立索引,必定建立索引会进行排序
- 也就是mysql底层真正存储是这样的
- 为何要排序呢?由于排序以后在查询就相对比较快了 如查询 id=3的我只须要按照顺序找到3就行啦(若是没有排序大海捞针,全靠运气?)
# 6.为了进一步提升效率mysql索引又进行了优化
-  就是基于页的形式进行管理索引
-  如 查询id=4的 直接先比较页 先去页目录中找,再去 数据目录中找

image-20210907205400859

# 7.上面这种索引结构称之为B+树数据结构,那么什么是B+树呢?B树和B+树的区别?

B树和B+树最主要的区别主要有两点:

  • B树中的内部节点和叶子节点均存放键和值,而B+树的内部节点只有键没有值,叶子节点存放所有
    的键和值。

  • B+树的叶子节点是通过相连在一起的,方便顺序检索。
    两者的结构图如下。

    image-20210907205830345

image-20210907205843607

# 8.什么是聚簇索引,什么是非聚簇索引?

聚簇索引和非聚簇索引最主要的区别是数据和索引是否分开存储。

  • 聚簇索引:将数据和索引放到一起存储,索引结构的叶子节点保留了数据行。
  • 非聚簇索引:将数据进和索引分开存储,索引叶子节点存储的是指向数据行的地址。
# 9.使用聚簇索引的优点
- 问题: 每次使用辅助索引检索都要通过两次B+树查找,看上去聚簇索引的效率明显要低于非聚簇索引,这不是画蛇添足吗?聚簇索引的优点在哪?

- 1.因为行数据和聚簇索引的叶子节点存储在一块儿,同一页中会有多条行数据,访问同一数据页不一样行记录时,已经把页加载到了Buffer中(缓存器),再次访问时,会在内存中完成访问,没必要访问磁盘。这样主键和行数据是一块儿被载入内存的,找到叶子节点就能够马上将行数据返回了,若是按照主键Id来组织数据,得到数据更快。

- 2.辅助索引的叶子节点,存储主键值,而不是数据的存放地址。好处是当行数据放生变化时,索引树的节点也须要分裂变化;或者是咱们须要查找的数据,在上一次IO读写的缓存中没有,须要发生一次新的IO操做时,能够避免对辅助索引的维护工做,只须要维护聚簇索引树就行了。另外一个好处是,由于辅助索引存放的是主键值,减小了辅助索引占用的存储空间大小。
# 10.聚簇索引须要注意什么?
- 当使用主键为聚簇索引时,主键最好不要使用uuid,由于uuid的值太过离散,不适合排序且可能出线新增长记录的uuid,会插入在索引树中间的位置,致使索引树调整复杂度变大,消耗更多的时间和资源。
- 建议使用int类型的自增,方便排序而且默认会在索引树的末尾增长主键值,对索引树的结构影响最小。并且,主键值占用的存储空间越大,辅助索引中保存的主键值也会跟着变大,占用存储空间,也会影响到IO操做读取到的数据量。

# 11. 为何主键一般建议使用自增id
- 聚簇索引的数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据必定也是相邻地存放在磁盘上的。若是主键不是自增id,那么能够想象,它会干些什么,不断地调整数据的物理地址、分页,固然也有其余一些措施来减小这些操做,但却没法完全避免。但,若是是自增的,那就简单了,它只须要一页一页地写,索引结构相对紧凑,磁盘碎片少,效率也高。
# 12. 什么状况下没法利用索引呢?
- 1. 查询语句中使用LIKE关键字
			在查询语句中使用 LIKE 关键字进行查询时,若是匹配字符串的第一个字符为“%”,索引不会被使用。若是“%”不是在第一个位置,索引就会被使用。

- 2.查询语句中使用多列索引
			多列索引是在表的多个字段上建立一个索引,只有查询条件中使用了这些字段中的第一个字段,索引才会被使用。

- 3.查询语句中使用OR关键字
			查询语句只有OR关键字时,若是OR先后的两个条件的列都是索引,那么查询中将使用索引。若是OR先后有一个条件的列不是索引,那么查询中将不使用索引。

数据库事务

事务的四大特性是什么?

  • 原子性:原子性是指包含事务的操作要么全部执行成功,要么全部失败回滚。
  • 一致性:一致性指事务在执行前后状态是一致的。
  • 隔离性:一个事务所进行的修改在最终提交之前,对其他事务是不可见的。
  • 持久性:数据一旦提交,其所作的修改将永久地保存到数据库中。

数据库的并发一致性问题

当多个事务并发执行时,可能会出现以下问题:

  • 脏读:事务A更新了数据,但还没有提交,这时事务B读取到事务A更新后的数据,然后事务A回滚
    了,事务B读取到的数据就成为脏数据了。

  • 不可重复读:事务A对数据进行多次读取,事务B在事务A多次读取的过程中执行了更新操作并提交
    了,导致事务A多次读取到的数据并不一致。

  • 幻读:事务A在读取数据后,事务B向事务A读取的数据中插入了几条数据,事务A再次读取数据时
    发现多了几条数据,和之前读取的数据不一致。

  • 丢失修改:事务A和事务B都对同一个数据进行修改,事务A先修改,事务B随后修改,事务B的修改
    覆盖了事务A的修改。

不可重复度和幻读看起来比较像,它们主要的区别是:在不可重复读中,发现数据不一致主要是数据被更新了。在幻读中,发现数据不一致主要是数据增多或者减少了。

数据库的隔离级别有哪些?

  • 未提交读:一个事务在提交前,它的修改对其他事务也是可见的。
  • 提交读:一个事务提交之后,它的修改才能被其他事务看到。
  • 可重复读:在同一个事务中多次读取到的数据是一致的。
  • 串行化:需要加锁实现,会强制事务串行执行。

数据库的隔离级别分别可以解决数据库的脏读、不可重复读、幻读等问题。

隔离级别 脏读 不可重复读 幻读
未提交读 允许 允许 允许
提交读 不允许 允许 允许
可重复读 不允许 不允许 允许
串行化 不允许 不允许 不允许

MySQL的默认隔离级别是可重复读。

这篇关于Mysql小结的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!