MySQL 用户,权限,引擎,优化操作

分享 123456789987654321 ⋅ 于 2022-02-16 20:15:25 ⋅ 205 阅读

1.用户管理

登录MySQL服务器

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"

1.创建用户

CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];
--CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';

2.修改用户

UPDATE mysql.user SET USER='li4' WHERE USER='wang5'; 
FLUSH PRIVILEGES;

3.删除用户

DROP USER 'kangshifu'@'localhost';

4.设置当前用户密码

--修改当前登录用户的密码
ALTER USER USER() IDENTIFIED BY '123456';
-- 修改其它用户密码
SET PASSWORD FOR 'username'@'hostname'='new_password';

2.权限管理

权限列表

mysql> show privileges;
(1) CREATE和DROP权限 ,可以创建新的数据库和表,或删除(移掉)已有的数据库和表。如果将MySQL数据库中的DROP权限      授予某用户,用户就可以删除MySQL访问权限保存的数据库。 
(2) SELECT、INSERT、UPDATE和DELETE权限 允许在一个数据库现有的表上实施操作。 
(3) SELECT权限只有在它们真正从一个表中检索行时才被用到。 
(4) INDEX权限 允许创建或删除索引,INDEX适用于已有的表。如果具有某个表的CREATE权限,就可以在CREATE TABLE语      句中包括索引定义。 
(5) ALTER权 限 可以使用ALTER TABLE来更改表的结构和重新命名表。 
(6) CREATE ROUTINE权限 用来创建保存的程序(函数和程序),ALTER ROUTINE权限用来更改和删除保存的程序,       EXECUTE权限 用来执行保存的程序。 
(7) GRANT权限 允许授权给其他用户,可用于数据库、表和保存的程序。 
(8) FILE权限 使用户可以使用LOAD DATA INFILE和SELECT ... INTO OUTFILE语句读或写服务器上的文件,任何被授      予FILE权限的用户都能读或写MySQL服务器上的任何文件(说明用户可以读任何数据库目录下的文件,因为服务器可以访问     这些文件)。

授予权限

--角色赋予用户给用户授权   ==》授权命令:  该权限如果没有该用户,则会直接新建一个用户。
GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];

--直接给用户授权
GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';

查看权限

SHOW GRANTS;
--查看某用户的全局权限
SHOW GRANTS FOR 'user'@'主机地址' ;

收回权限

REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;

--例子
--收回全库全表的所有权限 
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';
--收回mysql库下的所有表的插删改查权限 
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;

查看/设置缓冲池的大小

show variables like 'innodb_buffer_pool_size';
set global innodb_buffer_pool_size = 268435456;

3.存储引擎

1.查看存储引擎

show engines;

2.设置系统默认的存储引擎

查看默认的存储引擎

show variables like '%storage_engine%'; 
--或
SELECT @@default_storage_engine;

修改默认的存储引擎

--1.或者修改 my.cnf 文件:
SET DEFAULT_STORAGE_ENGINE=MyISAM;
--2.修改 my.cnf 文件:
    default-storage-engine=MyISAM 
    --重启服务 
    systemctl restart mysqld.service

1.创建表时指定存储引擎

CREATE TABLE 
表名( 建表语句; ) 
ENGINE = 存储引擎名称;

2.修改表的存储引擎

ALTER TABLE 表名 ENGINE = 存储引擎名称;

3.引擎分类

3.1InnoDB 引擎:

具备外键支持功能的事务存储引擎
MyISAM只缓存索引,不缓存真实数据;InnoDB不仅缓存索引还要缓存真实数据,

3.2MyISAM 引擎

主要的非事务处理存储引擎

3.3InnoDB和ACID模型

1. 原子方面 ACID的原子方面主要涉及InnoDB事务,与MySQL相关的特性主要包括:
    自动提交设置。
    COMMIT语句。
    ROLLBACK语句。
    操作INFORMATION_SCHEMA库中的表数据。
2. 一致性方面 ACID模型的一致性主要涉及保护数据不崩溃的内部InnoDB处理过程,与MySQL相关的特性主要包括:
    InnoDB双写缓存。
    InnoDB崩溃恢复。
3. 隔离方面 隔离是应用于事务的级别,与MySQL相关的特性主要包括:
    自动提交设置。
    SET ISOLATION LEVEL语句。
    InnoDB锁的低级别信息。
