玩命加载中 . . .

Mysql高级（一）

mysql
mysql
发布日期:   2020-11-10
文章字数:   6.2k
阅读时长:   25 分
阅读次数:  

Mysql高级

mysql的架构介绍

Linux安装Mysql

  1. 检查当前系统是否安装过mysql

    1. 在opt目录下打开命令行
    2. 查询命令：rpm -qa|grep -i mysql
    3. 删除命令：rpm -e rpm软件包名

  2. 安装Mysql服务端

    1. 安装rpm -ivh Mysql-server-5.5.48...rpm

  3. 安装mysql客户端

    1. 安装rpm -ivh mysql-client...rpm

  4. 查看mysql安装版本（是否安装成功）

    mysqladmin --version命令，类似java -version如果打出消息，即为成功。

  5. 启动

    service mysql start

    查看是否开启：ps -ef|grep mysql

  6. 关闭

    service mysql stop

  7. 修改Mysql登录密码

    1. 启动服务之后，执行命令/usr/bin/mysqladmin -u root password '123456'
    2. 通过mysql -uroot -p123456进行登录

  8. 设置mysql自启动

    1. chkconfig --level 5 mysql on开启
    2. chkconfig --list|grep mysql查看
    3. 【可选操作】ntsysv也是查看，如果[*]mysql带 *，就是自启动

  9. 配置文件

    1. 拷贝配置文件

      1. 5.5版本： cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
      2. 5.6版本：cp /usr/share/mysql/my-default.cnf /etc/my.cnf

    2. 重新启动mysql

      service mysql stop/start

    3. 登录

      mysql -uroot -p123456

    4. 退出

      exit

    5. 修改字符集

      1. cd /etc
      2. 查看是否有my.cnf文件，ls -1 my.cnf
      3. vim my.cnf
      4. [client]节点下，socket下加上default-character-set=utf-8
      5. [mysqld]节点下，port下加上
        1. character_set_server=utf8
        2. charactet_set_client=utf8
        3. collation-server=utf8_general_ci
      6. [mysql]节点下，no-auto-rehash下加上default-character-set=utf8
      7. esc，wq!保存退出
      8. 重启数据库，登录数据库，插入中文测试（新建的库才有用）

mysql安装目录介绍

查看安装目录ps -ef|grep mysql

路径 解释 备注
/var/lib/mysql/ mysql数据库的存放路径** 重要
/usr/share/mysql 配置文件目录
/usr/bin 相关命令目录** 重要
/etc/init.d/mysql 启停相关脚本

Mysql配置文件

二进制日志log-bin

用于主从复制

错误日志log-error

默认是关闭的，记录严重的警告和错误信息，每次启动关闭的信息

查询日志log

默认关闭，记录查询到的sql语句

数据文件

  1. 双系统
    1. window：\data
    2. linux：/var/lib/mysql，每一个目录代表一个同名的库
  2. frm文件：存放表结构
  3. myd文件：存放表数据
  4. myi文件：存放表索引

如何配置

  1. windows：my.ini文件
  2. linux：/etc/my.cnf文件

Mysql逻辑架构

  1. 连接层

  2. 服务层

    1. Management Serveices & Utilities： 系统管理和控制工具

    2. SQL Interface: SQL接口
      接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

    3. Parser: 解析器
      SQL命令传递到解析器的时候会被解析器验证和解析。

    4. Optimizer: 查询优化器。
      SQL语句在查询之前会使用查询优化器对查询进行优化，即选取最优的方式执行。

    5. Cache和Buffer： 查询缓存。

      如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

  3. 引擎层

  4. 存储层

存储引擎

  1. 查看命令

    1. 方式一：看你的mysql现在已提供什么存储引擎：show engines;
    2. 方式二：看你的mysql当前默认的存储引擎:show variables like '%storage_engine%';

  2. MyISAM和InnoDB

索引优化分析

整体优化策略：

  1. 慢查询的开启并捕获
  2. explain+慢sql分析
  3. show profile查询sql在mysql服务器里执行细节和生命周期情况
  4. sql数据库服务器的参数调优

