MySQLStudy04

发表于 | 分类于
字数统计: 2.5k 字 | 阅读时长 ≈ 9 分钟

B+树索引的使用

使用索引的代价

  • 空间上的代价：比较索引是一个16KB大小的类似于目录的文件，所以总会占用一些空间。
  • 时间上的代价：时间上会增加一些，毕竟无端增加一个东西。

==使用索引时要谨慎==

SQL语句查询和效率一点关系都没有，因为MySQL中有查询优化的东东。

使用B+树索引时必须要存在前面能查得到一样的，后面才能进行相应的查找，如：

1
2
As% 能使用索引
%As% 就不能，因为前面没有一样的字符

不能使用索引进行排序情况

  • ASC、DESC混用

==ORDER BY子句后的列如果不加ASC或者DESC默认是按照ASC排序规则排序的，也就是升序排序的==

  • WHERE子句中出现非排序使用到的索引列，如果按照顺序的话，索引能用，如果不是的话就不行，如：
1
2
3
4
5
6
7
8
9
10
11
前提创建一个表
mysql> CREATE TABLE person_info(
    ->     id INT NOT NULL auto_increment,
    ->     name VARCHAR(100) NOT NULL,
    ->     birthday DATE NOT NULL,
    ->     phone_number CHAR(11) NOT NULL,
    ->     country varchar(100) NOT NULL,
    ->     PRIMARY KEY (id),
    ->     KEY idx_name_birthday_phone_number (name, birthday, phone_number)
    -> );
Query OK, 0 rows affected (0.06 sec)
1
2
3
SELECT * FROM person_info WHERE country = 'China' ORDER BY name LIMIT 10; 不能使用索引，顺序不一致

SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10; 可以，顺序一致
  • 排序列包含非同一个索引的列:如
1
2
3
4
5
6
7
8
9
10
11
12
13
前提还是同一个表：
mysql> CREATE TABLE person_info(
    ->     id INT NOT NULL auto_increment,
    ->     name VARCHAR(100) NOT NULL,
    ->     birthday DATE NOT NULL,
    ->     phone_number CHAR(11) NOT NULL,
    ->     country varchar(100) NOT NULL,
    ->     PRIMARY KEY (id),
    ->     KEY idx_name_birthday_phone_number (name, birthday, phone_number)
    -> );
Query OK, 0 rows affected (0.06 sec)

SELECT * FROM person_info ORDER BY name, country LIMIT 10;不行，不能使用索引，顺序不一致
  • 排序列使用了复杂的表达式：如
1
SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;
  • 用于分组：如
1
SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number

回表的教训：使查询优化性能降低，原先使用的顺序I/O变成了随机I/O，大大毁坏了优化查询器的存在。

解决回表的教训：覆盖索引，最好在查询列表里只包含索引列，如

1
ELECT name, birthday, phone_number FROM person_info WHERE name > 'Asa' AND name < 'Barlow'

二级索引 + 回表使用情况：由MySQL优化查询器决定

索引的选择

  • 只为用于搜索、排序或分组的列创建索引
  • 考虑列的基数，最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。
  • 索引列的类型尽量小
  • 索引字符串值的前缀
  • 让索引列在比较表达式中单独出现，如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。
  • 主键插入顺序
  • 冗余和重复索引

B+树索引适用于下边这些情况：

  • 全值匹配
  • 匹配左边的列
  • 匹配范围值
  • 精确匹配某一列并范围匹配另外一列
  • 用于排序
  • 用于分组

MySQL的数据目录

InnoDB引擎：InnoDB其实是使用页为基本单位来管理存储空间的，默认的页大小为16KB。

数据目录：MySQL服务器程序在启动时会到文件系统的某个目录下加载一些文件，之后在运行过程中产生的数据也都会存储到这个目录下的某些文件中

数据目录存放位置，如下所示

1
2
3
4
5
6
7
8

mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+---------------------------------------------+
| Variable_name | Value                                       |
+---------------+---------------------------------------------+
| datadir       | C:\ProgramData\MySQL\MySQL Server 5.7\Data\ |
+---------------+---------------------------------------------+
1 row in set, 1 warning (0.01 sec)

MySQL数据文件

InnoDB引擎存放数据表空间分类

  • 系统表空间：表名.frm(MySQL8.0之后就没有.frm文件了，都放在了系统表空间里),解释
1
2
3
4
5
6
.frm files: Table metadata files. With the removal of .frm files:


- The 64KB table definition size limit imposed by the .frm file structure is removed.

- The INFORMATION_SCHEMA.TABLES VERSION column reports a hardcoded value of 10, which is the last .frm file version used in MySQL 5.7.
  • 独立表空间：表名.ibd
  • 两者之间相互转化
1
2
3
4
5
系统表空间中的表转移到独立表空间
ALTER TABLE 表名 TABLESPACE [=] innodb_file_per_table;

独立表空间的表转移到系统表空间
ALTER TABLE 表名 TABLESPACE [=] innodb_system;
  • 其他类型表空间：通用表空间（general tablespace）、undo表空间（undo tablespace）、临时表空间（temporary tablespace）…

MyISAM引擎存放数据:表数据都存放到对应的数据库子目录下

1
2
3
test.frm
test.MYD
test.MYI

