Mysql数据库操作简介

mysql操作简介

mysql中关键字执行顺序

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT

每个操作后都会产品一个虚拟表，这个虚拟表作为入参传入下一个步骤。

基础表操作

1. 添加字段

ALTER TABLE tableName(表名称) ADD (age(字段名称) int(11)(字段类型);

2. 修改字段名

ALTER TABLE tableName RENAME COLUMN age to big_age

3. 修改字段的数据类型

ALTER TABLE tableName MODIFY (age float(3,1));

4. 删除字段

ALTER TABLE tableName DROP COLUMN age;

表中的相关键

1. 超键
   能唯一识别一条记录的属性集。
2. 候选键
   超键中不包含多余属性，就是候选键。比如表A种有身份身份证，姓名，性别。超键可能是(身份证)(身份证，姓名)(身份证，性别)，(身份证，姓名，性别)。候选键就是(身份证)
3. 主键
   可以从候选键中选择一个作为主键。
4. 外键
   另一个表的主键。

建表的三范式

1. 1NF
   数据库表中的任何字段都是原子性的，不可分割的。实际上任何DBMS都满足第一范式，不会对字段进行拆分。
2. 2NF
     数据库中的非主属性都要和候选键有完全依赖关系。
     比如：一张考试成绩表score,里面存有学生身份证，学生姓名，考试科目，考试教室，考试分数。其中学生身份证 + 考试科目定义一条记录。这个表就不服务第二范式。因为这个表中还对应者这个关系。（学生身份证）–>(考试教室)，(考试科目)–>(学生姓名)。
     简单来说就是非主属性并非完全依赖候选键。也就是说一张表只表达一个意思。和这个表不相关的字段不要存。
3. 3NF
     在第二范式的基础上。对任何非主属性都不传递依赖于候选键。我感觉就是你这个表的内聚性要好，不能传递依赖。

索引分类

功能逻辑上分：

1. 普通索引
   基础的索引，没有任何约束。
2. 唯一索引
   普通索引 + 唯一约束
3. 主键索引
   唯一索引 + not null
4. 全文索引
   我们通常可以采用专门的全文搜索引擎，比如 ES(ElasticSearch) 和 Solr。

物理实现方式：

1. 聚集索引
     索引和数据保存在一起。按照主键来排序存储数据。
2. 非聚集索引
     在数据库系统会有单独的存储空间存放非聚集索引，这些索引项是按照顺序存储的，但索引项指向的内容是随机存储的。也就是说系统会进行两次查找，第一次先找到索引，第二次找到索引对应的位置取出数据行。

联合索引的最左匹配原则：
比如我们定义的联合索引(x, y, z)，可利用的索引就有：
(x),(x,y),(x,y,z)

索引失效常见场景：

1. 不符合最左匹配原则
2. like “%xxxx”
3. 字符串不加单引号索引失效
4. <>，not in ，!= 会导致全表扫描
5. is null,is not null

聚集索引与非聚集索引的原理不同，在使用上也有一些区别：

1. 聚集索引的叶子节点存储的就是我们的数据记录，非聚集索引的叶子节点存储的是数据位置。
2. 非聚集索引不会影响数据表的物理存储顺序。一个表只能有一个聚集索引，因为只能有一种排序存储的方式，但可以有多个非聚集索引，也就是多个索引目录提供数据检索。
3. 使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低。

游标的使用

1. 定义游标

 DECLARE cursor_name CURSOR FOR select_statement

2. 打开游标

OPEN cursor_name

3. 从游标中读取数据

FETCH cursor_name INTO var_name

4. 关闭游标

CLOSE cursor_name

5. 释放游标

DEALLOCATE cursor_namec

样式：

CREATE PROCEDURE `calc_hp_max`()
BEGIN
       -- 创建接收游标的变量
       DECLARE hp INT;  

       -- 创建总数变量 
       DECLARE hp_sum INT DEFAULT 0;
       -- 创建结束标志变量  
     DECLARE done INT DEFAULT false;
       -- 定义游标     
       DECLARE cur_hero CURSOR FOR SELECT hp_max FROM heros;
       -- 指定游标循环结束时的返回值  
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;  
       
       OPEN cur_hero;
       read_loop:LOOP 
       FETCH cur_hero INTO hp;
       -- 判断游标的循环是否结束  
       IF done THEN  
                     LEAVE read_loop;
       END IF; 
              
       SET hp_sum = hp_sum + hp;
       END LOOP;
       CLOSE cur_hero;
       SELECT hp_sum;
END

慢sql查询

1. 查看是否开启慢sql日志

SHOW Variables like ‘%slow_query_log%’;

2. 如果没开启，开启慢查询日志

mysql> set global slow_query_log='ON';
mysql> set global slow_query_log_file='/var/lib/mysql/hostname-slow.log';
mysql> set global long_query_time=2;

3. mysql提供了日志分析工具mysqldumpslow

-- 通过 mysqldumpslow --help 查看操作帮助信息
-- 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log
-- 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/hostname-slow.log
-- 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hostname-slow.log
-- 也可以和管道配合使用
mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log | more

explain执行计划

explain执行计划是模拟优化器进行sql查询语句。

我们可以从执行计划中得到的信息：

1. 表的读取顺序
2. 数据读取的操作类型
3. 可以用哪些索引
4. 实际用到了哪些索引
5. 表之间的引用
6. 每张表有多少行被优化器查询

操作也比较简单。explain + sql_statement
explain中的字段含义：

1. id
   select查询的序列号。
   id相同，执行顺序从上往下。
   id不相同，id值越大优先执行。

2. select_type
   数据读取操作类型。
   (1) SIMPLE
     简单色select查询，查询中不包含子查询，联合查询等复杂查询。
   (2) PRIMARY
      查询中包含任何复杂的子部分，最外层被标记为primary。
   (3) SUMQUERY
      在select 或者 where 后面包含子查询。
   (4) DERIVED
      在from列表中包含的子查询被标记为derived。mysql会递归执行这些查询，把结果放到临时表里。
   (5) UNION
     若第二个select出现在UNION之后，则被标记为UNION。若UNION包含在子句的子查询中，外层select将被标记为DERIVED。
   (6) UNION RESULT
     从union表获取结果的select
   3. table
   显示数据读取的是哪一张表的。
   4. type
   查询类型。从好到查排序为：
   system–>const–>equ_ref–>ref–>fulltext–>ref_or_null–>index_merge–>unique_subquery–>range–>index–>all
   (1) system
      表中只有一行记录。是const类型的特例。
   (2) const
      表示通过索引一次就找到了。const用于比较primary key 或 unique 索引。
   (3) equ_ref
      唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。比如A表关联B表。通过A表外键关联。
   (4) ref
      非唯一索引扫描，匹配某个单独值所有行。比如A表关联B表，对于A表中的索引字段，B表有多行记录。
   (5) range
      只检索给定范围的行。索引列出现>,<，in等的查询。
   （6）index
      遍历索引树。
   (7) all
      遍历全表找到匹配的行。

   一般来说保证查询至少达到range级别，最好到达ref。
   5. possible_keys
   显示这张表中的可能用到的索引
   6. key
   实际使用的所用，如果为null，则没有使用索引
   7. key_len
   索引中使用的字节数。
   8. ref
   显示索引中哪一列被使用，如果可能的话，是一个常数。
   9. rows
   根据表统计信息及索引使用情况，大致估算找到所需记录需要读取的行数。
   10. Extra
   包含不适合在其他列中显示但时分重要的额外信息。
   （1） Using where
      表示使用where条件过滤数据。往往需要结合explain中的type来综合判断是否需要优化。
   (2) Using index
      说明查询结果都在索引树上，这样的性能往往最好。
   (3) Using index condition
      这个比Using index多了一个condition，说明查询的所有列并不是都在索引树上。还需要回表查询其他列。
   (4) Using filesort
     说明需要对查询到的结果集，进行文件排序。这类sql需要优化。一般是对order by后面的列加索引。
   (5) Using temporary
     说明需要建立临时表来暂存中间结果。典型的是group by 和 order by同时存在，并且作用的是不同字段时，就会创建临时表，以便计算结果集。
   (6) Using join bufffer
     说明，需要进行嵌套循环计划。典型的是两个关联表join，关联字段均为建立索引，就会出现这种情况。

profiling

  MySQL 收集在 SQL 执行时所使用的资源情况。

1. 检查是否开启

select @@profiling;

2. 如果没有开启，可以通过以下指令开启

set profiling=1;

3. 执行自己的sql语句

select * from stu_classrecord_2022 

4. 通过以下指令查看所执行sql的资源使用情况
   查看当前会话所产生的的所有profiles

 show profiles

查看自己刚才执行sql的profile

show profile for query id(对应show profiles查询的id)

主键为什么建议用自增主键

使用UUID这种无序的缺点：

1. Mysql中的Innodb存储引擎的索引结构默认是B+树，这种结构的特点是索引树上的数据是有序的。如果使用UUID左右主键，那么每次插入数据时，因为无法保证UUID有序，所以就会出现新的UUID需要插入到索引树的中间去，这样可能会频繁的导致页分裂，是性能下降。
2. 太占用内存。每个UUID有36个字符组成，在字符串进行比较时，需要从前往后比较，字符串越长，性能越差。
3. 不连续的，可能新插入的记录页已经不在内存中，需要重新加载对应页。增加随机磁盘I/O的开销。

综上所述：主要是为了避免索引树上页分裂，增加随机磁盘I/O的性能开销。

自增id用完会出现什么现象：

1. 自增id用完(自增主键作为主键)，再插入数据会报异常，插入失败。获取到的自增值还是4294967295，就会出现主键冲突的错误。
2. 自增id用完(自增主键不作为主键)，默认rowid作为主键。rowid用尽，会从0开始从新递增。会出现数据覆盖。比如第一条数据中有个name属性叫"小强",当rowid从新递增的话，第一条记录中name会被替换为新插入的名称。

我们可以使用bigint作为自增主键。避免int自增用尽。