MySql优化建议总结

MySQL逻辑架构：

有三层结构：

第一层：客户端通过连接服务，将要执行的sql指令传输过来

第二层 （分为两种方式）

方式一： 服务器解析并优化sql，生成最终的执行计划并执行

方式二： 服务器从缓存中获取查询结果

第三层：存储引擎，负责数据的储存和提取

优化建议总结：

• 对于查询缓存

  查询缓存的空间不要设置的太大

  原因： 因为查询缓存是靠一个全局锁操作保护的，如果查询缓存配置的内存比较大且里面存放了大量的查询结果，当查询缓存失效的时候，会长时间的持有这个全局锁。因为查询缓存的命中检测操作以及缓存失效检测也都依赖这个全局锁，所以可能会导致系统僵死的情况

  对于写密集型的应用，直接禁用查询缓存

  原因：如果一个表被更改了，那么使用那个表的所查询缓存将不再有效，并且从缓冲区中移出。频繁移除缓存会消耗大量时间

  mysql缓存相关知识如 缓存配置， 使用监控，优化等有兴趣可自己查一下，包括很多内容

• 对于数据类型

  尽量使用对应的数据类型。比如，不要用字符串类型保存时间，用整型保存IP。

  选择更小的数据类型。能用TinyInt不用Int。

• 对于索引

  索引的列如果是表达式的一部分或者是函数的参数，则失效。

  针对特别长的字符串，可以使用前缀索引，根据索引的选择性选择合适的前缀长度。

  索引应该建在小字段上，对于大的文本字段甚至超长字段，最好不要建索引，实在要建的话可以用前缀索引

  使用多列索引的时候，可以通过 AND 和 OR 语法连接。

  重复索引没必要，如（A，B）和（A）重复。

  索引在where条件查询和group by语法查询的时候特别有效。

  将范围查询放在条件查询的最后，防止范围查询导致的右边索引失效的问题。

  索引最好不要选择过长的字符串，而且索引列也不宜为null。

  索引的字段必须是经常作为查询条件的字段;

  经常出现在Where子句中的字段，特别是大表的字段，应该建立索引

  如果索引多个字段，第一个字段要是经常作为查询条件的。如果只有第二个字段作为查询条件，这个索引不会起到作用;

  不要过度索引

  不要以为 索引 “ 越多越好 ” ，什么东西都用索引是错的。每个额外的 索引都要占用额外的磁盘空间，并降低写操作的性能。如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是，则就不要费力去增加这个索引了，因为已经有了。

  对于值唯一不重复的列要添加唯一索引，可以更快速的通过该索引来确定某条记录。唯一索引是最有效的

  频繁进行数据操作的表，不要建立太多的索引；

  复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

  1、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

  2、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

  3、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

  4、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

  5、如果索引多个字段，第一个字段要是经常作为查询条件的。如果只有第二个字段作为查询条件，这个索引不会起到作用;

  索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，
  一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

  对于具体索引有效性检查， 可以用explain 查看sql语句执行计划

