#索引与B+Tree
索引的概述和语法
-
什么是索引？
索引是帮助MySQL高效获取数据的数据结构；类似图书的目录索引，可以提高数据检索的效率，降低数据库的IO成本
索引是提高数据的检索效率的，是基于B+树的数据结构实现的。
-
索引的语法
-
创建索引
在创建表的时候添加索引
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );在创建表以后添加索引
ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name); -- 或者 CREATE INDEX index_name ON my_table(column_name); -
删除索引
DROP INDEX my_index ON tablename； -- 或者 ALTER TABLE table_name DROP INDEX index_name; -
查看表中的索引
SHOW INDEX FROM tablename;
-
-
索引的优缺点：
- 优点
- 可以快速检索，减少I/O次数，加快检索速度；
- 根据索引分组和排序，可以加快分组和排序；
- 缺点
- 索引本身也是表，因此会占用存储空间。一般来说，索引表占用的空间的数据表的1.5倍；
- 索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；
- 构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表;
- 优点
索引的分类
MySQL中有不同的索引类型，要求和效率也各不一样，主要分为以下五类。
主键索引、唯一索引、普通索引、全文索引、组合索引
- 主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；
-- 创建表时，直接创建主键索引
create table users(
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 修改时添加主键和自增
alter table users modify uid int primary key AUTO_INCREMENT;
-- 删除主键索引 注意需要先取消 自增,再删除主键
-- 先取消自增,修改字段
alter table users modify uid int;
-- 删除主键
alter table users drop primary key;
如果表中没有定义主键，InnoDB会选择一个非空唯一索引代替。
如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。
- 唯一索引：用来建立索引的列的值必须是唯一的，允许空值
-- 创建表时，直接创建唯一索引
create table users(
name varchar(10) NOT NULL,
UNIQUE KEY name(name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 添加唯一索引 UNIQUE 当前列要求唯一,但允许为空
alter table users add UNIQUE u_name(uname);
-- 删除唯一索引 根据当前索引名去进行删除
alter table users drop index u_name;
- 普通索引：用表中的普通列构建的索引，没有任何限制
-- 创建表时，直接创建普通索引
create table users(
email varchar(10) NOT NULL,
KEY index_email(email)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 添加索引
alter table users add index in_name(email);
-- 删除索引
drop index in_name on users;
- 全文索引：用大文本对象的列构建的索引
ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；
-- 5.6版本前的MySQL,全文索引只能用于MyISAM存储引擎
-- 5.6版本之后InnoDB存储引擎开始支持全文索引
-- MySQL8中支持中文的全文索引，因为目前中文分词技术已经非常成熟了。
- 组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值
-- 添加索引
alter table users add index in_x(email,phone,uname);
-- 删除索引
alter table users drop index in_x;
- 遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1,col1col2,col1col2col3三个索引。
- 在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引
ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；
--表示使用col1的前4个字符和col2的前3个字符作为索引
B+Tree:索引的实现原理
索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。
###1. 哈希索引
只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能
2. B树
【注意！红黑树是二叉树，B树和B+树都是多叉树】
红黑树：
基于平衡二叉树的一个优化，减少了旋转次数，保证树的平衡性。
树的查找性能取决于树的高度，让树尽可能平衡，就是为了降低树的高度。
当数据存在内存中，红黑树效率非常高，但是文件系统和数据库都是存在硬盘上的，如果数据量大的话，不一定能一次性加载到内存，
因此就出现了专为磁盘等存储设备而设计的一种平衡多路查找树，也就是B树。
与红黑树相比 ，在相同的节点的情况下，一棵B/B+树的高度远远小于红黑树的高度
B树即平衡查找树，一般理解为平衡多路查找树，也称为B-树，B_树，【注意不能叫做B减树】
B树是一种自平衡树状数据结构，一般较多用在存储系统上，比如数据库或文件系统，不过其旋转次数过多。
B树存在的问题：
-
查找效率不均衡
-
范围查找需要中序遍历，会相对消耗性能
-
每个节点上都带有数据，那么一次IO读取的节点更少
在计算机中，所有与空间相关的东西都是按照**块（block）**进行存取和操作的，每次读取都意味着一次I/O。B树的数据和索引都在同一个节点上，那么每个块中包含的索引是少量的，如果想要取出比较深层的数据，就要读取更多的块才能得到想要的索引和数据。
3. B+树
正常情况下，如果不指定索引的类型，那么一般是指B+Tree索引。
存储引擎以不同的方式使用B+Tree索引。性能也各有不同，但是InnoDB按照原数据格式进行存储。
B+Tree 索引能够加快数据的读取速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，相反是从索引的根节点开始进行搜索，通过相应的指针移动，最终存储引擎要么找到了对应的值，要么该记录不存在。树的深度与表的大小直接相关。
B+Tree索引是按照顺序组织存储的，所以适合范围查找数据
B+树：在B树的基础上做出的演变
-
在B+树上只有叶子节点存储数据，B树中每个节点都存储数据
-
非叶子节点起到了索引的作用
-
所有的叶子节点使用链表相连
进行范围查找时候就不用回树上继续遍历了
B+树的优势：
-
磁盘读写代价更低
B树的数据和索引都在同一个节点上，那么每个块（block）中包含的索引是少量的，如果想要取出比较深层的数据，就要读取更多的块才能得到想要的索引和数据，那么就增加了IO次数
B+树中每个块能存储的索引是B树的很多倍，那么获取比较深层的数据，只需读少量的块，那么就减少了IO次数 -
随机IO的次数更少
随机I/O：指读写操作时间连续，但是访问地址不连续，时长约为10ms
顺序I/O：指读取和写入操作基于逻辑块逐个连续访问来自相邻地址的数据，时长约为0.1ms
在相同情况下，B树要进行更多的随机IO，而B+树需要更多的顺序IO，因此B+树效率更快 -
查询速度/效率更稳定
由于B+树非叶子节点不存储数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的
所以，使用B+Tree的好处：
- 降低了磁盘读写代价
- 顺序I/O提高效率
- 查询速度更稳定
聚簇索引和非聚簇索引
在索引的分类中，我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。
索引又分为聚簇索引和非聚簇索引两种。非聚簇索引也叫二级索引。
-
只有主键索引（主索引）为聚簇索引
聚簇索引：把数据和索引放在一起；索引即数据，数据即索引【找到索引就等于找到了数据】
-
非主键索引的其他索引（辅助索引）都是非聚簇索引
找到其叶子节点时，找到的仅仅是当前的索引值和它的key，如果需要索引外的其他内容，则需要回表/回行【找到key之后，再通过key到主键索引里遍历一遍，最终找到数据】。非聚簇索引的索引和数据是分离的
也就是说，辅助索引依赖主键索引，所以主键索引必须存在
以上关于索引原理和聚簇、非聚簇索引都是以InnoDB表引擎为基础
InnoDB和MyISAM的区别
MyISAM表引擎的存储方案：
MyISAM也是B+树的索引方案，但是把索引和数据分开存储
所以不管是主索引还是辅助索引，其叶子节点存储的都是数据的地址；这种情况下，不管是使用主键索引还是非主键索引，找到最终叶子节点的索引以后， 都需要回表【通过索引找到数据的对应行号，再通过行号去找对应的数据】
所以MyISAM里所有索引都是非聚簇索引（二级索引）
###MyISAM和InnoDB的区别：
从MySQL5.7版本之后，表引擎默认都为InnoDB，MyISAM目前使用率比较低。
- 数据存储方式：
- InnoDB由两种文件组成：表结构，数据和索引
- MyISAM由三种文件组成：表结构，数据，索引
- 索引的方式：
- 索引的底层都是由B+Tree的数据结构建立
- InnoDB中主键索引为聚簇索引，辅助索引是非聚簇索引
- MyISAM中数据和索引存在不同的文件里，因此都是非聚簇索引
- 事务的支持：
- InnoDB支持事务
- MyISAM不支持事务
##总结
-
数据库的索引就是为了提高数据检索性能
-
数据库的索引是基于B+Tree的数据结构实现
-
在InnoDB中主键索引是聚簇索引，辅助索引是非聚簇索引
-
在MyISAM中主键索引和辅助索引都是非聚簇索引
慢查询和SQL优化
我们已经知道索引是为了提高检索性能，那么如何更好地合理使用索引，并且对一些执行较慢的sql进行优化也是需要掌握的一种技能。
慢查询
MySQL的慢查询，全名为慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句。默认情况下，MySQL数据库并不启动慢查询日志，需要手动设置。
慢查询日志由long_query_time执行时间超过几秒的SQL语句组成，
慢查询日志可用于查找需要很长时间才能执行的查询，因此是优化的候选者。
- 慢查询配置
-- // 查看“慢查询”的配置信息
mysql> show variables like "%slow%";
+---------------------------+-----------------------------------------------+
| Variable_name | Value |
+---------------------------+-----------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | F:\MySQL\mysql-5.7\mysql-5.7.28-winx64\data\DESKTOP-UC4C3A2-slow.log |
+---------------------------+-----------------------------------------------+
-- // 查看“慢查询”的时间定义
show variables like "long%";
-- //设置“慢查询”的时间定义
set long_query_time=2;
-- //开启慢日志
set global slow_query_log='ON';
Explain
一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化后会生成一个执行计划，也就是
EXPLAIN计划。
这个执行计划展示了接下来具体执行查询的方式，比如多表联结的顺序是什么等等。
MySQL为我们提供了EXPLAIN语句来帮助我们查看某个语句的具体执行计划，EXPLAIN返回SELECT语句中使用的每个表的一行信息 。它按照MySQL在处理语句时读取它们的顺序列出输出中的表。
EXPLAIN输出列说明：
| Column | 含义 |
|---|---|
| id | 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id |
| select_type | 该SELECT对应的查询的类型 |
| table | 表名 |
| partitions | 匹配的分区,通常不用 |
| type | 针对单表的访问方法 |
| possible_keys | 可能用到的索引 |
| key | 实际使用的索引 |
| key_len | 实际使用的索引长度 |
| ref | 列与索引进行比较 |
| rows | 预估要读取的行数 |
| filtered | 按表条件过滤后剩余行的百分比 |
| Extra | 附加信息 |
mysql> explain select * from orders where order_num = 20005\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
索引能给数据检索提高的效率非常明显
但每建立一个索引都会建立一棵B+树，并且需要维护，很耗费性能和存储空间。
【索引会占空间，且会影响写入性能。所以需要进行SQL优化】
SQL优化
MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度，在索引的使用过程中，存在一些使用细节和注意事项。
-
适当建立索引
- 创建并使用自增数字来建立主键索引
- 经常作为where条件的字段建立索引
- 添加索引的字段尽可能保持唯一性
- 可考虑使用联合索引/组合索引并进行索引覆盖【这样就不用回行了】
-
合理使用索引
MySQL索引通常是被用于提高WHERE条件的数据行匹配时的搜索速度。在索引的使用过程中，存在一些使用细节和注意事项。
因为不合理的使用可能会导致建立了索引之后不一定用的上。-
不要在列上使用函数和进行计算
-- 不要在列上使用函数，这将导致索引失效而进行全表扫描 select * from news where year(publish_time) = 2017 -- 改造 select * from news where publish_time = '2017-01-01'-- 不要在列上进行运算，这也将导致索引失效而进行全表扫描 select * from news where id / 100 = 1 -- 改造 select * from news where id = 1 * 100 -
隐式转换可能影响索引失效
当查询条件左右两侧类型不匹配的时候会发生隐式转换【例如，字符串转int】，隐式转换带来的影响就是可能导致索引失效而进行全表扫描
-- 修改表中数据 update users set name = '123456' where id=10086; -- 使用正常查询，只需要0.00秒 select * from users where name = '123456'; -- 出现了隐式转换，需要5.65秒 select * from users where name = 123456; -
like语句的索引失效问题
-- 当在尾部使用通配符时可以使用索引 select * from users where name like 'zhang%'; -- 当在头部使用通配符时，则导致索引失效【不管后面有没有通配符】 select * from users where name like '%zhang%';
-
-
复合索引的使用（联合、组合）
-
多个单列索引并不是最佳选择
MySQL查询时只能使用一个索引，会从多个索引中选择一个限制最为严格的索引，因此，为多个列创建单列索引并不能提高MySQL的查询性能
-
复合索引的最左前缀原则
**查询条件中使用了复合索引的第一个字段，索引才会被使用。**因此，在复合索引中索引列的顺序至关重要。如果不按照索引的最左列开始查找，则无法使用索引。
-
尽可能达成索引覆盖
如果一个索引包含所有需要查询的字段的值，直接根据索引的查询结果返回数据，而无需回行读数据，能够极大提高性能。
因此，可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的
-
【注意！索引不会包含有NULL值的列】
只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。
因此，在数据库设计时，除非有一个很特别的原因使用 NULL 值，不然尽量不要让字段的默认值为 NULL。
###总结
-
SQL语句的优化
-
避免嵌套语句（子查询）
-
避免多表查询（复杂查询简单化）
【把多表查询变为单表查询，且每个单表查询都用上索引】
-
-
索引优化
- 适当建立索引
- 合理使用索引
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
