如何进行MySQL数据库优化？

如何进行MySQL数据库优化？

  答：数据库优化标准有3点：研发高效率、访问高速度、服务高可用。基于以上3点可以在以下方面进行优化：

  恰当的数据类型：

　　状态类字段可存放在tinyint，但需注意数据类型范围有符号-128~127，无符号0~255。

　　主键或整型可存放在int或bigint，但注意有符号的int设置为10位也不能超过21亿（有符号）或者42亿（无符号）。

　　字符串可存放在char或varchar，但需注意char只有255位（中文占3个字节）、char是定长、varchar是变长、char检索时速度更优。

  恰当的存储引擎：

　　MySQL常用存储引擎InnoDB、MyISAM、Memory。需ACID事务和行级锁支持选择使用InnoDB，不需要ACID事务和查询频繁场景使用MyISAM。

　　InnoDB也可以配置nnodb_flush_log_at_trx_commit=2，每次事务提交都刷系统缓冲区，但是每隔1s批量刷盘。提高写的性能。

  恰当的索引：

　　在查询条件、排序、范围检索、联表字段上添加Btree索引（二叉树的改进，利用局部性预读4kb原理，降低树高度、增加了存储数据量）。

　　控制没必要的索引，例如：大量重复值的字段上，不适合建索引。索引会占用物理空间，且更新数据会重建索引，更新速度受到一定的影响。

　　命中索引：避免条件出现数据类型强制转换和函数计算、组合索引遵循最左侧原则、Like避免前导模糊查询、反向条件是不能命中索引的。

  恰当的服务设计：

　　百万级数据单库结构+数据备份即可。

　　千万级数据需一主多从设计，使用binglog进行数据同步。同时需要容灾备份数据。

　　亿级数据需一主多从+分片架构，分片架构即指根据业务分库（微服务）、大表分表。分表分纵向分和横向分，纵向分是把多字段表分成多个表，纵向分是根据范围或者哈希取摸分为多个表。

  恰当的缓存设计：

　　目的是减少对数据库的查询，对于高频查询且低频修改的数据，使用redis进行缓存。这样既能提升查询效率，也能降低数据库的查询压力。

  恰当的开发技巧：

　　MySQL调优的关键词explain，可以分析sql语句是否命中索引（keys），检索的行数（rows），执行的描述情况（Extra）等。以此可以判断是否要修改语句、增加索引等。

　　避免死锁：以相同的顺序访问表和行、大事务拆小、注意避免where条件的强制类型转换和函数计算。

　　预防离线更新丢失：事务内调用外部服务会出现离线更新丢失的问题。

　　预防不一致读：读取数据后数据发生更新就会出现不一致读，因此需要最终更新时对比版本。

　　预防本地数据更新丢失：事务内的异常不结束事务，会出现重启一个新的链接继续执行，最终只提交后半部分数据。嵌套事务会在第二个事务启动前，自动提交第一个事务，如果后一个事务回滚，那么会导致只提交了前半部分数据。

　　批数据处理：大批量查询会更新数据时，把数据分批处理。例如：1000条一批进行查询，可把1000次sql执行降低到一次。