高性能MySQL 个人笔记

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

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

## InnoDB
支持表锁和行锁;  
四个隔离级别(未提交读（READ UNCOMMITTED），提交读（READ COMMITTED），可重复读(REPEATABLE READ)(默认)，可串行（SERIALIZABLE），关于锁和事务有更详细的文档：[美团技术博客-Innodb中的事务隔离级别和锁的关系](https://tech.meituan.com/innodb-lock.html));  
聚簇索引:数据的存储方式，即索引和数据是一起存放的，读取到索引的同时，数据也能一并读出
二级索引(非主键索引)必须包含主键索引;
    
    
# 索引
## 索引类型
>         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.事务开启时，插入的数据不会被其他事务读到，事务级别至少是提交读
20.查询数据表大小
SELECT (SUM(DATA_LENGTH)+SUM(INDEX_LENGTH))/1048576 FROM information_schema.tables WHERE TABLE_SCHEMA='database_name' AND TABLE_NAME='TABLE_NAME';