java面试篇之mysql

mysql页大小为16K

mysql常用的存储引擎有哪些？ 

 InnoDB   (数据库默认)：支持事务，外键约束,行锁，并发性好，灾难恢复性好。

MyISAM：不支持事务，行锁，外键约束，并发性差，MyISAM表容易损坏，灾难恢复性不佳。

数据紧凑存储，可以获得更小的索引和更快的全表扫描。

Memory：数据保存在内存中，效率非常高，但是不安全，数据容易丢失。使用表锁，只支持固定大小的行。

InnoDB和MyISAM存储引擎的区别？

InnoDB存储引擎数据和索引存放在一个文件中(.ibd),MyISAM存储引擎数据和索引存放在不同的文件中，数据(.myd)和索引(.myi)。

InnoDB支持事务，MyISAM不支持事务。

InnoDB支持行锁和表锁，MyISAM支持表锁。

InnoDB支持外键，MyISAM不支持外键。

InnoDB按主键升序插入数据，MyISAM按记录的插入顺序保存数据。

为什么InnoDB表必须由主键，并且推荐使用整型自增主键？

InnoDB必须有主键是因为数据库设计就是如此，如果创建表时，没有指定主键，数据库会优先选择不包含NULL值的唯一索引列作为表的主键，如果没有找到唯一索引字段，数据库会使用隐藏字段row_id作为主键。

为什么选择整型呢？数据库的页大小是16K，整型占用的空间比较小，一页可以保存更多的数据，这样根据局部性原理，每次加载一页的数据会更多，减少IO次数，提高查询效率。而且，进行数据查询时整型数据比较的效率要更高。

为什么使用自增呢？选择自增是因为，数据记录可以顺序添加到当前索引的后续节点，主键的顺序就是当前数据的插入顺序。写满一页，才会开辟一个新的页。如果使用的不是自增主键，数据库会为了保证叶子节点的顺序性，而进行数据移动或者页分裂，增加了开销。而且还会产生大量的碎片，使索引不够紧凑。（后续通过optimize table来重建表并优化填充页也需要开销）。总之，非自增主键，会造成数据移动或页分裂增加开销，还会产生碎片，如果后期维护也需要时间，所以效率没有自增主键效率高。

为什么非主键索引(普通索引/辅助索引)叶子节点存储的是主键值？

如果叶子节点存储的是地址，那么如果出现了数据移动或页分裂，数据记录的地址发生了改变，就需要重新维护叶子节点中的地址。如果叶子节点保存的是主键，就不需要重新维护非主键索引。而且，叶子节点存储主键占用的空间更小。

索引本质是什么？

索引是帮助mysql高效获取数据的排好序的数据结构。索引保存在文件系统中，通常用B+树(B树)或hash来实现。

注：回表：普通索引叶子节点存储的是主键,根据主键再进行一次查询。

索引覆盖：查找建索引的字段，替换 select *。

最左匹配原则(针对组合索引):索引优先匹配索引最前面的字段，从中间开始，或者中间字段丢失，可能会造成索引失效。

索引下推(针对组合索引)：现根据索引最左字段查询数据到内存，然后在根据索引中的其他字段筛选数据。

 

索引一定能提高效率吗？

创建索引的目的就是为了提高查询效率，索引的确能提高查询效率，但是创建和维护索引是需要耗费时间，当表中数据进行增、删、改的操作时，索引也需要同时维护。会影响增、删、改操作的效率。所以不是索引越多越好。另外，索引是数据结构，是需要占用磁盘空间的。

索引的类型有哪些？

mysql索引有五种类型：主键索引、唯一索引、普通索引、组合索引和全文索引。

主键索引：一种唯一索引，索引列的值不能重复，不能为空，每个表只能有一个主键索引。

唯一索引：索引列的值不能重复，可以为空，每个表可以创建多个唯一索引。

普通索引：基本索引类型，值可以为空，可以重复。

组合索引：多个列值共同组成一个索引，使用于组合查询，但是要注意最左匹配原则。

全文索引：索引类型为FULLTEXT。可以在varchar、char、text类型上创建。

注：主键索引和唯一索引的区别：

       主键是一种约束，可以被其他表引用为外键，唯一索引就是一种索引。

       主键是一种唯一索引，但是唯一索引不一定是主键。

       主键不允许有空值，唯一索引可以有空值。

        一个表中能创建一个主键，可以创建多个唯一索引。

索引的使用场景？

索引是用来提高查询数据的速度，所以一般使用在增、删、改操作比较少，而查询多的情况下。

