MySQL存储引擎 MyISAM 和 InnoDB 的区别

• MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持.
• InnoDB不支持FULLTEXT类型的索引.
• InnoDB中不保存表的具体行数,但是MyISAM只要简单的读出保存好的行数即可.
• MyISAM支持表锁，只有读读之间是并发的，写写之间和读写之间（读和插入之间是可以并发的，去设置concurrent_insert参数，定期执行表优化操作，更新操作就没有办法了）是串行的，所以写起来慢；InnoDB支持行锁，这个一般指的是sql用到索引的时候，行锁是加在索引上的，不是加在数据记录上的，如果sql没有用到索引，仍然会锁定表，普通的select是不需要锁的
• MyISAM索引btree上的节点是一个指向数据物理位置的指针，所以查找起来很快；nnodb索引节点存的则是数据的主键，所以需要根据主键二次查找
• 因为在使用索引的时候用的是行锁，锁的粒度小，竞争相同锁的情况就少，就增加了并发处理，所以并发读写的效率还是很优秀的，问题在于索引查询后的根据主键的二次查找导致效率低

数据库设计范式

第一范式:每个属性都不可再分解
第二范式:每个属性都依赖与主键
第三范式:除了主键外，其他属性都没有依赖关系

MYSQL 性能优化

• 选择合适的存储引擎,设计良好的数据库结构，选择合适的表字段数据类型,允许部分数据冗余（空间换时间）
• 适当的添加索引
• 优化 sql 语句，不同的语句，根据你选择的引擎、表中数据的分布情况、索引情况、数据库优化策略、查询中的锁策略等因素，最终查询的效率相差很大；优化要从整体去考虑，有时你优化一条语句后，其它查询反而效率被降低了，所以要取一个平衡点
• Cache（缓存数据）
  查询：查询之前，要在Memcached或Redis中查找结果，如果找到，则返回它；如果未找到，则到数据库服务器上执行查询，并将结果返回给Memcached或Redis
  插入：先把数据插入数据库，在内存中受此影响的数据库将变成无效
• 读写分离
• 分布数据（分割数据）找规律分表,减少单表中的数据量提高查询速度

关于缓存

• 一般的首页不应当有查询，对首页生成静态页面
• 不经常改动的页面,生成静态页面.

切分

• 垂直切分保证业务的独立性，防止不同业务争抢资源，毕竟业务是有优先级的
• 切分后也可对不同片数据进行不同优化。如按时间切分，超过一定时间数据不允许修改，就可以引入压缩了，数据传输及读取减少很多
• 数据是否存在明显的冷热（考虑旧数据归档）

MySQL数据库基本的三个优化法则

• 系统服务优化，把MySQL的key_buffer、cache_buffer、query_cache等参数提高
  （sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size ）
• 优化SQL语句，减少Ditinct、Group、Join等等语句的操作
• 给所有经常查询的字段增加适当的索引

mysql要注意的地方及技巧

• 需要utf8mb4才能显示emoji
• join查询不一定优于子查询
• 选取最适用的字段属性,尽可能减少定义字段长度,尽量把字段设置NOT NULL
• GUID替代AutoIncrement作为主键的唯一性保障
• 把update和访问频繁的数据cache在内存中
• 使用队列，控制消费速度
• 只存储英文字母时，该字段可不需要采用utf8, 可选择字符长度更小的字符集
• 使用mysql自带的query cache，对应用基本完全透明。但会受限于本机。而且只缓存查询结果，mc和redis可以缓存一些加工后的数据

不好操作

• 主主复制（主键值分配、数据冲突）
• 水平分表（如果将来一定会到这个规模，又可能用到，建议越早做越好）

关于主从复制、读写分离

• 增加了数据的冗余备份，提高了可用性
• 扩展读
• 从库可配置myisam引擎及其他针对性设置，提升查询性能以及节约系统开销
• 主库向从库发送binlog是异步的，从库恢复数据也是异步的
• 当节点比较多时，使用多级缓存，减轻主的压力
• 读产生了延迟，牺牲了一致性

关于数据库外键及其他高级特性(触发器、存储过程等)

