存储引擎

MySQL中的存储引擎

  • 查看存储引擎：show engines;
  • 查看mysql文件存储位置
    show variables like ‘%dir%’;

    datadir下存储的是数据文件。

Innodb和MyISAM比较

  1. 建两张表：student和teacher
    student使用的引擎为Innodb，teacher使用MyISAM。 
create table student
(
	id int(11) not null primary key,
	uname varchar(20),   
	address  varchar(20),   
	age  int(11)
)   
ENGINE=INNODB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci   
AUTO_INCREMENT=1   
ROW_FORMAT=COMPACT; 

create table teacher
(
	id int(11) not null primary key,
	uname varchar(20),   
	address  varchar(20),   
	age  int(11)
)   
ENGINE=MYISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci   
AUTO_INCREMENT=1   
ROW_FORMAT=COMPACT;
  1. 查看数据文件
  • .frm文件是表结构等定义文件。Innodb和MyISAM都使用该类型文件。
  • MyISAM将索引和数据分开存储了，.MYD是数据存储文件，.MYI是索引文件。所以在MyISAM中查找数据时，会先在索引文件中找到对应的数据存储位置，然后再到数据文件中取出对应数据。
  • Innodb将索引和数据存储在一个文件中了，就是.ibd文件。

为什么Innodb和MyISAM存储方式不同？这其实和其索引的使用有关，下面就有答案。

索引

主要讲Innodb和MyISAM

为什么选择B- tree/B+ tree作为索引的数据结构？

我们先看看能用于索引的数据结构：

  1. 二叉搜索树：代码实现
    二叉搜索树可以使用在数据查询，其时间平均复杂度为O(logn)。但是当数据递增插入的时候二叉树会变为线性链表，时间复杂度会变为O(n)。即使使用平衡二叉搜索树或者红黑树，因为每个节点只会有两个子节点，所以在数据量很大的时候树的深度会很深，这样查询一条数据就会要执行很多次IO操作，IO操作是很耗时的。所以二叉搜索树并不适合作为索引。
  2. 哈希表
    哈希表查询的时间复杂度理想情况下是O(1)。但是在实际使用的时候难免会存在hash冲突，在冲突的时候，数据存储就需要转化为链表或者树的形式。而且使用哈希表作为索引，需要在内存中进行查询，这样当数据量很大时会很耗内存。
    弊端：
    • 会存在hash冲突。
    • 数据全部在内存中才能搜索，浪费内存。
    • 不支持范围查询

选择B+ tree的前提：

  1. 局部性原理：总体意思就是你访问的磁盘块的周围的内容最有可能被下次访问。
    a. 时间局部性（Temporal Locality）：如果一个信息项正在被访问，那么在近期它很可能还会被再次访问。
    b. 空间局部性（Spatial Locality）：在最近的将来将用到的信息很可能与正在使用的信息在空间地址上是临近的。
    c. 顺序局部性（Order Locality）：在典型程序中，除转移类指令外，大部分指令是顺序进行的。顺序执行和非顺序执行的比例大致是5:1。此外，对大型数组访问也是顺序的。

因为局部性原理，所以CPU会预读一部分数据：

  1. 磁盘预读：磁盘的顺序读写会很快，可以媲美内存。所以磁盘预读一部分数据耗时很小。操作系统中，预读数据最小单位为页，系统每次预读数据都为页的整数倍，预读数据=N*PageSize。一般操作系统的页大小为4k。

B-TREE和B+TREE结构

degree：阶，就是每个节点最大能存储数据的个数。

B-TREE

B+TREE

B+TREE会将数据都存储在叶子节点上，并组成链表结构。

根据局部性原理和磁盘预读设计索引

  1. 将索引块的大小设计为16K，结构如下:

    为什么索引块大小16K?
    假设使用int作为索引，一个索引占4byte，地址占用量忽略不计，则16K可以存储161024/4=4096个索引。则层数为2的树就能存储40964096=16777216条索引数据，这已经完全满足单表数据量了。所以选择16K为预读数据大小，即满足了要求，又减少了一次IO数据量。

B-TREE索引

下图是B-tree的结构，b-tree的每个节点存储数据为：索引+数据地址+下个索引块地址。对b-tree进行改良，可以将数据地址存储在树的叶子节点，这样索引块就能存储更多的索引。

B+TREE索引

