mysql 优化笔记

1.引擎选择

特性	InnoDB	MyISAM	MEMORY
事物安全	支持	不支持	不支持
对外建的支持	支持	不支持	不支持
存储限制	64TB	有	有
空间使用	高	低	低
内存使用	高	低	高
插入数据的速度	低	高	高

从mysql5.5.5开始，InnoDB是mysql表的默认引擎，因为目前使用关系型数据库的一般都需要考虑事务安全，因此InnoDB是最常用的。

其次是MyISAM，如果表的读操作远远多于写操作时，并且不需要事务的支持的。可以将MyIASM作为数据库引擎的首先。

MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。

2.字段类型

• 尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快；
• 尽量使用简单的数据类型，如用MySQL内建的类型(date, time, datetime)来存储时间和日期，使用整型存储IP地址；
• 尽量避免NULL，最好指定列为NOT NULL。

char & varchar

在定义字符串类型字段时，字段长度应按实际情况进行定义，尽量避免定义char(255)这样的，显然会造成空间浪费。
如果列中需要存储的数据长度差不多一致，应该有限考虑使用char，否则使用varchar;
如果列长度较大(>50byte)，考虑使用varchar，否则使用char。

MEDIUMINT & INT & BIGINT

与字符串类型同理，选择时应实际考虑字段可能存储的大小。如果MEDIUMINT（24位）足够使用，则不需要定义为BIGINT。

FLOAT & DOUBLE & DECIMAL

float类型使用4个字节存储，double使用8个字节存储，decimal占用的字节数是变长的，一般对精度要求较高的使用decimal存储，比如财务数据，但是当数据量较大时，考虑使用BIGINT代替decimal，将要存储的货币单位根据小数的位数乘以相应的倍数即可。在执行效率方面，float类型最快，decimal最慢。

3.索引

尽量避免全表查询，在where 及 order by 、join涉及的列上建立索引。

例如以下Sql:

SELECT * FROM `mytable` t WHERE t.device_pid = 'AAQ119A0120'

表mytable数据量为60万行，未在device_pid列上建立索引情况下，该sql查询耗时3.532s，以供查询9991条结果：

在device_pid列上建立索引后，该sql查询耗时为1.158s

3.1 选择索引字段

• 选择频繁用于查询的字段建立索引
  选择常出现在where、order by 、join中的字段，避免选择仅仅会出现在select中的字段做索引；
• 选择高维度的数据列建立索引
  如年龄和性别，优先选择年龄做索引。因为性别只有“男女”，命中索引也会返回一半的行，年龄的维度很高，命中后返回的行较少；
• 选择短小的值建立索引
  短小的值可以有效减小索引体积，减少I/O读取耗时；
  当遇到一定要给长字段（例如：BLOB,TEXT,或者很长的VARCHAR）建立索引的情况下，应该建立前缀索引。
• 合理建立联合索引
  将多个字段组合成联合索引，由于要满足多个字段的行很少所以能过滤掉大量数据，因此索引的列越多可过滤掉的数据越多，但同时过大的索引又会大大增加insert/update操作的性能。综合考虑建议如下：单表尽可能不要超过一个联合索引，单个联合索引不超过3个字段，合理的联合索引（索引覆盖：索引拥有的关键字内容，覆盖了查询所需要的全部数据，此时，就不需要在数据区获取数据，仅仅在索引区即可）可以对查询性能产生较大的提升。

3.2 选择索引类型

• Normal 普通索引
  普通索引，大多数情况下都可以使用；
• Unique 唯一索引
  唯一的，不允许重复的索引，例如身份证号码字段可以用；
• Full Text 全文索引
  全文收索，在检索长文本的时候，效果最好。
• SPATIAL 空间索引
  只能在存储引擎为MYISAM的表中创建，很少用到。

3.3 选择索引方法

索引方法分为hash和Btree。普遍选择Btree，hash索引不支持范围查询，仅仅支持“=”、“<=>”查询，同时hash索引也不支持排序操作，数据库无法利用索引的数据来避免任何排序运算。

注意：不要建立过多的索引！每一个多出的索引都要占据额外的磁盘空间，而且都会影响写入操作的性能；

4. SQL优化

4.1 EXPLAIN

Sql编写完成后，可以使用EXPLAIN关键字来查看SQL执行计划：

以下为计划中比较关键的几点：

• type字段
  该字段反映当前查询的链接类型，性能排名为：system > const > eq_ref > ref > range > index > ALL，以下排名有好到差。

  system
  const
  eq_ref
  ref
  fulltext
  ref_or_null
  index_merge
  unique_subquery
  index_subquery
  range
  index
  all

• keys 字段
  表示当前查询使用的索引，如果计划表明当前Sql不会使用索引，那十分有必要进行优化！！！

• rows 字段
  显示mysql 执行查询的行数，数值越大越不好，说明没有用好索引。

4.2. 仅输出需要字段

应尽量避免使用select * ，需要哪些字段写哪些字段。这也是良好的SQL习惯。

4.3. 使用连接(JOIN)来代替子查询

如以下Sql ：

SELECT d.`code`  FROM factory_device d WHERE d.`deviceCategoryId` 
IN (SELECT g.`id` FROM base_devicecategory g WHERE g`code` = 'J' )

查询结果：

修改为JOIN查询：

SELECT  d.`code` FROM factory_device d  JOIN base_devicecategory g 
on d.`deviceCategoryId` = g.id WHERE g.`code` = 'I'

查询结果：

4.4. 用IN来替换OR

以下Sql:

SELECT d.`code` FROM factory_device d WHERE d.`code` = '510S_0104B' 
OR d.`code` = '510F_0101b' OR d.`code` = '510P_0104A'

用IN来替换OR，后修改为：

SELECT d.`code` FROM factory_device d WHERE d.`code`
IN ('510S_0104B' ,'510F_0101b' , '510P_0104A')

4.6. 分组统计可以禁止排序

默认情况下，MySQL对所有GROUP BY col1，col2…的字段进行排序，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。

5. 避免索引失效

在下列情况下会导致索引失效

• or语句前后没有同时使用索引；
  WHERE name = ’1’ OR code = ‘1’ 如果name和code都有索引，则索引有效，否则无效。
• 联合索引，不是使用第一列索引，索引失效
  有联合索引index（name,code,age），当查询条件为以下时，索引生效：

WHERE name = ’1’ AND code = ’1’ AND age = 12 

索引失效查询条件为：

WHERE code = ’1’ AND age = 12 

即联合索引需要满足最左原则时才会生效，上述例子中的联合索引在下列查询条件下生效：

name = ’1’ AND code = ’1’ AND age = 12
name = ’1’ AND code = ’1’ 
name = ’1’ 

• 数据类型出现隐式转化
  如：如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
• 在索引列上使用 IS NULL 或 IS NOT NULL操作
  这种情况下不是一定会照成索引失效的，具体情况需要用EXPLAIN查看计划才知道。
• 在索引列上使用不等（not，<>，!=）操作
  不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。
• 使用LIKE '%%'
  LIKE ‘%name%’ 查询时会导致列上的索引失效，能避免就改为LIKE ‘name%’，此时索引生效。
• 在where中对索引字段使用函数
  Mysql不支持函数索引，如下sql（查询2016年后的数据），因使用YEAR()函数，d字段有索引的情况下，也不会走索引查询

SELECT * FROM t WHERE YEAR(d) >= 2016;

// 优化后走索引
SELECT * FROM t WHERE d >= ‘2016-01-01’;