高性能可扩展mysql学习笔记

1、数据库命名规范

所有数据库对象名称必须使用小写字母并用下划线分割

所有数据库对象名称禁止使用mysql保留关键字

数据库对象的命名要做到见名识义，并且最好不要超过32个字符

临时库表必须以tmp为前缀以日期为后缀

备份库表以bak为前缀并以日期为后缀 

所有存储相同数据的列和列类型必须一致 ，否则会导致列的索引失效

2、数据库基本设计规范

所有的表必须使用Innodb存储引擎（支持事务，行级锁，更好的恢复性，高并发下性能更好）

数据库和表的字符集统一使用UTF8（兼容性更好）

统一字符集可以避免由于字符集转换产成的乱码

Mysql 中UTF8字符集汉字点3个字节，ASCII吗占用1个字节

所有的表和字段都需要添加注释

尽量控制单表数据量的大小，建议控制在500万以内

可以用历史数据归档和分库分表等控制数据量大小

谨慎使用mysql分区表

分区表中物理上表现为多个文件，中逻辑上表示为一个表

谨慎选择分区键，跨分区查询效率可能更低

建议采用物理分表的方式管理大数据

尽量做到冷热数据分离，减少表的宽度

减少磁盘IO，保证热数据的内存缓存命中率

利用更有效的利用缓存，避免读入无用的冷数据

经常一起使用的列存放到一个表中

禁止在表中建立预留字段（命名很难做到见名识义，无法确认存储数据类型）

对预留字段类型的修改，会对表进行锁定

禁止在数据库中存储图片，文件等二进制数据

禁止在线上做数据压力测试

禁止从开发环境，测试环境直接连接生产环境数据库

3、数据库索引设计规范

索引对数据库等查询性能来说是非常重要的

不要滥用索引

限制每张表的索引数量，建议单张表的索引不超过5个

索引并不是越多越好，索引可以提高效率同样可以减低效率

索引可以添加查询效率，但同样也会减低插入和更新的效率

禁止给表的每一列都建立单独的索引

每个Innodb表必须有一个主键

不能使用更新频繁的列，不使用多列主键

不使用UUID，MD5，HASH，字符串作为主键

主键建议使用自增ID值

常见索引列建议

SELECT、UPDATE、DELETE语句的WHERE从句中的列

包含在ORDER BY、GROUP BY、DISTINCT中的字段

多表JION的关联列

索引字段越小越好

离散度越大的列放到联合索引的前面

如何选择索引列的顺序（从左到右的顺序来使用）

区分度最高的列放在联合索引的最左侧

尽量把字段长度小的列放在联合索引的最左侧

使用最频繁的列放在联合索引的左侧

避免建立冗余索引和重复索引

对于频繁的查询优先考虑使用覆盖索引

覆盖索引：就是包含了所有查询字段的索引

避免Innodb表进行索引的二次查找

可以把随机IO变成顺序IO加快查询效率

尽量避免使用外键

不建议使用外键约束，但一定在表与表之间的关联键上建立索引

外键可用于保证数据的参照完整性，但建议在业务端实现

外键会影响父表和子表的写操作从而降低性能

4、数据库字段设计规范

字段类型的选择，会直接影响数据库的性能

优先选择符合存储需要的最小的数据类型

将字符串转化为数字类型存储

INET_ATON(‘255.255,255,255’)

对于非负型的数据来说，要优先使用无符号整形来存储

无符号相对有符号可以多出一倍多存储空间

VARCHAR（N）中的N代表的是字符数，而不是字节数

使用UTF8存储汉字Varchar(255)=765个字节

过大的长度会消耗更多的内存

避免使用TEXT、BLOB数据类型

TinyText、Text、MidumText、LongText

 建议把BLOB或者是TEXT列分离到单独的扩展表中

TEXT或BLOB类型只能使用前缀索引

避免使用ENUM数据类型

修改ENUM值需要使用ALTER语句

