搜文章
推荐 原创 视频 Java开发 iOS开发 前端开发 JavaScript开发 Android开发 PHP开发 数据库 开发工具 Python开发 Kotlin开发 Ruby开发 .NET开发 服务器运维 开放平台 架构师 大数据 云计算 人工智能 开发语言 其它开发
Lambda在线 > 菜鸟NoFood > MYSQL的优化与实践

MYSQL的优化与实践

菜鸟NoFood 2020-05-23

Mysql的优化方法:

  • 设计:存储引擎、字段类型、范式与逆范式

  • 功能:索引、缓存、分区分表

  • 架构:主从复制、读写分离、负载均衡

  • 合理使用sql(优化查询sql)

一、存储引擎

  • 存储引擎是一种用来存储mysql中记录的一种特定文件结构,是创建mysql数据表的选择类型(如:innodb 、Myisam)


  • 查看mysql的引擎在Dos窗口下命令:show engines(发动机、引擎、火车头)

  • 主要两个引擎说明:Innodb与myisam

  1. Innodb:mysql版本大于5.5 支持事务、行级锁定、外键约束。事务安全型存储引擎,注重完整性与安全性

  2. 在mysql目录下data下存储文件格式如下:


    MYSQL的优化与实践

  3. db.opt:存放数据库的配置信息,如数据库字符集还有编码格式

  4. innodb_test.frm:是表结构文件,仅存储表的结构、元数据(meta)、包括表结构定义信息(任意表引擎都会拥有一个frm文件)

  5. innodb_test.idb:是当前表的索引文件,包含单独一个表的数据及索引内容。

innodb擅长事务、数据的完整性以及高并发处理,不擅长快速插入和检索


MyIsAm:

  • mysql版本小于等于5.5  isam(indexed Sequential access method)缩写,是一种文件系统,擅长处理、高速读与写

  • 在mysql目录下data下存储文件格式如下:


    MYSQL的优化与实践

  • .myd 是数据存储文件 .myi 是索引存储文件

  • 数据的存储顺序为插入顺序(无排序)

  • 功能:支持全文索引(mysql版本大于5.6innodb也支持)

  • 并发性:仅支持表级锁定,不支持高并发


二、字段类型

  1. 整型包括(tinyint、smallint、mediumint、int和bigint)

  2. 浮点型包括(float--单精度、double--双精度)

四、分区分表

  •     分区(partition):是将数据分段划分在多个位置存放,可以是同一块磁盘也可以在不同的机器。

分区之后表面还是一张表,但数据散列到多个位置。app读写操作还是大表名字,db自动去组织分区的数据

  • 注意 每个分区,就是独立的表,都要存储该分区数据的 数据、索引等信息

  • 如何创建分区:在创建表时,指定分区的选项:create table table_name(定义) partition by 分区算法(参数) 分区选项


  • 如:Partition by key (id) partitions 5;采用key取余算法,根据id的值进行取余,即对 5 取余 然后分到5个区里

  • 注意===》分区与存储引擎无关,是mysql逻辑层完成的, 使用 show variables like 'have_partitioning' 查看是否支持分区

目前提供四种分区算法:取余:key,hash  条件:list,range。其中参与分区的字段需要为主键的一部分

  1. key:取余 按照某个字段进行取余 partition by key(id) partitions 5: 分成5个区,就是对5取余,将id对5取余。

  2. hash:取余 按照某个表达式的值进行取余,如:学生表 按照生日的月份 划分到12个表中:partition by hash (month(birthday)) partitions 12;

  3. 条件:list 列表 需要指定的每个分区数据的存储条件。按照生日中月份 分成 春夏秋冬四个分区 partition by list (month(birthday)) (

    partiton chun values in (3,4,5),

    partiton xia values in (6,7,8,9),

    partiton qiu values in (10,11),

    partiton dong values in (12,1,2),

    );list 条件依赖的数据是列表形式

  4. 条件:range 范围 条件依赖的数据是一个条件表达式 如 按照生日年份分成不同的年龄段 partition by list (year(birthday)) (

    partiton age_70 values less than (1980),

    partiton age_80 values less than (1990),

    partiton age_90 values less than (2000),

    partiton age_00 values less than maxvalue

    )

  5. 注意 还有添加分区 减少分区数量 删掉分区(删除会使数据丢失,添加不会)

4.1 分表:分表是将一个大表按照一定规则分解成多张独立存储空间的实体表就是子表。mysql5.1后用分区代替分表

  1. 水平分表 创建结构相同的n个表(学生表1.2.3....)  在创建管理学生id的表

  2. 垂直分表 存在多个字段 且这些字段可以分为  常用与非常用 所以就可以把他们拆分来存储 

    基础信息表Student_base

    Id name age

    额外信息表Student_extra

    Id 籍贯 政治面貌

五、服务器架构

  • 主从复制 

    Mysql服务器内部支持复制功能,仅仅需要通过配置完成下面的拓扑结构。一主多从典型结果:主服务器负责写数据。从服务器负责读数据。复制功能mysql会自带

  • 读写分离 负载均衡

    php不再操作MYSQL数据库服务器,而是去操作读写分离、负载均衡服务器,只要服务器安装了mysql proxy或Ameoba软件就可以实现读写分离和负载均衡

    读写分离是指该服务器会判断客户端的操作是读还是写,从而选择操作mysql主服务器还是从服务器

    负载均衡算法是指,客户端读操作时,该服务器会根据取余算法去选择一台从服务器


版权声明:本站内容全部来自于腾讯微信公众号,属第三方自助推荐收录。《MYSQL的优化与实践》的版权归原作者「菜鸟NoFood」所有,文章言论观点不代表Lambda在线的观点, Lambda在线不承担任何法律责任。如需删除可联系QQ:516101458

文章来源: 阅读原文

相关阅读

关注菜鸟NoFood微信公众号

菜鸟NoFood微信公众号:gh_fae214c628a1

菜鸟NoFood

手机扫描上方二维码即可关注菜鸟NoFood微信公众号

菜鸟NoFood最新文章

精品公众号随机推荐