Mysql调优

优化的维度

  • 表结构与索引
  • SQL语句
  • Mysql参数
  • 硬件和系统配置

数据库的水平和垂直扩展能力,提前规则未来读写和数据量的增长,分库分表方案。按UID维度散列,分为4个库,每个库32张表,保证单表数据量控制在千万级别。

字段选择合适的最小空间的数据类型,如年龄使用tinyint

多字段表拆分为多个表,必要时使用中间表关联。

范式与反范式、反范式即保持一定的冗余。

分析慢查询日志,寻找需要优化的语句

利用分析工具,如explain、profile。

查询语句避免使用select *,要指定需要的列。可以避免查询列字段的元信息。

1.使用内置缓存
大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次,这些查询结果会被缓存。
对于像 NOW() 、RAND()、CURDATE()等SQL函数都不会开启查询缓存,因为这些函数的返回是会不定的易变的。所以需要用一个变量来代替MySQL的函数,从而开启缓存。

2.使用 EXPLAIN 执行SELECT查询
使用 EXPLAIN 关键字可以打印出MySQL是如何处理SQL语句的,可以帮助分析查询语句或者表结构的性能瓶颈。
EXPLAIN 的查询结果还会告诉索引主键是被如何利用的、数据表是如何被搜索和排序的等等。
在SELECT语句(推荐挑选那个最复杂的,有多表联接的),把关键字EXPLAIN加到前面,可以使用phpmyadmin来执行。然后会看到一张表格。

3. 追加 LIMIT 1
查询表有时已经知道结果只会有一条结果,此时加上 LIMIT 1 可以增加性能。MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。
但是在用explain查询分析,显示limit 1加与不加,性能相同。是因为可能是使用了缓存吗?

4. 搜索字段建索引
索引并不一定是给主键或是唯一的字段,如果表中有某个字段经常用来做搜索,那么请为其建立索引。
有些搜索是不能使用正常的索引的。例如需要在一篇大的文章中搜索一个词时,如: “WHERE post_content LIKE ‘%apple%’”,索引是没有意义的。这可能需要使用MySQL全文索引或是自己做一个索引(比如搜索关键词、标签)。
查询语句中使用use index可以指定索引。

5.使用 ENUM 代替 VARCHAR
ENUM 类型是非常快和紧凑的,实际上其保存的是 TINYINT,但其外表上显示为字符串。用这个字段类型来做选项列表变得相当的完美。比如“性别”,“国家”,“民族”,“状态”或“部门”字段,这些字段的取值是有限而且固定的,那么应该使用 ENUM 而不是 VARCHAR。

6.PROCEDURE ANALYSE() 结构建设
PROCEDURE ANALYSE() 会让 MySQL 帮助分析字段和其实际的数据,给出一些有用的建议。只有表中有实际的数据这些建议才会变得有用,因为要做一些大的决定是需要有数据作为基础的。
例如,如果你创建了一个 INT 字段作为你的主键,然而并没有太多的数据,那么,PROCEDURE ANALYSE()会建议你把这个字段的类型改成 MEDIUMINT 。或是你使用了一个 VARCHAR 字段,因为数据不多,你可能会得到一个让你把它改成 ENUM 的建议。但这些建议可能因为数据不够多而决策做得就不够准确。
在phpmyadmin里,你可以在查看表结构时,点击 “Propose table structure” 或“规则表结构”来查看这些建议,建议结果呈现在Optimal_fieldtype字段中。

7.字段尽量设置默认值
除非有特殊原因使用 NULL 值,否则应该总是让字段保持 NOT NULL。NULL需要额外的空间,并且在进行比较的时候,程序会更复杂,难以查询优化。
下面摘自MySQL自己的文档:
“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

MySQL 性能优化…经验分享
https://www.cnblogs.com/pengyunjing/p/6591660.html

mysql中不要用blob等大字段

在开发规范中,我们一般都是要求字段不要设置成blob这种大字段。原因是查询的时候效率问题。另外一个就是在插入量大的时候,生成的binlog太多,容易导致一些问题。线上就遇到了,1k多的插入,binlog狂写,磁盘干满了。

