5/05day47_Mysql性能优化

MySQL索引.png

回顾

1. mysql函数
    字符串
        concat(str1,str2...);
    日期
        now()
    数学
        truncate(num,截取位);
    高级函数
        if(表达式,"真"，"假");

2. mysql综合练习

3. 事务安全
    处理一个复杂的业务需求，包含多个步骤操作，这些步骤操作要么都成功，要么都失败
    操作
        手动
            begin
            commit/rollback
        自动（在jdbc中使用，明天讲）
    工作原理
        每一个用户都有一个临时的日志空间
    保存点
    特性
        A
        C
        I：隔离性
        D
    不考虑隔离性产生问题
        脏读：开发中必须要避免出现
        不可重复读：
        幻读：
    MySQL提供的四个隔离级别
        read uncommitted
        read committed 
        repeatable read
        serializable

MySQL索引

今日目标

1. mysql性能
        千万级别数据量，有索引和没有索引查询区别
        
2. mysql索引
    创建索引
    创建索引原则
    索引的数据结构
    mysql数据存储引擎

一 MySQL性能

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

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

硬优化、软优化（重点）

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

* 查询密集型
    我们使用查询频率较高，8：2 左右
        我们就可以使用索引来进行优化

* 修改密集型
    ES

-- 查询累计插入和返回数据条数
show global status like 'Innodb_rows%';
1588642111101.png

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

插入千万条记录

create database day22;

use day22;

-- 1. 准备表
CREATE TABLE `user`(
    id INT,
    username VARCHAR(32),
    `password` VARCHAR(32),
    sex VARCHAR(6),
    email VARCHAR(50)
);

-- 2. 创建存储过程，实现批量插入记录
DELIMITER $$ -- 声明存储过程的结束符号为$$
CREATE PROCEDURE auto_insert()
BEGIN
    DECLARE i INT DEFAULT 1;
    START TRANSACTION; -- 开启事务
    WHILE(i<=10000000)DO
        INSERT INTO `user` VALUES(i,CONCAT('jack',i),MD5(i),'male',CONCAT('jack',i,'@itcast.cn'));
        SET i=i+1;
    END WHILE;
    COMMIT; -- 提交
END$$ -- 声明结束
DELIMITER ; -- 重新声明分号为结束符号

-- 3. 查看存储过程
SHOW CREATE PROCEDURE auto_insert;

-- 4. 调用存储过程
CALL auto_insert();

帅哥电脑需要预计3分钟左右

1588642385704.png

慢查询日志

-- 查看慢查询日志开启情况
show variables like '%slow_query_log%';

-- 查看慢查询时间配置
show variables like '%long_query_time%';
1588642838462.png

开启慢查询日志

set global slow_query_log = on;
1588642917407.png

设置慢查询sql的时间阈值

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

测试慢查询日志

1588643235652.png

慢查询日志文件分析

1588643470996.png

二 MySQL索引

2.1 什么是索引

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

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

1588643911985.png

2.2 MySQL索引分类

* 主键（约束）索引
        主键约束+提高查询效率
        
* 唯一（约束）索引
        唯一约束+提高查询效率

* 普通索引
        仅提高查询效率

* 组合（联合）索引
        多个字段组成索引

* 全文索引
        solr、es

* hash索引
        根据key-value 效率非常高

2.3 MySQL索引语法

2.3.1 创建索引

① 直接创建【了解】

-- 创建普通索引
create index 索引名 on 表名(字段);

-- 创建唯一索引
create unique index 索引名 on 表名(字段);

-- 创建普通组合索引
create index 索引名 on 表名(字段1,字段2);

-- 创建唯一组合索引
create unique index 索引名 on 表名(字段1,字段2);

-- 创建学生表
CREATE TABLE student(
 id INT,
 `name` VARCHAR(32),
 telephone VARCHAR(11)
);

-- name字段适合设置什么索引？
CREATE INDEX name_idx ON student(`name`);

-- telephone适合设置什么索引？
CREATE UNIQUE INDEX telephone_uni_idx ON student(telephone);

② 修改表时指定【了解】

-- 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table 表名 add primary key(字段);  --默认索引名：primary

-- 添加唯一索引（除了NULL外，NULL可能会出现多次）
alter table 表名 add unique(字段); -- 默认索引名：字段名

-- 添加普通索引，索引值可以出现多次。
alter table 表名 add index(字段); -- 默认索引名：字段名

-- 指定id为主键索引
ALTER TABLE student ADD PRIMARY KEY(id);
-- 指定name为普通索引
ALTER TABLE student ADD INDEX(`name`);
-- 指定telephone为唯一索引
ALTER TABLE student ADD UNIQUE(telephone);

③ 创建表时指定【掌握】

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

2.3.2 删除索引

-- 直接删除
drop index 索引名 on 表名;

