PHP面试/笔试题杂记 VIII

2020-05-21 面试/笔试题阅读量

知识嘛就是常问常新.

MySQL

MySQL索引 (全文索引, 聚簇索引)

FULLTEXT

即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE INDEX创建FULLTEXT索引，要比先为一张表建立FULLTEXT然后再将数据写入的速度快很多。

HASH

Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

Hash 索引无法被用来避免数据的排序操作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

Hash 索引不能利用部分索引键查询。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

Hash 索引在任何时候都不能避免表扫描。

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

BTREE

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中

MySQL引擎 (MyISAM, InnoDB)

MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持.
MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快.
InnoDB不支持FULLTEXT类型的索引.
InnoDB中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可.
对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用.
MyISAM支持表锁，InnoDB支持行锁。

MySQL优化方式

MySQL 优化常用方法

选取最适用的字段属性
使用连接（JOIN）来代替子查询(Sub-Queries)
使用联合(UNION)来代替手动创建的临时表
事务
锁定表
使用外键
使用索引
优化的查询语句

MySQL 性能优化方案

列出mysql服务器运行各种状态值

1	show global status;

查询mysql服务器配置信息语句

1	show variables;

慢查询

mysql> show variables like 'slow%';
+---------------------+-------------------------------------------+
| Variable_name       | Value                                     |
+---------------------+-------------------------------------------+
| slow_launch_time    | 2                                         |
| slow_query_log      | ON                                        |
| slow_query_log_file | /home/mysql/data3056/mysql/slow_query.log |
+---------------------+-------------------------------------------+

mysql> show global status like 'slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 52321 |
+---------------------+-------+

连接数

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 4532  |
+-----------------+-------+

查询一下服务器响应的最大连接数：

mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 835   |
+----------------------+-------+

临时表

mysql> show global status like 'created_tmp%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Created_tmp_disk_tables | 44999364  |
| Created_tmp_files       | 1763      |
| Created_tmp_tables      | 120658849 |
+-------------------------+-----------+

查看一下mysql服务器对临时表的配置

mysql>  show variables where variable_name in ('tmp_table_size', 'max_heap_table_size');
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 67108864 |
| tmp_table_size      | 2097152  |
+---------------------+----------+

open table 情况

mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 1998  |
| Opened_tables | 7704  |
+---------------+-------+

open_tables 表示打开表的数量，opened_tables表示打开过的表数量，如果opened_tables数量过大，说明配置中 table_open_cache(5.1.3之前这个值叫做table_cache)值可能太小，我们查询一下服务器table_open_cache：

mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 2000  |
+------------------+-------+

进程使用情况

mysql> show global status like 'thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 10    |
| Threads_connected | 736   |
| Threads_created   | 85295 |
| Threads_running   | 3     |
+-------------------+-------+

threads_created表示创建过的线程数，如果发现threads_created值过大的话，表明 mysql服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，

查询服务器 thread_cache_size配置：

mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 100   |
+-------------------+-------+

查询缓存(query cache)

mysql> show global status like 'qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 3128864   |
| Qcache_hits             | 0         |
| Qcache_inserts          | 0         |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 712727780 |
| Qcache_queries_in_cache | 0         |
| Qcache_total_blocks     | 1         |
+-------------------------+-----------+

Qcache_free_blocks: 缓存中相邻内存块的个数。数目大说明可能有碎片。flush query cache会对缓存中的碎片进行整理，从而得到一个空闲块。
Qcache_free_memory: 缓存中的空闲内存
Qcache_hits: 每次查询在缓存中命中时就增大
Qcache_inserts: 每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。
Qcache_lowmem_prunes: 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks和free_memory可以告诉您属于哪种情况）
Qcache_not_cached: 不适合进行缓存的查询的数量，通常是由于这些查询不是 select 语句或者用了now()之类的函数。
Qcache_queries_in_cache: 当前缓存的查询（和响应）的数量。
Qcache_total_blocks: 缓存中块的数量。

再查询一下服务器关于query_cache的配置：

mysql> show variables like 'query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 1024    |
| query_cache_size             | 3145728 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

query_cache_limit: query_cache_limit
query_cache_min_res_unit: 缓存块的最小大小
query_cache_size: 查询缓存大小
query_cache_type: 缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询
query_cache_wlock_invalidate: 当有其他客户端正在对myisam表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。

