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索引失效的原因