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 还有两个特殊声明: 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版本 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主键,外键,唯一键,索引区别 主键一定是唯一性索引,唯一性索引并不一定就是主键。 一个表中可以有多个唯一性索引,但只能有一个主键。 主键列不允许空值,而唯一性索引列允许空值。 主键可以被其他字段作外键引用,而索引不能作为外键引用。 8、mysql事务特性 原子性是指事务包含的一系列操作要么全部成功,要么全部回滚,不存在部分成功或者部分回滚,是一个不可分割的操作整体 一致性是可以理解为事务对数据完整性约束的遵循,这些约束可能包括主键约束、唯一索引约束、外键约束等等。事务执行前后,数据都是合法的状态,不会违背任何的数据完整性 就拿转账来说,A和B加起来有5000块钱,不管A和B如何转账,转几次账,A和B加起来的钱永远都是5000块。 总之,可以理解为:一致性是为了保证数据的完整性。 隔离性是指当多个用户并发操作数据库,比如操作同一张表,数据库为每一个用户开启的事务,不能被其他的事务所干扰或者影响,事务之间是彼此独立的。 永久性是指一个事务一旦提交了,那么对数据库中数据的改变就是永久的,即使是在数据库发生故障时,也不会丢失事务提交的数据。 9、隔离级别 在RERAD UNCOMMITED级别,事务中的修改,即使没有提交,对其他事务也都是可见的。事务可以读取未提交的数据,这也成为脏读(Dirty Read)。这个级别会导致很多问题,从性能上说READ UNCOMMITED 不会比其他的级别好太多,但缺乏其他级别的好多好处,除非有非常必要的理由,在实际的应用中一般很少使用READ UNCOMMITED. 大多数数据库系统的默认隔离级别都是READ COMMITED (但是MYSQL不是)。READ COMMITED 满足前面提到的隔离性的简单定义:一个事务开始时,只能看到已经提交的事务所做的修改。换句话说,一个事务从开始到提交之前,所做的任何修改对其他事务都 是不可见的。这个级别有时候也叫做不可重复的(nonerepeatable read),因为两次执行同样的查询,可能会得到不一样的结果。 REPEATABLE READ (可重复读) 解决了脏读问题。该级别保证了在同一个事务中多次读取同样的记录的结果是一致的。但是,理论上,可重复读隔离级别还是无法解决另一个幻读 (PhantomRead)的问题。所谓幻读,指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读 取该范围的记录时,会产生幻行(Phantom Row)。InnoDB和XtraDB 存储引擎通过多版并发控制(MVCC ,Multivesion Concurrency Control )解决了幻读问题。 可重复读(REPEATABLE READ)是Mysql 默认的事务隔离级别,其中InnoDB主要通过使用MVVC获得高并发,使用一种被称为next-key-locking的策略来避免幻读。 SERIALIZABLE是最高的隔离级别。它通过强制事务串行,避免了前面说的幻读问题。简单的来说,SERIALIZABLE会在读的每一行数据上 都加上锁,所以可能导致大量的超时和锁征用问题。实际应用中也很少用到这个隔离级别,只有在非常需要确保数据的一致性而且可以接受没有并发的情况,才可考 虑用该级别。 10、谈谈悲观锁与乐观锁的区别与联系 11 .mysql索引失效的原因 https://www.cnblogs.com/95lyj/p/9343544.html
本文来自互联网用户投稿,文章观点仅代表作者本人,不代表本站立场,不承担相关法律责任。如若转载,请注明出处。 如若内容造成侵权/违法违规/事实不符,请点击【内容举报】进行投诉反馈!