Mysql数据库相关

存储引擎MyISAM 和InnoDB区别？

1）InnoDB支持事务，MyISAM不支持事务

2）InnoDB支持外键，MyISAM不支持外建

3）MyISAM支持全文索引，InnoDB不支持全文索引

4）InnoDB不保存表的总行数，可以使用select count(*) from table 计算多少行

5）清空整个表时，InnoDB逐行删除，效率低，MyISAM则是直接重建表。MyISAM删除表后不会立即清理磁盘空间，需要定时清理，命令：optimize table dept;

6) InnoDB支持行锁（某些情况下还是会锁住整张表）

7）InnoDB跨平台可直接使用，MyISAM不行

8）InnoDB表很难被压缩，MyISAM可以

 

 

应用场景：MyISAM不支持事务等高级功能，但是提供高速存储，检索和全文搜索能力，如果应用中有大量select查询，MyISAM是更好的选择（读多）

InnoDB用于需要事务处理的应用程序，包括ACID事务支持，如果需要进行大量insert或update操作，则选择InnoDB引擎，这样可以提高用户并发操作的性能（写多）

 

mysql的日志文件格式？

    binary log  

    update log  

     undo log 

     redo log

     query log

 

索引？

在关系型数据库中，索引是一种单独的，物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。即索引是对数据库表中一列或多列的值进行排序的结构，能够帮助mysql高效地获取数据的数据结构

 

索引建立的过程？

InnoDB中存储空间管理的最小单位是页，页的默认是16kb，每个页中存放了数据。页与页之间是通过双向链表来建立的，页中的数据都会根据主键的值从小到大排列并用单向链表连接起来。然后会为页建立页目录，页目录其实就是索引，在建立页目录的过程中规定了下一个数据页的主键值必须大于上一个数据页的主键值。当一个页中的数据超出了范围就产生一个新的页存放，这个过程会产生页分裂，主键小的会跑到上一个页，主键值大的往后一个页移动

存在的问题是页与页之间不是相邻的，但是页之间通过双向链表进行连接，为了快速的访问到特定的页号就需要为页建立页目录，这个页目就是索引

 

 

mysql中常用的索引有B+树索引（包括普通索引，唯一索引，主要索引）哈希索引，B树索引

    

覆盖索引？

  假如能够通过检查索引就可以读取到想要的数据而不需要再到数据表中读取，即索引包含了满足查询条件的字段时就叫做覆盖索引。覆盖索引必须要存储索引的列，而哈希索引，全文索引都不存储索引的列，所以mysql选择b+树索引做覆盖索引。用覆盖索引可以使查询效率得到很大的提升

 

索引的优点？

1）大大减少了服务器需要扫描的数据量

2）帮助服务器减少排序和临时表

3）可以将随机IO变为顺序IO

 

索引的缺点？

1）创建索引要花费时间，占用存储空间

2）减慢数据修改速度

 

 

什么是事务？

事务就是一组原子性的操作，这些操作要么全部发生，要么全部不发生，不能只发生一部分

事务具有ACID四种特性：原子性，一致性，隔离性，持久性

原子性：事务是一个不可分割的操作

一致性：事务开始前和结束后，数据库的完整性约束不被破坏

隔离性：每个读写事务之间是相互分开的，在事务提交前对其他事务是不可见的

持久性：事务一旦提交，其结果就是永久性的，宕机也能恢复

 

怎么保证的ACID特性？

    首先，持久性是由redolog保证的，就算断电也可以根据redolog进行未完成的事务，原子性有undolog保证，提供了回滚的特性，隔离性由MVCC机制保证，通过对每行数据添加两个隐藏的列来实现，而一致性则是由原子性，持久性，隔离性共同保证的

 

事务的隔离级别？

未提交读（read uncommit）

提交读（read commit）

可重复读（repeatable read）

可串行化（serializable）

 

隔离级别依次提高，依次解决了脏读，不可重复读，幻读问题

 

事务隔离级别的实现原理？

    未提交读：乐观锁实现的，对当前读取的数据不加锁，只有在更新的时候加上行级锁

    提交读：对当前读取的数据加上行级锁，一旦读完立即释放，更新数据时加上行级排它锁

   可重复读：读取数据时加上行级共享锁，更新数据时加上行级排它锁

   可串行化：读取数据时加上表级共享锁，更新时加上表级排它锁

 

