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