MySQL优化

MySQL优化

概述

系统的吞吐量瓶颈往往出现在数据库的访问速度上，随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢，数据是存放在磁盘上的，读写速度无法和内存相比。

数据库层面

MySQL的逻辑架构

最上层的服务并不是Mysql独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构

第二层架构中，大多数的MYSQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（日期时间等），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等

第三层包含了存储引擎。存储引擎负责Mysql中的数据的存储和提取。Mysql支持各种不同的存储引擎，每个存储引擎都有它的优势和劣势。服务器通过API和存储引擎进行通信。这些API接口屏蔽了不同存储引擎之间的差异，使的这些差异对上层的查询过程透明。存储引擎不会解析SQL不同的存储引擎之间也不能互相通信，而只是简单的响应上层服务器的请求

存储引擎

建议使用Innodb 存储引擎，在5.5后已经是MySQL默认引擎。

查看表当前使用的存储引擎，show table status like ‘表名’

读写分离

**主从复制:**主写从读

MyCat搭建读写分离服务：

分表

水平拆分：水平分表就是根据一列或多列数据的值把数据行放到多个独立的表里，这里不具备业务意义。用于解决解决表行数过大问题

垂直拆分：垂直分表就是把一个数据量很大的表,可以按某个字段的属性或使用频繁程度分类,拆分为多个表。解决列过长问题。

表设计

数据类选择的原则

• 更小的通常更好：通常情况下，应该选择可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更小的磁盘、内存和CPU缓存。但是要确保没有低估需要存储值得范围，在MYSQL中的多个地方增加数据类型范围是一个非常耗时和痛苦的操作。

• **简单就好：**简单数据类型的操作通常需要更少的CPU消耗。比如，整型比字符操作代价更低。举个例子：
  1、应该选择MySQL内建的类型（date、time等）存储时间，而不是字符串。
  2、应该用整型存储IP地址。推荐博文：http://www.cnblogs.com/skynet/archive/2011/01/09/1931044.html

• **尽量避免NULL：**通常情况下最好执行列为NOT NULL，除非真的需要存储NULL值。
  如果查询总包含可能为NULL的列，对于MySQL来说更难优化，因为可为NULL的列使的索引、索引统计等都变的更加复杂。通常把字段设置为NOT NULL带来的性能提升比较小，但是如果计划在列上创建索引，就应该尽量避免设计成可为NULL的列。

数据类型选择详解

• 尽量选择非定长的数据类型，如varchar，text，decimal

一张表的字段不要过多。

MySQL优化

MySQL查询的过程

1、客户端发送一条查询给服务器

2、服务器先检查查询缓存，如果命中缓存，则立即返回存储在缓存中的结果。否则进入下一阶段结构优化

3、服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划

4、MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询

5、将结果返回给客户端，同时缓存查询结果

索引优化

索引类型：唯一索引，前缀索引，组合索引，普通索引，主键索引

什么时候该加索引：where字句中的列，频繁作为查询字段的列，表连接关联的列，排序用到的列，索引的基数越大，索引的效率就越高，什么叫基数越大，比如手机号，每个列都具有不同的值，非常好区别，这个就适合建立索引，而性别这样的字段，因为只有两个值，以不适合建立索引，就是区分度高低的问题

什么时候不该加索引：表的数据量很小，该字段的数据频繁需要被修改。

硬件

更大的内存，减少磁盘IO

其他

超 100 万行的批量写 (UPDATE,DELETE,INSERT) 操作,要分批多次进行操作

大批量操作可能会造成严重的主从延迟

主从环境中,大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间， 而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况

binlog 日志为 row 格式时会产生大量的日志

大批量写操作会产生大量日志，特别是对于 row 格式二进制数据而言，由于在 row 格式中会记录每一行数据的修改，我们一次修改的数据越多，产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因

避免产生大事务操作

大批量修改数据，一定是在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对 MySQL 的性能产生非常大的影响。

特别是长时间的阻塞会占满所有数据库的可用连接，这会使生产环境中的其他应用无法连接到数据库，因此一定要注意大批量写操作要进行分批