mysql数据库实训报告总结5博客 mysql数据库实训心得体会

文章目录

• 索引

• 索引的SQL语句
• 索引的执行过程
• 索引的底层原理
• 索引的设计原则
• 索引和SQL的优化

• 单表的查询执行过程和执行优化--简单查询
• 单表的查询执行过程和执行优化–简单查询+排序
• 多表查询--连接查询的执行过程以及优化
• 多表查询--连接查询代替子查询

• 索引的失效

索引

索引的SQL语句

这个表，我们是没有给创建索引的 看如下：

1. 创建表时，创建索引：
2. 删除这个索引：
3. 在已创建的表上添加索引：
   另外一种方式：

索引的执行过程

1. 创建对应的表 student，以及添加数据
2. 查询名叫宋金周的这位天才的信息出来：
3. 分析上面SQL的执行计划：（使用explain命令可以查看SQL的执行计划，分析SQL是否正确使用索引）
   分析上图：红框内的两个。一个是可能用到的索引是什么；第二个是在查询过程中使用到的索引是什么。都是NULL 也即：找了11条数据，没有使用到索引（在查询过程之中没有命中索引）rows：11，表示了查询了11条数据，进行了全表查询。
4. 接下来我要给这个name这个字段添加一个索引
5. 再分析上面SQL的执行计划：成功命中索引

索引的底层原理

MySQL支持两种索引，一种是B-树索引，一种是哈希索引。

MYSQL中的InNoDB存储引擎是基于B- 树的存储引擎，但是MySQL实际使用的是B+树作为索引结构。
关于树的这块可以详见这篇写的不错的博客：B树、B-树、B+树、B*树介绍，和B+树更适合做文件索引的原因。

简而言之，B-树是一种m阶平衡树，叶子节点都在同一层。且每一个节点存储的数据量是比较大的，索引在整个B-树上的层数是比较低的，基本上不超过三层。一个节点的大小设置为磁盘上一个块的大小，目的在于：进行节点的读取或者存取的过程中 保证磁盘I/O是最少的。

那么为什么MySQL采用B+树而非B-树作为存储结构的？或者说MySQL的索引为什么采用B+树？

1. B-树的每一个节点存储的都是 关键字和对应的存储数据的地址，B+树非叶子节点存储关键字 不存储数据地址的（数据和关键字是存储在叶子节点上的）。那么对于同样大小的非叶子节点上 B+树存储的关键字的数量就会更多，因此 从树的高度上来讲：B+树的高度要小于B-的，使用的磁盘I/O的次数就要少，其查询速度也就越快。
2. B-树 上的查询效率是不平衡的（离根节点近的数据 查询就快，远则慢），B+树的所有数据信息都是在叶子节点上存储的 ，耗时情况是均匀的。
3. 区间查找 B+树 天生优势，B+树的所有叶子节点是被连接成一个 有序的链表结构（找到一个节点从链表上遍历），因此做整表遍历和区间查询是比较容易的。B-树 每次都要从根节点开始的一个查询，其耗时是很大的。

哈希索引：其实现是由哈希表完成的，不能保证数据的有序。（也不能做范围查找）

主键索引、辅助索引、聚集索引、非聚集索引。

MySQL是支持一种存储引擎 插件式的使用，主要的存储引擎InNoDB和myisam。

MYISAM存储引擎–主键索引：

非叶子节点存储的是 相应的关键字（15、56、77、20、49等），叶子节点存的是关键字以及数据存储地址。通过这个地址就可以在右下角的那个表里面，找到存的是 一行为一条记录。

MYISAM存储引擎–辅助索引：

根据上面两个图，首先按照B+树搜索算法搜索索引，若指定的key存在，那么取出其data域的值，然后以data域的值为地址，读取相应数据记录。可以看出：MYISAM存储引擎，索引结构叶子节点存储 关键字和数据地址，也即索引关键字和数据没有在一起存放，体现在磁盘上 就是索引在一个文件存储，数据在另一个文件存储。例如一个user表，会在磁盘上 存储3个文件：user.frm(表结构文件)，user.MYD(表的数据文件)，user.MYI(表的索引文件)。MYISAM的索引方式也叫做是 非聚集索引（把数据和索引是不存放在一块的），这是为了与INNODB的聚集索引（把数据和索引是存放在一块的）进行区分。INNODB存储引擎–主键索引：

