面试准备之mysql知识点介绍

目录

 

1.mysql索引

1.索引类型

2.索引的好处和坏处

4.为什么索引结构是B+树，而不是平衡树或者是B树或者是hash表

5.innerdb和myisam的索引

6.索引的好处和坏处

3.Mysql的锁

1.表锁和行锁

2.共享锁和排他锁

3.间隙锁

3.Mysql的事物

4.Mysql的优化

---

1.mysql索引

1.索引类型

聚集索引和辅助索引，辅助索引可以分为唯一索引，普通索引，全文索引

聚集索引和辅助索引的区别是聚集索引和数据存放的逻辑顺序是一致的，这样用聚集索引进行范围查找就会很快

聚集索引和唯一索引的区别就是

1.聚集索引和唯一索引的值都是唯一的，

2.一个表中的只有一个聚集索引，可以有多个唯一索引，

3.唯一索引的值可以是null，但是聚集索引的值不能是null，

4.聚集索引的顺序和数据存放的物理顺序是一致的，唯一索引索引顺序和数据存放的物理顺序有可能不一致。

普通索引：是在一个列或者多列数据上创建了索引，这些列的值可以重复也可以为null。

全文索引：全文索引有自己匹配方式，在mysql5.6以前，只有myisam支持全文索引，在5.6之后myisam和innerdb都支持全局索引。全局索引只支持的字段是char，varcha和text，而且全局索引有最小搜索长度的限制。

所用全局索引的查询的sql