性能下降sql慢，执行/等待时间长

  1. 查询语句写的烂
  2. 索引失效
    1. 单值create index 索引名idx_user_name on 表名user（列名name）
    2. 复合create index 索引名idx_user_name on 表名user（列名name）
  3. 关联查询太多join（设计缺陷或不得已的需求）
  4. 服务器调优及各个参数设置（缓冲、线程数等）

常见通用的Join查询

  1. 机读sql执行顺序

  2. 注意：mysql不支持full join

    1. 全外连：

      select * from tb_emp a left join tb_deptId b on a.deptId = b.id 
union #连接表，并去重
select * from tb_emp a right join tb_deptId b on a.deptId = b.id

    2. 全外连，中间部分去掉

      select * from tb_emp a left join tb_deptId b on a.deptId = b.id where b.id is null
union 
select * from tb_emp a right join tb_deptId b on a.deptId = b.id where a.id is null

索引

定义：是帮助mysql高效获取数据的数据结构。简单理解为：排好序的快速查找数据结构

除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

一般索引本身也很大，以索引文件的形式存储在磁盘上

优势

  1. 类似大学图书馆的书目索引，提高数据检索的效率（树的高度），降低数据库的io成本
  2. 通过索引列对数据进行排序，降低数据排序的成本，降低了cpu成本

劣势

  1. 实际上索引也是一张表，表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用空间的
  2. 虽然索引提高了查询速度，同时却会降低更新表的速度。每次更新表，还要更改索引信息。
  3. 要花时间研究建立最优秀的索引

索引的分类

  1. 单值索引：一个索引只包含单个列，一个表可以有多个单值索引
  2. 唯一索引：索引列的值必须唯一，但允许空值
  3. 复合索引：一个索引包含多个列

基本语法：

  1. 创建：

    create [unique] index indexName on mytable(columnname);
alter mytable add [unique] index[indexName] on (columnname);

  2. 删除：

    drop index[indexName] on mytable;

  3. 查看：

    show index from table_name

  4. alter命令

    alter table 表名 add primary key (列名)：添加主键
alter table 表名 add unique 索引名(列名)：创建索引的值必须是唯一的
alter table 表名 add index 索引名（列名）：增加普通索引
alter table 表名 fulltext 索引名（列名）：全文索引

哪些情况需要创建索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 单值/组合索引的选择问题，组合索引性价比更高
  5. 查询中排序的字段，排序字段若通过索引去访问呢将大大提高排序速度
  6. 查询中统计或分组字段

不要创建索引

  1. 记录数据少
  2. 经常增删改
  3. where条件用不到的字段
  4. 过滤性不好

索引结构

B+Tree

性能分析

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，知道MYSQL是怎么处理SQL语句的

用法：explain+SQL语句

用处：

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

