MySQL高级

标签：... MySQL 高级 查询 索引 表名 权限

大小写规范

1. MySQL在Windows下是大小写不敏感的，会全部转换成小写

2. MySQL在Linux下的大小写规则：

   1. 数据库名、表名、表的别名、变量名是严格区分大小写的；

   2. 关键字、函数名称在SQL中不区分大小写

   3. 列名（或字段名）与列的别名（或字段别名）在所有的的情况下均是忽略大小写的

 

SQL编写建议

1. 关键字和函数名全部大写

2. 数据库名、表名、表别名、字段名、字段别名全部小写

3. SQL语句必须以分号结尾

 

 

用户与权限管理

1.用户管理

1. 创建用户

   create user 'zhangsan'@'localhost' identified by 'zs123'

2. 删除用户

   DROP USER user[,user...]

3. 修改密码

    ALTER USER '用户名'@'主机' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '新密码';

    

2.权限管理

MySQL有哪些权限？

 show privileges;

查看权限

1. 查看当前用户权限

    show grants;
    -- 或
    show grants for current_user;
    -- 或
    show grants for current_user();

2. 查看某用户的全局权限

    show grants for 'user'@'主机地址'

    

 

授权

授权方法有两种，分别是赋予用户角色和直接给用户授权。

命令：

 GRANT 权限1，权限2...权限n ON 数据库名.表名 TO '用户名'@'用户地址' [IDENTIFIED BY '密码']

• 如果没有发现这个用户，则会直接新建一个用户。

• 给zhangsan用户，授予test数据库下所有表的增、删、改、查的权限

   GRANT SELECT,INSERT,UPDATE,DELETE ON test.* TO 'zhangsan'@'localhost'

• 授予zhangsan用户，所有数据库的所有权限，注意：这里不包括grant权限

   GRANT ALL PRIVILEGES ON *.* TO 'zhangsan'@'localhost'

 

收回权限

收回权限命令

 REVOKE 权限1，权限2...权限n ON 数据库名.表名 FROM 用户名@用户地址

 

3.权限表

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中。权限表最重要的有

user表、db表。除此之外，还有tables_priv表、column_priv表和proc_priv表等。

 

4.角色管理

4.1创建角色

CREATE ROLE 'role_name'[@'host_name'][,'role_name'[@'host_name']]...

角色命名的规则和用户名类似。如果host_name省略，默认为%，role_name不可省略。

只要创建一个角色，系统会自动给一个USAGE权限，意思是连接登录数据库的权限。

 

4.2给角色赋予权限

GRANT 权限 ON 数据库名.表名 TO 'role_name[@'host_name']'

 

4.3查看角色权限

SHOW PRIVILEGES FOR '角色名'[@'主机名']

 

4.4回收角色权限

REVOKE 权限 ON 数据库名.表名 FROM '角色名'[@'主机名']

 

4.5删除角色

DROP 角色1[,角色2...]

 

4.6给用户赋予角色

GRANT 角色1[,角色2...] TO 用户1[,用户2...]

 

4.7查看当前角色

SELECT CURRENT_ROLE();

 

4.8激活角色

激活角色有两种方式

方式一：使用set default role命令激活

SET DEFAULT ROLE '角色名'[@'主机名'] ALL TO '用户名1'[@'主机名'],'用户名2'[@'主机名']...

方式二：将变量activate_all_roles_on_login设置为ON，意思是：对所有角色永久激活。

SET GLOBAL activate_all_roles_on_login=ON;

 

注意：

MySQL创建角色后，默认都是未激活的，也就是不能用，需要手动激活

 

 

配置文件的使用

配置文件中的启动选项被划分为若干个组，每一个组有一个组名，用[]括起来，像下面这样：

[server]
(具体的启动选项...)

[mysqld]
(具体的启动选项...)

[mysqld_safe]
(具体的启动选项...)

[client]
(具体的启动选项...)

[mysql]
(具体的启动选项...)

[mysqladmin]
(具体的启动选项...)

 

 

SQL执行流程

了解查询语句底层执行过程：select @@profiling; 或者show variables like '%profiling%'查看是否开启计划。开启它可以让MySQL收集在SQL执行时所使用的资源情况，命令如下:

