MySQL笔记_3

SQL优化

insert优化

1. 使用批量插入，一次性插入500~1000条数据，而不是一条一条插入，也不能一次性插入过多
2. 使用手动事务提交
3. 主键顺序插入
4. 如果需要插入大批量的数据，可以使用MySQL提供的load指令，而不使用insert

   -- 使用load需要修改
   -- 客户端链接服务端 加上--local-infile参数
   mysql \-\-local-infile -u root -p
   -- 设置全局参数local-infile为1，开启本地加载文件到数据库的开关
   set global local-infile = 1;
   -- 执行load指令 将准备好的数据加载到表结构中
   load data local infile '/root/sql1.log' into table 'user' fields terminated by ',' lines terminated by '\n';
   -- 每个字段之间使用,分割，每行之间使用'\n'分割

5. 对于load命令而言，同样顺序插入比乱序插入更好

主键优化

• 表数据都是根据主键顺序存放的，这种存储方式的表称为索引组织表(index organized table, IOT)
• InnoDB中的页可以为空，也可以填充一半，或者存满。规定每个页至少包含两行数据，最多包含N行数据。因为如果只有一行数据，相当于形成了一个链表，如果一行数据比较大，超出于阈值后又会形成溢出现象。
• 行与行之间根据主键排列

主键顺序插入

• 如果主键顺序插入，则一页存放满以后自动开启新页并将数据存放其中
  

主键乱序插入

• 如果主键乱序插入，存放满以后，开启新的数据页，找到本应该要存放的数据页的50%的位置，后面的行移动到新的数据页，再决定要插入的行存放在原来剩下的50%的数据页中还是新数据页中，最后改变链表指针, 称为页分裂
• 删除一行记录时，没有物理删除，只是标记为删除并且可以被其他记录使用
• 页中删除的记录达到MERGE_THRESHOLD，即默认页的50%时，InnoDB就会查找最近的前后两个页是否可以合并，如果可以合并，就会优化空间，称为页合并
  
  
  

推荐使用主键自增

• 比如身份证号，如果拿身份证号作为主键，导致所有二级索引存储主键的值就会占用20B，如果是自增主键，大小就会很小，所以主键长度越小，普通索引的叶子节点就越小，占用空间也就越小。
• 但是如果只有一个唯一索引，并且是K-V键值对查询的索引，那就可以不用主键自增，避免回表

主键设计原则

1. 满足业务需求的情况下尽量降低主键的长度。因为主键索引只有一个，二级索引的叶子节点中记录的是主键，如果主键较长，会占用较多的磁盘空间。
2. 插入数据使用顺序插入，使用auto_increment
3. 不要使用UUID或者身份证号作为主键
4. 避免对主键的修改

order by优化

1. Using filesort: 通过表索引或全表扫描，读取满足条件的数据行。在排序缓冲区中sort buffer完成排序操作，所有不是通过索引直接返回排序结果的排序都是filesort排序
2. Using index: 通过有序索引顺序扫描直接返回有序数据，就是using index，不需要额外的排序，操作效率高。

• 当没有(age, phone)这个联合索引的时候，select id, age, phone from user order by age, phone;这个查询语句使用的是using filesort
• 当有(age, phone)这个联合索引的时候，则该语句使用using index
  • 若语句改变为select id, age, phone from user order by age desc, phone desc; 依然是using index
  • 但是如果语句改变为select id, age, phone from user order by age, phone desc; 则会出现前一个使用using index，后一个变为using filesort这是因为创建索引的时候值都是从小到大排列的，可以通过show index from user查看其中的Collection列是A就表示升序排列
  • 为了解决这个问题，只需要在创建索引的时候指定顺序即可，比如create index idx_user_age_phone_ad on user(age asc, phone desc); 此时在进行上一次的排序，就会发现using index
  • 如果排序变为select id, age, phone from user order by phone, age; 就会出现前一个使用using filesort，后一个使用using index，因为这个违背了最左前缀法则，所以会出现两个。这个不像where ... and ...可以自动匹配顺序变化。

