MySQL

几个问题：说说MySQL的优化，说说索引、范式与反范式各自的特点、对InnoDB引擎的了解、常见的limit语句优化、如何修改一个大表的结构，单点问题与主从复制，数据库复制与备份。

MySQL优化：

    (1)数据库表结构优化、索引优化、SQL语句优化

    (2)系统配置优化与服务器硬件优化。

表的范式化设计：3范式的内容，范式与反范式各自的优缺点

    (a)数据库3范式：

        第一范式：属性列是不可再分割的(eg:excel可分)，且有基本数据类型构成。

        第二范式：在第一范式的基础上，不能存在非主键列对部分主键的依赖（联合主键场景下）。设联合主键(col1,col2,col3)，若仅由col2,col3就可以确定col4，即(col2,col3)-->col4，显然存在部分依赖。

        第三范式：非主属性列即不部分依赖于主键，也不传递依赖于主键。

    (b)范式化设计的优缺点：

        优点：

        减少数据冗余(根本)：

1.更新操作比反范式化更快。(例如将选课表[学号,课程号]与课程详情表[课程号cid，学分数，教师编号]分离，修改cid=1的课程学分时，仅需更新课程详情表的一条记录，不需要对选课表更新；若两张表没有分离，则需要更新多条记录，受影响的行数少)。

2.范式化的表通常比反范式化更小。

        缺点：范式化可以提高对数据库的写性能而可能会降低读性能:

1.select操作可能需要关联多个表才能找到所需的全部列，而表关联的成本比较高。

2.更难对查询进行索引优化，范式化可能将两个经常一起使用的列存放在两个不同的表中，这样就没法建联合索引（若在同一个表中，本来是可以使用联合索引的，但在不同的表中就无法使用）。

    (c)反范式化的优缺点：

        优点：

1.对于查询，可以减少表的关联操作就可以找出所需的全部列；(空间换时间)

2.可以更好的对查询进行索引优化：对经常一起查询的列建联合索引。

        缺点：

1.存在大量数据冗余以及维护异常；

2.对数据的更新需要更多的成本(影响的记录数量，记录大小)。

        成本主要包含：CPU,磁盘IO,网络传输。

    完全的范式化或者反范式化对数据库性能来说都不是最好的，因此，在设计数据库时要结合范式化与反范式化，使数据库性能尽量高。

索引设计规范：索引的优缺点、在那些列上建索引，建什么类型的索引、联合索引列的排列顺序、什么情况下索引无法利用

1.不要滥用索引，索引可以加快查询速度，但也会增加insert/update/delete 时维护索引的开销；另外索引也占据一定的空间；过多的索引会增加查询优化器的选择时间。

2.索引建在哪些列上：select/update/delete语句中where从句中的列；包含在order by、group by、distinct从句中的字段，对于order by后面的列可以建立联合索引来提高效率；join的关联列；

3.建立联合索引时索引列的顺序：区分度最高的列放在联合索引的最左侧；区分度一致时，把字段长度小的放在联合索引的左侧，可以一定程度减少IO数据量；使用最频繁的列放在联合索引的左侧。

4.联合索引最左匹配原则。

    (5)前置模糊查询导致该索引无法利用：where a like "%olihao"。后置模糊查询可以走索引：where a like "olihao%"，但是联合索时，将导致后面的索引无法使用：有一个联合索引(col1,col2,col3)，查询条件where col1=xxx and col2 like "Olihao%" and col3=xxx，无法利用索引col3。

6.对经常一起查询的列可以建立覆盖索引。

SQL开发规范：

1.禁止在where从句中对列进行函数转换和运算，这样会导致无法利用该列上的索引。

        where date(createtime)='20160901';

       正确做法：where createtime>='20160901' && createtime<'20160902'。

        where id+1=100;

        正确做法：where id=99;

2.一般情况避免使用子查询，把子查询优化为join操作。子查询得到的结果集无法使用索引且结果集会被存储到一个临时表中，如果子查询得到的数据量很大则严重影响效率，这些临时表的也回浪费大量的CPU和IO资源。--视情况而定。但一次joi的表也不能太多。

3.避免前置模糊查询，如 a like "%123"，以便充分利用索引。

4.禁止使用select * ...；必须使用select [字段列表] 查询。不能有效利用覆盖索引，消耗更多的CPU及IO以及网络资源；对应用程序来说，用后者还可以减小表结构变更带来的影响。

5.禁止在数据库中存储图片，文件等二进制数据。一般把他们放到文件系统中，数据库存放其在文件系统中的路径。

