目录
事务
事务的4大特性(ACID):
- A:atomic原子性:事务是最小的执行单位,不允许分割,要么全部成功,要么全部失败
- C:consistency一致性:执行事务前后,数据保持一致,多个事务对统一数据的读取应该是相同的
- I:Isolation独立性:并发访问数据库时,多个事务之间应该是独立的
- D:determined持久性:事务提交后造成的数据影响是永久的
事务的隔离级别:
- Read-Uncommitted(读未提交):最低的隔离级别,允许读取尚未提交的事务,会导致脏读、幻读、不可重复读。
- Read-Committed(读已提交):允许并发事务读取已经提交的数据,可以防止脏读,单有可能幻读、不可重复读。
- Repeatable-Read(可重复读):mysql的默认隔离级别,对同一字段的多次读取都是相同的,除非数据是被本身事务修改。可以防止脏读与不可重复读,但有可能会有幻读。
- Serializable(可串行化):最高的隔离级别,完全按照ACID,所有事务依次执行。
需要注意:InnoDB引擎在Repeatable-Read级别下使用的Next-Key Lock,所以可以在这个级别下防止幻读,起到了Serializable的效果。
InnoDB在分布式事务的情况下采用的是Serializable隔离级别。
并发事务带来的问题:
- 脏读:dirty read:一个事务修改来了数据未提交,另一个数据读取了未提交的数据。
- 丢失修改:lost to modify:两个事务都修改了数据之后,第一个数据修改就会丢失。如数据A=10,事务1:A=A-1;事务2:A=A-1,最后结果发现为19;
- 不可重复读:Unrepeatable Read:A事务在多次修改数据之间,B事务读取了数据,但是B在A事务执行之间读取的时候,可能会发现读取同一个数据的时候两次读到的不一样;
- 幻读:Phantom Read:幻读与不可重复读相同,但是是一个事务多次插入或者删除数据,另一个事务读取的时候会发现多了或者少了数据。
数据库的索引
最左前缀匹配原则
MySQL中的索引可以以一定顺序引用多列,这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city),而最左前缀原则指的是,如果查询的时候查询条件精确匹配索引的左边连续一列或几列,则此列就可以被用到。如下:
select * from user where name=xx and city=xx ; //可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引
如何添加索引
主键是索引的一种,是唯一索引的特殊类型。创建主键的时候,数据库默认会为主键创建一个唯一索引。
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `column` )
- INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
- 多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
索引的底层实现原理和优化
B+树,经过优化的B+树
主要是在所有的叶子结点中增加了指向下一个叶子节点的指针,因此InnoDB建议为大部分表使用默认自增的主键作为主索引。
什么情况下设置了索引但无法使用
① 以“%”开头的LIKE语句,模糊匹配
② OR语句前后没有同时使用索引
③ 数据类型出现隐式转化(如varchar不加单引号的话可能会自动转换为int型)
如何进行SQL优化?
(1)选择正确的存储引擎
(2)优化字段的数据类型
(3)为搜索字段添加索引
(4) 避免使用Select *从数据库里读出越多的数据,那么查询就会变得越慢。
(5) 使用 ENUM 而不是 VARCHAR
ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选项列表变得相当的完美。例如,性别、民族、部门和状态之类的这些字段的取值是有限而且固定的,那么,你应该使用 ENUM 而不是 VARCHAR。
(6) 尽可能的使用 NOT NULL
(7) 固定长度的表会更快
MyISAM和InnoDB区别
1、MyISAM是非事务安全的,而InnoDB是事务安全的
2、MyISAM锁的粒度是表级的,而InnoDB支持行级锁
3、MyISAM支持全文类型索引,而InnoDB不支持全文索引
4、MyISAM相对简单,效率上要优于InnoDB,小型应用可以考虑使用MyISAM
5、MyISAM表保存成文件形式,跨平台使用更加方便
6、MyISAM管理非事务表,提供高速存储和检索以及全文搜索能力,如果在应用中执行大量select操作可选择
7、InnoDB用于事务处理,具有ACID事务支持等特性,如果在应用中执行大量insert和update操作,可选择。、
表级锁和行级锁对比:
- 表级锁: Mysql中锁定 粒度最大 的一种锁,对当前操作的整张表加锁,实现简单,资源消耗也比较少,加锁快,不会出现死锁。其锁定粒度最大,触发锁冲突的概率最高,并发度最低,MyISAM和 InnoDB引擎都支持表级锁。
- 行级锁: Mysql中锁定 粒度最小 的一种锁,只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小,并发度高,但加锁的开销也最大,加锁慢,会出现死锁。
InnoDB存储引擎的锁的算法有三种:
- Record lock:单个行记录上的锁
- Gap lock:间隙锁,锁定一个范围,不包括记录本身
- Next-key lock:record+gap 锁定一个范围,包含记录本身
SQL语言包括哪几部分?每部分都有哪些操作关键字?
答:SQL语言包括数据定义(DDL)、数据操纵(DML),数据控制(DCL)和数据查询(DQL)四个部分。
数据定义:Create Table,Alter Table,Drop Table, Craete/Drop Index等
数据操纵:Select ,insert,update,delete,
数据控制:grant,revoke
数据查询:select
主键、外键和索引的区别?
定义:
主键–唯一标识一条记录,不能有重复的,不允许为空
外键–表的外键是另一表的主键, 外键可以有重复的, 可以是空值
索引–该字段没有重复值,但可以有一个空值
作用:
主键–用来保证数据完整性
外键–用来和其他表建立联系用的
索引–是提高查询排序的速度
个数:
主键–主键只能有一个
外键–一个表可以有多个外键
索引–一个表可以有多个唯一索引
对SQL语句优化有哪些方法?
(1)Where子句中:where表之间的连接必须写在其他Where条件之前,那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。
(2)用EXISTS替代IN、用NOT EXISTS替代NOT IN。
(3) 避免在索引列上使用计算
(4)避免在索引列上使用IS NULL和IS NOT NULL
(5)对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
(6)应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描
(7)应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描
聚集索引与非聚集索引的总结
聚集(clustered)索引,也叫聚簇索引。
定义:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。
非聚集(unclustered)索引。
定义:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。
聚集索引和非聚集索引区别:
- 聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个
- 聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续
- 聚集索引:物理存储按照索引排序;聚集索引是一种索引组织形式,索引的键值逻辑顺序决定了表数据行的物理存储顺序
- 非聚集索引:物理存储不按照索引排序;非聚集索引则就是普通索引了,仅仅只是对数据列创建相应的索引,不影响整个表的物理存储顺序.
- 索引是通过二叉树的数据结构来描述的,我们可以这么理解聚簇索引:索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块。