• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

MySQL基础常见问题集锦

开发技术 开发技术 3小时前 2次浏览

MySQL基础常见问题集锦

  1. sql设置主键中一般规则?

    1. 不更新主键列的值 ( 要设置主键的列不可被更新)
    2. 不重用主键列的值 (主键值必须在整个主键列中唯一)
    3. 不在主键列中使用可能会更改的值 (主键列不可与其它列存在绑定更新关系)
  2. 不能部分使用 distinct 关键字 ?

    1. distinct关键字应用于所有列而不仅是前置它的列.

      (distinct关键字区分粒度为的查询结果的行 , 如果一行数据显示不同则显示 , 相同则只显示一行数据)

      mysql> SELECT DISTINCT student_id ,student_name,student_birthday FROM student;
      +------------+--------------+------------------+
      | student_id | student_name | student_birthday |
      +------------+--------------+------------------+
      |          1 | java         | 2021-11-01       |
      |          2 | python       | 2021-11-01       |
      |          3 | c            | 2021-11-01       |
      |          4 | java         | 2021-11-02       |
      |          5 | java         | 2021-11-02       |
      +------------+--------------+------------------+
      5 rows in set (0.00 sec)
      
      mysql> SELECT DISTINCT student_name,student_birthday FROM student;
      +--------------+------------------+
      | student_name | student_birthday |
      +--------------+------------------+
      | java         | 2021-11-01       |
      | python       | 2021-11-01       |
      | c            | 2021-11-01       |
      | java         | 2021-11-02       |
      +--------------+------------------+
      4 rows in set (0.00 sec)
      
  3. limit 1,1 实际表示?

    1. limit 1,1 表示从第 2 行开始,显示 1 行 , limit 检索第一行为行 0 .
    2. Mysql5.0以后 limit 4 offset 3 表示 : 表示从第 3 行开始,显示 4 行 .
  4. existnot exist 效率高于 innot in

  5. like '1000'regexp '1000'

    1. like '1000' 匹配时匹配整列 , 即使列中包含 1000 也不会返回结果
    2. regexp '1000' 匹配时匹配整列 , 列中包含 1000 即返回结果
  6. 多数DBMS拼接使用 + || , MySQL使用Concat(A,B,C...) 函数实现

  7. DATEDIFF(NOW(),'1995-12-01') 函数可计算指定日期与现在相差天数

  8. STR_TO_DATE('1995,12,01','%Y,%m,%d') 函数可将指定字符串转成日期格式

  9. count( 1 )表示在查询结果的基础上为每行新增一列 1 的填充列 , 计算 增加1的个数达到统计效果

  10. group by 使用时除聚合计算以外, select语句中每个列都必须在group by子句中给出

  11. where 用于分组后组外过滤 , having用于分组时组内过滤

  12. union 用于组合相同的返回列, 并去除组合后重复值 , union all 组合后不去除重复值

  13. MyISAM引擎支持全文搜索(仅作了解)

    1. 代码

      -- 建表
      CREATE TABLE class(
          class_id INT,
          class_name VARCHAR(20),
          PRIMARY KEY (class_id),
          FULLTEXT(class_name)	-- 指定全文搜索需要建立的索引列
      )ENGINE=MYISAM;	
      
      --使用全文搜索
      SELECT class_name FROM class WHERE MATCH(class_name) AGAINST('p1');
      
  14. insert into中间可添加关键字LOW_PRIORITY以降低插入数据优先级, 保证select优先级

  15. insert select 中语句不要求返回列名一致 , 但要求返回查询结果返回顺序与插入一致

  16. update语句更新多行时, 如果一行出现错误可导致整体更新失败 , update ignore tablenameignore可跳过失败继续更新

  17. alter table 的常用用法

    1. 给表添加一列

      ALTER TABLE student ADD remark VARCHAR(20);
      
    2. 给表修改列名

      ALTER TABLE student CHANGE remark intro varchar(30);
      
    3. 删除列

      ALTER TABLE student DROP intro;
      
    4. 修改列类型

      ALTER TABLE student MODIFY remark VARCHAR(100);
      
  18. SQL客户端写存储过程时, 需要用 DELIMITER 命令设置结束符

    DELIMITER // --设置操作符
    CREATE PROCEDURE query_student_info_arg(
    	OUT student_id INT,
    	OUT class_id INT,
    	OUT school_id INT
    )
    BEGIN 
    	SELECT MAX(student.`student_id`) INTO student_id FROM student ;
    	SELECT MAX(class.`class_id`) INTO class_id FROM class ;
    	SELECT MAX(school.`school_id`) INTO school_id FROM school ;
    END//
    DELIMITER ; -- 在将操作符设置回 ;
    
  19. 使用EXPLAIN命令查看执行计划时 , type 列执行效率依次从最优到最差分别为:

    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    
  20. 使用EXPLAIN命令查看执行计划时 , Extra列重要值及优化方案分别为:

    distinct: 一旦mysql找到了与行相联合匹配的行,就不再搜索了
    Using index:这发生在对表的请求列都是同一索引的部分的时候,返回的列数据只使用了索引中的信息,而没有再去访问表中的行记录。是性能高的表现。
    Using where:mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据,再按 where 条件进行检查,符合就留下,不符合就丢弃。
    Using temporary:mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的,首先是想到用索引优化。
    Using filesortmysql 会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录,并保存排序关键字和行指针,然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。
    

