MySQL建库建表索引知识点汇总

mysql优化教程
MyISAM与InnoDB两个存储引擎的索引实现方式
一般尽量选择InnoDB
- 有事务的操作
- InnoDb是行锁，而MyISAM是表锁，索引对并发支持更好
- MyISAM将索引缓存到内存，而InnoDb将索引和数据都缓存的内存中
- MyISAM在数据损坏时，无法进行全部恢复
MyISAM的适用场景
- 非事务性的应用
- 只读的应用
- 空间类应用如gps数据，进行运算
- 适合大量的insert和select
InnoDB

事务的隔离性

未提交读 第一个连接在事务中进行写操作，另外的连接能够看见（早期的MySQL有这个问题）
已提交读 第一个连接在事务中进行写操作，其他的事务不可见（大部分数据库都支持）事务结束后，其他事务可见
可重复读 第一个事务只要是读的内容，如果其他事务对其进行更改，以后的查询还是之前的结果
可串行化 只要是读到的都加锁，很少用

隔离性从低到高，并发性由高到低。InnoDB的默认级别是可重复读，事务进行后就看不见别人的事务了。

大事务的优缺点

优点：

将大量的事务集中在一个事务可以大大减少时间(至少在sqlite中是这样)

缺点：

运行时间长，锁表造成大量阻塞和锁超时
若发生错误，回滚时间长
造成主从延迟

如何减少大事务

分成小事务
减少select，因为一般select占用时间长，且大多数情况下可以放到事务外头

建表相关

所有表的第一个字段为id，并且为主键，可选择无符号的
尽量选择is not null，原因如下
字符集使用utf8mb4
排序规则使用utf8mb4_general_ci
varchar记得填写默认值为’’，在navicat中选择EMPTY STRING
一般在表后面增添created_at和updated_at两个字段
记录时间尽量用datetime，而不是timestamp
- 因为timestamp最多到2038年
- datetime是部分时区的

选择对应的类型

类型选择排序
- 数字类型>日期或二进制>字符串
- 同级别类型中，尽量选择占用空间小的类型
整数类型
- 适用unsigned可以使范围扩大一倍
- tinyint 1字节 (unsigned 0-255)
- smallint 2字节 (unsigned 0-2^16)
- mediumint 3字节
- int 4字节
  - int(11) 这种后面跟着的数字一般不用管，在字段有FILLZERO属性的时候才会补0，例如1变成00000000001
- bigint 8字节
实数类型
- 一共有float、double、decimal，只有decimal是精确的
- float 4字节
- double 8字节
- decimal(18,9)需要9个字节
varchar
- varchar是可变长度并且以字符为单位，比如varchar存储10个utf-8字符，占用30字节的空间
- varchar在0-255的时候多占用一个字节来记录长度，如果长度大于255则需要两个字节来记录长度
- varchar适合很少被更新的列，因为会产生磁盘碎片
- 会删除末尾的空格
- 适合存储不定长度的类型
char
- 不变长，定义多长，数据就是多长
- 不会删除末尾的空格
- 最大宽度为255
- 适合存储类型长度近似的值
- 适合存储短字符串
- 适合经常更新的字符串
datetime 8字节
- mysql5.6之前可以支持到秒，之后支持到微秒
- 与时区无关
timestamp 4字节
- 1970年到2038年
- 一般用于update_time
date 3字节只存日期（生日）
time 时间，可以存到微秒

索引相关

如果经常查询A,B两个字段，那么可以考虑对A,B同时建立索引。
在where查询时，查询条件为A，或者A and B，该索引生效。
如果where查询条件为B,则索引失效。
区分度（姓名，ID等唯一性比较多）大的尽量放在前面。
如果分别对A，B，建立单独的索引，那么在where查询时，
如果是A and B,则只生效A索引。
如果是B and A,则只生效B索引。

索引优化策略

前缀索引的时候，尽量做到可以区分，最好每行的索引都不一样
使用联合索引
- 经常会被使用的列优先
- 选择性高的列优先，能分辨差异的越强
- 宽度越小的列优先，加快IO
覆盖索引
- 优化缓存，减少磁盘IO
- 随机IO变成顺序IO
- 避免主键索引的二次查询

使用索引的好处

大大减少存储引擎需要扫描的数据量
排序的时候避免创建临时表
将随机IO变成顺序IO

索引的缺点

增加写操作的成本
会增加查询优化器的选择时间

索引类型

Btree
- 最佳场景
  - 全值匹配索引
  - 精确匹配左前列并范围匹配另外一列
  - 匹配最左前缀 like ‘123%’
  - 只访问索引的查询
- 使用限制
  - 最左边不是索引，则无法使用索引
  - 使用索引时不能跳过索引中的列
  - not in 和 <> 无法使用索引
  - 如果有一列使用了范围查询，则右边的所有列都无法使用索引
Hash
- 场景
  - 精确全值匹配
- 使用限制
  - 要查两遍，先查hash表，再查行
  - 无法用于排序
  - 有可能产生碰撞