• 如果有联合索引，则全部是升序排序，或者全部是降序排序，均可实现using index；
• 但是如果一个是升序，另一个是降序，则需要在创建一个指定排序的索引才行
• 这些都是建立在覆盖索引，不需要回表查询的基础上。如果需要回表，则一定是using filesort，比如: select * from user order by age, phone;

排序设计原则

1. 建立合适的索引，多字段排序也遵循最左前缀法则
2. 使用覆盖索引
3. 多字段排序，一个升序一个降序，注意联合索引在创建时的规则
4. 不可避免出现filesort的情况下，出现大数据排序时可以增大缓冲区的大小，sort_buffer_size默认为256K

select city,name,age from t where city='杭州' order by name limit 1000;

• 初始化sort buffer, 确定要放入city name age字段
• 从索引city中根据索引找到第一个满足条件的主键ID
• 根据主键ID返回name city age存入sort buffer
• 从索引ID取下一个符合要求的主键ID，直到取完
• sort buffer按照name快排
• 取1000行返回

这是全字段排序，可能在内存中完成，也可能在外部完成，取决于sort_buffer_size, number_of_tmp_files表示排序的过程中需要使用的临时文件个数，所以配置的sort_buffer_size越小，number_of_tmp_files就越大

• 全字段排序如果一行内容很多，就会导致sort buffer放不下，产生很多临时文件，性能就不好
• 配置max_length_for_sort_data，如果单行长度超过这个值，MySQL就会换一个排序方法
  • 如果max_length_for_sort_data变小，比如只能放入name，就不能直接返回了
  • 初始化sort buffer，确定放入name和id(rowid)
  • 根据city索引找到主键id，根据主键id返回name和id字段，存入sort buffer中
  • 从索引city取下一个主键ID，重复，直到不满足city条件为止
  • sort buffer对name字段排序
  • 结果根据id找到city name age返回
  • 所以比全字段排序多了一次回表
• 如果建立(city,name)联合索引，这条语句就不会进行排序
  • 从索引city中找到第一个满足条件的主键ID
  • 从主键ID找到city name age作为结果集的一部分直接返回
• 如果建立(city, name, age)联合索引，则变成了覆盖索引，不需要回表，直接返回结果

• 如果explain的结果中，是using temporary, using filesort的话，那么结果就是需要临时表，并且需要排序

如果现在有单词表，需要每次随机显示三个单词:select word from words order by rand() limit 3;

• 创建临时表，引擎Memory，两个字段，一个随机值R，一个单词word，没有索引

• 按照单词表主键顺序读出word，生成0-1之间的随机数，存入临时表中

• 按照字段R排序

  • 初始化sort buffer， 两个字段，一个是double，一个是整型
  • 从内存临时表读取R和rowid，存入sort buffer中
  • sort buffer根据R进行排序
  • 取前三个rowid，根据rowid在内存表找到对应的word，返回

• 对于InnoDB表，全字段排序减少磁盘访问，会优先选择

• order by rowid用的就是内存表进行排序

• 对于内存表，回表就是根据行数据，直接访问内存得到数据，不会多访问磁盘，所以优化器优先考虑行越小越好，所以会选择根据rowid排序

• rowid就是InnoDB唯一标识数据行的值，如果主键被删除了，InnoDB会自动生成一个6B的rowid

• 对于Memory引擎，rowid就是数组下标。如果内存临时表中进行排序，这个临时表就是Memory引擎的

• tmp_table_size参数设置了内存临时表的大小，默认16MB，超过大小就是磁盘临时表，InnoDB引擎，如果使用磁盘临时表，就是一个没有显式主键的InnoDB表排序过程

• 如果limit值特别小的情况，引擎会使用优先队列排序优化

limit优化

• 由于需要排序记录，并且将其他记录全部丢弃，查询排序的时间非常长。因此，limit越往后时间越长
• 可以使用覆盖索引 + 子查询的形式优化