4. 耐久性方面 ACID模型的耐久性主要涉及与硬件配置相互影响的MySQL软件特性。由于硬件复杂多样化,耐久性方面没有具体的规则可循。与MySQL相关的特性有:
    InnoDB双写缓存,通过innodb_doublewrite配置项配置。
    配置项innodb_flush_log_at_trx_commit。
    配置项sync_binlog。
    配置项innodb_file_per_table。
    存储设备的写入缓存。
    存储设备的备用电池缓存。
    运行MySQL的操作系统。
    持续的电力供应。
    备份策略。
    对分布式或托管的应用,最主要的在于硬件设备的地点以及网络情况。

4.索引

索引按照物理实现方式,索引可以分为 2 种:聚簇(聚集)和非聚簇(非聚集)索引

4.1聚簇索引

页内 的记录是按照主键的大小顺序排成一个 '单向链表' 。
各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 '双向链表' 。

#优点:
数据访问更快 ,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以 节省了大量的io操作 。

#缺点:
插入速度严重依赖于插入顺序 ,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键
更新主键的代价很高 ,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义'主键为不可更新'
二级索引访问需要两次索引查找 ,第一次找到主键值,第二次根据主键值找到行数据

4.2索引的创建与设计原则

创建表的时候创建索引

CREATE TABLE emp( 
    emp_id INT PRIMARY KEY AUTO_INCREMENT, 
    emp_name VARCHAR(20) UNIQUE, 
    dept_id INT, 
    CONSTRAINT emp_dept_id_fk 
    FOREIGN KEY(dept_id) 
    REFERENCES dept(dept_id) 
);

显式创建表索引

CREATE TABLE table_name [col_name data_type] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]
  • UNIQUE 、 FULLTEXT和 SPATIAL 为可选参数,分别表示唯一索引、全文索引和空间索引;
  • INDEXKEY 为同义词,两者的作用相同,用来指定创建索引;
  • index_name指定索引的名称,为可选参数,如果不指定,那么MySQL默认col_name为索引名;
  • col_name 为需要创建索引的字段列,该列必须从数据表中定义的多个列中选择;
  • length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度;
  • ASCDESC指定升序或者降序的索引值存储。

4.2.1. 创建普通索引

#在book表中的year_publication字段上建立普通索引
CREATE TABLE book( 
    book_id INT ,
    comment VARCHAR(100), 
    year_publication YEAR, 
    INDEX(year_publication)
);

4.2.2创建唯一索引

CREATE TABLE test1( 
    id INT NOT NULL, 
    name varchar(30) NOT NULL, 
    UNIQUE INDEX uk_idx_id(id) 
);

4.2.3主键索引

#设定为主键后数据库会自动建立索引,innodb为聚簇索引
CREATE TABLE student ( 
    id INT(10) UNSIGNED AUTO_INCREMENT , 
    student_no VARCHAR(200), 
    student_name VARCHAR(200), 
    PRIMARY KEY(id) 
);

删除主键索引:

--修改主键索引:必须先删除掉(drop)原索引,再新建(add)索引

4.2.4创建单列索引

CREATE TABLE test2( 
    id INT NOT NULL, 
    name CHAR(50) NULL, 
    INDEX single_idx_name(name(20)) 
);

4.2.5创建组合索引

CREATE TABLE test3( 
    id INT(11) NOT NULL, 
    name CHAR(30) NOT NULL,
    age INT(11) NOT NULL, 
    info VARCHAR(255),
    INDEX multi_idx(id,name,age) 
);

4.2.6.创建全文索引

--,在表中的info字段上建立全文索引
CREATE TABLE test4( 
    id INT NOT NULL, 
    name CHAR(30) NOT NULL, 
    age INT NOT NULL, 
    info VARCHAR(255), 
    FULLTEXT INDEX futxt_idx_info(info) 
) ENGINE=MyISAM;
--全文索引用match+against方式查询:
SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);
--注意点
    --1. 使用全文索引前,搞清楚版本支持情况;
    --2. 全文索引比 like + % 快 N 倍,但是可能存在精度问题;
    --3. 如果需要全文索引的是大量数据,建议先添加数据,再创建索引。

4.2.7创建空间索引

--空间索引创建中,要求空间类型的字段必须为 '非空' 。
CREATE TABLE test5( 
    geo GEOMETRY NOT NULL,
    SPATIAL INDEX spa_idx_geo(geo) 
) ENGINE=MyISAM;

