文章目录
MySQL数据库是面试的必考点,所以了解MySQL的相关知识非常有必要,下面整理收纳一些MySQL的必考点:引擎、索引、事务
引擎
引擎是什么(定义):数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务,从而满足企业内大多数需要处理大量数据的应用程序的要求。 使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象(如索引、视图和存储过程)。
为什么要选择不同的引擎:MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能。
简单来说,就是不同的引擎有不同的功能以及作用,要根据实际情况选择数据库引擎
MySQL引擎的分类
MySQL的引擎有很多种,最常见的是InnoDB、MyISAM,还有一些不常见的例如MEMORY、CSV等等,可以在MySQL中使用show engines;命令来查看支持的引擎。
1)InnoDB
InnoDB是MySQL的默认的事务性引擎,也是目前使用最广泛,最普遍的引擎,它支持事务操作,行级锁,以及外键约束
InnoDB提供了标准的ACID的事务级别,提供了回滚,恢复的事务安全,以及实现了四种SQL级的事务隔离级别(后面会讲到这四种隔离级别),该引擎还提供了行级锁、外键约束以及自动的灾难恢复,它没有保存表的行数,所以在使用SELECT COUNT(*) FROM TABLE 时,需要扫描全表。但如果要使用事务管理,InnoDB是最好的选择,同时它使用的是行级锁,粒度更小,写操作不会锁全表,所以在多线程并发时,使用InnoDB能具有更高的效率。
什么时候使用InnoDB:
1.在需要使用事务时
2.经常更新的表,适合处理多重并发的更新请求
3.可以从灾难中恢复
4.需要使用外键时
5.支持auto_increment主键自增
2) MyISAM
在MySQL5.1以前,MyISAM是MySQL的默认引擎,MyISAM具有很多核心态,例如全文索引,压缩、空间函数。但是MyISAM不支持外键以及行级锁以及事务,虽然后来的版本支持了事务,但是大部分人眼中还是不支持事务的。并且MyISAM没有崩溃恢复的功能,在崩溃时数据会丢失。
因为MyISAM不支持行锁,所以在进行INSERT和UPDATE时(即写操作),会锁定整张表,当有海量数据写入的时候,MyISAM的效率会变得很低。但是MyISAM也不是一无是处,它的访问速度很快,在一些只读数据或者只有少量写数据的情况下,它的速度会很快。
索引
索引的定义:索引(index)是帮助MySQL高效获取数据的一种数据结构。所以索引的本质是一种数据结构。索引的目的在于提高查询效率,类似于字典、目录等。
可简单理解成“排好序的快速查找数据结构”,一般来说,索引文件比较大,所以不能存储在内存中,一般以文件形式存储到磁盘上。
一般来说,如果没有特别指明,索引都指的是B树索引。其中聚集索引、次要索引、覆盖索引、前缀索引、唯一索引默认都是用B树。通过show index from tablename可以查看表的索引情况。
索引类型
- 普通索引:没有任何限制的索引,可以在任何字段上建立,字段本身的限制条件可以判断其是否为空或唯一。
- 唯一索引:使用UNIQUE可以设置唯一索引,创建该索引时,索引的值必须唯一。主键是一种特殊的唯一索引
- 全文索引:使用FULLTEXT参数可以设置全文索引,全文索引只可以建立在CHAR、VARCHAR、TEXT字段上,查询较大的字符串类型字段时,使用该索引可以提高查询速度。该索引对大小写不敏感,在MySQL中只有MyISAM引擎支持该索引
- 单列索引:顾名思义,一个索引值对应一个字段,上述三种索引都可以是单列索引,只要一个索引值对应一个字段即可。
- 多列索引:一个索引值对应多个字段。该索引需要在创建时指定多个字段,可以通过这几个字段进行查询。在使用该索引进行查询时,索引对应的第一个字段必须要用到,否则会无效。
- 空间索引:使用SPATIAL参数可以设置控件索引。控件索引只能建立在控件数据类型(LINESTRING、POINT、GEOMETRY等)上,这样可以提高系统获取控件数据的效率。MySQL中只有MyISAM存储引擎支持空间索引,且该字段不能为空值。
索引结构:
- B-TREE索引 (默认)
- HASH索引
- FULLTEXT索引
- R-TREE索引
如何建立索引
- 建立主键索引:ALTER TABLE
table_name
ADD PRIMARY KEYcolumn_name
; - 建立唯一索引:ALTER TABLE
table_name
ADD UNIQUEcolumn_name
; - 建立全文索引:ALTER TABLE
table_name
ADD FULLTEXTcolumn_name
; - 建立单列索引:ALTER TABLE
table_name
ADD indexindex_name
column_name
; - 建立多列索引:ALTER TABLE
table_name
ADD indexindex_name
(column_name1
,column_name2
,…); - 查看索引:show index from
tablename
何时建立索引
①建立索引的情况
- 主键自动创建主键索引
- 频繁作为查询的字段
- 查询中与与其他表关联的字段,外键建立索引
- 查询中排序的字段,如果字段排序了,用索引将会提高效率
- 高并发下趋向使用组合索引
- 查询中统计或分组字段
- 尽量选择唯一性索引
- 适合索引的列是WHERE查询子句的列,或者是连接子句中指定的列
②不建立索引的情况
- 表记录较少(MySQL自己可以处理,不需要索引,反而会影响效率)
- 经常增删改的表(在增删改的时候,索引文件同时需要更改,影响效率)
- 数据重复且平均分配的字段,例如国籍,性别等,不适合创建索引
- 频繁更新的字段
- WHERE里用不到的字段不适合创建索引,因为在使用WHERE时,用不到的字段会导致索引失效。
索引优化
索引优化的目的是为了让索引不失效
SQL关键字:Explain
explain(执行计划),使用explain关键字可以模拟优化器执行sql查询语句,从而知道MySQL是如何处理sql语句。explain主要用于分析查询语句或表结构的性能瓶颈。
通过explain+SQL语句可以得到以下信息:
①:表的读取顺序(对应id
)
②:数据读取操作的操作类型(对应select_type
)
③:SQL操作的好与坏(对应type
)
④:哪些索引可以使用(对应possible_keys
)
⑤:哪些索引可以被使用(对应key
)
⑥:表的直接引用(对应ref
)
⑦:每张表有多少行被优化器查询(对应rows
)
1、id
id是表的读取顺序,select查询的序列号。
id相同,读取顺序从上到下,id不同,值越大,优先级越高。
2、select_type
查询的类型,主要用于区别普通查询,联合查询,子查询等复杂的查询,其值有六个:
- SIMPLE:简单的查询,其中不包含子查询或union查询
- PRIMARY:查询中若包含复杂的子部分,最外层查询为PRIMARY,也就是最后加载的是PRIMARY
- SUBQUERY:在select或where中包含了子查询,就会被标记为SUBQUERY
- DERIVED:在from列表中包含的子查询会被标记为DERIVED,MySQL会递归执行这些子查询,将结果保存在临时表中。
- UNION:若第二个select出现在union后,则被标记为UNION,若union包含在from子句的子查询中,外层select将被标记为DERIVED。
- UNION RESULT
3、table:显示SQL操作是属于哪张表的
4、type:该值表示SQL查询语句的好坏,从最好到最坏依次是:
system>const>eq_ref>ref(非唯一性全表扫描)>range(范围扫描)>index(全索引扫描)>ALL(全表扫描)。
一般来说,最少需要保证到range级别,最好能达到ref
5、possible_keys和key、key_len
possible_keys:显示可能应用在表中的索引,可能是一个或多个。查询字段中包含的索引都会被列出,但不一定都会使用
key:实际中使用的所用,如NULL,则是未使用索引
key_len:表示索引中所使用的字节数,可通过该列计算查询中使用的索引长度。在不损失精确性的情况下,长度越短越好
。key_len显示的值为索引字段的最大可能长度
,并非实际使用长度
,即key_len是根据表定义计算而得,并不是通过表内检索出的
6、rows:根据表统计信息及索引选用情况大致估算出找到所需记录所要读取的行数。当然该值越小越好。
简单来说,possible_keys是可能使用的索引,而key是实际使用的索引
7、Extra:
这一项显示十分重要的额外信息额外信息,其值有几个,使用优先级从高到低:
Using index>Using filesort(九死一生)>Using temporary(十死无生)。
也就说出现后面两项表明sql语句是非常烂的,急需优化!!!
最佳左前缀法则
定义:在定义了多列索引的时候,查询从索引的最左前列开始且不能跳过中间的列。
最佳最前缀法则说明,如果创建了多列索引,使用索引时要根据创建索引的顺序来使用,不能缺少或跳过。当然只使用第一个索引是可以的,通俗理解:“带头大哥不能死,中间兄弟不能断
”。要点:“头不能掉
”。下面将用案例进行说明。
现在我们创建表tb_emp:
DROP TABLE IF EXISTS `tb_emp`;
CREATE TABLE `tb_emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` int(11) NOT NULL,
gender varchar(10) NOT NULL,
email varchar(20),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Tom', '22','male','1@qq.com');
INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Mary', '21','female','2@qq.com');
INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Jack', '27','male','3@qq.com');
INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Rose', '23','female','4@qq.com');
然后创建多列索引:
create index idx_nameagegender on tb_emp(name,age,gender);
现在我们有了火车头(name),火车车厢(age),火车尾(gender),现在我们来分别根据火车头查询,火车车厢查询,火车尾查询,火车头+火车车厢查询,火车头+火车尾查询
火车头(name)查询
我们看到 type=ref key_len=82,效果还不错
火车车厢(age)查询
我们发现type=all,直接变为了全表查询,效率低下,没有用到索引
火车尾(gender)查询
和火车车厢查询一样,type=all,全表查询,没用到索引
火车头(name)+火车车厢(age)查询
我们发现,type=ref,key_len=86,ref=const,const,效果还可以,可以看到ref=两个const,说明name和age的索引都起了作用。
火车头(name)+火车尾查询(age)
我们发现,type=ref,key_len=82,ref=const,可以看出来,只有火车头自己在跑,火车尾的索引我们没有用到
通过以上的例子,我们可以看出,火车头自己跑可以,没有火车头其他的车厢跑不了,火车头+和火车头连接的车厢可以一起跑,火车头+不连接的车厢只有火车头能跑。
火车头+火车车厢+火车尾一起跑
ref=const,const,const,变成了呜呜呜满载的小火车
最佳左前缀法则总结:带头大哥不能死,中间兄弟不能断;带头大哥可跑路,老二也可跟着跑,其余兄弟只能死
不在索引上做任何修改
在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效从而转向全表扫描。
1、
这里使用了函数,left(name,3),我们看见type=all,使用了函数从而变成了全盘扫描
2、
这里使用了 name=111,自动进行类型转换,从而变成了全盘扫描
范围右边全失效
我们之前已经看到,如果小火车一起跑,应该是这样子的
1、如果我们这样查询:name=‘Tom’ and age>22 and gender=‘male’
我们发现type=range,key_len=86,说明使用了部分索引,gender的索引没用上(使用key_len与上面对比)
结论:返回右边的索引列失效
尽量使用覆盖索引
尽量使用覆盖索引(如果对A,B列创建了索引,在查询时就不要使用*作为查询结果,而使用A,B列作为查询结果)
1、使用SELECT *
这是使用SELECT * 作为查询结果
2、使用SELECT name,age,gender
我们和使用SELECT * 对比,发现 Extra变成了Using index,提高了效率
使用is null 和 not is null会使索引失效
在使用is null的时候,索引完全失效,在使用not is null的时候,索引没失效但是type=all
like通配符以%开头会使索引失效
1、使用%Tom%
type=all,key=null,索引完全失效
2、使用%Tom
type=all,key=null,索引完全失效
3、使用Tom%
type=range,索引并没有完全失效
分析:在%在左边的时候,type=all,key=null,索引完全失效,全盘扫描,但是当%在右边的时候,type=range。但是在实际生产过程中,%在右边并不能满足我们的需求,这是我们应该使用覆盖索引
此时type=index,由全盘扫描变成了全索引扫描,提高了效率,也不错了。
总结
- 最佳左前缀法则:带头大哥不能死,中间兄弟不能断;带头大哥可跑路,老二也可跟着跑,其余兄弟只能死。
- 索引列上不计算。
- 覆盖索引记住用。
- is null、is not null导致索引失效。
- like百分加右边,加左边导致索引失效,解决方法:使用覆盖索引。
- 少用or,用or导致索引失效。
事务
事务是一组SQL语句,要么全部执行,要么全部不执行。通常一个事务对应一个完整的业务(例如银行转账业务)。
在MySQL中只有InnoDB才支持事务,MyISAM不支持事务
事务的四大特性(ACID)
1、原子性(atomicity):事务是最小的单位,不可再分。要么全部成功,要么失败回滚。
2、一致性(consistency):在事务开始到事务结束,数据库的完整性不能被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
3、隔离性(isolation):数据库可以允许多个事务同时对数据进行读取和修改,隔离性可以防止因事务交叉进行而导致的数据不一致。数据隔离分为四个级别,分别是读未提交(read uncommitted),读提交(read committed),可重复读(repeatable read),串行化(Serializable)。
4、持久性(durability):事务完成后,对事务的修改是永久的,即保存在硬盘上。
事务的四大隔离级别
事务并发可能出现的问题
在了解事务的四大隔离级别之前,我们先说说由于事务并行发生,会产生的三种现象:脏读、不可重复读、幻读。
我们有一个牛人数据库,里面记载了三个牛人的名字
1、脏读:
今天,事务A访问数据库,他往数据库中加了一个牛人的名字,但是还没有提交
INSERT INTO T VALUES (4, ‘牛D’);
这个时候,事务B也访问数据库,他查询了所有的牛人记录:
SELECT name FROM T;
事务B发现读取到了牛D,可是事务A还没有提交事务!
在这个情景中,事务B读取到了还未提交的事务
,这个就是脏读。
2、不可重复读:
今天,事务A访问数据库,想查询id为1的牛人的名字(A事务开启,进行查询)
SELECT name FROM T WHERE id=1; (查询结果:牛A)
这个时候,因为牛A改名了,所以事务B来数据库中更新一下牛A的名字(B事务开启,进行update)
UPDATE T SET name=‘不牛A’ WHERE id=1; (将牛A的名字改成不牛A)(事务B结束)
这时候,事务A再次查询了一下牛A的名字(第二次查询)
SELECT name FROM T WHERE id=1;(查询结果:不牛A)(事务A结束)
在这个情景中,事务A两次查询的结果不相同
,这就是不可重复读
3、幻读:
今天,事务A访问数据库,查了一下所有牛人的名字(A事务开始,进行查询)
SELECT * FROM T; (查到了三个牛人的名字)
这个时候,事务B往数据库里新增了一条记录 (事务B开始,进行insert)
INSERT INTO T VALUES (4, ‘牛D’); (事务B结束)
现在事务A忘了刚刚查询的结果,于是又查了一次 (A事务第二次查询)
SELECT * FROM T;(查到了四个牛人的名字)(事务A结束)
这时候事务A就懵逼了,我刚刚还差的是三个人,现在怎么就变成四个人了???这就是幻读
我们发现:
- 脏读是因为SELECT没有守规矩
- 不可重复读是UPDATE没有守规矩
- 幻读是INSERT DELETE没有守规矩
事务的四大隔离级别
一般的数据库,都包含四种隔离级别:
- 读未提交(read uncommitted)
- 读提交(read committed)
- 可重复读(repeatable read)
- 串行化(Serializable)
1、读未提交(read uncommitted)
读未提交,顾名思义,就是可以读到未提交的内容。
因此,在这种隔离级别下,查询是不会加锁的,也由于查询的不加锁,所以这种隔离级别的一致性是最差的,可能会产生“脏读”、“不可重复读”、“幻读”。
如无特殊情况,基本是不会使用这种隔离级别的。
2、读提交(read committed)
读提交,顾名思义,就是只能读到已经提交了的内容。
这是各种系统中最常用的一种隔离级别,也是SQL Server和Oracle的默认隔离级别。这种隔离级别能够有效的避免脏读,但是不能避免“不可重复读”、“幻读”。
3、可重复读(repeatable read)
可重复读,顾名思义,就是专门针对“不可重复读”这种情况而制定的隔离级别,自然,它就可以有效的避免“不可重复读”。而它也是MySql的默认隔离级别。
在这个级别下,普通的查询同样是使用的“快照读”,但是,和“读提交”不同的是,当事务启动时,就不允许进行“修改操作(Update)”了,而“不可重复读”恰恰是因为两次读取之间进行了数据的修改,因此,“可重复读”能够有效的避免“不可重复读”,但却避免不了“幻读”,因为幻读是由于“插入或者删除操作(Insert or Delete)”而产生的。
4、串行化(Serializable)
这是数据库最高的隔离级别,这种级别下,事务“串行化顺序执行”,也就是一个一个排队执行。
这种级别下,“脏读”、“不可重复读”、“幻读”都可以被避免,但是执行效率奇差,性能开销也最大,所以基本没人会用。
总结:
- 读未提交可以预防什么:什么都预防不了
- 读提交可以预防什么:可以预防脏读,不可以预防不可重复读、幻读
- 可重复读可以预防什么:可以预防脏读、不可重复读,不可以预防幻读
- 串行化可以预防什么:都可以预防,但是执行效率差,性能低,很少用