MySQL索引分类、使用、特点及原理，存储引擎概述

索引的介绍
索引是通过某种算法，构建出一个数据模型，用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找到相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。
索引类似一本书的目录，比如要查找'student'这个单词，可以先找到s开头的页，然后向后查找，这个就类似索引。
索引的分类
索引是存储引擎用来快速查找记录的一种数据结构，
1、按照实现的方式分类，主要有 Hash索引和 B+Tree索引
2、按照功能的方式划分：单列索引，组合索引，全文索引，空间索引
单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引；
普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

添加索引方式
#数据准备
drop table if exists student;
create table student(
    sid int primary key,
    card_id varchar(50),
    name varchar(50),
    gender varchar(50),
    age int,
    brith date,
    phone_num varchar(20),
    score int    
);
1、create table student(
...
     index index_name(name)--给name列创建索引
)
2、直接创建
create index index_gender on student(gender);
3、修改表结构（添加索引）
alter table student add index index_age(age);

如果是主键的话，会自动创建索引
1、查看数据库所有的索引
SELECT * FROM mysql.innodb_index_stats a WHERE a.database_name='数据库名';
2、查看表中的所有索引
SELECT * FROM mysql.innodb_index_stats a WHERE a.database_name='数据库名' and a.table_name like '%表名%';
3、查看表中的所有索引
show index from table_name;
4、删除索引
drop index index_gender on student;
alter table student drop index index_age;


单列索引中的唯一索引(unique)
唯一索引：唯一索引与前面的普通索引类似，不同的是：索引列的值必须是唯一的，但允许有空值，如果是组合索引，则列值的组合必须唯一，它有以下几种创建方式：
#可以统一都添加index，第二种方式index是不能省略的
1、create table student(
...
     unique (index) index_name(name)--给name列创建索引
)
2、直接创建
create unique index index_gender on student(gender);
3、修改表结构（添加索引）
alter table student add unique (index) index_age(age);
4、删除索引
drop index index_gender on student;
alter table student drop index index_age;

单列索引中的主键索引
每张表一般都有自己的主键，当我们在创建表时，MySQL会自动在主键列上建立一个索引，这就是主键索引。主键索引是具有唯一性并且不允许为null，所以他是一种特殊的唯一索引。组合索引
组合索引也叫复合索引，指的是我们在建立索引的时候使用多个字段，例如同时使用身份证和手机号建立索引，同样的可以建立为普通索引或者唯一索引。

复合索引使用复合最左原则。

#创建索引的基本语法
create index indexname on table_name(column(length),column(length));
#例子
create index index_phone_name on student(phone_num,name);
#唯一复合索引（两列值形成唯一值，可以一个重复，但不能两个都重复）
create unique index index_phone_name on student(phone_num,name);

(1)select * from student where name='张三';
(2)select * from student where phone_num='15090878890';
(3)select * from student where phone_num='15090878890' and name='张三';
(4)select * from student where name='张三' and phone_num='15090878890';
因为复合索引使用最左原则，所以第一种情况不会使用到索引，因为条件里面必须包含索引前面的字段才能够进行匹配。
2、3、4都能使用到索引index_phone_name
第4种情况也会用到索引，是因为mysql本身就有一层sql优化，他会根据sql来识别出来该用哪个索引，我们可以理解为3和4在mysql眼中是等价的。

全文索引
全文索引的关键字是 fulltext
全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，基于相似度的查询，而不是简单的where语句的参数匹配。
用like+% 就可以实现模糊匹配了，为什么还要全文索引？
like+% 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like+% 快N倍，速度不是一个数量级的，但是全文索引可能存在精度问题。
全文索引的版本，存储引擎，数据类型的支持情况：
1、mysql 5.6以前的版本，只有myisam存储引擎支持全文索引；
2、mysql 5.6及以后的版本，myisam和innodb存储引擎均支持全文索引；
3、只有字段的数据类型为char、varchar、text及其系列才可以建全文索引；
4、在数据量较大时，现将数据放入一个没有全文索引的表中，然后再用create index创建 fulltext索引，要比先为一张表建立 fulltext然后再将数据写入的速度快很多；
5、测试或使用全文索引时，要先看一下自己的mysql版本，存储引擎和数据类型是否支持全文索引。
MySQL的全文索引中，有两个变量，最小搜索长度和最大搜索长度。对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。如果一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。
#这两个默认值可以使用以下命令查看：
show variables like '%ft%';
ft_max_word_len                  84              
ft_min_word_len                  4               
             
innodb_ft_max_token_size         84              
innodb_ft_min_token_size         3  