脏读：事务可以读取未提交的数据

不可重复读：执行两次同样的查询，可能得到不一样的结果

幻读：当某个事务在读取某个范围的记录时，另一个事务又插入了新的记录，当之前事务再次读取时，会产生幻行

 

不可重复读和幻读容易混淆，不可重复读侧重修改，幻读侧重新增和删除。解决不可重复读只需要锁住满足条件的行，解决幻读需要锁表

 

数据库的三大范式？

第一范式：确保每列的原子性，即每列的属性都是不可再分的最小数据单元

第二范式：满足第一范式，并且除了主键以外的其他列，都要依赖该主键

第三范式：满足第二范式，并且不依赖除了主键外的其他列

 

 

 

mysql中有哪几种锁？

MyISAM支持表锁，InnoDB支持表锁和行锁，默认为行锁，还有页级锁

表级锁：开销小，加锁快，不会死锁，粒度大，锁冲突概率高，并发量最低

行级锁：开销大，加锁慢，会死锁，粒度小，锁冲突概率小，并发量最高

 

InnoDB行级锁的实现方式？

   InnoDB行级锁是通过给索引上的索引项加锁来实现的，也就意味着只有通过索引条件检索数据，innodb才能使用行级锁，否则将使用表锁

 

B树和B+树的区别？

B+树只有叶子结点存放数据，其余的节点都用来索引。

B树的节点既要存储索引，又要存储data（也就是所有节点都能存放索引和数据）

 

为什么数据库中用B+树做索引？

B+树的磁盘IO代价更低，查询效率也更加地稳定，更有利于数据的范围查找

 

为什么mysql的索引选择了B+树而不是红黑树？

    红黑树一般是存储在内存中才会使用的数据结构，而索引是存放在磁盘中的，当存储大量数据时，红黑树由于深度过大的原因会导致磁盘io过于频繁，进而导致效率低下（因为要获取磁盘上的数据，必须先通过磁盘移动臂移动到数据所在的页面，然后找到指定盘面，接着找到数据所在的磁道，最后才进行读写），而红黑树的深度过大导致磁盘io频繁读写。而B+树可以有多个子女就能够降低树的高度以此来减少磁盘io。它将节点的大小设为等于一个页，这样每个节点只需要一次io就可以完全载入
 

 

B+树对比B树的优势？

1）B+树的磁盘IO代价更低

2）遍历更快也更方便

3）查询效率更加稳定

 

索引是怎么提高性能的？

索引通过事先排好序，从而在查找时可以应用二分查找等高效率的算法

 

索引的原则？

   1）最左前缀匹配原则：mysql会一直向右匹配直到遇到（>,<,between,like）时才会停止匹配

   2）比较频繁地作为查询条件的字段才创建索引

    3）更新频繁的字段不适合创建索引

    4）对于text，image，bit的数据类型不要建立索引

     5）有外键的数据列一定要建立索引

     6）索引字段越小越好：数据库的数据存储是以页为单位，一页能存储的数据越多查询的效率越高

 

聚簇索引和非聚簇索引，回表操作？

    聚簇索引：数据和索引放在一起，数据放在叶子页上，索引放在非叶子结点上

   非聚簇索引：又叫做二级索引，它的叶子节点中保存的不是数据本身，而是行的主键值，通过二级索引查找需要进行两次查找

   聚簇索引的优点：

                            1.它将数据行和索引保存在同一棵B+树中，通过聚簇索引可以直接获取数据，相比非聚簇索引更快

                           2.对于范围查询的效率很高，因为其数据是按照大小排列的

   聚簇索引的缺点：

                           1.更新代价比较高，如果更新了行的聚簇索引列，就需要移动数据到相应的位置，可能还会发生页分裂

                           2.插入速度严重依赖于插入顺序

                           3.聚簇索引可能导致全表扫描变慢，因为可能需要加载物理上相隔较远的页到内存中

  非聚簇索引的优点：维护的代价更低，当更新了数据行时，由于非聚簇索引存储的是主键的值，所以非聚簇索引并不需要也去更新

   数据库的回表操作：数据库根据索引找到了指定的记录所在行后，还需要根据rowid再次到数据块里取数据的操作

  避免回表：将需要的字段放在索引中去

 

