进阶学习|进阶学习之优化MySQL 学习|b树|mysql

索引是什么
是一种特殊的数据，保存实际数据的位置，通过索引能快速定位到实际的数据，提高查询速度。
如：书籍目录、楼层索引
优缺点
优点：大大提高查询速度
缺点：
? 1）占用存储空间
? 2）降低增删改数据
? 3）创建时比较耗时
索引的分类
按功能分类

普通索引
建在某一个列上，提高按该列查询速度，没有特别约束，表中可以建多个
语法：

create index 索引名 on 表名(列名);
主键索引
建主键时，同时创建，约束是不能重复、不能为空、表只能有一个
唯一索引
建在某一个列上，提高按该列查询速度同时该列不能重复，表中可以建多个

create unique index 索引名 on 表名(列名);
全文索引
用于长文本的列（text）

create fulltext index 索引名 on 表名(列名);
组合索引
用于多个列的查询

create index 索引名 on 表名(列名1,列名2,列名3);

最左前缀原则：把最重要的列放到左边
查询时如果没有最左边的列，索引就失效
列1生效
列1,列2 生效
列1,列2,列3 生效
列2,列3 失效

索引的使用
explain 查询语句;
分析查询语句，是否能正常使用索引
最重要的：type、key、ref

文章图片

使用场景

1. 数据量特别大（百万级别） 2. 列没有太多空值 3. 列没有很多重复 4. 列经常用于查询和排序

失效情况

like ‘%xxx’ 模糊查询%在前面
!= 情况
使用or，or两边每个列都需要有索引，否则失效
使用组合索引，没有使用最左边的列
条件中出现表达式或函数，如： age - 1 = 19、year(birth_day) = 1990
is null 或 is not null、in

索引的数据结构
B-Tree和B+Tree
B-Tree属于平衡搜索多叉树，分为根节点、枝节点、叶子节点，每个节点由：键、数据、指针组成
指针指向下面的子节点，搜索时采用二分查找，效率比较高。
查询效率和树的高度（高度越高、效率越低）

文章图片

B+Tree是B-Tree升级版
将数据全部放到叶子节点，非叶子节点只保存键和指针，磁盘块就能保存更多节点，降低了树的高度，大大提升了查找效率。
?

文章图片

数据库设计

需求分析，确定功能
概要设计

? 概念建模：确定系统涉及对象，以及对象相关属性，还有对象之间的关系
? E-R图，容易理解
? 1) 实体，长方形
? 2) 属性，椭圆形
? 3) 关系，菱形
? 4) 连接线

文章图片

详细设计

? 逻辑建模：设计表，表的字段、类型、约束等
? 数据库设计文档（数据库字典）
? 三范式
? 第一范式：每一列原子性，不可再分
? 第二范式：每一列都和主键相关
? 第三范式：每一列都和主键直接相关，消除传递依赖
? 学生表：id、姓名、系名、系主任
? —> 学生表： id、姓名、系id
? —> 系表：id、名称、系主任
? 范式
? 优点：规范数据库设计，消除冗余，方便数据的修改
? 缺点：降低查询效率

开发阶段

? 物理建模：建库建表建约束…
MySQL优化架构优化
? 系统规模大，用户量、数据量大

- MySQL集群，多台MySQL服务器- 分库分表

? 垂直分库
? 将相关的表，放入不同的数据库，如将电商数据库分为：订单数据库、库存数据库
? 水平分库
? 将一个数据库的数据，分开存储到不同的服务器上
? 垂直分表
? 将表的字段分开，放到不同的表中
? 如：商品表分为商品和详情表
? 某些数据在分页中查看，某些大的数据text\blob放详情表
? 水平分表
? 数据量特别大，会严重影响查询
? 将一个表的数据分到多张表中

主从复制
多台MYSQL服务器，分为主（master）和从（slave）服务器
一主多从，多主多从

-读写分离

? 建立在MySQL主从复制的基础上，分为主数据库和从数据库
? 主数据库负责写（增删改）从数据库负责读（查询）
? 主数据库写入数据后，会记录到bin-log文件中，从数据库会将bin-log中的数据同步过来
? 使用数据库中间件：Sharding-jdbc、myCat等

文章图片

设计优化

范式
? 优点：规范数据库设计，消除冗余，方便数据的修改
? 缺点：降低查询效率
反范式在表中加入冗余字段
? 提高查询效率
表的设计按具体情况范式和反范式结合使用

选用合适的存储引擎
存储引擎是数据库中存储数据的方式，如存储的位置、存储约束、数据结构等的结合
不同的存储引擎有不同的功能和性能
常用存储引擎：

InnoDB
MyIsam
Memory （不能持久化）
Blackhole
Performance Schema
…

不同点：

	InnoDB	MyIsam
事务	支持	不支持
查询性能	以前低于MyIsam，目前差不多	快
锁	支持表锁和行锁，并发性能高	支持表锁
外键	支持	不支持
行数保存	不保存，需要用count(*)	保存查询数据
索引	非聚簇索引（索引和实际数据不在一起）	聚簇索引（索引和实际数据在一起）

一般情况下使用InnoDB

字段优化
1. 主键
  必须给表设置主键id
  尽量不用业务字段，使用没有意义字段如（int自增）
  int自增效率高于UUID
2. 数据类型
  字符串尽量使用varchar，不使用char （varchar存储空间更小，数据少查询效率高）
  尽量使用小的数据类型，如：性别 varchar(1) 男女 --> int 1 0 --> small int --> tiny int (1字节 -128~127)
3. 尽量加not null约束

使用优化
? 1）索引
? 数据量大，使用索引
? 介绍索引的应用场景、分类
? 2）缓存
? Redis缓存、MyBatis缓存
? 3）使用连接池
? Druid\Hikari\c3p0\dbcp
? 4）分页查询
查询优化
? 1. 查询之前，使用explain查看执行计划
? 2. 尽量避免select *
? 3. 尽量避免where中使用<>和！=
? 4. 尽量避免where中使用is null和is not null
? 5. 尽量避免where中列避免使用函数和表达式 where 1 + age = 20 where max(age) = 20
? 6. 尽量避免模糊查询时，通配符在前面，如 name like ‘%xx’
? 7. 尽量使用exists代替in

尽量避免where中使用or，union 代替or
select name from student where age = 20 or address =‘武汉’
==>
select name from student where age = 20
union

is not null
? 5. 尽量避免where中列避免使用函数和表达式 where 1 + age = 20 where max(age) = 20
? 6. 尽量避免模糊查询时，通配符在前面，如 name like ‘%xx’
? 7. 尽量使用exists代替in

尽量避免where中使用or，union 代替or
select name from student where age = 20 or address =‘武汉’
==>
select name from student where age = 20
union
【进阶学习|进阶学习之优化MySQL】select name from student where address =‘武汉’