• 原始语句: select * from 表名 order by id limit 9000000, 10;
• 覆盖索引: select id from 表名 order by id limit 9000000, 10;
• 子查询: select * from 表名 where id in (select id from 表名 order by id limit 9000000, 10); 这句话会报错，因为不能在in后面使用limit
• select s.* from 表名 s, (select id from 表名 order by id limit 90000000, 10) a where s.id=a.id; 将limit查询结果作为一个表联合查询

count优化

• MyISAM把表的总行数存储在磁盘上，count(*)直接返回这个数，效率高，前提是没有where条件
• InnoDB即使没有where条件，也需要将数据一行一行读出，然后累加
  • 这是因为MVCC，所以不能记录count(*)的值直接返回，因为每一次返回的值是不确定的
  • InnoDB会保证逻辑正确的情况下，找索引树最小的那棵来遍历计数
• 优化只有自己计数，例如使用redis

• count(列名)返回的是列中不为null的个数，
• count(主键)遍历整张表，把每一行主键id取出，返回给服务层，服务层拿到主键以后直接累加，因为主键不会为null
• count(字段)需要看字段是否有not null约束，如果没有该约束，需要将每行字段值取出，返回给服务层。服务层判断是否为null，不为null才累加计数
  • 如果有not null约束，则跟主键处理一样
• count(1)遍历整张表，但是不取值，服务层对于返回的每一行，都放一个数字1进去，直接按行累加。参数是什么数字都可以，0，-1都可以，返回一样的结果
• count(*)不会把全部字段取出来，而是专门优化了，直接在服务层累加
• 所以最终的效率count(*)性能最高，类似于count(1)，然后才是count(主键)，性能最差的是count(字段), 推荐使用count(*)

缓存系统计数

• 使用Redis计数，插入一行，计数就加一，反之减一
  • 可能丢失更新，Redis在内存中，虽然可以持久化保存在磁盘中，如果没有保存的时候异常重启了，那么再启动时，Redis从磁盘中得到的数据就丢失了，不过可以通过异常重启以后执行count(*)更新值
  • 就算这样，逻辑也会有问题，比如取出总数同时显示最近100条，就需要从Redis中取出数据，再从MySQL中取100条
    • 返回的100条的时候可能新加入了数据
    • 可能100条数据中有新数据，但是Redis没有 + 1
    • 可能100条数据中没有新数据，但是Redis + 1

数据库计数

• 单独存储一张表用来计数
• 可以崩溃恢复
• 可以利用事务隔离保证计数和返回的结果不会有偏差

update优化

• 更新数据时，根据索引字段级进行更新，否则行锁会升级成表锁。
• update course set name='Java' where id=1;这句话是行锁，别的事务更新别的行不会被卡住
• update course set name='Springboot' where name='kafka';这句话是表锁，别的事务更新别的行会被卡住，因为name字段没有索引
  • 如果建立了name的索引，则上句更新就是行锁了
• 一定要根据索引字段进行更新！否则表锁会锁住整张表。

存储对象: 视图、存储过程、触发器、存储函数

视图

• 视图是虚拟存在的表，视图中的数据不在数据库中真实存在，行列数据来自视图查询中使用的表，称为基表，在使用视图时动态生成
• 视图只保存SQL逻辑，不保存查询结果。创建视图主要工作在SQL查询语句上

增

• 创建视图: create [or replace] view 视图名[(列名列表)] as select语句 [with [cascade | local] check option];
  • 替换视图就使用or replace
  • select语句关联的表就是基表
  • 创建视图的时候指定with cascade check option，并且设置了视图select语句为where id < 20，若此时增加了一条id = 30的数据就会报错，提示无法插入。如果没有设置check option就不会报错，可以正常插入

删

• 删除视图: drop view [if exists] 视图名[, 视图名1, ...];

改

• 替换视图: create or replace view 视图名[(列名列表)] as select语句 [with [cascade | local] check option];
• 修改视图: alter view 视图名[(列名列表)] as select语句 [with [cascade | local] check option];

查

• 查询创建视图语句: show create view 视图名;
• 查看视图数据: select * from 视图名;视图就是虚拟存在的表，所以可以跟操作表一样操作视图