• 增加数据库负担(外键作用：约束数据库的一致性)
• 用户量大，并发度高时数据库很容易成为性能瓶颈
• 数据库不容易水平伸缩，应用服务器能轻松地水平伸缩
• 将这些特性分离到程序中作为数据逻辑
• 不经常改的常数,可使用外键,来记录数据关系.而那些经常更改的,使用更高层的逻辑来记录数据关系.

mysql vs oracle

• Oracle目前还是最先进的关系数据库，其传统使用方法：存储使用EMC阵列（容量大，数据安全），IBM服务器，即IOE组合，这三个组合很强大（高可用，高性能），但是也高价格
• MySQL的优势:
  • 省去了巨额license费用
  • MySQL代码开源，可以根据业务特点定制和优化
  • 将MySQL运行在普通PC上，硬件费用大大降低

mysql中int、bigint、smallint 和 tinyint的区别与长度的含义

• bigint
  从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字）。存储大小为 8 个字节。
  P.S. bigint已经有长度了，在mysql建表中的length，只是用于显示的位数

• int
  从 -2^31 (-2,147,483,648) 到 2^31 – 1 (2,147,483,647) 的整型数据（所有数字）。存储大小为 4 个字节。int 的 SQL-92 同义字为 integer。

• smallint
  从 -2^15 (-32,768) 到 2^15 – 1 (32,767) 的整型数据。存储大小为 2 个字节。

• tinyint
  从 0 到 255 的整型数据。存储大小为 1 字节。
  int(M) 在 integer 数据类型中，M 表示最大显示宽度。在 int(M) 中，M 的值跟 int(M) 所占多少存储空间并无任何关系。和数字位数也无关系 int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。
  TinyINT(M) always has a range from -128..+127 signed or 0..255 unsigned. M is the display width.

Importance of varchar length in MySQL table

There’s one possible performance impact: in MySQL, temporary tables and MEMORY tables store a VARCHAR column as a fixed-length column, padded out to its maximum length. If you design VARCHAR columns much larger than the greatest size you need, you will consume more memory than you have to. This affects cache efficiency, sorting speed, etc.

This is the var (variable) in varchar: you only store what you enter (and an extra 2 bytes to store length upto 65535)

To be clear: Storing a string 100 characters in a varchar(200) field will take 101 bytes. Storing a string of 100 characters in a varchar(256) field will take 102 bytes. This is why you see varchar(255) so frequently; 255 characters is the longest string you can store in MySQL’s varchar type with only one byte of overhead. Anything larger requires two bytes of overhead.

varchar(N) N is the number of characters, so varchar(255) charset utf8mb4 would actually use up to 1021 bytes

Specifically, when doing sorting, larger column do take up more space, so if that hurts performance, then you need to worry about it and make them smaller. But if you only ever select 1 row from that table, then you can just make them all 255 and it won’t matter.

看看这个

选择合适的 MySQL 日期时间类型来存储你的时间

范围

timestamp（1970-2038）
datetime （1000-9999）
int 是从1970年开始累加的，但是 int 支持的范围是 1901-12-13 到 2038-01-19 03:14:07，如果需要更大的范围需要设置为 bigInt。但是这个时间不包含毫秒，如果需要毫秒，还需要定义为浮点数。
datetime 和 timestamp 原生自带6位的微秒。

用处

timestamp 是自带时区转换的,可记录经常变化的更新/创建/发布/日志时间/购买时间/登录时间/注册时间等，并且是近来的时间，够用，时区自动处理，比如说做海外购或者业务可能拓展到海外
datetime 记录固定时间如服务器执行计划任务时间/健身锻炼计划时间等，在任何时区都是需要一个固定的时间要做某个事情。超出 timestamp 的时间，如果需要时区必须记得时区处理

sql

状态值：tinyint；
有限范围值：根据范围选择tinyint(不要用enum)，smallint，mediumint ,int, bigint, float, double；
从技术上尽可能将字段设置成NOT NULL，但如果业务上要求该字段可以为NULL，而且如果该字段不会被建立索引，则保持NULL，否则可以用’’(空字符串)代替；

全文索引

mysql5.6.4支持innodb的全文搜索