我的 MySQL开发实践经验

表结构设计

1.主键

在实际项目中，主键id推荐使用数据库自增ID（类型为bigint）和雪花算法生成的随机ID。

业务量小，采用自增ID；业务量大，推荐采用雪花算法。

使用自增id的缺点：

1、自增id如果暴露，容易被人发现规律

2、对于高并发的情况下，innodb引擎在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点。

3、单表数据量达到一定程度后要分库分表，导致ID重复，解决起来比较麻烦

2.外键

不要使用外键与其它表进行关联，避免高并发场景的性能问题

1、外键是极影响并发性能的，因为当存在外键约束的时候，MySQL会进行即时检查，每次insert和update都要要去扫描此记录是否满足

2、耦合度高，后期很难进行分库分表

3.合适的字段类型和长度

数据库的资源是很宝贵的，合适的字段类型和长度，不但节约数据库表空间和节约索引存储空间，更重要的是提升检索速度

1、对于固定长度的坚决使用char/tiyint等类型

2、对于不固定长度但是确定了总长度的使用varchar类型

3、不要用varchar/char 存储长字符串,直接用text。并且让长字符串拆分到另一个表，保持主表尽量瘦小

4.字段冗余

允许适当冗余其他表的字段，以提高查询性能，但必须考虑数据一致而且不要冗余过长的字段

5.字段默认值

避免将字段默认值设为null

对MySQL来说，会使得索引、索引统计和值的比较都更加复杂
NULL会参与字段比较，所以对效率有一部分影响，比如!=, <>等

索引设计

1.覆盖索引

对于count和group场景，请使用覆盖索引，提高查询性能。

索引就像是一本书的目录，如果查询的内容都只和目录上的内容有关，那mysql只要扫描索引结构就能得到查询结果，比如我给student表增加了个索引：

ALTER TABLE `student` ADD INDEX index_name (name,gender);

用到索引覆盖的sql语句：

select name,gender from student;
select name,gender from student where name='不高兴就喝水' and gender=1;
select name,gender from student group by name,gender;
select name,gender,count(1) from student group by name,gender;
select name from student group by name;
select name ,count(1) from student group by name;

2.复合索引

设计索引的时候尽量使用复合索引，并将区分度高的字段放在前面

那么什么是区分度高的字段呢？

执行如下语句，假设查询结果为 0.9,0.1,1000，可以看到name列的选择性最高，因此将其作为联合索引的第一列，即建立(name, gender)的联合索引

select count(distinct name) / count(*), count(distinct gender) / count(*), count(*) from student

根据索引最左匹配原则，能够触发这个联合索引的sql语句是：

select name,gender from student where name="不高兴就喝水" and gender=1
select name,gender from student where gender=1 and name="不高兴就喝水" ;
select name,gender from student where name="不高兴就喝水";
select name,gender from student where age=18 name='不高兴就喝水';

3.索引失效

以下几个操作会引起索引失效：

1.在索引列上做计算、函数、转换类型等操作

2.违反最左匹配原则

3.like以通配符开头（例如：‘%喝水’）

4.防止隐式转换，比如：索引的字段为字符串类型，查询的时候不加单引号（ name为vachar类型，查询的时候 where name = 1）

5.or连接，等等…

4.唯一索引

对于需要保证表中唯一的字段，即使在应用层做了校验，也必须建立唯一索引

注意：在性能上，唯一索引在查询时的性能要比非聚集索引高，但是在插入与更新时要比非聚集索引低

5.长字符索引

在长度较长的字段上建立索引时，必须指定索引长度，没必要对全字段建立索引

索引的长度与区分度是一对矛盾体，一般对于字符串类型的字段，设置索引的长度为 20，区分度会高达 90%以上，可以使用以下sql来确定区分度：

select count(distinct left(列名,索引长度)) / count(*) from 表名

语句设计

1.逻辑删除

大多时候，删除操作应该采用逻辑删除，不能物理删除。
我们必须承认数据是无价之宝，在很多时候，数据的价值是远远高于人工成本的。

正式环境的数据库账号往往是没有delete权限的，避免误操作，删库跑路等等

并且update操作比delete性能高

2.in的使用

虽然in的数量 MySQL 并没有做具体的限制，但对整个 SQL 语句的长度做了限制。 不要进行 in 大量数据集合的操作，若实在无法避免，可以分批次查询，一次in 一定数量集。

3.inner join的使用

当我们使用关联查询的时候，用小表驱动大表的方式效率会提升很多。而 inner join 会自动的进行小表驱动大表的优化

4.触发器和存储过程

避免使用触发器和存储过程，难以调试和扩展不说，更是没有可移植性，这些边缘功能最好不用。

5.count

mysql5.7对count(*) 进行了优化

所以现在 count( *)和count(1)的执行效率是一样的。

而count(字段)因为有sql解析的过程，不仅效率会慢，而且不会对null值进行统计

6.避免大事务

大事务就是运行的时间比较长，操作的数据比较多的事务
大事务会影响数据库的性能，应当尽量把大事务拆成若干个小事务，禁止写过于复杂的sql语句，除了造成大事务不说，还会让别人头大，无法维护。