下面的一列就是一条记录，可以看到索引关键字和数据是一起存放在 叶子节点上的。非叶子节点存储的是主键信息。INNODB存储引擎–辅助索引：

如上图：其叶子节点存储的是 当前索引的关键字+主键的id。找到主键id之后，再到上面的那个主键索引（主键索引树）里面找到对应的行记录信息。主键索引树上可以看到：INNODB的索引关键字和数据都是在一起存放的，体现在磁盘上：例如一个user表，会在磁盘上 存储2个文件：user.frm(表结构文件)，user.idb(存储表的数据和索引)。MYISAM的索引方式也叫做是 非聚集索引（把数据和索引是不存放在一块的），这是为了与INNODB的聚集索引（把数据和索引是存放在一块的）进行区分。

INNODB的索引树 叶子节点包含了完整的数据记录，这种索引被称为是 聚集索引。因为INNODB的数据文件本身要按照主键聚集，所以INNODB要求表必须有主键（MYISAM可以没有），若是没有显示指定，那么MYISAM系统会自动选择一个 可以唯一标识数据记录的列作为主键；若是不存在这种列，那么MYSQL自动为INNODB表生成一个隐含字段作为主键，这个字段长度为6字节，类型为长整型。

索引的设计原则

1. 选择唯一性索引；或者说给区分度高的字段创建索引。唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。
2. 为经常需要排序、分组和多表联合操作的字段建立索引
   经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。
3. 为常作为查询条件的字段建立索引
   如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。
4. 限制索引的数目
   索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
5. 尽量使用数据量少的索引（如前缀索引，主要针对字符串索引）
   如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR（100）类型的字段进行全文。检索需要的时间肯定要比对CHAR（10）类型的字段需要的时间要多。
6. 尽量使用前缀来索引
   如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
7. 删除不再使用或者很少使用的索引
   表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
8. 对于多列索引，优先指定最左边的列集。

索引和SQL的优化

单表的查询执行过程和执行优化–简单查询

表的结构：

现在 查询一下SID=1的学生信息：

可能用到的两个索引，最后在SQL的执行过程中，通过INNODB存储引擎分析 最后用的主键索引上一条数据就可以来进行查询。现在想通过name='宋金周’来进行查询：

用的是辅助索引idx_name索引树，找到了 姓名为宋金周的对应主键id=1，找到主键id之后，再到上面的那个主键索引（主键索引树）主键id=1，里面找到对应的行记录信息。

现在如下：

用的是辅助索引idx_name索引树，找到了 姓名为宋金周的对应主键id=1，此时单单在辅助索引就可以找到 其结果主键id=1了，就不需要再去使用其他的索引了（不需要在主键索引上查找了）。

现在如下：通过宋金周这个名字查询到其年龄信息

用的是辅助索引idx_name索引树，找到了 姓名为宋金周的对应主键id=1，找到主键id之后，再到上面的那个主键索引（主键索引树）主键id=1，里面找到对应的行记录信息（年龄信息）。

单表的查询执行过程和执行优化–简单查询+排序

现在再建立一张orderlist表：

订单表里面数据如下：

两个用户，对应商品id，以及加入购物车的时间。里面也没有什么索引：

现在要求是：查询userid=1按照日期升序排序：

如下图所示：没有命中索引

现在给它添加一个索引：（userid是有重复的）

然后再执行一下上面的那个过程：

没有命中索引，但是重点在Using filesort，文件排序。上面的SQL语句的order by date使用到了对整个文件的排序（效率比较低）。我们可以这么做去避免这个过程：

再创建一个date索引：

然后再执行一下上面的那个过程：

还是没有命中索引：（强制使用索引id）

但是如上：我们还是使用到了 文件排序。但是我们这里若是强制使用索引idx_date的话：

这里是把所有数据都排了一遍，没有使用文件排序。

注：一个SQL在执行中，最多能用到一个索引。强制用到其中一个，另一个无法使用。（既要查询id，又要进行排序）此时就得使用联合索引了！（但是放置的位置 左右边是有很大区别的）

