2020 PHP高级面试题之Mysql（三）

2020年求职之路，最近面试十多家公司，通过这些丰富的经历，笔者总结了面试中普遍的三类问题，分享给大家。我总结出了这三类面试题

• 2020 PHP高级面试题之PHP（一）
• 2020 PHP高级面试题之Redis（二）
• 2020 PHP高级面试题之Mysql（三）
• 2020 PHP高级面试题之设计模式（四）
• 2020 各企业PHP高级面试题汇集

1、MySql有哪些索引？

MySQL目前主要有的索引类型为：普通索引、唯一索引、主键索引、组合索引、全文索引。通过给字段添加索引可以提高数据的读取速度，提高项目的并发能力和抗压能力。索引优化时mysql中的一种优化方式。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

下面我们来看看MySQL目前主要有的索引类型：

• 普通索引

普通索引是最基本的索引，它没有任何限制，值可以为空；仅加速查询。可以通过以下几种方式来创建或删除：

• 唯一索引

唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。简单来说：唯一索引是加速查询 + 列值唯一（可以有null）。以通过以下几种方式来创建：

• 主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。简单来说：主键索引是加速查询 + 列值唯一（不可以有null）+ 表中只有一个。

• 组合索引

组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

可以说：组合索引是多列值组成的一个索引，专门用于组合搜索，其效率大于索引合并。

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

• 全文索引

全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

2、建索引有哪些原则，什么时候可能会失效？

     参考：https://www.cnblogs.com/angelyan/p/11417304.html

     索引最左前缀原则

     假设有如下表： create table AAA (a varchar2(32) ,b varchar2(32),c date);在a和c列上建普通索引： create index iN_AAA_1 on AAA (a, c);

1.索引左前缀性的第一层意思：

必须用到索引的第一个字段。select * from AAA where b=:xxx and c=sysdate;则不会用到索引，因为必须有a出现在where 语句中才会使用到该索引。

2. 索引前缀性的第二层意思：

对于索引的第一个字段，用like时左边必须是固定值，通配符只能出现在右边。select * from AAA where a like '1%';会用到索引;而select * from AAA where a like '%1';不会用到索引。

3.索引前缀性的第三层意思：

如果在字段前加了函数，则索引会被抑制，例如：select * from aaa where trim(a)=1,则不会用到索引。

在字段前嵌入了表达式，索引也将被抑制。假设a是date格式的，那么where a+7<sysdate将不会用到索引，而where a<sysdate-7会用到索引。

还有两个特殊声明：

1).select * from AAA where a=:xxx and c=sysdate与 select * from AAA where c=sysdate and a=:xxx;都会用到索引，即与where语句中字段出现的顺序无关；

2).select * from AAA where a=:xxx and b=1;会使用索引，此时A出现，即使其他字段不是索引字段也会使用到索引

3、如何去分析一个查询语句

（1）慢SQL如何获取？

        应用程序能够感知到SQL慢，因为慢了，提供服务就慢，系统吞吐自然就上不去。这时候可以在mysql上面查看慢SQL的日志，这个文件看起来不是很方便，DBA通过工具把这些日志加工成比较容易分析，能够很直观的看出慢SQL是啥，以及SQL执行花费的时间和执行的时间点。获取了这些慢SQL，就有了分析的依据。

（2）如何分析SQL为啥慢了？

        mysql提供了一个工具来分析SQL执行查询的时候的情况，使用很简单：explain select语句

        输出的结果是表格，各个字段的解释如下：

select_type	simple：简单表，即不使用表连接或者子查询； primary：主查询 union：union中的第二个或者后面的查询语句 subquery：子查询中的第一个select
table	输出结果集的表
type	表连接的类型 system：表中仅有一行，即常量表 const：单表中最多有一个匹配行，主键或者唯一索引 eq_ref：对于前面的每一行，在此表中只查询一条记录，简单说就是多表查询中使用主键或者唯一索引 ref：和eq_ref类似，区别在于使用的是普通的索引 ref_or_null：和ref类似，区别在于查询总有NULL的查询 index_merge：索引合并优化 unique_subquery：in后面是一个查询主键的子查询 index_subquery:in后面的是一个查询普通索引的子查询 range：单表中的范围查询 index：前面的每一行，都通过查询索引获取数据 all:全表扫描，性能最差
possible_keys	查询时可能用到的索引
key	查询时实际用到的索引
key_len	索引字段的长度
ref
rows	扫描行的数量
extra	执行情况的说明和描述

4、MySQL引擎 区别

	myisam	innodb
事务	不支持	支持
外键	不支持	支持
聚集索引	非	是
最小的锁粒度	表锁	行锁	一个更新语句myisam会锁住整张表,导致其他查询和更新都会被阻塞，因此并发访问受限
保存表的具体行数	保存	不保存	select count(*) from table 时innodb需要全表扫描;而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
版本支持	5.1版本	5.5版本

• 总结：InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。使用场景，如：银行（对事务的完整性要求比较高），售票（要求实现并发控制）。
• 总结：MyISAM的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。
• 总结：MEMORY用到的很少，因为它是把数据存到内存中，如果重启或者关机，所有数据都会消失。

5、Mysql常用优化方法 有哪些

