MySQL 开发实践

在开发过程中，数据库往往是很重要的一部分，数据库的优化以及开发过程中遇到的问题往往困扰者开发者。

下面我们来看看平时经常会遇到的一些mysql的问题吧：

1：如果遇到mysql负载高时，有什么办法：

      mysql负载高往往是一些sql语句耗时多产生的，怎么去找到这些sql语句呢？

      这个时候慢查询日志分析就排上用场了

使用MySQLdumpslow进行慢查询日志分析

MySQLdumpslow -s t -t 10 ***.txt

该语句的意思是输出耗时最多的TOP10条语句

-s：排序方法（后面跟的参数：t表示按时间，c表示按次数，r表示按返回记录数）

-t：TOP条数（后面跟的参数表示数量）

通过慢查询日志分析，就可以找到最耗时的sql

慢查询相关的配置参数

log_slow_queries：是否打开慢查询日志，得先确保=ON后面才有得分析

long_query_time：查询时间大于多少秒的SQL被当做是慢查询，一般设为1S

log_queries_not_using_indexes：是否将没有使用索引的记录写入慢查询日志

slow_query_log_file：慢查询日志存放路径

2：如何进行sql优化：

使用Explain分析sql语句执行计划

使用Profiles分析sql语句消耗的时间和资源

（具体的方法这里就不详细介绍了，以后说到sql语句分析的时候再仔细说这两个方法吧）

sql优化常用的一些技巧：

      2.1：使用索引，避免全表扫描

      注意：索引字段上加函数会导致索引失效

+----------+------------+---------------------------------------+
| Query_ID | Duration   | Query                                 |
+----------+------------+---------------------------------------+
|        1 | 0.00019800 | select * from mytable where id=10    |
|        2 | 0.28652100 | select * from mytable where id+10=20  |
+----------+------------+---------------------------------------+

      2.2：select尽量选出需要的字段，尽量不要*

+----------+------------+-----------------------------------------------------+
| Query_ID | Duration   | Query                                               |
+----------+------------+-----------------------------------------------------+
|        1 | 0.02635800 | select count(1) from ( select id from mytable ) a   |
|        2 | 1.48625900 | select count(1) from ( select * from mytable ) a    |
+----------+------------+-----------------------------------------------------+

      2.3：尽早过滤，让Join或者Union等后续操作的数据量尽量小

      2.4：逻辑层进行一些数据排序，时间函数计算等

      sql函数的优化有很多，这里简单说几种

3：如果sql优化已经差不多的，但是数据库的请求量还是很大，并且还在增加，如何解决呢？

      3.1：分库分表

      3.2：使用集群，读写分离

      3.3：增加业务的cache层

      3.4：使用连接池

4：mysql如何做主从数据同步？

      复制机制：

      主服务器通过复制机制，将主服务器的写操作通过binlog传到从服务器中生成中继日志（relaylog），从服务器再将中继日志redo，使得主库和从库的数据保持同步

5：mysql有哪些存储引擎？

      在平时中最常见的的存储引擎有MyISAM和InnoDB，我们下面来说说这2中引擎的区别吧：

      InnoDB（mysql5.5之后的默认引擎）

            1.支持行锁：并发性能好

            2.支持事务：所以InnoDB称为事务性存储引擎，支持ACID，提供了具有提交，回滚和崩溃恢复能力的事务安全

            3.支持外键：当前唯一支持外键的以你去哪个

      MyISAM（mysql5.5之前的默认引擎）

            1.支持表锁：插入、查询速度快，更新、删除速度慢

6：mysql内部结构有哪些层次？

      6.1.Connectors：连接器，接收不同语言的Client交互

      6.2.Management Serveices & Utilities：系统管理和控制工具

      6.3.Connection Pool: 连接池。管理用户连接

      6.4.SQL Interface: SQL接口。接受用户的SQL命令，并且返回用户需要查询的结果

      6.5.Parser: 解析器。验证和解析SQL语句成内部数据结构

      6.6.Optimizer: 查询优化器。为查询语句选择合适的执行路径

      6.7.Cache和Buffer：查询缓存。缓存查询的结果，有命中即可直接返回

      6.8.Engine：存储引擎。MySQL数据最后组织并存储成具体文件