设计层优化主要包括:索引的使用、分表分区等。
目录
一、索引
索引的基本概念:
索引是数据结构+算法,是对数据表中一列或多列(字段)的值进行排序的一种结构,结构支撑一定的算法,可以保证数据被快速检索。
Mysql的索引数据结构都是B+tree(balance平衡查找树)结构,(可以了解相关的数据结构例如B-tree、二叉树、Binary-tree等)。
B-tree索引:
抽象来看,B-tree结构,可理解为”排好序的快速查找结构”。名叫btree索引,用的也是平衡树,但具体的实现上, 各引擎稍有不同,比如,严格来讲:NDB引擎,使用的是T-tree,Myisam、innodb中默认用B-tree索引。
hash索引:
在memory表里,默认是hash索引,hash的理论查询时间复杂度为O(1),查找非常高效,但是也不是都用hash索引,原因主要有:
① hash函数计算后的结果,是随机的,如果是在磁盘上放置数据的话,比如主键为id为例,那么随着id的增长,id对应的行会在磁盘上随机放置,越来越不好。
③ 无法利用前缀索引。比如在btree中,Col1列的值“helloworld”,并加上索引,查询xx=helloword,自然可以利用索引,xx=hello,也可以利用索引. (左前缀索引),但是hash索引不行,因为hash(‘helloword’)和hash(‘hello’)两者的关系仍为随机。
⑤ 必须回行,也就是说通过索引拿到数据位置,必须回到表中取数据。
使用索引好处:
在现实生活中有许多地方使用到索引,例如:公交车站牌、书的目录、办公楼指示牌、查询汉字所在页码等。
索引速度快的原因:
在没有索引的时候,sql语句查询会遍历全部的记录信息,找到与条件相符合的。
在有索引的时候是精准定位指定的记录信息,sql语句做数据查询的时候,要通过索引做条件,在索引里边可以通过算法快速、准确地获得记录索引字段及字段对应的物理地址,再通过物理地址去数据表中获得记录的详细信息。
索引类型:
① 主键索引(值不重复,auto_increment自增特性)
索引的数据结构:
Myisam索引数据结构:
Myisam的数据和索引是分离的,数据结构为“非聚集型”,如下图:
Myisam主键索引数据结构原理:
数据表有3个字段:Col1、Col2、Col3,其中Col1是主键:
节点:根节点、分支节点、叶子节点,索引字段内容根据算法存储在各个节点中。
叶子节点:字段内容+记录物理地址,物理地址与数据记录的物理地址一一对应。
空白指针:记录着下个节点的位置,通过算法寻找、指引到下个节点。
高度:索引结构从上到下的层数,高度不要过高或过低,最好趋向“正三角形”,有利于数据快速查找。
索引字段内容 --------> 记录物理地址--------> 具体记录信息
Myisam非主键索引数据结构原理:
Innodb索引数据结构:
Innodb索引结构称为“聚合型”,“索引”和”数据”是合并在一个文件里边的。
innodb的主索引文件上直接存放该行数据,称为聚簇索引,次索引指向对主键的引用。
Innodb主键索引数据结构原理:
主键索引既存储索引值,又在叶子中存储行的数据。像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为“聚簇索引”。
这个表如果没有主键,则会unique key做主键 ,如果又没有unique key,则系统生成一个内部的rowid做主键。
Innodb非主键索引数据结构原理:
索引字段内容 ---> 记录主键id值 --->(主键索引)---> 记录信息
innodb索引里边没有物理地址,非主键索引需要借助主键索引找到数据记录,所以:
聚簇索引的优劣:
优势:根据主键查询条目比较少时,不用回行(数据就在主键节点下)。
聚簇索引的主键值,应尽量是连续增长的值,而不是要是随机值 (不要用随机字符串或UUID) ,否则会造成大量的页分裂与页移动。
索引的业务设计依据:
sql语句中,被频繁用在where和order等条件里的字段适合用来创建索引。数据表创建完毕,预估哪些字段会被经常使用,可以给其创建索引。
select * from 表名 where id > 5; //不会使用索引,记录范围大
select * from 表名 where id < 5; //会使用索引
5.sql语句里边有in条件,有时候可以给in条件的字段设计索引:
创建索引:
1.创建表的同时创建索引:
create table 表名(
...
primary key (字段),
unique key [索引名称] (字段),
key [索引名称] (字段),
fulltext key [索引名称] (字段)
除了主键索引,其他索引设置的同时可以给其起一个“索引名称”,名称不设置的话会与该索引字段名称一致,如果索引名称叠加(重复)则会自动额外设置序号。
2.给已经创建过了的表增加索引:
alter table 表名 add primary key (字段);
alter table 表名 add unique key [索引名称] (字段);
alter table 表名 add key [索引名称] (字段);
alter table 表名 add fulltext key [索引名称] (字段);
首先创建一个没有任何索引的数据表,也不要给id设置auto_increment属性:
索引简介:
前缀索引简介:
索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多)。前缀索引是通过字段前N位创建的索引。
如果一个字段内容的前边的n位信息已经足够标识当前的字段内容,就可以把字段的前N位获得出来并创建索引,该索引占据更空间更小、运行速度更快。
前缀索引制作:
alter table 表名 add key (字段(前n位位数)) ;
② 截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大--增删改变慢,并间影响查询速度.
④ 取字段的前(n)1、2、3.....位不重复的信息并计算总数目,n从1开始不断累加,直到总数目 与 ①计算的总数目相等,此时N就是我们设计前缀索引的数字N信息。
mysql> select count(distinct left(word,6))/count(*) from dict;
+---------------------------------------+
| count(distinct left(word,6))/count(*) |
+---------------------------------------+
+---------------------------------------+1 row in set (0.30 sec)
对于一般的系统应用来说,区别度能达到0.1,索引的性能就可以接受。
全文索引:
其他索引是把字段的内容作为一个整体进行索引设计,而全文索引会把文章的各个关键单词获得出来,制作为索引内容。Mysql5.6.4之前只有Myisam支持全文索引,之后 Myisam和Innodb都支持,类似我们有一篇作文,把作文中的一些关键字给获取出来当成是索引内容。
目前Mysql中只有英文支持全文索引,要使用支持中文的全文索引一般都是借助第三方软件sphinx,一般使用全文索引也不使用Mysql本身的。
举例使用fulltext全文索引:
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
)engine=myisam charset utf8;
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
alter table articles add fulltext key (title);
select * from articles where match(字段) against(模糊内容); 不正确:
select * from articles where 字段 like ‘%模糊内容%’;
mysql本身的全文索引在做模糊查询的时候会有“自身的考虑”,把一些不常见的特殊内容给设计为索引内容,一些生活等常见用语(what where how等等)就不会设计为索引内容。
复合索引:
以ecshop商城为例,goods表中的cat_id,brand_id做多列索引,从区分度看,brand_id区分度更高:
mysql>select count(distinct cat_id) / count(*) from goods;
+-----------------------------------+
| count(distinct cat_id) / count(*) |
+-----------------------------------+
+-----------------------------------+
mysql>select count(distinct brand_id) / count(*) from goods;
+-------------------------------------+
| count(distinct brand_id) / count(*) |
+-------------------------------------+
+-------------------------------------+
但从实际业务业务看,客户一般先选大分类再选小分类最后是品牌。最终选择index(cat_id,brand_id)来建立索引。
[primary/unique] key [索引名称] (字段1,字段2,字段3...)
多个字段组合设置索引,主键、唯一、普通索引的都可以设置为复合索引,如果不指明索引名称,就把索引的第一个字段获得出来当做索引名称使用。
现在再给name做一个唯一索引,为了让复合索引的name名称与普通索引的name名称起冲突,我们不设置索引名称。
结果没有冲突,系统把第二个name设置了一个“_2”的序号。
删除索引:
1.删除非主键索引(唯一、普通、全文、复合索引)
2.删除主键索引
alter table 表名 drop primary key;
注:如果有“auto_increment”属性,要先除去“auto_increment”属性。
alter table student4 modify id int unsigned not null auto_increment comment '主键id';
alter table student4 modify id int unsigned not null comment '主键id';
主键和auto_increment属性的设置顺序:
在auto_increment存在的情形下删除主键,系统会报错:
索引覆盖:
索引覆盖是指如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据。这种查询速度就非常快,称为”索引覆盖”。
1.推断出是innodb引擎,物理行在叶子上,如果是mysam的话,物理行在磁盘上,查找不会这么慢,那应该不会出现这个问题。
2.有比较长的字段列,块比较多,id主键是聚簇索引,导致沿id查找时要跨许多小块。
看上面的sql语句,没有使用where、也没有使用order,只有ename的复合索引可用,并且查询的字段(ename,deptno)与ename复合索引字段(ename,deptno)完全一致,此时就把当前使用索引的情形称为是“索引覆盖”,也可以称为“黄金索引”,在索引里边就已经可以获得需要的信息(不需要去数据表里边做二次记录查询了)。
索引使用原则:
2.1左原则:
例如“like”模糊查询,左边内容固定,右边匹配模糊内容的模式就是——左原则。
like '北京_' //“北京”内容右边只出现一个模糊内容
2.2对于左前缀不易区分的列,建立索引的技巧:
列的前11个字符都是一样的,不易区分,此时可以用以下两个办法来解决:
3.复合索引(2个字段)
③单独使用其他字段(复合索引的非第一个字段)作为条件,没有索引。
上图,deptno本身是一个普通的索引,但是当前情况也没有用到该索引为什么呢?
因为是deptno>200的条件获得数据记录太多了,不会用到索引要想用到deptno的普通索引,可以把条件设置获取的记录的少一些(例如deptno>20000)。
4.or原则
btree索引的常见误区:
1.在where条件用的列上都加上索引:
因为只能用上cat_id或price索引其中一个索引,因为是独立的索引,同时只能用上1个。
2.在多列上建立索引后,查询哪个列,索引都将发挥作用:
SQL语句 | 索引是否发挥作用 |
where a=3 | 是,只使用了a列 |
where a=3 and b=5 | 是,使用了a,b列 |
where a=3 and b=5 and c=4 | 是,使用了a,b,c |
where b=3 或 where c=4 | 否 |
where a=3 and c=4 | a列能发挥索引,c不能 |
where a=3 and b>10 and c=7 | a能利用,b能利用,c不能利用 |
where a=3 and b like ‘xxxx%’ and c=7 | a能利用,b能利用,c不能利用 |
假设某一个表有一个联合索引(c1,c2,c3,c4),以下只能使用该联合索引的c1,c2,c3部分的语句为哪个?
A. where c1=x and c2=x and c4>x and c3=x
B. where c1=x and c2=x and c4=x order by c3
C. where c1=x and c4=x group by c3,c2
D. where c1=x and c5=x order by c2,c3
E. where c1=x and c2=x and c5=? order by c2,c3
create table t4 (
c1 tinyint(1) not null default 0,
c2 tinyint(1) not null default 0,
c3 tinyint(1) not null default 0,
c4 tinyint(1) not null default 0,
c5 tinyint(1) not null default 0,
index c1234(c1,c2,c3,c4)
);
insert into t4 values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);
c1=x and c2=x and c4>x and c3=x 等价于 c1=x and c2=x and c3=x and c4>x
mysql>explain select * from t4 where c1=1 and c2=2 and c4>3 and c3=3 \G
***************************1. row ***************************
key_len: 4 #可以看出c1,c2,c3,c4索引都用上
select * from t4 where c1=1 and c2=2 and c4=3 order by c3
c1 ,c2索引用上了,在c2用到索引的基础上,c3是排好序的,因此不用额外排序,而c4没发挥作用。
mysql>explain select * from t4 where c1=1 and c2=2 and c4=3 order by c3 \G
***************************1. row ***************************
mysql>explain select * from t4 where c1=1 and c2=2 and c4=3 order by c5 \G
***************************1. row ***************************
Extra: Using where; Using filesort
只用到c1索引,因为group by c3,c2的顺序无法利用c2,c3索引
mysql>explain select * from t4 where c1=1 and c4=2 group by c3,c2 \G
***************************1. row ***************************
key_len: 1 #只用到c1,因为先用c3后用c2分组,导致c2,c3索引没发挥作用
Extra: Using where; Using temporary; Using filesort
mysql>explain select * from t4 where c1=1 and c4=2 group by c2,c3 \G
***************************1. row ***************************
c1确定的基础上,c2是有序的,c2之下c3是有序的,因此c2,c3发挥的排序的作用,不会用到filesort。
mysql>explain select * from t4 where c1=1 and c5=2 order by c2,c3 \G
***************************1. row ***************************
它等价与select * from t4 where c1=1 and c2=3 and c5=2 order by c3; 因为c2的值既是固定的,所以参与排序时并不考虑。
mysql>explain select * from t4 where c1=1 and c2=3 and c5=2 order by c2,c3 \G
***************************1. row ***************************
有商品表,有主键goods_id,栏目列cat_id,价格列price,在价格列上已经加了索引,但按价格查询还是很慢,问可能是什么原因,怎么解决?
在实际场景中,一个电商网站的商品分类很多,直接在所有商品中,按价格查商品,是极少的,一般客户都来到分类下,然后再查价格。
去掉单独的price列的索引,加 (cat_id,price)复合索引再使用。
索引与排序:
1.对于覆盖索引,直接在索引上查询时,就是有顺序的:using index。
2.先取出数据,形成临时表做filesort(文件排序,但文件可能在磁盘上,也可能在内存中)。
在实际项目中,目标就是取出来的数据本身就是有序的!利用索引来排序。
goods商品表,(cat_id,shop_price)组成联合索引,where cat_id=N order by shop_price,可以利用索引来排序。
select goods_id,cat_id,shop_price from goods order by shop_price;(// using where),按照shop_price索引取出的结果,本身就是有序的。
select goods_id,cat_id,shop_price from goods order by click_count;(// using filesort)用到了文件排序,即取出的结果需要再次排序。
重复索引与冗余索引:
重复索引:
是指在同1个列(如age),或者顺序相同的几个列(age,school),建立了多个索引,这种就是称之为重复索引,重复索引没有任何帮助,只会增大索引文件,拖慢更新(增删改)速度。
冗余索引:
比如 x,m,列,加索引index x(x),index xm(x,m)两索引,两者的x列重叠了,这种情况称为冗余索引。甚至可以把 index mx(m,x) 索引也建立,mx与xm也不是重复的,列的顺序不一样。
索引碎片与维护:
在长期的数据更改过程中,索引文件和数据文件,都将产生空洞,形成碎片。我们可以通过一个nop操作(不产生对数据实质影响的操作),来修改表。
表的引擎本来就为innodb ,可以再alter table xxx engine innodb,此时不会更改数据,但是会整理数据。
修复表的数据及索引碎片,就会把所有的数据文件重新整理一遍,使之对齐。这个过程如果表的行数比较大,也是非常耗费资源的操作。所以,不能频繁的修复。
如果表的Update操作很频率,可以按周/月来修复,如果不频繁可以更长的周期来做修复。
二、分表设计
一个表里存储太多的记录的话会导致该表的活性大大降低,表的运行速度就会比较慢、效率也比较低,进而影响到Mysql数据库的整体性能。这时候可以采取分表设计,把记录信息平均分摊给各个子表来解决上面的问题。
分表设计的两种方式:
逻辑分表是Mysql本身就支持的技术,使用者不需要考虑记录在哪个数据表存储。
1.逻辑分表(分区):
通过mysql自带的的分区功能,mysql将会根据指定的规则,把数据放在不同的表文件上,相当于在文件上被拆成了小块,但是给使用者的界面还是1张表。
分区常用的规则:
key分表:
分表设计好后,可以简单(非严格)理解为通过“求余”算法把记录写到各个分表中。例如下图分表字段id对分表数量5,进行求余然后填充数据记录信息到对应分区。
partition by key(分表字段) partitions 分表数量;
hash分表:
partition by hash(表达式/字段) partitions 数量;
range分表:
该分表与业务联系非常紧密,数据可以根据分表算法严格填充到分表中。分表字段可以是表达式也可以是独立字段。
partition 名称 values less than (常量),
partition 名称 values less than (常量),
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than MAXVALUE
(分表为innodb存储引擎)需要注意的是,每个innodb数据表有独立的数据文件,如下图:
(set global innodb_file_per_table=1; //设置innodb独立数据文件,但是不建议使用)
list分表:
该分表与业务联系非常紧密,数据可以根据分表算法严格填充到分表中,创建分表的内容可以是表达式也可以是独立字段。
partition 名称 values in (列表范围),
partition 名称 values in (列表范围),
2.分表管理(增加、减少):
删除分表:
①在key/hash领域不会造成数据丢失(删除分表后数据会重新整合到剩余的分表去)。
alter table 表名 coalesce partition 数量;
alter table 表名 drop partition 分表名称;
增加分表:
alter table 表名 add partition partitions 数量;
partition 名称 values less than (常量)
2.物理分表:
物理方式分表设计是自己手动创建多个数据表出来,然后例如PHP等程序需要考虑分表算法:数据往哪个表写,从哪个表读等。
3.垂直分表:
实际项目中,一个数据表设计好了,里边有许多字段,但是这些字段有的是经常使用的,有的是不常用的。在进行正常数据表操作的时候,不常用的字段也会占据一定的资源,对整体操作的性能造成一定的干扰、影响。为了减少资源的开销、提升运行效率,就可以把不常用的字段给创建到一个专门的辅表中去。而这种同一个业务表的不同字段分别存储到不同数据表的过程就是“垂直分表”。
垂直分表地制作:
下面把会员表根据字段的常用程度而分为两个表的过程就是垂直分表:
例如会员数据表有如下字段:
user_id 登录名 密码 邮箱 手机号码 身高 体重 性别 家庭地址 身份证号码
为了使得常用字段运行速度更快、效率更高,把常用字段给调出来,因此数据表做以下垂直分表设计:
会员表(主)user字段:user_id 登录名 密码 邮箱 手机号码