表的优化与列类型的选择
表的优化
1:定长与变长分离
如id int类型,占4个字节,char(4)占4个字节,也是定长,即每一单元占的字节是固定的。核心且常用字段,宜建成定长,放在一张表。
而varchar 、txt、blob,这种变长字段,适合单独放一张表,用主键和核心表关联起来
2、常用字段和不常用字段要分离
要结合网站具体业务来分析,分析字段的查询场景,查询频度低的字段,单拆出来
比如用户信息表,姓名、性别、年龄 都是固定的,且是比较常用的,而个人介绍缺特别长,也不太常用,可以单独放在一张表
列表页
3、在1对多需要关联统计字段上,添加冗余字段,提高速度
第一范式 、范式越高
两表联查 时,考虑是否能增加一个冗余字段,防止多个两个表
空间换时间,
4、列选择原则
1、)字段类型优先级 整型> date.time>enum ,char>vchar >blob,text
字符char(1) 与tinyint 1,2 都是一个字节时,但是order by 排序时,tiny int 比char快,应为字符集需要考虑校对集(就是排序规则)
enum 能起约束值的目的,内部用整型来存储,但与char联查时,内部要经历串与值的转化
char定长,考虑字符集的转换与排序的校对集,速度慢
varchar不定长,要考虑字符集的转换与排序的校对集,速度慢
text blob 无法使用内存临时表(排序等操作只能在磁盘上进行)
关于data time选择,大师的明确意见,直接选择
int unsigned not null;
够用就行,不要慷慨(如 small int ,varchar(N))
原因:大的字段浪费内存,影响速度
以年龄为例,tinyint unsigned not null ,可以存储255 岁足够了,用int 浪费3个字节
以varchar(!0)、varchar(300)存储的内容相同,但在联查时,varchar(300)要花费跟多内存
3、尽量避免用NULL
拥有NULL ,不利于索引,要用特殊的字节来标注
在磁盘上占据的空间其实更大
索引优化 高效查询的数据结构
1、btree 索引 时间复杂度 log2N
2、hash 索引
hash索引 只能在mymory 表里(放在内存中的),默认是hash索引,hash的理论查询时间复杂度为O(1)
疑问:既然hash 的查找如此高效,为什么不都用hash索引
hash地址可能有冲突,hash函数后,返回的值是无顺序的。对精准查询,比如
where id>4 ,用hash不行
2)无法进行范围优化
3)hash索引无法利用前缀索引,比如在btree中,field列的值“helloword”,加索引查询 xx=helloword ,自然可以利用索引x=hello(左前缀索引),而hash不行,因为hash(helloword)和hash(hello)不相等
4)排序也无法优化
5)必须回航,就是说,通过索引拿到数据位置,必须回到表中取数据
在where条件常见的列上加上索引,意义不大。
2.2在多列上建立索引后,查询哪个列,索引都能发挥作用
错误
btree的左前缀原则,想象老师讲的三块木板
|-------------|-------------------|---------------------|
| a | b | c |
|-------------|-------------------|-------------------- |-
从左到右,只有走过a,才能到b,走了b才能到c,
如果中间某个列跳过了,或者模糊查询了,后面的列的索引就不能用了
![](https://i-blog.csdnimg.cn/blog_migrate/dc89f842fc47c238ae8fb682a35cbc11.png)
索引:提高查询速度,提高排序速度、提高分组统计的速度
考虑多列索引
![](https://i-blog.csdnimg.cn/blog_migrate/0ef8358bb51788a52bdc8d98f4f81b01.jpeg)
树上只保存主键值,所以如果select name from xx where id>5,
通过索引查到了,主键值,想得到name得回行到 数据保存文件,此时需要访问磁盘
聚簇索引与非聚簇索引
1、
myisam 与innodb引擎索引文件的异同
非聚簇索引,数据得从索引树上所指的位置,到数据文件的指定位置找到数据。。这个过程叫回行
2、
![](https://i-blog.csdnimg.cn/blog_migrate/215496470f8f6dfe9e13ff69fc986836.jpeg)
树上保存所有数据,主键,name ,email等等
innodb 数据 直接存在叶子节点上,不用再通过节点保存的索引值再去找数据
3、
innodb索引
![](https://i-blog.csdnimg.cn/blog_migrate/492a213611eba191f19ea12d59ec1924.jpeg)
4、myisam索引
![](https://i-blog.csdnimg.cn/blog_migrate/cb86d5e3aaa2e60bebe9968bc935949f.jpeg)
5、聚簇索引
![](https://i-blog.csdnimg.cn/blog_migrate/8f6f41a5a59b2067bb6343f6e313b0a2.jpeg)
innodb 主键值不规律时,叶子节点需要进行不停的页片分裂,速度减慢。 但是你查询时,查询出来的数据是有规律的,因为innodb数据放在了树的节点上,在插入时,会按
二叉树的规律进行插入,自然取出来时,数据也是有规律的。
如果换成myisam引擎,如果主键不规律插入,取出来的数据也是不规律的
![](https://i-blog.csdnimg.cn/blog_migrate/78d8dac7ef6f74f18b930fa033e0444a.jpeg)
t99 表中是id,name 联合索引 ,如果select name from t99 where id=2;
此时不需要回行,因为 建立索引时 是(id,name),name在节点上存着,直接可以访问。。
而 select intro from t99 where id=2;此时需要回行,因为intro没有保存在索引树上
![](https://i-blog.csdnimg.cn/blog_migrate/cb29ada0767f1c92b7a93f558e337fe9.jpeg)
1、是否回行对比
![](https://i-blog.csdnimg.cn/blog_migrate/b06221dd2866dc180a84a3174cc25cad.jpeg)
1、常用的sql命令
![](https://i-blog.csdnimg.cn/blog_migrate/23a293092f699f9fbbc86018bc743405.jpeg)
show profiles; 查看语句执行是时间
![](https://i-blog.csdnimg.cn/blog_migrate/2dc1de29bf78556e835c0c69576c4521.jpeg)
取数据的左边一个
select left<word,1> from dict limit 10
去除左边一个字符,去重复,然后数数一共有多少
select count <distinct left<word,1>> from dict;
5、理想的索引
![](https://i-blog.csdnimg.cn/blog_migrate/5761ba836282968ed6577bbead482341.jpeg)
10、索引与排序
11、索引碎片与维护
12、防止三表以上的联查 left join
查询 、排序 、分组尽量走索引 ,尽量不要回行,走索引覆盖。索引覆盖,顺着索引进行查询,不需要回行,速度快
sql语句 left join ,inner join
mysql in优化
一、
mysql会对sql语句做优化, in 后面的条件不超过一定数量仍然会使用索引。
mysql 会根据索引长度和in后面条件数量判断是否使用索引。
另外,如果是in后面是子查询,则不会使用索引。
一个文章库,里面有两个表:category和article。category里面有10条分类数据。article里面有 20万条。article里面有一个"article_category"字段是与category里的"category_id"字段相对应的。 article表里面已经把 article_category字义为了索引。数据库大小为1.3G。
问题描述:
执行一个很普通的查询:
Select
*
FROM
`article`
Where
article_category
=
11
orDER
BY
article_id
DESC
LIMIT
5
//执行时间大约要5秒左右
解决方案:
建一个索引:
create
index
idx_u
on
article (article_category,article_id);
Select
*
FROM
`article`
Where
article_category
=
11
orDER
BY
article_id
DESC
LIMIT
5
减少到0.0027秒
继续问题:
Select
*
FROM
`article`
Where
article_category
IN
(
2
,
3
)
orDER
BY
article_id
DESC
LIMIT
5
执行时间要11.2850秒。
使用OR:
select
*
from
article
where
article_category
=
2
or
article_category
=
3
order
by
article_id
desc
limit
5
执行时间:11.0777
解决方案:避免使用in 或者 or (or会导致扫表),使用union all
使用UNION ALL:
(
select
*
from
article
where
article_category
=
2
order
by
article_id
desc
limit
5
)
UNION
ALL
(
select
*
from
article
where
article_category
=
3
order
by
article_id
desc
limit
5
)
orDER
BY
article_id
desc
limit
5
执行时间:0.0261
二、强制使用索引
select id from it_area
use index(primary) where pid=69 limit 1;
三、union
总是会产生临时表
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
查看当前数据库 中的临时表
show status like '%tmp%';
union all不去重,如果union 去重的话,会很耗时
三、设置数据库某列的编码格式
![](https://i-blog.csdnimg.cn/blog_migrate/554eadc3c233ce25b0e0b582dbe662f6.jpeg)
show profile 可以查看详细的时间耗费
show profile for query 2;
limit语句换成条件查询
limit 5,3
where id>5 limit 3;这样可以用到索引,当 limit 10000000 ,100 ; 换成
select *from tab where id>10000000 limit 100;时 效率明显提高,索引的作用。前提时,列中的数据是连续的,别删除。如果列有删除,两条语句就不等效了。
![](https://i-blog.csdnimg.cn/blog_migrate/afd144be2aba9aea13154e1270faef0e.jpeg)
select *from lx_com inner join (select id from lx_com
limit 12000000,3)as tmp on lx_com.id=tmp.id;
内部是查询用到了索引
燕十八老师 mysql高级教程
精通mysql视频教程