MYSQL性能优化总结（1）

SQL优化

一、查询sql执行效率

我们可以通过show status了解当前数据库应用是以插入更新为主还是以查询操作为主以及各种类型的sql执行比例是多少。

例如：

mysql> show status like 'Com_%';

+-----------------------------+-------+

| Variable_name               | Value |

+-----------------------------+-------+

| Com_admin_commands          | 0     |

| Com_assign_to_keycache      | 0     |

| Com_alter_db                | 0     |

| Com_alter_db_upgrade        | 0     |

| Com_alter_event             | 0     |

| Com_alter_function          | 0     |

二、定位执行效率较低的语句

可以通过以下语句分析某条sql的执行效率

mysql> explain select * from class  where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | class | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

select_type：表示select的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个select）等。

table：输出结果集的表。

type：表示MySQL在表中找到所需行的方式，或者叫访问类型。

常见类型，从左至右，性能由最差到最好：

ALL < index < range < ref < eq_ref < const,system < NULL

(1) type=ALL，全表扫描，MySQL遍历全表来找到匹配的行。

(2) type=index，索引全扫描，MySQL遍历整个索引来查询匹配的行。

(3) type=range，索引范围扫描，常见于<,<=,>,>=,between等操作符。

(4) type=ref，使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行。

(5) type=eq_ref，类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中有一条记录匹配，简单来说，就是多表连接中使用primary key或者unique index作为关联条件。

(6) type=const/system，单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当做常量来处理，例如，根据主键primary key或者唯一索引unique index记性的查询。

(7) type=NULL，MySQL不用访问表或者索引，直接就能够得到结果。

三、mysql设计数据库时的一些原则

(1) 核心原则：不在数据库中做运算、cpu计算务必移交业务层、控制列数（字段少而精，建议在20个字段以内）、平衡范式与冗余（往往牺牲范式）、拒绝3b（拒绝大sql语句：big sql、拒绝大事物：big transaction、拒绝大批量：bigbatch）。

(2) 字段相关：用好数值类型（用合适的字段类型节省空间）、定义合适的字段长度、避免使用null字段、少用text类型。

(3) 索引相关：合理使用索引(改善查询,减慢更新,索引一定不是越多越好)、字符字段必须建前缀索引、不在索引做列运算、尽量不用外键(由程序保证约束)。

(4) 考虑表中数据存储量，若数据存储量过大，则要考虑分表分库操作。

四、sql语句优化

(1) 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

(2) 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

(3) 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

(4) 尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

(5) 尽量别面前置％

(6) 应尽量避免在 where 子句中对字段进行表达式操作

(7) 应尽量避免在where子句中对字段进行函数操作

(8) 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

(9) 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致。

(10) 很多时候用 exists 代替 in 是一个好的选择

(11) 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

(12) 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

(13) 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

(14)任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。