MySQL存储引擎与索引

• 存储引擎分类和比较
• 索引分类
• MyISAM索引结构
• InnoDB索引结构
• 性能优化注意点

存储引擎分类和比较

存储引擎分类

1、InnoDB存储引擎
2、MyISAM存储引擎
建表时在ENGINE处设置

CREATE TABLE user (
    id INT,
) ENGINE = InnoDB;

存储引擎比较

支持事务

• InnoDB自动添加事务
• MyISAM不支持事务

BEGIN;
  SELECT * FROM student;
  INSERT INTO student value(1);
COMMIT;

全文索引

• 类似搜索引擎，对文章进行索引
• MyISAM支持，InnoDB不支持
• 关键词：FULLTEXT INDEX

CREATE TABLE student (
    id INT,
    introduce TEXT,
    FULLTEXT INDEX fulltextIDX(introduce)
) ENGINE=MyISAM;

外键

• A表中的主键M，B表中作为一个属性出现，M为B的外键
• MyISAM不支持，InnoDB支持

CREATE TABLE student (
    id INT,
) ENGINE=InnoDB;

CREATE TABLE school (
    id INT,
    uid INT
) ENGINE=InnoDB;

ALTER TABLE `school` FOREIGN KEY (`uid`) REFERENCES `student`(`id`);

锁级别

• MyISAM到表锁
• InnoDB到行锁，有更细致的事务隔离级别

索引结构

• MyISAM非聚集索引，B+树结构，叶子节点为地址
• InnoDB聚集索引，B+树结构，叶子节点为数据

索引分类

按功能进行索引分类

• 主索引/主键索引/一级索引：使用主键，建表后自动生成，无法修改，如无主键则MySQL会内置一列主键
• 辅助索引/非主索引：人为添加的除主索引外的其他索引
  
  
• 唯一索引：列的值唯一，插入数据时会进行检查，一个表中可以有多个唯一索引
• 外键索引：如果表中存在外键，则会自动在该表中加入外键索引
• 全文索引：类似搜索引擎，可以做LIKE %word%的模糊匹配，只有MyISAM支持
• 复合索引/混合索引/连接索引：本质是多列索引，其中列的顺序非常重要
  
  
• 单列索引：是个统称。上述的主码索引、唯一键索引、外键索引、聚集主码、聚集索引、主索引等等，除了多列索引和聚合索引其他都属于单列索引。
• 普通索引：不是主索引、唯一索引等的索引。

按结构进行索引分类

• 聚集索引：叶子节点为数据
• 非聚集索引：叶子节点为地址

MyISAM索引结构

MyISAM索引简介

• MyISAM为非聚集索引
• 主索引：默认为主键，建表后自动生成，无法修改
• 辅助索引：人为添加

MyISAM主索引结构图

默认为主键，建表后自动生成

MyISAM辅助索引结构图

人为添加，同样指向数据

InnoDB索引结构

InnoDB索引简介

• InnoDB为聚集索引
• 主索引：默认为主键，建表后自动生成
• 辅助索引：人为添加，指向主索引

InnoDB主索引结构图

默认为主键，建表后自动生成

InnoDB辅助索引结构图

人为添加，指向主索引

InnoDB不建议使用过长字段主键

所有辅助索引都使用了主索引，过长的主索引会令辅助索引文件变得过大

InnoDB建议使用递增字段作为主键

非递增字段会导致 B+ Tree 在插入新纪录时频繁的分裂调整，降低效率

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT
) ENGINE=MyISAM;

索引结构图比较

性能优化注意点

尽量使用主键进行查询

主索引能够极大提高效率

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT
) ENGINE=MyISAM;

SELECT * FROM student WHERE id > 5;

对于经常查询但非主键字段，要建立辅助索引

主索引 > 辅助索引 > 无索引

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR,
    age INT,
    INDEX(name)
) ENGINE=MyISAM;

SELECT * FROM student WHERE name = `zhangsan`;

避免全表扫描

表中存在 null 会导致全表扫描

SELECT id FROM student WHERE name is null;

//可以考虑用0代替null

查询语句存在 != 或 <> 操作符，会导致全表扫描

SELECT id FROM student WHERE name != `zhangsan`;

左模糊查询会导致全表扫描

SELECT id FROM student WHERE name like `%abc`; //会

SELECT id FROM student WHERE name like `abc%`; //不会

不要用*返回所有列

数据量大时，会出现问题，用哪列则查哪列

SELECT * FROM student;

当只要一行数据时使用 LIMIT 1

MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据

SELECT id FROM student WHERE name LIKE `abc%` LIMIT 1;

索引并非越多越好

• 索引可以提高 select 的效率，但也降低了 insert 及 update 的效率
• 因为需要重建索引，同时占用磁盘空间

尽量使用数字型字段

• 若只含数值信息的字段尽量不要设计为字符型
• 会降低查询和连接的性能，并会增加存储开销。

// ip地址的存储
mysql> SELECT inet_aton('255.255.255.255');
+------------------------------+
| inet_aton('255.255.255.255') |
+------------------------------+
|                   4294967295 |
+------------------------------+
1 row in set (0.00 sec)

多用EXPLAIN SELECT跟踪查询效果

• 使用 EXPLAIN 关键字可以让你知道 MySQL 是如何处理你的 SQL 语句的
• 其中 type 字段能够告诉你是否为全表扫描

mysql> EXPLAIN SELECT * FROM student WHERE id = 2;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

参考资料

• MySQL 性能优化神器 Explain 使用分析
• 这大概是最全的sql优化方案了
• MySQL中应该怎样存储IP地址
• 索引分类
• MySQL的MyISAM与InnoDB的索引方式
• InnoDB记录存储结构
• MySQL的索引
• MySQL的索引（中）