一、explain執行計劃分析

當我們的系統上線後資料庫的記錄不斷增加，之前寫的一些SQL語句或者一些ORM操作效率變得非常低。我們不得不考慮SQL最佳化，那我們如何進行最佳化呢？

總結出來就以下幾個步驟：

1、定位執行效率低的SQL語句（定位）

2、分析為什麼這段SQL執行的效率比較低（分析）

3、最後根據第二步分析的結構採取最佳化措施（解決）。

透過explain命令去檢視是如何執行查詢方法的，能夠定位到執行效率低的sql語句，分析為什麼效率低下。

例如：使用EXPLAIN+SQL查詢：

mysql> explain select * from cons1 where id<=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | cons1 | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |   10 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (5.75 sec)

各個引數詳解：

id：SELECT識別符。這是SELECT查詢序列號。這個不重要,查詢序號即為sql語句執行的順序。例如：

mysql> explain select * from cons1 where id<=(select id3 from cons2 where id=10);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | cons1 | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL  |   10 |   100.00 | Using where |
|  2 | SUBQUERY    | cons2 | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

selectype：

引數	說明
SIMPLE	簡單查詢
PRIMARY	最外層查詢
SUBQUERY	對映為子查詢
DERIVED	子查詢
UNION	聯合
UNION RESULT	使用聯合的結果

type：

引數	說明
ALL	全資料表掃描
index	全索引表掃描
RANGE	對索引列進行範圍查詢
INDEX_MERGE	合併索引，使用多個單列索引搜尋
REF	根據索引查詢一個或多個值
EQ_REF	搜尋時使用primary key 或 unique型別
CONST	常量，表最多有一個匹配行, 因為僅有一行, 在這行的列值可被最佳化器剩餘部分認為是常數,const表很快,因為它們只讀取一次。
SYSTEM	系統，表僅有一行(=系統表)。這是const聯接型別的一個特例。

效能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

效能在 range 之下基本都可以進行調優

possible_keys：提示使用哪個索引會在該表中找到行，不太重要

key：MYSQL真實使用的索引，簡單且重要

key_len：MySQL中使用索引位元組長度

ref：ref列顯示使用哪個列或常數與key一起從表中選擇行。

rows：顯示MYSQL執行查詢的行數，簡單且重要，數值越大越不好，說明沒有用好索引

Extra：包含MySQL解決查詢的詳細資訊。

    Using index：此值表示mysql 將使用覆蓋索引，以避免訪問表。

     Using where ：mysql將在儲存引擎檢索行後再進行過濾，許多where條件裡涉及索引中的列，當(並且如果)它讀取索引時，

     就能被儲存引擎檢驗，因此不是所有帶where子句的查詢都會顯示“Using where”。有時“Using where”的出現就是一個

     暗示：查詢可受益於不同的索引。

     Using temporary ：mysql對查詢結果排序時會使用臨時表。

     Using filesort：mysql會對結果使用一個外部索引排序，而不是按索引次序從表裡讀取行。Mysql有兩種檔案排序演算法，

     這兩種排序方式都可以在記憶體或者磁碟上完成，explain不會告訴你mysql將使用哪一種檔案排序，也不會告訴你排序會在

     記憶體裡還是磁碟上完成。

     Range checked for each record(index map: N)：沒有好用的索引，新的索引將在聯接的每一行上重新估算，

     N是顯示在possible_keys列中索引的點陣圖，並且是冗餘的

 二、許可權管理

mysql在給使用者授權的時候，可以在不同的級別上進行授權。

1、全域性性管理許可權（作用於整個mysql例項級別）

如：*.*代表所有資料庫的許可權

mysql> grant all on *.* to 'ljw'@'10.211.55.%';

2、資料庫級別許可權（作用於某個指定資料庫或所有資料庫上）

mysql> grant all on test.* to 'ljw'@'10.211.55.%';

3、資料庫物件級別許可權（作用於指定資料庫物件上（表、檢視等）或所有資料庫物件上）

如：

mysql> grant select, insert on test.ljw to 'ljw'@'10.211.55.%';

4、檢視使用者許可權

檢視有哪些使用者：

mysql> select user,host from mysql.user;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| sstuser       | 192.168.111.% |
| test          | 192.168.111.% |
| bkpuser       | localhost     |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
| sstuser       | localhost     |
+---------------+---------------+
7 rows in set (0.63 sec)

        檢視指定使用者許可權：

mysql> show grants for sstuser@'192.168.111.%';
+--------------------------------------------------------------------------------------------+
| Grants for sstuser@192.168.111.%                                                           |
+--------------------------------------------------------------------------------------------+
| GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'192.168.111.%' |
+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

