mysql

发表于 更新于 分类于 ，

写作目的

最近读了《高性能MySQL_第3版（中文）》决定写一篇专门的博客来记录所学所得,以及记录自己平时的积累经验，并传播给大家。

存储引擎

MyISAM

单独存放行数信息(count(*)不带筛选条件时不用读表);
支持表锁,不支持行锁;
不支持事务;
对于blob以及text长字段也可以基于前500个字符加索引;
可以设置延迟更新索引;
仅有此类型支持地理空间搜索

InnoDB

支持表锁和行锁;
四个隔离级别(未提交读（READ UNCOMMITTED），提交读（READ COMMITTED），可重复读(REPEATABLE READ)(默认)，可串行（SERIALIZABLE），关于锁和事务有更详细的文档：美团技术博客-Innodb中的事务隔离级别和锁的关系);
聚簇索引,二级索引(非主键索引)必须包含主键索引;

索引

索引类型

1
2
3
4
5
6
7
8
9
10
11
12
13
    B-Tree索引
    (树形结构,只有两层节点,子节点之间有指向后面节点的指针):
    按照左前缀原则,从最左列开始查找;
    不能跳过前面的索引,即联合索引只能从第一列开始索引;
    如果从某列是按照范围查询,则后面的列都无法使用索引查找，例如between
哈希索引:
    只有精确匹配索引搜有的列的查询才有效(因为是计算hash值);
    不存储字段值;
    哈希索引不是按照索引值排序的,所以无法用于排序;
    不支持部分索引;
    只支持等值索引,即=,!=,IN(),也不支持范围查询比如>;
    哈希非常快,除非很多冲突;
    选择性很低的列建立哈希索引,冲突很多,性能影响极大

索引策略

  1. 独立的列,不能是表达式的一部分,也不能是函数,如column+3=4;
  2. 前缀索引,当索引的列很长时可以模拟hash索引,或者选取部分字符作为索引,减少索引空间,而且尽量选择重复性低的字符,对于blob text 或者很长的varchar必须使用前缀索引,缺点是无法使用前缀索引order by 和group by,也无法使用前缀索引做覆盖扫描
  3. 聚簇索引,不是一种索引类型,而是一种数据存储方式,叶子页包含了行的全部数据,但是节点页只包含了索引列,访问数据更快,使用覆盖索引时可以直接使用页节点的主键值,更新索引的代价很高,插入新行或者更新导致移动行时导致”页分裂”,页分裂将导致占用更多的磁盘空间,聚簇索引可能导致全表扫描变慢,尤其当行比较稀疏或者由于页分裂导致数据存储不连续时,二级索引的叶子节点包含引用行的主键列,且二级索引访问需要两次索引查找,因为存放的是”行指针”
  4. 覆盖索引,如果一个索引包含（覆盖）所有需要查询的字段的值,这个索引被称为覆盖索引

mysql schema和数据类型优化

  1. 数据类型越小越好(载入内存以及存储空间),不要用NULL,如果为NULL,mysql自身优化会很麻烦,带来不必要的开销
  2. varchar和char,varchar更新时容易产生分裂页的问题,varchar(10)实际需要11的存储空间(1字节用于存储长度,<=255用1个字节),varchar适用于:字符串最大长度比平均长度大很多;更新少;使用UTF-8类似的复杂字符集,每个字符使用不同的字节数存储；
  3. blob和text,当值太大时InnoDB会使用存储指针的方式存储,blob是二进制数据没有排序规则和字符集,text有字符集和排序规则,排序时只对max_sort_length做排序(或者使用ORDER BY SUBSTRING(column,length))
  4. 使用枚举代替字符串,枚举实际存储的是整数
  5. 时间类型 DATETIME-存储的是YYYYMMDDHHMMSS的整数,TIMESTAMP-存储的是从1970年1月1日午夜来的秒数(只用4个字节)
  6. BIT 类型是字符类型 ‘0’=48 实际是ASII码值;SET类型无法通过索引查找,存储整数,按位炒作
  7. 特殊类型 低于秒精度的时间错使用bigint,IPv4用无符号整数存储 INET_ATON()和INET_NTOA()函数做转换
  8. double 数据类型 格式 DOUBLE(M,D)M的含义是整个数的长度，数字长度，D是精度，几位小数

其他积累经验

  1. 转换成字符串时,空格会被忽略,而java中不会忽略,例如在数据库中是 “ abc”,在select where 条件中筛选 “abc” 会被筛出来,而在Java中再去匹配则会产生不相等问题
  2. where 条件中not in范围筛选可用关联替换表,然后关联字段 is null 用于等效筛选
  3. 时间格式化函数 DATE_FORMAT(createtime,”%Y%m%d %H:%i:%s”)
  4. GROUP_CONTACT()聚合函数,分组之后连接字符串
  5. 设置字符区分大小写: 字符集中_ci后缀–不区分大小写(character ignore),_bin后缀–二进制比对,_cs后缀–区分大小写(character sense)
  6. 查询后插入 insert into table1(item1,item2,…) select item1,item2,… from table2
  7. ORDER BY FIELD(column, 2, 0) DESC,ctime DESC
  8. FIND_IN_SET(column,’value1’)
  9. 表关联顺序由优化器决定,并不是指定的顺序,将外链接转化成内连接
  10. 没必要返回所有的字段
  11. IN() MySQL会事先做排序,然后二分法查找,当条目很多时用IN()
  12. mysql 索引原理:使用B+树 MyISAM 引擎使用过节点存储地址信息;InnoDB引擎使用节点储存数据,叶节点包含了完整的数据记录,因为InnoDB的数据文件本身要按主键聚集,辅助索引data域存储相应记录主键的值而不是地址,用非单调的字段作为主键在InnoDB中不是个好主意
  13. 优化sql:最前缀原则-联合索引(a,b,c)先匹配a,再匹配b,再匹配c,但是order by和group by时失效(需要进行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%’)
  14. 尽量的扩展索引,不要新建索引,因为索引建立太多会影响插入和删除的操作
  15. 查询where条件数据类型不匹配也无法使用索引
  16. 对于like查询,”%”不要放在前面
  17. 字符串与数字比较索引使用不到
  18. last_insert_id()与每个连接相关，由MySQL server来维护的，由每条连接维护独立的值，某条连接调用last_insert_id()获取到的值是这条连接最近一次insert操作执行后的自增值，该值不会被其它连接的sql语句所影响。
    19.事务开启时，插入的数据不会被其他事务读到，事务级别至少是提交读