• 在视图中插入数据会插入到对应的基表中，因为视图不会保存数据。
• with check option 就是检查项，在增删改的时候都会检查是否符合视图创建时的定义。MySQL允许创建视图的视图，检查依赖视图规则有两个选项，就是cascade以及local

cascade

• create view v1 as select id, name from student where id <= 20; 这里没有设置check option，所以修改数据不会检查id <= 20
• create view v2 as select id, name from v1 where id >= 10 with cascade check option; 这里修改了v2的数据时，不仅会检查id >= 10，还会检查id <= 20
• create view v3 as select id, name from v2 where id <= 15; 这里修改v3的数据> 15也可以插入，但是如果> 20则不能插入，因为不能满足v2的检查项

local

• create view v1 as select id, name from student where id <= 15; 修改v1中的数据不会检查id <= 15
• create view v2 as select id, name from v1 where id >= 10 with local check option; 先检查是否满足v2条件，然后再找v1是否需要检查，发现v1不需要检查，则直接插入。所以插入id = 17也可以
• create view v3 as select id, name from v2 where id < 20; 没有定义检查选项，会先找v2的检查项，发现v2需要检查id >= 10，再发现v1没有检查选项，所以最终id >= 10即可

• 视图如果要能够更新，必须满足视图中的行与基表中的行一对一关系，只要包含下面任意一项，则视图不可更新
  1. 聚合函数或窗口函数sum, min, max, count
  2. distinct
  3. group by
  4. having
  5. union, union all

视图作用

1. 操作简单，将复杂的经常使用的查询定义在视图中，不需要每次都指定同样的相同操作
2. 安全，由于MySQL不能授权到特定的行列，只能授权到表。所以可以通过视图修改用户能见到的数据
3. 数据独立: 可以帮助用户屏蔽真实表结构带来的变化

例子:

1. 为了保证安全性，操作user表时，只能看到用户的基本字段，屏蔽手机号和邮箱两个字段:
   create view user_view as select id, name, profession, age, gender, status, create_time from user;
2. 为了简化操作，查询每个学生所选修课程，三表联查，定义一个视图:

   select s.name, s.no, c.name from student s, student_course sc, course c where s.id = sc.studentid and c.id = sc.courseid;
   -- 封装成视图
   create view stu_course_view as select s.name studentname, s.no studentno, c.name coursename 
   from student s, student_course sc, course c where s.id = sc.studentid and c.id = sc.courseid;

存储过程

• 很多地方禁止使用
• 事先编译并存储在数据库中的一段SQL语句集合，减少数据库和应用服务器之间的传输，提高数据处理效率
• 就是SQL语句封装重用
• 特点：封装、复用；可以接收参数和返回数据；减少网络交互，提升效率

增

• 创建存储过程

  create procedure 存储过程名称([in/out/inout参数列表])
  begin
      -- SQL语句
  end; -- 这里有个分号
  -- 如果是在命令行中创建存储过程，遇到分号就自动结束了，所以会报错，需要通过delimiter指定sql语句结束符
  --  delimiter $$
  --  会一直到$$才认为是语句结束了

删

• 删除存储过程: drop procedure if exists 存储过程名;

调用

• 调用存储过程: call 存储过程名([参数])

查

• 查询指定数据库的存储过程以及状态信息: select * from information_schema.routines where routine_schema='xxx';
• 查询存储过程定义: show create procedure 存储过程名;

变量

MySQL变量分为三类，系统变量，用户自定义变量以及局部变量

• 存储过程的参数类型：in, out, inout三种类型，默认是in类型的，作为输入，out类型作为输出，作为返回值，inout既可以作为输入又可以作为输出

系统变量:

• 不需要用户自定义，属于服务器，分为全局变量global以及会话变量session
• 会话变量表示在当前会话内有效
• 查询所有系统变量: show [session | global] variables;
• 模糊查询: show [session | global] variables like ...;
• 查询指定的系统变量，不写默认是session: show @@[session | global].系统变量名;
• MySQL重启后，设置的global会失效，需要在my.cnf中完成设置才能保持

用户自定义变量

