SQL最左前缀原则

针对SQL进行调整，在写SQL的时候遵循最左前缀原则，向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，范围列可以用到索引，但是范围列后面的列无法用到索引。like以通配符%开头索引失效会变成全表扫描的操作。如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描。只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。不要使用select *，改用select加字段名称，因为select *走的聚集索引，会进行全表扫描，如果一定要使用select *的话，mysql至少使用5.6版本，这个版本有一个离散读的优化，离散读的优化是将离散度大的列放到联合索引的前面，举个例子，select * from user where staff_id = 2 and customer_id = 584，这个时候索引优化会将customer_id放到前面，因为它的离散度更高，可以通过select count(distinct customer_id),count(distinct staff_id) from user查看列的离散度。有一个ICP的优化，以往，根据索引查找记录，再根据WHERE条件来过滤记录。使用ICP优化后，会在取出索引的同时，直接根据WHERE条件过滤，将WHERE的部分过滤操作放在了存储引擎层。在某些查询下可以大大减少上层SQL层对记录的索取，从而提高性能。还有一个MRR优化，它是减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，在查询辅助索引时，首先根据得到的查询结果，将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的，然后按照主键进行排序，并按照主键排序的顺序进行书签查找。顺序查找可以对一个页进行顺序查找，无需离散加载数据页，可以减少缓冲池中页被替换的次数，能够批量处理对键值的查询操作。另外在写sql的时候，尽量使用它的一个explain执行计划，去看我们的索引是不是失效了。索引失效有这么几种情况，上面也提到过几个，如果条件中有or，即使其中有部分条件带索引也不会使用。对于复合索引，如果不使用前列，后续列也将无法使用。like以%开头。列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引。where中索引列有运算，有函数的，不使用索引。如果mysql觉得全表扫描更快的时候，数据少的情况下，不使用索引。

第四步，随着数据量的增涨之后，会考虑一个数据的分区，Partition分区，表分区必须在表建立的时候创建规则，而已经存在的没有创建过表分区规则的表需要重新做导入处理。如果想修改有规则的表分区，只能新增，不要随意删除，删除表分区会造成该表分区内部数据也一起被删除掉。除此之外，分区也需要结合实际场景进行分区，而且Partition分区也是有一个局限性，因为单台的mysql服务器支持1024个分区，一旦达到这个分区上限，考虑垂直拆分和水平拆分了。垂直分区，它是将单表变多表，这样也是有些优点的，一条数据存储的数据量越小，三层b+所容纳的数据量是更多的，这样一个分区就可以承载多个数据，多个分区它承载的数据就会更多，这是一个累加的效果，不过也有缺点，就是进行表的垂直划分的时候，还需要考虑它的一个关联性，在进行sql查询的情况下，需要反复测试，考虑它的一个性能问题，最好的结果就是拆分出来的表还是能够支持铁定的业务线。比如一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。随着数据量持续的增涨，这个时候就需要考虑水平分区了，水平分区有多种模式，Range（范围）模式：允许DBA将数据划分不同范围。比如DBA可以将一个表通过年份划分成三个分区，80年代的数据，90年代的数据以及任何在2000年之后的数据。Hash（哈希）模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区，比如DBA可以建立一个，对表的主键进行分区的表。List（预定义列表）模式：允许系统通过DBA定义列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年，2005年，2006年值对应数据。Composite（复合模式），就是多模式的组合使用，在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。

第五步，就是冷热备份，对于一些无用的数据，这个时候根据实际的需求，对数据进行一个实时的备份，保证MySQL的数据保持在一个比较稳定的情况。