会先去执行where过滤条件里面的（优先级高），然后才会去执行order by的（优先级低）。也就是说，下面的创建联合索引的两个字段的先后顺序是很重要的，否则创建的索引是不一样的。如下的联合索引：首先以userid作为关键字创建一个索引，在userid相同的情况下，才可以相应的date做一个相应的排序过程。

下面创建联合索引：

但是没有命中（没有命中原因：判断用索引还是不用索引，哪个效率高？当这里的数据量大的时候 就可以自动会使用一个索引了）

于是强制使用联合索引，如上。注：这里创建一个联合索引（a,b）

现在看一下 下面三句SQL是否命中索引：（联合索引字段顺序 SID CID）

第一种：（第一个字段 可以命中索引）

第二种：（第二个字段 没有命中索引）

第三种：（共二个字段 命中索引）

多表查询–连接查询的执行过程以及优化

联表查询：

如上：student表是大表（数据量大），score表是小表（数据量小）。在进行多表联合查询时，MYSQL首先会判断那个表是小表 小表要进行全表扫描（创建索引是没有用的），而大表则要进行使用索引来提高查询效率（在有索引的情况下）。小表决定了查询次数，大表决定了查询时间。在我给上面的那个小表也建立索引的情况下，仍然进行的是 整表搜索。

下面的需求是：查询一下score=87的是哪个天才考的？其用户信息查出来

如上图所示：仅仅做一个联合查询，没有加where过滤条件，b仍旧是小表 整表查询 没有用到索引。大表命中索引。

如上：a怎么成了小表了呢？分析为：a表用where a.SID=1进行了一个过滤 由于我的student表进行了一个SID的相关索引创建，所以这里直接使用索引进行条件过滤。但是过滤完成之后，就1行数据，所以此刻的a成了小表，b反而成为了大表。于是下面的b的索引就可以去使用了。

注： 在链接查询中，大表小表的角色是不一定的，没有where字句，那么就按照表的行数来定；若有则按照条件过滤完的行数来定大小表的。上面SID=1 在b表里面是有两行数据的，所以rows=2；如下：

多表查询–连接查询代替子查询

子查询在多表联合查询时的效率是很低的，因为要产生中间表（子查询是有中间表的产生和销毁） 多表查询最好优化成连接查询，效率很高。

子查询的结果通过in给当前的这个SQL，返回的SID 相当于就给创建了一个中间表的过程，所以查询效率低于连接查询过程。

索引的失效

1. like后面的通配符在前面，索引会失效

 但是下面就是可以用到：

 但是下面就用不到索引：2. 没有使用联合索引的第一列、not in、！=、使用MySQL函数、类型转换和or等都无法使用到索引。
<<<<<<<<<<<<<<<<<<<<<<<<实际项目中表的设计<<<<<<<<<<<<<<<<<<<<<<<<
 第一步：先找实体：
 商品product：商品ID(pid)，商品名称(pname)，商品价格(pprice)，商品库存(pamount)
 用户user：用户ID(uid)，用户名称(uname)，用户密码(upwd)
 订单order：订单ID， 订单送货地址， 订单的商品信息第二步：再找实体和实体之间的关系 一对一 一对多 多对多
 用户 商品 没关系用户 =》 订单 一对多的关系
 uid uname upwd user
 100 曹操 111
 102 孙权 112
 103 刘备 113orderid orderaddr orderprice uid order
 87987234 陕西西安 125.0 100
 23784618 四川成都 40.0 100select * from user a inner join order b on a.uid = b.uid where a.uid=100
商品 订单 多对多的关系 =》一定要设计一张中间的表
 pid pname pprice pamount product
 100 鼠标 20.0 200
 101 笔记本 2000.0 100
 102 键盘 25.0 500orderid orderaddr orderprice uid order
 87987234 陕西西安 125.0 100
 23784618 四川成都 40.0 100
 87788888 河南郑州 25.0 103多对多的实体关系，设计的时候，要产生如下的中间表
 orderlist
 id pid orderid pcount pchoiceprice
 1 100 87987234 5 100.0
 2 100 23784618 2 40.0
 3 102 87987234 1 25.0
 4 103 87788888 1 25.0

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<