• 对于具体查询

  避免查询无关的列，如使用Select * 返回所有的列。

  从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的
  服务器的话，这还会增加网络传输的负载。所以，你应该养成一个需要什么就取什么的好的习惯。

  避免查询无关的行

  切分查询。将一个对服务器压力较大的任务，分解到一个较长的时间中，并分多次执行。如要删除一万条数据，可以分10次执行，每次执行完成后暂停一段时间，再继续执行。过程中可以释放服务器资源给其他任务。

  分解关联查询。将多表关联查询的一次查询，分解成对单表的多次查询。可以减少锁竞争，查询本身的查询效率也比较高。因为MySql的连接和断开都是轻量级的操作，不会由于查询拆分为多次，造成效率问题。

  注意count的操作只能统计不为null的列，所以统计总的行数使用count（*）。

  group by 按照标识列分组效率高，分组结果不宜出行分组列之外的列。

  关联查询延迟关联，可以根据查询条件先缩小各自要查询的范围，再关联。

  Limit分页优化。可以根据索引覆盖扫描，再根据索引列关联自身查询其他列。

  原理：
  利用表的覆盖索引来加速分页查询。
  索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。
  SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

  Union查询默认去重，如果不是业务必须，建议使用效率更高的Union All

  只查询一条数据时，加 limit 1

  当你查询表的有些时候，你已经知道结果只会有一条结果,在这种情况下，加上 LIMIT 1
  可以增加性能。这样MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查找下一条符合记录的数据。

  千万不要 ORDER BY RAND()

  想打乱返回的数据行？随机挑一个数据？这样使用只让你的数据库的性能呈指数级的下降。这里的问题是：MySQL会不得 不去执行RAND()函数（很耗CPU时间），而且这是为了每一行记录去记行，然后再对其排序。就算是你用了Limit 1也无济于事（因为要排序）

  我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。

  就算是你 users 表有一个主键叫 “email”的字段，你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外，在你的程序中使用表的ID来构造你的数据结构。

  选择正确的存储引擎

  在 MySQL 中常用的有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。
  MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都 无法操作直到读操作完成。
  InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。InnoDB的性能更加平衡。
  老版本中mysql默认搜索引擎是MyISAM， 现在已经都改为innodb。

  使用INNER JOIN 而不是WHERE来创建连接(多表查询)

  这个笛卡尔连接问题， 若量表各有100数据， where 产生 100 * 100 = 10000 条数据，再根据where条件过滤
  而 INNER JOIN 只产生 100 条结果

• mysql插入

  一条SQL语句插入多条数据

  INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);

  合并后日志量（MySQL的binlog和innodb的事务日志）减少了，降低日志刷盘的数据量和频率，从而提高效率，同时也能减少SQL语句解析的次数，减少网络传输的IO。

  数据有序插入

  数据有序的插入是指插入记录在主键上是有序排列，dateid：

  INSERT INTO `insert_table` (`dateid`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0);INSERT INTO `insert_table` (`dateid`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1);INSERT INTO `insert_table` (`dateid`, `uid`, `content`, `type`) VALUES ('2', 'userid_2', 'content_2',2);

  由于数据库插入时，需要维护索引数据，无序的记录会增大维护索引的成本。我们可以参照innodb使用的B+tree索引，如果每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小；如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作。 建表时最好以自增id为主键就解决了这个问题。

• 主从同步，读写分离

  MySQL主从同步的作用：

  1、可以作为一种备份机制，相当于热备份（在从备份，避免备份期间影响主服务器服务）

  2、可以用来做读写分离，均衡数据库负载(主写从读)

  3、当主服务器出现问题时，可以切换到从服务器。

• 分库分表

  对表进行水平划分

  如果一个表的记录数太多了，比如上千万条，而且需要经常检索，那么我们就有必要化整为零了。如果我拆成100个表，那么每个表只有10万条记录。当然这 需要数据在逻辑上可以划分。一个好的划分依据，有利于程序的简单实现，也可以充分利用水平分表的优势。比如系统界面上只提供按月查询的功能，那么把表按月 拆分成12个，每个查询只查询一个表就够了。

  对表进行垂直划分

  有些表记录数并不多，可能也就2、3万条，但是字段却很长，表占用空间很大，检索表时需要执行大量I/O，严重降低了性能。这个时候需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。（降低了每张表的文件大小） 实例：文章信息表就可以拆分成两张表，一张记录文章信息如作者、栏目、发表日期、关键词等，另一张表记录 文章内容，摘要等。利用id进行对应。因为在大多数情况下只需要搜索第一张表即可，只有在文章展示页才需要访问两张表，如此可以提高搜索性能!

  水平分库分表

  水平分库分表与上面讲到的水平分表的思想相同，唯一不同的就是将这些拆分出来的表保存在不同的数据中。

  某种意义上来讲，有些系统中使用的“冷热数据分离”（将一些使用较少的历史数据迁移到其他的数据库中。而在业务功能上，通常默认只提供热点数据的查询），也是类似的实践。在高并发和海量数据的场景下，分库分表能够有效缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源的瓶颈。

  垂直分库

  垂直分库基本的思路就是按照业务模块来划分出不同的数据库，而不是像早期一样将所有的数据表都放到同一个数据库中。