select @@profiling

profiling=0代表关闭，设置为1则把profiling打开

set @@profiling=1;

执行sql操作后，执行以下命令，查看过程：

# 查看所有的执行sql
show profiles;

# 查看最后执行sql的执行过程
show profile;

# 查看指定的sql执行过程
show profile for query query_id;

还可以加上cpu、io阻塞等参数进行查询。

show profile,cpu,block io for query query_id;

 

 

存储引擎

1.查看存储引擎

• 查看mysql提供什么存储引擎：

  show engines;

• 查看默认存储引擎

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

 

2.修改存储引擎

• 修改默认存储引擎

  SET DEFAULT_STORAGE_ENGINE=MyISAM;

• 或修改my.cnf文件

  default-storage-engine=MyISAM

   

3.InnoDB引擎

• 支持外键和事务

• 数据文件结构

  • 表名.frm ：存储表结构（MySQL8.0时，合并在表名.ibd中）

  • 表名.ibd：存储数据和索引

• 对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且占用更多的磁盘空间以保存数据和索引

• 不仅缓存索引还缓存真实数据，对内存要求较高，而且内存大小对性能有决定性影响。

 

4.MyISAM引擎

• mysql5.5之前的默认存储引擎

• 优势：访问数据快，对事务完整性没有要求或以SELECT、INSERT为主的应用

• 数据文件结构

  • 表名.frm： 存储表结构

  • 表名.MYD： 存储数据（MYData）

  • 表名.MYI ：存储索引（MYIndex）

• 应用场景：只读应用或以读为主的业务

 

5.InnoDB与MyISAM对比

对比项	InnoDB	MyISAM
外键	支持	不支持
事务	支持	不支持
行表锁	行锁，操作时只锁住一行，适合高并发操作	表锁，操作一条数据，也会锁住整个表，不适合高并发情况
缓存	不仅缓存索引还缓存真实数据，对内存要求比较高，而且内存大小对性能有决定性影响	只缓存索引，不缓存真实数据
关注点	事务：并发写、更大资源	性能：节省资源、消耗小、简单事务
自带系统表使用	N	Y
默认安装	Y	Y
默认使用	Y	N

 

 

索引

1.索引的分类

MySQL索引包含：普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引等。

• 从功能逻辑上划分，有四种：普通索引、唯一索引、主键索引、全文索引。

• 从物理实现实现方式，分为：聚簇索引、非聚簇索引。

• 从作用字段个数，分为：单例索引、联合索引。

 

2.创建索引

1. 创建表CREATE TABLE中指定索引

2. 使用ALTER TABLE语句在存在的表上创建索引

3. 使用CREATE INDEX语句在已存在的表上创建索引

4. 全文索引，只能在类型为CHAR、VARCHAR、TEXT的字段上创建

   • 全文索引查询数据采用以下语句：

     SELECT * FROM table_name WHERE MATCH(列名) AGAINST ('查询字符串')

      

2.1 创建表的时候创建

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

 

2.2在已有表的时候创建

• 第一种

  ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] 索引名(列名)

• 第二种

  CREATE INDEX 索引名 ON 表名 (列名)

   

3查看索引

SHOW INDEX FROM 表名

 

4删除索引

# 通过删除主键约束的方式删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY

# 直接删除索引
ALTER TABLE 表名 DROP INDEX 索引名

# 直接删除
DROP INDEX 索引名 ON 表名

 

5.MySQL8.0索引新特性

• 支持索引降序

  CREATE INDEX 索引名 ON 表名(索引1, 索引2 DESC);

• 隐藏索引：删除索引前，先隐藏索引，验证是否有影响，无影响后再删除索引。

  ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] 索引名(列名) invisible;

  # 修改索引的可见性
  ALTER TABLE 表名 ALTER INDEX 索引名 (invisible | visible)

   

索引的设计原则

创建表

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(128) DEFAULT NULL,
  `password` varchar(128) DEFAULT NULL,
  `tenant_id` int DEFAULT NULL,
  `created_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `tenant` (
  `tenant_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`tenant_id`)
);

