龙空技术网

mysql数据库调优总结(一)

阿东Luke 800

前言:

如今姐妹们对“cmysqlblob”都比较珍视,姐妹们都需要了解一些“cmysqlblob”的相关文章。那么小编也在网络上收集了一些对于“cmysqlblob””的相关内容,希望你们能喜欢,各位老铁们一起来了解一下吧!

关于mysql调优以前也做过一些总结,但是总是不全面,不能合理的调优;网上也有很多关于mysql调优的文章,今天作为回顾再次整理一下。我使用的是mysql5.7版本。闲话少说今天我们从一下几个方面来进行总结:

第一、性能监控,必须要有监控,没有监控是无法进行有效的调优的;

1、通过使用show profile查询工具,用来分析当前会话中sql语句执行时资源消耗情况,可以通过type指定;默认关闭状态,并保存最近15次的运行结果。可以通过

`show variables like 'profiling'`来查看状态,通过`set profiling = 1;` 命令来开启,如下图:

up-4e1b57b99b4b9ec7ee1673b8dcf92124c4e.png

up-913c79afe650044a417b5af0d1c760d3101.png

show profile工具,可以通过type指定(具体参数请看官网参数 [SHOW PROFILE Statement]( "SHOW PROFILE Statement"))

以后的版本中将会移除show profiles

2、使用performance schema来更加容易的监控mysql,监控会消耗一些系统资源

这些表主要记录运行时元数据信息(一共87张表),不会进行持久化,存在内存中。

performance_schema中的事件与写入二进制日志中的事件(描述数据修改的events)、事件计划调度程序(这是一种存储程序)的事件

不同。 performance_schema中的事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况。

3、使用show processlist查看连接的线程个数,来观察是否有大量线程处于不正常的状态或者其他不正常的特征

id表示session id

user表示操作用户

host表示操作主机

db表示操作数据库

command 表示当前状态:sleep线程正在等待客户端发送新的请求、query线程正在执行查询或正在将结果发送给客户端、locked在mysql的服务层,该线程正在等待表锁、analyzing and statistic线程正在收集存储引擎的统计信息,并生成查询的执行计划、copying to tmp table线程正在执行查询;并且将结果都复制到一个临时表中、sorting result线程正在对结果进行排序、sending data线程可能在多个状态之间传送数据,或者向客户端返回数据。

info表示sql详情

time表示命令执行的时间

state表示命令执行的状态

第二、schema与数据库类型的优化;

1、数据类型的优化:**更小的通常更好**,应该尽量使用可以正确存储数据的最小数据类型,更小的数据类型通常更快,因为它们占用更少的磁盘、内 存和CPU缓存,并且处理时需要的CPU周期更少,但是要确保没有低估需要存储的值的范围,如果无法确认哪个数据类型,就选择你认为不会超过范围 的最小类型;**简单就好**,简单数据类型的操作通常需要更少的CPU周期,例如:整型比字符操作代价更低,因为字符集和校对规则是字符比较比整 型比较更复杂;使用mysql自建类型而不是字符串来存储日期和时间;用整型存储IP地址。案例:创建两张相同的表,改变日期的数据类型,查看SQL 语句执行的速度。**尽量避免null**,如果查询中包含可为NULL的列,对mysql来说很难优化,因为可为null的列使得索引、索引统计和值比较都更加 复杂,坦白来说,通常情况下null的列改为not null带来的性能提升比较小,所有没有必要将所有的表的schema进行修改,但是应该尽量避免设计成可 为null的列。**具体数据类型**,整数类型:可以使用的几种整数类型:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT分别使用8,16,24, 32,64位存储空间。尽量使用满足需求的最小数据类型。字符和字符串类型:char长度固定,即每条数据占用等长字节空间;最大长度是255个字 符,适合用在身份证号、手机号等定长字符串;varchar可变程度,可以设置最大长度;最大空间是65535个字节,适合用在长度可变的属性;text不 设置长度,当不知道属性的最大长度时,适合用text;按照查询速度:char>varchar>text。如下图:

up-6e3d846fb019002006045bc8b450fac2668.png

BLOB和TEXT类型:MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。两者都是为了存储很大数据而设计的字符串类型,分别采用二进制和字 符方式存储。日期时间类型:datetime和timestamp,如下图:

使用枚举代替字符串类型:有时可以使用枚举类代替常用的字符串类型,mysql存储枚举类型会非常紧凑,会根据列表值的数据压缩到一个或两个字节 中,mysql在内部会将每个值在列表中的位置保存为整数,并且在表的.frm文件中保存“数字-字符串”映射关系的查找表。

IP地址使用整型存储:select INET_ATON('IP')转换为整型,select INET_NTOA('整型数')转换为ip。

2、合理使用范式、反范式

3、主键的选择:代理主键(推荐使用)与业务无关无意义的数字序列,不与业务耦合,更容易维护、自然主键,业务中唯一自然标识。

4、字符集选择:如果我们可以确定不需要存放多种语言,就没必要非得使用UTF8或者其他UNICODE字符类型,这回造成大量的存储空间浪费。MySQL 的数据类型可以精确到字段,所以当我们需要大型数据库中存放多字节数据的时候,可以通过对不同表不同字段使用不同的数据类型来较大程度减小数 据存储量,进而降低 IO 操作次数并提高缓存命中率。

