索引
什么是索引:是一种排好序的数据结构,根据sql条件在这个数据结构可以快速定位数据的磁盘指针然后找到数据。
存储引擎
针对表 不同表可以有不同的存储引擎
Innodb
1.支持事务
2.行级锁
3.支持外键
必须有主键 数据存储在一起
MyISAM
1.不支持事务
2.表级锁
3.不支持外键
4
索引使用规则
1.最左前缀
5.范围查询右边的索引列失效
2.索引不能运算
3.索引字段没加引号,字符串没加单引号
4.or必须前后都是索引字段
以%开头的Like模糊查询,索引失效
如果MySQL评估使用索引比全表更慢,则不使用索引。
is NULL , is NOT NULL 有时索引失效
in , not in有时索引失效
尽量使用覆盖索引,避免select *
索引创建规则:
适当创建索引,需要经常查询的字段创建索引,不要创建过多的索引 索引需要维护
常见的sql优化
order by
group by
limit
count
大批量插入
mysql 为什么使用b+Tree
聚集索引 非聚集索引
叶子节点包含了完整的数据记录。这种索引叫做聚集索引。
主键索引 联合索引
回表操作
给name字段建立索引 select * from student where name = ‘张三’
首先根据非聚集索引查出 张三所对应的主键索引 根据主键索引再去查找数据 这样就导致回表操作
如果 select name from student where name = ‘张三’ 可以避免回表操作 因为保存了name的值
为什么innodb必须有主键?
因为InnoDB的数据⽂件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没
有),如果没有显式指定,则MySQL系统会⾃动选择⼀个可以唯⼀标识数据记录的列作为主键,
如果不存在这种列,则MySQL⾃动为InnoDB表⽣成⼀个隐含字段作为主键,这个字段⻓度为6个
字节,类型为⻓整形。
为什么使用B+tree b-tree考虑到了磁盘存储这块的性能
1.最小存储单元
innodb存储引擎页:16kb , 文件系统 4k, 磁盘扇区: 512字节 一个叶子节点是一页
一行数据约1k 所以1页大概存储16行 页可以存放数据(叶子节点) 也可存放指针+键值(非叶子节点) 跟页长住内存 所以一般IO2-3次
select from user where ID= 5
先根据跟页 通过二分法 定位5在那一页 再去页中查找数据
主键id为bigint型 长度8字节 指针6字节 一页可存放1170这个单元 117016*1170 = 21902400 条数据3阶
b-tree 非叶子节点保存数据 导致保存的键值和指针数少 导致树高增加 io次数增加
b+tree叶子结点直接有之间是一种链式环结构。因此可以对B+Tree进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。
为什么建议使用自增主键
不是自增 每次新进来的数据会要插入原先数据中 需要移动数据