当前位置:Gxlcms > 数据库问题 > 索引原理与数据库优化

索引原理与数据库优化

时间:2021-07-01 10:21:17 帮助过:12人阅读

存储引擎机制

# 磁盘预读
    # 4096字节 block
# b树
    # balance tree
# b+树
    # 数据只存储在叶子节点
    # 在子节点之间加入了双向地址连接,更方便的在子节点之间进行数据的读取
聚集索引

索引原理

索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查。
# innodb索引
    # 聚集索引 只有一个主键
    # 辅助索引 除了主键之外所有的索引都是辅助索引
    # 回表: 只查询一个索引并不能解决查询中的问题,还需要到具体的表中去获取正行数据
# myisam索引
    # 辅助索引 除了主键之外所有的索引都是辅助索引
  
 索引的影响
 1、在表中有大量数据的前提下,创建索引速度会很慢
 2、在索引创建完毕后,对表的查询性能会发幅度提升,但是写性能会降低
 # 创建了索引之后的效率大幅度提高
 # 文件所占的硬盘资源也大幅度提高

你是否对索引存在误解?

索引是应用程序设计和开发的一个重要方面。若索引太多,应用程序的性能可能会受到影响。而索引太少,对查询性能又会产生影响,要找到一个平衡点,这对应用程序的性能至关重要。一些开发人员总是在事后才想起添加索引----我一直认为,这源于一种错误的开发模式。如果知道数据的使用,从一开始就应该在需要处添加索引。开发人员往往对数据库的使用停留在应用的层面,比如编写SQL语句、存储过程之类,他们甚至可能不知道索引的存在,或认为事后让相关DBA加上即可。DBA往往不够了解业务的数据流,而添加索引需要通过监控大量的SQL语句进而从中找到问题,这个步骤所需的时间肯定是远大于初始添加索引所需的时间,并且可能会遗漏一部分的索引。当然索引也并不是越多越好,我曾经遇到过这样一个问题:某台MySQL服务器iostat显示磁盘使用率一直处于100%,经过分析后发现是由于开发人员添加了太多的索引,在删除一些不必要的索引之后,磁盘使用率马上下降为20%。可见索引的添加也是非常有技术含量的。

结论 最好在建表时就添加索引 考虑好相关事情 索引不是越多越好会影响写性能

当一次IO时,不光把当前磁盘地址的数据,而是把相邻的数据也都读取到内存缓冲区内,因为局部预读性原理告诉我们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关,一般为4k或8k,也就是我们读取一页内的数据时候,实际上才发生了一次IO,这个理论对于索引的数据结构设计非常有帮助。

索引的数据结构

1. 平衡树 b树

树状图是一种数据结构,它是由n(n>=1)个有限结点组成一个具有层次关系的集合。把它叫做“树”是因为它看起来像一棵倒挂的树,也就是说它是根朝上,而叶朝下的。

特点:每个结点有零个或多个子结点;没有父结点的结点称为根结点;每一个非根结点有且只有一个父结点;除了根结点外,每个子结点可以分为多个不相交的子树

2. b+树

B+树是通过二叉查找树,再由平衡二叉树,B树演化而来

特性:

  1. 数据只存储在叶子节点
  2. 在叶子节点之间加入了双向地址连接,更方便的在子节点之间进行数据的读取
  3. 最左匹配特性
  4. 索引字段越小查询速度越快
  5. B+树的高度一般都在2~4层
  6. B+树索引可以分为聚集索引(clustered index)和辅助索引(secondary index),
  7. 其他的树根啊树枝啊保存的就是数据的索引

技术图片

数据库中的

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

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

叶子结点存放的是索引值和主键**

回表: 只查询一个索引并不能解决查询中的问题,还需要到具体的表中去获取整行数据

索引字段要尽量的小

.索引的最左匹配特性

索引的种类

MySQL常用的索引
普通索引 index:加速查找
唯一索引:
    -主键索引PRIMARY KEY:加速查找+约束(不为空、不能重复)
    -唯一索引UNIQUE:加速查找+约束(不能重复)
