MYSQL优化

类型选择

一般是按 int > date ,time > enum ,char > varchar > blob , text      进行选择 。

类型优劣说明:

int : 定长,没有国家、地区之分,没有字符集(考虑字符集与校对集)的差异。

time :定长,运算快,节省空间,考虑时区。

enum:整形,定长,能起到约束类型的作用,内部实际是用整形存储,但与char联合查询时,内部要经历值得转化。

char:定长,考虑字符集与校对集。

varchar:不定长,要考虑字符集的转换和校对集的排序,速度不较慢。

text/blob:无法使用内存临时表(排序操作只能在磁盘上进行)。

 

尽量避免使用 null :null不利于索引,要用特殊的字节标注,实际上在磁盘上占据的空间更大。

enum类型说明:

enum列在内部用整形存储

enum与enum相关查询最快

enum列比(var)char弱势——在碰到与(var)cahr查询相关联时,enum要转化,耗时。

enum优势在于,当(var)char数据比较大时,enum任然是整形定长。

索引优化策略——提高查询、分组、排序速度

索引优化策略

索引类型分类

B-tree索引:即是Balance-tree,(多路搜索树,并不是二叉的)是一种常见的数据结构。使用B-tree结构可以显著减少定位记录时所经历的中间过程,从而加快存取速度。这个数据结构一般用于数据库的索引,综合效率较高。myisam  、 innodb 默认的都是B-tree索引。

注意:

错误得在where条件常用的列上都加索引——独立索引,同时只能使用一个

在都列上建立索引后,查询哪个列,索引都将发挥作用——联合索引

在实际生活中联合索引用的比较多

下面来一个例子:

建一个be_tree表

create table be_tree(
id int primary key auto_increment,
c1 int not null default 0,
c2 int not null default 0,
c3 int not null default 0,
c4 int not null default 0,
  index  c (`c1`,`c2`,`c3`,`c4`)
) engine = Innodb charset utf8;

 insert into be_tree (`c1`,`c2`,`c3`,`c4`) values(1,2,3,4);
  insert into be_tree (`c1`,`c2`,`c3`,`c4`) values(5,6,7,8);

进行条件查询查看索引使用情况:

explain select * from be_tree where `c1`=1 and `c2`=2 and `c3` =3 and `c4`=4G

image

explain select * from be_tree where `c1`=1 and `c2`=2 and `c3` >2 and `c4`=4G

image

explain select * from be_tree where `c1`=1 and `c2`<2 and `c3` =2 and `c4`=4G

image

从上面的查询结果可知:B-tree发挥的是左前缀原则——前面的索引发挥作用了吗,后面的才发挥作用。

就好比一座桥分为四块,必须要前面的四块都能确定的能用,才能过去。

Hash索引:一般翻译为“散列”,在memory表里默认的为hash索引,在理论上它的时间复杂度为o(1),但是他计算后的结果是随机得。

注意:

无法对范围查询进行优化

无法利用前缀索引

排序也无法优化

在计算出数据的位置后,必须回行——即是拿到数据的位置,必须回到表数据中拿取数据

聚簇索引与非聚簇索引——Innodb 与 Myisam

聚簇索引:在索引文件中既存储主键值又在主键的叶子中存放该行数据,即是Innodb,聚簇的次索引指向对主键的引用。

非聚簇索引:索引指向行在磁盘位置上,即是Myisam。

注意对Innodb:

主键索引索引,及存储索引值,又在叶子中存储行数据。

如果没有主键(primary key )则会以 unique key 作为主键

如果没有unique ,则系统会生成一内部的rowid做主键

 

聚簇索引优势:如果根据主键查询的条目较少时,不用回行,直接在索引的叶子节点下取数据

聚簇索引劣势:如果碰到主键卜谷泽插入数据时,会造成频繁得页分裂。

0 条评论
发表一条评论