#1、创建表的时候添加全文索引
create table t_article(
    id int primary key auto_increment,
    title varchar(255),
    content varchar(1000),
    writing_date date,
    fulltext(content) --创建全文索引(但是这种数据量大时，插入数据效率较低，不推荐)
);
#2、修改表结构添加全文索引
alter table t_article add fulltext index_content(content);
#3、添加全文索引
create fulltext index index_content on t_article(content);
#使用全文索引
select * from t_article where match(content) against('yo');     --没有结果，少于最小长度3
select * from t_article where match(content) against('you');    --有结果

空间索引（了解）
空间索引是对空间数据类型的字段建立索引，MySQL中的空间数据类型有4种，分别为geometry，point，linestring，polygon
mysql使用spatial关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。

索引的原理
索引本身比较大，不会全部存储在内存中，往往会以索引文件的形式存储在磁盘上。索引查找过程中会产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗会高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。，即索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

hash算法的原理
优点：通过字段的值计算的hash值，定位数据非常快
缺点：不能进行范围查找，因为散列表中的值是无序的，无法进行大小比较（如条件出现>,<等符合，无法提高检索效率）
二叉树算法的原理
特性：分为左子树、右子树和根节点，左子树比根节点的值要小，右子树比根节点的值要大
缺点：有可能产生不平衡，类似于链表的结构，就是检索都往一边靠，跟没有索引效果一样
二叉树，最多有两个子树，对于sum的求和不适合
平衡二叉树
特点
a、它的左子树和右子树都是平衡二叉树
b、左子树比中间小，右子树比中间大
c、左子树和右子树的深度之差绝对值不超过1
缺点：
a、插入数据需要旋转
b、支持范围查询，但回旋查询效率太低，比如要查询大于某个数，就会回旋
c、如果存放几百条数据的情况下，数高度越高，查询效率越慢
BTree树（B+Trees目前是大部分数据底层索引所应用的）分为 B-trees 和 B+Trees 
数据结构的可视化：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
B+trees 可视化网址：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
大大减少了磁盘扫描的次数，叶子节点保留了所有的数据，并且存在双向链条，以空间换时间
MYISAM 引擎使用B+Tree
MYISAM 引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
InnoDB引擎使用B+Tree
InnoDB的叶节点的data域存放的是数据，相比myISAM效率要高一些，但是比较占硬盘内存大小。
索引的特点
索引的优点：
1、大大加快数据的查询速度；
2、使用分组和排序进行数据查询时，可以显著减少查询时分组和排序的时间；
3、创建唯一索引，能够保证数据表中每一行数据的唯一性；
4、在实现数据的参考完整性方面，可以加速表和表之间的连接
索引的缺点：
1、创建索引和维护索引需要消耗时间，并且随着数据量的增加，时间也会增加
2、索引需要占据磁盘空间
3、对数据表中的数据进行增加、修改，删除时，索引也要动态维护，降低了维护的速度。
索引的创建原则
1、更新频繁的列不应设置索引
2、数据量小的表不要使用索引
3、重复数据多的字段不应设为索引（比如性别，一般重复的数据超过15%就不应该建索引）
4、首先考虑对where和order by涉及的列上建立索引

MySQL的存储引擎

1、数据库存储引擎数据库底层软件组织，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据。

2、不同的存储引擎提供不同的存储机制，索引技巧，锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。

3、用户可以根据不同的需求为数据表选择不同的存储引擎。

4、可以使用show engines 命令可以查看MySQL的所有执行引擎我们可以到默认的执行引擎是innoDB支持事务，行级锁定和外键。

引擎的分类

MyISAM：Mysql5.5之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务

InnoDB：事务型速记的首选引擎，支持ACID事务，支持行级锁定，MySQL5.5成为默认数据库引擎

Memory：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比

的内存空间。并且其内容会在MYSQL重新启动是会丢失。

Archive：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的

插入速度，但其对查询的支持相对较差

Federated：将不同的MySQL服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用

#查询当前数据库支持的存储引擎

SHOW ENGINES;

#查看当前默认的存储引擎

SHOW VARIABLES LIKE '%storage_engine%';

#查看某个表用了什么引擎（在显示结果里参数engine后面的就表示该表当前用的存储引擎）

SHOW CREATE TABLE student;

#创建新表时指定存储引擎

CREATE TABLE (...)ENGINE=MYISAM;

#修改数据库引擎

ALTER TABLE student ENGINE=INNODB;

ALTER TABLE student ENGINE=MYISAM;

#修改MySQL默认存储引擎方法

1、关闭mysql服务

2、找到mysql安装目录下的my.ini文件

3、找到default-STORAGE-ENGINE=innodb改为目标引擎

如：default-STORAGE-ENGINE=MYISAM

4、启动mysql服务