MySQL优化概述MySQL数据库常见的两个瓶颈是：CPU和I/O瓶颈。CPU饱和通常发生在数据加载到内存或从磁盘读取时。当加载的数据远大于内存容量时，就会出现磁盘I/O瓶颈。如果应用程序分布在网络上，当查询量很大时，网络就会出现瓶颈。我们可以使用mpstat、iostat、sar和vmstat来查看系统的性能状态。除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以通过工具来优化数据库的性能。MySQL优化方案Mysql优化大致可以分为三个部分：索引优化、SQL语句优化、表优化索引优化1.索引一般应用系统，读写比在10:1左右，插入操作相同作为一般的更新操作，很少会出现性能问题。在生产环境中，我们遇到的问题最多也最容易出问题，或者是一些复杂的查询操作。所以查询语句的优化是加快查询速度的重中之重***方法就是索引。索引：简单的说，相当于图书的目录，可以帮助用户快速找到自己需要的内容。在MySQL中也称为“key”，是存储引擎用来快速查找记录的一种数据结构。可以大大提高查询效率。尤其是当数据量非常大，查询涉及到多张表时，使用索引往往可以将查询速度提高上千倍。总结：建立索引的目的是为了提高查询效率，这和我们查询书籍的目录是一样的：先定位到章节，再定位到该章下的一个摘要，再找到页数。类似的例子还有：查字典、查地图等。2、索引类型普通索引是最基本的索引，没有任何限制。唯一索引和前面的普通索引类似，不同的是索引列的值必须是唯一的，但是允许空值。如果是复合索引，列值的组合必须是唯一的。复合索引是指在多个字段上创建的索引。只有在查询条件中使用了创建索引时的第一个字段，才会使用索引。主键索引是一种特殊的唯一索引。一张表只能有一个主键，不允许有空值。一般在建表的时候会同时创建主键索引。全文索引主要用于查找文中的关键词，而不是直接与索引中的值进行比较。全文索引与其他索引有很大不同。它更像是一个搜索引擎，而不是简单的where语句的参数匹配。全文索引与匹配操作一起使用，而不是一般的where语句加上like。可用于createtable、a??ltertable、createindex，但目前只有char、varchar、text列可以创建全文索引。值得一提的是，当数据量很大时，将数据放入没有全局索引的表中，然后使用CREATE索引创建全文索引，比先为表创建全文，然后写入数据导入速度更快。3.索引优化只要列中包含NULL值，本例中最好不要设置索引。如果复合索引中有NULL值，则该列在使用时不会使用该索引。尝试使用短索引。如果可能的话，应该制定一个Prefixlength对于where子句中经常使用的列，最好设置索引，这样会加快查找速度。对于多列的where或orderby子句，应该建立复合索引。对于like语句，使用%或'-'开头的索引将不会被使用，在%结尾的索引将被使用尽量不要对列进行操作（函数操作和表达式操作）尽量不要使用notinand<>操作的SQL慢查询优化1.如何捕获低效sql1)slow_query_log参数设置为ON，可以捕获执行时间超过一定值的SQL语句。2）ong_query_time当SQL语句执行时间超过这个值时，就会记录在日志中。建议将其设置为1或更短。3）slow_query_log_file记录日志的文件名。4）参数log_queries_not_using_indexes设置为ON，可以捕获所有不使用索引的SQL语句，虽然这个SQL语句可能执行的很快。2、慢查询优化的基本步骤1）先运行一下，看是否真的慢，注意设置SQL_NO_CACHE2）where条件单表查询，锁定最小返回记录表。这句话的意思是将查询语句的where应用到表中返回记录数最少的表上，开始查找。分别查询单表的每个字段，看哪个字段有区分度***3)explain查看执行计划，是否符合1的预期（从锁定记录少的表开始查询)4)orderbylimit形式的sql语句允许先查找已排序的表原则7)如果观察结果不符合预期，则从1继续分析。2.优化查询时，如果可以,尽量写完整的字段名。大多数情况下，连接效率远大于子查询。使用explain和profile来分析查询语句。查看慢查询日志，找出执行时间较长的SQL语句。优化多表连接时，尽量以小表驱动大表，即join小表，大表分页时使用limit。对于经常使用的查询，您可以启用缓存数据库。表优化表的字段尽量使用。固定长度为NOTNULL的表查询会更快。将数据库的大表按照时间或者一些标志划分成小表。拆分表格数据表格拆分：主要是垂直拆分和水平拆分。水平切分：Hash记录到不同的表中，每个表的结构完全一样，每次都从子表中查询，提高效率。垂直拆分：将一个表中的大字段拆分到另一个表中，形成一对一的关系。简而言之：Mysql的优化主要在于：索引的优化、SQL语句的优化、表的优化。并发优化技术。以上是MySQL的优化技术方案，下面是***阿里P8架构师的架构设计系列讲座。
