网址之家123上网主页,网站结构优化,百度网站地图代码,青岛网页建站模板目录
索引的作用 与 概念
MySQL有哪几种索引类型
如何提高查找效率
聚簇索引与非聚簇索引
覆盖索引
索引的优点和缺点
索引的一些基本操作
索引优化
B树、B树、Hash、红黑树的区别
B树与B树的区别
MySQL为什么使用B树作为索引
联合索引中的顺序
MySQL的最左前缀原…目录
索引的作用 与 概念
MySQL有哪几种索引类型
如何提高查找效率
聚簇索引与非聚簇索引
覆盖索引
索引的优点和缺点
索引的一些基本操作
索引优化
B树、B树、Hash、红黑树的区别
B树与B树的区别
MySQL为什么使用B树作为索引
联合索引中的顺序
MySQL的最左前缀原则
查看表的索引信息
怎么判断要不要加索引
所有的字段都适合建索引吗
如何评估一个索引创建的是否合理 索引在哪些情况下会失效 如何避免索引失效
如何判断数据库的索引有没有生效
索引是用来干什么的
索引的使用的场景
事务的概念
事务原子性如何保证
MySQL 事务的 ACID 特性 事务的使用
MySQL中事务的隔离级别
脏读、不可重复读、幻读 索引的作用 与 概念
索引就相当于书的目录主要作用就是提高查找效率。
MySQL有哪几种索引类型
1、从存储结构上来划分BTree索引B-Tree或BTree索引Hash索引full-index全文索引。这里所描述的是索引存储时保存的形式。
2、从应用层次来分普通索引唯一索引复合索引。
普通索引即一个索引只包含单个列一个表可以有多个单列索引唯一索引索引列的值必须唯一但允许有空值复合索引多列值组成一个索引专门用于组合搜索其效率大于索引合并聚簇索引(聚集索引)并不是一种单独的索引类型而是一种数据存储方式。具体细节取决于不同 的实现InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是BTree)和 数据行。非聚簇索引 不是聚簇索引就是非聚簇索引。
如何提高查找效率
当从数据库中进行查找操作时比如根据条件 id3 查找可以遍历表进行查询但是这种方式效率比较低。 如何提高效率呢 这就需要想办法尽量避免遍历可以通过一些特殊的数据结构来表示一些记录的特征通过这些特征来减少比较次数加快比较的速率。 当查找效率提高时也将会付出一些代价 就相当于与给书加上目录会费一些纸即加上索引就会消耗一定的存储空间数据量越大消耗的空间就越大 书的目录确定了后续每次对书的内容进行调整时都可能会影响到目录的准确性就需要重新调整目录同理数据库的索引也是一样的当进行增删查改时往往也需要同步的调整索引的结构
聚簇索引与非聚簇索引
在 InnoDB 里索引B Tree的叶子节点存储了整行数据的是主键索引也被称之为聚簇索引即将数据 存储与索引放到了一块找到索引也就找到了数据。 而索引B Tree的叶子节点存储了主键的值的是非主键索引也被称之为非聚簇索引、二级索引。
对于InnoDB来说想要查找数据我们还需要根据主键再去聚集索引中进行查找这个再根据聚集 索引查找数据的过程我们称为回表。第一次索引一般是顺序IO回表的操作属于随机IO。需要回表的次数越多即随机IO次数越多我们就越倾向于使用全表扫描 。通常情况下主键索引聚簇索引查询只会查一次而非主键索引非聚簇索引需要回表查询多次。当然如果是覆盖索引的话查一次即可 。注意MyISAM无论主键索引还是二级索引都是非聚簇索引而InnoDB的主键索引是聚簇索引二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。
覆盖索引
覆盖索引是指一个索引包含了查询所需的所有列数据因此在查询时可以直接使用索引返回结果而不需要回到数据表中查找数据行从而大幅提高查询性能。
举个例子假设有一张订单表包含了订单编号、订单金额、订单日期等列如果我们需要查询某个日期范围内的所有订单金额我们可以在订单日期列上创建一个索引如果该索引还包含了订单金额列那么查询时就可以直接使用这个索引返回查询结果而不需要再回到订单表中查找对应的订单金额这就是覆盖索引。
可以看出覆盖索引可以大幅提高查询性能尤其是在大数据量的情况下。但是在创建覆盖索引时需要注意索引需要包含查询所需的所有列数据因此索引的大小可能会比较大从而增加读取磁盘的成本也需要权衡创建索引的成本和查询性能的提高。 索引的优点和缺点
索引带来的好处提高了查找得速度 索引带来得坏处占用了更多的空间拖慢了增删查改的速度
从表面来上看似乎索引的坏处 比 索引带来的好处要多。但这不必意味着 弊大于利 因为在实际需求的场景中查询操作往往是最高频率的操作。 相对于“增删改” 的使用频率则低的可怜。 因此查询作为一个高频操作索引对其来说是不可缺少的 另外有了索引之后对于查询的效率的提升使非常巨大的 当MySQL里面的数据量级 达到千万级别的时候一个表里就有几千万甚至破亿的数据再去遍历表就会非常非常的低效 在另一方面MySQL在进行数据比较的时候不是像我们编程那样一个for循环这样的想法是错误的。 编程上的查询是在内存中的比较MySQL 中的比较是在硬盘上比较。 也就是说在MySQL中的每一次比较都会涉及到硬盘的 IO 操作。
索引的一些基本操作
1.查看索引show index from 表名 有些约束是自动带索引的比如primary unique2.给指定列创建索引create index 索引名字 on 表名(列名); 例如create index class_index on student(class);-- 给class这一列加上索引 注意创建索引是一个非常低效的事情尤其是当前表里面已经有很多数据的时候 所以当你针对线上的数据库的时候如果这个表没有索引的时候不要贸然去加上索引…3.删除索引drop index 索引名字 on student 表名 例如drop index class_index on student; 注意删除索引和创建索引一样都是效率比较低的操作,也容易把数据库搞挂 因此在创建表的时候就应该把索引规划好。
索引优化
确定需要创建的索引的列
可以通过查询SQL语句的执行计划找出哪些SQL语句的查询效率比较低然后确定需要创建索引的列。
确定索引类型
不同的索引类型在不同的场景下具有不同的优势需要根据具体的业务需求和场景来选择合适的索引类型。
避免在索引列上使用函数或者表达式
在索引列上使用函数或者表达式会导致索引失效从而降低查询性能。
避免使用 OR 连接条件
在查询语句中使用 OR 连接条件会导致索引失效从而降低查询性能。
避免在表达式左侧使用运算符
在查询语句中将运算符放在表达式的左侧会导致索引失效从而降低查询性能。
B树、B树、Hash、红黑树的区别
BTree
B树是一棵多路平衡查找树
B树的每个节点都存储索引和数据key和data每个节点中的关键字都按照从小到大的顺序排列所有叶子节点都位于同一层或者说根节点到每个叶子节点的长度都相同 BTree
B树也是一棵多路平衡查找树
B树中非叶子节点不存储数据只存储索引。对于非叶子节点中key都按照从小到大的顺序排列 非叶子节点中的每一个key都会出现在子节点中是子节点中最大或最小元素。所有的非叶子节点起到了索引作用。只有叶子节点存储data叶子节点包含了这棵树的所有数据。叶子节点依据关键字的大小从小到大顺序链接形成一个有序链表。便于区间查找和遍历。所有叶子节点都位于同一层或者说根节点到每个叶子节点的长度都相同。 Hash索引 虽然可以快速定位但是没有顺序IO复杂度高 适合等值查询如、in()不支持范围查询 因为不是按照索引值顺序存储的就不能像BTree索引一样利用索引完成排序 如果有大量重复键值得情况下哈希索引的效率会很低因为存在哈希碰撞问题 。红黑树 树的高度随着数据量增加而增加IO代价高。
B树与B树的区别
B树的优势
IO次数更少查询性能很稳定范围查询更简便
1.B树的每个节点都存储key和dataB树只有叶子节点存储data叶子节点包含了这棵树的所有数据这样一个叶子节点可以存储更多的key,可以使树更矮所以 IO操作的次数更少。
2.B树中所有的叶子节点构成一个有序链表可以按照关键子码排序的次序遍历全部记录由于数据顺序排列并相连所以编译区间查找和搜索。而B树则需要进行每一层的遍历相邻的元素可能在内存中并不相邻。
3.在B树中当要查找的值恰好在一个非叶子节点时查找到该节点就会成功并结束查询而B树由于非叶子节点只是索引部分这些节点中只含有其子树中最大或最小关键字当非终端节点的关键字等于给定值时查找并不终止而是继续向下查找直到叶子节点。因此在B树中无论是否查找成功都是走了一条从根节点到叶子节点的路径。
MySQL为什么使用B树作为索引
1B树的磁盘读写代价更低B树的内部节点并没有指向关键字具体信息的指针因此其内部节点相对B树更小如果把所有同一内部节点的关键字存放在同一盘块中那么盘块所能容纳的关键字数量也越多一次性读入内存的需要查找的关键字也就越多相对IO读写次数就降低了。
2B树的查询效率更加稳定由于非终结点并不是最终指向文件内容的结点而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同导致每一个数据的查询效率相当。
3B树更便于遍历由于B树的数据都存储在叶子结点中分支结点均为索引方便扫库只需要扫一遍叶子结点即可但是B树因为其分支结点同样存储着数据我们要找到具体的数据需要进行一次中序遍历按序来扫所以B树更加适合在区间查询的情况所以通常B树用于数据库索引。
B树更适合基于范围的查询B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题正是为了解决这个问题B树应用而生。B树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的而B树不支持这样的操作或者说效率太低。
联合索引中的顺序
MySQL可以使用多个字段同时建立一个索引叫做联合索引。在联合索引中如果想要命中索引需要 按照建立索引时的字段顺序挨个使用否则无法命中索引。
具体原因为: MySQL使用索引时需要索引有序假设现在建立了nameageschool的联合索引那么索引的排序为: 先按照name排序如果name相同则按照age排序如果age的值也相等则按照school进行排序。
当进行查询时此时索引仅仅按照name严格有序因此必须首先使用name字段进行等值查询之后对于匹配到的列而言其按照age字段严格有序此时可以使用age字段用做索引查找以此类推。因此在 建立联合索引的时候应该注意索引列的顺序一般情况下将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。
MySQL的最左前缀原则
最左前缀原则就是最左优先在创建多列索引时要根据业务需求where子句中使用最频繁的一列放在最左边。
mysql会一直向右匹配直到遇到范围查询(、 3 and d 4 如果建立(a,b,c,d)顺序的索引d是用不到索引的如果建立(a,b,d,c)的索引则都可以用 到a,b,d的顺序可以任意调整。和in可以乱序比如a 1 and b 2 and c 3 建立(a,b,c)索引可以任意顺序mysql的查询优化器会帮 你优化成索引可以识别的形式。
在设计索引时要考虑使用最左前缀原则将最常用的列放在最左边。这样可以使索引更加高效查询速度更快。
查看表的索引信息
SHOW INDEX FROM 表名; 怎么判断要不要加索引
当唯一性是某种数据特征的时候加唯一索引。需要判断定义列的完整性以此提高查询速度。在频繁的进行分组或者排序时即建立group by / order by 的列上建立索引如果待排序的列有多个可以进行组合索引。
所有的字段都适合建索引吗
数据较少的表频繁更新的字段数据比较重复的字段比如性别、真假值where条件中用不到的字段不需要建立索引参与列计算的列
如何评估一个索引创建的是否合理 索引在哪些情况下会失效 如何避免索引失效
1对列进行计算或者是使用函数则该列的索引会失效
2不匹配数据类型会造成索引失效
3where语句中使用了IS NULL或者IS NOT NULL会造成索引失效
4使用了反向操作该索引将不起作用
5使用了link操作索引就将不起作用
6在WHERE中使用OR时有一个列没有索引那么其它列的索引将不起作用
如何判断数据库的索引有没有生效
通过explain
索引是用来干什么的
给信息分配一个id方便能够快速找寻到该数据
索引的使用的场景
通常情况下应该为需要经常进行查询的列创建索引特别是那些数据量较大的列。使用索引可以显著提高查询效率。但是在创建索引的时候需要注意索引会增加数据表的存储空间和数据修改的成本因此不能为所有列都创建索引。
以下是创建索引的一些建议
主键应该是唯一的且自动递增的因此默认会创建主键索引。对经常用于搜索条件的列进行索引如where和join语句中使用的列。列值不重复或者重复很少的列上创建索引如性别、状态等列。对经常需要排序的列进行索引。
需要注意的是索引并不是万能的也并不是越多越好。在数据量比较小的情况下无索引查询的效率可能比使用索引还要高在数据修改和写入比较频繁的表中创建过多的索引会影响数据的修改和写入性能因此需要权衡索引的使用和维护成本选择合适的索引策略。
事务的概念
MySQL 事务是指一组操作是一个不可分割的工作单位可以确保一组数据库操作要么全部执行要么全部不执行。换句话说事务是 MySQL 中保证数据一致性和完整性的机制。
在 MySQL 中事务可以用来保证数据库中数据的一致性和完整性例如在向数据库中插入或更新一组数据时要么所有数据插入或更新成功要么所有操作全部回滚保持数据的原样。
事务原子性如何保证
在执行到第二个SQL之前是无法预料这次执行会失败假设执行到第二个会失败 因此当出现执行失败后有数据库执行一些“还原”操作来消除前面SQL带来的影响。这个还原性的操作叫做“回滚”。 因而外面看起来当执行失败时一个都没有执行。
MySQL 事务的 ACID 特性
ACID 是事务处理中的关键概念它指的是
原子性指事务中的操作要么全部执行成功、要么全部失败回滚不会出现部分执行的情况。一致性指在事务开始和结束后数据库的完整性约束没有被破坏也就是说事务执行前后都需要满足一些预定义的约束条件。隔离性指一个事务中的执行不受其他并发事务的影响它们之间是相互隔离的。持久性指在事务提交之后对数据的更新就被永久写入数据库即使数据库出现故障也能够恢复。 事务的使用
开启事务start transaction;执行多条SQL回滚或提交rollback commit
说明rollback即是全部失败commit即是全部成功
MySQL中事务的隔离级别
隔离级别是指在并发情况下不同事务之间对数据库操作数据的可见性和影响范围的规定。 MySQL 支持以下四种隔离级别
读未提交一个事务所做的修改即使没有提交对其他事务也是可见的。在这种隔离级别下可能会出现脏读和不可重复读问题。读已提交一个事务所做的修改在提交后才会对其他事务可见同样可能会出现不可重复读问题。可重复读保证了在同一个事务中对同一数据的读取是一致的不受其他事务的影响。但是可能会出现幻读问题。序列化最严格的隔离级别它通过对所有事务进行串行化执行来保证事务的隔离性。这种隔离级别能够避免所有并发问题但是会对数据库性能产生较大影响。
在选择隔离级别时需要根据应用的实际情况和数据安全性要求来选择即需要权衡数据安全性和数据库性能。通常情况下可重复读已经能够满足大部分应用需求。
脏读、不可重复读、幻读 处理脏读在写的过程中别人就不能读了加锁的状态等修改完了之后别人才能读解除加锁 处理不可重复读给读加锁读的时候不能写就解决了不可重复读的问题。 处理幻读串行化完成事务即写——读——写——读 一个事务在执行中读到另一个执行中事务的更新 ( 或其他操作 ) 但是未 commit 的数据这种现象叫做脏读 同一个事务内同样的读取在不同的时间段(依旧还在事务操作中 ) 读取到了不同的值这种现象叫做不可重复读 数据在可重复读情况被读取出来导致多次查找时会多查找出来新的记录就如同产生了幻觉。这种现象叫做幻读