各字段的解释

  1. id：查询中执行select子句或操作表达顺序
    1. id相同，执行顺序从上到下
    2. id不同，id值越大，越先被执行
    3. id有相同有不同，越大越优先，其余顺序执行。
      1. derived衍生表，derived2就是Id为2查询出来的表作为衍生表。
  2. select_type：查询的类型，主要用于区别普通、联合、子查询等复杂查询
    1. simple：简单的select查询，查询中不包含子查询或union
    2. primary：查询中若包含任何复杂的子查询，最外层查询则被标记为
    3. subquery：在select或where列表中包含的子查询
    4. derived：在from列表中包含的的子查询被标记为derived（衍生），mysql会递归执行这些子查询，把结果放在临时表里
    5. union：若第二个select出现在union之后，则会标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
    6. union result：从union表获得结果的select
  3. table：显示这一行的数据是关于哪张表的
  4. type：访问类型排列，显示查询使用了哪种类型
    1. 最好到最差：system>const>eq_ref>ref>range>index>all
    2. 一般来说，保证查询达到range级别，最好达到ref
      1. system：表只有一行记录，const类型的特例
      2. const：标识通过索引一次就查找到，const用于比较primary key 或者 unique索引。因为只匹配一行数据，所以很快。若where id = 1（唯一/主键索引确定）
      3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常用于主键或唯一索引扫描（多个表的唯一/主键索引确定）
      4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行
      5. range：只检索给定范围的行，使用一个索引来选择行。一般来说就是between、<、>，in，这种范围扫描索引扫描比全表扫描好。
      6. index：全索引扫描，与all区别为index类型只遍历树，通常比all快，因为索引文件通常比数据文件小
      7. all：遍历全表以找到匹配的行
  5. possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到字段存在索引，但不一定被实际使用
  6. key：实际使用的索引。如果为Null，则没有使用索引。如果使用了覆盖索引，则该索引出现在key列表中
  7. key_len：表示索引中使用的字节数，显示的值为索引字段最大可能长度，并非实际使用长度。在不损失精度的情况下，长度越短越好。
  8. ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
  9. rows：根据表统计信息及索引寻用情况，大致估算出找到所需的就所需要读取的行数，越小越好
  10. extra：包含不适合在其他列中显示但是十分重要的额外信息
    1. Using filesort：我们创建的索引，虽然mysql用索引来查找，但是排序却没有用到，而是自己在表内又进行了内部排序。Mysql无法利用索引进行排序称作“文件排序”（复合索引c1_c2_c3，where c1=’’,order by c3）
    2. Using temporary：使用了临时表保存中间结果，Mysql在对查询结果排序时使用临时表。常见排序order by，或分组查询group by（复合索引c1_c2,group by c2）
    3. Using index：表示响应的select操作使用了覆盖索引，避免访问了数据行。如果同时出现using where，表明索引被用类执行索引键值查找。如果每日有出现using where，表明索引用来读取数据而并非执行查找。
      1. 覆盖索引：建立的复合索引的个数和顺序，和查找时的个数和顺序相同。就是select的数据列只用从索引中就能取得，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件。
    4. Using where：表明使用了where过滤
    5. using join buffer：使用了连接缓存
    6. impossible where：where子句的值总是false，不能用来获取任何元组
    7. select tables optimized away：(不常见)在没有group by子句下，对索引优化的一些操作
    8. distinct：（不常见）优化distinct操作，在找到第一个匹配的时候就停止找相同值的动作

索引优化

索引分析

单表

  1. 范围查询，后面的索引会失效

  2. 创建所有条件的索引

  3. 删除索引

  4. 创建新索引

    #案例:查询category_id为1且comments大于1的情况下，view最多的article_id
select id,author_id from article where category_id = 1 and comments>1 order by views desc limit 1;
#第一次创建索引
create index idx_article_ccv on article(category_id,comments,views)
#出现以下问题：type为range，用到了索引，但由于comments是范围查找，导致后面的views索引失效，出现Using fileSort
#删除索引
drop index idx_article_ccv on article
#第二次创建索引
create index idx_article_cv on article(category_id,views)
#出现情况：type为ref,用到了索引，并且ref用到了常量，没有出现Using fileSort
两表

  1. 左右连接，索引建在从表上

    #案例：
select * from class left join book on class.card = book.card
#建立索引
create index idx_class_card on class(card)
#出现的情况：建在主表上，type为index,查找rows仍然是所有数据
#建立索引
create index idx_book_card on book(card)
#出现的情况：建立在从表上，type为ref，并且ref是class.card，查找的rows变为1
三表

  1. 左右连接，索引建在从表

    #案例：
select * from class left join book on class.card = book.card left join phone on book.card = phone.card
#建立索引
create index idx_book_card on book(card)
#建立索引
create index idx_phone_card on phone(card)
#出现的情况：建立在从表上，type为ref，并且ref是class.card、book.card，查找的rows变为1

  2. join语句的优化：

    1. 尽可能减少join语句循环嵌套，永远用小表驱动大表（小表作为主表）
    2. 优先优化循环嵌套的内层循环
    3. 保证join语句中被驱动表上的Join条件字段已被索引
    4. 当无法保证被驱动表的join条件字段被索引且内存充足，调大joinbuffer的设置