在哪些列上可以建立索引？

1）在经常需要搜索的列上

2）在作为主键的列上

3）在经常用在连接的键上

4）经常需要使用where子句的列上

5）在经常需要排列的列上

 

哪些列不利于创建索引？

1）对于查询很少使用或者参考列不应该创建索引

2）数据值很少的列不应该创建

3）定义域text，image，bit数据类型的列不应该

4）修改性能大于检索性能的时候不应该

 

什么时候索引会失效？

1）列与列对比时

2）可以存在null值时

3）查询中使用“！=”，“>”,"<"会失效（当不是主索引或者不是整型索引的时候）

4）like通配符以%开头就会失效

5）条件上包括函数

6）多列索引不适用第一部分就会失效

7）or连接多个条件索引会失效，除非or的条件都加了索引

 

怎么看是否加了索引？

查看执行计划，表示index seek的话就用了索引

 

事务的底层原理？

MVCC和锁

 

主从复制过程？

1.master将改变记录写入到二进制日志（binary log）中

2.slave将master的binary log 拷贝到它的中继日志relay log

3.slave重新来一遍中级日志中的时间，将改变应用到自己的数据库中，mysql复制是异步的且串行化的

 

 

关系型数据库和非关系型数据库的区别？

  关系型数据库：最大特点是事务一致性，缺点是读写性能比较差，高并发读写需求下磁盘IO会是一个性能瓶颈

  非关系型数据库：使用键值对存储数据，格式灵活，速度快，扩展性高，缺点是没有事务处理，数据结构相对复杂

 

mysql慢查询？

    也就是慢查询日志，是用来记录mysql中响应时间超过阀值的语句，运行时间超过long_query_time的sql语句就会被记录到慢查询日志中。慢查询日志需要手动开启，设置slow_query_log：1

    然后就可以对慢查询日志进行分析了。这里是利用explain来分析，如果发现type为index或者all就说明必须要优化了。我们可以去看下是否出现了%开头的模糊匹配，看下索引是否生效。优化数据库结构，比如将大表进行拆分，还可以分解关联查询，也就是将大的查询分解为多个小查询

 

数据库崩溃时事务的恢复机制（redo日志和undo日志）

undo Log：

为了实现事务的原子性，在mysql数据库InnoDB存储引擎中，还用undo log 来实现多版本并发控制

原理：为了满足事务的原子性，在操作任何数据之前，首先将数据备份到undo log，然后进行数据的修改，如果出现了错误或者用户执行了回滚语句，系统可以利用undo log将备份的数据恢复到事务开始之前的状态

如何保证原子性和持久性：更新数据前记录undo log，为了保证持久性，必须将数据在事务提交前写到磁盘，undo log必须先于数据持久化到磁盘

缺陷：每个事务提交前将数据和undo log提交到磁盘，导致了大量的磁盘Io

 

 

redo Log：

原理和undo log相反，redo log记录的是新数据的备份，在事务提交前，只要将redo log持久化即可，当事务崩溃时，虽然数据没有持久化，但是redo log已经成就化，系统可以根据redo log的内容，将所有数据恢复到最新的状态

 

mysql热备份原理？

   也就是主从复制的原理：master将更改记录写入binary log中，然后slave将binary log中的数据读取到自己的relay log中，再重做一次relay log中的事件从而实现了热备份

 

 

百万级别的数据如何删除？

    1.先删除索引（因为对数据进行修改操作，对应的也会对索引产生操作）

    2.删除无用的数据

     3.删除完成后重新创建索引

 

mysql数据库cpu飙升到500%怎么处理？

    首先用top命令观察是否是mysql的原因，如果是的话，去查看是否有消耗高的sql语句，看下索引是否生效，或者数据量是否过于庞大，然后进行相应的修改

 

数据库主键索引和普通索引的区别，哪个查询更快？

    主键索引是一级索引，叶子结点存放的是整行记录，普通索引是二级索引，叶子结点存放的是主键id。索引要查询数据时主键索引只需要一次查询，而普通索引需要两次查询，因此普通索引查询要慢一些

 

