mysql之mysql优化

1、什么是Mysql？

    度娘：Mysql是一个关系型数据库管理系统，在web应用开发方面，Mysql是最好的关系数据库管理系统应用软件之一。所谓关系型数据库，就是将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。在Mysql中，所使用的SQL语言是用于访问数据库的最常用标准化语言。

2、既然Mysql这么牛，为什么要优化？

   影响Mysql性能的主要因素：

    1.数据库存储引擎

    2.数据库结构设计

    3.sql语句的书写

    4.CPU和内存

    5.在高并发情况下的查询

   以下针对于每个影响因素做分析和处理：

3.优化一、Mysql的存储过程

   3.1 什么是Mysql的存储过程

    在我们学习java基础的时候，会封装好一些工具类供我们在开发过程中调用，从而完成一些特定的工作。我们编写的这个工具类，在运行过程中，根据java中类的加载机制理解，我们编写好的工具类会被编译成一个对象存储在内存中。所以，很好理解了，Mysql的存储过程就是Mysql封装好了的一些特定SQL语句的工具类，供我们调用的。我们只需要指定存储过程的名字和传入正确的参数，它就可以帮我们完成一些工作。

   3.2 存储过程的优缺点

   优点(在理解过程中，可以和java中类的加载和销毁过程来进行理解)： 

    1、存储过程对象只在创建时进行编译，以后每次执行存储过程的时候，都不需要再次编译(这个跟java中类不一样)，而对于一般的SQL语句，每执行一次，就需要编译一次，所以使用存储过程可以提高数据库的执行速度。

    2、存储过程可以重复使用，这样就可以减少开发人员的工作量

    3、可减少java应用与Mysql数据库的IO交互(减少网络通信)。(这里有一个很好的例子)转：案例

   缺点(任何事物，有利就有弊)

    1、不可移植性(什么叫不可移植性)：数据库多种多样，然而每种数据库的编码方式又不尽相同，此时，如果你的项目中，出于性能和成本考虑，你会使用多种数据库，这个时候，就会出现一些数据不兼容的情况。

    2、当你使用存储过程之后，在你的系统中，有一部分业务逻辑不是你的java应用逻辑，采用这种架构，将会增添项目的维护和调试成本。

    3、对运行速度也有影响。很多高级数据库中都有statement(预编译对象)，cache(缓存)，所以编译一般的SQL语句没有什么影响，但是执行存储过程的话就要花费很多时间了，例如检查权限等操作。

     4、占用物理内存(在面试过程中，即使其他缺点回答不出来，但是这个回答出来也可以)。在调用存储过程中，会设计到连接数据库的操作，那么这些连接对象和存储过程对象都会占用内存。

 虽然存储过程的缺点多于优点，但是它还是很好用滴！！！

存储过程的使用、存储过程的循环、存储过程参数、存储过程异常处理以及游标介绍转：

4.优化二、触发器 

   4.1 什么是触发器

     触发器是与数据库中表有关的数据库对象(又是数据库的对象)，在满足自己定义的条件下触发，并执行触发器中自己定义的SQL语句。它的这种特性可以协助应用在数据库端确保数据的完整性和安全性。(举个简单例子，加入数据库中有两张表，一张表中写的是你的名字和性别，另外一张表中写的是你的名字、性别和年龄。现在我修改表1中的性别为女，那么表2就会根据触发器的特性，自动的完成表2中性别的修改，不可能两张表中的性别不一样吧！！)

   4.2 触发器的优点和缺点

   优点：很简单，就是在满足自定义条件下，自动的完成一些工作，从而来保证数据的完整性与安全性，从而来提高工作效率。

   缺点：1、如果这个触发器完成的是一个数据量较大的数据集的话，那么触发器的性能和效果会非常差。

              2、如果定义了多个触发器，在协同开发的时候(就是大家一起来写一个项目的时候)，会不清楚哪些功能是触发器完成的，又用到了哪些触发器，啊！问题太多。。就会导致阅读代码困难，加大维护成本。

              3、占用物理内存(又提到了占用物理内存了)

触发器也是缺点多于优点！！！

关于触发器的使用，请转：

