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

索引原理与数据库优化

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

存储引擎机制

  1. <code># 磁盘预读
  2.     # 4096字节 block
  3. # b树
  4.     # balance tree
  5. # b+树
  6.     # 数据只存储在叶子节点
  7.     # 在子节点之间加入了双向地址连接,更方便的在子节点之间进行数据的读取
  8. 聚集索引</code>

索引原理

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

你是否对索引存在误解？

索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。一些开发人员总是在事后才想起添加索引----我一直认为，这源于一种错误的开发模式。如果知道数据的使用，从一开始就应该在需要处添加索引。开发人员往往对数据库的使用停留在应用的层面，比如编写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+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

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

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

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

索引字段要尽量的小

.索引的最左匹配特性

索引的种类

  1. <code>MySQL常用的索引
  2. 普通索引 index：加速查找
  3. 唯一索引：
  4.     -主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
  5.     -唯一索引UNIQUE:加速查找+约束（不能重复）
  6. 联合索引：
  7.     -primary KEY(id,name):联合主键索引
  8.     -unique(id,name):联合唯一索引
  9.     -index(id,name):联合普通索引</code>

聚集索引

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

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

  1. <code># priamry key 的创建自带索引效果       非空 + 唯一 + 聚集索引
  2. 自己不创建 系统默认创建
  3. #如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。
  4. #如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。
  5. 好处
  6. 　　聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录
  7.   　聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可</code>
  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

  1. <code>#我们可以在创建上述索引的时候，为其指定索引类型，分两类
  2. hash类型的索引：查询单条快，范围查询慢
  3. btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
  4. #不同的存储引擎支持的索引类型也不一样
  5. InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
  6. MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
  7. Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
  8. NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
  9. Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；</code>

索引的优缺点

  1. <code># 优点 : 查找速度快
  2. # 缺点 : 浪费空间,拖慢写的速度
  3.     # 不要在程序中创建无用的索引</code>

创建删除索引

  1. <code>#方法一：创建表时
  2.     　　CREATE TABLE 表名 (
  3.                 字段名1  数据类型 [完整性约束条件…],
  4.                 字段名2  数据类型 [完整性约束条件…],
  5.                 [unique | fulltext | spatial ]   index | kye
  6.                 [索引名]  (字段名) 
  7.                 );
  8. 实列
  9. #方式一
  10. create table t1(
  11.     id int,
  12.     name char,
  13.     age int,
  14.     sex enum('male','female'),
  15.     unique key uni_id(id),
  16.     index ix_name(name) #index没有key
  17. );
  18. #方法二：CREATE在已存在的表上创建索引
  19.         sreate  [unique | fulltext | spatial ]  index  索引名 
  20.                      on 表名 (字段名) ;
  21. 实列
  22. create index ix_age on t1(age);
  23. #方法三：ALTER TABLE在已存在的表上创建索引
  24.         ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
  25.                              索引名 (字段名) ;
  26. 实列
  27. alter table t1 add index ix_sex(sex);
  28. #删除索引：DROP INDEX 索引名 ON 表名字;
  29. 实列
  30. drop index ix_sex on t1;
  31. </code>

正确的使用索引

如何命中索引

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

不能命中索引条件

  1. <code># 1.所查询的列不是创建了索引的列
  2. # 2.在条件中带运算或者函数 不能命中,必须是"字段 = 值"
  3. # 3.如果创建索引的列的内容重复率高也不能有效利用索引
  4. # 重复率不超过10%的列比较适合做索引
  5. # 4.数据对应的范围如果太大的话,也不能有效利用索引
  6. # between and > < >= <= != not in
  7. # 5.like如果把%放在最前面也不能命中索引
  8. # 6.多条件的情况
  9. # and 只要有一个条件列是索引列就可以命中索引
  10. # or  只有所有的条件列都是索引才能命中索引
  11. </code>

什么是联合索引

  1. <code>什么是联合索引
  2. 联合主键 联合唯一
  3. # 7.联合索引
  4. # 在多个条件相连的情况下,使用联合索引的效率要高于使用单字段的索引
  5. # where a=xx and b=xxx;
  6. 创建联合索引
  7. # 对a和b都创建索引 - 联合索引
  8. # create index 索引名 on 表名(字段1,字段2)
  9. # create index ind_mix on s1(id,email)
  10. # 1.创建索引的顺序id,email 条件中从哪一个字段开始出现了范围,索引就失效了
  11. # select * from s1 where id=1000000 and email like 'eva10000%'  命中索引
  12. # select count(*) from s1 where id > 2000000 and email = 'eva2000000'  不能命中索引
  13. # 2.联合索引在使用的时候遵循最左前缀原则
  14. # select count(*) from s1 where email = 'eva2000000@oldboy';
  15. # 3.联合索引中只有使用and能生效,使用or失效
  16. # 字段 能够尽量的固定长度 就固定长度
  17. # varchar 尽量往后面放
  18. </code>

mysql 神器 explain

  1. <code># 查看sql语句的执行计划
  2. # explain select * from s1 where id < 1000000;
  3. # 是否命中了索引,命中的索引的类型
  4. </code>

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

开启慢日志

  1. <code>知道mysql可以开启慢日志
  2. # 慢日志是通过配置文件开启
  3. # 如果数据库在你手里 你自己开
  4. # 如果不在你手里 你也可以要求DBA帮你开
  5. </code>

事务

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

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

  1. <code>事务
  2. 原子性 
  3. 其对数据的修改，要么全部成功，要么全部都不成功。
  4. 一致性 
  5. 事务开始到结束的时间段内，数据都必须保持一致状态。
  6. 隔离性 
  7. 数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的"独立"环境执行。
  8. 持久性 
  9. 事务完成后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
  10. 我们可以在mysql事务处理过程中定义保存点(SAVEPOINT)，然后回滚到指定的保存点前的状态。
  11. 　　　　定义保存点，以及回滚到指定保存点前状态的语法如下。
  12. 　　　　1.定义保存点---savepoint 保存点名;
  13. 　　　　2.回滚到指定保存点---rollback to savepoint 保存点名:
  14. </code>

实列 开启一个事务

  1. <code>开启事务,给数据加锁
  2. # begin;#开始
  3. # select id from t1 where name = 'alex' for update;
  4. # update t1 set id = 2 where name = 'alex';
  5. # commit;#结束
  6. </code>

实列 开启回滚

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

数据表\库的导入导出

  1. <code># 备份表 :homwork库中的所有表和数据
  2.     # mysqldump -uroot -p123 homework > D:\s23\day42\a.sql
  3.     # 备份单表
  4.     # mysqldump -uroot -p123 homework course > D:\s23\day42\a.sql
  5. # 备份库 :
  6.     # mysqldump -uroot -p123 --databases homework > D:\s23\day42\db.sql
  7. # 恢复数据:
  8.     # 进入mysql 切换到要恢复数据的库下
  9.     # sourse D:\s23\day42\a.sql
  10.     
  11. mysqldump
  12. 语法:mysqldump -u用户名 -p -B（又不用自己创库） -d 库名>路径(g:\av\av.sql)
  13. 备份:mysqldump -uroot -p -B -d 库名>(g:\av\av.sql)
  14. 语法mysql -uroot -p < (g:\av\av.sql)
  15. 还原:mysql -uroot -p < 路径(g:\av\av.sql)
  16. </code>

7表联查速度慢怎么办?

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

索引原理与数据库优化

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

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！