mysql8 cookbook_<MySQL8 Cookbook > - 数据库优化

绝地灬酷狼 2023-01-11 13:17 110阅读 0赞

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.emp\_no,salary 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 对于模拟多个用户同时对 MySQL 发起“进攻”提供了方便。同时详细的提供了“高负荷\*\*\* MySQL”的详细数据报告。

\#mysqlslap -uroot -p --create-schema=employees --query="SELECT e.emp\_no,salary 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=50,100 --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=50,100,200 --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=";"

where、order by、group by、表连接都会使用到索引

主键的选择：

1、唯一非空

2、尽量的小，因为所有的二级索引都会存储主键