MySQL索引

MySQL索引

1，索引

索引是帮助数据库（关系型、非关系型数据库）高效获取数据的 排好序的数据结构。
索引的作用：用于快速找出在某个列中有一特定值的行。
例如：查询千万条数据的表单：字段没有索引需要几十秒。有索引只要几百毫秒

不使用索引，MySQL必须从第一条记录开始遍历整个表，直到找出相关的行，表越大查询数据所花费的时间就越多。如果表中查询的列有索引，MySQL能够快速的定位到一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

例如：有一张person表，其中有2W条记录，记录着2W个人的信息。有一个Phone的字段记录每个人的电话号码，现在想要查询出电话号码为xxxx的人的信息。

如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止。

如果有了索引，那么会将 Phone 字段，通过一定的方法进行存储（如B+tree），快速查到该号码所在的行在表单中存储的位置，拿到位置直接去读取该行的信息，就不用遍历了，节省大量的便利时间。

其中MySQL中的索引的存储类型有两种：BTREE、HASH。 也就是用树或者Hash值来存储该字段，更详细的查找逻辑就需要会算法的知识了。

2，索引优、缺点

优点：

　　1、所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引。
　　2、大大加快数据的查询速度。

缺点：

1、创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
2、索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值。
3、当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。

使用原则：

通过上面说的优点和缺点，我们应该可以知道，并不是每个字段都设置为索引好，也不是索引越多越好，而是需要自己合理的使用。

1、对经常更新的表就避免对其设置过多的索引，对经常用于查询的字段应该创建索引。
2、数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
3、在一个列上(字段上)不同值较少的不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多的可以建立索引。

3，索引的分类

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引：

MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换。（但是innoDB存储引擎支持hash索引是自适应的，innoDB存储引擎会根据表的使用情况自动为表生成hash索引，不能人为干预是否在一张表中生成hash索引。后续再整理）

MEMORY、HEAP存储引擎：支持HASH和BTREE索引。

存储引擎的类型及特点：

引擎名称	优点	缺陷	应用场景
MyISAM	独立于操作系统，这说明可以轻松地将其从Windows服务器移植到Linux服务器	不支持事务/行级锁/外键约束	适合管理邮件或Web服务器日志数据
InnoDB	健壮的事务型存储引擎；支持事务/行级锁/外键约束自动灾难恢复/AUTO_INCREMENT		需要事务支持，并且有较高的并发读取频率
MEMORY	为得到最快的响应时间，采用的逻辑存储介质是系统内存	当mysqld守护进程崩溃时，所有的Memory数据都会丢失；不能使用BLOB和TEXT这样的长度可变的数据类型	临时表
MERGE	是MyISAM类型的一种变种。合并表是将几个相同的MyISAM表合并为一个虚表		常应用于日志和数据仓库
ARCHIVE	归档的意思，支持索引，拥有很好的压缩机制	仅支持插入和查询功能	经常被用来当做仓库使用

索引我们分为四类：单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引。

• 单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了。

  1、普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

  2、唯一索引：索引列中的值必须是唯一的，但是允许为空值，

  3、主键索引：是一种特殊的唯一索引，不允许有空值。

• 组合索引：一个的索引包含多个列，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀。会在后面的例子细说。

• 全文索引：要求只有在MyISAM引擎上才能使用，只能在CHAR、VARCHAR、TEXT类型字段上使用全文索引。就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 ..." 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节。

• 空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。

在创建空间索引时，使用SPATIAL关键字。

要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。具体细节看下面

4，MySQL索引使用

1、在创建表时创建索引

创建索引：单列索引（普通、唯一、主键）、组合索引、全文索引和空间索引。

格式：CREATE TABLE 表名[字段名 数据类型] [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length])

1、创建普通索引：

创建普通索引，创建索引时未指定索引的名，会自动帮我们用字段名当作索引名

CREATE TABLE book(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
author VARCHAR(20) NOT NULL,
info VARCHAR(255) NULL,
INDEX(author));

2，在创建表后创建索引

# MySQL中可以使用alter table这个SQL语句来为表中的字段添加索引。基本语法如下：
ALTER TABLE <表名> ADD INDEX (<字段1>);

