索引（index）是帮助MySQL高效获取数据的数据结构(有序)
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引
| 优势 | 劣势 |
|---|---|
| 提高数据检索的效率，降低数据库的IO成本 | 索引列也是要占用空间的 |
| 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗 | 索引大大提高了查询效率，同时却也降低更新表的速度， 如对表进行INSERT、UPDATE、DELETE时，效率降低 |
1. MySQL的索引
索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。
| 索引结构 | 描述 |
|---|---|
| B+ Tree 索引 | 最常见的索引类型，大部分引擎都支持 B+ 树索引 |
| Hash索引 | 底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询 |
| R-tree（空间索引） | 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类 型，通常使用较少 |
| Full-text（全文索引） | 是一种通过建立倒排索引,快速匹配文档的方式。类似于 Lucene,Solr,ES |
1. B+ Tree 索引
B+ Tree 是大多数 MySQL 存储引擎的默认索引类型
因为不再需要进行全表扫描，只需要对树进行搜索即可，因此查找速度快很多。除了用于查找，还可以用于排序和分组
可以指定多个列作为索引列，多个索引列共同组成键
适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引
InnoDB 的 B+Tree 索引分为主索引和辅助索引
主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚集索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚集索引
辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。
2. R- Tree(空间数据索引)
MyISAM 存储引擎支持空间数据索引(R-Tree)，可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。
必须使用 GIS 相关的函数来维护数据
3. Full-text(全文索引)
MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 match against，而不是普通的 where
全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射
InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引
4. Hash索引
哈希索引能以 O(1) 时间进行查找，但是失去了有序性，它具有以下限制:
-
无法用于排序与分组
-
只支持精确查找，无法用于部分查找和范围查找
InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找
2. B+ Tree原理
1. 数据结构
B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层。
B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。
在 B+ Tree 中，一个节点中的 key 从左到右非递减排列，如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，且不为 null，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1
2. 操作
进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。
插入删除操作记录会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并等操作来维护平衡性
3. 与红黑树的比较
红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，主要有以下两个原因:
-
更少的查找次数
红黑树虽然在顺序插入数据时，最终会形成一颗平衡的二叉树，但是在大数据量的情况下，层级较层，检索速度慢 (二叉树不可避免的问题)
-
利用计算机预读特性
为了减少磁盘 I/O，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的旋转时间，因此速度会非常快
操作系统一般将内存和磁盘分割成固态大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点，并且可以利用预读特性，相邻的节点也能够被预先载入
3. 索引的语法
-
创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ;-
index_name：索引名
-
table_name：表名
-
index_col_name：字段名
-
-
查看索引
SHOW INDEX FROM table_name; -
删除索引
DROP INDEX index_name ON table_name;
4. SQL性能分析
1 SQL执行频率
# session 是查看当前会话
# global 是查询全局数据
SHOW GLOBAL STATUS LIKE 'Com___';-
Com_delete：删除次数
-
Com_insert：插入次数
-
Com_select：查询次数
-
Com_update：更新次数
通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据
如果是以增删改为主，我们可以考虑不对其进行索引的优化
如果是以查询为主，那么就要考虑对数据库的索引进行优化
2 慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有 SQL语句的日志
MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量：slow_query_log
# 查看慢查询日志是否开启
show variables like 'slow_query_log';如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2配置完毕之后，通过以下指令重新启动MySQL服务器进行测试
systemctl restart mysqld查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log
通过慢查询日志，就可以定位出执行效率比较低的SQL，从而有针对性的进行优化。
3 profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了
# 查看当前MySQL是否支持profile操作
SELECT @@have_profiling;
# 查看是否开启了profile操作
SELECT @@profiling
# 通过set语句在 session/global 级别开启profiling
SET profiling = 1;执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时
# 查看每一条SQL的耗时基本情况
show profiles;
# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;4 explain
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法：
# 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;含义：
| 字段 | 含义 |
|---|---|
| id | select 查询的序列号，表示查询中执行select子句或者是操作表的顺序 (id相同，执行顺序从上到下；id不同，值越大，越先执行) |
| select_type | 表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接 或者子查询）、PRIMARY（主查询，即外层的查询）、 UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（ SELECT / WHERE 之后包含了子查询）等 |
| type | 表示连接类型，性能由好到差的连接类型为NULL、system、const、 eq_ref、ref、range、 index、all 。 |
| possible_key | 显示可能应用在这张表上的索引，一个或多个。 |
| key | 实际使用的索引，如果为 NULL，则没有使用索引。 |
| key_len | 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长 度，在不损失精确性的前提下， 长度越短越好 。 |
| rows | MySQL 认为必须要执行查询的行数，在 innodb 引擎的表中，是一个估计值，可能并不总是准确的。 |
| filtered | 表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。 |
5. 索引使用
1 最左前缀法则
如果索引了多列（联合索引），要遵守最左前缀法则；最左前缀法则指的是查询从索引的最左列开始， 并且不跳过索引中的列；如果跳跃某一列，索引将会部分失效（后面的字段索引失效）
有一个联合索引涉及到三个字段，顺序分别为：A、B、C。对于最左前缀法则指的是，查询时，最左变的列，也就是 A 必须存在，否则索引全部失效，而且中间不能跳过某一列，否则该列后面的字段索引将失效
-
索引生效
explain select * from table_name where A = *** and B = *** and C = ***; explain select * from table_name where A = *** and B = ***; explain select * from table_name where A = ***; -
索引失效
explain select * from table_name where A = *** and C = ***; explain select * from table_name where C = ***;
查询时存在 A 字段，最左边的列是存在的，索引满足最左前缀法则的基本条件。但如果跳过了 B ，后面的列索引是不会使用的，也就是索引部分生效。
explain select * from table_name where A = *** and C = ***;最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段（即是 第一个字段）必须存在，与我们编写SQL时，条件编写的先后顺序无关
2 范围查询
在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <
因为联合索引中，出现范围查询（ > , < ），范围查询右侧的列索引失效
3 索引失效情况
-
索引列使用函数运算符操作之后，索引失效
-
字符串类型字段使用时，不加引号，索引将失效
-
尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效
-
or连接的条件，左右两侧字段中有一侧字段没有索引时，索引才会生效
-
MySQL评估使用索引比全表更慢，则不使用索引（评估条件是什么？）
4 覆盖索引
尽量使用覆盖索引，减少回表查询；覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。
回表查询：
先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询
SQL语句的执行计划中的 Extra
| Extra | 含义 |
|---|---|
| Using where; Using Index | 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需 要回表查询数据 |
| Using index condition | 查找使用了索引，但是需要回表查询数据 |
Q：
一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对以下SQL语句进行优化, 该如何进行才是最优方案:
select id,username,password from tb_user where username = 'itcast';
A：
针对于 username, password 建立联合索引, SQL 为: create index idx_user_name_pass on tb_user(username,password);
这样可以避免上述的SQL语句，在查询的过程中，出现回表查询。
5 前缀索引
当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让 索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率；此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率
create index index_name on table_name(index_col_name(n));6 单列索引和联合索引
-
单列索引：即一个索引只包含单个列
-
联合索引：即一个索引包含了多个列
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引， 而非单列索引
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