ENUM类型的ORDER BY操作效率低，需要额外操作

禁止使用数值作为ENUM的枚举值

尽可能把所有的列定义为NOT NULL

索引NULL列需要额外的空间来保存，所以要占用更多的空间

进行比较和计算时要对NULL值做特别的处理

不能使用字符串来存储日期

无法用日期函数进行计算和比较

用字符串存储日期和占用更多的空间

使用TIMESTAMP或DATETIME类型存储时间

TIMESTAMP 1970-01-01 00:00:01～2038-0-8 03:14:07

TIMESTAMP 占用4个字节和INT相同，但比INT可读性高

超出TIMESTAMO取值范围的使用DATETIME类型

同财务相关的金额类数据，必须使用decimal类型

非精准浮点：float，double

精准浮点：decimal

Decimal 类型为精准浮点数，在计算时不会丢失精度

占用空间由定义的宽度决定

可用于存储比bigint更大的整形数据

5、数据库SQL开发规范

建议使用预编译语句进行数据库操作

只传参数，比传递SQL语句更高效

相同的语句可以一次解析，多次使用，提高处理效率

尽量避免数据类型的隐式转换

隐式转换导致索引失效

合理的利用存在索引，而不是盲目添加索引

充分利用表上已经存在的索引

避免使用%号的查询条件，如a like ‘%123%’

一个SQL只能利用复合索引中的一列进行范围查询

使用left join 或not exists来优化not in 操作

程序连接不同的数据库使用不同的账号，禁止跨库查询

为数据库迁移和分库分表留出余地

降低业务耦合度

避免权限过大而产生的安全风险

禁止在查询使用SELECT * 

消耗更多的CPU和IO以及网络带宽资源

无法使用覆盖索引

可减少表结构变更带来的影响

禁止使用不含字段列表的INSERT语句

insert into t values(‘a’,’b’,’c’);

insert into t(t1,t2,t3) values(‘a’,’b’,’c’);

可减少表结构变更带来的影响

避免使用子查询，可以把子查询优化为join操作

子查询的结果集无法使用索引

子查询会产生临时表操作，如果子查询数据量大则严重影响效率

消耗过多的CPU及IO资源

避免使用JOIN关联太多的表

每JOIN一个表会多占用一部分内存（join_buffer_size）

会产生临时表操作，影响查询效率

Mysql最多允许关联61个表，建议不超过5个

减少同数据库的交互次数

数据库更适合批量操作

合并多个相同的操作到一起，可以提高处理效率

使用in代替or

In的值不要超过500个

In操作可以有效的利用索引

禁止使用order by rand()进行随机排序

会把表中的所有符合条件装载到内存中进行排序

会消耗大量的CPU和IO及内存资源

推荐中程序中获取一个随机值，然后从数据库中获取数据的方式

WHERE从句中禁止对列进行函数转换和计算，无法使用相关索引

在明显不会有重复时使用UNION ALL而不是UNION

UNION会把所有数据放到临时表中后，再进行去重操作

UNION ALL不会再对结果集进行去重操作

拆分复杂的大SQL为多个小SQL

Mysql一个SQL只能使用一个CPU进行计算

SQL拆分后，可以通过并行执行来提高处理效率

6、数据库操作行为规范

超过100万行的批量写操作，要分批多次进行操作

大批量操作可能会造成严重的主从延迟

binlog 日志为row格式时会产生大量的日志

避免产生大事务操作

对大表数据结构修改一定要谨慎，会造成严重的锁表操作。尤其是生产环境，是不能忍受的

对于大表操作使用pt-online-schema-change修改表结构

percona工具

可以避免大表修改产生的主从延迟

避免在对表字段进行修改时进行锁表

禁止为程序使用账户赋予super权限

当达到最大连接数限制时，还允许1个有super权限的用户连接

Super权限只能留给DBA处理问题的账号使用

对于程序连接数据库账号，遵循权限最小原则