排序使用情况

mysql> show global status like 'sort%';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| Sort_merge_passes | 3948     |
| Sort_range        | 19442930 |
| Sort_rows         | 87116099 |
| Sort_scan         | 1748111  |
+-------------------+----------+

文件打开数(open_files)

mysql> show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 10    |
+---------------+-------+

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+

比较合适的设置：open_files / open_files_limit * 100% <= 75%

表锁情况

mysql> show global status like 'table_locks%';
+-----------------------+----------+
| Variable_name         | Value    |
+-----------------------+----------+
| Table_locks_immediate | 61629615 |
| Table_locks_waited    | 77       |
+-----------------------+----------+

Table_locks_immediate: 表示立即释放表锁数
Table_locks_waited: 表示需要等待的表锁数

如果 table_locks_immediate / table_locks_waited > 5000，最好采用innodb引擎，因为innodb是行锁而myisam是表锁，对于高并发写入的应用innodb效果会好些

表扫描情况

mysql> show global status like 'handler_read%';
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| Handler_read_first    | 177188783    |
| Handler_read_key      | 3542386589   |
| Handler_read_last     | 52930        |
| Handler_read_next     | 445982431928 |
| Handler_read_prev     | 89234087     |
| Handler_read_rnd      | 2283006748   |
| Handler_read_rnd_next | 175219902622 |
+-----------------------+--------------+

服务器完成的查询请求次数:

mysql> show global status like 'com_select';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| Com_select    | 713039214 |
+---------------+-----------+

计算表扫描率:

表扫描率＝ handler_read_rnd_next / com_select

MySQL 分库分表

数据库主从

从数据库(Slave)是主数据库的备份，当主数据库(Master)变化时从数据库要更新，这些数据库软件可以设计更新周期。这是提高信息安全的手段。主从数据库服务器不在一个地理位置上，当发生意外时数据库可以保存。

主从分工

其中Master负责写操作的负载，也就是说一切写的操作都在Master上进行，而读的操作则分摊到Slave上进行。

基本过程

Mysql的主从同步就是当master（主库）发生数据变化的时候，会实时同步到slave（从库）。
主从复制可以水平扩展数据库的负载能力，容错，高可用，数据备份。
不管是delete、update、insert，还是创建函数、存储过程，都是在master上，当master有操作的时候，slave会快速的接受到这些操作，从而做同步。

用途和条件

mysql主从复制用途

实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务

主从部署必要条件：

主库开启binlog日志（设置log-bin参数）
主从server-id不同
从库服务器能连通主库

主从形式

一主一从
主主复制
一主多从—扩展系统读取的性能，因为读是在从库读取的;
多主一从—5.7开始支持
联级复制

主从同步的延迟等问题、原因及解决方案：

mysql数据库从库同步的延迟问题

MySQL数据库主从同步延迟原理mysql主从同步原理：主库针对写操作，顺序写binlog，从库单线程去主库顺序读”写操作的binlog”，从库取到binlog在本地原样执行（随机写），来保证主从数据逻辑上一致。mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率比较高，下一步，问题来了，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的，不是顺序的，成本高很多，还可能可slave上的其他查询产生lock争用，由于Slave_SQL_Running也是单线程的，所以一个DDL卡主了，需要执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延时。有朋友会问：“主库上那个相同的DDL也需要执行10分，为什么slave会延时？”，答案是master可以并发，Slave_SQL_Running线程却不可以。

MySql数据库从库同步的延迟解决方案

架构方面

业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。
单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。
服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。
不同业务的mysql物理上放在不同机器，分散压力。
使用比主库更好的硬件设备作为slave总结，mysql压力小，延迟自然会变小。

硬件方面

提高服务器性能

mysql主从同步加速

sync_binlog在slave端设置为0
–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。
直接禁用slave端的binlog
slave端，如果使用的存储引擎是innodb，innodb_flush_log_at_trx_commit =2

水平分表

例：QQ的登录表。假设QQ的用户有100亿，如果只有一张表，每个用户登录的时候数据库都要从这100亿中查找，会很慢很慢。如果将这一张表分成100份，每张表有1亿条，就小了很多，比如qq0,qq1,qq1…qq99表。

