首页  思维导图  详情

MySQL优化

2019-09-09 11:01:26   5  举报





AI智能生成

MYSQL优化

数据库优化

MYSQL性能优化

数据库性能优化

软件开发

作者其他创作

大纲/内容

1、概述

为什么要优化

一个应用吞吐量瓶颈往往出现在数据库的处理速度上

随着应用程序的使用，数据库数据逐渐增多，数据库处理压力逐渐增大

关系型数据库的数据是存放在磁盘上的，读写速度较慢（与内存中的数据相比）

如何优化

表、字段的设计阶段，考量更优的存储和计算

数据库自身提供的优化功能，如索引

横向扩展，主从复制、读写分离、负载均衡和高可用

典型SQL语句优化（收效甚微）

2、字段设计

典型方案

对精度有要求

decimal

小数转整数

尽量使用整数表示字符串

inet_aton("ip")

inet_ntoa(num)

尽可能使用not null

null数值的计算逻辑比较复杂

定长和非定长的选择

较长的数字数据可以使用decimal

char为定长（超过长度的内容被截掉），varchar为非定长，text对内容长度的保存额
外保存，而varchar对长度的保存占用数据空间

字段数不要过多、字段注释是必要的、字段命名见名思意、可以预留字段以备扩展

范式

第一范式

字段原子性（关系型数据库有列的概念，默认就符合了）

第二范式

消除对主键的部分依赖（因为主键可能不止一个）

使用一个与业务无关的字段作为主键

第三范式

消除对主键的传递依赖

高内聚，如商品表可分为商品简略信息表和商品详情表

3、存储引擎的选择（MyISAM和Innodb）

功能差异

Innodb支持事务、行级锁定、外键

存储差异

存储方式

MyISAM的数据和索引是分开存储的（.MYI,.MYD），而Innodb是存在一起的（.frm）

表可移动性

可以通过移动表对应的MYI和MYD能够实现表的移动，而Innodb还有额外的关联文件

碎片控件

MyISAM删除数据时会产生碎片空间（占用表文件空间），需要定期通过optimize
table table-name手动优化，而Innodb不会。

有序存储

Innodb插入数据时按照主键有序来插入，因此表中数据默认按主键有序（耗费写入时间，
因为需要在b+tree中查找插入点，但查找效率高）

选择依据

读多写少用MyISAM

新闻、博客网站

读多写也多用Innodb

支持事务/外键，保证数据一致性、完整性

并发能力强（行锁）

4、索引

什么是索引

从数据中提取的具有标识性的关键字，并且有到对应数据的映射关系

类型

主键索引 primary key

要求关键字唯一且不为null

普通索引 key

符合索引仅按照第一字段有序

唯一索引 unique key

要求关键字唯一

全文索引 fulltext key(不支持中文)

索引管理语法

查看索引

show create table student

desc student

建立索引

创建时指定，如 first_name varchar(16),last_name varchar(16),key name(first_name,last_name)

更改表结构

alter table student add key/unique key/primary key/fullextx key key_name(first_name,last_name)

删除索引

alter table student drop key key_name

如果删除的是主键索引，并且主键自增长，则需要alter_modify 先取消自增长再删除

执行计划explain

分析SQL执行是否用到了索引，用到了什么索引

索引的使用场景

where

如果查找字段都建立了索引，则会索引覆盖

order by

如果排序字段建立了索引，而索引又是有序排列的，直接根据索引拿到对应数据即可，与
读取查询出来的所有数据再排序相比效率很高

join

如果用join on的条件字段简历了索引，查找会变得高效

索引覆盖

直接对索引查找，而不去读取数据

语法细节

对数据表的改动会导致基于该数据表的所有缓存失效（表管理的层面）

即使建立了索引，有些场景也不一定使用

where id+1 =？建议协程where id = ? -1, 即保证索引字段的独立出现

like 语句不要再关键字前模糊匹配，即'%keyword%'不会使用索引，而'keyword%'会使用索引

or关键两边条件字段都建立索引时才会使用索引，只要有一边不是就会做全表扫描

状态值，像性别这样的状态值，一个关键字对应很多条数据，会认为使用索引比全表扫描效率还低

索引的存储结构

btree

搜索多叉数：结点内关键字有序排列，关键字之间有一个指针，查找效率log(nodeSize,N)
其中nodeSize指一个结点内关键字数量（这取决于关键字长度和结点大小）

b+tree

由btree升级而来，数据和关键字存在一块空间，省去了由关键字到数据的映射找数据存放地的时间

5、查询缓存

将select查询结果缓存起来，key为SQL语句，value为查询结果

如果SQL功能一样，但只是多个空格或略微改动都会导致key的不匹配

客户端开启

query_cacha_type

0-不开启

1-开启，默认缓存每条select,针对某个sql不缓存：select sql-no-cache

2-开启，默认都不缓存，通过select sql-cache制定缓存哪一个

客户端设置缓存大小

query_cache_size

重置缓存

reset_query_cache

缓存失效

6、分区

默认情况下一张表对应一组存储文件，但当数据量较大时（通常千万条级别）需要将数据分到多组存储文件，保证单个文件的处理效率

partition by 分区函数（分区字段）（分区逻辑）

hash-分区字段为整型

key-分区字段为字符串

range-基于比较，只支持iess than

list-基于状态值

分区管理

创建时分区

create table article() partition by key(title) partitions 10

修改表结构

alter table article add partition(分区逻辑)

分区字段应选择常用的检索字段，否则区分意义不大

7、水平分隔和垂直分隔

水平

多张结构相同的表存储同一类型数据

单独一张表保证id唯一性

垂直

分割字段到多张表，这些表记录是一一对应关系

8、集群

主从复制

首先手动将slave和master同步一下

stop slave

master导出数据导slave执行一遍

show master status with read lock 记录File和Position

到slave上change master to

start slave查看Slave_IO_Running和Slave_SQL_Running，必须都为YES

master可读可写，但slave只能读，否则主从复制会失效需要重新手动同步

mysqlreplicate快速配置住处复制

读写分离（基于主从复制）

使用原生javax.sql.Connection

WriteDatabase提供写连接

ReadDatabase提供读连接

借助Spring_AOP和Aspect实现数据源动态切换

RoutingDataSourceImpl extends AbstractRountingDataSource,重写
determineDatasource，注入到SqlSessionFactory,配置defaultTargetDataSource和
targetDatasource（根据detemineDatasource的返回值选择具体数据源value-ref）

DatasourceAspect切面组件，配置切入点@Pointcut aspect()(所有DAO类的所有方法），
配置前置增强@Before("aspect()") before(Joinpoint point),通过ponit.getSignature.getName
获取方法名，与NETHOD_TYPE_MAP的前缀集合对比，将write/read设置到当前线程上，（也是
接下来要执行DAO方法的形成，前置增强将其拦截下来了）

DatasourceHandler，使用ThreadLoacal在前置通知中将方法要使用到的数据源绑定到执行该方法的
线程上，执行方法要获取数据源时再根据当前线程获取

负载均衡

算法

轮询

加权轮询

依据负载情况

高可用

为单机服务提供一个冗余机

心跳监测

虚拟IP

主从复制

9、典型SQL