程序使用数据库账号只能在一个DB下使用，不准垮库

程序使用的账号原则上不准有drop权限

第三范式

一个表的列和其它列之间既不包含部分函数依赖关系，也不包含传递函数依赖关系，那么这个表的设计就符合第三范式

确认mysql是否支持分区表：mysql>SHOW PLUGINS

如果有partition就说明有分区

HASH分区的特点

根据MOD（分区键，分区数）的值把数据行存储到表的不同分区中

数据可以平均分布在各个分区中

HASH分区的键值必须是一个INT类型的值，或是通过函数可以转为INT类型

RANGE分区特点

根据分区值的范围把数据行存储到表的不同分区中

多个分区的范围要连续，但是不能重叠

默认情况下使用VALUES LESS THAN属性，即每个分区不包括指定的那个值

RANGE分区适用场景

分区键为日期或者时间类型

所有查询中都包括分区键

定期按分区范围清理历史数据

LIST分区的特点

按分区键取值的列进行分区

同范围内分区一样，各分区的列表值不能重复

每一行数据必须能找到对应的分区列表，否则数据插入失败

 PRATITION BY LIST(login_type){

}

添加分区ALTER TABLE login_log ADD PARTITION (PARTITION p4 VALUES LESS THAN(2018))

删除分区ALTER TABLE login_log DROP PARTITION p0

分区数据归档迁移条件

1、mysql>=5.7

2、结构相同

3、归档到的数据表一定要是非分区表

4、非临时表不能有外键约束

5、归档引擎要是archive

归档引擎的表只能进行读操作，不能写操作

使用分区表注意事项

结合业务场景选择分区键，避免跨分区查询

对分区表进行查询最好在WHERR从句中包括分区键

具有主键或唯一索引的表，主键或唯一索引必须是分区键的一部分

查看SQL执行计划

EXPLAIN

支持select，update，insert，replace，delete

使用执行计划可以知道

SQL如何使用索引

联捷查询的执行顺序

查询扫描的数据行数

ID列中的数据为一组数字，表示执行SELECT语句顺序

ID值相同，执行顺序由上至下

ID值越大优先级越高，越先被执行

SELECT_TYPE

SIMPLE                  不包含子查询或是UNION操作的查询（简单查询）

PRINARY                查询中如果包含任何子查询，那么最外层的查询则被标记为PRINAPY

SUBQUERY            SELECT列中的子查询

DEPENDENT SUBQUERY     依赖外部结果的子查询

UNION                         Union操作的第二个或者是之后的查询的值为union

DEPENDENT UNION  当UNION做为查询时，第二个或是第二个后的查询的select_type值

UNION RESULT           UNION产生的结果集

DERIVED                      出现在FROM字句中的子查询

TABLE列

输出数据行所在的表的名称或者别名

<unionM,N>由ID为M，N查询union产生的结果集，临时表

<derivedN>/<subqueryN>由ID为N的查询产生的结果

PARTITIONS列

对于分区表，显示查询的分区ID

对于非分区表，显示为NULL

type列

高

System  这是const联接类型的一种特例，当查询的表只有一行时使用

const     表中有且只有一个匹配的行时，如对主键或者是唯一索引的查询，这是效率最高的联接方式

eq_ref    唯一索或者主键引查找，对于每个索引键，表中只有一条记录与之匹配

ref          非唯一索引查找，返回匹配某个独立值的所有行

ref_or_null          类似于ref类型的查询，但是附加了对NULL值列的查询

index_merge      该联接类型表示使用了索引合并优化方法

range      索引范围扫描，常见于between,>,<这样的查询条件

index       FULL index scan 全索引扫描，同ALL的区别是，遍历的是索引树

ALL          FULL TALBLE Scan 全表扫描，这是效率最差的联接方式

低

Extra列

Distnct             优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

Not exists        使用not exists来优化查询

Using filesort   使用额外操作进行排序，通常会出现在order by 或group by 查询中