用户登录的时候，可以将用户的id%100，那么会得到0-99的数，查询表的时候，将表名qq跟取模的数连接起来，就构建了表名。比如123456789用户，取模的89，那么就到qq89表查询，查询的时间将会大大缩短。

这就是水平分割。

垂直分表

也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用的，数据较大，长度较长（比如text类型字段）的拆分到“扩展表“。一般是针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。

MySQL+如何做双机热备和负载均衡

MySQL 主从复制，双机热备

双机热备

所谓双机热备其实是一个复制的过程，复制过程中一个服务器充当主服务器，一个或多个服务器充当从服务。这个复制的过程实质上是从服务器复制主服务器上MySQL的二进制日志（bin-log），并在从服务器上还原主服务器上的sql语句操作，这样只要两个数据库的初态是一样的，就能一直同步。

实现双机热备，原理其实就是做两个机器的互相主从，我们把上述步骤主从对调，然后做一遍就能实现了双机热备了

负载均衡

mysql读写分离

mysql复制时，产生了多个数据副本（备库），为减少服务器压力，从库用于处理读操作，主库可同时处理读写是mysql集群实现读写分离的常用策略。

由于备库的复制是异步的，无法实时同步，读写分离的主要难点也在于从库上的脏数据。通常如果使用备库进行读，一般对数据的实时性要求不能太高。对此，mysql提供了几种常见的读写分离方式，例如基于查询的读写分离、基于脏数据、基于会话等，有兴趣可继续研究。

mysql设置的读写分离，减少了主库的请求量，将大量读的操作发送给从库，实现负载均衡。

修改DNS

通过n个服务器IP指定到一个域名，根据请求的不同标识特征，将请求发送给不同的IP服务器进行处理。

引入中间件

mysql官方提供了一个mysql负载的中间件，mysql_proxy，也需要在服务器上进行安装，修改配置文件（mysql的服务器IP）,实质与nginx类似，也是一个代理服务器。

数据表类型有哪些

MyISAM、InnoDB、HEAP、BOB,ARCHIVE,CSV等
MyISAM：成熟、稳定、易于管理，快速读取。一些功能不支持（事务等），表级锁。
InnoDB：支持事务、外键等特性、数据行锁定。空间占用大，不支持全文索引等。

MySQL 把一个大表拆分多个表后,如何解决跨表查询效率问题

MySQL 对于大表(千万级),要怎么优化呢?

优化SQL和索引
加缓存, Memcached, redis等
主从复制,读写分离
分库分表
分布式系统

优化

规范设计
- 配置规范
- 建表规范
- 命名规范
- 索引规范
- 应用规范
业务层优化
- 业务拆分
- 数据拆分
- 读多写少优化场景
  - 采用缓存
- 读少写多优化场景
  - 采用异步提交模式
  - 使用队列技术
  - 降低写入频率
架构层优化
管理优化
- 数据清理如何做
- 数据变更如何做
- 尽可能避免数据碎片

MySQL 的慢查询问题

其实通过慢查询日志来分析是一种比较简单的方式，如果不想看日志，可以借助工具来完成，
如mysqldumpslow, mysqlsla, myprofi, mysql-explain-slow-log, mysqllogfilter等，感觉自己来分析一个需要丰富的经验，一个浪费时间。

SQL语言分4大类请列举 ?

DDL–CREATE,DROP,ALTER

DDL 数据库定义语言 (Data Definition Language)

DML–INSERT,UPDATE,DELETE

数据库操纵语言 (Data Manipulation Language)

DQL–SELECT

数据库查询语言 (Data Query Language)

DCL–GRANT,REVOKE,COMMIT,ROLLBACK

数据库控制语言 (Data Control Language)

(mysql)请写出数据类型(int char varchar datetime text)的意思;

int : 数值类型
char : 固定长度字符串类型
varchar : 可变长度字符串类型
datetime : 时期时间类型
text : 文本类型

varchar和char有什么区别:

char 长度是固定的，不管你存储的数据是多少他都会是固定的长度。而varchar则是可变长度但他要在总长度上加1字符，这个用来存储位置。
char 固定长度，所以在处理速度上要比varchar快速很多，但是浪费存储空间，所以对存储不大，但在速度上有要求的可以使用char类型，反之可以用varchar类型来实例。