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

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 为可选参数，分别表示唯一索引、全文索引和空间索引；
INDEX与KEY 为同义词，两者的作用相同，用来指定创建索引；
index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
ASC或 DESC指定升序或者降序的索引值存储。

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;

1.创建用户

2.修改用户

3.删除用户

4.设置当前用户密码

1.查看存储引擎

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

3.引擎分类

3.1InnoDB 引擎：

3.2MyISAM 引擎

3.3InnoDB和ACID模型

4.1聚簇索引

4.2索引的创建与设计原则

4.2.1. 创建普通索引

4.2.2创建唯一索引

4.2.3主键索引

4.2.4创建单列索引

4.2.5创建组合索引

4.2.6.创建全文索引

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

4.4删除索引

1.查看系统性能参数

2.统计SQL的查询成本

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

4.查看慢查询数目

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

6.关闭慢查询日志

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

8.分析查询语句：EXPLAIN

1.table

2.id

3.select_type

4. type

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

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

10.1 Sys schema视图使用场景

作者：123456789987654321

123456789987654321 的其他话题

分类下其他主题

随机推荐话题

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

1.创建用户

2.修改用户

3.删除用户

4.设置当前用户密码

1.查看存储引擎

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

3.引擎分类

3.1InnoDB 引擎：

3.2MyISAM 引擎

3.3InnoDB和ACID模型

4.1聚簇索引

4.2索引的创建与设计原则

4.2.1. 创建普通索引

4.2.2创建唯一索引

4.2.3主键索引

4.2.4创建单列索引

4.2.5创建组合索引

4.2.6.创建全文索引

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

4.4删除索引

1.查看系统性能参数

2.统计SQL的查询成本

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

4.查看慢查询数目

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

6.关闭慢查询日志

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

8.分析查询语句：EXPLAIN

1.table

2.id

3.select_type

4. type

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

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

10.1 Sys schema视图使用场景

添加附言

作者：123456789987654321

123456789987654321 的其他话题

分类下其他主题

随机推荐话题

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