6.超过100万行的批量写操作，要分批多次进行操作。因为大批量的操作可能会导致严重的主从延迟；这种操作一般在一个事务中进行，会导致对表中大批量的数据进行锁定，从而产生大量的阻塞导致性能下降。

7.将逻辑复杂的大sql拆分为多个小sql 。因为mysql的一个sql只能在一个CPU上进行计算，sql拆分后可以通过并行执行来提高处理效率。

8.对大表结构的修改要谨慎，有可能导致严重的锁表操作，最好借助某些工具完成。以pt-online-schema-change为例，先复制一份副本，对副本进行表结构修改，这样原表依然可以对外提供服务(copyOnWrite机制)，并在原表上定义触发器，把对原表的数据内容修改同步到副本，最后重命名新表)。

9.尽量做到冷热数据分离。冷数据量大，访问概率低，可以进行定期归档。

MyISAM与InnoDB存储引擎：两种存储引擎表的物理存储形式，各自的优缺点

    (a)MyISAM：MyISAM表由3个文件组成：.frm表定义文件，.MYD数据文件，.MYI索引文件(数据文件与索引文件独立)；IMyISAM采用表级锁，意味着比较低的并发性；不支持事务。适用场景：非事务型应用，只读类应用(虽然采用表级锁，但读锁是共享的)。

     (b)InnoDB：InnoDB表由2个文件组成：.frm表定义文件，.idb文件(通过索引组织表组织数据，索引和数据存在于一个文件中)；InnoDB支持行级锁，可以最大程度的支持并发； 支持事务。

     InnoDB索引组织表：一颗B+树，数据都存在叶子节点上，并且通过单链表连接起来。

InnoDB分区表：逻辑上的一张表，实际上数据存放在多个文件中。各种分区的适用场景。

    (a)对于innodb，没有进行分区时一张表只包含两个文件：表定义文件table_name.frm，数据文件table_name.idb(独立表空间开启)。采用分区表后，不仅包含一个表定义文件table_name.frm，还包含多个分区数据文件。

    (b)分区方式：hash分区，range范围分区，list枚举分区

//HASH分区demo
CREATE TABLE 'customer_login_log' (
   'customer_id' INT(10) UNSIGNED NOT NULL,
   'login_time' TIMESTAMP NOT NULL,
   'login_ip' INT(10) NOT NULL,
   'login_type' TINYINT(4) NOT NULL
)ENGINE=INNODB
PARTITION BY HASH(customer_id)(
    PARTITIONS 4//4个分区
)
//RANGE范围分区demo，用id分区--看场景
CREATE TABLE 'customer_login_log' (

'customer_id' INT(10) UNSIGNED NOT NULL, 
'login_time' TIMESTAMP NOT NULL, 
'login_ip' INT(10) NOT NULL, 
'login_type' TINYINT(4) NOT NULL    
)ENGINE=INNODB    
PARTITION BY RANGE(customer_id)( 
PARTITION p0 VALUES LESS THAN (10000), 
PARTITION p1 VALUES LESS THAN (20000),//10000-19999 
PARTITION p2 VALUES LESS THAN (30000),//20000-29999 
PARTITION p3 VALUES LESS THAN MAXVALUE    
)  
//List分区demo
CREATE TABLE 'customer_login_log' (
   'customer_id' INT(10) UNSIGNED NOT NULL,
   'login_time' TIMESTAMP NOT NULL,
   'login_ip' INT(10) NOT NULL,
   'login_type' TINYINT(4) NOT NULL
)ENGINE=INNODB
PARTITION BY LIST(login_type)(
    PARTITION p0 VALUES IN (1,3,5,7,9),
    PARTITION p1 VALUES IN (2,4,6,8)
 )

        hash分区的主要特点：根据mod运算把数据行存储到表的不同分区中；数据可以均匀的分布在各个分区中。若用非整形列作为hash分区的键，则要用一些函数把值转化为相应的整形。

        range分区的适用场景：分区键是日期或者时间类型。(1)按这类分区键可以使各分区表中存储的数据尽量均衡。假设本例的活跃用户id分布在10000-19999中，那么将导致分区表p2比其它分区大很多，失去了分区的意义。(2)另外按时间分区可以方便对数据进行归档。(3)方便定期清理历史数据。

        list枚举分区的特点：同范围分区一样，各分区的列表值不能重复；每一行的数据必须要能找到对应的分区列表，

    (c)采用了分区键，则在进行查询时where子句中最好包含分区键，这样可以尽量避免跨分区表扫描数据，提高查询效率。

    (d)要根据应用场景来采用正确的分区类型以及分区键。对于登录日志可以采用范围分区，用登录时间做分区键。

