分享好友 站长动态首页 网站导航

学习 MySQL 的 28 个小技巧

2022-03-31 09:46 · 头闻号数据库

前言

无论是运维、开发、测试,还是架构师,数据库技术都是一个 必备加薪神器,那么,一直说学习数据库、学 MySQL,到底是要学习它的哪些东西呢?

一、如何快速掌握 MySQL?

1.培养兴趣

兴趣是最好的老师,不论学习什么知识,兴趣都可以极大地提高学习效率。不管学习 MySQL5.7 还是 MySQL8.0 都不例外!

2.夯实 SQL 基础

计算机领域的技术非常强调基础,刚开始学习可能还认识不到这一点。随着技术应用的深 入,只有有着扎实的基础功底,才能在技术的道路上走得更快、更远。对于 MySQL 的学习来说, SQL 语句 是其中最为基础的部分,很多操作都是通过 SQL 语句来实现的。所以在学习的过程中, 读者要多编写 SQL 语句,对于同一个功能,使用不同的实现语句来完成,从而深刻理解其不同之处。

🏆 这里可以参考文章:基础篇:数据库 SQL 入门教程

3.及时学习新知识

正确、有效地利用搜索引擎,可以搜索到很多关于 MySQL 的相关知识。同时,参考别 人解决问题的思路,也可以吸取别人的经验,及时获取最新的技术资料。

4.多实践操作

数据库系统具有极强的操作性,需要多动手上机操作。在实际操作的过程中才能发现问题, 并思考解决问题的方法和思路,只有这样才能提高实战的操作能力。

二、技巧分享

下面分享学习 MySQL 的 28 个不得不知道的小技巧!

1、MySQL 中如何使用特殊字符?

诸如单引号 ',双引号 ",反斜线 等符号,这些符号在 MySQL 中不能直接输入使用,否则会产生意料之外的结果。

举例:

