MySQL优化

目录

架构优化

设计优化

使用优化

查询优化

MySQL优化主要分为架构优化、设计优化、使用优化、查询优化。

架构优化

解决系统规模大，并发量大、数据量大等问题。

• 解决表数据量大

  • 分库分表

  垂直分库：将相关的表，放入不同的数据库，如将电商数据库分为：订单数据库、库存数据库。

  水平分库：将一个数据库的数据，分开存储到不同的服务器上。

  垂直分表：将表的字段分开，放到不同的表中。

  如：商品表(id,名称，价格，详细信息，图片。。。）分为商品和详情表

  某些数据在分页中查看，某些大的数据text\blob放详情表

  水平分表：将一个表的数据分到多张表中

  数据量特别大，会严重影响查询

• 解决并发量大

  • MySQL集群，主从复制

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

  • 读写分离

  建立在MySQL主从复制的基础上，分为主数据库和从数据库

  主数据库负责写（增删改）从数据库负责读（查询）

  主数据库写入数据后，会记录到bin-log文件中，从数据库会将bin-log中的数据同步过来

• 使用数据库中间件 Sharding-jdbc、myCat等

设计优化

• 规范化设计

  • 范式

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

    缺点： 降低查询效率

    反范式 在表中加入冗余字段

    提高查询效率

  表的设计按具体情况范式和反范式结合使用

• 选用合适的存储引擎

  存储引擎是数据库中存储数据的方式，如存储的位置、存储约束、数据结构等的结合

  不同的存储引擎有不同的功能和性能

  常用存储引擎：

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

  不同点：

  	InnoDB	MyIsam
  事务	支持	不支持
  查询性能	略低	高
  锁	支持表锁和行锁，并发性能高	支持表锁
  外键	支持	不支持
  行数保存	不保存，需要用count(*)	保存
  全文索引	不支持	支持
  容错	支持数据恢复，通过bin-log日志	不支持

  推荐使用InnoDB

• 字段优化

  1. 主键

     必须给表设置主键id

     尽量不用业务字段，使用没有意义字段如（int自增）

     int自增效率高于UUID

  2. 数据类型

     字符串尽量使用varchar，不使用char （varchar存储空间更小，数据少查询效率高）

     尽量使用小的数据类型，如：性别 varchar(1) 男 女 --> int 1 0 --> small int --> tiny int (1字节 -128~127)

     有限的值使用 enum, 而不是 varchar

  3. 字段尽量加not null约束

使用优化

1） 加索引

介绍索引的应用场景、分类

2） 加缓存

介绍Redis缓存、MyBatis缓存

3） 使用连接池

介绍Druid\Hikari\c3p0\dbcp

4） 分页查询

查询优化

1. 查询之前，使用explain查看执行计划

2. 尽量避免select *

3. 尽量避免where中使用<>和！=

4. 尽量避免where中列避免使用函数和表达式

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

6. 尽量使用exists代替in

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