一 MySQL性能

1.1 分析-数据库查询效率低下

  1. 我们进入公司进行项目开发往往关注的是业务需求和功能的实现，但是随着项目运行的时间增加，数据量也就增加了，这时会影响到我们数据库的查询性能

硬优化、软优化（重点）

1.2 分析-执行次数比较多的语句

  1. * 查询密集型
  2.     我们使用查询频率较高，8：2 左右
  3.         我们就可以使用索引来进行优化
  5. * 修改密集型
  6.     ES
  1. -- 查询累计插入和返回数据条数
  2. show global status like 'Innodb_rows%';

1588642111101.png

1.3 查看-sql语句的执行效率

插入千万条记录

  1. create database day22;
  3. use day22;
  5. -- 1. 准备表
  6. CREATE TABLE `user`(
  7.     id INT,
  8.     username VARCHAR(32),
  9.     `password` VARCHAR(32),
  10.     sex VARCHAR(6),
  11.     email VARCHAR(50)
  12. );
  14. -- 2. 创建存储过程，实现批量插入记录
  15. DELIMITER $$ -- 声明存储过程的结束符号为$$
  16. CREATE PROCEDURE auto_insert()
  17. BEGIN
  18.     DECLARE i INT DEFAULT 1;
  19.     START TRANSACTION; -- 开启事务
  20.     WHILE(i<=10000000)DO
  21.         INSERT INTO `user` VALUES(i,CONCAT('jack',i),MD5(i),'male',CONCAT('jack',i,'@itcast.cn'));
  22.         SET i=i+1;
  23.     END WHILE;
  24.     COMMIT; -- 提交
  25. END$$ -- 声明结束
  26. DELIMITER ; -- 重新声明分号为结束符号
  28. -- 3. 查看存储过程
  29. SHOW CREATE PROCEDURE auto_insert;
  31. -- 4. 调用存储过程
  32. CALL auto_insert();

**
1566375320229.png

慢查询日志

  1. -- 查看慢查询日志开启情况
  2. show variables like '%slow_query_log%';
  4. -- 查看慢查询时间配置
  5. show variables like '%long_query_time%';

1588642838462.png

开启慢查询日志

  1. set global slow_query_log = on;

设置慢查询sql的时间阈值

  1. -- 全局配置（下次生效...）
  2. set global long_query_time=3;
  3. -- 临时（会话）配置（本次会话窗口生效）
  4. set session long_query_time=3;

1588643084135.png

测试慢查询日志

1588643235652.png

慢查询日志文件分析

1588643470996.png

二 MySQL索引

2.1 什么是索引

将数据进行排序整理的过程就称为索引

我们根据索引去查，提高效率

1588643911985.png

2.2 MySQL索引分类

  1. * 主键（约束）索引
  2.         主键约束+提高查询效率
  4. * 唯一（约束）索引
  5.         唯一约束+提高查询效率
  7. * 普通索引
  8.         仅提高查询效率
  10. * 组合（联合）索引
  11.         多个字段组成索引
  13. * 全文索引
  14.         solr、es
  16. * hash索引
  17.         根据key-value 效率非常高

2.3 MySQL索引语法

2.3.1 创建索引

① 直接创建【了解】

  1. -- 创建普通索引
  2. create index 索引名 on 表名(字段);
  4. -- 创建唯一索引
  5. create unique index 索引名 on 表名(字段);
  7. -- 创建普通组合索引
  8. create index 索引名 on 表名(字段1,字段2);
  10. -- 创建唯一组合索引
  11. create unique index 索引名 on 表名(字段1,字段2);
  1. -- 创建学生表
  2. CREATE TABLE student(
  3.  id INT,
  4.  `name` VARCHAR(32),
  5.  telephone VARCHAR(11)
  6. );
  8. -- name字段适合设置什么索引？
  9. CREATE INDEX name_idx ON student(`name`);
  11. -- telephone适合设置什么索引？
  12. CREATE UNIQUE INDEX telephone_uni_idx ON student(telephone);

② 修改表时指定【了解】

  1. -- 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
  2. alter table 表名 add primary key(字段);  --默认索引名：primary
  4. -- 添加唯一索引（除了NULL外，NULL可能会出现多次）
  5. alter table 表名 add unique(字段); -- 默认索引名：字段名
  7. -- 添加普通索引，索引值可以出现多次。
  8. alter table 表名 add index(字段); -- 默认索引名：字段名
  1. -- 指定id为主键索引
  2. ALTER TABLE student ADD PRIMARY KEY(id);
  3. -- 指定name为普通索引
  4. ALTER TABLE student ADD INDEX(`name`);
  5. -- 指定telephone为唯一索引
  6. ALTER TABLE student ADD UNIQUE(telephone);

③ 创建表时指定【掌握】

  1. -- 创建教师表
  2. CREATE TABLE teacher(
  3.  id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
  4.  `name` VARCHAR(32),
  5.  telephone VARCHAR(11) UNIQUE, -- 唯一索引
  6.  sex VARCHAR(5),
  7.  birthday DATE,
  8.  INDEX(`name`) -- 普通索引
  9. );