创建函数

# 函数一：随机产生字符串
DELIMITER //
CREATE FUNCTION rand_str(n INT) RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
		DECLARE chars_str VARCHAR(100) DEFAULT 'qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM';
		DECLARE return_str VARCHAR(255) DEFAULT '';
		DECLARE i INT DEFAULT 0;
		WHILE i < n DO
		SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOAT(1 + RAND() * 52), 1));
		SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

# 函数二：产生随机数字
DELIMITER //
CREATE FUNCTION rand_num (from_num INT, to_num INT) RETURNS INT(11)
DETERMINISTIC
BEGIN
		DECLARE i INT DEFAULT 0;
		SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1));
RETURN i;
END //
DELIMITER ;

创建存储过程

# 存储过程一：插入user表
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user`(max_num INT, tenant_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
 SET autocommit = 0;  # 设置手动提交事务
 REPEAT # 循环
 SET i = i + 1;
 INSERT INTO user(username, password, tenant_id, created_time) VALUES (rand_str(6), rand_str(10), rand_num(1, tenant_num), NOW());
 UNTIL i = max_num
 END REPEAT;
 COMMIT;  # 提交事务
 
 END
 
# 存储过程二：插入tenant表
DELIMITER //
CREATE PROCEDURE insert_tenant(max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
 SET autocommit = 0;  # 设置手动提交事务
 REPEAT # 循环
 SET i = i + 1;
 INSERT INTO tenant(name) VALUES (rand_str(6));
 UNTIL i = max_num
 END REPEAT;
 COMMIT;  # 提交事务
 
 END //
 DELIMITER ;

 

调用存储过程

call insert_tenant(100);
call insert_user(1000000, 100);

 

哪些情况适合建索引

1.字段的数值有唯一性限制

 

2.频繁作为WHERE查询条件的字段

 

3.经常GROUP BY 或 ORDER BY的列

 

4.UPDATE、DELETE 的 WHERE 条件列

如果进行更新的时候，更新的字段是非索引字段，提升的效率会更加明显，这是因为非索引字段更新时不需要对索引进行维护

 

5.DISTINCT字段需要创建索引

 

6.多表JOIN连接操作时，创建索引注意事项

1. 首先，连表数量尽量不要超过3张。

2. 其次，对WHERE条件列创建索引。

3. 最后，对于连接的字段创建索引，并且该字段在多张表中的类型必须一致。

 

7.使用列的类型小的创建索引

类型大小指的是该类型表示的数据范围的大小

 

8.使用字符串前缀创建索引

怎么计算不同长度的选择性？

先看一下字段在全部数据中的选择度：

select count(distinct 列名) / count(*) from 表名

通过不同长度去计算，与全表选择性对比

公式：

count(distinct left(列名, 索引长度)) / count(*)

例如：

select count(distinct left(address, 10)) / count(*) as sub10,    -- 截取前10个字符选择度
count(distinct left(address, 15)) / count(*) as sub10, 			 -- 截取前15个字符选择度
count(distinct left(address, 20)) / count(*) as sub10, 			 -- 截取前20个字符选择度
count(distinct left(address, 25)) / count(*) as sub10 			 -- 截取前25个字符选择度
from user;

拓展：Alibaba《Java开发手册》

【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引。

 

9.区分高度（散列性高） 的列适合做索引

公式：

select count(distinct 列名) / count(*) from 表名;

说明：越接近1越好，一般超过33%，就算是比较高的索引了。

 

10.使用最频繁的列放到联合索引的左侧

 

11.在多个字段都要创建索引的情况下，联合索引要优于单列索引。

 

12.限制索引的数量

建议单张表索引数量不超过6个。原因：

1. 索引需要占用磁盘空间，索引越多，磁盘空间占用越大。

2. 索引会影响INSERT、UPDATE、DELETE语句的性能，因为数据的更新，索引也会进行调整和更新，会造成负担。

3. 对优化器进行索引评估，降低了性能。

 

 

哪些情况不适合创建索引

1.WHERE（包括GROUP BY、ORDER BY）条件没有用到的字段不要建索引

 

2.数据量小的表不要建索引

 

3.有大量重复数据的列上不要建索引

比如重复度高于10%的时候不要创建索引

 

4.避免对经常更新的表创建过多的索引

 

5.不建议用无序的值创建索引

列如身份证、UUID（在索引比较时需要转为ASCLL，并且插入时可能造成页分裂）、MD5、HASH、无序长字符串等。

 

 

性能分析工具的使用

1.查看系统性能参数

在MySQL中可以使用SHOW STATUS语句查询MySQL数据库服务器的性能参数、执行频率。

语法：

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：插入操作的次数。对于批量操作，只累加一次

• Com_update：更新操作的次数

• Com_delete：删除操作的次数

 

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

运行时间超过long_query_time值的SQL，会被记录到慢查询日志中。

long_query_time默认值是10，意思是执行10秒以上的语句会被记录。

默认情况下，MySQL数据库没有开启慢查询日志，需要手动开启。如果不是调优需要，不建议开启改参数

，开启会有一定的性能影响。

2.1开启慢查询日志参数

使用前我们查看下是否开启：

show variables like '%slow_query_log%';

开启：

set global slow_query_log = 'ON';

 

2.2慢查询日志分析工具：mysqldumpslow（不是在mysql环境下执行）

查看命令帮助：

mysqldumpslow --hlep

 

分析查询语句：EXPLAIN

定位查询慢的SQL后，我们就可以使用 EXPLAIN 或 DESCRIBE 工具做针对性的分析查询语句。

1.能做什么？

• 表的读取顺序

• 数据读取操作的操作类型

• 哪些索引可以使用

• 哪些索引被实际使用

• 表之间的引用

• 每张表有多少行被优化器查询

 

基本语法

EXPLAIN SELECT * FROM table;
# 或
EXPLAIN UPDATE table set ...;

 

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

列名	描述
id	查询语句中select关键字对应的唯一id
select_type	SELECT关键字对于的查询类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上用到的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估需要读取的记录条数，值越小越好
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

 

小结：

• id如果相同，可以认为是一组，从上往下顺序执行

• 所有组中，id值越大，优先级越高，越先执行

• 每个id，表示一趟独立的查询，一个sql查询趟数越少越好

 

select_type

名称	描述
SIMPLE
PRIMARY
UNION
UNION RESULT
SUBQUERY
DEPENDENT SUBQUERY
DEPENDENT UNION
DERIVED
MATERIAKIZED
UNCACHEABLE SUBQUERY
UNCACHEABLE UNION

 

 

type

比如，看到type列的值为ref，表明MySQL会使用ref访问方法来执行对表的查询

完整访问方法入下：

1. system：表中只有一条记录，并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system

2. const：当我们根据主键或唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const

3. eq_ref：在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较）

4. ref：当通过普通的二级索引列与常量进行等值匹配时来查询某个表

5. fulltext

6. ref_or_null：当对普通二级索引进行等值匹配查询时，改索引列的值也可以是NULL值时

7. index_merge：有两个单列索引可以选择时

8. unique_subquery：针对于一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话

9. index_subquery

10. range：如果使用索引获取某些范围区间的记录，那么可能使用到range方法访问

11. index：当我们可以使用索引覆盖，当需要扫描全部的索引记录时，该表的访问方法就是index

12. ALL：全表扫描

 

注意：越往后，性能越低

SQL优化的目标：至少达到range级别，要求是ref级别，最好时const级别

 

possible_keys和key

可能使用到的索引和实际使用到的索引

 

 

 

EXPLAIN进一步使用

1.四种输出格式

• 传统格式

• JSON格式

  EXPLAIN FORMAT = JSON SELECT ...

• TREE格式

  EXPLAIN FORMAT = TREE SELECT ...

• 可视化输出：可以通过MySQL Workbench可视化查看MySQL的执行计划。通过点击Workbench的放大镜图标，即可生成可视化的查询计划

 

 

索引优化与查询优化

141

标签：...,MySQL,高级,查询,索引,表名,权限
From： https://www.cnblogs.com/zhouqiangshuo/p/17014857.html