Using index      使用了覆盖索引进行查询

Using temporary mysql需要使用临时表来处理查询，常见于排序，子查询，和分组查询

Using where      需要在mysql服务器层使用where条件来过滤数据

select tables optimized away     直接通过索引来获取数据，不用访问表，执行效率最高

POSSIBLE_KEY列

指出mysql能使用哪些索引来优化查询

查询列所涉及到列上的索引都会被列出，但不一定会被使用

KEY列

查询优化器优化查询实际使用的索引

如果没有可用的索引，则显示为NULL

如查询使用了覆盖索引，则该索引仅出现在key列中

KEY_LEN列

表示索引字段的最大可能长度

key_len的长度由字段定义计算而来，并非数据的实际长度

Ref列

表示那些列或常量被用于查找索引列上的值

Rows列

表示mysql通过索引统计信息，估算的所需读取的行数

Rows值的大小是个统计抽样结果，并不十分准确

filtered列

表示返回结果的行数占需读取行数的百分

Filtered列的值越大越好

Filtered列的值依赖说统计信息

执行计划的限制

无法展示存储过程，触发器，UDF对查询的影响

无法使用EXPLANE对存储过程进行分析

早期版本的mysql只支持SELECT语句进行分析

分组

SELECT order_id product_id ,COUNT(*)

FROM product_comment

GROUP BY order_id,product_id HAVING COUNT(*)>1

备份表（如果数据量大的话，需要使用文件备份） 

CREATE TABLE bak_product_comment_180303

AS 

SELECT * FROM product_comment;

删除重复数据

1、先查询是否存在对于同一个订单同一商品的重复评论

2、备份表

3、删除重复数据

捕获有问题的SQL

启动mysql慢查询日志

set global slow_query_log_file = /sql_log/slow_log.log

set global log_queties_not_using_indexes = on; 

未使用索引的SQL记录日志

set global long_query_time = 0.001;

抓取执行超过多少时间的SQL（秒）

set global low_query_log = no;

mysqldumpslow slow-mysql.log

数据库备份

对于如何数据库来说，备份都是非常重要;

数据库复制不能取代备份的作用

逻辑备份和物理备份

逻辑备份的结果为SQL语句，适合于所有存储引擎。需要花费很多时间

物理备份是对数据库目录的拷贝，对于内存表只备份结构

PERCONA在线处理备份数据工具

全量备份和增量备份

全量备份是对整个数据库的一个完整备份

增量备份是在上次全量备份基础上，对于更改数据进行的备份

mysqldump不支持 增量备份

mysqldump [OPTIONS] database [tables] 针对某个数据库

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2..] 针对多个数据库

mysqldump [OPTIONS] --all-databases [OPTIONS] 针对所有数据库

mysqldump的常用参数

-u - -user =name

-p,--password[=name]

SELECT,RELOAD,LOCK TABLES,REPLICATION CLIENT,SHOW VIEW ,PROCESS

— single-transaction先启动一个事务，以此获得数据备份的一致性，只能对InonoDb有效

-l,—lock-tables一次性锁下所有表 和上面是互斥的

-x,—lock-all-tables。可以保证整个实例下的数据保持一致性，备份的过程中，数据库就成只读数据库，不能写

—master-data =[1/2] 

如果选项赋值为2，那么CHANGE MASTER TO 语句会被写成一个SQL comment（注释），从而只提供信息; 最好用2

如果选项赋值为1，那么语句不会被写成注释并且在dump被载入时生效。默认为1

 

如果要备份存储过程，触发器，数据库调度事件

-R，—routines 存储过程

—triggers 触发器

-E，—events 数据库调度事件

—hex-bob. 数据库就会以16进制保存

—tab=path 会在指定的路径下生成2个文件，一个存储表结构，一个存储表数据

-w,—where = ‘过滤条件‘ where只支持单表数据条件导出

备份全部数据表