• 直接@变量名即可
• 赋值自定义变量:

  set @var_name = expr[, @var_name = expr, ...];
  set @var_name := expr[, @var_name := expr, ...];
  select @var_name := expr
  select 字段名 into @var_name from 表名;

• 使用select @varname，如果var_name没有赋值，这个语句不会报错，只会显示null

局部变量

• 使用declare申明，存储过程内的局部变量和输入参数，在begin ... end模块内生效
• 声明变量: declare 变量名 变量类型 [default ...];
• 赋值局部变量:

  set 变量名 = 值;
  set 变量名 := 值;
  select 字段名 into 变量名 from 表名;

case

case case_value
	when value1 then statement1
	when value2 then statement2
	else statement3
end case;

case 
	when condition1 then statement1
	when condition2 then statement2
	else statement3
end case;

while

while 条件 do
	-- sql语句
end while;

repeat

repeat
	-- sql语句
	until 条件
end repeat; -- 满足条件就退出循环

loop

[begin_label:] loop
	-- sql语句
end loop[end_label];
leave [label] -- 退出指定的循环
iterate [label] -- 跳过当前循环直接进入下一次循环
-- 如果不退出就是死循环

游标

• 用于存储查询结果集的数据类型，在存储过程个函数中可以使用游标对结果集进行循环处理，包括游标声明，open, fetch, close
• 声明游标: declare 游标名称 cursor for 查询语句;
• 打开游标: open 游标名称;
• 打开游标后才能获取游标记录: fetch 游标名称 into 变量[, 变量];
• 关闭游标: close 游标名称;

例子:

1. 根据传入参数uage, 查询user表，用户年龄小于等于uage的用户姓名name和专业profession插入到创建的一张表id, name, profession中

   create procedure p(in uage int)
   begin
       declare uname varchar(100);
       declare upro varchar(100);
       declare u_cursor cursor for select name, profession from user where age <= uage;
       -- 游标的申明应该放在普通变量后面
       declare exit handler for sqlstate '02000' close u_cursor;
       -- 满足SQL状态码是02000的, 触发退出操作，关闭游标u_cursor
       -- 同样可以将sqlstate '02000' 替换成not found
       drop table if exists user_pro;
       create table if not exists user_pro(
           id int primary key auto_increment,
           name varchar(100),
           profession varchar(100)
       );
   
       open u_cursor;
       while true do 
           fetch u_cursor into uname, upro;
           insert into user_pro values(null, uname, upro);
       end while; -- 但是这里是死循环，因为不知道游标什么时候会结束
   
       close u_cursor;
   end;

   • 条件处理程序handler定义在流程遇到问题和相应的处理步骤
   • declare handler_action handler for condition_value[, condition_value ...] statement;
   • handler_action包括两个内容，continue以及exit
   • condition_value有SQLSTATE sqlstate_value状态码，比如02000; sqlwarning是所有以01开头的sqlstate代码的缩写，not found是所有以02开头的代码的缩写，sqlexception是所有其他的sqlstate代码的缩写

存储函数

• 就是有返回值的存储过程，并且参数只能为in类型，可以被存储过程替代

create function 存储函数名([参数列表])
return type [characteristic ...]
begin
    -- SQL语句
    return ...;
end;

characteristic:

• deterministic: 相同的输入参数总是产生相同的结果
• no sql: 不包含SQL语句
• reads sql data: 包含读取数据的语句，但是不包含写入数据的语句

触发器

• 不建议使用
• MySQL只支持行级触发器，不支持语句级触发器，因此一次修改多行，就会触发多次

触发器类型	NEW 和 OLD
INSERT型	NEW表示将要或者已经新增的数据
UPDATE型	OLD表示修改之前的数据，NEW表示将要或已经修改的数据
DELETE型	OLD表示将要或已经删除的数据

• 创建触发器:

  create trigger trigger_name 
  before/after insert/ update/ delete 
  on 表名 for eachrow 
  begin 
      -- trigger_stmt; 
  end;

• 查看触发器: show triggers;

• 删除触发器: drop triggers [schema_name.]trigger_namel 如果没有指定schema_name，默认为当前数据库