当表中的数据很少时，是不需要创建索引的，即使你创建了索引，mysql进行优化后，也不会使用创建的索引。但是随着数据量的增加，创建索引来提高效率是非常必要的。

当要检索的列有重复值时，使用普通索引。

当要检索的列没有重复值时，使用唯一索引。

当要检索的列为多个时，使用组合索引。

普通索引不支持空值，唯一索引支持空值。

频繁更改的字段不适合创建索引。

where子句或者用作join连接的字段适合创建索引。

使用短索引，对长字符串进行索引时，应指定长度。减少占用的空间。

注：创建组合索引索引时，重复值最少的字段放在前面。

使用非空列创建索引，含有空值的列查询优化比较困难，可以通过给字段添加默认值实现字段非空(用0或“”代替NULL)

索引常用的数据结构有那些？

B+树、B树、hash。

B树特点：

关键字集合分布在整棵树中且不重复。

搜索可能在非叶子节点结束。

叶子节点有相同的深度，叶子节点指针为空。

节点中的数据从左向右递增排列

优点：经常访问的数据更靠近根节点，访问更迅速。（查询效率不稳定）

B+树特点：

非叶子节点不存储数据，只存储索引(冗余)，目的为放更多的索引。

叶子节点包含所有索引字段。

叶子节点用指针连接，提高区间访问的性能。 

优点：非叶子节点不保存数据，这样内存页就可以保存更多的索引，使数据存放更加紧密，更具有空间局部性，访问叶子节点关联的数据也具有更好的缓存命中率。

叶子节点用指针相连，而且是顺序排列的，提高了区间查找和搜索的效率。B树需要进行每一层的遍历，相邻的元素在内存中可能不相邻，所以命中率没有B+树高。

B+树的深度更小，IO也就更少，查询效率更加稳定。

注：二叉树深度过大，会造成I/O读写频繁，影响查询效率。

创建索引的三种方式？

创建表时创建索引。

alter table table_name add index/unique index_name(column);

create index index_name on table_name(column);

mysql中有那些锁？

按锁的类别区分：

共享锁：读锁，当用户进行数据读操作时，对数据进行加锁。

排他锁：写锁/独占锁，当用户进行数据写操作时，对数据进行加锁。排他锁是线程私有的，同一时刻只能有一个线程占有写锁，其他线程等待。

注：解决死锁的办法：

多个程序以相同顺序访问表。

在一个事务中尽量一次锁定所需要的全部资源。

容易死锁的地方，使用表锁。

 

按照锁的粒度区分：

行锁(对当前操作行加锁)：开销大，加锁慢，锁粒度最小，发生锁冲突的概率最低，并发度高，会出现死锁。

表锁(对当前操作表加锁)：开销小，加锁快，锁粒度大，发生锁冲突的概率最高，并发度低，不会出现死锁。

页面锁(锁定当前相邻的一组记录)：开销和加锁时间介于表锁和行锁之间，锁定粒度介于表锁和行锁之间，并发度一般，会出现死锁。

悲观锁和乐观锁：

悲观锁：就是上面提到的数据库锁机制。

//添加共享锁
select field form table_name where … LOCK IN SHARE MODE
//添加排他锁
select field form table_name where id=1 for update;

//注意：如果Id是索引列，则使用行锁，否者使用表锁。

乐观锁：通过版本号或CAS实现

update table_name set filed=xxx,version=version+1 where id =#{id} and version=#{version};

注:Read uncommitted隔离级别，读取数据时不需要加共享锁，不会和其他锁产生冲突。

Read committed隔离级别，读取数据时加共享锁，语句执行完就释放共享锁。

Repeatable read隔离级别，读取数据时加共享锁，事务提交后才释放共享锁。

serializable隔离级别，对整张表加锁，事务提交后释放锁。

InnoDB有三种锁算法：

       record lock 单行记录上的锁。

gap lock 间隙锁，锁定一个范围，不包括当前记录。

next-key lock 间隙锁，锁定一个范围，包括当前记录。

查询时使用next-key lock，当有唯一索引时，锁降级为record key。

数据库优化方面的经验？

要合理的使用索引，索引能够提高查询效率，但是也会降低增删改的效率并且占用内存空间。

如果可以保证数据库的完整性，可以去掉外键约束(影响增删改性能)。

如果可以保证数据库的完整性，可以破坏三大范式设计数据库。

字段多的表分解成多个表，使用频率低的字段，单独创建一张表保存。

联合查询的表，创建中间表。

合理的增加数据冗余字段。复杂的查询分解成小的查询。

使用缓存，减少数据库的压力。