# 举例：
# 1.添加PRIMARY KEY（主键索引） 
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`) 
# 2.添加UNIQUE(唯一索引) 
ALTER TABLE `table_name` ADD UNIQUE (`column`) 
# 3.添加INDEX(普通索引) ，添加多列索引 
ALTER TABLE `table_name` ADD INDEX index_name (`column`) 
ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`)
# 4.添加FULLTEXT(全文索引) 
ALTER TABLE `table_name` ADD FULLTEXT (`column`) 

3、删除索引

删除索引是指将表中已经存在的索引删除掉。不用的索引建议进行删除，因为它们会降低表的更新速度，影响数据库的性能。

1，使用 DROP INDEX 语句删除索引

# 语法格式：<索引名>：要删除的索引名。<表名>：指定该索引所在的表名。
DROP INDEX <索引名> ON <表名>

# 举例：删除表 tb_stu_info 中的索引，输入的 SQL 语句和执行结果如下所示。
mysql> DROP INDEX height ON tb_stu_info;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tb_stu_info\G
Table: tb_stu_info
Create Table: CREATE TABLE `tb_stu_info` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

2， 使用 ALTER TABLE 语句删除索引

# 语法如下：
DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
DROP INDEX index_name：表示删除名称为 index_name 的索引。
DROP FOREIGN KEY fk_symbol：表示删除外键。
# 注意：如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。

# 举例：删除表 tb_stu_info2 中名称为 id 的索引，输入的 SQL 语句和执行结果如下所示。
mysql> ALTER TABLE tb_stu_info2 DROP INDEX height;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tb_stu_info2\G
Table: tb_stu_info2
Create Table: CREATE TABLE `tb_stu_info2` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

5，总结

1、索引是干嘛的？为什么要有索引？

很重要，用来提高查询效率的。

2、索引的分类：单列索引（普通索引、唯一索引和主键索引）、组合索引、全文索引和空间索引。

3、索引的使用：给表中创建索引，添加索引，删除索引。

2，索引数据结构/原理：

1，二叉树：

二叉树索引：

当单边增长的时候，二叉树就相当于链表结构。

binary.png

左边的值小于右边的值。

二叉树作为索引方法/存储结构：

key为索引字段值，value为对应行的磁盘文件位置指针。

优点：相对于没有索引算法，明显提升查询效率。
缺点：数据单边增长的场景下，二叉树结构，就演变成了链表结构，查询和直接轮询没什么区别，效率低。

2，红黑树：

树的高度太高，查找麻烦：

redTree.png

本质上也是二叉树，是一种二叉平衡树，不让一边增长太过分了。

红黑树作为索引方法/存储结构：

优点：
1，相对二叉树，解决单边增长的问题。
缺点：
1，树的高度太高。希望3~5层放千万级别的数据。
2，范围查询性能差。

3，Hash：

对字段取hashcode值。

优点：Hash索引的查询速度，非常非常快。
缺点：Hash索引方法对范围查找支持的很差。

4，B-tree：

B-tree：

B-Tree.png

根节点或叶节点的key对应的value直接存有 行数据对应的磁盘文件位置指针。

优点：
相对于红黑树，层级少，查询次数少。索引节点可以存储多个索引值。
缺点：
B-Tree 对范围查询的支持也是很差的。（叶节点之间没有指针，叶节点也不是有序的）

为什么不只设置为一层？

放在一层，即所有的索引数据都在一个节点，不安全而且，占用内存多。

5，B+tree：

B+Tree.png

B+Tree是B-Tree的变种，3层。每个节点16k（根节点、叶节点都是16k）。

只有叶节点有data数据/行的磁盘文件位置索引。

根节点存储的内容：索引字段+下一节点的索引。

根节点一般会存放到内存/ram里面去。叶节点肯定存在磁盘。

B+tree ：索引的枝叶部分，value为：存储的直接就是 所在行的内容。

MySQL在设计的时候，它的底层原理就是按照B+Tree组织的一个索引结构文件。如果在创建表的时候，没有创建主键，后台会默认帮你创建主键并帮你维护。

为什么innoDB表必须要有主键，并且推荐使用整型的自增主键？

主键：一般是整型数据、字符串（如：UUID等）。

选择整型的原因：

1，整型数据比较大小的效率远远高于字符串比较大小的效率。
2，UUID长度比较长，占用的空间大。

自增的原因：