select * from test where match (content) against( 'aaa’ );

覆盖索引：覆盖索引是当我们查询的出来的列都在同一个索引里面的时候就是覆盖索引，这样只需要查询索引文件就可以查询出想要的结果，不需要回表查询。

创建索引的sql：

创建聚集索引  alter table test add primary key('id');

创建唯一索引  alter table test add unique('phone');

创建普通索引  alter table test add index('name');

创建全文索引  alter table test add fulltext('content’);

创建多咧索引  alter table test add index(‘class','school’);

删除索引: alter table test drop index ’name’;

 

2.索引的好处和坏处

索引的好处：

1.加快查询，因为索引的数据结构可以帮我们还快查询速度。

2.可以保证数据的唯一性，如果是聚集索引或者是唯一索引，可以保证这个列的数据在整个表里面是唯一的。

3.索引可以加快表与表直接的连接。

4.使用分组和排序的时候如果用到了索引，可以减少分组和排序的时间

索引的坏处：

1.维护索引需要额外的开销，当对数据频繁进行增删改的时候，为了维护索引的结构，有可能需要对索引进行重新排序。

2.索引需要额外的存储空间来存储。

 

3.索引的数据结构

在myisam和innerdb里面，索引的数据接口是B+树，而memory里面是hash表

B树的特点是如果是n介B树的话

1.非叶子结点里面存储的都是关键字，叶子结点里面存储的是关键字和数据

2.所有叶子结点都在同一层。

3.左子树的关键字都小于节点里面的值，右子树里面的关键字都大于节点里面的值

如果是N阶B+树的话，非跟节点，一个节点里面最多有n-1个关键字，也就是最多有n个子树，最少有n/2-1个关键字，也就是最少有n/2个子树。

4.而且叶子结点是空的。

B+树和B树最大的不同就是

B+树的非叶子结点里面存储的只是关键字，而B树的结点里面存储的是关键字和数据，B树的叶子结点都是空的

B+树的叶子结点是一个双向链表结构

B+树的结点里面有几个关键字，那么就有几颗子树。

 

4.为什么索引结构是B+树，而不是平衡树或者是B树或者是hash表

       如果用hash表结构的话，如果是等值查询的话，使用hash表的索引确实要快一些，但是hash表不支持范围查询的，不能利用索引的来排序。hash表不支持最左前缀原则。hash表也不能支持模糊查询。hash表等值查询虽然要快一些，但是不稳定，因为当某些值大量重复的时候，会发生hash碰撞，这样会导致查询变慢。

       平衡树一个节点里面只能存储一个关键字，当表很大的时候，这个树的高度会很大，会造成多次I/O，而且平衡树不支持范围查找。为什么树的高度会和I/O次数有关，I/O从硬盘将数据读取到内存一次应该能读取很多数据，但是根据平衡树的左右子树选址的话，一次能确定获取的数据就是左子树结点或右子树结点，至于下面的结点那就只能看运气了，如果再想往下一层获取数据的话，可能又得根据左右子树的指针来I/O一次了。但是如果使用B+树作为索引结构,它的非叶子结点都存储的是关键字，所以一个节点能存放很多关键字，导致B+树的高度大大降低，它的I/O次数会明显减少。

       B树虽然一个节点能存储很多关键字，但是每个节点里面也会存储数据，这回导致一个节点里面存储关键字能力要小于B+树，所以B树的高度要大于B+树，而且B树不支持范围查找。

总结起来的话：使用B+树的好处 ：查找效率稳定   支持范围查找    IO次数更少

 

5.innerdb和myisam的索引

相同点

innerdb和myisam都是使用B+树来作为索引存储结构

不同点

innerdb里面的聚集索引和数据都是在同一个文件里面的，innerdb的聚集索引的B+树叶子结点里面存储的不仅有索引的关键字，还带有完整的数据。

myisam的聚集索引和数据是分离的，myisam的聚集索引的叶子节点里面存储的是索引关键字和这个数据的地址。

innerdb的辅助索引的叶子结点里面存储的是聚集索引的关键字。所以如果用innerdb的辅助索引查找的话会经过两次遍历B+树的经历。

myisamd的辅助索引的叶子结点里面存储的是这个数据的地址，所以myisam使用辅助索引来查找数据的话经过一次就能查找到，我想这也是为什么当查询过多的时候可以选择myisam存储引擎，因为如果用辅助索引的话只需要遍历一次就能查找到，减少了I/O次数。

 

6.索引的好处和坏处

好处

1.索引能加快查询速度。

2.索引能加快表与表的之间的连接。

3.索引能加快排序和分组。

4.聚集索引或者唯一索引能保证数据的唯一性。

坏处

1.当数据修改或者删除的时候需要维护索引，增加系统开销

2.索引会占用大量的存储空间

2.InnerDB和MyIsam的的不同点

1.InnerDB支持事务，MyIsam不支持事务。

2.InnerDB支持表锁和行锁，MyIsam只支持表锁。

3.InnerDB支持外健，MyIsam不支持外健。

4.InnerDB聚集索引和数据是在一起的，聚集索引叶子结点里面存储的是完整的数据，辅助索引叶子结点存储的是聚集索引的值。MyIsam聚集索引和非聚集索引的的叶子结点存储的都是一样的，是数据的记录地址。

5.Myisam存储的有表记录的总数，而InnerDB是没用存储的，所以当InnerDB通过

select count(*) from table 的时候会全表查询，而MyIsam是不想要的。

6.MyIsam可以不需要主键，但InneDB一定需要主键,如果InnerDB如果没有主键，它会自己给自己生存一个。

7.MyIsam支持全文索引，InnerDB在5.7版本后也支持全文索引。

8.Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

   Innodb：frm是表定义文件，ibd是数据文件

   Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

 

3.Mysql的锁

1.表锁和行锁

https://www.cnblogs.com/leedaily/p/8378779.html

按照锁的级别来分，分为表锁和行锁

表锁：加锁快，开销小，加锁力度大，不会造成死锁，但是并发度低，容易造成锁冲突

行锁：加锁慢，开销大，加锁力度大，会造成死锁，并发度高，造成锁冲突的概率低

 

 

按照锁的特征分，分为共享锁（读锁）和排他锁（写锁）

当前session对表或者行加了共享锁后，其他session也能加共享锁，但会禁止本session和其他session加排他锁。

当前session对表或者行加排他锁后，其他session不能加共享锁也不能加排他锁和共享锁。

 

由于MyIsam只支持表锁，所以的加的也是表级共享锁和表级排他锁。

 

2.共享锁和排他锁

显示加锁解锁方法：

排他锁

lock table tablename write;    

unlock tables;

 

select ……. lock in share mode

共享锁

lock table tablename read;

unlock tables;

 

select ….. for update

 

3.间隙锁

当使用排他锁或者是共享锁的时候，如果对带有索引的列进行的不是等值查询而是用范围进行查询的话，那么锁定的不止是数据库里面存在满足条件的记录，还会锁住数据库里面不存在但也满足条件的记录

举个例子，数据库里有id 是1,2,3,4,5 五条记录，并在这个id上有索引，当

select * from table where id>3 for update;  这个sql锁住的是id为4，5还有id 大于5的记录，虽然这些记录不在数据库里。

 

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作 （UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁。

但是在加了表级共享锁后，还是可以并发插入数据的，

如果是lock table table name read local; 其中local就是在满足条件下准许在并发插入

当concurrent_insert设置为0时，不允许并发插入。

当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。

当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

 

InnerDB的话由于是支持行锁的，所以可以给某一行加共享锁和排他锁

update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，

如果加排他锁可以使用select …for update语句，

加共享锁可以使用select … lock in share mode语句。

所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ mysql InnoDB引擎默认的修改数据语句： update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型 ，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。 所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

show status like 'innodb_row_lock%';

 

https://www.cnblogs.com/leedaily/p/8378779.html

 

 

3.Mysql的事物

 

https://www.cnblogs.com/kismetv/p/10331633.html   事物原理

mysql的事物默认是开启的，也就是执行一条sql就是一个事物，查看事物是否开启

show variables like ‘autocommit'

如果要关闭事物的提交，就需要  set autocommit = 0;

 

事物是数据库执行的一个单元，里面有一个或者多个sql，要么全部执行要么全部不执行

事物的四大特性就是ACID，也就是原子性，一致性，隔离性和持久性。

原子性：事物里面的所有操作可以看作是一个整体，要么全部成功，要么全部失败,如果前面的成功了，后面的失败了，则前面成功的需要会滚。事物的原子性原理是靠undo日志来维持的，当对数据进行修改的时候，会生成相应的undo log，如果事物执行失败或者发生回滚，则需要利用undo log将数据恢复到以前的样子。以update作为例子，undo log需要记录被修改的主键，确认有哪些列都被修改了，记录被修改前后的值，回滚的时候就可以利用这些信息将数据还原。

一致性：将数据库从一种状态改成另外一种状态,数据库里面的数据执行前后都是合法的，数据库的数据要保持完整性。原子性，持久性和隔离性都是为了保持一致性。

持久性：事物对数据的修改并提交后，就是永久性的改变，及时数据库发生了崩溃，这个更改也是存在的。持久性实现原理是redo log。innerdb为了减少IO次数，有一个缓冲池，取数据的时候会先从缓冲池里面取，如果缓冲池里面没有数据，就会从磁盘里面取，然后放入到缓冲池里面。修改数据的时候也是先写入到缓冲池中，如果缓冲池里面，然后再写进到磁盘中，但如果当写入到缓冲池但还没有写到磁盘里面发生宕机后，就会导致数据丢失。在修改数据的时候会先写到redo log里面，再写入缓冲池中，所以在事物提交的时候，会先将redo log写入硬盘中。这样在发生宕机的时候，就可以读取redo log进行日志恢复了。其实总结起来就是：在修改数据的时候会先用redo log记录修改的数据，然后再来修改数据，当提交数据的时候会先将redo log存入到硬盘中。

隔离性：就是两个事物并发执行，其中一个事物对数据的更改不会影响到另一个事物对数据的执行，两个事物之间不会产生干扰。但是由于隔离级别设置的不同，所以导致两个事物执行的时候会产生不同的影响。

 

(一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性

(一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性

MVCC即多版本控制，可以让不同的事物在同一时刻看到的数据不一样。它最大的好处是读写不冲突，并发性能好，这是在我们的数据行都隐藏有三个字段，分别是创建时间段，删除时间段和回滚指针，即指向undo log的指针。其中创建时间段存储的是这行数据被创建时的事物版本号，删除时间段指的是这行数据被更改或者删除的事物版本号，可以通过undo log做回滚操作查看之前的数据。

MVCC查询事物的两个原则

1.查询的数据的创建版本号要小于等于当前版本号。

2.查询的数据的删除版本号要大于当前版本号或者为空。

 

最后总结一下事物的原理：

原子性是利用undo log，当发生回滚的时候利用修改时所留下来的undo log进行数据恢复

持久性时利用redo log，当数据修改被写入缓冲池中的时候，会先将修改用redo log记录下来。

隔离性利用数据库锁机制和mvcc机制来进行数据隔离的判断。

 

 

4.Mysql的优化

最主要的优化就是sql优化

1.开启慢查询，查看慢查询里面查询过慢的sql

2.使用explain对sql进行分析，看是否使用索引，扫描的行数和使用索引的类型以及查询中有回表查询

3.注意让mysql索引失效的一些操作和能让mysql加快查询的一些操作。

4.如果数据太多的话，就分区分表了。

5.如果还是比较慢的话可以用show  processlist 查看，看看有可能是连接数过多，或者有很多sleep时间过长的线程，或者有出现锁等待过长的线程，可以进行杀掉。或者查看waittime的设定值， show variables like “%timeout%”; 修改这个值来减少连接

6.查看锁冲突的情况，看是否发现死锁或者锁冲突的情况很多 show status like 'innodb_row_lock%';

https://blog.csdn.net/xuanxuan_good/article/details/53730842   解决锁冲突的问题