联合索引:
    -primary KEY(id,name):联合主键索引
    -unique(id,name):联合唯一索引
    -index(id,name):联合普通索引

聚集索引

聚集索引的好处之一:它对主键的排序查找和范围查找速度非常快,叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表,查询最后的10位用户信息,由于B+树索引是双向链表,所以用户可以快速找到最后一个数据页,并取出10条记录  

聚集索引的好处之二:范围查询(range query),即如果要查找主键某一范围内的数据,通过叶子节点的上层中间节点就可以得到页的范围,之后直接读取数据页即可

# priamry key 的创建自带索引效果       非空 + 唯一 + 聚集索引
自己不创建 系统默认创建
#如果未定义主键,MySQL取第一个唯一索引(unique)而且只含非空列(NOT NULL)作为主键,InnoDB使用它作为聚簇索引。
#如果没有这样的列,InnoDB就自己产生一个这样的ID值,它有六个字节,而且是隐藏的,使其作为聚簇索引。

好处
  聚集索引的好处之一:它对主键的排序查找和范围查找速度非常快,叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表,查询最后的10位用户信息,由于B+树索引是双向链表,所以用户可以快速找到最后一个数据页,并取出10条记录
   聚集索引的好处之二:范围查询(range query),即如果要查找主键某一范围内的数据,通过叶子节点的上层中间节点就可以得到页的范围,之后直接读取数据页即可
  1. 叶子结点存放的是一整行的信息
  2. 由于实际的数据也是聚集索引的一部分,并且数据只能按照一棵B+树进行排序,所以每张表只能有一个聚集索引就是主键,(1、如果未定义主键,MySQL取第一个唯一索引(unique)而且只含非空列(NOT NULL)作为主键,InnoDB使用它作为聚簇索引;2、如果没有这样的列,InnoDB就自己产生一个这样的ID值,它有六个字节,而且是隐藏的,使其作为聚簇索引)

聚集索引的优点:

  1. 它对主键的排序查找和范围查找速度非常快,叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表,查询最后的10位用户信息,由于B+树索引是双向链表,所以用户可以快速找到最后一个数据页,并取出10条记录
  2. 范围查询(range query),即如果要查找主键某一范围内的数据,通过叶子节点的上层中间节点就可以得到页的范围,之后直接读取数据页即可

辅助索引

表中除了聚集索引外其他索引都是辅助索引(Secondary Index,也称为非聚集索引)(unique key啊、index key啊),与聚集索引的区别是:辅助索引的叶子节点不包含行记录的全部数据。

叶子节点存放的是对应的那条数据的主键字段的值,除了包含键值以外,每个叶子节点中的索引行中还包含一个书签(bookmark),其实这个书签你可以理解为是一个{‘name字段‘,name的值,主键id值}的这么一个数据

通过辅助索引的叶子节点不能直接拿到age的值,需要通过辅助索引的叶子节点中保存的主键id的值再去通过聚集索引来找到完整的一条记录,然后从这个记录里面拿出age的值,这种操作有时候也成为回表操作,就是从头再回去查一遍,这种的查询效率也很高,

  1. 叶子结点存放的是索引值和主键
  2. 回表: 只查询一个索引并不能解决查询中的问题,还需要到具体的表中去获取整行数据

覆盖索引

设置字典name为辅助索引 查到他为name 就是覆盖索引

简单来说就是是设置什么查到什么

索引的两大类型hash与btree

#我们可以在创建上述索引的时候,为其指定索引类型,分两类
hash类型的索引:查询单条快,范围查询慢
btree类型的索引:b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)

#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;
NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引;
Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引;

索引的优缺点

# 优点 : 查找速度快
# 缺点 : 浪费空间,拖慢写的速度
    # 不要在程序中创建无用的索引

创建删除索引

#方法一:创建表时
      CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [unique | fulltext | spatial ]   index | kye
                [索引名]  (字段名) 
                );

实列
#方式一
create table t1(
    id int,
    name char,
    age int,
    sex enum('male','female'),
    unique key uni_id(id),
    index ix_name(name) #index没有key
);