4.3 在已经存在的表上创建索引

--使用CREATE INDEX创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) 
[ASC | DESC]

4.4删除索引

DROP INDEX index_name ON table_name;

性能分析工具的使用

1.查看系统性能参数

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

'一些常用的性能参数如下': 
• 'Connections':连接MySQL服务器的次数。 
• 'Uptime':MySQL服务器的上线时间。 
• 'Slow_queries':慢查询的次数。 
• 'Innodb_rows_read':Select查询返回的行数 
• 'Innodb_rows_inserted':执行INSERT操作插入的行数 
• 'Innodb_rows_updated':执行UPDATE操作更新的行数 
• 'Innodb_rows_deleted':执行DELETE操作删除的行数 
• 'Com_select':查询操作的次数。 
• 'Com_insert':插入操作的次数。对于批量插入的 INSERT 操作,只累加一次。 
• 'Com_update':更新操作的次数。 
• 'Com_delete':删除操作的次数。

2.统计SQL的查询成本

SHOW STATUS LIKE 'last_query_cost';

3.定位执行慢的 SQL:慢查询日志

--1.开启slow_query_log 慢查询已经开启,同时文件保存在 /var/lib/mysql/atguigu02-slow.log 文件中。
mysql > set global slow_query_log='ON';
--2. 修改long_query_time阈值   设置慢查询的时间阈值
mysql > show variables like '%long_query_time%';

4.查看慢查询数目

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

5.慢查询日志分析工具:mysqldumpslow

#查看mysqldumpslow的帮助信息
mysqldumpslow --help

mysqldumpslow 命令的具体参数如下:
-a: 不将数字抽象成N,字符串抽象成S 
-s: 是表示按照何种方式排序:
    c: 访问次数
    l: 锁定时间
    r: 返回记录
    t: 查询时间
    al:平均锁定时间
    ar:平均返回记录数
    at:平均查询时间 (默认方式)
    ac:平均查询次数
-t: 即为返回前面多少条的数据;
-g: 后边搭配一个正则匹配模式,大小写不敏感的;

#工作常用参考:
#得到返回记录集最多的10个SQL 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log 
#得到访问次数最多的10个SQL 
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log 
#得到按照时间排序的前10条里面含有左连接的查询语句 
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log 
#另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现爆屏情况 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

6.关闭慢查询日志

--方式1:永久性方式
[mysqld] 
slow_query_log=OFF
    --重启MySQL服务,执行如下语句查询慢日志功能。
SHOW VARIABLES LIKE '%slow%'; --查询慢查询日志所在目录 
SHOW VARIABLES LIKE '%long_query_time%'; --查询超时时长

--方式2:临时性方式
SET GLOBAL slow_query_log=off;
    --重启MySQL服务
SHOW VARIABLES LIKE '%slow%'; 
--以及
SHOW VARIABLES LIKE '%long_query_time%';

7.查看 SQL 执行成本:SHOW PROFILE

mysql > show variables like 'profiling';
#通过设置 profiling='ON’ 来开启 show profile:
mysql > set profiling = 'ON';
mysql > show profile;
mysql> show profile cpu,block io for query 2; -- 查询第二条的参数
#show profile的常用查询参数:
① ALL:显示所有的开销信息。 
② BLOCK IO:显示块IO开销。 
③ CONTEXT SWITCHES:上下文切换开销。 
④ CPU:显示CPU开销信息。
⑤ IPC:显示发送和接收开销信息。 
⑥ MEMORY:显示内存开销信息。 
⑦ PAGE FAULTS:显示页面错误开销信息。
⑧ SOURCE:显示和Source_function,Source_file, Source_line相关的开销信息。 
⑨ SWAPS:显示交换次数开销信息。

8.分析查询语句:EXPLAIN

官网介绍
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

EXPLAIN 语句输出的各个列的作用如下:

EXPLAIN不考虑各种Cache EXPLAIN不能显示MySQL在执行查询时所作的优化工作
EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
部分统计信息是估算的,并非精确值
列名 描述
id 在一个大的查询语句中每个SELECT关键字都对应一个 唯一的id
select_ pe SELECT关键字对应的那个查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际上使用的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时,与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息
创建函数,假如报错,需开启如下命令:允许创建函数设置:--不加global只是当前窗口有效。
set global log_bin_trust_function_creators=1; 

1.table

