1、表结构设计上
1)
字符集是否遵循了最小化原则?(能用latin的就不用gbk。能用gbk的就不用utf8)
2)
索引上是否有滥用？(根本不使用的字段建索引、不适合建索引的字段建索引、重复建索引或者不能很好的利用前缀索引等)
3)
冗余字段是否太多？(各表中不用的或者字段冗余太多)
4)
不正确的字段类型？(能用1个字节非要用几个字节，像枚举类、状态类比较常见)
5)
将较长的字段或者几个字段组合做为主键？（主键最好用mysql自增）
具体事例如下：
CREATE
TABLE
`class_meta`
(
`class_name`
varchar(128)
NOT
NULL
COMMENT
'类名',
`class_desc`
varchar(2048)
default
''
COMMENT
'类的描述',
`class_status`
char(20)
default
'test1'
COMMENT
'test1,test2',
PRIMARY
KEY
(`class_name`),
UNIQUE
KEY
`cm_cn_uk`
(`class_name`),
KEY
`cm_cd_ind`
(`class_desc`(767)),
KEY
`cm_cs_ind`
(`class_status`),
KEY
`cm_cdcn_ind`
(`class_desc`(767),`class_name`)
)
ENGINE=InnoDB
DEFAULT
CHARSET=latin1
COMMENT='meta信息'
通过上面的表结构能看到如下地方不合适
1、主键与唯一索引明显重复,索引cm_cd_ind与索引cm_cdcn_ind索引重复（这种情况经常出现，大家留意下）
2、cm_cs_ind如果两个状态分布均匀也明显不合适建索引
3、class_desc由于是描述性质的，也不合适建索引
4、最好以自增做为主键，可以减少整表的空间
5、class_status列明显可以用tinyint来存，可以省下19个字节
2、存储内容上
1)
是否将图片、视频、音乐等大数据存储在表中？(表里最好只保留路径而不是实际的文件内容)
3、数据保留上
1)是否有已过期而未删除的数据?(对于无效数据及时清理或者进行历史归档)
4、后期维护上
1)是否对经常删除的表进行维护(optimize
table)
建议:
1、在性能要求不高的case中(并发不太高)，可以考虑使用压缩表。一般压缩率在30%-70%之间，收益非常可观。
2、对于删除非常频繁的表要定期进行优化，使表中碎片减少。提高查询、写入的性能。
3、在表结构设计上，一定要发扬“斤斤计较”的精神，能用1个字节表示的坚决不用2个字节。
4、尽量减少大字段的使用。
ps:经常在跟开发评审表结构的时候，时常会被笑话说DBA太抠门，一点都不大气。当数据量小的时候可能大家不觉得，但当你的数据级到T或者P的时候，哪怕多省几个字节都是非常可观的，给大家简单算笔账，如果我们将一张5亿条记录的表，字段从100个字节降到60个字节(应该很容易做到吧)，那么不算上索引将节约大约18G的空间。
分区介绍：
一、什么是分区？
所谓分区，就是将一个表分成多个区块进行 *** 作和保存，从而降低每次 *** 作的数据，提高性能。而对于应用来说则是透明的，从逻辑上看只有一张表，但在物理上这个表可能是由多个物理分区组成的，每个分区都是独立的对象，可以进行独立处理。
二、分区作用
1.可以逻辑数据分割，分割数据能够有多个不同的物理文件路径。
2.可以存储更多的数据，突破系统单个文件最大限制。
3.提升性能，提高每个分区的读写速度，提高分区范围查询的速度。
4.可以通过删除相关分区来快速删除数据
5.通过跨多个磁盘来分散数据查询，从而提高磁盘I/O的性能。
6.涉及到例如SUM()、COUNT()这样聚合函数的查询，可以很容易的进行并行处理。
7.可以备份和恢复独立的分区，这对大数据量很有好处。
三、分区能支持的引擎
MySQL支持大部分引擎创建分区，入MyISAM、InnoDB等；不支持MERGE和CSV等来创建分区。同一个分区表中的所有分区必须是同一个存储引擎。值得注意的是，在MySQL8版本中，MyISAM表引擎不支持分区。
四、确认MySQL支持分区
从MySQL5.1开始引入分区功能，可以如下方式查看是否支持：
老版本用：SHOW VARIABLES LIKE '%partition%'
新版本用：show plugins
五、分区类型
1. RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
例如，可以将一个表通过年份划分成两个分区，2001 -2010年、2011-2020。
2. LIST分区：类似于RANGE分区，LIST是列值匹配一个离散值集合中的某个值来进行选择。
比如 根据字段 把值为1、3、5的放到一起，2、4、6的另外放到一起 等等...
3. HASH分区：基于用户定义的表达式的返回值来进行选择分区，该表达式使用将要插入到表中的这些行的列值来进行计算，这个函数必须产生非负整数值。
通过HASH运算来进行分区，分布的比较均匀
4. KEY分区：类似于按HASH分区，由MySQL服务器提供其自身的哈希函数。
按照KEY进行分区类似于按照HASH分区
六、使用分区注意事项
1. 如果表中存在primary key 或者 unique key 时，分区的列必须是paimary key或者unique key的一个组成部分，也就是说，分区函数的列只能从pk或者uk这些key中取子集
2. 如果表中不存在任何的paimary key或者unique key，则可以指定任何一个列作为分区列
3. 5.5版本前的RANGE、LIST、HASH分区要求分区键必须是int；MySQL5.5及以上，支持非整形的RANGE和LIST分区，即：range columns 和 list columns (可以用字符串来进行分区)。
七、分区命名
1. 分区的名字基本上遵循其他MySQL 标识符应当遵循的原则，例如用于表和数据库名字的标识符。应当注意的是，分区的名字是不区分大小写的。
2. 无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录。
八、 创建分区
1. RANGE分区：
解读：以上为 uuid小于5时放到p0分区下，uuid大于5且小于10放到p1分区下，uuid大于10且小于15放到p2分区下，uuid大于15 一直到最大值的存在p3分区下
2. LIST分区：
解读：以上为uuid 等于1/2/3/5时放到p0分区，7/9/10放到p1分区，11/15放到p2分区。当时用insert into时 如果uuid的值不存在p0/p1/p2分区时，则会插入失败而报错。
3. HASH分区:
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE分区和LIST分区中必须明确指定一个指定的列值或列值集合以指定应该保存在哪个分区中。而在HASH分区中，MySQL会自动完成这些工作，要做的只是基于将要被哈希的列值指定一个表达式，以及指定被分区的表将要被分割成的分区数量，如：
解读：MySQL自动创建3个分区，在执行insert into时，根据插入的uuid通过算法来自动分配区间。
注意：
(1) 由于每次插入、更新、删除一行，这个表达式都要计算一次，这意味着非常复杂的表达式可能会引起性能问题，尤其是在执行同时影响大量行的运算（例如批量插入）的时候。
(2) 最有效率的哈希函数是只对单个表列进行计算，并且它的值随列值进行一致的增大或减小，因为这考虑了在分区范围上的“修剪”。也就是说，表达式值和它所基于的列的值变化越接近，就越能有效地使用该表达式来进行HASH分区。
3.1：线性HASH分区
线性HASH分区在“PARTITION BY”子句中添加“LINEAR”关键字。
线性HASH分区的有点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有及其大量数据的表。它的缺点在于各个分区间数据的分布不大可能均衡。
4. KEY分区
类似于HASH分区，HASH分区允许用户自定义的表达式，而KEY分区则不允许使用用户自定义的表达式；HASH分区只支持整数分区，KEY分区支持除了blob和text类型之外的其他数据类型分区。
与HASH分区不同，创建KEY分区表的时候，可以不指定分区键，默认会选择使用主键或唯一键作为分区键，没有主键或唯一键，就必须指定分区键。
解读：根据分区键来进行分区
5. 子分区
子分区是分区表中，每个分区的再次分割，适合保存非常大量的数据。
解读：主分区使用RANGE按照年来进行分区，有3个RANGE分区。这3个分区中又被进一步分成了2个子分区，实际上，整个表被分成了3 * 2 = 6个分区。每个子分区按照天进行HASH分区。小于2017的放在一起，2017-2020的放在一起，大于2020的放在一起。
注意：
(1) 在MySQL5.1中，对于已经通过RANGE或LIST分区了的表在进行子分区是可能的。子分区既可以使用HASH分区，也可以使用KEY分区。这也被称为复合分区。
(2) 每个分区必须有相同数量的子分区。
(3) 如果在一个分区表上的任何分区上使用SUBPARTITION来明确定义任何子分区，那么就必须定义所有的子分区。
(4) 每个SUBPARTITION子句必须包含（至少）子分区的一个名字。
(5) 在每个子分区内，子分区的名字必须是惟一的，目前在整个表中，也要保持唯一。例如：
子分区可以用于特别大的表，可以在多个磁盘间分配数据和索引。例如：
九、MySQL分区处理NULL值的方式
十、分区管理概述
可以对分区进行添加、删除、重新定义、合并或拆分等管理 *** 作。
① RANGE和LIST分区的管理
1. 删除分区语句如：alter table tbl_test drop partition p0
注意：
(1) 当删除了一个分区，也同时删除了该分区中所有的数据。
(2) 可以通过show create table tbl_test来查看新的创建表的语句。
(3) 如果是LIST分区的话，删除的数据不能新增进来，因为这些行的列值包含在已经删除了的分区的值列表中。
2. 添加分区语句如：alter table tbl_test add partition(partition p3 values less than(50))
注意：
(1) 对于RANGE分区的表，只可以添加新的分区到分区列表的最高端。
(2) 对于LIST分区的表，不能添加已经包含在现有分区值列表中的任意值。
3. 如果希望能不丢失数据的条件下重新定义分区，可以使用如下语句：
REORGANIZE会对分区的数据进行重构。
ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO(partition_definitions)
(1) 拆分分区如：
ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO(partition s0 values less than(5),partition s1 values less than(10))
或者如：
ALTER TABLE tbl_name REORGANIZE PARTITION p0 INTO(partition s0 values in(1,2,3), partition s1 values in(4,5))
(2) 合并分区如：ALTER TABLE tbl_name REORGANIZE PARTITION s0,s1 INTO(partition p0 values in(1,2,3,4,5))
4. 删除所有分区，但保留数据，形式：ALTER TABLE tbl_name remove partitioning
② HASH和KEY分区的管理
1. 减少分区数量语句如：ALTER TABLE tbl_name COALESCE PARTITION 2
2. 添加分区数量语句如：ALTER TABLE tbl_name add PARTITION partitions 2
③ 其他分区管理语句
1. 重建分区：类似于先删除保存在分区中的所有记录，然后重新插入它们，可用于整理分区碎片。如：ALTER table tbl_name REBUILD PARTITION p2,p3
2. 优化分区：如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB或TEXT类型的列）做了许多修改，可以使用 ALTER TABLE tbl_name OPTIMIZE PARTITION来收回没有使用的空间，并整理分区数据文件的碎片。如：ALTER TABLE tbl_name OPTIMIZE PARTITION p2,p3
3. 分析分区：读取并保存分区的键分布，如：ALTER TABLE tbl_name ANALYZE PARTITION p2,p3
4. 检查分区：检查分区中的数据或索引是否已经被破坏，如：ALTER TABLE tbl_name CHECK PARTITION p2,p3
5. 修补分区：修补被破坏的分区，如：ALTER TABLE tbl_name REPAIR PARTITION p2,p3
十、查看分区信息
1. 查看分区信息：select * from information_schema.partitions where table_schema='arch1' and table_name = 'tbl_test' G
2. 查看分区上的数据：select * from tbl_test partition(p0)
3. 查看MySQL会 *** 作的分区：explain partitions select * from tbl_test where uuid = 2
十一、 局限性
1. 最大分区数目不能超过1024，一般建议对单表的分区数不要超过50个。
2. 如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内。
3. 不支持外键。
4. 不支持全文索引，对分区表的分区键创建索引，那么这个索引也将被分区。
5. 按日期进行分区很合适，因为很多日期函数可以用。但是对字符串来说合适的分区函数不太多。
6. 只有RANGE和LIST分区能进行子分区，HASH和KEY分区不能进行子分区。
7. 临时表不能被分区。
8. 分区表对于单条记录的查询没有优势。
9. 要注意选择分区的成本，没插入一行数据都需要按照表达式筛选插入的分区。
10. 分区字段尽量不要可以为null
Mysql 工作原理图Mysql是由SQL接口，解析器，优化器，缓存，存储引擎组成的。
mysql原理图各个组件说明：
1. connectors
与其他编程语言中的sql 语句进行交互，如php、java等。
2. Management Serveices &Utilities
系统管理和控制工具
3. Connection Pool (连接池)
管理缓冲用户连接，线程处理等需要缓存的需求
4. SQL Interface (SQL接口)
接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
5. Parser （解析器）
SQL命令传递到解析器的时候会被解析器验证和解析。
主要功能：
a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，后面SQL语句的传递和处理就是基于这个结构的
b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的，语句将不会继续执行下去
6. Optimizer (查询优化器)
SQL语句在查询之前会使用查询优化器对查询进行优化(产生多种执行计划,最终数据库会选择最优化的方案去执行,尽快返会结果) 他使用的是“选取-投影-联接”策略进行查询。
用一个例子就可以理解： select uid,name from user where gender = 1
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
将这两个查询条件联接起来生成最终查询结果.
7. Cache和Buffer (查询缓存)
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
8.Engine (存储引擎)
存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。
Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）
SQL 语句执行过程
数据库通常不会被直接使用，而是由其他编程语言通过SQL语句调用mysql，由mysql处理并返回执行结果。那么Mysql接受到SQL语句后，又是如何处理
首先程序的请求会通过mysql的connectors与其进行交互，请求到处后，会暂时存放在连接池（connection pool)中并由处理器（Management Serveices &Utilities）管理。当该请求从等待队列进入到处理队列，管理器会将该请求丢给SQL接口（SQL Interface）。SQL接口接收到请求后，它会将请求进行hash处理并与缓存中的结果进行对比，如果完全匹配则通过缓存直接返回处理结果；否则，需要完整的走一趟流程：
(1)由SQL接口丢给后面的解释器（Parser），解释器会判断SQL语句正确与否，若正确则将其转化为数据结构。
(2)解释器处理完，便来到后面的优化器（Optimizer），它会产生多种执行计划,最终数据库会选择最优化的方案去执行,尽快返会结果。
(3)确定最优执行计划后，SQL语句此时便可以交由存储引擎（Engine）处理，存储引擎将会到后端的存储设备中取得相应的数据，并原路返回给程序。
注意点
(1)如何缓存查询数据
存储引擎处理完数据，并将其返回给程序的同时，它还会将一份数据保留在缓存中，以便更快速的处理下一次相同的请求。具体情况是，mysql会将查询的语句、执行结果等进行hash，并保留在cache中，等待下次查询。
(2)buffer与cache的区别
从mysql原理图可以看到，缓存那里实际上有buffer和cache两个，那它们之间的区别：简单的说就是，buffer是写缓存，cache是读缓存。
(3)如何判断缓存中是否已缓存需要的数据
这里可能有一个误区，觉得处理SQL语句的时候，为了判断是否已缓存查询结果，会将整个流程走一遍，取得执行结果后再与需要的进行对比，看看是否命中，并以此说，既然不管缓存中有没有缓存到查询内容，都要整个流程走一遍，那缓存的优势在哪？
其实并不是这样，在第一次查询后，mysql便将查询语句以及查询结果进行hash处理并保留在缓存中，SQL查询到达之后，对其进行同样的hash处理后，将两个hash值进行对照，如果一样，则命中，从缓存中返回查询结果；否则，需要整个流程走一遍。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）