mysql的优化

最新推荐文章于 2023-07-31 23:13:38 发布

一腔&孤勇

最新推荐文章于 2023-07-31 23:13:38 发布

阅读量86

点赞数

分类专栏： mysql 数据库

本文链接：https://blog.csdn.net/qq_38052995/article/details/82972299

版权

mysql 同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

数据库

6 篇文章 0 订阅

订阅专栏

建表时列的选择

数据类型

尽量使用定长的数据,可以使查询速度变快

因为使用定长的数据可以更方便的在磁盘上找到相关的数据

数据类型优先级

int>time,date >enum,char>varchar>text,blob

int

全世界通用,没有字符集和校对集的区别

time date

实际int类型,尽量使用time时间戳,可以省去时区方面的麻烦

enum char

enum 枚举类型内部实际int型,enum转char时需要进行串与数据的转发所以较慢
char 定长的字符串,使运算速度更快

避免使用null

null不利于索引,且占用内存更大

数据分离

将常用的数据与不常用的列进行分离

添加一些必要冗余

例如博客系统的数据库设计,可以在用户表加上一列已发表文章数,这样在查询该用户的文章发布数时,可以减少一次数据的级联查询,当前硬件成本较低,不用死扣范式,采用空间换时间可能使用户体验更好

索引的介绍

索引的作用

1.提高查询的速度
2.提高排序的速度
3.提高分组的速度

bTree类型的索引

内部实际采用二叉树的数据结构,例如

              4
	2           6
     1    3      5     7

采用类似的数据结构将数据的查询时间复杂度从N/2降低到log2N

hash类型的索引

