MySql初窥-糖果梦论坛

MySQL 是最流行的关系型数据库管理系统。

在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

1 MySQL底层存储

页 = 页目录 + 用户数据区域 + 页头

插入的数据存在用户数据区域里，插入后会按照主键排序。(如果没有主键:MySQL会默认为每条数据生成一个rowId，如果没有主键，那么就按这个rowId排序)

每一页会对数据进行分组，创建页目录

会为所有页的页目录再抽取一层形成目录页。重新用一个页记录这个。

就好比 1、1.1、1.1.1

假如1.1.1里存的是数据，那么1.1就是页目录，1.1.1就是目录页

Page是Innodb存储的最基本结构，也是Innodb磁盘管理的最小单位，与数据库相关的所有内容都存储在Page结构里。
每个数据页的大小为16kb。
查询数据库的时候，你查一条数据，他都是把这一页的数据都加载到内存里，而不是你查那一条，他把哪一条加载到内存。
每个Page使用一个32位（一位表示的就是0或1）的int值来表示，正好对应Innodb最大64TB的存储容量(16kb * 2^32=64tib)：64T

2 索引

2.1 主键索引

根据主键创建的索引：对上图简化一下，如下

根据主键查询的时候会走这个索引

或者根据其他索引查询完后，没有拿到全部数据(查询的字段不在)，只能查到数据的索引，这时候就得来主键索引里查了

2.2 联合索引(辅助索引)

create index_tb1_bcd on t1(b,c,d);

select * from t1 where c=2 and d=3;

这个查询不会走索引，会走全表扫描

因为要遵循最左前缀原则,联合索引是bcd，查询根据 ?cd 在索引第一步就卡住了，没法比较查询哪个分支。所以只能走全部扫描。

select * from t1 where b > 1;

回表:根据某个字段去数据里查数据。如果用联合索引查询的话，先根据联合索引查到数据的主键，然后再回表根据主键去查询数据。

这个查询不走索引,条件 b > 1 ，结果能查出来很多数据的索引，要进行很多次回表，所以不如，全部扫描

select b from t1 where b > 1;

覆盖索引。这个会走索引，因为要查询的字段b在联合索引里直接有。

select b from t1;

这个走联合索引

因为主键索引里，每页存储的数据肯定比辅助索引里每页存储的数据多(主键索引存的是全部数据)。所以走辅助索引查询的页数就少一点。(比如主键索引一共存了4页，而辅助索引里可能只存了3页)

3 扯皮

3.1 为什么主键建议用自增数字，不用uuid

1.uuid占用空间大，到时候页数很多。

2.在插入数据的时候，会根据主键进行排序，主键自增的话就自动排好序了。

3.2 如果没有表没有主键，那么B+树怎么建立。

1.MySQL自动会为每条数据加上一个rowId，当没有主键的时候，会用这个rowId去建索引(B+树)

3.3 MySQL中有哪些存储引擎

5.5之前默认MylSAM
5.5之后默认InnoDB，主要是因为InnoDB支持事务，其他不太了解

3.4 MySQL中有哪些类型索引

3.4.1 普通索引

最基本的索引，它没有任何限制，用于加速查询。

1:建表的时候一起创建
CREATE TABLE mytable ( name VARCHAR(32) , INDEX index_mytable_name (name) );
2:建表后，直接创建索引
CREATE INDEX index_mytable_name ON mytable(name);
3:修改表结构
ALTER TABLE mytable ADD INDEX index_mytable_name (name);

3.4.2 主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

1:建表的时候一起创建
CREATE TABLE mytable ( `id` int(11) NOT NULL AUTO_INCREMENT , `name` VARCHAR(32) , PRIMARY KEY (`id`) );
2:修改表结构
ALTER TABLE test.t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id);

注：如果是字符串字段，还可以指定索引的长度，在列命令后面加上索引长度就可以了（例如:name(11)）

3.4.3 唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

1:建表的时候一起创建
CREATE TABLE mytable ( `name` VARCHAR(32) , UNIQUE index_unique_mytable_name (`name`) );
2:建表后，直接创建索引
CREATE UNIQUE INDEX index_mytable_name ON mytable(name);
3:修改表结构
ALTER TABLE mytable ADD UNIQUE INDEX index_mytable_name (name);

注：如果是字符串字段，还可以指定索引的长度，在列命令后面加上索引长度就可以了（例如:name(11)）

3.4.4 组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

1:建表的时候一起创建
CREATE TABLE mytable ( `id` int(11) , `name` VARCHAR(32) , INDEX index_mytable_id_name (`id`,`name`) );
2:建表后，直接创建索引
CREATE INDEX index_mytable_id_name ON mytable(id,name);
3:修改表结构
ALTER TABLE mytable ADD INDEX index_mytable_id_name　(id,name);

3.4.5 全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。

fulltext索引配合match against操作使用，而不是一般的where语句加like。

它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。

1:建表的时候一起创建
CREATE TABLE `article` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(250) NOT NULL , `contents` text NULL , `create_at` int(10) NULL DEFAULT NULL , PRIMARY KEY (`id`), FULLTEXT (contents) );
2:建表后，直接创建索引
CREATE FULLTEXT INDEX index_article_contents ON article(contents);
3:修改表结构
ALTER TABLE article ADD FULLTEXT INDEX index_article_contents　(contents);