Rootop 服务器运维与web架构

2020-09-18
发表者 Venus
mysql中explain分析sql执行type字段值的含义已关闭评论

mysql中explain分析sql执行type字段值的含义

# mysql5.7中type的类型达到了14种之多，这里只记录和理解最重要且经常遇见的六种类型，它们分别是all、index、range、ref、eq_ref、const。
# 从左到右，它们的效率依次是增强的。撇开sql的具体应用环境以及其他因素，应当尽量优化你的sql语句，使它的type尽量靠右
# 资料来自：https://blog.csdn.net/dennis211/article/details/78170079

创建2张表测试

CREATE TABLE `user` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `name` varchar(100) DEFAULT NULL,
   `version` int(10) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

insert into `user` (`id`, `name`, `version`) values('1','aaaaaaa','11');
insert into `user` (`id`, `name`, `version`) values('2','bbbbbb','10');

CREATE TABLE `info` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `name` varchar(100) DEFAULT NULL,
   `age` int(10) DEFAULT NULL,
   `num` int(10) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `index_num` (`num`),
   KEY `index_name` (`name`)
 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
 
insert into `info` (`id`, `name`, `age`, `num`) values('1','user1','1','11');
insert into `info` (`id`, `name`, `age`, `num`) values('2','user2','2','12');
insert into `info` (`id`, `name`, `age`, `num`) values('3','user3','3','13');
insert into `info` (`id`, `name`, `age`, `num`) values('4','user4','4','14');
insert into `info` (`id`, `name`, `age`, `num`) values('5','user5','5','15');

测试：

# type = all ，age字段无索引，则进行全表扫描
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info WHERE age = 1;'
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | info  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

# type = index 且 Extra = NULL 时，为走索引的全表扫描。这样的索引，在排序时比type = all的排序速度快。
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info ORDER BY id DESC;'
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | info  | NULL       | index | NULL          | PRIMARY | 4       | NULL |    5 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+

# type = index 且 Extra = Using index 则为索引覆盖，从索引直接获取了数据，不用查表。name字段是普通索引
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT `name` FROM info;'
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | info  | NULL       | index | NULL          | index_name | 303     | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+

# type = range ， id自增主键，范围扫描
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info WHERE id <= 100;'
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | info  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    5 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

# type = ref ， name字段普通索引，sql使用了索引，但该索引列的值并不唯一，有重复
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info WHERE `name` = "user1";'
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | info  | NULL       | ref  | index_name    | index_name | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+

# type = eq_ref ， 字段索引类型唯一，但单表测试结果是const，下面换联合查询可以。
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info WHERE num = 11;'
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | info  | NULL       | const | index_num     | index_num | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+

mysql -uroot -p"root" -e 'use aaa;EXPLAIN 
SELECT i.* 
FROM `user` AS u
LEFT JOIN info AS i 
ON u.id = i.id;'
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref      | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | index  | NULL          | PRIMARY | 4       | NULL     |    2 |   100.00 | Using index |
|  1 | SIMPLE      | i     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | aaa.u.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+------+----------+-------------+

# const ， id自增主键，精确定位
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info WHERE id = 1;'
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | info  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

2020-09-16
发表者 Venus
ldconfig配置已关闭评论

ldconfig配置

https://www.man7.org/linux/man-pages/man8/ldconfig.8.html

作用：

默认搜索/lib和/usr/lib，以及配置文件/etc/ld.so.conf内所列的目录下的库文件。

搜索出可共享的动态链接库，库文件的格式为：lib*.so*，进而创建出缓存文件。

缓存文件默认为/etc/ld.so.cache，该文件保存已排好序的动态链接库名字列表。

ldconfig通常在系统启动时运行，而当用户安装了一个新的动态链接库时，就需要手工运行这个命令。

ldconfig需要注意的地方：

1、往/lib和/usr/lib中添加库文件后需要调用下ldconfig生成最新缓存。

2、如果添加的库文件不在默认的/lib或/usr/lib中的话，就要修改/etc/ld.so.conf文件，追加库文件所在的路径，然后重新调用下ldconfig命令。
比如在安装MySQL的时候，其库文件/usr/local/mysql/lib，就需要追加到/etc/ld.so.conf文件中。命令如下：

# echo "/usr/local/mysql/lib" >> /etc/ld.so.conf
# ldconfig -v | grep mysql

3、如果添加的库文件不在/lib或/usr/lib下，且没有权限修改/etc/ld.so.conf文件的话，这时就需要export一个全局变量LD_LIBRARY_PATH，就可以了。

2020-09-16
发表者 Venus
mysql redo log 重做日志已关闭评论

mysql redo log 重做日志

资料：https://dev.mysql.com/doc/refman/5.7/en/innodb-redo-log.html
重做日志用于数据库崩溃后未写入到数据库中数据的恢复。
重做日志默认文件名叫 ib_logfile0 和 ib_logfile1两个个文件。
# 这俩文件的当前大小通过下面变量查看（单位字节）

SHOW VARIABLES LIKE 'innodb_log_file_size';

# 当前的重做日志个数通过下面变量查看

SHOW VARIABLES LIKE 'innodb_log_files_in_group';

如果想改变重做日志的数量和大小，按照以下步骤操作：
1、停止数据库并确定没有报错
2、修改my.cnf，添加

# 数量最大值100，比如这里设置为5个
innodb_log_files_in_group = 5
# 单个文件大小不能超过512GB/innodb_log_files_in_group
innodb_log_file_size = 1024000000
# 设置文件路径，默认在 SHOW VARIABLES LIKE 'datadir'; 目录下
innodb_log_group_home_dir = /usr/local/mysql/data


3、启动mysql