主从复制，读写分离。

数据库做分库操作，减轻数据库压力。

执行计划怎么查看？执行计划各个字段的含义？

explain + sql语句查看执行计划。

字段含义：

ID：select 语句执行顺序，数值越大，越先执行，值相同，顺序由上至下

select_type:查询类型，区分普通查询、联合查询、子查询等复杂查询。

    simple：简单查询，不包含子查询或union

     primary：包含复杂查询，最外层别标记为primary

     subquery：select或where中包含子查询。

     derived：form中包含子的查询被标记为derived

     union：如果第二个select在union之后，被标记为union，如果union包含在form子句的子查询中，外层select被标记为derived。

     union result：从union表中获取结果的select。

     dependent subquery:依赖外部结果的子查询。

     dependent union：union作为子查询时，第二个或第二个后面的查询被标记为这个值。

table：使用到的表

type：扫描表的索引类型system>const>eq_ref>ref>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all

最低到达range，尽量到达ref，最好到达const。

possible_keys：存在的索引，并不一定使用。

key:实际使用的索引。

key_len:索引字段最大可能长度，由字段计算出来。

ref:表示那些列或者常量被用于查找索引列上的值。

row：估算读取的行数

extra：额外信息，是否使用了覆盖索引，是否创建临时表，是否使用where条件过滤等都放在这。

SQL语句的优化有哪些？

尽量避免使用select * ，尽量sql全部大写

合理使用索引，索引可以提高查询效率。

优化sql，避免全表扫描，在where和order by字段创建索引。

is null和is not null 要谨慎使用，可能造成索引失效。

尽量避免使用or 造成索引失效，可以使用union替换。

尽量避免是！=或者<>,造成索引失效。

使用join替换子查询。

in 和not in要谨慎使用，避免索引失效。

复合索引要遵循最左匹配原则。

模糊查询不要使用前模糊比如'%key',造成索引失效。

where 中不要使用函数运算或表达式操作，可能造成索引失效。

避免查询不必要的数据 可以使用limit 1。

重复查询的数据，可以添加缓存。

使用索引覆盖，避免全表扫描。

 

数据库三范式是什么？

第一范式：1NF是对原子性的约束，要求字段具有原子性，不可以再拆分。

第二范式：2NF是记录的唯一性约束，要求数据要有唯一性标识，即要有主键，非主键依赖于主键。

第三范式：3NF是对数据的冗余约束，要求非主键字段直接依赖于主键，不能存在传递依赖。

什么是数据库事务？

事务是一个不可分隔的数据库操作序列，是数据库并发控制的基本单位，执行结果是从一种一致性状态变成另一种一致型状态。事务中的操作要么全部成功，要么全部失败。

事务的四个特性？

原子性：事务是最小的执行单元，不允许分隔，事务内的操作要么全部执行，要么全部不执行。

一致性：事务执行前后，是从一种正确的状态转换成另一种正确的状态。

隔离性：一个事务内的操作，不允许被其他事务所干扰。各个事物之间的操作是相互独立的。

持久性：一旦事务提交后，事务操作的数据是不可改变。即使数据库故障也不影响数据。

事务隔离级别是什么？（默认Repeatable read）

事务隔离级别	产生的问题
未提交读（read uncommitted）:一个事务可以读取到另一个事务未提交的数据。	产生脏读、不可重复读、幻读
已提交读(read committed) ：一个事务可以读取到另一个事务提交后的数据。	不产生脏读，产生不可重复读、幻读
可重复读（Repeatable read）：一个事务读取数据时，不允许其他事务修改数据。	不产生脏读、不可重复读，产生幻读
可串行化（serializable）：事务串行话执行。	不产生脏读、不可重复读、幻读

in和exists有什么区别？

in是内外表进行hash连接，而exists是对外表做loop循环，每次loop对内表进行查询。

两种使用场景：

如果两个表的数据量差不多，使用in和exist查询的效率差不多。

如果两个表的数据量差别比较大。子查询表小的用in，子查询表大的用exists。

not in 和 not exists比较：not in 内外表都进行全表扫描，没有使用索引。

not exists 子查询能使用索引。所以 not exists 比not in效率高。

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

先删除索引、在删除数据、创建索引

 

一天五万条数据增量，使用五年，该怎么优化数据库？

合理选择数据库字段和存储引擎类型，合理的添加索引。

数据库表可以有部分冗余字段，减少多表join产生非常复杂的sql。

书写高效sql，不要使用select *  操作，禁止使用不带任何条件的查询。

添加缓存机制。

数据库做主从读写分离