采用hash算法,将每一个主键进行自定义的hash函数从而直接得到数据存储的地址,因为是直接进行运算得到的地址,不需要进行查找,所以查询时间复杂度为(

优点
快(适用于精准查询)

缺点

1.因为是随机放置数据,所以容易引起内存空洞
2.因为是对某一个主键进行hash函数的运算,可能在精准查询的时候很快,但是碰到范围查询时不能进行优化,例如where id>20
3.无法使用前缀索引(具体定义见下文),而bTree可是使用前缀索引
4.排序无法优化
5.必须进行回行,即hash只能得到地址,所以需要进一步根据地址去取值(bTree的聚簇索引不需要)

索引左前缀

建立一个多列索引 a,b,c

语句	是否发挥作用
where a = 1	a 发挥作用
where a= 1 and b = 2	a,b发挥作用
where a= 1 and b = 2 and c = 3	a,b,c发挥作用
where b = 1 / where c= 1	无
where a= 1 and c = 1	a发挥作用
where a= 1 and b > 10 and c = 1	a,b发挥作用
where a= 1 and b like “123%”	a,b发挥作用
where a= 1 and b like “%123”	a发挥作用

因为多列索引具有顺序性和左前缀性
例如 a,b,c是一根完整的管道,当没有b时只有a能用,c是断的所以不能用
当只有b,c或者只有b只有c时,因为坐前缀性,因为左边没有a所以整根管道处于断路,都不能用
当b为"123%“时,因为左边为确认值,所以b能部分使用,但是处于b后面的都不能用
当b为”%123"时,因为左边为不确认值,所以b不能使用,所以b以及处于b后面的都不能用

经典索引题
现有a,b,c,d四列,将a,b,c三列建立索引

语句	是否发挥作用	备注
where a =1 and b = 1 and d >8 and c = 1	a b c都能起作用	虽然语义上不符左前缀,但是mysql会自动优化
where a =1 and b = 1 and d >8 order by c	a b c都能起作用	ab作用于查询,c用于排序
where a =1 and d >8 order by c,b	a都能起作用	a作用于查询,bc虽然建立索引但是顺序不符合左前缀
where a =1 and d >8 order by b,c	a,b,c都能起作用	a作用于查询,bc用于排序
where a =1 and b = 2 order by b,c	a,b,c都能起作用	a,b作用于查询,c用于排序

二级索引

一般为多列的索引,其无论主键索引是聚簇索引还是非聚簇索引,其存放的数据仅为指向主键的位置的指针,即指向主键

索引覆盖

当查询的内容就是索引的一部分时,就不会产生回行现象,查找速度较快
例如表test建立了a,b的联合索引
select a from test where a = 1 and b = 3;(快)
select c from test where a = 1 and b = 3;(慢)

聚簇索引(innerDb)

虽然采用二叉树的数据结构,但是叶子不仅保存数据的地址还保存实际数据

                                    4[索引|实际数据] 
	        2[索引|实际数据]                        6[索引|实际数据] 
     1[索引|实际数据]      3[索引|实际数据]      5[索引|实际数据]      7[索引|实际数据]

优点
1.查询速度较快

因为索引与数据存放在一起,所以不存在回行现象,所以查询速度较快

2.排序速度较快

因为索引与数据存放在一起,所以不存在回行现象,所以可以避免fielsort操作,排序速度较快

2.当乱序插入数据的时候,结果依然有序(非聚簇索引不一定)

当聚簇索引插入数据,当生成索引树的时候,因为聚簇索引索引与数据存放在一起,因为索引是根据二叉树有序的,所以数据也是有序的,而非聚簇索引,因为索引树只存放相关实际数据的地址,所以乱序存放时并不会自动排序

缺点
当插入顺序不规律时,会形成页分裂现象,速度会比非聚簇索引稍慢

因为叶子存储索引与所有的数据,所以叶子较重,当插入顺序不规律时,因为要生成二叉树时,因为叶子较重,速度会比较慢(使用固态硬盘会有所缓解)

经典样例
例如一个innerdb的表test,定义了,a,b,c三列,其中c为text的超长列,a为主键,再定义a,b为一个索引
select * from test order by a;(很慢)
select * from test order by a ,b;(很快)

关键原因如下
1.存储引擎为innerdb(使用聚簇索引),如果使用非聚簇索引则查询速度一致
2.使用的索引为主键索引,因为有大数据列存在所以,主键索引树较重所以速度较慢
3.有大数据列c存在,如果没有大数据列,查询速度相差不大

虽然a建立了主键索引,但是因为是聚簇索引,所以主键a会与大数据c一起放在一棵索引树内,会非常重,而二级索引a,b则是另一个索引树,存放数据仅为主键的地址,所以查询速度会比主键还快,所以建议合理建立索引,合理使用索引

非聚簇索引(myisam)

虽然采用二叉树的数据结构,但是叶子不仅保存数据的地址还保存实际数据
内部实际采用二叉树的数据结构,例如

                         4[索引]
	        2[索引]             6[索引] 
     1[索引]      3[索引]      5[索引]      7[索引]

利用索引进行排序

一.排序的数据是索引的一部分
发生索引覆盖,速度很快

二.排序的数据与是索引无关
这个时候mysql会将数据取出形成一个临时表,然后进行filesort(可能会在磁盘上进行)

在非聚簇索引中,虽然可以根据有序的索引去一边取索引一边取数据,这样取出的数据虽然也是有序的.
但是因为频繁的回行效率很低,所以非聚簇索引的数据库引擎会先把所有的数据取出然后对数据直接进行排序,
因为可能会在磁盘上进行,所以速度可想而知,我们应该尽可能的去避免filesort

我们需要争取去数据库取出的数据就是有序的(利用索引进行排序)
坑
当sql语句的查询操作使用的索引与排序操作的索引不一致时,虽然两个操作都使用了索引,但是还是会产生filesort,
例,test表有cat_id与good_id这两个单列索引
select * from test where cat_id > 10 order by good_id;(使用filesort)
select * from test where good_id > 10 order by good_id;(未使用filesort)

因为当查询操作使用的索引与排序操作的索引不一致时,根据where查询出来的语句对order使用的索引依然是无序的,所以还是会产生filesort

建立索引

常见误区

1.所有的常用列都建立索引(索引越多越好)

因为建立的是独立索引,所以实际只有一个索引发挥作用,建议建立多列索引

2.建立多列索引,当查询某一列时索引都会产生作用,

多列索引的使用需要满足左前缀原则

3.重复索引与冗余索引,

有时候为了某些查询频繁的表的一些特殊业务请求,可以多设计一些冗余索引使查询速度更快,但是,不是重复索引
例如以下的三个索引
一:类别,品牌
二:类别,价格
三:类别,品牌,价格
其中索引一与索引三重复(左前缀原则),索引二与索引三冗余,所以我们应该保存冗余索引,去除重复索引,结果为
一:类别,价格
二:类别,品牌,价格

建立原则

1.在使用频繁的列上建立索引
2.区分度高

例如一张用户表,在性别上建立索引则对应了近50%的数据,意义不大
在年龄上建立索引每一个年龄也对应太多数据,意义不大
应该在身份证号上建立索引,没一个索引对应一条数据,

3.长度尽可能短

例如有一张中国常用词语表,在这张表上面建立索引
当我们以常用词语的第N个字作为索引时,索引效率如下

选取的字的个数	效率
1	0.0291
2	0.5865
3	0.9449
4	0.9876
5	0.9982
6	0.9998

此时我们选择3 - 4个字作为索引时较优

多列索引的建立原则

1.列的使用频率
2.列的区分度
3.列的查询顺序(左前缀原则)

例如去京东购买一台笔记本,
第一步: 登陆京东首页,京东不会让你去根据价格筛选等等,而是让你去选择商品类别(电子产品,文具用品,母婴用品等)
第二步: 笔记本中去选择相应的笔记本品牌
第三步: 才会让你根据价格,销量等去筛选

据此我们建立多列索引时,第一步确定需要索引的列,商品类别,商品品牌,商品价格等(第一二条原则),确定后,根据业务查询的流程,以及客户的购买习惯,我们可以建立索引如下(顺序不能错)(第三条原则)
一:类别,品牌
二:类别,价格
三:类别,品牌,价格
因为第一条索引与第三条索引的前半部分完全一样,所以删除第一条(左前缀原则),所以最终设计得到索引为
一:类别,价格
二:类别,品牌,价格