比如 https://www.rootop.org/pages/4797.html 这个错误就是重做日志大小发生改变，导致启动时检测到文件大小错误。

2020-09-10
发表者 Venus
mysql中创建纯数字的数据库及删除已关闭评论

mysql中创建纯数字的数据库及删除

在mysql中创建纯数字数据库是不合标准的，默认会提示语法错误，但通过mysql转义符 ` 还是可以创建。

CREATE DATABASE 222;

报错：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '222' at line 1

# 加上转义符

CREATE DATABASE `222`;

成功。

# 查询语句

SELECT * FROM 222.aaa WHERE id = 1;

报错：
错误码: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '222.aaa WHERE id = 1' at line 1

# 加上转义符查询

SELECT * FROM `222`.aaa WHERE id = 1;

可以查询

# 删除加转义符删除

DROP DATABASE `222`;

2020-09-10
发表者 Venus
mysql用户权限已关闭评论

mysql用户权限

mysql.user表用户权限相关字段的含义：

Select_priv   		  #查询
Insert_priv  		  #插入
Update_priv  		  #修改
Delete_priv   		  #删除
Create_priv   		  #创建数据库或表
Drop_priv     		  #删除数据库或表
Reload_priv   		  #执行刷新和重新加载MySQL所用各种内部缓存的特定命令,包括日志、权限、主机、查询和表重新加载权限表  
Shutdown_priv 		  #关闭MySQL服务，在将此权限提供给root账户之外的任何用户时,都应当非常谨慎 
Process_priv  		  #用户是否可以通过SHOW PROCESSLIST命令查看其他用户的进程服务器管理  
File_priv     		  #用户是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令加载服务器上的文件  
Grant_priv    		  #用户是否可以将已经授予给该用户自己的权限再授予其他用户(任何用户赋予全部已有权限
References_priv 	  #目前只是某些未来功能的占位符；现在没有作用  
Index_priv      	  #用户是否可以创建和删除表索及引用索引查询表
Alter_priv      	  #用户是否可以重命名和修改表结构
Show_db_priv    	  #用户是否可以查看服务器上所有数据库的名字,包括用户拥有足够访问权限的数据库可以考虑对所有用户禁用这个权限,除非有特别不可抗拒的原因
Super_priv      	  #用户是否可以执行某些强大的管理功能,例如通过KILL命令删除用户进程,使用SET GLOBAL修改全局MySQL变量,执行关于复制和日志的各种命令超级权限  
Create_tmp_table_priv #用户是否可以创建临时表
Lock_tables_priv      #用户是否可以使用LOCK TABLES命令阻止对表的访问/修改
Execute_priv          #用户是否可以执行存储过程此权限只在MySQL 5.0及更高版本中有意义
Repl_slave_priv		  #用户是否可以读取用于维护复制数据库环境的二进制日志文件此用户位于主系统中,有利于主机和客户机之间的通信主服务器管理 
Repl_client_priv 	  #用户是否可以确定复制从服务器和主服务器的位置从服务器管理
Create_view_priv 	  #用户是否可以创建视图此权限只在MySQL 5.0及更高版本中有意义
Show_view_priv 		  #用户是否可以查看视图或了解视图如何执行此权限只在MySQL 5.0及更高版本中有意义  
Create_routine_priv   #用户是否可以更改或放弃存储过程和函数此权限是在MySQL 5.0中引入的  
Alter_routine_priv    #用户是否可以修改或删除存储函数及函数此权限是在MySQL 5.0中引入的  
Create_user_priv      #用户是否可以执行CREATE USER命令,这个命令用于创建新的MySQL账户  
Event_priv 			  #用户是否创建、修改和删除事件这个权限是MySQL 5.1.6新增的
Trigger_priv 		  #用户是否创建和删除触发器,这个权限是MySQL 5.1.6新增的  
Create_tablespace_priv#用户是否可以创建表空间

# GRANT赋权的关键词和数据库中mysql.user表权限字段对应关系参考链接
https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

# 指定全部权限，mysql.user 表中会体现出权限控制信息
GRANT ALL PRIVILEGES ON *.* TO 'user1'@'1.1.1.1' IDENTIFIED BY '111111';

# 指定库的权限，会在 mysql.db 表中体现出精细到指定库的信息
GRANT SELECT,INSERT,UPDATE ON ops.* TO 'user1'@'%';

# 指定库指定表的权限，会在 mysql.tables_priv 表中体现出权限精细到某张表的信息
GRANT SELECT,INSERT,UPDATE ON ops.ding TO 'user1'@'%';

# 指定某表的某列权限，会在 mysql.columns.priv 表中体现出来
GRANT SELECT(`age`),UPDATE(`age`) ON aaa.info TO 'user1'@'%';
#测试
#mysql> update info set age = 1 where id =1;
#ERROR 1143 (42000): SELECT command denied to user 'user1'@'x.x.x.x' for column 'id' in table 'info'
#mysql> update info set age = 1 where age = 11;
#Query OK, 1 row affected (0.03 sec)

# 刷新内存中的权限
FLUSH PRIVILEGES;

# 查看用户权限
SHOW GRANTS FOR 'user1';


# 赋权语句解析
GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' IDENTIFIED BY '111111' WITH GRANT OPTION;

ALL PRIVILEGES：表示将所有权限赋给用户。也可指定具体的权限，如：SELECT、UPDATE、INSERT等。
ON：这些权限对哪些数据库和表生效，第一个*号是数据库名，第二个*号是表名
TO：将权限赋予哪个用户。用户名@地址，所有地址用%表示，或者可以指定具体ip、ip段、主机名。
IDENTIFIED BY：指定用户的密码。
WITH GRANT OPTION：允许用户将自己的权限赋权给其它用户。