#方法二:CREATE在已存在的表上创建索引
        sreate  [unique | fulltext | spatial ]  index  索引名 
                     on 表名 (字段名) ;
实列
create index ix_age on t1(age);

#方法三:ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名) ;
实列
alter table t1 add index ix_sex(sex);

#删除索引:DROP INDEX 索引名 ON 表名字;
实列
drop index ix_sex on t1;

正确的使用索引

如何命中索引

在条件中不能带运算或者函数,必须是"字段 = 值"
 4.数据对应的范围小一点
        # between and > < >= <= != not in
6.多条件的情况
        # and 只要有一个条件列是索引列就可以命中索引
        # or  只有所有的条件列都是索引才能命中索引
# 字段 能够尽量的固定长度 就固定长度

不能命中索引条件

# 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 索引名 on 表名(字段1,字段2)
# 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;
# 是否命中了索引,命中的索引的类型

关于explain,如果大家有兴趣,可以看看这篇博客,他总结的挺好的:http://www.cnblogs.com/yycc/p/7338894.html

开启慢日志

知道mysql可以开启慢日志
# 慢日志是通过配置文件开启
# 如果数据库在你手里 你自己开
# 如果不在你手里 你也可以要求DBA帮你开

事务

事务定义 一件事从开始发生到结束的整个过程

锁和事务
innodb存储引擎默认是行级锁
myISAM 表锁

事务
原子性 
其对数据的修改,要么全部成功,要么全部都不成功。
一致性 
事务开始到结束的时间段内,数据都必须保持一致状态。
隔离性 
数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的"独立"环境执行。
持久性 
事务完成后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

我们可以在mysql事务处理过程中定义保存点(SAVEPOINT),然后回滚到指定的保存点前的状态。
    定义保存点,以及回滚到指定保存点前状态的语法如下。
    1.定义保存点---savepoint 保存点名;
    2.回滚到指定保存点---rollback to savepoint 保存点名:

实列 开启一个事务

开启事务,给数据加锁
# begin;#开始
# select id from t1 where name = 'alex' for update;
# update t1 set id = 2 where name = 'alex';
# commit;#结束

实列 开启回滚

mysql> begin; -- 或者 start transaction;#开启事务
mysql> INSERT INTO user VALUES ('3','one','0','');#向表user中插入2条数据
mysql> INSERT INTO user VALUES ('4,'two','0','');#向表user中插入2条数据
mysql> select * from user;#查询user表
mysql> savepoint test;#指定保存点,保存点名为test
mysql> INSERT INTO user VALUES ('5','three','0','');#向表user中插入第3条数据
mysql> select * from user;#查表
mysql> ROLLBACK TO SAVEPOINT test;#回滚到保存点test
mysql> select * from user;#查表

数据表\库的导入导出

# 备份表 :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
    
mysqldump
语法:mysqldump -u用户名 -p -B(又不用自己创库) -d 库名>路径(g:\av\av.sql)
备份:mysqldump -uroot -p -B -d 库名>(g:\av\av.sql)

语法mysql -uroot -p < (g:\av\av.sql)
还原:mysql -uroot -p < 路径(g:\av\av.sql)

7表联查速度慢怎么办?

# 1.表结构
    # 尽量用固定长度的数据类型代替可变长数据类型
    # 把固定长度的字段放在前面
# 2.数据的角度上来说
    # 如果表中的数据越多 查询效率越慢
        # 列多 : 垂直分表
        # 行多 : 水平分表
# 3.从sql的角度来说
    # 1.尽量把条件写的细致点儿 where条件就多做筛选
    # 2.多表尽量连表代替子查询
    # 3.创建有效的索引,而规避无效的索引
# 4.配置角度上来说
    # 开启慢日志查询 确认具体的有问题的sql
# 5.数据库
    # 读写分离
        # 解决数据库读的瓶颈

索引原理与数据库优化

标签:databases   alter   读写   sam   组成   环境   快速   永久   数据类型   

人气教程排行