==test.MYD代表表的数据文件，也就是我们插入的用户记录；test.MYI代表表的索引文件，我们为该表创建的索引都会放到这个文件中==

其他文件：服务器进程文件、服务器日志文件、默认/自动生成的SSL和RSA证书和密钥文件(主要是为了客户端和服务器安全通信而创建的一些文件)

InnoDB表空间

表空间也页为单位，页以区为单位，区以段为单位。

区的分类

  • 空闲的区：现在还没有用到这个区中的任何页面。（FREE状态）

  • 有剩余空间的碎片区：表示碎片区中还有可用的页面。（FREE_FRAG状态）

  • 没有剩余空间的碎片区：表示碎片区中的所有页面都被使用，没有空闲页面。（FULL_FRAG状态）

  • 附属于某个段的区。每一个索引都可以分为叶子节点段和非叶子节点段，除此之外InnoDB还会另外定义一些特殊作用的段，在这些段中的数据量很大时将使用区来作为基本的分配单位。（FSEG状态）

==处于FREE、FREE_FRAG以及FULL_FRAG这三种状态的区都是独立的，算是直属于表空间；而处于FSEG状态的区是附属于某个段的。==

==表空间比作是一个集团军，段就相当于师，区就相当于团。一般的团都是隶属于某个师的，就像是处于FSEG的区全都隶属于某个段，而处于FREE、FREE_FRAG以及FULL_FRAG这三种状态的区却直接隶属于表空间，就像独立团直接听命于军部一样。==

数据库连接

连接本质：各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户

记录连起来组成新的更大的记录,所以这个查询过程称之为连接查询,结果集就可以称之为笛卡尔积

连接过程

  • 单表条件：搜索条件
  • 1
    2
    比如t1.m1 > 1是只针对t1表的过滤条件，
    t2.n2 < 'd'是只针对t2表的过滤条件

  • 两表条件：

1
SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

==需要查询的表，这个表称之为驱动表==

连接查询

1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM student, score WHERE student.number = score.number;
+----------+-----------+--------------------------+----------+-----------------------+-------+
| number   | name      | major                    | number   | subject               | score |
+----------+-----------+--------------------------+----------+-----------------------+-------+
| 20180101 | 杜子腾    | 软件学院                 | 20180101 | 母猪的产后护理        |    78 |
| 20180101 | 杜子腾    | 软件学院                 | 20180101 | 站在                  |    88 |
| 20180102 | 范统      | 计算机科学与工程         | 20180102 | 站在                  |    98 |
| 20180102 | 范统      | 计算机科学与工程         | 20180102 | 母猪的产后护理        |   100 |
+----------+-----------+--------------------------+----------+-----------------------+-------+
4 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
mysql> SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1, score AS s2 WHERE s1.number = s2.number;
+----------+-----------+-----------------------+-------+
| number   | name      | subject               | score |
+----------+-----------+-----------------------+-------+
| 20180101 | 杜子腾    | 母猪的产后护理        |    78 |
| 20180101 | 杜子腾    | 站在                  |    88 |
| 20180102 | 范统      | 站在                  |    98 |
| 20180102 | 范统      | 母猪的产后护理        |   100 |
+----------+-----------+-----------------------+-------+
4 rows in set (0.00 sec)

连接种类：

  • 内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上边提到的连接都是所谓的内连接
  • 外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。（左外连接
    选取左侧的表为驱动表。
    右外连接
    选取右侧的表为驱动表。）

==我们都把只涉及单表的过滤条件放到WHERE子句中，把涉及两表的过滤条件都放到ON子句中，我们也一般把放到ON子句中的过滤条件也称之为连接条件==

1
2
3
4
5
6
7
8
9
10
11
mysql>  SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1 LEFT JOIN score AS s2 ON s1.number = s2.number;
+----------+-----------+-----------------------+-------+
| number   | name      | subject               | score |
+----------+-----------+-----------------------+-------+
| 20180101 | 杜子腾    | 母猪的产后护理        |    78 |
| 20180101 | 杜子腾    | 站在                  |    88 |
| 20180102 | 范统      | 站在                  |    98 |
| 20180102 | 范统      | 母猪的产后护理        |   100 |
| 20180103 | 史珍香    | NULL                  |  NULL |
+----------+-----------+-----------------------+-------+
5 rows in set (0.00 sec)

左（外）连接语法

1
SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

右（外）连接语法：

1
SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

内连接语法：

1
2
3
4
5
6
7
8
9
SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];

也就是说在MySQL中，下边这几种内连接的写法都是等价的：

SELECT * FROM t1 JOIN t2;

SELECT * FROM t1 INNER JOIN t2;

SELECT * FROM t1 CROSS JOIN t2;

==由于在内连接中ON子句和WHERE子句是等价的，所以内连接中不要求强制写明ON子句==

连接原理

驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为嵌套循环连接

单表访问方法

MySQL Server有一个称为查询优化器的模块:执行计划

查询方式：使用全表扫描进行查询、使用索引进行查询（采用二级索引来执行查询的访问方法称为：ref）

使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为ref_or_null

------------------------------------本文结束感谢您的阅读--------------------------------
wangyekun wechat
扫一扫关注一波
坚持原创，您的支持将鼓励我继续创作！