一、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：

type：

性能：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>