索引失效

  1. 全值匹配：复合索引3个，用到的查询条件也是3个。

  2. 最左前缀法则：

    1. 带头大哥不能死：第一个索引没有用到，其他索引就会失效
    2. 中间兄弟不能断：第一个索引用到了，但第二个条件中没有用到，则后续的索引都不会用到
    #符合索引c1,c2,c3,c4
select * from test03 where c1 = 'a1' and c3='a3' and c2 ='a2'
#结果：c123都用到了索引，mysql在底层已经优化翻译

  3. 索引列上不做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效而转向全表扫描

    1. 在条件语句中，对索引列包裹有函数

      #符合索引为(nameAgePosition)，失效
select * from staffs where left(name,4) = 'judy'

  4. 范围查询，后面的索引会失效

    #name用到索引，age用到range范围索引，pos失效
select * from staffs where name='judy' and age>25 and pos = 'manager'

#复合索引c1,c2,c3,c4
select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3 = 'a3'
#结果：4个均用到索引

  5. 尽量使用覆盖索引，减少select *

    这样只会在索引中进行查找，而不需要进行io操作查找表中数据

    select * from staffs where name='judy' and age=25 and pos='manager'
#出现的情况：除了索引的列，其他列还需要在表中查找
select name,age,pos from staffs where name='judy' and age=25 and pos='manager'
#出现的情况：只会在索引中查找。出现Using index
select name,age,pos from staffs where name='judy'
#出现的情况：也会用到索引中扫描，出现Using index
select name,age,pos from staffs where name='judy' and age>25 and pos='manager'
#出现的情况：type是ref，而不是range，因为查找的数据都是在索引中查找，级别就是ref，涉及到表中查找才会使range

  6. 使用不等于会导致索引失效

  7. is null,is not null无法使用索引

  8. like以通配符开头会导致索引失效

    1. like百分号加右边

      这种情况下，type是range级别

    2. 如果一定要用 %字符串%，为保证索引不失效，需要使用覆盖索引解决，查询的字段都需要在索引上。如果有一个不在就会导致索引失效从而全盘扫描。

  9. 字符串不加单引号会导致索引失效（自动做了类型转换，参考第三条）

  10. 少用or，索引会失效

案例

#复合索引c1,c2,c3,c4
select * from test where c1='a1' and c2='a2' and c4='a4' order by c3;
#结果：c1,c2用于查找，c3用来排序

select * from test03 where c1='a1' and c2='a2' order by 'c4';
#结果：c1,c2用到，c4没用到排序，产生filesort 内排序

select * from test where c1='a1' and c5='a5' order by c2,c3;
#结果：c1用到，c2,c3用于排序，无filesort
select * from test where c1='a1' and c5='a5' order by c3,c2;
#结果：c1用到，出现filesort
select * from test where c1='a1' and c2='a2' where order by c3,c2;
#结果：c1用到，因为c2是个常量，所以只有c3做了排序，所以不会出现filesort

select * from test where c1='a1' and c4='a4' group by c2,c3;
#结果：c1用到，c2,c3用于排序

select * from test where c1='a1' and c4='a4' group by c3,c2;
#结果，c1用到，出现using filesort,using temporary。

定值是常量，范围之后是失效，最后看排序。
group by分组前必排序，如果排序错乱，则会产生内排序和临时表

一般性建议

  1. 对于单值索引，尽量选择针对当前查询过滤性更好的索引
  2. 在选择组合索引，但其查询过滤性最好的字段应该放在最靠左的位置
  3. 在选择组合索引时，尽量选择能够包含当前查询中where字句更频繁的字段的索引
  4. 尽可能通过分析统计信息和调整查询的写法，来达到合适索引的目的

Order By 优化

#复合索引age_birth
select * from tbl where age > 20 order by age;
#结果：用到索引排序using index
select * from tbl where age > 20 order by birth;
#结果：using filesort
select * from tbl where age > 20 order by birth,age;
#结果: using filesort
select * from tbl where age > 20 order by age asc,birth desc;
#结果：using filesort
select * from tbl where age > 20 order by age desc,birth desc;
#结果：用到索引排序using index(同升、同降都能用到索引)