1.多表先约束再连接
由全表连接,变为先约束为小表再连接。

2.索引
随着集合的增长,需要针对查询条件中数据量大的排序字段做索引。
如果没有对索引的键排序,数据库需要将所有数据提取到内存并排序。因此在做无索引排序时,如果数据量过大以致无法在内存中进行排序,数据库将会报错。初步可以对出现查询慢的表建立索引、组合索引。
在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在索引组合的最左边。
mysql 组合索引“最左前缀”规则,简单的理解就是只从最左面的开始组合。查询条件中没有使用组合索引最左列列的语句,不会使用到索引。
MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。在以通配符%和_开头作查询时,MySQL不会使用索引。
虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
explain sql1 可以分析sql语句的执行情况,进而对sql语句进行优化。
组合索引-最左优先原则(最左原则)

MySQL有一个复合索引:INDEX(`a`, `b`, `c`),以下查询能用上索引。
A、select * from users where a = 1 and b = 2
B、select * from users where b = 2 and a = 1
C、select * from users where a = 2 and c = 1
第二项会由Mysql查询器优化。

https://blog.csdn.net/u014590757/article/details/79590561

3.数据库拆分:分库、分表、分区

单表的数据量限制,当单表数据量到一定条数之后数据库性能会显著下降。分库减少单台数据库的压力。有些数据表适合通过主键进行散列分库分表。

数据库的垂直切分和水平切分
数据切分可以是物理上的,对数据通过一系列的切分规则将数据分布到不同的DB服务器上,通过路由规则访问特定的数据库,降低单台机器的负载压力。

数据切分也可以是数据库内的,对数据通过一系列的切分规则,将数据分布到一个数据库的不同表中,比如将article表分为article_001,article_002等子表,若干个子表水平拼合有组成了逻辑上一个完整的article表。比如article表中有5000w条数据,在这个表中增加(insert)一条新的数据,insert后数据库会针对这张表重新建立索引,5000w行数据建立索引的系统开销还是不容忽视的。如果将这个表分成100 个子表,每个子表里边就只有50万行数据,向一张只有50w行数据的table中insert数据后建立索引的时间就会呈数量级的下降,提高了DB的并发量。分表的好处还有诸如写操作的锁操作等。

分库降低了单点机器的负载;分表提高了数据操作的效率,尤其是Write操作的效率。

垂直水平切分

分库分表后id主键处理的方法:
A、单库生成自增 id
B、设置数据库 sequence 或者表自增字段步长
C、UUID
D、最佳方法是snowflake 算法

snowflake 算法是 twitter 开源的分布式id生成算法,采用 Scala 语言实现,是把一个 64 位的 long 型的 id,1 个 bit 是不用的,用其中的 41 bit 作为毫秒数,用 10 bit 作为工作机器 id,12 bit 作为序列号。
1 bit 不用,是因为二进制里第一个 bit 为如果是 1,那么都是负数,但是我们生成的 id 都是正数,所以第一个 bit 统一都是 0。 41 bit:表示的是时间戳,单位是毫秒。41 bit 可以表示的数字多达 2^41 – 1,也就是可以标识 2^41 – 1 个毫秒值,换算成年就是表示69年的时间。 10 bit:记录工作机器 id,代表的是这个服务最多可以部署在 2^10台机器上哪,也就是1024台机器。但是 10 bit 里 5 个 bit 代表机房 id,5 个 bit 代表机器 id。意思就是最多代表 2^5个机房(32个机房),每个机房里可以代表 2^5 个机器(32台机器)。 12 bit:这个是用来记录同一个毫秒内产生的不同 id,12 bit 可以代表的最大正整数是 2^12 – 1 = 4096,也就是说可以用这个 12 bit 代表的数字来区分同一个毫秒内的 4096 个不同的 id。

4.读写分离
读写分离,基本的原理是让主数据库处理事务性增、改、删操作(INSERT、UPDATE、DELETE),从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

