在MySQL中，索引是在存储引擎层而不是在服务器层实现的，所以并没有统一的索引标准。不同存储引擎索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也是不同的。
B-TREE 索引
- 存储引擎以不同的方式使用B-Tree索引，性能各有不同，各有优劣。例如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。
B+Tree每个叶子节点都包含指向下一个叶子结点的指针，从而方便叶子节点的范围遍历。（每个节点为一个页）
索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引树开始搜索。父节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。最终存储引擎要么找到对应的值，要么该记录不存在。 - B-Tree对索引列是顺序存储的，所以很适合查找范围数据。
索引根据创建索引时列的顺序，对多个值进行排序。首先，对索引的第一个字段进行排序。如果第一个字段相同，那么就对索引的第二个字段进行排序，以此类推。 - 索引适用于全键值（全值匹配）、键值范围或键前缀查找。其中键前缀查找遵守最左前缀原则。
索引的类型
- 普通索引：这是最基本的索引，它没有任何限制。
直接创建索引：CREATE INDEX 索引名称 ON 表名 (字段名 ( 长度 ));
修改表结构的方式添加索引：ALTER TABLE 表名 ADD INDEX 索引名称 ON (字段名 ( 长度 ))
创建表时创建索引：
CREATE TABLE 表名 (
…
INDEX 索引名称 (字段名 ( 长度 ))
);
删除索引：DROP INDEX 索引名称 ON 表名 - 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
直接创建索引：CREATE UNIQUE INDEX 索引名称 ON 表名 (字段名 ( 长度 ));
修改表结构的方式添加索引：ALTER TABLE 表名 ADD UNIQUE 索引名称 ON (字段名 ( 长度 ))
创建表时创建索引：
CREATE TABLE 表名 (
…
UNIQUE 索引名称 (字段名 ( 长度 ))
);
删除索引：DROP UNIQUE 索引名称 ON 表名 - 主键索引：索引列的值必须唯一，不允许有空值。
修改表结构的方式添加索引：ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
创建表时创建索引：列级约束，表级约束。 - 组合索引：多个字段做索引。遵循最左前缀原则。
修改表结构的方式添加索引：ALTER TABLE 表名 ADD INDEX 索引名称 ON (字段名1，字段名2…)
最左前缀原则
创建组合索引：ALTER TABLE table ADD INDEX idx_ABC ON (A,B,C)
下面三种情况可以用到索引：
SELECT * FROM table WHERE A=1 AND B=2 AND C=3;
SELECT * FROM table WHERE A=1 AND B=2;
SELECT * FROM table WHERE A=1;
下面的情况不会使用到索引（看上面B+Tree图）：
SELECT * FROM table WHERE B=2 AND C=3;
SELECT * FROM table WHERE B=2;
SELECT * FROM table WHERE C=3;
下面的情况会使用部分索引：
SELECT * FROM table WHERE A=1 AND C=3;
索引的优缺点
索引的优点：
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
- 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
- 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
- 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
- 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
索引的缺点：
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
- 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
建立索引的时机
什么样的字段适合创建索引:
- 在经常需要搜索的列上，可以加快搜索的速度；
- 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
- 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
- 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
- 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
- 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
什么样的字段不适合创建索引:
- 对于那些在查询中很少使用或者参考的列不应该创建索引。
- 对于那些只有很少数据值的列也不应该增加索引，例如性别列。尽量选择区分度高的列作为索引，区分度的公式是COUNT(DISTINCT column)/COUNT(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1。
- 对于那些定义为text, image和bit数据类型的列不应该增加索引。
- 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。
索引的注意事项
- 索引不会包含有NULL值的列：只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
- 范围之后的字段用不了索引：如建立索引（a，b），SELECT * FROM table WHERE a>1 AND b=1;，b就用不到索引。
- 使用短索引：对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
- 索引列排序：MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
- like语句操作：一般情况下不鼓励使用like操作，如果非使用不可。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
- 不要在列上进行运算：例如：SELECT actor_id FROM actor WHERE actor_id + 1 = 5;或SELECT … WHERE DATEDIFF(DATE1,DATE2) > 10;，将在每个行上进行运算，这将导致索引失效而进行全表扫描。
- 强制类型转换会全表扫描。
- 建立联合索引的时候，要把查询频率较高的列放在最左边。
- 尽量用具体的字段：SELECT A,B FROM table;如果建立了索引（a，b），可以使用覆盖索引。
- =和in可以乱序：建立索引(a,b,c)，可以任意顺序，比如A=1 AND C=2 AND B=3或B=3 AND C=2 AND A=1;都可以使用索引。MySQL的查询优化器会优化成索引可以识别的形式，最好按照索引建立的顺序。
- 使用 !=，<>，IS NULL，IS NOT NULL 无法使用索引。
- 连接查询时，索引建立在从表。
- 永远用小结果集驱动大的结果集。
聚簇索引
- 如果没有定义主键，Innodb会选择一个唯一的非空索引代替。如果没有这样的索引，Innodb会隐式定义一个主键来作为聚簇索引。
- 聚簇索引将索引和数据保存在同一个B-Tree中。InnoDB的数据文件本身就是索引文件。MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
- 二级索引（非聚簇索引）需要两次索引查找，原因是二级索引叶子节点保存的不是指向数据行的物理位置的指针，而是行的主键值。通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获取对应的主键值，然后根据这个值去聚簇索引中查找对应的行。
叶节点的 data 域存放的是数据记录的地址。
- 如果正在使用的Innodb表没有什么数据可以聚集，那么可以定义一个代理键作为主键，最简单的方法是使用AUTO_INCREMENT自增列。
如果新插入的主键值不一定比之前的大，那么会导致页分裂和碎片。因为写入是乱序的，Innodb不得不频繁的做页分裂操作，以便为新的行分配空间。由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终数据会产生碎片。
覆盖索引：SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。Extra信息已经有’Using Index’，表示已经使用了覆盖索引。
EXPLAIN分析
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。EXPLAIN不考虑各种Cache。EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况。
语法：EXPLAIN + SQL语句
| 字段 | 描述 |
|---|---|
| id | 查询的序号，表示查询中执行select子句或操作表的顺序 1. id相同，执行顺序由上至下 2. id不同，id值越大，优先级越高，越先执行 |
| select_type | SIMPLE:简单的SELECT查询，不使用UNION或子查询 PRIMARY:查询中包含任何复杂的子部分，最外层查询则被标记为PRIMARY SUBQUERY：在SELECT或WHERE列表中包含了子查询 DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中 UNION：若第二个SELECT出现在UNION之后，则被标记为UNION。若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED UNION RESULT：UNION的结果 |
| table | 当前行所引用的表 |
| partitions | 该列显示的为分区表命中的分区情况,非分区表该字段为空（NULL）。 |
| type | 访问类型，结果值从最好到最坏依次是：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>All 1. system：表中仅有一行（等于系统表）这是const类型的一个特例 2. const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如果将主键置于where列表中，MySQL能将该查询转换为一个常量 3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描 4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体 5. range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是where语句中出现了>，<，between，in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描 6. index：index与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多 7. all：遍历全表以找到匹配的行 一般保证查询至少达到range级别，最好能达到ref |
| possible_keys | 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出。但不一定被查询实际使用。 |
| key | 实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中，不会出现在possible_keys列表中。 |
| key_len | 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。 |
| ref | 哪些列或常量被用于查找索引列上的值。如果是使用的常数等值查询，这里会显示const。如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段。如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。 |
| rows | 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数 |
| filtered | 表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的百分比 |
| Extra | 包含不适合在其他列中显示但十分重要的额外信息。 Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为“文件排序”。 Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。 Using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行。如果同时出现using where，表明索引被用来执行索引键值的查找。如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。 Using where：表明使用了where过滤。 Using join buffer：使用了连接缓存。 Impossible where：where子句的值总是false，不能用来获取任何元组。即查询语句中where的条件不可能被满足，恒为False。 select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 distinct：优化distinct操作，在找到第一匹配的元组后即停止找相同值的动作。 |
慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句。具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
long_query_time的默认值是10，意思是运行10秒以上的语句。
由它来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前的explain进行全面分析。
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
查看慢查询日志是否开启：SHOW VARIABLES LIKE ‘%slow_query_log%’;
开启慢查询日志：SET GLOBAL slow_query_log=1;
查看阈值：SHOW VARIABLES LIKE ‘%long_query_time%’;
设置阈值：SET GLOBAL long_query_time=3;=
查询当前系统中有多少条慢查询记录：SHOW GLOBAL STATUS LIKE ‘%Slow_queries%’;
日志分析工具
MySQL提供了日志分析工具mysqldumpslow。
- 查看mysqldumpslow的帮助信息
mysqldumpslow --help
s：是表示按照何种方式排序
c：访问次数
I：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
t：即为返回前面多少条的数据
g：后边搭配一个正则匹配模式，大小写不敏感
得到返回记录集最多的10条SQL：
mysqldumpslow -s r -t 10 /opt/mysql/localhost-slow.log
得到访问次数最多的10条SQL：
mysqldumpslow -s c -t 10 /opt/mysql/localhost-slow.log
得到按时间顺序的前10条包含左连接的查询语句：
mysqldumpslow -s t -t 10 -g “left join” /opt/mysql/localhost-slow.log | more
Show Profile
Show Profile是MySQL提供的可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果。
查看Profile是否开启：SHOW VARIABLES LIKE ‘%profiling%’;
开启Profile：set profiling = on;
查看结果：SHOW profiles;
诊断SQL：SHOW profile cpu,block io FOR SQL序号
- 参数列表
- all：显示所有的开销信息
- block io：显示块io开销
- context switches：上下文切换开销
- cpu：显示cpu的开销信息
- ipc：显示发送和接收的开销信息
- memory：显示内存的开销信息
- page faults：显示页面错误开销信息
- source：显示Source_function，Source_file，Source_line开销信息
- swaps：显示交换次数的开销信息
注意：
converting HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬了
Creating tmp table：创建临时表。拷贝数据到临时表，用完再删除。
Copying to tmp table on disk：把内存中临时表复制到磁盘，危险！！
locked
参考博客
数据库索引
细说mysql索引
数据库索引原理
mysql-覆盖索引
MySQL高级学习笔记
MySQL索引实现原理分析
《高性能MySQL》（第3版）
mysql数据库索引类型和原理
【MySQL优化】——看懂explain
数据库索引原理，及MySQL索引类型
324
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