根据业务需求对数据库进行分库分表操作。

超大分页怎么处理？

使用覆盖索引：select * from table_name where id in (select Id form table_name where age > 20 limit 1000000,100);

尽量减少这样的操作，要求逐页循序查看。

使用redis缓存，将数据加载到缓存中。

mysql数据库服务器cpu突然剧增，怎么处理？

首先使用top命令查看是不是mysql进程造成的cup剧增。

如果不是，就要找出占用高的进程，进行相应处理。

如果是，使用show processlist查看session情况，看看是不是有消耗资源的sql在跑。找到消耗高得sql，分析执行计划，看看是不是索引失效，或者就是数据量太大了。

一般来说，是要杀掉这些线程的,同时观察，cup是否下降。进行相应调整后(可能是修改索引、修改sql、或者修改内存参数)，在重新跑这些sql。

还一种可能是每个sql消耗的资源并不多，但是突然有大量的请求进来，造成cup剧增。这需要分析连接数剧增的原因，做出相应的调整，比如限制连接数等。

分库分表带来的问题？

事务支持：分库分表后就变成了分布式事务。

跨库join：分两次查询，根据第一次查询的结果集找到关联的id，进行第二次查询。

跨节点count、order by、group by以及聚合函数问题。

全局唯一ID问题。

数据迁移，扩容等问题。

mysql复制原理？

主库把数据更新的记录保存到binlog二进制日志文件中。

从库将主库的binlog日志复制到自己的中继日志中。

从库读取中继日志中的事件，把数据重放到数据库中。

注：master在每个事务更新数据完成之前，将该操作记录串行的写入到binlog文件当中。

salve开启一个I/O线程，从master获取binlog文件，将sql事件写入中继日志中，如果读取进度赶上了master，线程就睡眠等待master产生新的sql事件。

sql线程读取中继日志，顺序执行日志中的sql事件，保持与主数据库中的数据一致。

主从复制作用：

主数据库宕机，可以切换到从库

进行数据库层面的读写分离。

从库可以用来做数据备份。

读写分离实现方案？

  使用mysql代理，直接实现读写分离和负载均衡，但是不支持事务，性能不高。

dao层决定数据源，mybatis可以通过插件拦截sql，将增删改sql都访问master，查sql访问salve，但是不支持事务，需要自己处理。

service层决定数据源，可以支持事务。

binlog三种录入格式是什么？

statement：每一条修改数据的sql记录在binlog中，不需要记录每一行的变化，减少binlog日志量，节约IO，提高性能。需要保存sql语句上下文。

row：不需要保存sql语句上下文，仅保存哪条记录被修改。

mixed：一种折中方案，普通是statement记录，无法使用时，使用row。

SQL约束有哪几种？

not null:控制字段不能为空

unique:控制字段不能重复，一个表可以有多个unique约束。

primary key:控制数据内容不能重复，一个表只能有一个。

foreign key：使表与表之间关联起来。

check：控制字段的取值范围。

varchar和char区别？

char是定长字符串，长度固定，最多存放255个字符，数据不够指定长度时，填充空格。因为长度固定，所以存区速度比varchar快很多，但是占用多余的空间。

varchar是变长字符串，长度是可变的，最多存放65532个字符，字符长度就是实际插入的字符长度。节省空间但是存取比较慢。

注：varchar(100):100标识最多存放100个字符。varchar(100)和varchar(200)存储xiaoming占用的空间相同，但是varchar（200）排序时消耗更多的内存，因为order by col 使用的是fixed_length计算col长度。

int(10):10的含义是最大显示的宽度为10，仍任占用四个字节存储，存储范围不变。如果字段定义为zerofill，则展示时前面会自动补0。

sql的执行顺序：

FORM、WHERE、JOIN、ON、GROUP BY、HAVING、

SELECT、DISTINCT、UNION、ORDER BY、LIMIT 

慢查询日志：

用来记录查询时间超过某个值的sql日志。

show variables like ‘slow_query_log’查看是否开启慢查询日志。

my.cnf中set global slow_query_long=on 开启慢查询日志。

long_query_time设置临界时间。

慢查询怎么优化？

查看是否加载了多余的数据，比如加载了不需要的字段或者加载了不需要行数据，最后又过滤掉了。

分析语句的执行计划，查看使用索引情况，修改语句或索引，使索引尽量命中。

考虑是否数据量过大，如果是的话，可以考虑分库分表。

mysql有关权限的表有哪些？

user、db、table_priv、columns_priv和host。

 

声明：本文为个人整理总结，如有错误，请指正。