2.3.2 删除索引

  1. -- 直接删除
  2. drop index 索引名 on 表名;
  4. -- 修改表时删除 【掌握】
  5. alter table 表名 drop index 索引名;
  1. -- 删除name普通索引
  2. DROP INDEX name_idx ON student;
  4. -- 删除telephone唯一索引
  5. ALTER TABLE student DROP INDEX telephone_uni_idx;

2.4 千万表记录索引效果演示

先来测试没有索引情况下查询

  1. -- 1.指定id查询
  2. select * from user where id = 8888888;
  3. -- 2.指定username精准查询
  4. select * from user  where username = 'jack1234567';
  5. -- 3.指定email模糊查询
  6. select * from user  where email like 'jack1234567%';

1588646695848.png

给这三个字段添加索引

  1. -- 指定id为主键索引
  2. ALTER TABLE USER ADD PRIMARY KEY(id);
  3. -- 指定username为普通索引
  4. ALTER TABLE USER ADD INDEX(username);
  5. -- 指定email为唯一索引
  6. ALTER TABLE USER ADD UNIQUE(email);

再测试有索引情况下查询

  1. -- 1.指定id查询
  2. select * from user where id = 8888888;
  3. -- 2.指定username精准查询
  4. select * from user  where username = 'jack1234567';
  5. -- 3.指定email模糊查询
  6. select * from user  where email like 'jack1234567%';

1588647278406.png

2.5 索引的优缺点

  1. * 优点
  2.         减少磁盘IO，提高查询效率
  4. * 缺点
  5.         索引占用磁盘空间
  6.         我们在进行增删改时，索引的维护会增加成本，可能会降低服务器性能

2.6 索引创建原则

  1. 1. 字段内容可识别度不能低于70%
  3. 2. 经常使用where条件搜索的字段
  5. 3. 经常使用表连接的字段（内连接、外连接）
  7. 4. 经常排序的字段 order by
  10. * 注意：索引本身会占用磁盘空间，不是所有的字段都适合增加索引....

2.7 常见索引失效情况

  1. -- 1.使用like模糊匹配，%通配符在最左侧使用时
  2. select * from user where email like '%jack1234567%';
  1. -- 2.尽量避免使用or，如果条件有一个没有索引，那么会进行全表扫描
  2. select * from user where id = 88 or sex = 'male';
  1. -- 3.在索引列上进行计算
  2. select * from user where  id+1 = 88;
  1. -- 4.使用 !=、 not in、is not null时
  2. select * from user where username != 'jack12';

2.8 索引的数据结构【了解】

2.8.1 概述

我们知道索引是帮助MySQL高效获取排好序的数据结构。

为什么使用索引后查询效率提高很多呢？接下来我们来了解下。

1566372154562.png

  1. 在没有索引的情况下我们执行一条sql语句，那么是表进行全局遍历，磁盘寻址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。
  1. select  * from user where col1=6;
  1. 为了加快的查找效率，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。
  1. select  * from user where col2=89;

2.8.2 索引的数据结构

  1. 二叉树：左边的子节点比父节点小，右边的子节点比父节点大1588649871508.png
  2. 红黑树：平衡二叉树（左旋、右旋）1588650208409.png
  3. BTree：多路平衡搜索树1588650730396.png

  4. B+Tree：优化BTree（非叶子节点：索引+指针、叶子节点：索引+数据【地址】）1588651284679.png

    1. 它可以做到减少树的高度(深度)/支持范围查询/支持排序


  6. Hash：通过散列算法，不支持范围查询（hash是无序的）

数据结构学习网站

  1. https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

2.8.3 MySQL中的B+Tree

  1. -- 查看mysql索引节点大小
  2. show global status like 'innodb_page_size';

MySQL中的 B+Tree 索引结构示意图：
1566372947007.png

2.9 数据库的存储引擎

MySQL存储引擎的不同，那么索引文件保存的方式也有所不同，常见的有二种存储引擎MyISAM和InnoDB。

2.9.1 MyISAM（非聚集索引）

MySQL5.5版本之前默认存储引擎

特点：不支持事务、不支持外键约束

  1. CREATE DATABASE day22_pro;
  2. USE day22_pro;
  4. -- 创建 myisam存储引擎表
  5. CREATE TABLE tab_myisam(
  6.  id INT,
  7.  `name` VARCHAR(32)
  8. )ENGINE=MYISAM;

1566374878261.png
1566374932300.png

2.9.2 InnoDB（聚集索引）

MySQL5.5版本之后默认存储引擎

特点：支持事务、支持外键约束

  1. -- 创建 innodb存储引擎表
  2. CREATE TABLE tab_innodb(
  3.  id INT,
  4.  `name` VARCHAR(32)
  5. )ENGINE = INNODB;

1566375122187.png
1566375228182.png

innodb存储引擎必须要设置主键（整型），且自增类型….