Q41.Examine these entries from the general query log:
Time Id Command Argument
2019-12-17T00:36:23.389450z 24 Connect root@localhost on mydb using SSL/TLS
2019-12-17T00:36:23.389450z 24 Query select @@version_comment limit 1
2019-12-17T00:36:23.929519z 25 Connect root@localhost on mydb using SSL/TLS
2019-12-17T00:36:23.929846z 25 Query select @@version_comment limit 1
2019-12-17T00:36:27.633082z 24 Query START TRANSACTION
2019-12-17T00:36:30.321657z 24 Query UPDATE t1 SET val =1 WHERE ID=130 //连接24 给ID=130加锁
2019-12-17T00:36:32.417433z 25 Query START TRANSACTION
2019-12-17T00:36:33.617642z 25 Query UPDATE t2 SET val =5 WHERE ID=3805 //连接25给ID=2805加锁
2019-12-17T00:36:36.045498z 25 Query UPDATE t1 SET val =10 WHERE ID=130 //连接25给ID=130加锁，但24已加锁，所以有锁等待
2019-12-17T00:36:33.617642z 25 Query UPDATE t2 SET val =5 WHERE ID=3805 //连接25给ID=3805加锁
2019-12-17T00:36:38.513674z 24 Query UPDATE t2 SET val =42 WHERE ID=3805 //连接24给ID=3805加锁，但25已加锁，所以有锁等待
All UPDATE statements reference existing rows.
Which describes the outcome of the sequence of statements?
A)A deadlock occurs after innodb_lock_wait_timeout seconds.
B)Connection 24 experiences a lock wait timeout.
C)Connection 25 experiences a lock wait timeout.
D)All statements execute without error.
E)A deadlock occurs immediately.
Answer:E
Q42.Examine this output:
Mysql>SELET FORMAT_BYTES(@@global.innodb_buffer_pool_size)AS BufferPoolSize,
@@global.innodb_buffer_pool_instances AS NumInstances,
FORMAT_BYTES(@@global.innodb_buffer_buffer_poof-chunk-size) AS Chunksize;
+----------------------+--------------------+----------------+
|BufferPoolSize | NumInstances | ChunkSize |
+----------------------+--------------------+----------------+
|12.00 GiB | 8 | 128.00 MiB |
+----------------------+--------------------+----------------+
Mysql> SELECT * FROM sys.metrics WHERE Variable_name LIKE ‘Threads%’;
+----------------------+--------------------+----------------+-------------+
|Variable_name |Variable_b=value |Type |Enabled |
+----------------------+--------------------+----------------+-------------+
|Threads_cached | 4 |Global Status |YES |
|Threads_connected | 32 |Global Status |YES |
|Threads_created | 112 |Global Status |YES |
|Threads_running | 16 |Global Status |YES |
+----------------------+--------------------+----------------+-------------+
4 rows in set (0.00 sec)
Which change should optimize the number of buffer pool instances for this workload?
A)Decrease the number of buffer pool instances to 4.
B)Increase the number of buffer pool instances to 16.//thread_running 是 16，所以将buffer pool instance增加到16个更好的匹配线程数，有助于减少线程之间的竞争
C)Increase the number of buffer pool instances to 12.
D)Decrease the number of buffer pool instances to 1.
E)Increase the number of buffer pool instances to 32.
Answer:B
innodb_buffer_pool_size=innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
12G=8*1536MBmysql> desc sys.metrics;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| Variable_name | varchar(193) | YES | | NULL | |
| Variable_value | text | YES | | NULL | |
| Type | varchar(210) | YES | | NULL | |
| Enabled | varchar(7) | NO | | | |
+----------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)variable_name:指标名称
variable_value:指标值
type:变量类型
enabled：是否被启用Threads_connected：当前连接（线程）数，这个值等于 SHOW PROCESSLIST 的总条数。
Threads_running：当前处于活跃状态的线程数，即 SHOW PROCESSLIST 中非 sleep 状态的连接数
Threads_cached：Thread Cache 缓存的线程数。
Threads_created：已创建的线程数。反映的是累计值。
Q43,Which three requirements must be enabled for group replication?
A)primary key or primary key equivalent on every table //必须要有主键
B)semi-sync replication plugin
C)binary log ROW format //binlog row模式必须要打开
D)binary log MIXED format
E)replication filters
F)binary log checksum
G)slave updates logging //该参数决定了是否将接收到的更新记录到自己的二进制中，如果是多主就必须要打开该参数，单主的主节点不打开该参数，就无法将主节点的日志传递到从库服务器
Answer:ACG
Q44.
Which two statements are true?
A)The command drops the mysql_innodb_cluster_metadata schema and re-creates it.(删除元数据但是不重建)
B)The mysql_innodb_cluster_metadata schema is dropped from the instance where the connection was established.//删除已建立连接的实例的元数据
C)Connections driven by MySQL Router are not affected by the command.（元数据已经被删掉了，所以mysql router无法找到正确的路由信息，连接就会受到影响）
D)The mysql_innodb_cluster_metadata schema is dropped from all reachable members of the cluster.//删除集群中所有可访问成员的元数据
E)Group Replication will be dissolved and all metadata purged.（删除集群元数据但是组复制是正常，如果解散集群需要执行 dissolve）
F)Group Replication is still operational, but InnoDB Cluster must be reimported under MySQL Shell.
Answer:DF
Q45.Which three commands can report all the current connections running on the MySQL server?
A)SELECT * FROM sys.metrics //查看变量的表
B)SELECT * FROM information_schema.events
C)SELECT * FROM sys.statement_analysis
D)SELECT * FROM information_schema.processlist
E)SHOW EVENTS
F)SHOW FULL PROCESSLIST
G)SELECT * FROM performance_schema.threads
H)SELECT * FROM performance_schema.events_transactions_current
Answer:DFG
Q45.Which three commands can report all the current connections running on the MySQL server?
A)SELECT * FROM sys.metrics //展示变量的信息，比如名称，类型，值，以及是否启用等
B)SELECT * FROM information_schema.events //提供事件管理器的event信息
C)SELECT * FROM sys.statement_analysis //这些视图列出的是经过规范化处理的语句以及聚合后的统计信息。
D)SELECT * FROM information_schema.processlist
E)SHOW EVENTS
F)SHOW FULL PROCESSLIST
G)SELECT * FROM performance_schema.threads
H)SELECT * FROM performance_schema.events_transactions_current //当前事务的evnet信息
Answer:DFG
Q46.Examine this statement, which executes successfully:
CREATE TABLE employess(
emp_no int unsigned NOT NULL,
Birth_date date NOT NULL,
First_name varchar(14) NOT NULL,
Last_name varvhar(16) NOT NULL,
Hire_date date NOT ULL,
PRIMARY KEY(emp_no)
)ENGIN=InnoDB;
Now examine this query:
SELET emp_no,first_name,last_name,bitrh_date
FRON employees
WHERE MONTH((birth_date)=4;
You must add an index that can reduce the number of rows processed by the query. Which two statements can do
this?
首先month()函数用于从date类型中提取月份，所以需要在where条件后面给这个MONTH(birth_date)加索引
或者使用GENERATED ALWAYS AS将MONTH(birth_date)生成一个虚拟列，然后给虚拟列添加索引
A)ALTER TABLE employees ADD INDEX ((MONTH(birth_date)));
B)ALTER TABLE employees ADD INDEX (birth_date);
C)ALTER TABLE employees ADD COLUMN birth_month tinyint unsigned GENERATED ALWAYS AS (MONTH(birth_date))
VIRTUAL NOT NULL, ADD INDEX (birth_month);
D)ALTER TABLE employees ADD INDEX (birth_month);
E)ALTER TABLE employees ADD INDEX ((CAST(birth_date->>'$.month' AS unsigned)));
F)ALTER TABLE employees ADD COLUMN birth_month tinyint unsigned GENERATED ALWAYS AS (birth_date-
>>'$month') VIRTUAL NOT NULL, ADD INDEX (birth_month);
Answer:AC
Q47.Which three are types of InnoDB tablespaces?
A)schema tablespaces
B)temporary table tablespaces
C)encryption tables
D)data tablespaces
E)redo tablespaces
F)undo tablespaces
Answer:BDF
Q48.On examination, your MySQL installation datadir has become recursively world read/write/executable. What
are two major concerns of running an installation with incorrect file privileges?
A)Data files could be deleted. (datafiles)
B)Users could overwrite configuration files.(mysqld-auto.cnf)
C)SQL injections could be used to insert bad data into the database. //此处是系统文件权限比较大，跟SQL注入没啥关系
D)Extra startup time would be required for the MySQL server to reset the privileges.（重启无法修改OS中文件的权限）
E)MySQL binaries could be damaged, deleted, or altered.（basedir 中才有 MySQL binaries是MySQL的二进制文件，题目是datadir有wrx权限，但是没说basedir有，所以这些文件不会被删除）
Answer:AB
Q49.Which two tools are available to monitor the global status of innodb locking?
A)INFORMATION_SCHEMA.INNODB_METRICS //关于一些统计信息，也有锁的
B)SHOW ENGINE INNODB STATUS; //可以查看锁的情况
C)INFORMATION_SCHEMA.STATISTICS
D)SHOW STATUS;
E)SHOW TABLE STATUS;
F)INFORMATION_SCHEMA.INNODB_TABLESTATS
Answer:AB
Q50.You want to dump all databases with names that start with "db". Which command will achieve this?
A)mysqldump --include-tables=db.% --result-file=all_db_backup.sql //我的MySQL8.0.36中没有看到--include-tables和include-databases选项
B)mysqldump --include-databases=db --result-file=all_db_backup.sql
C)mysqldump --include-databases=db% --result-file=all_db_backup.sql
D)mysqldump > all_db_backup.sql
Answer:D
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
