MySQL8的數據庫優化講義

下文內容主要給大家帶來MySQL8的數據庫優化講義，這里所講到的知識，與書籍略有不同，都是億速云專業技術人員在與用戶接觸過程中，總結出來的，具有一定的經驗分享價值，希望給廣大讀者帶來幫助。

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.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=";"

參考：https://my.oschina.net/moooofly/blog/152547

where、order by、group by、表連接都會使用到索引  
主鍵的選擇：
1、唯一非空
2、盡量的小，因為所有的二級索引都會存儲主鍵
3、選擇一個單調自增的鍵，物理行是按照主鍵排序的

alter table employees add [unqiue] index idx_name(first_name(10));
alter table employees drop index idx_name;

對于以上關于MySQL8的數據庫優化講義，如果大家還有更多需要了解的可以持續關注我們億速云的行業推新，如需獲取專業解答，可在官網聯系售前售后的，希望該文章可給大家帶來一定的知識更新。