mysql_索引
- 新增
- 名词解释
- 索引优缺点
- 聚簇索引与非聚簇索引
- order by xxx索引
- 主键索引与非主键索引
- 数据与数据页
- 索引的原理
- 关于主键
- 普通索引与唯一性索引
- 性能调优
- 尽量使用主键索引,减少回表
- 覆盖索引
- 最左前缀原则
- 一个案例去掉无用索引
- Explain索引是否生效
- 使用主键索引possible_keys
- explain的各字段的解释
- select_type
- TYPE
- key
新增
1.给日期建索引,如果差异越大,建索引越好,如果给会计日期 加索引则基本上没啥用 都是同一天的数据,基本上没有太大的差异,所以不需要加
名词解释
索引优缺点
索引优点是增加查询效率
缺点是增删改效率差点,增加容量
聚簇索引与非聚簇索引
聚簇索引 最通信的说就是主键索引 非聚簇索引 就是单独的一个索引会生成一个文件来存储
order by xxx索引
orer by xxx 走索引也会更快,如果id为自增 那么order by id 会自动走索引 排序也挺快的
如果 a,b 为联合索引 则order by a,b 或order by a 也会走索引
主键索引与非主键索引
非主键索引是一个物理键,实际在数据库中会产生一个文件来存储,而主键索引是一个逻辑键,不会产生一个真正的专有文件,只不过主键的搜索靠索引树的搜索而已
redolog与binlog
redo log 记录的是物理日志"某个数据页上做了什么修改" 循环使用
bin log 记录的是逻辑日志 语句的原始逻辑"ID=1 ,2 " 追加使用数据与数据页
1.mysql的数据插入后,实际的存储都是放在数据页上面
2.如果数据页满了,按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%
当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程
2.先内存–>再磁盘–>内存做操作–>持久到redolog中去
查询时会先看目标数据页是否在内存中,如果不在则去磁盘加载.以更新操作为例,如果不在内存,首先将数据页从磁盘中加载到内存中,其次更新内存中的值,最后再更新到redolog的日志中去
索引的原理
原理:B+树索引 二叉搜索树这棵树是平衡二叉树 N叉树为了减少树高,通过B+树 从树根开始,按层搜索到叶子节点,这个叶子节点对应一个数据页,把这个数据页加载进内存中,然后对这个数据页再进行二分查找来定位记录.
innodb是按数据页单位来存读取的,每个数据页的默认大小为16K
关于主键
自增主键,比较合理,性能好与省存储空间,主键长度越小普通的索引叶子节点就越小,索引文件的就越小省空间
具体的关于主键的mysql的原理连接
普通索引与唯一性索引
唯一性索引不使用change buffer ,而普通索引会使用chang buffer,因为唯一性索引更新时都要先判断是否违反唯一性约束,这必须将数据页读入内存才能判断.而普通索引不会
change buffer
服务于更新,内存里不存在,不直接从磁盘取,而是先放在change buffer中,然后再慢慢落盘
1.当想要更新时,如果数据页在内存中就直接更新 ,如果不在内存中会先放在 change buffer,等下次查询再访问这个数据页时,再执行change buffer
2.change buffer 也会写在磁盘上这个过程叫purge,系统会定期的进行purge
3.change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%
change buffer使用场景
适合于写频繁,不马上查 多于于日志,账单
不适合于写频繁,马上查
1.在一个数据页做purge之前，change buffer记录的变更越多，收益就越大,对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
2.反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer,但之后由于马上要访问这个数据页，会立即触发purge过程。
这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来说，change buffer反而起到了副作用。
尽可能使用普通索引。
redo log主要节省的是随机写磁盘的IO消耗(转成顺序写)，而change buffer主要节省的则是随机读磁盘的IO消耗。
.
性能调优
尽量使用主键索引,减少回表
如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+树
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，ID 的值为 500，再到 ID 索引树搜索一次。这个过程称回表
也就是说，基于非主键索引的查询需要多扫描一棵索引树,因此，我们在应用中应该尽量使用主键查询。
覆盖索引
create table T ( ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');select * from T where k between 3 and 5
这种查询K的索引搜索到主键 然后搜索主键的索引 拿到具体的信息有回表(因为select * )
覆盖索引的做法
select ID from T where k between 3 and 5 这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表 由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
最左前缀原则
索引(a,b) 查询条件b是无法使用联合索引的 查询条件a可以使用索引
%%开头是不会使用索引的
一个案例去掉无用索引
CREATE TABLE geek (a int(11) NOT NULL,b int(11) NOT NULL,c int(11) NOT NULL,d int(11) NOT NULL,
PRIMARY KEY (a,b),
KEY c (c),
KEY ca (c,a),
KEY cb (c,b)
) ENGINE=InnoDB;
由于历史原因，这个表需要 a、b 做联合主键
那意味着单独在字段 c 上创建一个索引，就已经包含了三个字段为什么要创建“ca”“cb”这两个索引？
select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;
问题 这位同事的解释对吗? 哪些索引没有必要可以删除
当表记录为情况一时
–a--|–b--|–c--|–d--
1 2 3 d
1 3 2 d
1 4 3 d
2 1 3 d
2 2 2 d
2 3 4 d主键 a，b 的聚簇索引组织顺序相当于 order by a,b ，也就是先按 a 排序，再按 b 排序，c 无序。
索引 ca 的组织是先按 c 排序，再按 a 排序，同时记录主键
–c--|–a--|–主键部分b-- （注意，这里不是 ab，而是只有 b）
2 1 3
2 2 2
3 1 2
3 1 4
3 2 1
4 2 3上面的这个索引ca 与 索引c 的数据是一模一样的
索引 cb 的组织是先按 c 排序，在按 b 排序，同时记录主键
–c--|–b--|–主键部分a-- （同上）
2 2 2
2 3 1
3 1 2
3 2 1
3 4 1
4 3 2所以结论是ca可以去掉 cb保留
Explain索引是否生效
使用主键索引possible_keys
EXPLAIN SELECT * from t_course;
EXPLAIN SELECT * from t_course where id=1;主要看possible_key是否使用相应的主键索引值
列出可能用的索引,但不一定能使用
PRIMARY
<auto_key0>
explain的各字段的解释
select_type
PRIMARY 如果有子查询那么最外层的查询
derived 派生表类似于临时表
TYPE
ALL 全表扫描效率最差的方式
ref 非唯一索引(理解为 普通索引)
index 索引
key
使用的索引