//对用户登录日志表采用范围分区，用登录时间做分区键
CREATE TABLE 'customer_login_log' (
   'customer_id' INT(10) UNSIGNED NOT NULL,
   'login_time' TIMESTAMP NOT NULL,
   'login_ip' INT(10) NOT NULL,
   'login_type' TINYINT(4) NOT NULL
)ENGINE=INNODB
PARTITION BY RANGE(YEAR(login_time))(
    PARTITION p0 VALUES LESS THAN (2017),
    PARTITION p1 VALUES LESS THAN (2018),
    PARTITION p2 VALUES LESS THAN (2019)
)

数据库规划：

        (1)避免跨库操作，把经常一起关联查询的表放在一个DB中。

        (2)为了方便识别表所在的db,一般在表名前加库名前缀。

数据库表、数据库的拆分：

数据库分库：

1.把一个数据库实例中的多个数据库拆分到不同的实例中，如把一个同时含<订单库、商品库、用户库>的数据库示例拆分到不同的数据库实例中。一个数据库实例一般指提供相同功能的集群。

缺点：1.可能导致跨库查询(跨数据库实例)；2.如果本身的写压力本身就是集中在某一个数据库中，那么拆分后压力基本还是没有得到缓解。

2.把原来逻辑上属于同一个数据库的表，分到不同数据库中。当单表数据量逐渐变大后，单库单表的压力任然可能很大

数据库分表：对数据表的拆分

        表的垂直拆分：把原来一个有很多列的表拆分成多个表，解决了表宽度过宽的问题。垂直拆分原则：把经常一起使用的字段放在一起；把不常用的字段一起单独存放到一个表中；把大字段独立存放到一个表中。

        表的水平拆分：解决单表数据量过大的问题，水平拆分得到的所有表的表结构都是相同的。常见拆分方法：hash取模。问题：跨表查询，统计查询。也可以采用历史数据归档解决大表问题。

        水平拆分与分区表的区别(不要混淆)

MySQL复制与备份：

    单点的宕机将导致整个服务的不可用，于是引用主/从数据库服务器架构。同时，主/从架构可以实现对数据库的读写分离，利用从库分担主库的读负载，在从服务器上进行读操作，在主服务器上进行写操作。

mysql支持两种复制方式：基于行的复制和基于sql语句的复制。这两种方式都是通过在主库上记录二进制日志，在备库重放日志以实现异步的数据复制，这意味着可能有一定的主从延迟。 利用二进制日志进行增量复制，一般不需要太多的带宽，特别是基于sql语句的复制方式时,所需要的带宽更少；采用基于行的复制方式在进行大批量更改时会给带宽带来一定压力。

    数据库复制不能取代数据库备份的作用，如果在主数据库上错删了一个数据，由于主从复制的延迟很短，从数据库上的数据也可能也已经被删除，因此无法通过从数据库上的数据恢复被删除的数据。

    数据库备份根据结果可以分为逻辑备份和物理备份，逻辑备份的结果一般是sql语句，物理备份直接复制表空间；按内容可以分为全量备份和增量备份，全量备份是对整个数据库的一次完整备份，增量备份在已经备份的基础上对修改的数据进行备份。

为什么用MySQL用B+树做索引而不用B树或者hash：

        (1)hash只适合做等值查询，对于范围查询无能为力。...where id>=100

        (2)B/B+树是多叉树，每层结点数目非常多，层数很少(2-3层)，可以有效减少磁盘IO的次数。在查询数据时，最好的情况就是立即找到目标索引，然后读取数据，使用B+树可以很好地达到这个目的；但B树每个节点都有data域，这无疑增大了每个索引节点的大小；假设磁盘IO一次读出的数据量大小是固定的，单个索引节点变大，则把该索引节点读入内存的IO次数增多）。更重要的，B+树把所有叶子节点以单链表的形式连接起来，利于范围查询...where id>=100。

limit的优化

其它概念:

    1.临时表：在排序、分组等操作时，当数量达到一定大小之后，由查询优化器建立的临时表。

    2.大表：表中数据超过1000w行(看场景)，或者表的数据文件大小达到某个阈值10G。

    3.单条的sql语句本身也是一个事务；mysql默认自动提交。

    4.主从数据库同步复制：主库上事务只有至少在一个从库上提交后才能认为其执行完成。

参：

https://blog.youkuaiyun.com/ligupeng7929/article/details/79529072

https://blog.youkuaiyun.com/ligupeng7929/article/details/79481082