mysql索引原理

读取硬盘的一次开销
每分钟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进行优化