EXPLAIN语句输出的每条记录都对应着某个单表的访问方法,该条记录的table列代表着该表的表名(有时不是真实的表名字,可能是简称)。

2.id

id如果相同,可以认为是一组,从上往下顺序执行
在所有组中,id值越大,优先级越高,越先执行
关注点:id号每个号码,表示一趟独立的查询, 一个sql的查询趟数越少越好

3.select_type

名称 描述
SIMPLE Simple SELECT (not using UNION or subqueries)
PRIMARY Outermost SELECT
UNION Second or later SELECT statement in a UNION
UNION RESULT Result of a UNION
SUBQUERY First SELECT in subquery
DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query
DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query
DERIVED Derived table
MATERIALIZED Materialized subquery
UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

4. type

完整的访问方法如下: system , const , eq_ref , ref , fulltext , ref_or_null , index_merge , unique_subquery , index_subquery , range , index , ALL 。

结果值从最好到最坏依次是: 
'system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL' 
其中比较重要的几个提取出来(见上图中的蓝色)。SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,最好是 consts级别。(阿里巴巴开发手册要求)

9.分析优化器执行计划:**trace**

SET optimizer_trace="enabled=on",end_markers_in_json=on; 
set optimizer_trace_max_mem_size=1000000;

--开启后,可分析如下语句:
SELECT INSERT REPLACE UPDATE DELETE EXPLAIN SET DECLARE CASE IFRETURN CALL

10.MySQL监控分析视图-sys schema

--Sys schema视图摘要
1. 主机相关:以host_summary开头,主要汇总了IO延迟的信息。
2. Innodb相关:以innodb开头,汇总了innodb buffer信息和事务等待innodb锁的信息。
3. I/o相关:以io开头,汇总了等待I/O、I/O使用量情况。
4. 内存使用情况:以memory开头,从主机、线程、事件等角度展示内存的使用情况
5. 连接与会话信息:processlist和session相关视图,总结了会话相关信息。
6. 表相关:以schema_table开头的视图,展示了表的统计信息。
7. 索引信息:统计了索引的使用情况,包含冗余索引和未使用的索引情况。
8. 语句相关:以statement开头,包含执行全表扫描、使用临时表、排序等的语句信息。
9. 用户相关:以user开头的视图,统计了用户使用的文件I/O、执行语句统计信息。
10. 等待事件相关信息:以wait开头,展示等待事件的延迟情况。

10.1 Sys schema视图使用场景

索引情况

#1. 查询冗余索引 
select * from sys.schema_redundant_indexes; 
#2. 查询未使用过的索引 
select * from sys.schema_unused_indexes; 
#3. 查询索引的使用情况 
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema='dbname' ;

表相关

# 1. 查询表的访问量 
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc; 
# 2. 查询占用bufferpool较多的表 
elect object_schema,object_name,allocated,data from sys.innodb_buffer_stats_by_table order by allocated limit 10; 
# 3. 查看表的全表扫描情况 
select * from sys.statements_with_full_table_scans where db='dbname';

语句相关

#1. 监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis order by exec_count desc; 
#2. 监控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query from sys.statements_with_sorting limit 1; 
#3. 监控使用了临时表或者磁盘临时表的SQL 
select db,exec_count,tmp_tables,tmp_disk_tables,query from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc;

IO相关

#1. 查看消耗磁盘IO的文件
select file,avg_read,avg_write,avg_read+avg_write as avg_iofrom sys.io_global_by_file_by_bytes order by avg_read limit 10;

Innodb 相关

--1. 行锁阻塞情况 
select * from sys.innodb_lock_waits;
版权声明:原创作品,允许转载,转载时务必以超链接的形式表明出处和作者信息。否则将追究法律责任。来自海牛部落-123456789987654321,http://hainiubl.com/topics/75822
点赞
成为第一个点赞的人吧 :bowtie:
回复数量: 0
    暂无评论~~
    • 请注意单词拼写,以及中英文排版,参考此页
    • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
    • 支持表情,可用Emoji的自动补全, 在输入的时候只需要 ":" 就可以自动提示了 :metal: :point_right: 表情列表 :star: :sparkles:
    • 上传图片, 支持拖拽和剪切板黏贴上传, 格式限制 - jpg, png, gif,教程
    • 发布框支持本地存储功能,会在内容变更时保存,「提交」按钮点击时清空
    Ctrl+Enter