MySQL索引

索引

索引是什么

索引是一种特殊的数据，能够保存数据的位置，通过索引能够快速定位到相应的数据。

索引的优缺点

• 优点
  1.提升查询速度
• 缺点
  1.索引也是数据，会占用空间
  2.创建时会耗费时间
  3.降低插入删除数据的速度

索引的分类

1.普通索引

建在某一列上，没有特别的约束，表中可以创建多个

create index 索引名 on 表名（列名）

2.主键索引

在添加主键时，同时创建，约束是不能为空，不能重复，表中只能有一个

3.唯一约束

建在某一列上，约束是不能重复，表中可以创建多个

create unique index 索引名 on 表名（列名）

4.全文索引

用于长文本的列（text）

create fulltext index 索引名 on 表名（列名）

5.组合索引

用于多个列的查询

create index 索引名 on 表名（列名1，列名2，列名3）

最左前缀原则：查询时如果没有最左边的列，索引就会失效

索引的使用

使用explain开启查询计划 explain 查询语句

使用场景

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

 索引失效的情况

1. like'%XX' 模糊查询时通配符在前面
2. !=的情况
3. 使用or时（除非or两边都有索引）
4. 组合索引没有遵循最左前缀原则时
5. 条件中含有表达式或函数
6. is null 、not null 、in

建议使用union替代or，exist 替代in

索引的数据结构

B-Tree

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

B+Tree

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

数据库设计

1) 需求分析，确定功能

2) 概要设计

概念建模：确定系统涉及对象，以及对象相关属性，还有对象之间的关系

E-R图，容易理解

1) 实体，长方形

2) 属性，椭圆形

3) 关系，菱形

4) 连接线

3) 详细设计

逻辑建模：设计表，表的字段、类型、约束等

数据库设计文档（数据库字典）

三范式

第一范式：每一列原子性，不可再分

第二范式：每一列都和主键相关

第三范式：每一列都和主键直接相关，消除传递依赖

学生表：id、姓名、系名、系主任

---> 学生表： id、姓名、系id

---> 系表：id、名称、系主任

范式

优点： 规范数据库设计，消除冗余，方便数据的修改

缺点： 降低查询效率

4) 开发阶段

物理建模：建库建表建约束...

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中列使用函数和表达式 

6. 尽量避免模糊查询时，通配符在前面

7. 尽量使用exists代替in

8.尽量避免where中使用or，使用union 代替or