假设 Lucifer 表中需要存入一行记录,值为 lucifer's dog,其中的单引号 ' 号,如果不做转义,则无法成功执行:

  1. mysql> create table lucifer (id int,name char(100)); 
  2. Query OK, 0 rows affected (0.02 sec) 
  3.  
  4. mysql> insert into lucifer values (1,'lucifer's dog'); 
  5.     '>  
  6.     '> mysql>  
  7.  
  8. ^C 
  9. mysql> 

在 MySQL 中,这些特殊字符称为转义字符,在输入时需要以反斜线符号 开头,所以在使用单引号和双引号时应分别输入 ' 或者 ",输入反斜线时应该输入 ,其他特殊字符还有回车符 r,换行符 n,制表符 tab,退格符 b 等。

  1. mysql> create table lucifer (id int,name char(100)); 
  2. Query OK, 0 rows affected (0.03 sec) 
  3.  
  4. mysql> insert into lucifer values (1,'lucifer's dog'); 
  5. Query OK, 1 row affected (0.00 sec) 
  6.  
  7. mysql> select * from lucifer; 
  8. +------+---------------+ 
  9. | id   | name          | 
  10. +------+---------------+ 
  11. |    1 | lucifer's dog | 
  12. +------+---------------+ 
  13. 1 row in set (0.00 sec) 
  14. mysql>  

📢 注意: 在向数据库中插入这些特殊字符时,一定要进行转义处理。

2、MySQL 中可以存储文件吗?

答案当然是可以的!

MySQL 中的 BLOB 和 TEXT 字段类型可以存储数据量较大的文件,可以使用这些数据类型 存储图像、声音或者是大容量的文本内容,例如网页或者文档。

  1. mysql> create table view(id int unsigned NOT NULL AUTO_INCREMENT, catid int,title varchar(256),picture MEDIUMBLOB, content TEXT,PRIMARY KEY (id)); 
  2. Query OK, 0 rows affected (0.03 sec) 
  3.  
  4. mysql> show fields from view; 
  5. +---------+--------------+------+-----+---------+----------------+ 
  6. | Field   | Type         | Null | Key | Default | Extra          | 
  7. +---------+--------------+------+-----+---------+----------------+ 
  8. | id      | int unsigned | NO   | PRI | NULL    | auto_increment | 
  9. | catid   | int          | YES  |     | NULL    |                | 
  10. | title   | varchar(256) | YES  |     | NULL    |                | 
  11. | picture | mediumblob   | YES  |     | NULL    |                | 
  12. | content | text         | YES  |     | NULL    |                | 
  13. +---------+--------------+------+-----+---------+----------------+ 
  14. 5 rows in set (0.00 sec) 
  15.  
  16. mysql>  

虽然使用 BLOB 或者 TEXT 可 以存储大容量的数据,但是对这些字段的处理会降低数据库的性能。

📢 注意: 如果并非必要,可以选择只储存文件的路径。

3、MySQL 中如何执行区分大小写的字符串比较?

MySQL 是 不区分大小写 的,因此字符串比较函数也不区分大小写。

  1. mysql> select 'TRUE' from dual where 'DOG' = 'dog'; 
  2. +------+ 
  3. | TRUE | 
  4. +------+ 
  5. | TRUE | 
  6. +------+ 
  7. 1 row in set (0.00 sec) 

如果想执行区分大小写的比较,可以在字符串前面添加 BINARY 关键字。

  1. mysql> select 'TRUE' from dual where BINARY'DOG' = 'dog'; 
  2. Empty set (0.00 sec) 
  3.  
  4. mysql>  

例如默认情况下,’DOG‘=’dog‘ 返回结果为 TRUE,如果使用 BINARY 关键字,BINARY’DOG’=‘dog’ 结果为 FALSE,在区分大小写的情况下,’DOG’ 与 ’dog’ 并不相同。

4、如何从日期时间值中获取年、月、日等部分日期或时间值?

MySQL 中,日期时间值以字符串形式存储在数据表中,因此可以使用字符串函数分别截取日期时间值的不同部分。

  1. mysql> create table lucifer(date date); 
  2. Query OK, 0 rows affected (0.04 sec) 
  3.  
  4. mysql> show fields from lucifer; 
  5. +-------+------+------+-----+---------+-------+ 
  6. | Field | Type | Null | Key | Default | Extra | 
  7. +-------+------+------+-----+---------+-------+ 
  8. | date  | date | YES  |     | NULL    |       | 
  9. +-------+------+------+-----+---------+-------+ 
  10. 1 row in set (0.00 sec) 
  11.  
  12. mysql> insert into lucifer values (now()); 
  13. Query OK, 1 row affected, 1 warning (0.00 sec) 
  14.  
  15. mysql> select * from lucifer; 
  16. +------------+ 
  17. | date       | 
  18. +------------+ 
  19. | 2021-11-25 | 
  20. +------------+ 
  21. 1 row in set (0.00 sec) 

例如某个名称为 date 的字段有值 2021-11-25,如果只需要获得年值,可以输入 LEFT(date, 4),这样就获得了字符串左边开始长度为 4 的子字符串,即 YEAR 部分的值;

  1. mysql> select LEFT(date, 4) from lucifer; 
  2. +---------------+ 
  3. | LEFT(date, 4) | 
  4. +---------------+ 
  5. | 2021          | 
  6. +---------------+ 
  7. 1 row in set (0.00 sec) 

如果要获取月份值,可以输入 MID(date,6,2),字符串第 6 个字符开始,长度为 2 的子字符串正好为 date 中的月份值。同理,读者可以根据其他日期和时间的位置,计算并获取相应的值。

  1. mysql> select MID(date,6,2) from lucifer; 
  2. +---------------+ 
  3. | MID(date,6,2) | 
  4. +---------------+ 
  5. | 11            | 
  6. +---------------+ 
  7. 1 row in set (0.00 sec) 

5、如何改变默认的字符集?

ConVERT() 函数改变指定字符串的默认字符集!

MySQL 的安装和配置过程中,其中的一个步骤是可以选择 MySQL 的默认字符集。但是,如果只改变字符集,没有必要把配置过程重新执行一遍,在这里,一个简单的方式是 修改配置文件。

读者可以在修改字符集时使用 SHOW VARIABLES LIKE 'character_set_%'; 或者 status 命令查看当前字符集,以进行对比。

  1. mysql> SHOW VARIABLES LIKE 'character_set_%'; 
  2. +--------------------------+----------------------------+ 
  3. | Variable_name            | Value                      | 
  4. +--------------------------+----------------------------+ 
  5. | character_set_client     | latin1                     | 
  6. | character_set_connection | latin1                     | 
  7. | character_set_database   | utf8mb3                    | 
  8. | character_set_filesystem | binary                     | 
  9. | character_set_results    | latin1                     | 
  10. | character_set_server     | utf8mb4                    | 
  11. | character_set_system     | utf8mb3                    | 
  12. | character_sets_dir       | /usr/share/mysql/charsets/ | 
  13. +--------------------------+----------------------------+ 
  14. 8 rows in set (0.00 sec) 
  15.  
  16. mysql> status 
  17. -------------- 
  18. mysql  Ver 8.0.26-0ubuntu0.21.04.3 for Linux on aarch64 ((Ubuntu)) 
  19.  
  20. Connection id:          10 
  21. Current database: 
  22. Current user:           root@localhost 
  23. SSL:                    Not in use 
  24. Current pager:          stdout 
  25. Using outfile:          '' 
  26. Using delimiter:        ; 
  27. Server version:         8.0.26-0ubuntu0.21.04.3 (Ubuntu) 
  28. Protocol version:       10 
  29. Connection:             Localhost via UNIX socket 
  30. Server characterset:    utf8mb4 
  31. Db     characterset:    utf8mb4 
  32. Client characterset:    latin1 
  33. Conn.  characterset:    latin1 
  34. UNIX socket:            /var/run/mysqld/mysqld.sock 
  35. Binary data as:         Hexadecimal 
  36. Uptime:                 36 min 55 sec 
  37.  
  38. Threads: 2  Questions: 325  Slow queries: 0  Opens: 181  Flush tables: 3  Open tables: 69  Queries per second avg: 0.146 
  39. -------------- 
  40.  
  41. mysql>  

MySQL 配置文件名称为 my.cnf,该文件在 MySQL 的安装目录下面。修改配置文件中的 default-character-set 和 character-set-server 参数值,将其改为想要的字符集名称,如 gbk、gb2312、latinl 等,修改完之后重新启动 MySQL 服务,即可生效。

  1. ## 找到 my.cnf 位置 
  2. root@modb:~# find /etc -iname my.cnf -print 
  3. /etc/alternatives/my.cnf 
  4. /etc/mysql/my.cnf 
  5.  
  6. ## 修改字符集 
  7. 在[client ]下面加入 
  8. default-character-set=utf8 
  9. 在[ mysqld ] 下面加 
  10. character_set_server=utf8 
  11.  
  12. ## 重启 mysql 生效 
  13. service mysql restart 

此时,登录 MySQL 后使用 SHOW VARIABLES LIKE 'character_set_%'; 或者 status 命令查看修改结果!

6、DISTINCT 可以应用于所有的列吗?

查询结果中,如果需要对列进行降序排序,可以使用 DESC,这个关键字只能对其前面的列 进行降序排列。

  1. mysql> select * from lucifer; 
  2. +------+----------+ 
  3. | id   | name     | 
  4. +------+----------+ 
  5. |    1 | lucifer  | 
  6. |    2 | lucifer1 | 
  7. |    3 | lucifer2 | 
  8. +------+----------+ 
  9. 3 rows in set (0.00 sec) 
  10.  
  11. mysql> select * from lucifer order by id desc; 
  12. +------+----------+ 
  13. | id   | name     | 
  14. +------+----------+ 
  15. |    3 | lucifer2 | 
  16. |    2 | lucifer1 | 
  17. |    1 | lucifer  | 
  18. +------+----------+ 
  19. 3 rows in set (0.00 sec) 

例如,要对多列都进行降序排序,必须要在每一列的列名后面加 DESC 关键字。

  1. mysql> select * from lucifer order by id desc,name desc; 
  2. +------+----------+ 
  3. | id   | name     | 
  4. +------+----------+ 
  5. |    3 | lucifer2 | 
  6. |    2 | lucifer1 | 
  7. |    1 | lucifer  | 
  8. +------+----------+ 
  9. 3 rows in set (0.00 sec) 

而 DISTINCT 不同,DISTINCT 不能部分使用。换句话说,DISTINCT 关键字应用于所有列而不仅是它后面的第一个指定列。

例如,查询 2 个字段 sex,age,如果不同记录的这 2 个字段的组合值都不同,则所有记录都会被查询出来。

  1. mysql> select * from lucifer; 
  2. +------+-----------+--------+------+ 
  3. | id   | name      | sex    | age  | 
  4. +------+-----------+--------+------+ 
  5. |    1 | xiaoli    | male   |   20 | 
  6. |    1 | xiaoliu   | female |   21 | 
  7. |    1 | xiaozhang | female |   21 | 
  8. |    1 | xiaowu    | female |   21 | 
  9. +------+-----------+--------+------+ 
  10. 4 rows in set (0.00 sec) 
  11.  
  12. mysql> select distinct sex,age from lucifer; 
  13. +--------+------+ 
  14. | sex    | age  | 
  15. +--------+------+ 
  16. | male   |   20 | 
  17. | female |   21 | 
  18. +--------+------+ 
  19. 2 rows in set (0.00 sec) 
  20.  
  21. mysql>  

7、ORDER BY 可以和 LIMIT 混合使用吗?

在使用 ORDER BY 子句时,应保证其位于 FROM 子句之后,如果使用 LIMIT,则必须位于 ORDER BY 之后,如果子句顺序不正确,MySQL 将产生错误消息。

✅ 正确用法:

  1. mysql> select * from lucifer order by age desc limit 2,4; 
  2. +------+--------+--------+------+ 
  3. | id   | name   | sex    | age  | 
  4. +------+--------+--------+------+ 
  5. |    1 | xiaowu | female |   21 | 
  6. |    1 | xiaoli | male   |   20 | 
  7. +------+--------+--------+------+ 
  8. 2 rows in set (0.00 sec) 

❎ 错误用法:

  1. mysql> select * from lucifer limit 2,4 order by age desc; 
  2. ERROR 1064 (42000): 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 'order by age desc' at line 1 
  3. mysql>  

8、什么时候使用引号?

在查询的时候,会看到在 WHERE 子句中使用条件,有的值加上了单引号,而有的值未加。

  1. mysql> select * from lucifer where sex = 'female'; 
  2. +------+-----------+--------+------+ 
  3. | id   | name      | sex    | age  | 
  4. +------+-----------+--------+------+ 
  5. |    1 | xiaoliu   | female |   21 | 
  6. |    1 | xiaozhang | female |   21 | 
  7. |    1 | xiaowu    | female |   21 | 
  8. +------+-----------+--------+------+ 
  9. 3 rows in set (0.00 sec) 
  10.  
  11. mysql>  

单引号用来限定字符串,如果将值与字符串类型列进行比较,则需要限定引号;而用来与数值进行比较则不需要用引号。

  1. mysql> select * from lucifer where age = 20; 
  2. +------+--------+------+------+ 
  3. | id   | name   | sex  | age  | 
  4. +------+--------+------+------+ 
  5. |    1 | xiaoli | male |   20 | 
  6. +------+--------+------+------+ 
  7. 1 row in set (0.00 sec) 
  8.  
  9. mysql>  

9、在 WHERE子句中 AND 和 OR 必须使用圆括号吗?

任何时候使用具有 AND 和 OR 操作符的 WHERE 子句,都应该使用圆括号明确操作顺序。

  1. mysql> select * from lucifer where (age = 20 or sex = 'female') and name != 'xiaowu'; 
  2. +------+-----------+--------+------+ 
  3. | id   | name      | sex    | age  | 
  4. +------+-----------+--------+------+ 
  5. |    1 | xiaoli    | male   |   20 | 
  6. |    1 | xiaoliu   | female |   21 | 
  7. |    1 | xiaozhang | female |   21 | 
  8. +------+-----------+--------+------+ 
  9. mysql> 3 rows in set (0.00 sec) 

如果条件较多,即使能确定计算次序,默认的计算次序也可能会使 SQL 语句不易理解,因此使 用括号明确操作符的次序,是一个好的习惯。

10、更新或者删除表时必须指定 WHERE子 句吗?

个人建议所有的 UPDATE 和 DELETE 语句全都在 WHERE 子句中指定条件。

  1. mysql> update lucifer set age = 22 where name = 'xiaoliu'; 
  2. Query OK, 1 row affected (0.01 sec) 
  3. Rows matched: 1  Changed: 1  Warnings: 0 
  4.  
  5. mysql> select * from lucifer where name = 'xiaoliu'; 
  6. +------+---------+--------+------+ 
  7. | id   | name    | sex    | age  | 
  8. +------+---------+--------+------+ 
  9. |    1 | xiaoliu | female |   22 | 
  10. +------+---------+--------+------+ 
  11. 1 row in set (0.00 sec) 
  12.  
  13. mysql>  

如果省略 WHERE 子句,则 UPDATE 或 DELETE 将被应用到表中所有的行。

  1. mysql> update lucifer set age = 22; 
  2. Query OK, 3 rows affected (0.01 sec) 
  3. Rows matched: 4  Changed: 3  Warnings: 0 
  4.  
  5. mysql> select * from lucifer; 
  6. +------+-----------+--------+------+ 
  7. | id   | name      | sex    | age  | 
  8. +------+-----------+--------+------+ 
  9. |    1 | xiaoli    | male   |   22 | 
  10. |    1 | xiaoliu   | female |   22 | 
  11. |    1 | xiaozhang | female |   22 | 
  12. |    1 | xiaowu    | female |   22 | 
  13. +------+-----------+--------+------+ 
  14. 4 rows in set (0.00 sec) 
  15.  
  16. mysql>  

因此,除非确实打算更新或者删除所有记录,否则要注意使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。

📢 注意: 建议在对表进行更新和删除操作之前,使用 SELECT 语句确认需要删除的记录,以免造成无法挽回的结果。

11、索引对数据库性能如此重要,应该如何使用它?

索引的优点:

缺点:

使用索引时,需要综合考虑索引的优点和缺点。

为数据库选择正确的索引是一项复杂的任务。如果索引列较少,则需要的磁盘空间和维护开销 都较少。如果在一个大表上创建了多种组合索引,索引文件也会膨胀很快。

而另一方面,索引较多 可覆盖更多的查询。可能需要试验若干不同的设计,才能找到最有效的索引。可以添加、修改和删 除索引而不影响数据库架构或应用程序设计。

因此,应尝试多个不同的索引从而建立最优的索引。

12、尽量使用短索引(前缀索引)

对字符串类型的字段进行索引,如果可能应该指定一个前缀长度。

例如,如果有一个 CHAR(255) 的列,如果在前 10 个或 30 个字符内,多数值是惟一的,则不需要对整个列进行索引。

  1. mysql> select * from lucifer; 
  2. +------+-----------+--------+------+ 
  3. | id   | name      | sex    | age  | 
  4. +------+-----------+--------+------+ 
  5. |    1 | xiaoli    | male   |   22 | 
  6. |    1 | xiaoliu   | female |   22 | 
  7. |    1 | xiaozhang | female |   22 | 
  8. |    1 | xiaowu    | female |   22 | 
  9. +------+-----------+--------+------+ 
  10. 4 rows in set (0.00 sec) 
  11.  
  12. mysql> create index idx_lucifer_name on lucifer (name(4)); 
  13. Query OK, 0 rows affected (0.03 sec) 
  14. Records: 0  Duplicates: 0  Warnings: 0 
  15.  
  16. mysql> show index from lucifer; 
  17. +---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 
  18. | Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | expression | 
  19. +---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 
  20. | lucifer |          1 | idx_lucifer_name |            1 | name        | A         |           1 |        4 |   NULL | YES  | BTREE      |         |               | YES     | NULL       | 
  21. +---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 
  22. 1 row in set (0.01 sec) 
  23.  
  24. mysql>  

短索引不仅可以提高查询速度而且可以节省磁盘空间、减少 I/O 操作。

13、MySQL 存储过程和函数有什么区别?

在本质上它们都是存储程序。

函数:

存储过程:

14、存储过程中的内容可以改变吗?

不可以!

目前,MySQL 还不提供对已存在的存储过程代码的修改,如果必须要修改存储过程,必须使用 DROP 语句删除之后,再重新编写代码,或者创建一个新的存储过程。

不得不说,这方面还是 Oracle 做的比较好。

15、存储过程中可以调用其他存储过程吗?

可以!

存储过程包含用户定义的 SQL 语句集合,可以使用 CALL 语句调用存储过程,当然在存储过程中也可以使用 CALL 语句调用其他存储过程,但是不能使用 DROP 语句删除其他存储过程。

16、存储过程的参数不要与数据表中的字段名相同。

在定义存储过程参数列表时,应注意把参数名与数据库表中的字段名区别开来,否则将出 现无法预期的结果。

17、存储过程的参数可以使用中文吗?

一般情况下,可能会出现存储过程中传入中文参数的情况,例如某个存储过程根据用户的 名字查找该用户的信息,传入的参数值可能是中文。这时需要在定义存储过程的时候,在后面加 上 character set gbk,不然调用存储过程使用中文参数会出错,比如定义 userInfo 存储过程,代码 如下:

CREATE PROCEDURE useInfo(IN u_name VARCHAR(50) character set gbk, OUT u_age INT)

18、MySQL 中视图和表的区别以及联系是什么?

两者的区别:

两者的联系:

视图(view)是在基本表之上建立的表,它的结构(即所定义的列)和内容(即所有记录) 都来自基本表,它依据基本表存在而存在。

一个视图可以对应一个基本表,也可以对应多个基本表。

视图是基本表的抽象和在逻辑意义上建立的新关系。

19、使用触发器时须特别注意!

在使用触发器的时候需要注意,对于相同的表,相同的事件只能创建一个触发器。

  1. mysql> create trigger lucifer_tri before insert on lucifer for each row set NEW.id=NEW.id+1; 
  2. Query OK, 0 rows affected (0.01 sec) 
  3.  
  4. mysql>  
  5. mysql>  
  6. mysql> select * from lucifer; 
  7. +------+-----------+--------+------+ 
  8. | id   | name      | sex    | age  | 
  9. +------+-----------+--------+------+ 
  10. |    1 | xiaoli    | male   |   22 | 
  11. |    1 | xiaoliu   | female |   22 | 
  12. |    1 | xiaozhang | female |   22 | 
  13. |    1 | xiaowu    | female |   22 | 
  14. |    1 | lucifer   | male   |   20 | 
  15. |    1 | lucifer   | male   |   20 | 
  16. +------+-----------+--------+------+ 
  17. 6 rows in set (0.00 sec) 
  18.  
  19. mysql> insert into lucifer values(1,'lucifer','male',20); 
  20. Query OK, 1 row affected (0.00 sec) 
  21.  
  22. mysql> select * from lucifer; 
  23. +------+-----------+--------+------+ 
  24. | id   | name      | sex    | age  | 
  25. +------+-----------+--------+------+ 
  26. |    1 | xiaoli    | male   |   22 | 
  27. |    1 | xiaoliu   | female |   22 | 
  28. |    1 | xiaozhang | female |   22 | 
  29. |    1 | xiaowu    | female |   22 | 
  30. |    1 | lucifer   | male   |   20 | 
  31. |    1 | lucifer   | male   |   20 | 
  32. |    2 | lucifer   | male   |   20 | 
  33. +------+-----------+--------+------+ 
  34. 7 rows in set (0.00 sec) 

比如对表 lucifer 创建了一个 BEFORE INSERT 触发器,那么如果对表 lucifer 再次创建一个 BEFORE INSERT 触发器,MySQL 将会报错,此时,只可以在表 lucifer 上创建 AFTER INSERT 或者 BEFORE UPDATE 类型的触发器。

  1. mysql> create trigger lucifer_tri before insert on lucifer for each row set NEW.id=NEW.id+1; 
  2. ERROR 1359 (HY000): Trigger already exists 
  3. mysql>  

灵活的运用触发器将为操作省去很多麻烦。

20、及时删除不再需要的触发器触发器

定义之后,每次执行触发事件,都会激活触发器并执行触发器中的语句。

如果需求发生变化,而触发器没有进行相应的改变或者删除,则触发器仍然会执行旧的语句,从而会影响新的数据的完整性。

  1. mysql> drop trigger lucifer_tri; 
  2. Query OK, 0 rows affected (0.03 sec) 
  3.  
  4. mysql>  

因此,要将不再使用的触发器及时删除。

21、应该使用哪种方法创建用户?(3种方式)

创建用户有 3 种方法:

一般情况, 最好使用 GRANT 或者 CREATE USER 语句,而不要直接将用户信息插入 user 表,因为 user 表中存储了全局级别的权限以及其他的账户信息,如果意外破坏了 user 表中的记录,则可能会对 MySQL 服务器造成很大影响。

  1. -- 使用 CREATE USER 语句创建用户 
  2. mysql> create user 'lucifer'@'localhost' identified by 'lucifer'; 
  3. Query OK, 0 rows affected (0.01 sec) 
  4.  
  5. mysql>  
  6.  
  7. -- 在 mysql.user 表中添加用户 
  8. mysql> select MD5('lucifer'); 
  9. +----------------------------------+ 
  10. | MD5('lucifer')                   | 
  11. +----------------------------------+ 
  12. | cae33a0264ead2ddfbc3ea113da66790 | 
  13. +----------------------------------+ 
  14. 1 row in set (0.00 sec) 
  15.  
  16. mysql>  
  17. mysql> INSERT INTO mysql.user(Host, User, authentication_string, ssl_cipher, ssuex509_i09_sr, x5ubject) VALUES ('lohoscalt',uci 'lfer MD5('1',lucifer'), '', '',; '') 
  18. Query OK, 1 row affected (0.01 sec) 
  19.  
  20. mysql>  
  21.  
  22. -- 使用 GRANT 语句创建用户 
  23. mysql> GRANT SELECT ON*.* TO 'lucifer2'@localhost IDENTIFIED BY 'lucifer'; 
  24. ERROR 1064 (42000): 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 'IDENTIFIED BY 'lucifer'' at line 1 
  25. mysql> 

📢 注意: 由于测试使用的是 MySQL 8 版本,已经不支持 GRANT 直接创建用户,5.7 版本依然是支持的。

22、mysqldump 备份的文件只能在 MySQL 中使用吗?

逻辑备份工具,适用于所有的存储引擎,支持温备、完全备份、部分备份、对于 InnoDB 存储引擎支持热备。

mysqldump 备份的文本文件实际是数据库的一个副本,使用该文件不仅可以在 MySQL 中恢复数据库,而且通过对该文件的简单修改,可以使用该文件在 SQL Server 或者 Sybase 等其他数据库中恢复数据库。

  1. root@modb:~# mysqldump -uroot -p hr > /root/hr.db 
  2. Enter password:  
  3. root@modb:~#  
  4. root@modb:~# ll hr.db  
  5. -rw-r--r-- 1 root root 25327 Nov 26 08:52 hr.db 

这在某种程度上实现了数据库之间的迁移。

23、如何选择备份工具?

根据备份的方法(是否需要数据库离线)可以将备份分为:

MySQL 中进行不同方式的备份还要考虑存储引擎是否支持,如 MyISAM 不支持热备,支持温备和冷备。而 InnoDB 支持热备、温备和冷备。

一般情况下,我们需要备份的数据分为以下几种:

下面是几种常用的备份工具:

直接复制数据文件是最为直接、快速的备份方法,但缺点是基本上不能实现增量备份。备份时必须确保没有使用这些表。如果在复制一个表的同时服务器正在修改它,则复制无效。备份 文件时,最好关闭服务器,然后重新启动服务器。

24、平时应该打开哪些日志?

日志既会影响 MySQL 的性能,又会占用大量磁盘空间。因此,如果不必要,应尽可能少地 开启日志。

根据不同的使用环境,可以考虑开启不同的日志。

例如,在开发环境中优化查询效率低的语句,可以开启慢查询日志;

开启慢查询日志: 可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。

  1. -- 检查是否开启慢查询 
  2. mysql> show variables like 'slow_query%'; 
  3. +---------------------+------------------------------+ 
  4. | Variable_name       | Value                        | 
  5. +---------------------+------------------------------+ 
  6. | slow_query_log      | OFF                          | 
  7. | slow_query_log_file | /var/lib/mysql/modb-slow.log | 
  8. +---------------------+------------------------------+ 
  9. 2 rows in set (0.00 sec) 
  10.  
  11. mysql> show variables like 'long_query_time'; 
  12. +-----------------+-----------+ 
  13. | Variable_name   | Value     | 
  14. +-----------------+-----------+ 
  15. | long_query_time | 10.000000 | 
  16. +-----------------+-----------+ 
  17. 1 row in set (0.01 sec) 
  18.  
  19. -- 开启慢查询日志 
  20. mysql> set global slow_query_log='ON';  
  21. Query OK, 0 rows affected (0.00 sec) 
  22.  
  23. -- 设置查询超过10秒就记录 
  24. mysql> set global long_query_time=10; 
  25. Query OK, 0 rows affected (0.00 sec) 
  26.  
  27. -- 再次检查是否开启 
  28. mysql> show variables like 'slow_query%'; 
  29. mysql> +---------------------+------------------------------+ 
  30. | Variable_name       | Value                        | 
  31. +---------------------+------------------------------+ 
  32. | slow_query_log      | ON                           | 
  33. | slow_query_log_file | /var/lib/mysql/modb-slow.log | 
  34. +---------------------+------------------------------+ 
  35. 2 rows in set (0.00 sec) 

如果需要记录用户的所有查询操作,可以开启通用查询日志;

  1. mysql> show variables like 'general_log%'; 
  2. +------------------+-------------------------+ 
  3. | Variable_name    | Value                   | 
  4. +------------------+-------------------------+ 
  5. | general_log      | OFF                     | 
  6. | general_log_file | /var/lib/mysql/modb.log | 
  7. +------------------+-------------------------+ 
  8. 2 rows in set (0.00 sec) 
  9.  
  10. -- 开启通用查询日志 
  11. mysql> SET GLOBAL general_log=1;  
  12. Query OK, 0 rows affected (0.00 sec) 
  13.  
  14. mysql> show variables like 'general_log%'; 
  15. +------------------+-------------------------+ 
  16. | Variable_name    | Value                   | 
  17. +------------------+-------------------------+ 
  18. | general_log      | ON                      | 
  19. | general_log_file | /var/lib/mysql/modb.log | 
  20. +------------------+-------------------------+ 
  21. 2 rows in set (0.00 sec) 

如果需要记录数据的变更,可以开启二进制日志;错误日志是默认开启的。

  1. mysql> show variables like 'log_bin%'; 
  2. +---------------------------------+-----------------------------+ 
  3. | Variable_name                   | Value                       | 
  4. +---------------------------------+-----------------------------+ 
  5. | log_bin                         | ON                          | 
  6. | log_bin_basename                | /var/lib/mysql/binlog       | 
  7. | log_bin_index                   | /var/lib/mysql/binlog.index | 
  8. | log_bin_trust_function_creators | OFF                         | 
  9. | log_bin_use_v1_row_events       | OFF                         | 
  10. +---------------------------------+-----------------------------+ 
  11. 5 rows in set (0.00 sec) 
  12.  
  13. mysql>  

25、如何使用二进制日志?

二进制日志主要用来记录数据变更。

如果需要记录数据库的变化,可以开启二进制日志。基于二进制日志的特性,不仅可以用来进行数据恢复,还可用于数据复制。

  1. root@modb:/var/lib/mysql# ls binlog* 
  2. binlog.000001  binlog.000002  binlog.index 
  3. root@modb:/var/lib/mysql# mysqlbinlog binlog.000001 | mysql -u root -p                                             
  4. Enter password:  
  5. root@modb:/var/lib/mysql#  

在数据库定期备份的 情况下,如果出现数据丢失,可以先用备份恢复大部分数据,然后使用二进制日志恢复最近备份后变更的数据。在双机热备情况下,可以使用 MySQL 的二进制日志记录数据的变更,然后将变更部分复制到备份服务器上。

26、如何使用慢查询日志?

慢查询日志主要用来记录查询时间较长的日志。

在开发环境下,可以开启慢查询日志来记录查询时间较长的查询语句,然后对这些语句进行优化。

  1. root@modb:/var/lib/mysql# cat /var/lib/mysql/modb-slow.log 
  2. /usr/sbin/mysqld, Version: 8.0.26-0ubuntu0.21.04.3 ((Ubuntu)). started with: 
  3. Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock 
  4. Time                 Id Command    Argument 
  5. root@modb:/var/lib/mysql#  

通过配 long_query_time 的值,可以灵活地掌握不同程度的慢查询语句。

27、是不是索引建立得越多越好?

合理的索引可以提高查询的速度,但不是索引越多越好。

在执行插入语句的时候,MySQL 要为新插入的记录建立索引。所以过多的索引会导致插入操作变慢。原则上是只有查询用的字段才建立索引。

使用索引时,需要综合考虑索引的优点和缺点。

28、如何使用查询缓冲区?

查询缓冲区可以提高查询的速度,但是这种方式只适合查询语句比较多、更新语句比较少 的情况。

默认情况下查询缓冲区的大小为 0,也就是不可用。可以修改 queiy_cache_size 以调整查询缓冲区大小;修改 query_cache_type 以调整查询缓冲区的类型。

在 my.cnf 中修改 query_cache_size 和 query_cache_type 的值如下所示:

  1. [mysqld] 
  2. query_cache_size= 512M  
  3. query_cache_type= 1 
  4. query_cache_type=1 

表示开启查询缓冲区。

只有在查询语句中包含 SQL_NO_CACHE 关键字时,才不会使用查询缓冲区。可以使用 FLUSH QUERY CACHE 语句来刷新缓冲区,清理查询缓冲区中的碎片。

 

免责声明:本平台仅供信息发布交流之途,请谨慎判断信息真伪。如遇虚假诈骗信息,请立即举报

举报
反对 0
打赏 0
更多相关文章

评论

0

收藏

点赞