为什么mysql主键不能过大？

     内存很珍贵，mysql的缓冲区有限，如果主键太大，存储的索引和数据就会减少，磁盘io的概率也会增加

 

mysql join的底层原理？

    嵌套循环算法：

                         1.通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中进行查询，然后合并结果

                          2.如果还有第三个参与join，则再通过前两个表的join结果作为新的循环基础数据，再一次重复上述的操作

 

数据库B+树索引的建立过程？

数据库按照页从磁盘读       数据在一页中是通过单链表的形式按大小顺序连接的，为了在一页中进行快速的查找，会在页中建立页目录，也就是建立了一个索引，当数据多了一页放不下，就会有更多的页，然后会专门有一页是给存数据的页建立B+索引

 

为什么二级索引存储的是主键id而不直接存放数据位置？

     因为这样做减少了出现行移动或者数据页分裂时二级索引的维护工作（当数据更新时，二级索引不需要修改，只需要修改一级索引，一个表只能有一个一级索引，其他的都是二级索引，这样的话就不需要重新构建二级索引了）

     缺点：二级索引体积可能会变大，二级索引需要两次查找，速度没那么快了

 

一条查询语句是怎么执行的（比如select）？

    先检查该语句是否有权限，如果没有权限，直接返回错误消息，如果有权限，则先查询缓存，有直接缓存，没有的话再通过分析器进行词法分析，提取sql语句的关键元素（这里是select，还有去查询的表，查询的条件），然后判断sql语句是否有语法错误，没有的话进行下一步，优化器确定执行方案，最后进行权限校验，失败返回，成功的话返回执行结果
 

 

为什么MyisAM不支持行锁？而innoDB支持？

    因为myisAM的一级索引和二级索引都指向物理行，当通过一级索引或者二级索引来查询数据的时候，都是先查找到物理位置，然后再在物理位置上去寻找数据。而InnoDB的一级索引存储索引，二级索引存储的是主键的id，通过一级索引进行查找的时候能够很快查到数据，通过二级索引查找数据的时候，需要先找到对应的主键id，然后才能查找到对应的数据，也就是说InnoDB在一级索引时在叶子结点上存放数据，在非叶子节点上存储了行的数据，也叫作聚簇索引。所以，InnoDB支持行锁，因为InnoDB的索引和数据是在一起的，而myisAM的索引和数据是分离的，myisAM天生就是非聚簇索引，之所以innoDB可以锁行，是因为InnoDB的索引结构上即存储了索引，又存储了数据，而myisAM的索引指向的是另一片数据，所以没法精确地锁住数据段
 

 

数据库的分页过程：

    InnoDB中存储空间管理的最小单位是页，页的默认大小是16kb。每个页中都存放了数据。页与页之间通过双向链表连接，索引中的数据会根据主键的值从小到大排列并用单向链表连接，在建立目录的过程中规定了下一个数据页的主键值必须大于上一个数据页的主键值。当一个页中的数据超过了范围就会产生新的页，这个时候主键小的跑到上一个页，大的往后一个页移动，这就是说页分裂

 

B+树如何进行查询，插入，删除？

    查询：从根节点开始，首先从节点内部进行二分查找，直到最后进入叶子节点，若叶子结点中存放该索引值，就能找到对应记录的指针，若不存在，则查找失败

   插入：首先找到所插入索引值所在的叶子结点及叶节点内对应的位置，拟插入，然后会检查叶子节点的合法性，假如不合法，那么叶子结点会进行分裂，分裂后的两个叶节点的最小索引值，将大的索引值插入到父节点，然后检查父节点的合法性，假如父节点不合法，会进行再分裂，一直重复这个过程直到合法，然后插入结束

   删除：首先拟删除该数据，如果不破坏B+树本身的性质，则直接删除。如果删除操作导致了节点的最大值或最小值改变，那么相应的改动父节点中的索引值，在删除数据后，如果导致节点中数据个数不足，那么要么向兄弟节点借数据，要么就直接和兄弟节点进行合并

 