key a_b_c(a,b,c)
使用索引遵循最左前缀
order by a
order by a,b
order by a,b,c
order by a desc,b desc,c desc
如果where使用索引的最左前缀定义为常量，order by 能使用索引
where a = const order by b,c
where a = const and b = const order by c
where a = const and b > const order by b,c #这里虽然b是范围，但是a是常量和order的bc相连，故生效

索引**不能**进行排序的情况：
order by a asc,b desc,c desc #排序不一致
where g=const order by b,c #丢失a索引
where a=const order by c #丢失b索引
where a=const order by a,d #d不是索引的一部分
where a in (...) order by b,c #对于排序来说，多个相等条件也是范围查询

Mysql支持两种方式排序：FileSort和Index，Index效率高，它指mysql扫描索引本身完成排序，Filesort方式为内排序

会index方式排序的情况：

mysql能为排序与查询使用相同的索引

  1. order by语句使用索引最左前列（最左前缀原则）
  2. 使用where子句与order by子句条件列组合满足索引最左前列

filesort的两种排序算法：

  1. 双路排序：（在mysql5之前）进行两次IO操作，数据可以全部取出
  2. 单路排序：一般进行一次IO操作，性能优于双路。但数据只能取出sort_buffer缓冲区大小，如果没有得到需要的结果，还需要继续取出，从而出现多次IO操作，有可能不如双路。
    1. 优化策略：
      1. 避免使用select *，它会增加字段大小从而占满缓冲区
        1. 如果查询的字段大小总和小于max_length_for_sort_data，就会用单路，否则会用多路
        2. 两种算法都有可能占满sort_buffer，超出后会进行多次取数据的IO操作
      2. 尝试提高sort_buffer_size
      3. 尝试提高max_length_for_sort_data

Group by 优化：

  1. group by 实质是先排序后进行分组，遵循索引的最左前缀原则
  2. 当无法使用索引列，增大max_length_for_sort_data、sort_buffer_size
  3. where高于having，能写在where限定的条件就不要用在having

查询截取分析

in与exists的优化

小表驱动大表，即小的数据集驱动大的数据集

  1. 当B表的数据集小于A表的数据集时，in优于exists

    select * from A where id in (select id from B)
等价于
for select id from B #外循环数据集小
for select * from A where A.id = B.id #内循环数据集大

  2. 当A表数据集小于B表的数据集时，用exists优于in

    select * from A where exists (select 1 from B where B.id = A.id)
等价于
for select * from A #外循环数据集小
for select * from B where B.id = A.id #内循环数据集大

    意思就是：将主查询的数据，放到子查询中做条件验证，根据验证结果（true或false）来决定主查询的数据结果是否得以保留

    Exists: 先查询一次外SELECT，后去子查询里做验证。IN：先查询子SELECT，然后用外表的一个记录做验证。

慢查询日志

是mysql提供的一种日志记录，它用于记录mysql响应时间超过阈值的语句。具体运行时间超过long_query_time

默认情况下，mysql数据库没有开启慢查询日志，需要手动配置参数。不调优的时候，一般建议关闭

  1. 查看是否开启：show variables like '%slow_query_log%'
  2. 开启：set global slow_query_log=1
    1. 开启只会对当前数据库生效，重启mysql会失效
  3. 【可选】指定默认路径：slow_query_log_file，系统默认为：host_name-slow.log
  4. 查看阈值：show variables like 'long_query_time%'，默认为10s
  5. 设置阈值：set global long_query_time=3
    1. 需要重新连接或新开一个会话才会看到生效
  6. 查看抓取了几条慢查询sql：show global status like '%Slow_queries%'

慢查询日志分析工具mysqldumpslow

批量插入数据脚本

案例需求：插入50w个记录，要求员工名和部门编号为随机

  1. 建表

    # 新建库
create database bigData;
use bigData;
 
