MySQL 8 OCP 188道练习题1-10

       这套题库一共188题，争取2个月内更完，部分答案存疑，欢迎留言讨论。

1.Choose the best answer.

Examine these statements and output:

mysql> GRANT PROXY ON accounting@localhost TO ' '@'%' ;

mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;

Which statement is true?

A) The user failed to define a username and the connecting username defaulted to ' '@'%'.

B) The user is authorized as the rsmith@localhost user.

C) The user is authenticated as the anonymous proxy user ' '@'%'.

D) The user is logged in with --user=accounting as an option.

E) The user is authorized as the accounting@localhost user.

答案： E

解析：本题考察PROXY用户知识点。USER()表示当前的连接用户，实际用户是accounting@localhost，CURRENT_USER()的权限是真正当前作用的用户权限。' '@'%'是匿名代理账户，代理账户类似权限集-角色的概念，实际使用不多，具体的可以参考官文proxy用户。

2.Choose two.

Which two actions can obtain information about deadlocks?

A) Run the SHOW ENGINE INNODB MUTEX command from the mysql client.

B) Enable the innodb_status_output_locks global parameter.

C) Enable the innodb_print_all_deadlocks global parameter.

D) Run the SHOW ENGINE INNODB STATUS command from the mysql client.

E) Use the sys.innodb_lock_waits view.

答案： C、D

解析：本题考察获取死锁信息的两种方式，官文链接Innodb-deadlocks。innodb_print_all_deadlocks默认为OFF状态，设置成ON后，InnoDB用户事务中的所有死锁信息将保存到error_log中；否则，只能通过“SHOW ENGINE INNODB STATUS”语句获取最近的一条死锁信息。

A.该语句显示InnoDB 互斥锁和读写锁统计信息

B.显示InnoDB mutex和rw-lock统计信息。

E.查看锁等待信息的表，并且'INNODB_LOCK_WAITS' is deprecated and will be removed in a future release.

3.Choose the best answer.

Examine this statement, which executes successfully:

You want to improve the performance of this query:

 

SELECT Name
FROM world.city
WHERE Population BETWEEN 1000000 AND 2000000;

Which change enables the query to succeed while accessing fewer rows?

A) ALTER TABLE world.city ADD INDEX (Name) ;

B) ALTER TABLE world.city ADD SPATIAL INDEX (Name) ;

C) ALTER TABLE world.city ADD FULLTEXT INDEX (Name) ;

D) ALTER TABLE world.city ADD FULLTEXT INDEX (Population) ;

E) ALTER TABLE world.city ADD SPATIAL INDEX (Population) ;

F) ALTER TABLE world.city ADD INDEX (Population) ;

答案： F

解析：本题考察SQL优化—索引，利用索引快速访问数据，具体参考官文链接
。

A、B、C显然错误，D中的FULLTEXT INDEX一般用于文本字符的查找，比如char、varchar和text字段，E中的SPATIAL INDEX（空间索引）适合精确匹配而非范围查找，用于多维数据，MyISAM和InnoDB（5.7）支持使用，强行使用会报错：

mysql> ALTER TABLE city ADD SPATIAL INDEX(Population);
ERROR 1687 (42000): A SPATIAL index may only contain a geometrical type column

答案F，The most common type of index involves a single column, storing copies of the values from that column in a data structure, allowing fast lookups for the rows with the corresponding column values. The B-tree data structure lets the index quickly find a specific value, a set of values, or a range of values, corresponding to operators such as =, >, ≤, BETWEEN, IN, and so on, in a WHERE clause.

4.Choose two.

User `fwuser`@`localhost` is registered with the MySQL Enterprise Firewall and has been granted

privileges for the SAKILA database.

Examine these commands that you executed and the results:

mysql> SELECT MODE FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS

WHERE USERHOST = 'fwuser@localhost' ;