sql注入和解决方式？

  sql注入就是指用户所输入的内容在sql语句拼接过程中使逻辑发生变化而产生了新的sql语句（比如加上个or或者and）

 解决方案：使用预编译sql语句进行参数的传递。比如用PrepareStatement,我们先把语句固定好，然后将其中的？换成要传递的参数
 

 

mysql长事务？

   长事务是运行时间比较长，长时间未提交的事务，这种事务往往会造成大量的阻塞和锁超时，容易造成主从延迟，应该尽量避免

   解决办法：尽量避免一次处理太多数据，对长事务进行拆分，这样当事务发生错误时，只需要回滚一部分数据就行了

 

mysql的体系结构？

  自顶向下分别是：网络连接层，服务层，存储引擎层，系统文件层

  网络连接层：负责连接管理，授权认证，安全，服务器上维护了一个线程池

  服务层：进行查询解析，sql执行计划分析与优化，还有查询缓存等

  存储引擎层：负责数据的存储与提取，服务器中的查询执行引擎通过api与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异

  系统文件层：主要是将数据库的数据存储在文件系统之上，并完成与存储引擎的交互

 

红黑树和b+树？

  红黑树其实就是不那么平衡的AVL树，虽然红黑树舍弃了一定的平衡性，导致读取效率没AVL树高，但是更有利于维护，每次插入和删除的平均旋转次数远小于AVL树

  红黑树一般用在内存中进行排序，b+树多用于磁盘上进行排序

  b+树的磁盘io代价更低：它的非叶子结点没有数据，索引比较小，避免了树形结构不断的向下查找，可以降低io次数

 并且b+树的查询效率更加稳定，每次查询都是到达叶子节点，所以效率稳定

而且b+树只要遍历叶子结点就能实现整棵树的遍历，而红黑树需要中序遍历，所以b+树遍历更方便

 

drop主要用于删除结构

例如删除数据库：drop database XX，删除表 drop table XX。字段也是结构的一种，也可以使用drop了？对的，但是我们改变了表结构要先alter方法。例如，我们要删除student表上的age字段的信息，可以这样写：alter table student drop age
delete主要用于删除数据

举个例子，要删除 student表上名字为‘张三’的所有信息：delete from student where name=‘张三’。这种情况下用delete，由此可见delete常用于删除数据。

 

 

数据库的优化？

   1.优化索引，sql语句，分析慢查询

  2.设计表的时候根据数据库的三大范式来进行设计

  3.使用缓存将经常访问并且变化很少的数据存到缓存中

  4.进行主从复制读写分离

  5.分库分表

 

 

悲观锁的实例（行锁，表锁也是悲观锁）？

  select * from table for update....

 

乐观锁实际上没有加锁

 

 

问：SELECT COUNT(*) 和 SELECT COUNT(某个字段) 有什么区别
答：第一个返回表中所有的记录（包括 null），第二个则是返回所有记录（不包含 null）

 

mysql底层是用什么写的？

   c++

 

联合索引？

   联合索引就是对表中两列或两列以上的数据建立索引，也叫作复合索引。它遵循最左匹配原则，从左到右的使用索引中的字段，必须匹配最左侧部分。它的使用与where中的索引顺序无关，mysql查询会自动进行分析优化。但是最好和索引从左到右的顺序一致，这样的话查询效率最好。联合索引碰到范围查询的话之后的索引就会失效

 

 

char和varchar的区别？

   1.char的长度是不可变的，varchar的长度可变，假如初始化都为10，这个时候存入‘abcd’,char的长度依然是10，而varchar的长度就为4了。

  2.但是char的速度要比varchar快很多，因为它的长度固定，所以方便程序的存储和查找，但是varchar不固定

 3.char存储字符时英文字符占1个字节，汉字占两个字节。而varchar英文字符和汉字都是两个字节

 

mysql的内连接，左连接，右连接？

    内连接：select * from table1 a inner join table2 b on a.id = b.id;

   左连接：select * from table1 a left join table2 b on a.id = b.id;

   右连接：select * from table1 a right join table2 b on a.id = b.id;

 

mysql的聚合函数？

     AVG()        COUNT（）  MAX（）  MIN（）  SUM（）