MySQL使用总结

建表

create table test(
        id int(11) NOT NULL AUTO_INCREMENT,
        a int(4) NOT NULL DEFAULT 0,
        b char(10) NOT NULL DEFAULT ‘’,
        c varchar(255) NOT NULL DEFAULT ‘’,
        create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
         update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
    ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

show create table test; 查看创建表DDL
    show engine; 查看mysql引擎

1.库名表名一律小写，见名知意，linux风格命令，比如a_b_c

2.引擎用InnoDB,高并发高可用互联网业务都是InnoDB,云服务商提供的云DB也仅仅支持InnoDB,MyISAM已被弃用

3.字符集用UTF-8，汉子3个字节，英文1个字节，通用无乱码风险，存储表情等特殊符号时需用utf8mb4,占4个字节

4.所有字段NOT NULL且必须有默认值，NULL用特殊字符保存，需要更多的存储空间(5.5后优化了)，查询时只能使用is[not] null,不能使用=/!=/in/not in，索引不存储NULL记录，导致NULL列索引统计更复杂，也更难优化

5.列类型使用顺序int > timestamp/year/date/time/datetime > enum/char > varchar > text/blob, int类型效率高，char涉及字符集，int指定长度类型，不要使用int(11)默认长度

6.ip用int存储,占4字节，电话号码可能出现+/-/()等字符,用varchar(20),可用like模糊查询

7.小数字段推荐使用 decimal 类型，float 和 double 精度不够，特别是涉及金钱的业务，必须使用 decimal

8.尽量避免据库中使用 text/blob 来存储大段文本、二进制数据、图片、文件等内容，而是将这些数据保存成本地磁盘文件或对象存储系统中，数据库中只保存其路径等索引信息

9.表都要有主键，推荐使用unsigned int,以便从库高效删除数据，主键也不该被修改，字符串不能做主键，如果不指定主键，innodb会使用唯一且非空值索引代替

10.存在自增列的表，自增列上必须存在一个单独的索引，若在复合索引中，自增列必须置于第一位

11.表不要有外键约束，提高插入并发效率，外键在大并发下容易产生死锁，应在业务代码中保证一致性

12.平衡范式与冗余，为提高效率可以牺牲范式设计，冗余数据; 大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据

13.避免使用触发器/视图/触发器/用户自定义函数，调试迁移拍错困难，扩展性差，数据库存储数据为主，降低业务逻辑和数据存储的耦合度，应由应用层业务程序取而代之

14.单表列不超过20，记录控制在千万级别，拒绝3B(big)，大sql，大事务，大批量

15.短期内业务达不到一个比较大的量级，建议不要使用分区表，分区表主要用作归档管理，多用于快递行业和电商行业订单表，莫迷信分区表提升性能，除非业务中 80% 以上的查询走分区字段

查询

1.不能select *,只获取必要字段,能有效利用索引覆盖，表结构变更时也对业务程序无影响，同理insert时也必须指定字段

2.禁止在where条件列使用函数或者表达式，以免索引失效，全表扫描

3.禁止负向查询以及%开头的模糊查询，导致不能命中索引，全表扫描

4.or改写成in或union,最新mysql已优化，in范围须少于50个

5.union all代理union,union有去重开销

6.少用join，大表禁止join，join列上要有索引，两张表join必须让小表做驱动表，务必使用同类型比较，避免全表扫描

7.LIMIT 分页优化，LIMIT 80000，10这种操作是取出80010条记录，再返回后10条，数据库压力很大，推荐先确认首记录的位置再分页，如SELECT * FROM test WHERE id >80000 LIMIT 10

8.应用程序捕获SQL异常，方便调试

9.explain;show slow log;查看执行计划和慢查询

10.insert … on duplicate key update 不存在更新，存在update

索引

1.合理使用索引，改善查询，但减慢更新，更新频率高的列不能建索引

2.唯一索引命名uniq_列名, 非唯一索引idx_列名

3.单表索引控制在5个以内，太多起不到过滤作用了，索引也占空间，索引多可能影响更新性能，索引列少更新，不在索引列做计算

4.varchar字段上建索引时，建议指定索引长度，不要直接将整个列建索引，一般 varchar 列比较长，指定一定长度作索引已经区分度够高，没必要整列建索引，整列建索引会显得比较重，增大了索引维护的代价，可以用 count(distinct left(列名, 索引长度))/count(*)来看索引区分度

5.选择业务中SQL过滤走的最多的并且cardinality值比较高的列建索引，业务SQL不走的列建索引是无意义的，字段的唯一性越高即代表 cardinality值越高，索引过滤效果也越好，一般索引列的 cardinality 记录数小于10%是一个低效索引，例如性别字段；

6.组合索引最左前缀原则，避免重复建索引，如果建立了(a,b,c)，相当于建立了(a), (a,b), (a,b,c),但不是值SQL语句的where顺序要和组合索引一致

7.InnoDB是BTree索引，一个表只能有一个聚簇索引，即索引和数据记录存储在一起(记录只有一份)，一般主键上是聚簇索引，非主键上是普通索引,叶子节点存储索引和主键(不是记录指针)，Hash索引一般用在内存数据库中

8.普通索引叶子节点存储主键，因此较长的列不能做主键，普通索引上查询先通过索引找到主键，然后在聚簇索引上通过主键找记录

9.建议使用趋势递增的key做主键，由于数据行与索引一体，这样不至于插入记录时，有大量索引分裂，行记录移动

10.B+Tree高度低，查询效率高，非叶子节点不存储key,叶子节点存储key和记录，根到所有叶子节点路径长度相同，叶子节点可存储多个记录，可利用局部性原理进行磁盘预读提高磁盘IO,叶子节点增加双向连接变成双向有序链表，便于范围查询/有序查询，非叶子节点一般被缓存，叶子节点包含record从磁盘读取

索引失效

- 隐式类型转换，如索引a的类型是varchar，SQL 语句写成 where a = 1 varchar 变成了int
- 对索引列进行数学计算和函数等操作，例如:使用函数对日期列进行格式化处理
- join 列字符集不统一
- 多列排序顺序不一致问题，如索引是 (a,b)，SQL 语句是 order by a b desc
- 模糊查询使用的时候对于字符型xxx%形式可以走到一些索引，其他情况都走不到索引；
- 使用了负方向查询（not，!=，not in 等）

锁&事物

1.业务代码中事务及时提交，避免产生没必要的锁等待

2.InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上，如果访问没有命中索引，无法使用行锁，将要退化为表锁，锁粒度较大，会影响并发

3.记录锁(Record Locks)锁定索引记录，阻止其他事物对该记录的修改；间隙锁(Gap Locks)锁定一个范围，防止范围内数据被其他事物更改；临建锁(Next-Key Locks)锁定索引记录及其之前紧邻的索引区间，防止幻读

4.并发控制用于保证数据的一致性，通常的方法有锁Locking和数据多版本Multi Vesion;共享锁（Share Locks，记为S锁）读取数据时加S锁，读读可并行，排他锁（eXclusive Locks，记为X锁），修改数据时加X锁，写读，写写不可以并行；数据Multi Version即CopyOnWrite,写时更新新版本，读时读取旧版本，实现读写并行

5.事物ACID,A(atomic)原子性要么不执行，要么全执行；C(consistence)事物前后数据处于一致状态；I(independent)事物之间不会有影响；D(durable)事物执行完成后对数据的改变不会因其他外部因素变化

6.redo日志用于保障已提交事物的ACID,redo log是磁盘顺序写，速度快，在定期将内存记录数据写磁盘

7.undo日志用于保障未提交事物ACID,undo log记录未提交事物时数据的旧版本，用于事物的回滚，事物提交后undo日志则可删除，存放undo log的地方叫回滚段

8.多版本并发控制MVCC(Multi Version Concurrency Control)就是通过“读取旧版本数据”也就是回滚段中数据来降低并发事务的锁冲突，提高任务的并发度

9.回滚段数据是历史数据快照，在写事物未提交前可进行无锁快照读(Snapshot Read),这种一致性不加锁的读（Consistent Nonlocking Read），就是InnoDB并发如此之高的核心原因之一，这里的一致性是指事务读取到的数据，要么是事务开始前就已经存在的数据（当然，是其他已提交事务产生的），要么是事务自身插入或者修改的数据

10.普通select都是快照读，除非显示加锁(select xxx for update/lock in share mode)

11.事物隔离性指多个并发事物之间互不影响，通过不同策略的锁实现的隔离级别不同；读未提交(Read Uncommitted)select不加锁，并发度高，隔离级别低；可重复读(Repeated Read, RR)是InnoDB默认隔离级别，普通select快照读不加锁，加锁select是行锁或表锁；读提交(Read Committed, RC)是互联网最常用的隔离级别，普通select也是快照读；串行化(Serializable)所有select语句都会被隐式的转化为select xxx  in share mode,如果有未提交的事务正在修改某些行，所有读取这些行的select都会被阻塞住,这是一致性最好的，但并发性最差的隔离级别

12.RR下事物里读取数据是第一次Read View，RC下每次读都会建立Read View