读取硬盘的一次开销
每分钟7200 圈,60/7200=0.09ms
寻道时间:找到那一圈 大概是0.05ms 5/100000s
找到一圈上具体数据的平均时间 0.045ms
从硬盘上读取一次数据的时间大概是0.095ms=0.1ms
而cpu500 00 0000=5亿条指令/s
从硬盘上读取一次数据的时间大概是0.1ms,0.1ms可以让cpu执行50000条指令
故读取一次硬盘的开销是非常大的
磁盘的预读性原理
open(‘file’) as f
for line in f:
pass
例如让读10个字节,10个字节之后的内容也很快会被用到,会比用到的部分多读取一部分,每一次读取硬盘的单位不是你要多少就读多少,每一次读取的数据块大小是固定的,会每次读多少个block
二叉排序树:找几次却决于树的高度
平衡二叉树: 能够让查找某一个值经历的查找速度尽量平衡
数据结构为什么不用平衡树?
B+树的结构存数据?
b是balanc的意思
为了保证每一次数据查找经历io次数都相同
只在叶子节点存储数据:为了降低树的高度
叶子节点之间加入了双向连接,为了查找范围的时候比较快
两种索引的差别?
聚集索引:Innodb必有且仅有一个聚集索引
全表数据都存储在叶子节点上
非聚集索引(辅助索引)
叶子节点部存放具体的整行数据,而是存储这一行的主键的值
非聚集索引 select *from 表名 where age=18 age是非聚集索引
则查找过程
(1-40)-(1-20)-(18,5)-(5-8)(开始回表)-(5-6-(5 ,b哥,18)
select *from 表 where id=2,id是聚集索引则
(1-4)-(1-2)-(2,wusir,74)
innodb
mysam
创建索引和删除
create index 索引名字 on 表名(字段)
create index 索引名字 on 表名(字段名1,字段名2)
drop index 索引名 on 表名
索引不能命中的情况
数据的准备
1.查询的条件字段不是索引字段
对哪一个字段创建了索引,就用这个字段做条件查询
2.在创建索引的时候应该对区分度比较大的列进行创建
1/10以下的重复率比较适合创建索引
3范围
范围越大越慢,范围越小越快。
如果用了like的话 like‘a%'就快一些 like’%a%‘就慢
id已经设置为索引,但是范围查询仍然不快:范围查询太大了
但是如果是范围查询小了,查询比上面快,因为读树的结构的块小了
between…and…也是同样的道理,范围大了需要读的块数多,用的时间长
4条件列参与计算、或者使用函数
对emali创建索引
一个快一个慢,why?字符串从左向右进行筛选
如果用了like的话 like‘a%'就快一些 like’%a%‘就慢
5.and 和or
and命中索引的几率高,or很难命中索引
多个条件组合,如果使用and连接,其中一列含有索引,都可以加快查找速度
如果使用or连接,必须所有的列都含有索引,才能加快查找速度
用or非常慢,这时候是id是索引,name没有索引
把email的那一列索引删除掉
6 联合索引
最左匹配原则:必须带着最左边的列做条件,从出现范围开始索引失效
去掉id条件查询的就很慢了
其他注意事项:
避免使用select*
使用count(*)
创建表尽量使用char代替varchar:跟索引没关系,固定空间,查询速度和写入速度会变快
表的字段顺序固定长度的字段优先
组合索引代替多个单列索引:查的都是组合在一起的就组合,查的都单个就单个索引
尽量使用短索引:索引列厂,block存的数据少,会加高树的高度
使用连接代替子查询
连表时注意条件类型需一致
索引散列值(重复少)不适合建索引,例如:性别不适合:
使用索引(命中和不命中)
1查询的字段不是索引字段
2 在条件中使用范围,结果的范围越大速度越慢,范围越小,就越快
like ‘a%’ 命中索引 like ‘%a’不命中索引
3.条件列不能参与计算和函数
4.and条件相连,有一个有索引,都会命中 or条件相连,所有列都有索引才能命中。
5.联合索引 :遵循最左匹配原则,创建的时候,最左边的索引必须在开始且出现范围开始索引失效。
create index mix_ind on 表(id,name,email)
select * from 表 where id=123 命中索引
select *from 表 where id>123 不命中索引
select *from 表 where id=123 and name='alex' 命中索引
select *from 表 where email=‘***@126.com’ 不命中索引,因为条件中没有id
select *from 表 where id>123 and name='alex'不命中索引
select *from 表 where name='alex' and email='alex@oldboy' 不命中索引,条件中无没有id
6.条件中的数据类型和实际字段必须一致
7.select字段中应该包含order by中的字段
select age from 表 order by age 快
select name from 表 order by age 慢
覆盖索引:查询过程中不需要回表
select id from 表 where id>100000;
select max(id) from 表 where id>100000;
索引合并: 分别创建的两个索引在一次查询中临时和并程一条索引
执行计划:explain select 语句 能够 查看sql语句有没有按照预期执行,可以查看索引的使用情况,type等级,
慢查询优化
从sql角度优化
把每一句单独执行,找到效率低的表,优化这句sql
了解业务场景,适当创建索引,帮助查询
尽量用连表代替子查询
确认命中索引的情况
考虑修改表结果
拆表
把固定的字段往前调整
使用执行计划,观察sql的type,通过以上调整是否提高
mysql的慢日志
在mysql的配置开启并设置一下
在超过设定时间之后,这条sql总是会被记录下来
这个时候可以对这些被记录的sql进行优化