MySQL开发

最近接收了MySQL培训，一直没有总结，在这里记录一下培训心得，理解有误的地方望指正。

1、MySQL是单进程多线程，Oracle是多进程。

2、MySQL利于分库分表，但是Oracle分库分表成本高，生态不成熟。

3、MySQL插件式存储引擎，一个数据库可以有多个存储引擎，这里介绍常用的四种。

（1）MyISAM存储引擎，这种引擎是MySQL最早提供的。他提高告诉存储和检索，以及全文搜索能力。

（2）InnoDB存储引擎，它提供了事物、行级锁机制和外键约束的功能。是MySQL5.6版本的默认存储引擎。

（3）ARCHIVE存储引擎，这种类型只支持select 和insert语句，而且不支持索引。常应用于日志记录和聚合分析。

（4）Memory存储引擎，这种引擎将表中的数据放在内存中，如果数据库重启或发生崩溃，表中的数据都将消失。非常适合用于存储临时数据的临时表等。默认使用哈希索引。

4、数据库范式化：是指消除冗余、有效组织数据、减少在数据操作期间潜在的不规则和提高数据一致性。在范式化数据库中，每个元素只会被存储一次，至少满足第三范式；逆范式化数据库中，信息是重复的或者保存在多个地方。在某些情况下，从性能角度出发，可考虑逆范式。

5、MySQL在处理大表（char的表>500w行，或int表>1000w）时，性能就开始明显降低，所以单表大小限制在16GB。

6、需要join的字段，数据类型要保持绝对一致。如果两个字段不一致，就会使该字段的索引不可用，从而进行全表扫描。

7、字符串类型，VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，表示可以最大存储255个汉字。TEXT，仅仅当字符数量可能超过20000个的时候，才可以使用TEXT类型来存放字符类数据，因为所有MySQL数据库都会使用UTF8MB4字符集。所有使用TEXT类型的字段必须和原表进行分拆，与原表主键单独组成另外一个表进行存放。

8、NULL，字段建议使用not null类型，可用默认值代替null。MySQL NULL类型和Oracle 的NULL有差异，会进入索引中。此外，NULL在索引中的处理也是特殊的，也会占用额外的存放空间。

9、索引类型

（1）聚簇索引，不是一种单独的索引类型，而是一种存储数据的方式。当表有聚簇索引的时候，它的数据行实际保存在索引的叶子页，聚簇是指实际的数据行和相关的键值都保存在一起，每个表只能有一个聚簇索引。InnoDB的主键为聚簇索引，除此之外，不能通过其他方式指定聚簇索引，如果InnoDB不指定主键，InnoDB会找一个非空唯一的列做聚簇索引，如果还没有这样额字段，则InnoDB会建一个非可见的系统默认的主键--row_id(6个字节长)作为聚簇索引。主键是有序并且无业务含义的。

（2）二级索引，InnoDB中非聚簇索引的所有索引都是二级索引。二级索引的查询代价变大，需要两次B-Tree查询，一次二级索引，一次聚簇索引。

（3）覆盖索引，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件。像这样包含满足查询需要的数据的索引称为覆盖索引。 在ORDER BY , GROUP BY, DISTINCT 的字段需要添加在索引的后面，形成覆盖索引。

（4）前缀索引，在MySQL中，索引只能从字段内容的最左端开始建，查询的时候也只能从索引的最左端开始查，对字段内容只建从左开始的部分字节的索引，而非全部都做索引的这种Index就叫做前缀索引。禁止对过长（MySQL的varchar索引只支持不超过768个字节，UTF8MB4是4字节，所以字段最长192）的VARCHAR类型字段建立索引，除前缀索引外超过32字节的varchar列加索引需要DA评估。所以我们可以通过前缀索引进行like查询。

10、索引的设计规范

（1）连接列上创建索引。对于一个比较大的连接操作，如果被驱动表的连接列上没有索引的话，由于MySQL的连接算法是nestted loop算法，会造成多次扫描被驱动表，对数据库造成的压力和开销是巨大的。

（2）正确创建联合索引中各列的顺序。对于MySQL普遍使用的B-Tree索引，索引列的顺序对于SQL使用该索引至关重要。对于联合索引中包含的属性列中，有一列对应在相关SQL的where子句的过滤条件是以范围条件出现，而索引中其他属性列是以等于条件出现，则应该把这些等值条件对应的列放在索引的前面，把范围条件对应的列放到索引的最后。减少最终数据的访问。

（3）通过索引列属性的前缀控制索引的长度。不管是什么类型的索引，在查询中使用都是需要从磁盘中加载到内存中去的，如果索引大小比较大的话，会造成这些存放索引的内存区域无法存下整个索引数据，根据LRU算法频繁地淘汰索引，加载新的索引进去，这就造成比较大的I/O开销。

（4）避免重复索引。MySQL允许你在同一列上创建多个索引，它不得不单独维护每一个索引，并且查询优化器在优化查询的时候会逐个考虑他们，执行计划也会越长，这会严重影响性能。

（5）其他。唯一键由3个以下字段组成；索引中的字段建议不超过5个；单张表的索引数量控制在5个以内；索引的首字段，必须在where条件中；对于确定需要组成组合索引的多个字段，建议将选择性高的字段靠前放；禁止使用外键，太容易产生死锁，应由程序保证约束。

10、SQL语句编写

（1）拒绝大SQL，拆解成多条简单SQL。简单SQL缓存命中率更高；减少锁表时间；用上多CPU。一条SQL只能在一个CPU运算，5000+QPS的高并发中，可能一条大SQL就把整个数据库堵死。

（2）分解联接保证高并发。

（3）同数据类型的列值比较。数字对数字，字符对字符。数值列同字符类型比较，同时转换为双精度，进行比对；字符列同数值类型比较，字符列整列转数值，不会使用索引查询（导致索引不可用）。

（4）不在索引列做运算。不在索引列进行数学运算或函数运算，否则会导致无法使用索引，进行全表扫描。

（5）禁止使用SELECT *。使用SELECT * 更多的消耗CPU、内存、IO、网络带宽，只取自己想要的列，减少表变化带来的影响，为使用covering index提供可能性，而且在SELECT/JOIN减少硬盘临时表的生成，特别是有TEXT/BLOB时。

（6）避免负向查询和%前缀模糊查询。像NOT , NOT EXISTS, NOT IN , NOT LIKE 等负向查询，使用不了索引，%前缀模糊查询也使用不了索引，导致全表扫描查询。

（7）保持事务（连接）短小。与事务无关操作放到事务外面，减少锁资源的占用，不破坏一致性前提下，使用多个短事务代替长事务，这样也有利于高并发。

（8）其他。用IN代替OR， IN的效率是O(Log n)， OR的效率是O(n)；改写OR为union，Merge index效率比较差；在Limit分页查询中，偏移量越大则越慢，可以将分页查询的全表扫描走主键索引，例如将 select * from table limit 10000,10改为 select * from table where id >= (select id from table limit 10000,1) limit 10；GROUP BY会自动排序，通过order by null，设置无需排序。