1，B+tree 的特点，根节点、叶节点，它的索引，从左到右都是递增有序的。叶节点之间的指针也是有序的。
2，范围查询的 效率特别高。（如：uid>49，只要定位到 uid=49的叶节点指针，就马上拿到结果了）
3，主键自增可以降低节点之间分裂的概率节约资源开销。（如果中间插入某一个数字的主键，节点之间的分裂的概率比较高）

6，聚集索引、非聚集索引：

注意:聚集索引，非聚集索引，索引算法使用的都是B+tree算法。

非聚集索引（myisam）：

myisam.png

聚集索引（innoDB）：

innoDB.png

聚集索引查询效率更高：
非聚集索引（如：myisam 存储引擎的索引）：数据和索引分开存储。
聚集索引（innoDB 存储引擎的索引）：数据和索引存在一起。
innoDB的：主键是聚集索引，二级索引就是非聚集索引，其中非聚集索引的叶子节点，存储的是主键值，去主键的表里查数据。
一张表有且只有一个聚集索引。即使没有创建主键，innoDB也会根据某一列帮助创建聚集索引。
innoDB：一定要建主键，否则innoDB会自己维护一个聚集索引，浪费资源。

7，联合索引：

最好不要建立单值索引，最好建联合索引：

联合索引的底层存储结构长什么样？索引是排好序的数据结构，联合索引内部是怎么排序的？

比如3个字段作为索引：
三个字段放在一个节点，容易排序的字段放在第一个，优先按照第一个字段的value进行排序，如果第一个字段的值相同，则按照第二个字段排序，如果第二个字段值也相同，则按照第三个字段排序。以此类推。

叶节点：三个字段存在key位置，此行其他字段存在value位置。

最左前缀原理：

# 不能跳过最左边的字段去查后面的字段。
select *from employee name="Bill" and age=31;   # 走索引
# 不走索引，因为单纯的跳过name直接去查age，age已经不是排好序的了（如上图所示），所以不走索引，而是全表查询。
select *from employee age=31 and position="dev";    
select *from employee position="manager";   # 不走索引

JointIndex.png

理解之后就可以理解MySQL的所有索引优化的原因。

myisam 数据表查找的流程：

select *from t where  uid = 49;

# 如果uid是索引字段：读取t表的**MYI文件**，找到uid=49所在的节点，节点的key为49，value为uid=49 所在行的 磁盘文件地址指针》根据查到的磁盘文件地址指针 去t表的**MYD文件**，直接定位到到所在的行。读取数据。

索引方法可以选择Btree方法也可以选择hash方法：

Hash方法：

将key经过hash之后存起来：key：列值的hashcode值，value：列支所在行的磁盘文件地址指针。
Hash运算优点：非常非常快。
hash算法应用举例：
MD5加密、CRC32/16。

一般不选用hash方法的原因：

hash算法不支持范围查询。（如：uid = 49很好查，但是uid>49 就很难查到）

3，MySQL存储引擎：

MySQL创建表单，默认是innoDB存储引擎

存储引擎是针对表的，不同的表可以选择不同的存储引擎。

myisam和innodb引擎中，表的索引，使用的都是B+tree算法；不同的是，叶节点data部分：myisam存的是索引值，innodb存的直接就是数据值。

1，myisam存储引擎

myisam存储引擎，创建一个表，会产生3个文件。

# 如：创建test表会产生3个文件。
test.frm 文件：    # 表结构文件。
test.MYD文件：     # 表内容。
test.MYI文件：     # 表索引文件。

2，innodb存储引擎

innodb存储引擎，创建一个表，产生2个文件：

# 如：创建test表会产生2个文件。
test.frm 文件：    # 表结构文件
test.ibd 文件：    # 表内容+索引文件

4，数据的存储

表数据存储：

MySQL-save.png

一个数据库，存放到磁盘上的一个对应的文件夹里。文件夹里存放表的文件。

# innodb存储引擎创建的表，一个表对应两个文件。
# myisam存储引擎创建的表，一个表对应三个文件。

5，MySQL事务

主要用到的是：读已提交。

6，缓存池Buffer Pool原理

理解了Buffer Pool原理，各种各样的日志，就都了解了。

7，日志

8，MySQL优化准则

懂得底层原理才能更好的优化。懂的底层，分分钟就能找到有效的方法，不懂原理，只能慢慢去试。

Java优化：

虚拟机优化：

MySQL优化：索引优化。

某些中间件的优化。

表的关联查询：大部分只查询1张表，最多不超过3张表。

Just Do It.