mysql - 索引原理 - 《Java 理论笔记》

索引类型
索引原理
- B+树
  - b-Tree
  - b+Tree
- 聚簇索引和辅助索引
索引分析和优化
查询优化

索引类型

从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
从应用层次划分：普通索引、唯一索引、主键索引、复合索引
从索引键值类型划分：主键索引、辅助索引（二级索引）
从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

普通索引

在一个普通字段上添加的索引

CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名) );

唯一索引

索引字段的值必须唯一，允许有空值

CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( […], UNIQUE [索引的名字] (字段名) ;

主键索引

特殊的唯一索引，不允许有空值，添加主键约束即可

CREATE TABLE tablename ( […], PRIMARY KEY (字段名) );
ALTER TABLE tablename ADD PRIMARY KEY (字段名);

复合索引

在多个普通字段上添加的索引

CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2…);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2…);
CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名1，字段名2…) );

全文索引

mysql5.6之前，只有MyISAM存储引擎支持全文索引，
mysql5.6及之后，MyISAM和InnoDB存储引擎均支持

CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE tablename ( […], FULLTEXT KEY [索引的名字] (字段名) ;

查询语句

select * from user where match(name) against('aaa');

索引原理

MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。
索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价

设计的理论知识：二分查找法、hash、B+树

B+树

b-Tree

索引值和data数据分布在整棵树结构中
每个节点可以存放多个索引值及对应的data数据
树节点中的多个索引值从左到右升序排列

b+Tree

非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
叶子节点包含了所有的索引值和data数据
叶子节点用指针连接，提高区间的访问性能

优势：
相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

聚簇索引和辅助索引

聚簇索引和非聚簇索引：

聚簇索引: B+Tree的叶子节点存放主键索引值和行记录
非聚簇索引: B+Tree的叶子节点分开存放索引值和行记录

主键索引和辅助索引：

主键索引：B+Tree的叶子节点存放的是主键字段值
辅助索引（二级索引）：B+Tree的叶子节点存放的是非主键值

InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据

InnoDB表为聚簇索引结构存储，必须要有主键

MyISAM数据表为非聚簇索引结构存储

索引分析和优化

性能分析语句

explain select * from user where id <3

select_type
表示查询的类型
type
表示存储引擎查询数据时采用的方式
possible_keys
表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。
key
表示查询时真正使用到的索引，
rows
MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录
key_len
表示查询使用了索引的字节数量。
Extra
Extra表示很多额外的信息，

查询优化

慢查询定位

查看mysql数据库是否开启慢查询日志和慢查询日志文件的存储位置的命令如下

SHOW VARIABLES LIKE 'slow_query_log%'

开启慢查询日志：

SET global slow_query_log = ON;
SET global slow_query_log_file = 'OAK-slow.log'; 
SET global log_queries_not_using_indexes = ON; #表示会记录没有使用索引的查询SQL
SET long_query_time = 10; #慢查询阈值，单位秒

查看慢查询日志：

文本方式查看
使用mysqldumpslow查看
使用第三方分析工具查看，pt-query-digest、mysqlsla

慢查询优化

判断是否使用索引，
提高索引的过滤性，减少扫描行数

分页查询优化

深分页

select from student limit 10000,100;
优化：
select from student where id > =10000 limit 100;