索引类型
从数据结构的角度
- B+树索引（重点关注）
B+树是为磁盘或其他直接存取辅助设备而设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶节点中，各叶节点指针进行连接。
B+树特点：
- 有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
- 所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。（链表）
- 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。
- B+树查找时是从上到下查找；B-树则是从下往上查找（中序遍历）。
B+树优点：
1.单一节点存储更多的元素（这样该节点下分支变多了，树变矮胖了），使得查询的IO次数更少。
2.所有查询都要查找到叶子节点，查询性能稳定。
3.所有叶子节点形成有序链表，便于范围查询。
B+树为什么适合磁盘：
由于是多叉树，数的高度比较低，导致整体查询稳定。
叶子结点相互临近，适合磁盘顺序读取。
只有叶子结点存储数据，那么非叶子结点数据少，非常适合放入内存提升性能。
根据B+树的结构，每个叶子结点的深度一样；
适合等值查找，范围查找，前缀查找；
-
Hash索引
O(1)的时间复杂度；
只能进行等值或不等值查询，不能使用范围查询；
只有Memory存储引擎支持； -
全文索引
适合文章内容搜索，一般场景不会使用； -
R-Tree索引
myisam支持空间索引，可以用作地理数据存储，一般场景不会使用。
从物理存储的角度
- 聚集索引
- 非聚集索引
从逻辑角度
- 主键索引
- 单列普通索引和多列普通索引
- 唯一索引和非唯一索引
- 空间索引
索引优化原则
-
组合索引中，如果索引字段的用到了范围查询，那么之后的字段就无法使用索引。
例如：有组合索引（a1,a2,a3）在表table1上，那么SQL语句 select * from table1 where a1 = 1 and a2 > 1 and a3 = 3;
那么a3字段无法走索引。 -
关联查询的关联字段的索引建立原则，左连接右表建索引，右连接左表建索引。
-
索引用在经常使用的字段上面。
-
小表驱动大表。
-
优先优化内层循环。
-
Order By子句优化
尽量使用Index的方式排序，避免使用FileSort方式排序。
尽可能在索引列上完成排序操作，遵照组合索引的最左前列原则和排序对应原则。
MySQL支持2种方式的排序，FileSort和Index，Index效率高。因为index直接扫描索引本身就完成了排序，注意索引都是有序的。
where子句与order by子句组合时也要满足组合索引的最左前列原则。
如果出现了filesort，那么就要注意filesort的两种算法，单路排序和双路排序。
- 双路排序： 4.1版本之前的算法，使用2次扫描磁盘,很少使用这么低版本了，因此这里不做介绍。
- 单路排序： 4.1版本之后的算法，从磁盘读取查询需要的所有列，按照order by列在buffer对他进行排序，然后扫描排序后的列表进行输出，它效率更快一些，避免了二次读取数据。并且把随机IO变成了顺序IO，但是会使用更多的空间，因为它把每一行都保存在内存中了。
单路排序的弊端：在sort_buffer中，方法B比方法A要占用更多的空间，因为方法B是把所有的字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能区sort_buffer容量大小的数据，进行排序（创建临时文件，多路合并），排序完再取sort_buffer容量大小，再排…从而多次IO。
本来想省一次IO操作，反而导致大量的IO操作，反而得不偿失。
下一步只能调整mysql的配置：
sort_buffer_size
max_length_for_sort_data
总结：
7. group by优化
- group by实质是先排序后进行分组，一样需要符合组合索引的最左前列原则。
- 一样可以通过调整参数sort_buffer_size、max_length_for_sort_data来优化
- where高于having，能写在where的条件就不要写到having中。
- 其他优化手段与order by类似。
- JoinBuffer可以适当调大。
慢SQL日志
慢SQL日志是MySQL提供的一种日志记录，用于记录执行较慢的SQL语句，可以通过参数long_query_time设置超过指定时间的SQL记录到慢SQL日志中。默认是不开启慢SQL日志，因为会影响一定的性能。
可以使用mysqldumpslow工具分析慢SQL日志。
索引失效
- 违反组合索引的最左前缀法则会使得索引失效，要先从第一个开始、在第二个、第三个…以此类推的使用。不要从中间列开始使用，否则组合索引会失效。
- where条件中，不要对索引列做任何操作，例如计算、函数、类型转换，都会导致索引失效。
- 尽量使用覆盖索引查询，即查询列也是索引列，少用select *。
- != 操作无法使用索引
- is null, is not null无法使用索引
单列索引无法存储空值，因此单列索引会直接全表查询；
复合索引无法储全为null的值，全列空值查询也会全表查询； - like以通配符开始无法使用索引，但是通配符放在后面可以走索引。
注意：如果非要使用where name like ‘%kin%’,可以使用覆盖索引解决该问题，这样可以避免索引失效。 - 少用or，用它会导致索引失效。
- 重复数据比较多的列，不适合建索引，就算建了索引有可能索引失效。
- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引。
- 避免MySQL的隐式类型转换，如果在索引列上出现隐式转换则索引会失效。
- order by违反索引的最左前缀法则，或者包含非索引字段会导致文件排序。
- group by违反索引的最左前缀法则，或者包含非索引字段会导致产生临时表。
Show Profile进行sql分析
对于SQL详细的执行步骤，每步骤的耗时分析，可以使用show profile功能进行详细分析。由于开启之后会记录每条SQL的执行详细内容，因此，建议只在分析SQL的时候使用，其他情况建议关闭。
- 开启profile
set profiling=1;
- 执行业务的SQL语句
- 使用‘show profiles;’命令查询sql id和执行耗时；
- 使用‘’show profile ALL for query 1; ’查询SQL执行的详细分析；
注意：1为第3步骤查询到的sql id。
除了All之外还有很多其他的分析参数，大家可以自行查询。
profiling statas参数的常见结论
converting HEAP to MyISAM
查询结果太大，内存都不够用了往磁盘上放数据。
creating tmp table
创建临时表，将数据复制到临时表，用完再删除。
Copying to tmp table on disk
把内存中临时表的数据复制到磁盘。
locked
加锁操作
全局查询日志
建议只在测试环境使用，能查询到的信息有限，没有profiling好用。
开启方法
永久开启方法，在my.conf配置文件中：
# 开启
general_log=1
# 保存路径
general_log_file=/path/logfile
#输出格式
log_output=FILE
查询方法：查看相应的文件即可。
临时开启方法：
#开启
set global general_log=1;
#输出格式，直接将日志输出到表中存储。
set global log_output = 'TABLE';
执行后，每次执行SQL语句都会往mysql.general_log表插入日志。
查询方法：
select * from mysql.general_log;