mysql 小记

innodb引擎下

数据类型

• DECIMAL 用于存储精确的浮点数据，但是通常会消耗更多的空间和计算开销。
• VARCHAR 存储可变长字符串，如果列长小于等于255字节，需要额外的1字节记录字符串长度，大于255字节，需要额外的2字节。varchar类型节省了空间。但是由于行是变长的，当update的时候可能使得行更长，页内没有更多的空间存储。对于MyISAM来说，会将行拆成不同的片段存储。InooDB需要分裂页。所以VARCHAR适用于：字符串列的最大长度比平均大的多，列的更新少
• DATETIME 精确到秒，与时区无关，8字节存储空间。
  TIMESTAMP 4字节存储空间，显示的值依赖于时区。默认情况下，更新记录时会记录当前时间，如果不想自动更新，需设置DEFAULT CURRENT_TIMESTAMP。

schema

• 范式和反范式：通常范式更新操作要快，占用内存较少，更好的放进内存；但是需要关联，代价昂贵，且索引策略可能失效。

锁

表锁

MDL锁(metadata lock) 是 server层的表级别锁，表的增删改查加的是读锁，表结构变更加的是写锁，读读之间不冲突，读写、写写之间冲突。如：当一个查询很耗时的时候，一个查询也是起一个事务的，这个读锁还未释放，此时去变更表结构如加索引等，都会引起锁冲突，此后所有关于这个表操作都会堵着。

行锁

innodb支持行锁。为了提高并发性的，读写、写写互斥，select默认不加锁，另外锁是加在索引上的。另外为了解决幻读，增加了间隙锁（可重复读级别下），与行锁配合使用。next-key lock

小技巧

• delete时，可加limit 减少锁范围
• count性能，count(*)~count(1)<count(字段)，前两者每次遍历一行，加1，不用取数据，count（id）需遍历取数据加1.
• alter table t engine=innoDB

索引

• 覆盖索引(Using index)

• 延迟关联（主要利用覆盖索引）（如：前：select * from t_portal_user where create_time > '2012-10:10' and create_time<'2017:10:10' LIMIT 5000,10;延迟关联：SELECT * from t_portal_user INNER JOIN (select id from t_portal_user where create_time > '2012-10:10' and create_time<'2017:10:10' LIMIT 5000,10) as a USING(id)）

• 回表

• 索引下推（如索引ca，查询时where c like 'lk%' and a=10, 联合索引ca都可以用到，直接在索引里过滤两个条件，再回表过滤）

• 最左前缀

• 索引合并（index merge）（k1，k2；where k1=1 or k2=2）

• 索引去重（如联合主键a、b，普通索引c，联合索引ca，联合索引cb，那么索引c和索引ca是一样的，都有cab元素，并且是去重的，不是caab等）

• 表达式无法使用索引

• 同等条件下，联合索引尽量将选择性高的列放到前面

• order by排序时，索引顺序列要和order by字句顺序一样；如果关联多个表，则order by字句全部是第一个表的字段时，才能使用索引排序 如（索引k(a,b,c), where a=1 order by b,c 可以用到k，但是where a=> order by b,c就不能了）

explain 执行计划

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

id

包含一组数字，表示查询中执行select子句或操作表的顺序

• 原则上从大到小，从上至下，即相同先上后下，不同，先大后小

select_type

查询中每个select子句的类型（简单OR复杂）

• SIMPLE：查询中不包含子查询或者UNION
• 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
• 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
• 在FROM列表中包含的子查询被标记为：DERIVED（衍生）用来表示包含在from子句中的子查询的select，mysql会递归执行并将结果放到一个临时表中。服务器内部称为"派生表"，因为该临时表是从子查询中派生出来的
• 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
• 从UNION表获取结果的SELECT被标记为：UNION RESULT

注意：

• SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE。
• DEPENDENT意味着select依赖于外层查询中发现的数据。
• UNCACHEABLE意味着select中的某些 特性阻止结果被缓存于一个item_cache中。

type

ALL, index, range, ref, eq_ref, const, system, NULL
从左至右，由差至好

• ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
• index：Full Index Scan，index与ALL区别为index类型只遍历索引树
• range:索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。
• ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行
• eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件。
• const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。system是const类型的特例，当查询的表只有一行的情况下，使用system
• NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引， 例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）。

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

Extra

• Using index（覆盖索引）
• Using where
  表示mysql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where字句的查询都会显示"Using where"。
• Using temporary 表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
• Using filesort MySQL中无法利用索引完成的排序操作称为“文件排序”
• Using join buffer该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。
• Impossible where这个值强调了where语句会导致没有符合条件的行。

工具

• percona toolkit 工具箱