5.优化三、索引

   5.1 什么是索引

      索引是对数据库表中一列或多列的值进行排序的一种数据结构，使用索引可以快速的找到数据库表的特定的信息(索引是一种通过一定的算法，将表中数据分类，从而提高查找效率的数据结构)。举个例子，对于一本书来说，书中提供的目录就相当于是数据库表中的索引，它可以快速的帮助我们对我们所需要的知识进行查找，提高效率。

   5.2 索引的优缺点

   优点：

        1、通过创建唯一索引，可以保证数据库表中每一行数据的唯一性(这个是根据索引的分类来的)

        2、可以大大加快数据的检索速度，这也是创建索引最主要的原因

        3、索引还可以帮助我们加快表与表之间的连接

   缺点：

        1、创建索引和维护索引要耗费时间，这种耗费时间的积累会根据数据量的增加而增加

        2、索引需要占据物理空间。索引主要是在创建表的时候，为每一个需要创建索引的列进行创建，从而来提高查询效率的，这样一来，我们存储的数据需要占据空间，索引页需要占据物理空间。

        3、当对数据库表中数据进行维护的时候，与之相关的索引也要进行动态得我维护，这样就会降低维护效率。

关于索引的使用，请转：

6.优化四、SQL语句优化

   我们在上边提到了索引这个概念，而对于sql语句优化来说，就是防止索引失效。

   常见的SQL语句优化方案：

   1)首先在创建表的时候，在更新较少，查询较多的列上创建合适的索引(常见的索引有主键索引、普通索引、唯一索引、组合索引和全文索引)

   主键索引：在数据库中，经常有一列或列组合，它的值是唯一的，这个就叫表的主键(通常情况下，就是Id)，我们在创建表的时候，只要定义了主键列，那么会自动的为这一列创建主键索引，不允许有空值。

   普通索引：最基本的索引，没有任何限制。

   唯一索引：唯一索引的列值必须唯一，允许为空值。

   全文索引：全文索引仅适用于MyIsam存储引擎的数据表，作用于char，varchar和text数据类型的列。

   组合索引：将几个列作为一条索引进行检索，适用最左匹配原则。

   2) 在编写SQL语句的时候，可以多用Explain去执行分析计划，进行SQL语句优化，避免索引失效。

   3) 常见的索引索引失效的：

           A、在类型为数字索引列做算数运算

           B、like前面有%(使用全文索引去解决)

           C、避免使用is  not  null判断

           D、or判断条件(假如or判断条件前后两个条件都有索引，那么就可以命中索引，只要其中一个条件没有索引，就会索引失效)

            E、避免使用select  * 查询语句(这样的话会发生全表扫描)

            F、exists 关键字优于 in

            G、避免在字符串或者日期列进行函数运算

7.优化五、Explain执行计划  

    Explain执行计划是在书写SQL语句的时候，对select查询语句。使用很简单，就是在select语句前加上explain关键字，对select语句进行分析，并输出select执行的详细信息，以供开发人员针对性优化。

    主要功能：通过计算分析系统中收集到的统计信息，为客户端的请求提供它认为最优的执行计划。

Explain执行计划具体的使用转：

8.优化六、读写分离+主从复制

  什么是读写分离？

  在Mysql数据库中，实现了一个读写分离的功能，就是让主库处理事务性查询(就是涉及到数据更新的查询)，而从库处理select查询。由于Mysql的IO是同步的，所以，如果系统过大，那么项目中的select会先去执行，其他的delete，update都会被select线程堵塞，只有当所有查询结束之后，才会去执行删除和修改操作。简单说，就是创建一个主库，多个从库主库主要完成写的功能，然后把数据同步到从库上去。

  主库如何将数据同步到从库上的？

  主库会将更新数据的操作记录保存，生成一个二进制文件(binary)，然后从库连接到主库以后，从库中有一个IO线程去读取这个二进制文件，缓存到本库的relay.log(中继日志)中，然后执行中继日志中的内容，也就是再执行一次SQL，这样就可以保证从库和主库的数据一致。

9.优化七、分库分表

   在我们以前的开发中，用户少，数据量也很小的情况下，我们使用单机数据库就够了。但是，随着用户数量的增加，为了提高Mysql数据库的并发量，我们就将数据库的读和写两个操作进行分离，使用多个从库来执行读的操作，使用主库来完成写的操作，从库与主库同步更新数据，保持数据一致，而从库可以水平扩展，例如它可以配置Myisam存储引擎(这种存储引擎它不支持事务，也不存锁什么的，对数据的查询效率很高)，这样就提高了数据库的读写能力。

单库太大：单个数据库的处理能力有限；单库所使用的服务器磁盘空间有限，单库对IO的操作也需要时间。

单表太大：会造成索引膨胀，查询超时，影响用户体验。

切分方式：垂直查分，水平拆分(由于水平拆分的方式使用较少，所以介绍垂直拆分)

垂直分表：基于列字段进行拆分，一般是表中的字段较多，不常用的数据，数据较大的，数据长度较长的数据。

垂直分库：根据一个系统中的不同业务进行拆分，例如对User一个库，Product一个库，Orderyige ku ,然后把它们放到不同的服务器上。