mysqldump -uuser -p - -master-data = 2 - -single-transaction - - routines - -triggers - -events mc_orderdb > mc_orderdb.sql

备份单个表

mysqldump -uuser -p - -master-data = 2 - -single-transaction - - routines - -triggers - -events mc_orderdb order_master > order_master.sql

备份所有的库及全部表

mysqldump -uuser -p - -master-data = 1 - -single-transaction - - routines - -triggers - -events - - all -databases  >mc.sql

恢复mysqldump备份的数据库

mysql -u -p dynamo < backup.sql

Mysql > sure /tmp/backup.sql

如果主数据库被误删除，可以从备份数据库中恢复

1、先左关联表，查出id不存在的数据库

2、如何再插入到原表中

如何进行指定时间点恢复

进行某一时间点的数据恢复

恢复到误操作的时间

需要具有指定时间点前到一个全备

具有自上次全备后到指定时间点的所有二进制日志

Mysql5.6之后，可以实时备份Binlog

CRANT REPLICATION SLAVE ON *.* TO ‘real’@‘ip’ IDENTIFIED BY ‘xxxx”;

Mkdir -p binlog_backup

Mysqlbinlog - -raw - -read-from-remote-server \

 - -stop-never  - -host localhost - -port 3306 \

-u real -p xxxxx二进制日志名

可以实时二进制文件备份

xtrabackup 用于在线备份innodb存储引擎的表

PERCONA开源的在线备份工具

备份过程中，不会影响表的读写操作

只会备份数据文件而不会备份表的结构

innobackupex 是对xtrabackup的封装并提供MyISAM表的备份功能

同样会锁表

innobackupex - - user=root - -password=pwd \

                        - -paraller=2 /home/db_backup/ 

支持多线程备份，paraller 是指线 程数 

innobackupex - - apply-log /path/to/BACKUP-DIR

如何解决单点问题

增加额外的数据库服务器，组建数据库集群

同一集群中的数据库服务器需要具有相同的数据

集群中的任一服务器宕机后，其它服务器可以取代宕机服务器

Mysql主从复制架构

1、主库将变更写入到主库的binlog中

一些mysql版本并不会开启二进制日志，所以一定要检查是否开启

增量备份也需要二进制日志

2、从库的IO进程读取主库binlog内容存储到RelayLog 日志中

     二进制日志点

     GTID（mysql>=5.7推荐使用）

3、从库的SQL进程读取Relay Log 日志中内容中从库中重放

Mysql主从配置步骤

配置主从数据库服务器参数

在Master服务器上创建用于复制的数据库账号

备份Master服务器的数据并初始化Slave服务器数据

启动复制链路

Master 主服务器配置

log_bin = /data/mysql/sql_log/mysql-bin

server_id = 100

slave服务器配置

log_bin = /data/mysql/sql_log/mysql-bin

Server-id = 101

relay_log - /data/mysql/sql_log/relay-bin

read_only = no

skip_slave_start = on

master_info_repository = TABLE

relay_log_info_repository = TABLE

Mysql5.7 可以使用super_read_only = on

在MASTER服务器上建立复制账号

用于IO进程连接Master服务器获取binlog日志

需要PEPLICATION SLAVE权限

创建 CREATE UESR ‘repl’@‘’IP段‘ identified by ‘Password’;

授权 GRANT REPLICATION SLAVE ON *.* TO ‘real’@‘IP段‘；

初始化Slave数据

建议主从数据库服务器采用相同的mysql版本

建议使用全库备份的方式初始化Slave数据

Mysqldump - - master-data=2 -uroot -p -A \

                    - -single-transaction -R - -triggers

启动基于日志点的复杂链路

CHANGE MASTER TO 

        MASTE_HOST= ‘master_host_ip’,

        MASTER_USER=‘real’,

        MASTER_PASSWORD=‘PassWord’,

        MASTER_LOG_FILE=‘mysql_log_file_name’

        MASTER_LOG_POS=xxxxx;