mysql之索引那些事儿~

磁盘预读

4096字节 block

b树

balance tree

b+树

数据只存储在叶子节点

在子节点之间加入了双向地址连接,更方便的在子节点之间进行数据的读取

索引原理

innodb索引
# 聚集索引 一个表只有一个主键，即聚集索引
# 辅助索引 除了主键之外所有的索引都是辅助索引，回表: 只查询一个索引并不能解决查询中的问题,还需要到具体的 表中去获取正行数据
myisam索引
# 辅助索引 除了主键之外所有的索引都是辅助索引

索引的种类

数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index）

• 聚集索引

  • InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
  • 由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。
    此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。
  • 聚集索引的好处：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录。范围查询（range query）即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可**

• 辅助索引

  • 表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

  • 辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

    举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问才能得到最终的一个数据页。

• 聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

• 聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息

  priamry key 的创建自带索引效果 非空 + 唯一 ---------》 聚集索引

  unique 唯一约束的创建也自带索引效果 唯一 --------------》辅助索引

  index 普通的索引 --------------》辅助索引

创建索引

create index ind_name on 表(name);

删除索引

drop index ind_name on 表;

索引的优缺点

优点 : 查找速度快，创建了索引之后的效率大幅度提高

缺点 : 浪费空间,拖慢写的速度，文件所占的硬盘资源也大幅度提高

不要在程序中创建无用的索引

正确的使用索引

1.所查询的列是创建了索引的列
2.在条件中不能带运算或者函数,必须是"字段 = 值"
3.如果创建索引的列的内容重复率高也不能有效利用索引
# 重复率不超过10%的列比较适合做索引
4.数据对应的范围如果太大的话,也不能有效利用索引
# between and > < >= <= != not in
5.like如果把%放在最前面也不能命中索引
6.多条件的情况
# and 只要有一个条件列是索引列就可以命中索引
# or 只有所有的条件列都是索引才能命中索引
7.联合索引
# 在多个条件相连的情况下,使用联合索引的效率要高于使用单字段的索引
# where a=xx and b=xxx;
# 对a和b都创建索引 - 联合索引
# create index ind_mix on s1(id,email)

1.创建索引的顺序id,email 条件中从哪一个字段开始出现了范围,索引就失效了
# select * from s1 where id=1000000 and email like 'eva10000%' 命中索引
# select count(*) from s1 where id > 2000000 and email = 'eva2000000' 不能命中索引

2.联合索引在使用的时候遵循最左前缀原则
# select count(*) from s1 where email = 'eva2000000@oldboy';

3.联合索引中只有使用and能生效,使用or失效

字段 能够尽量的固定长度 就固定长度

varchar

mysql 神器 explain

查看sql语句的执行计划

explain select * from s1 where id < 1000000;

是否命中了索引,命中的索引的类型

两个名词

覆盖索引 using index

select count(id) from 表;

select id from 表 where id <20;

索引合并

创建的时候是分开创建的

用的时候临时和在一起了

using union 表示索引合并

知道mysql可以开启慢日志

慢日志是通过配置文件开启

如果数据库在你手里 你自己开

如果不在你手里 你也可以要求DBA帮你开

表联查速度慢怎么办?

1.表结构

尽量用固定长度的数据类型代替可变长数据类型

把固定长度的字段放在前面

2.数据的角度上来说

如果表中的数据越多 查询效率越慢

列多 : 垂直分表

行多 : 水平分表

3.从sql的角度来说

1.尽量把条件写的细致点儿 where条件就多做筛选

2.多表尽量连表代替子查询

3.创建有效的索引,而规避无效的索引

4.配置角度上来说

开启慢日志查询 确认具体的有问题的sql

5.数据库

读写分离

解决数据库读的瓶颈

数据表\库的导入导出

备份表 :homwork库中的所有表和数据

mysqldump -uroot -p123 homework > D:\s23\day42\a.sql

备份单表

mysqldump -uroot -p123 homework course > D:\s23\day42\a.sql

备份库 :

mysqldump -uroot -p123 --databases homework > D:\s23\day42\db.sql

恢复数据:

进入mysql 切换到要恢复数据的库下面

sourse D:\s23\day42\a.sql

开启事务,给数据加锁

begin;

select id from t1 where name = 'alex' for update;

update t1 set id = 2 where name = 'alex';

commit;