例如用户信息,每个用户都有系统内部的一个userid,与userid对应的还有用户名。那么如果分库分表的时候单纯通过userid进行散列分库,那么根据登录名来获取用户的信息,就无法知道该用户处于哪个数据库中。或许可以维护一个username—-userid的映射关系,先查询到userid再根据userid的分库分表规则到对应库的对应表来获取用户的记录信息。但这个映射关系的条数本身也是个瓶颈,原则上是没有减少单表内数据的条数,算是一个单点。并且也要维护这个映射关系和用户信息的一致性。最大一个原因,其实用户信息实际是一个读大于写的库,所以对用户信息拆分还是有一定局限性的。

对于这类读大于写并且数据量增加不明显的数据库,推荐采用读写分离+缓存的模式,用户注册、修改信息、记录登录时间、登录IP、修改密码,这些是写操作,但是这些操作次数都是很小的,所以整个数据库的写压力是很小的。读写分离首要解决的就是将经常变化的数据的拆分,比如:用户登录时间、记录用户登录IP,这类信息可以单独独立出来,记录在持久化类的缓存中(可靠性要求并不高,登陆时间、IP丢了就丢了,下次来了就又来了)。

主库负责写数据、读数据。读库仅负责读数据。每次有写库操作,同步更新cache,每次读取先读cache再读DB。写库就一个,读库可以有多个,例如Oracle采用dataguard来负责主库和多个读库的数据同步。

参考
https://www.cnblogs.com/panxuejun/p/5887118.html
https://www.jianshu.com/p/65e8a31fbab8
https://www.cnblogs.com/alvin_xp/p/4162249.html

MySQL数据库则可以利用MySQL主从配置,实现读写分离,既起到备份作用又可以减轻数据库的读写的压力。读写分离的基本原理是让“主”数据库处理事务性增、改、删操作,“从”数据库处理查询操作。利用数据库复制把事务性操作导致的变更从主数据库同步到从数据库。写库有一个,读库可以有多个,采用日志同步的方式实现主库和多个读库的数据同步。

Mysql 的 Replication 是一个异步的复制过程,从一个MySQL节点(称为Master)复制到另一个MySQL节点(称Slave)。在 Master 与 Slave 之间的实现整个复制过程主要由三个线程来完成,其中两个线程(SQL 线程和 I/O 线程)在 Slave 端,另外一个线程(I/O 线程)在 Master 端。
要实现 MySQL 的 Replication ,首先要打开 Master 端的 Binary Log,整个复制过程实际上就是 Slave 从 Master 端获取日志,然后在自己身上完全顺序的执行日志中所记录的各种操作。
MySQL的Replication原理有以下结论和推论:
* 每个从仅可以设置一个主。
* 主在执行sql之后,记录二进制log文件(bin-log)。
* 从连接主,并从主获取bin-log,存于本地relay-log,并从上次记住的位置起执行sql,一旦遇到错误则停止同步。
* 主从间的数据库不是实时同步。
* 如果主从的网络断开,从会在网络正常后,批量同步。
* 如果对从进行修改数据,那么很可能从在执行主的bin-log时出现错误而停止同步,这个是很危险的操作。所以一般情况下,非常小心的修改从上的数据。
* 一个衍生的配置是双主,互为主从配置,只要双方的修改不冲突,可以工作良好。
* 如果需要多主的话,可以用环形配置,这样任意一个节点的修改都可以同步到所有节点。

4.数据缓存
大型网站为了应对大量的并发访问,除了在业务端实现分布式负载均衡,还要减少数据库的连接,例如采用优秀的代码框架进行代码优化,采用优秀的数据缓存技术如memcached,如果资金丰厚还可以架设服务器群来分担主数据库的压力。

建表原则

1、定长与变长分离
如id int占4个字节,char(4)占4个字节,即每一个单元值占的字节都是固定的。而varchar,text,blob 变长字段适合单放另一张表,在其中使用主键与核心表关联。
2、常用字段和不常用字段向分离
结合具体的业务分析字段的查询场景,把查询频度低的字段单拆出来。
3、把1对多等需要关联统计的数值添加冗余字段。
例如论坛中每个栏目显示今日发帖数目存储下来,不需要每次查询都再统计。

发表评论