-- 修改表时删除 【掌握】
alter table 表名 drop index 索引名;

-- 删除name普通索引
DROP INDEX name_idx ON student;

-- 删除telephone唯一索引
ALTER TABLE student DROP INDEX telephone_uni_idx;

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

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

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

给这三个字段添加索引

-- 指定id为主键索引
ALTER TABLE USER ADD PRIMARY KEY(id);
-- 指定username为普通索引
ALTER TABLE USER ADD INDEX(username);
-- 指定email为唯一索引
ALTER TABLE USER ADD UNIQUE(email);

再测试有索引情况下查询

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

2.5 索引的优缺点

* 优点
        减少磁盘IO，提高查询效率

* 缺点
        索引占用磁盘空间
        我们在进行增删改时，索引的维护会增加成本，可能会降低服务器性能

2.6 索引创建原则

1. 字段内容可识别度不能低于70%

2. 经常使用where条件搜索的字段

3. 经常使用表连接的字段（内连接、外连接）

4. 经常排序的字段 order by


* 注意：索引本身会占用磁盘空间，不是所有的字段都适合增加索引....

2.7 常见索引失效情况

-- 1.使用like模糊匹配，%通配符在最左侧使用时
select * from user where email like '%jack1234567%';

-- 2.尽量避免使用or，如果条件有一个没有索引，那么会进行全表扫描
select * from user where id = 88 or sex = 'male';

-- 3.在索引列上进行计算
select * from user where  id+1 = 88;

-- 4.使用 !=、 not in、is not null时
select * from user where username != 'jack12';

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

2.8.1 概述

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

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

1566372154562.png

​ 在没有索引的情况下我们执行一条sql语句，那么是表进行全局遍历，磁盘寻址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。

select  * from user where col1=6;

​

​ 为了加快的查找效率，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

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

  5. Hash：通过散列算法，不支持范围查询

数据结构学习网站

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

2.8.3 MySQL中的B+Tree

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

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

1566372947007.png

2.9 数据库的存储引擎

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

2.9.1 MyISAM（非聚集索引）

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

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

CREATE DATABASE day22_pro;
USE day22_pro;

-- 创建 myisam存储引擎表
CREATE TABLE tab_myisam(
 id INT,
 `name` VARCHAR(32)
)ENGINE=MYISAM;
1588659151895.png
1588659294559.png

2.9.2 InnoDB（聚集索引）

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

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

-- 创建 innodb存储引擎表
CREATE TABLE tab_innodb(
 id INT,
 `name` VARCHAR(32)
)ENGINE = INNODB;
1588659606099.png
1588659700008.png

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

老师晚上总结

疑惑|拓展

  1. 索引的分类

    • 主键索引
    • 联合索引(组合索引)
    • 普通索引
    • 唯一索引

  2. 索引的创建

    -- 创建主键索引
ALTER TABLE student ADD PRIMARY KEY(id);

-- 创建唯一索引
ALTER TABLE student ADD UNIQUE(tel);

-- 创建普通索引
ALTER TABLE  student ADD INDEX(NAME,tel);
-- 创建联合索引
ALTER TABLE student ADD UNIQUE(NAME,tel);

  1. 索引的删除

    • alter table 表名 drop index 索引名

  2. 索引要失效的情况

    • 索引值在列上去计算
    • 非空、不等于这些判断
    • 使用like进行模糊查询，左边不准使用%。
    • 使用or关键，并且or的某一边没有建立索引。

    ==疑问1：使用模糊查询的时候的%再最右边，请问索引会失效吗？==

    ==疑问2： 使用or关键字的时候，如果左右两边都是索引列呢，那么请问会不会失效呢？==

  3. 数据结构有点慌

1588677900727.png

老师：工作或者面试的时候会不会叫我写出一个B+tree出来呢？ 不会的，go

工作中我们是不需要写，因为java都已经封装好了。

面试的时候： 我们现在需要从一千万数据中查找一条数据，但是搜索效率低？请问

有什么解决方案？ 索引，索引里面使用B+tree数据结构存储，所以搜索的效率高。

  1. 工作中什么时候需要创建索引值

    • 新开发的系统： 一般是在系统设计阶段， 你的项目经理、项目组长会评估这个系统的搜索量、数量量。 然后就会在需求文档中给你说明那些字段需要创建索引。

    • 在做系统后期维护: 如果客户反馈某些数据搜索的速度比较慢，那么这时候你可以考虑客户搜索的列

      适不适合做索引，如果可以那么就建立索引。

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,470评论 6赞 501
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,393评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 162,577评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,176评论 1赞 292
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,189评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,155评论 1赞 299
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,041评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,903评论 0赞 274
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,319评论 1赞 310
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,539评论 2赞 332
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,703评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,417评论 5赞 343
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,013评论 3赞 325
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,664评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,818评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,711评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,601评论 2赞 353