5、存储引擎的选择:innodb、myisam

6、适当的数据冗余:被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段。这样的场景由于每次Join仅仅只是为了取得某个小 字段的值,Join到的记录又大,会造成大量不必要的 IO,完全可以通过空间换取时间的方式来优化。不过,冗余的同时需要确保数据的一致性不会遭 到破坏,确保更新的同时冗余字段也被更新。

7、适当拆分:当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候,如果我们大部分访问这张表的时候都不需要这个字段,我 们就该义无反顾的将其拆分到另外的独立表中,以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以 大大增加,既减少物理 IO 次数,也能大大提高内存中的缓存命中率。

第三、执行计划;

为了知道优化SQL语句的执行,需要查看SQL语句的具体执行过程,以加快SQL语句的执行效率。可以使用explain+SQL语句来模拟优化器执行SQL查询 语句,从而知道mysql是如何处理sql语句的。[官网地址]( "官网地址")

第四、通过索引进行优化。

1、索引基本知识:

索引的优点:大大的减少了服务器需要扫描的数据量;帮助服务器避免排序和临时表;将随机io变成顺序io

索引的用处:快速查询匹配WHERE子句的行;从consideration中消除行,如果可以在多个索引之间进行选择,mysql通常会使用找到最少行的索引;

如果表具有多列索引,则优化器可以使用索引的任何最左前缀来查找行;当有表连接的时候,从其他表检索行数据;查找特定索引列的min或max 值;如果排序或分组时在可用索引的最左前缀上完成的,则对表进行排序和分组;在某些情况下,可以优化查询以检索值而无需查询数据行

索引的分类:主键索引、唯一索引、普通索引、全文索引、组合索引

技术名词:回表、覆盖索引、最左匹配、索引下推

索引采用的数据结构:哈希表、B+Tree

索引匹配方式:请看下图:

2、哈希索引:基于哈希表的实现,只有精确匹配索引所有列的查询才有效;在mysql中,只有memory的存储引擎显式支持哈希索引;哈希索引自身只需 存储对应的hash值,所以索引的结构十分紧凑,这让哈希索引查找的速度非常快;

3、组合索引:当包含多个列作为索引,需要注意的是正确的顺序依赖于该索引的查询,同时需要考虑如何更好的满足排序和分组的需要

4、聚簇索引与非聚簇索引:聚簇索引,不是单独的索引类型,而是一种数据存储方式,指的是数据行跟相邻的键值紧凑的存储在一起。非聚簇索引:数 据文件和索引文件分开存放

5、覆盖索引:基本介绍,如果一个索引包含所有需要查询的字段的值,我们称之为覆盖索引;不是所有类型的索引都可以称为覆盖索引,覆盖索引必须 要存储索引列的值;不同的存储实现覆盖索引的方式不同,不是所有的引擎都支持覆盖索引,memory不支持覆盖索引。优势:索引条目通常远小于 数据行大小,如果只需要读取索引,那么mysql就会极大的较少数据访问量;因为索引是按照列值顺序存储的,所以对于IO密集型的范围查询会比随 机从磁盘读取每一行数据的IO要少的多;一些存储引擎如MYISAM在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系 统调用,这可能会导致严重的性能问题;由于INNODB的聚簇索引,覆盖索引对INNODB表特别有用。

6、优化小细节:当使用索引列进行查询的时候尽量不要使用表达式,把计算放到业务层而不是数据库层;尽量使用主键查询,而不是其他索引,因此主 键查询不会触发回表查询;使用前缀索引;使用索引扫描排序;union all,in,or都能够使用索引,但是推荐使用in;范围列可以用到索引,范围条件是: <、<=、>、>=、between,范围列可以用到索引,但是范围列后面的列无法用到索引,索引最多用于一个范围列;强制类型转换会全表扫描`explain select * from user where phone=13800001234;`不会触发索引;`explain select * from user where phone='13800001234';`触发索引;更新十分频 繁,数据区分度不高的字段上不宜建立索引(更新会变更B+树,更新频繁的字段建议索引会大大降低数据库性能,类似于性别这类区分不大的属性, 建立索引是没有意义的,不能有效的过滤数据,一般区分度在80%以上的时候就可以建立索引,区分度可以使用 count(distinct(列名))/count(*) 来计 算);创建索引的列,不允许为null,可能会得到不符合预期的结果;当需要进行表连接的时候,最好不要超过三张表,因为需要join的字段,数据类 型必须一致;能使用limit的时候尽量使用limit;单表索引建议控制在5个以内;单索引字段数不允许超过5个(组合索引);创建索引的时候应该避免 以下错误概念(索引越多越好、过早优化,在不了解系统的情况下进行优化)。

7、索引监控:`show status like 'Handler_read%';`

up-457216bfc969264e9965deda21982e8d587.png

**五、六、七、八章后续抽时间补充,作为第二节更新出来**

标签: #cmysqlblob #mysql添加枚举类型