9.Skill Check Optimizing Query Performance

What does the FORCE command do? (Choose three)

analyzes and stores key distribution statistics of a table

reorganizes data evenly across data pages

reclaims file system space used by empty pages

provides the optimizer with extra information about data distribution

forces an index rebuild even though the SQL statement does not change the table structure

produces multiple output rows when analyzing complex queries

Which logs contain queries that the MySQL server executes? (Choose two)

the Slow query log

the General query log

the Binary log

the Relay log

the Audit log

Examine this command which executes successfully: mysql> EXPLAIN SELECT emp_no, title FROM titles \G It generates this output: id: 1 select_type: SIMPLE table: titles partitions: NULL type: eq_ref … Which type of comparison plan is used to access rows?

matching a primary or unique key against a constant at the start of a query

matching one or more referenced values for equality

matching rows in a range that the named index supports

matching a single referenced value for equality

Which storage engines support the mysqlcheck client program? (Choose three)

MEMORY

ARCHIVE

MyISAM

InnoDB

MERGE

BLACKHOLE

Which materially improves overall system performance? (Choose two)

A query that executes once per minute has its response time reduced from 20 ms to 15 ms.

A query that executes once per minute has its response time reduced from 2 seconds to 1 second.

A query that executes several thousand times per minute has its response time reduced from 50 ms to 40 ms.

A query that executes several thousand times per minute has its response time reduced from 40 ms to 25 ms.

A query that executes once per minute has its response time reduced from 2 seconds to 800 ms.

Which properties are true about Invisible Indexes? (Choose three)

They provide an approximation of data distribution in indexed columns.

They analyze and store key distribution statistics of a table.

They can test the effect of removing an index on performance without dropping the index.

They can be applied to primary keys.

They can be used to facilitate a staged rollout of changes caused by index removal.

They are updated when indexed columns in their associated table is modified by DML statements.

What does the EXPLAIN command do? (Choose two)

It generates a query execution plan.

It performs data modification in a query.

It examines SQL statements beginning with the SELECT, INSERT, REPLACE, UPDATE, and DELETE statements.

It enables efficient access to rows.

It returns data from tables.

---

9.Skill Check Optimizing Query Performance

What does the FORCE command do? (Choose three)

analyzes and stores key distribution statistics of a table

reorganizes data evenly across data pages

reclaims file system space used by empty pages

provides the optimizer with extra information about data distribution

forces an index rebuild even though the SQL statement does not change the table structure

produces multiple output rows when analyzing complex queries

答案：
reorganizes data evenly across data pages
reclaims file system space used by empty pages
forces an index rebuild even though the SQL statement does not change the table structure

说明：
Rebuilding Indexes
• Use FORCE:
– Forces a rebuild even though the statement does not change the table structure
– Reorganizes data more evenly across data pages
– Reclaims file system space used by empty pages
• Rebuild full text indexes by using OPTIMIZE TABLE:
– By default, OPTIMIZE TABLE rebuilds the entire table.
– The innodb_optimize_fulltext_only option ensures that OPTIMIZE TABLE
rebuilds only the full text index

Which logs contain queries that the MySQL server executes? (Choose two)

the Slow query log

the General query log

the Binary log

the Relay log

the Audit log

答案：
the Slow query log
the General query log

说明：
Identifying Slow Queries
• Identify the queries that the server executes:
– Use the general query log and slow query log.
• Identify the queries that take a long time:
– Use the slow query log.
– Prioritize these over outlier queries or queries that you execute
infrequently.
• Find queries that execute many times:
– Regularly execute SHOW PROCESSLIST or query the Performance
Schema threads table to see currently executing statements and
their durations and to identify emerging patterns.
– Use sys.statement_analysis to view normalized statements with
aggregated statistics.
– Use the slow query log with a low threshold to record most statements

Examine this command which executes successfully: mysql> EXPLAIN SELECT emp_no, title FROM titles \G It generates this output: id: 1 select_type: SIMPLE table: titles partitions: NULL type: eq_ref … Which type of comparison plan is used to access rows?

matching a primary or unique key against a constant at the start of a query

matching one or more referenced values for equality

matching rows in a range that the named index supports

matching a single referenced value for equality

答案：
matching a single referenced value for equality

说明：

Common type Values
The type column indicates the type of comparison used by the optimizer to access rows.
• ALL: Full table scan
• index: Full index scan
• const: Matching a primary or unique key against a constant at the start of a query
• eq_ref: Matching a single referenced value (identified by the ref column) for equality
• ref: Matching one or more referenced values for equality
• range: Matching rows in a range that the named index (key) supports

Which storage engines support the mysqlcheck client program? (Choose three)

MEMORY

ARCHIVE

MyISAM

InnoDB

MERGE

BLACKHOLE

答案：
MyISAM
InnoDB
ARCHIVE

说明：
mysqlcheck Client Program
• Can be more convenient than issuing SQL statements
• Works with InnoDB, MyISAM, and ARCHIVE tables
• Three levels of checking:
– Table specific
– Database specific
– All databases
• Some mysqlcheck maintenance options:
– –analyze: Performs ANALYZE TABLE
– –check: Performs CHECK TABLE (default)
– –optimize: Performs OPTIMIZE TABLE
• Examples:
mysqlcheck -uroot -p employees employees salaries
mysqlcheck –login-path=admin –analyze –all-databases

Which materially improves overall system performance? (Choose two)

A query that executes once per minute has its response time reduced from 20 ms to 15 ms.

A query that executes once per minute has its response time reduced from 2 seconds to 1 second.

A query that executes several thousand times per minute has its response time reduced from 50 ms to 40 ms.

A query that executes several thousand times per minute has its response time reduced from 40 ms to 25 ms.

A query that executes once per minute has its response time reduced from 2 seconds to 800 ms.

答案：
A query that executes several thousand times per minute has its response time reduced from 50 ms to 40 ms.

A query that executes several thousand times per minute has its response time reduced from 40 ms to 25 ms.

Which properties are true about Invisible Indexes? (Choose three)

They provide an approximation of data distribution in indexed columns.

They analyze and store key distribution statistics of a table.

They can test the effect of removing an index on performance without dropping the index.

They can be applied to primary keys.

They can be used to facilitate a staged rollout of changes caused by index removal.

They are updated when indexed columns in their associated table is modified by DML statements.

答案：
They can test the effect of removing an index on performance without dropping the index.
They can be used to facilitate a staged rollout of changes caused by index removal.

They are updated when indexed columns in their associated table is modified by DML statements.

说明：
Invisible Indexes
• Enable you to “hide” indexes from the optimizer
• Test the effect of removing indexes on query performance
— Without making destructive changes to drop the index
— Avoids expensive operation to re-create the index if it is found to be required
• Continue to be updated by the server when data is modified by DML statements
• Cannot be applied to primary keys
• Should be marked as INVISIBLE in CREATE TABLE, ALTER TABLE, or CREATE
INDEX statements
– Can be “unhidden” by using the VISIBLE keyword

What does the EXPLAIN command do? (Choose two)

It generates a query execution plan.

It performs data modification in a query.

It examines SQL statements beginning with the SELECT, INSERT, REPLACE, UPDATE, and DELETE statements.

It enables efficient access to rows.

It returns data from tables.

答案：
It generates a query execution plan.
It examines SQL statements beginning with the SELECT, INSERT, REPLACE, UPDATE, and DELETE statements.

说明：
EXPLAIN Command
• Generates a query execution plan