MySQL数据库优化

冷不防 2022-07-12 11:45 244阅读 0赞

可以从几个方面进行数据库优化

![Center][]

SQL及索引优化---索引

如何选择合适的列建索引？

1、在where从句，group by从句，order by从句，on从句中出现的列

2、索引字段越小越好

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

select \* from payment where staff\_id = 2 and customer\_id= 584;

是index(staff\_id,customer\_id)好还是index(customer\_id,staff\_id)好？

由于customer\_id的离散度更大，所以应该使用index(customer\_id，staff\_id)

通过唯一值来判断哪个离散度比较高,唯一值越多，离散度更大

select coutn（distinct customer\_id）,count(distinct staff\_id)

索引的维护及优化-----重复及冗余索引

重复索引是指相同的列以相同的顺序建立的同类型的索引，如下表中primary key和ID列上的索引就是重复索引

create table test(

id int not null primary key,

name vaechar(10) not null,

title varchar(50) not null,

unique(id)

)engine=innodb

冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引，下面这个例子中key(name,id)就是一个冗余索引

create table test(

id int not null primary key,

name varchar(10) not null,

title varchar(50) not null,

key(name,id)

)engine=innodb,每个索引都会包含主键

过多的索引不但会印象我们插入的效率还会影响我们查询的效率，因为数据库再进行查询的时候首先会分析选择哪个索引，如果索引越多，那么分析的过程就会越慢，这样同样会影响查询的效率。

如何找到重复及冗余索引

![Center 1][]

索引维护及优化---删除不用索引

目前MySQL中还没有记录索引的使用情况，但是在PerconMySQL和MariaDB中可以通过INDEX\_STATISTICS表来查看哪些索引未使用，但在MySQL中目前只能通过慢查日志配合pt-index-usage工具来进行索引使用情况的分析

![Center 2][]

数据库结构优化

选择合适的数据类型

数据类型的选择，重点在于合适二字，如何确定选择的数据类型是否合适

1、使用可以存下你的数据的最小数据类型

2、使用简单的数据类型。Int要比varchar类型在mysql处理上简单。

3、尽可能地使用not null定义字段

4、尽量少用text类型，非用不可时最好考虑分表

选择合适的数据类型

使用Int来存储日期时间，利用FROM\_UNIXTIME(),UNIX\_TIMESTAMP()俩个函数进行转换

create Table test(id int AUTO\_INCREMWNT not null,timestr int,primary key(id));

insert into test(timestr) values(UNIX\_TIMESTAMP('2014-06-01 13:12:00'));

select FROM\_UNIXTIME(timestr) FROM test;

使用bigint来存储IP地址，利用INET\_ATON()，INET\_NTOA()俩个函数来进行转化

create table sessions(id int auto\_increment not null , ipaddress bigint,primary key(id));

insert into sessions(ipaddress) value (inet\_aton('192.168.0.1'));

select inet\_ntoa(ipaddress) from sessions;

表的范式化和反范式化

范式化是指数据库设计的规范，目前说到范式化一般是指第三设计范式，也就是要求表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式

![Center 3][]

存在以下传递函数依赖关系：

（商品名称）->（分类）->(分类描述)

也就是说存在非关键字段“分类描述”对关键字“商品名称”的传递函数依赖

不符合第三范式存在的问题

1、数据冗余：（分类，分类描述）对于每一个商品都会进行记录，

2、数据的插入异常

3、数据的更新异常

4、数据的删除异常

将表进行拆分解决该问题

![Center 4][]

范式化的缺点。查询时容易关联很多表，影响时间效率，而反范式化就是以空间来换取时间

![Center 5][]

![Center 6][]

将表结构反范式化

![Center 7][]

![Center 8][]

表的垂直拆分

所谓的垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。通常垂直拆分可以按以下原则进行。

1、把不常用的字段单独存放带一个表中

2、把大字段独立存放到一个表中

3、把经常一起使用的字段放到一起

表的水平拆分

表的水平拆主要是为了解决表单的数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的。

常用的水平拆分方法为：

1、对customer\_id进行hash运算，如果要拆分成5个表则使用mod(customer\_id,5)取出0-4个值

2、针对不同的hashID把数据存到不同的表中。

面临的问题

1、跨分区表进行数据查询

2、统计及后台报表操作

[Center]: /images/20220710/aa5ee4f992ec48fcb5e5207a9c44c749.png
[Center 1]: /images/20220710/878c2cf1c2b34d0ea97198cc278a476c.png
[Center 2]: /images/20220710/4a8377395aaa407f87bd1150220f3084.png
[Center 3]: /images/20220710/b28819956da848b59e7fcea976bd20c9.png
[Center 4]: /images/20220710/8362a02124f245a5b314f01bf4bf15fc.png
[Center 5]: /images/20220710/e56cc102d06b4203bde4a7708f4516b8.png
[Center 6]: /images/20220710/03b94d7a82f840cb95746fdb906198be.png
[Center 7]: /images/20220710/e80a042e45ef4f96994b404c829a78d6.png
[Center 8]: /images/20220710/f10620a8268e4fa191ae38c0005fff4c.png