#1 建表dept
CREATE TABLE dept(  
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,   
dname VARCHAR(20) NOT NULL DEFAULT "",  
loc VARCHAR(13) NOT NULL DEFAULT ""  
) ENGINE=INNODB DEFAULT CHARSET=UTF8 ;  
 
#2 建表emp
CREATE TABLE emp  
(  
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/  
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/  
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/  
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/  
hiredate DATE NOT NULL,/*入职时间*/  
sal DECIMAL(7,2) NOT NULL,/*薪水*/  
comm DECIMAL(7,2) NOT NULL,/*红利*/  
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/  
)ENGINE=INNODB DEFAULT CHARSET=UTF8 ;

  2. 设置参数log_bin_trust_function_creators

    1. 由于开启慢查询日志，需要配置一个参数，否则创建函数会报错DETERMINISTIC
    2. show variables like 'log_bin_trust_function_creators';
    3. set global log_bin_turst_function_creators=1;

  3. 创建函数，保证每条数据都不同

    1. 随机产生字符串

      DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    ##方法开始
 DECLARE chars_str VARCHAR(100) DEFAULT   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 ##声明一个 字符窜长度为 100 的变量 chars_str ,默认值 
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
##循环开始
 WHILE i < n DO  
 SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
##concat 连接函数  ，substring(a,index,length) 从index处开始截取
 SET i = i + 1;
 END WHILE;
 RETURN return_str;
END $$

    2. 随机产生部门编号

      #用于随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num( ) 
RETURNS INT(5)  
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(100+RAND()*10);  
RETURN i;  
 END $$

  4. 创建存储过程，用于插入数据

    1. emp表

      DELIMITER $$
CREATE PROCEDURE insert_emp10000(IN START INT(10),IN max_num INT(10))  /*参数1：起始员工id，参数2：插入的数据量*/
BEGIN  
DECLARE i INT DEFAULT 0;   
#set autocommit =0 把autocommit设置成0  ；事务开启
 SET autocommit = 0;    
 REPEAT  ##重复，先执行后判断
 SET i = i + 1;  
 INSERT INTO emp10000 (empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),FLOOR(1+RAND()*20000),FLOOR(1+RAND()*1000),rand_num());  
 UNTIL i = max_num   ##直到  上面也是一个循环
 END REPEAT;  ##满足条件后结束循环
 COMMIT;   ##执行完成后一起提交
 END $$

    2. dept表

      #执行存储过程，往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))  /*参数1：起始部门id，参数2：插入的数据量*/
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO dept (deptno ,dname,loc ) VALUES (START +i ,rand_string(10),rand_string(8));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END $$

  5. 调用存储过程，插入数据

    DELIMITER ;
CALL insert_dept(100,10); 
CALL insert_emp(100001,500000);

Show Profile

用来分析当前会话中语句执行的资源消耗，记录每一步花费了多少时间

默认情况下，处于关闭状态，并保存最近15此运行结果

步骤

  1. 查看Mysql版本是否支持：show variables like 'profiling'
  2. 开启功能：set profiling = on
  3. 运行sql：执行一些sql语句，用于之后的记录
  4. 查看结果：show profiles
  5. 诊断sql（查询这个sql的整个生命周期每个阶段的时间）：show profile cpu,block io for query 号码，这里的号码是上一步查出来的id
  6. 结论：出现有以下几点，需要优化
    1. converting heap to myisam 查询结果太大，内存不够用
    2. creating tem table 创建临时表
      1. 拷贝数据到临时表
      2. 用完再删除
    3. copying to tmp table on disk 把内存中临时表复制到磁盘
    4. Locked

全局查询日志

作用：用于把所有sql语句，将会记录在mysql库里的general_log表中，可以用下面的指令查看

select * from mysql.general_log;

注意：不要在生产环境开启这个功能

步骤

  1. set global general_log = 1;
  2. set global log_output='TABLE'
文章作者: 小苏
文章链接: https://fenghen0918@github.io/2020/11/10/mysql/mysql-gao-ji-yi/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 小苏 !
mysql
赏
评论
  目录