下文內(nèi)容主要給大家?guī)鞰ySQL8的數(shù)據(jù)庫優(yōu)化講義，這里所講到的知識，與書籍略有不同，都是創(chuàng)新互聯(lián)專業(yè)技術(shù)人員在與用戶接觸過程中，總結(jié)出來的，具有一定的經(jīng)驗(yàn)分享價(jià)值，希望給廣大讀者帶來幫助。

創(chuàng)新互聯(lián)服務(wù)項(xiàng)目包括平果網(wǎng)站建設(shè)、平果網(wǎng)站制作、平果網(wǎng)頁制作以及平果網(wǎng)絡(luò)營銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，平果網(wǎng)站推廣取得了明顯的社會效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到平果省份的部分城市，未來相信會繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

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 對于模擬多個(gè)用戶同時(shí)對 MySQL 發(fā)起“進(jìn)攻”提供了方便。同時(shí)詳細(xì)的提供了“高負(fù)荷*** MySQL”的詳細(xì)數(shù)據(jù)報(bào)告。

#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、盡量的小，因?yàn)樗械亩壦饕紩鎯χ麈I
3、選擇一個(gè)單調(diào)自增的鍵，物理行是按照主鍵排序的

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

對于以上關(guān)于MySQL8的數(shù)據(jù)庫優(yōu)化講義，如果大家還有更多需要了解的可以持續(xù)關(guān)注我們創(chuàng)新互聯(lián)的行業(yè)推新，如需獲取專業(yè)解答，可在官網(wǎng)聯(lián)系售前售后的，希望該文章可給大家?guī)硪欢ǖ闹R更新。