阿里编码规约中关于MySQL数据库部分

建表规约

  1. 【强制】 表达是与否概念的字段,必须使用 is_xxx 的方式命名,数据类型是 unsigned tinyint
    ( 1 表示是, 0 表示否)。
  2. 【强制】 表名、字段名必须使用小写字母或数字, 禁止出现数字开头,禁止两个下划线中间只
    出现数字。数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。
  3. 【强制】 表名不使用复数名词。
  4. 【强制】 禁用保留字,如 desc、 range、 match、 delayed 等, 请参考 MySQL 官方保留字。
  5. 【强制】 主键索引名为 pk_字段名;唯一索引名为 uk_字段名; 普通索引名则为 idx_字段名。
  6. 【强制】 小数类型为 decimal,禁止使用 float 和 double。
  7. 【强制】 如果存储的字符串长度几乎相等,使用 char 定长字符串类型。
  8. 【强制】 varchar 是可变长字符串,不预先分配存储空间,长度不要超过 5000,如果存储长度
    大于此值,定义字段类型为 text,独立出来一张表,用主键来对应,避免影响其它字段索引效
    率。
  9. 【强制】 表必备三字段: id, gmt_create, gmt_modified。
  10. 【推荐】 表的命名最好是遵循“业务名称_表的作用” 。
  11. 【推荐】 库名与应用名称尽量一致。
  12. 【推荐】 如果修改字段含义或对字段表示的状态追加时,需要及时更新字段注释。
  13. 【推荐】 字段允许适当冗余,以提高查询性能,但必须考虑数据一致。冗余字段应遵循:
    1) 不是频繁修改的字段。
    2) 不是唯一索引的字段。
    3) 不是 varchar 超长字段,更不能是 text 字段。
  14. 【推荐】 单表行数超过 500 万行或者单表容量超过 2GB,才推荐进行分库分表。
  15. 【参考】 合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索
    速度。

索引规约

  1. 【强制】 业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引。
  2. 【强制】 超过三个表禁止 join。需要 join 的字段,数据类型保持绝对一致; 多表关联查询时,
    保证被关联的字段需要有索引
  3. 【强制】 在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据
    实际文本区分度决定索引长度。
  4. 【强制】 页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。
  5. 【推荐】 如果有 order by 的场景,请注意利用索引的有序性。 order by 最后的字段是组合索
    引的一部分,并且放在索引组合顺序的最后,避免出现 file_sort 的情况,影响查询性能。
  6. 【推荐】 利用覆盖索引来进行查询操作, 避免回表。
  7. 【推荐】 利用延迟关联或者子查询优化超多分页场景。
  8. 【推荐】 SQL 性能优化的目标:至少要达到 range 级别, 要求是 ref 级别, 如果可以是 consts最好。
  9. 【推荐】 建组合索引的时候,区分度最高的在最左边。
  10. 【推荐】 防止因字段类型不同造成的隐式转换, 导致索引失效。

文章部分笔记来源 : 《MySQL必知必会》by Ben Forta 、《Java开发手册(泰山版)》by Alibaba

文章博客其他地址 :
[STR_TO_DATE 函数转换日期格式说明]: https://www.cnblogs.com/feiwenstyle/p/9531571.html “STR_TO_DATE 函数转换日期格式说明”
[mysql explain命令说明及优化方案]: https://cloud.tencent.com/developer/article/1093229 “mysql explain详解”


程序员灯塔
转载请注明原文链接:MySQL基础常见问题集锦
喜欢 (0)