MySQL的分区分表和SQL性能优化详解
昨天为大家分享了MYSQL优化系列课程,接着给大家分享下个系列的课程内容索引、查询缓存query_cache、分区分表、服务器架构、SQL性能优化系列的课程!
本次教程分享大纲:
1、储存引擎
2、字段类型选择
3、范式与逆范式
4、索引
5、查询缓存query_cache
6、分区分表
7、服务器架构
8、SQL性能优化
1-3节系列的课程,可以通过下方的链接进入观看!
一、索引
1、索引概述
利用关键字,就是记录的部分数据(某个字段,某些字段,某个字段的一部分),建立与记录位置的对应关系,就是索引。索引的关键字一定是排序的。索引本质上是表字段的有序子集,它是提高查询速度最有效的方法。一个没有建立任何索引的表,就相当于一本没有目录的书,在每次查询时就会进行全表扫描,这样会导致查询效率极低、速度也极慢。如果建立索引,那么就好比一本添加的目录,通过目录的指引,迅速翻阅到指定的章节,提升的查询性能,节约了查询资源。
测试查询,添加索引前后比对执行时间:
2、索引种类
从索引的定义方式和用途中来看:主键索引,唯一索引,普通索引,全文索引。
无论任何类型,都是通过建立关键字与位置的对应关系来实现的。索引是通过关键字找对应的记录的地
址。
以上类型的差异:对索引关键字的要求不同。
关键字:记录的部分数据(某个字段,某些字段,某个字段的一部分)。
普通索引,index:对关键字没有要求。唯一索引,unique index:要求关键字不能重复。同时增加唯一
约束。
主键索引,primary key:要求关键字不能重复,也不能为NULL。同时增加主键约束。
全文索引,fulltext key:关键字的来源不是所有字段的数据,而是从字段中提取的特别关键词。
关键字含义:可以是某个字段,也可以是某些字段。如果一个索引通过在多个字段上提取的关键字,称之为复合索引。命令:alter table exp add index (fifield1, fifield2);
PS:这里主键索引和唯一索引的区别在于:主键索引不能为空值,唯一索引允许空值;主键索引在一张表内只能创建一个,唯一索引可以创建多个。主键索引肯定是唯一索引,但唯一索引不一定是主键索引。
3、索引操作
1)使用sql语句的方式建立索引----建表时就创建索引
注意:索引可以起名字,但是主键索引不能起名字,因为一个表仅仅可以有一个主索引,其他索引可以出现多个。名字可以省略,mysql会默认生成,通常使用字段名来充当。
2)使用sql语句的方式建立索引----更新表时创建索引
注意:如果表中存在数据,数据符合唯一或主键的约束才可能创建成功。
3)使用sql语句的方式删除索引。
4、索引原则
如果索引不遵循使用原则,则可能导致索引无效。
1)列独立
如果需要某个字段上使用索引,则需要在字段参与的表达中,保证字段独立在一侧。
第三个语句 empno-1就不是列独立:就不能用索引。其他两个列独立可以使用。
2)左原则
Like:匹配模式必须要左边确定不能以通配符开头。
假如业务逻辑上出现: fifield like%keywork%
’;类似查询,需要使用全文索引。
复合索引:一个索引关联多个字段,仅仅针对左边字段有效果。
示例:添加复合索引
对Ename的查询,使用了索引,结果如下:
Empno的查询没有使用索引,结果如下:
3)OR的使用
必须要保证 OR 两端的条件都存在可以用的索引,该查询才可以使用索引。
为后面的条件增加可以使用的索引后,再查看执行计划:
4)MySQL智能选择
即使满足了上面说原则,MySQL也能弃用索引:如下图
sql查询优化器认为全表扫描效率比索引扫描高,就会使用全表扫描 ,弃用索引。
综上归纳:
a.不要过度索引。索引越多,占用空间越大,反而性能变慢;
b.只对WHERE子句中频繁使用的建立索引;
c.尽可能使用唯一索引,重复值越少,索引效果越强;
d.充分利用左前缀,索引需要使用复合索引,那么应该将WHERE最频繁的放置在左边。
e.索引存在,如果没有满足使用原则,也会导致索引无效:
5、全文索引
该类型的索引特殊在:关键字的创建上。是为了解决 like‘%keyword%’这类查询的匹配问题。(mysql的全文索引几乎不用,因为它不支持中文,我们应该使用sphinx全文索引)。
示例:假如有一张表,表中有标题和内容两个字段,现在要查询标题或者内容包含“database”关键字的记录。
补充:text和varchar的区别是text的数据不存在记录里,一条记录的最大空间是65535.
形成的SQL如下:
Select*from articles where title like‘%database%’or body like‘%database%’;
此时不能建立普通索引,查询不符合左原则,建立了也使用不了。
此时全文索引就可以发挥其作用了:
直接使用上面的SQL,需要使用特殊的全文索引匹配语法才可以生效: Match() against();
Tip: 该MYSQL提供的全文索引,不能对中文起作用!
停止词 in:
in这个词,是不能被全文索引所检索到的。因为in这个词是不可以用在全文索引的关键词里的。
二、查询缓存query_cache
MySQL提供的缓存区,将select的结果,存取起来供二次使用的缓存区域。
未开启前:
两次查询时间消耗一致。
开启查询缓存,通过变量控制:
开启并设置大小:
再次执行查询:
可见,第二次查询,使用了开启的缓存!
注意事项:查询缓存存在判断是严格依赖于select语句本身的:严格保证SQL一致。
如果查询时包含动态数据,则不能被缓存。
一旦开启查询缓存,MySQL会将所有可以被缓存的select语句都缓存。如果存在不想使用缓存的SQL执行,则可以使用 SQL_NO_CACHE语法提示达到目的:
注意:这里的缓存仅当数据表的记录改变时,缓存才会被删除。而不是依靠过期时间的。每次写入操作时,需要将对应表的所有缓存都设置失效。如果缓存较大或碎片很多,则会带来很大消耗。所以查询缓存特别适用于更新频率非常低、查询频率非常高的场景。
三、分区分表
日常开发中我们经常会遇到大表的情况,所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大,导致数据库在查询和插入的时候耗时太长,性能低下,如果涉及联合查询的情况,性能会更加糟糕。分表和表分区的目的就是减少数据库的负担,提高数据库的效率,通常点来讲就是提高表的增删改查效率。
分区,partition,分区是将数据分段划分在多个位置存放,可以是同一块磁盘也可以在不同的机器。分区后,表面上还是一张表,但数据散列到多个位置了。app读写的时候操作的还是大表名字,db自动去组织分区的数据
其实每个分区,就是独立的表。都要存储该分区数据的数据,索引等信息。
创建分区:在创建表时,指定分区的选项:
Create table table_name (定义)
Partition by 分区算法 (参数) 分区选项。
例如:Partition by key (id) partitions 5;
采用key取余算法,根据id的值进行取余,即对5取余,然后分配到5个区里。
分区结果如下:myisam下
Innodb下
Tip:分区与存储引擎无关,是MySQL逻辑层完成的。
可以通过变量查看当前mysql是否支持分区:
1、分区算法
MySQL提供4种分区算法:取余:Key,hash 条件:List,range 。
参与分区的参数字段需要为主键的一部分。
1)KEY–取余 ,按照某个字段进行取余
分成5个区,就是对5取余。将id对5取余。
2)Hash–取余,按照某个表达式的值进行取余
示例:学生表分区,按照生日的月份,划分到12个表中。
注意:Key,hash都是取余算法,要求分区参数(括号里的),返回的数据必须为整数。
3)List–条件–列表,需要指定的每个分区数据的存储条件。
示例:按照生日中的月份,分成春夏秋冬四个分区。
List,条件依赖的数据是列表形式。
4)Range-条件–范围, 条件依赖的数据是一个条件表达式。
逻辑:按照生日的年份分成不同的年龄段。
2、分区的管理与选择
1)取余:key,hash
增加分区数量: add partition partitions N
减少分区数量: COALESCE partition N
采用取余算法的分区数量的修改,不会导致已有分区数据的丢失,因为会重新分配数据到新的分区。
2)条件:list,range
添加分区
删除分区:
Drop partition partition_name;
注意:删除条件算法的分区,会导致分区数据丢失。添加分区不会。
3)选择分区算法
平均分配:就按照主键进行key(primary key)即可(非常常见)
按照某种业务逻辑分区:选择那种最容易被筛选的字段,整数型
3、分表
分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表,我们可以称为子表,每个表都对应三个文件,MYD数据文件,.MYI索引文件,.frm表结构文件。这些子表可以分布在同一块磁盘上,也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名,然后去操作它。分表技术是比较麻烦的,需要手动去创建子表,app服务端读写时候需要计算子表名。
分表是分区之前用的,MYSQL5.1后,就开始用分区代替分表了。分表很少用了。
四、服务器架构介绍
服务器架构,不仅仅是用一台MySQL
主从复制:
Mysql服务器内部支持复制功能,仅仅需要通过配置完成下面的拓扑结构。一主多从典型结果:主服务器负责写数据。从服务器负责读数据。复制功能mysql会自带。
读写分离,负载均衡:
程序不再操作MYSQL数据库服务器,而是去操作读写分离、负载均衡服务器,只要服务器安装mysqlproxy或Ameoba软件就可以实现读写分离和负载均衡,读写分离是指该服务器会判断客户端操作是读还是写,从而选择操作mysql主服务器还是从服务器。负载均衡算法是指,客户端读操作时,该服务器会根据取余算法去选择一台从服务器。
同时,服务器架构需要保证,高可用(稳定),7x24不宕机。因此需要增加一些冗余服务器以便备用。时时检测正在用的服务器。
五、SQL优化
1.对于并发性的SQL
少用(不用)多表操作(子查询,联合查询),而是将复杂的SQL拆分多次执行。如果查询很原子(很
小),会增加查询缓存的利用率。
2.大量数据的插入
多条 insert或者Load data into table(从文件里载入数据到表里)
建议,先关闭约束及索引,完成数据插入,再重新生成索引及约束。
针对于myisam,步骤:
Alter table table_name disable keys; 禁用索引约束
大量的插入
Alter table table_name enable keys; 启用
针对innodb,步骤:
Drop index, drop constraint 删除索引及约束,要保留主键
Begin transaction|set autocommit=0; 开启事务,不让他自动提交
[数据本身已经按照主键值排序]
大量的插入
Commit;
Add index, add constraint
3.分页
分页假定Limit offffset, size; size=10;
Limit 的使用,会大大提升无效数据的检索(被跳过),因为是先检索,检索会检索全部,再取得想要的。
4.需要随机选一些数据的时候,不要使用Order by Rand()
上面的查询,会导致每条记录都执行rand(),成本很高!
可以在表中添加一个存随机数的列,添加数据的时候给这个列赋值
INSERT INTO TB(tbfifiled1,tbfifiled2,tbfifiledrand) VALUES('33','dd',rand())tbfifiledrand 也可以在程序中直接赋值。
相关推荐:
1、
2、
3、
4、
回复关键词
Redis 分布式限流 消息队列 alibaba JVM性能调优
看更多精彩教程
喜欢本文,记得点击个在看,或者分享给朋友哦!