B+TREE会将所有的索引都在叶子节点上存储，数据会存储在data上。

  • MyISAM
    MyISAM将索引和数据进行了分开存储。在叶子节点上，data存储的是该条数据对应的地址。在MyISAM中，通过索引找到数据存储地址，然后再到数据文件中读取数据返回。

    • 主键索引和普通索引结构相同
    • 索引的叶子节点储存数据记录的地址/指针

  • Innodb
    Innodb中主键索引叶子节点data中存储的是整行数据，所以Innodb只是用一个文件存储索引和数据。非主键索引中的data存储的是主键值。

    • 主键索引的叶子节点存储整行数据。
    • 普通索引叶子节点存储id，所以通过普通索引检索，要经过主键索引再次查询到数据，即回表。

怎么建索引最好？

1. 数据的离散度
数据离散程度高的列建立索引才能更好的发挥B+tree的优势。
查询数据的离散度：

select count(distinct column_name)/count(*) from table_name;

离散度越高，说明数据重复的越少，越适合建索引。
以uname和age为例：

uname列的离散度为0.9987，age列的数据离散度为0.0000。uname的数据离散度很高，很适合建立索引，而age几乎全是重复的数据，索引极不适合建立索引。

2. 前缀索引
因为每个节点空间为16K，所以索引列的数据要越小越好，如果使用长字符串，每个节点能存储的索引越少，这样树的深度就越深，检索数据所需要的IO操作就越多。如果需要长字符串作为索引，我们可以建立前缀索引。建立前缀索引也要满足数据离散度的要求，尽量建立数据离散度高的前缀索引。
以表student为例，使用address建立索引：

create table student
(
	id int(11) not null primary key,
	uname varchar(20),   
	address  varchar(20),   
	age  int(11)
)   
ENGINE=INNODB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci   
AUTO_INCREMENT=1;
  • 方式一：查询前缀冲突的数量前10条： 
select distinct left(address,5) pref,count(*) count from student group by pref order by count desc limit 10;


增加前缀长度，当count数据不在急剧下降的时候，前缀长度就可以定为最小的那个值。

  • 方式二：查看比重 
select 
	count(distinct left(address,5))/count(*) as sel5,
	count(distinct left(address,6))/count(*) as sel6,
	count(distinct left(address,7))/count(*) as sel7 
from student;


由数据可以看到，随着前缀长度的增加，比值却未大幅增加，则说明前缀长度6已经能满足高区分度了。所以可以建前缀长度为6的索引。

alter table student add index index_address (address(6));

索引分类

Innodb为例：
1. 主键索引
Innodb每个表都要有一个主键索引，如果不指定，Innodb会默认创建一个6byte的自增主键索引。主键索引的叶子节点上存储着整行数据。使用主键索引查询的效率最高。mysql建议使用自增序列作为主键。
2. 普通索引
普通索引是除了主键之外的列建的索引。普通索引的叶子节点存储的是id，通过普通索引查询到id，然后再根据id去主键索引查询数据。
3. 组合索引
组合索引是由多个列组成的索引。组合索引的使用要注意以下几点：
a. 数据离散度高的列排在前面。
b. 查询时索引中前面的列不能范围匹配，这样会导致后面的列失效。
4. 索引覆盖
索引覆盖是指：查询的数据就是索引数据，会直接返回，不会再进行回表查询。例如建一个idx_name_address的索引，我们只查name和address，即

select name,address from student where name = 'zhangsan' and address = 'beijing';

会直接返回name、address，不需要再根据id回表查询了。

其它

count(*)、count(1)和count(列名)谁更好？

使用count(*)更好。count(*)是SQL92定义的标准统计行数的语法，与数据库无关，跟NULL和非NULL无关。count(*)会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行。

如果根据URL进行检索，怎么提高查询效率？

url一般是很长的字符串，如果使用url建索引的话肯定是不合适的。如果使用前缀索引的话，url一般以域名开头，所以前缀也要建很长。这两种方法都不合适。
我们可以采用下面的方法：
新增一个列：url_crc。这个列存储的就是url进行crc32（循环冗余校验）的值，对url_crc建立索引，每次根据url查询时，就可以先计算url的crc32值，然后查询数据。但是crc32也会存在冲突的，所以单靠crc32值可能会查询到多条。则我们可以加上url匹配，最终sql：

select * from table_name where url = 'url' and url_crc = 'crc32';