Shulou(Shulou.com)06/01 Report--

The following content mainly brings you MySQL8 database optimization handouts, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

EXPLAIN

EXPLAIN

Select dept_name from dept_emp join employees on dept_emp.emp_no=employees.emp_no join departments on departments.dept_no=dept_emp.dept_no where employees.first_name='Aamer'

EXPLAIN format=JSON select dept_name from dept_emp join employees on dept_emp.emp_no=employees.emp_no join departments on departments.dept_no=dept_emp.dept_no where employees.first_name='Aamer'

Mysql > select connection_id ()

+-+

| | connection_id () |

+-+

| | 30 |

+-+

1 row in set (0.01 sec)

Mysql > explain format=json for connection 30\ G

ERROR 3012 (HY000): EXPLAIN FOR CONNECTION command is supported only for SELECT/UPDATE/INSERT/DELETE/REPLACE

Mysql > pager grep rows

Mysql > SELECT e. Emptiness no from salaries s join employees e on s.emp_no=e.emp_no where first_name='Adam'

2384 rows in set (0.24 sec)

Mysql > pager

Default pager wasn't set, using stdout.

Mysqlslap

Mysqlslap provides convenience for simulating multiple users to launch an "attack" on MySQL at the same time. At the same time, the detailed data report of "high load MySQL" is provided in detail.

# mysqlslap-uroot-p-- create-schema=employees-- query= "SELECT e.emptive no from salaries s join employees e on s.emp_no=e.emp_no where first_name='Adam';"-c 100-I 100

Enter password:

Benchmark

Average number of seconds to run all queries: 0.995 seconds

Minimum number of seconds to run all queries: 0.876 seconds

Maximum number of seconds to run all queries: 1.155 seconds

Number of clients running queries: 100

Average number of queries per client: 1

# mysqlslap-uroot-p-auto-generate-sql-- concurrency=50100-- number-of-queries=1000-I 5-- debug-info

Enter password:

Benchmark

Average number of seconds to run all queries: 0.148 seconds

Minimum number of seconds to run all queries: 0.148 seconds

Maximum number of seconds to run all queries: 0.148 seconds

Number of clients running queries: 50

Average number of queries per client: 20

Benchmark

Average number of seconds to run all queries: 0.143 seconds

Minimum number of seconds to run all queries: 0.143 seconds

Maximum number of seconds to run all queries: 0.143 seconds

Number of clients running queries: 100

Average number of queries per client: 10

Mysqlslap-uroot-p-auto-generate-sql-- only-print

# mysqlslap-uroot-p-concurrency=50100200-iterations=1-number-int-cols=4-number-char-cols=35\

Auto-generate-sql--auto-generate-sql-add-autoincrement-- auto-generate-sql-load-type=mixed-- engine=innodb-- number-of-queries=200

Enter password:

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 0.042 seconds

Minimum number of seconds to run all queries: 0.042 seconds

Maximum number of seconds to run all queries: 0.042 seconds

Number of clients running queries: 50

Average number of queries per client: 4

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 0.022 seconds

Minimum number of seconds to run all queries: 0.022 seconds

Maximum number of seconds to run all queries: 0.022 seconds

Number of clients running queries: 100

Average number of queries per client: 2

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 0.515 seconds

Minimum number of seconds to run all queries: 0.515 seconds

Maximum number of seconds to run all queries: 0.515 seconds

Number of clients running queries: 200

Average number of queries per client: 1

# mysqlslap-uroot-p-delimiter= ";"-- create= "CREATE TABLE a (b int); INSERT INTO a VALUES (23)"-- query= "SELECT * FROM a"-- concurrency=50-- iterations=200

Enter password:

Benchmark

Average number of seconds to run all queries: 0.002 seconds

Minimum number of seconds to run all queries: 0.002 seconds

Maximum number of seconds to run all queries: 0.007 seconds

Number of clients running queries: 50

Average number of queries per client: 1

Mysqlslap-concurrency=5-iterations=5-query=query.sql-create=create.sql-delimiter= ";"

Reference: https://my.oschina.net/moooofly/blog/152547

Indexes are used for where, order by, group by, and table joins.

Choice of primary key:

1. The only non-empty

Keep it as small as possible, because all secondary indexes store primary keys

3. Select a monotonous self-increasing key, and the physical rows are sorted by the primary key.

Alter table employees add [unqiue] index idx_name (first_name (10))

Alter table employees drop index idx_name

For the above MySQL8 database optimization handout, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.