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. exist 和 not exist 效率高于 in 和 not 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 tablename 中ignore可跳过失败继续更新

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 filesort：mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时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详解"