許可權具體引數語法參考官網https://dev.mysql.com/doc/refman/8.0/en/grant.html

三、建立以及使用事件

MySQL事件排程器event_scheduler負責呼叫事件，它預設是關閉的。這個排程器不斷地監視一個事件是否要呼叫， 要建立事件，必須開啟排程器。

查詢事件排程器引數

show variables like 'event_scheduler';

若為OFF,開啟事件排程器

set global event_scheduler = on;

建立語法

CREATE 
    [DEFINER = { user | CURRENT_USER }]  
    EVENT  
    [IF NOT EXISTS]  
    event_name  
    ON SCHEDULE schedule  
    [ON COMPLETION [NOT] PRESERVE]  
    [ENABLE | DISABLE | DISABLE ON SLAVE]  
    [COMMENT 'comment']  
    DO event_body;  
   
schedule:  
    AT timestamp [+ INTERVAL interval] ...  
  | EVERY interval  
    [STARTS timestamp [+ INTERVAL interval] ...]  
    [ENDS timestamp [+ INTERVAL interval] ...]  
   
interval:  
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |  
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |  
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

名詞解釋：

event_name ：建立的event名字（唯一確定的）。

ON SCHEDULE：計劃任務。

schedule: 決定event的執行時間和頻率（注意時間一定要是將來的時間，過去的時間會出錯），有兩種形式 AT和EVERY。

[ON COMPLETION [NOT] PRESERVE]： 可選項，預設是ON COMPLETION NOT PRESERVE 即計劃任務執行完畢後自動drop該事件；ON COMPLETION  PRESERVE則不會drop掉。

[COMMENT 'comment'] ：可選項，comment 用來描述event；相當註釋，最大長度64個位元組。

[ENABLE | DISABLE] ：設定event的狀態，預設ENABLE：表示系統嘗試執行這個事件， DISABLE：關閉該事情，可以用alter修改

DO event_body: 需要執行的sql語句（可以是複合語句）。CREATE EVENT在儲存過程中使用時合法的。

1.每天凌晨2點呼叫

CREATE EVENT `NewEvent`
ON SCHEDULE EVERY 1 DAY STARTS '2021-06-17 02:00:00'
ON COMPLETION PRESERVE
ENABLE
DO
INSERT INTO events_list VALUES('event_now', NOW());

 2.每隔一秒自動呼叫e_test()儲存過程

CREATE EVENT IF NOT EXISTS event_test
ON SCHEDULE EVERY 1 SECOND
ON COMPLETION PRESERVE ENABLE
DO CALL e_test();

3.從現在開始每隔九天定時執行

CREATE EVENT EVENT1
ON SCHEDULE EVERY 9 DAY STARTS NOW() 
ON COMPLETION PRESERVE ENABLE 
DO 
BEGIN 
CALL TOTAL(); 
END

4.每個月的一號凌晨1點執行

CREATE EVENT EVENT2 
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR) 
ON COMPLETION PRESERVE ENABLE 
DO 
BEGIN 
CALL STAT(); 
END

管理事件

1.檢視所有的event

SELECT * FROM  INFORMATION_SCHEMA.EVENTS;
select * from mysql.event;

2.手動關閉（開啟）事件

ALTER EVENT e_delete_upvote DISABLE[ENABLE];

3.修改EVENT名字

ALTER EVENT olddb.myevent RENAME TO newdb.myevent;

4.刪除事件

DROP EVENT e_delete_upvote;

四、root密碼丟失處理

1.檢視mysql程式

shell> ps -ef|grep mysql
root      24571      1  0 04:00 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql     25470  24571  0 04:00 ?        00:01:25 /usr/sbin/mysqld --basedir=/usr --datadir=/mysql/data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep-provider=/usr/lib64/galera3/libgalera_smm.so --log-error=/mysql/log/error.log --open-files-limit=65535 --pid-file=/mysql/run/mysql.pid --socket=/mysql/run/mysql.sock --port=3306 --wsrep_start_position=2ac50ed4-c9d4-11eb-8299-0f79163b603a:21059
root      31302  29472  0 11:09 pts/2    00:00:00 grep --color=auto mysql

2.kill程式

shell> kill -9 24571
shell> kill -9 25470

3.跳過許可權表，啟動資料庫

shell> mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables &

4.重置root密碼

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
 
mysql> update user root set authentication_string=password('ljw') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
 
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

5.重啟資料庫

shell> mysqladmin -uroot -pljw shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
 
shell> mysqld_safe --defaults-file=/etc/my.cnf &

6.使用新密碼登陸

shell> mysql -uroot -pljw
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.26-ndb-7.6.10-cluster-gpl-log MySQL Cluster Community Server (GPL)
 
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>