选取合适的字段属性 ; 尽量把字段设置为not null；尽量少用join，少用子查询，如果有多表业务逻辑需求，尽量使用程序解决数据整合问题，单表查询数据，然后整合，将压力放在程序上（服务层），而不是数据层；使用UNION代替手动创建的临时表； 锁定表；合理使用索引（注意sql语句的写法，尽量避免索引失效）；少用like这种查询语句，虽然很方便，但是是以消耗系统性能为代价的。

6、MySQL主从同步机制

mysql的复制原理大致如下。

(1)首先，mysql主库在事务提交时会把数据库变更作为事件Events记录在二进制文件binlog中；mysql主库上的sys_binlog控制binlog日志刷新到磁盘。

(2)主库推送二进制文件binlog中的事件到从库的中继日志relay log,之后从库根据中继日志重做数据库变更操作。通过逻辑复制，以此来达到数据一致。

Mysql通过3个线程来完成主从库之间的数据复制：其中BinLog Dump线程跑在主库上，I/O线程和SQl线程跑在从库上。当从库启动复制（start slave）时，首先创建I/O线程连接主库，主库随后创建Binlog Dump线程读取数据库事件并发给I/O线程，I/O线程获取到数据库事件更新到从库的中继日志Realy log中去，之后从库上的SQl线程读取中继日志relay log 中更新的数据库事件并应用。

7、MySQL主键，外键，唯一键，索引区别

• 主键(primary key) 能够唯一标识表中某一行的属性或属性组。一个表只能有一个主键，但可以有多个候选索引。主键常常与外键构成参照完整性约束，防止出现数据不一致。主键可以保证记录的唯一和主键域非空,数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。
• 总结：
  • 索引(index) 是用来快速地寻找那些具有特定值的记录。主要是为了检索的方便，是为了加快访问速度， 按一定的规则创建的，一般起到排序作用。所谓唯一性索引，这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。
  • 外键（foreign key） 是用于建立和加强两个表数据之间的链接的一列或多列。外键约束主要用来维护两个表之间数据的一致性。简言之，表的外键就是另一表的主键，外键将两表联系起来。一般情况下，要删除一张表中的主键必须首先要确保其它表中的没有相同外键（即该表中的主键没有一个外键和它相关联）。

主键一定是唯一性索引，唯一性索引并不一定就是主键。

一个表中可以有多个唯一性索引，但只能有一个主键。

主键列不允许空值，而唯一性索引列允许空值。

主键可以被其他字段作外键引用，而索引不能作为外键引用。

8、mysql事务特性

• 原子性(Atomicity) 

原子性是指事务包含的一系列操作要么全部成功，要么全部回滚，不存在部分成功或者部分回滚，是一个不可分割的操作整体

• 一致性(Consistency)

一致性是可以理解为事务对数据完整性约束的遵循，这些约束可能包括主键约束、唯一索引约束、外键约束等等。事务执行前后，数据都是合法的状态，不会违背任何的数据完整性

就拿转账来说，A和B加起来有5000块钱，不管A和B如何转账，转几次账，A和B加起来的钱永远都是5000块。

总之，可以理解为：一致性是为了保证数据的完整性。

• 隔离性(Isolation)

隔离性是指当多个用户并发操作数据库，比如操作同一张表，数据库为每一个用户开启的事务，不能被其他的事务所干扰或者影响，事务之间是彼此独立的。

• 永久性(Durability)

永久性是指一个事务一旦提交了，那么对数据库中数据的改变就是永久的，即使是在数据库发生故障时，也不会丢失事务提交的数据。

9、隔离级别

• READ UNCOMMITED(未提交读)

　　在RERAD UNCOMMITED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也成为脏读（Dirty Read）。这个级别会导致很多问题，从性能上说READ UNCOMMITED 不会比其他的级别好太多，但缺乏其他级别的好多好处，除非有非常必要的理由，在实际的应用中一般很少使用READ UNCOMMITED.

• READ COMMITED (提交读)

　 　大多数数据库系统的默认隔离级别都是READ COMMITED （但是MYSQL不是）。READ COMMITED 满足前面提到的隔离性的简单定义：一个事务开始时，只能看到已经提交的事务所做的修改。换句话说，一个事务从开始到提交之前，所做的任何修改对其他事务都 是不可见的。这个级别有时候也叫做不可重复的（nonerepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。

• REPEATABLE READ (可重复读)

REPEATABLE READ (可重复读) 解决了脏读问题。该级别保证了在同一个事务中多次读取同样的记录的结果是一致的。但是，理论上，可重复读隔离级别还是无法解决另一个幻读 （PhantomRead）的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读 取该范围的记录时，会产生幻行（Phantom Row）。InnoDB和XtraDB 存储引擎通过多版并发控制（MVCC ,Multivesion Concurrency Control ）解决了幻读问题。

　　 可重复读(REPEATABLE READ)是Mysql 默认的事务隔离级别，其中InnoDB主要通过使用MVVC获得高并发，使用一种被称为next-key-locking的策略来避免幻读。

• SERIALIZABLE(可串行化)

　 　SERIALIZABLE是最高的隔离级别。它通过强制事务串行，避免了前面说的幻读问题。简单的来说，SERIALIZABLE会在读的每一行数据上 都加上锁，所以可能导致大量的超时和锁征用问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况，才可考 虑用该级别。

10、谈谈悲观锁与乐观锁的区别与联系

• 悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
• 悲观锁和乐观锁的区别：
  • 两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。
  • 乐观锁： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

11 .mysql索引失效的原因

https://www.cnblogs.com/95lyj/p/9343544.html