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`=4\G
explain select * from be_tree where `c1`=1 and `c2`=2 and `c3` >2 and `c4`=4\G
explain select * from be_tree where `c1`=1 and `c2`<2 and `c3` =2 and `c4`=4\G
从上面的查询结果可知:B-tree发挥的是左前缀原则——前面的索引发挥作用了吗,后面的才发挥作用。
就好比一座桥分为四块,必须要前面的四块都能确定的能用,才能过去。
Hash索引:一般翻译为“散列”,在memory表里默认的为hash索引,在理论上它的时间复杂度为o(1),但是他计算后的结果是随机得。
注意:
无法对范围查询进行优化
无法利用前缀索引
排序也无法优化
在计算出数据的位置后,必须回行——即是拿到数据的位置,必须回到表数据中拿取数据
聚簇索引与非聚簇索引——Innodb 与 Myisam
聚簇索引:在索引文件中既存储主键值又在主键的叶子中存放该行数据,即是Innodb,聚簇的次索引指向对主键的引用。
非聚簇索引:索引指向行在磁盘位置上,即是Myisam。
注意对Innodb:
主键索引索引,及存储索引值,又在叶子中存储行数据。
如果没有主键(primary key )则会以 unique key 作为主键
如果没有unique ,则系统会生成一内部的rowid做主键
聚簇索引优势:如果根据主键查询的条目较少时,不用回行,直接在索引的叶子节点下取数据
聚簇索引劣势:如果碰到主键卜谷泽插入数据时,会造成频繁得页分裂。