MYSQL-索引优化

在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单

简单来说：索引就是图书的目录，能够帮助快速定位相关内容

MySQL索引的数据结构是树，常用的存储引擎innodb采用的是B+Tree

B+树是B树变体，相对于B树

叶子结点的值包含了所有的值；

所有父结点的值是重复了叶子结点的值；

父结点只起索引查找的作用，同时所有叶子结点也也构成了一条有序的链表。

推荐使用该地址查看B+树的节点创建过程及查找过程

https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

1.硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。

2.没有索引或者索引失效。（一般在互联网公司，DBA会在半夜把表锁了，重新建立一遍索引，因为当你删除某个数据的时候，索引的树结构就不完整了。所以互联网公司的数据做的是假删除.一是为了做数据分析,二是为了不破坏索引 ）

3.数据过多（分库分表）

4.服务器调优及各个参数设置（调整my.cnf）

1、开启慢查询日志，设置一定的阈值（比如超过2秒就是慢SQL），生产环境可以跑一天，通过日志排查哪些SQL比较慢

2、Explain和慢SQL分析：分析工具可采用mysqldumpslow或pt-query-digest

3、Show Profile可以查询到执行每一条SQL都做了什么事情，这些事分别花费了多少秒

4、服务器的参数设置

explain的使用方式：

explain select * from tableA;

explain返回的参数：

主要参数再解释：

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表获取结果的select

type：

常见的有：all , index , range , ref , eq_ref , const , system , null 八个级别。

性能从最优到最差的排序：system > const > eq_ref > ref > range > index > all
all：（full table scan）全表扫描，数据量很大（百万级别）全表扫描会非常慢。
index：（full index scan）全索引文件扫描，效率比all好一些
range：只检索给定范围的行，使用索引来匹配行。缩小范围，自然比全表扫描和全索引文件扫描要快。sql语句中一般会有between，in，>，< 等查询。
ref：非唯一性索引扫描，返回所有匹配某个单独值的行。
eq_ref：唯一性索引扫描，返回匹配某个单独值的唯一行
const：表示通过索引一次就可以找到，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快，若将主键至于where列表中，MySQL就能将该查询转换为一个常量。
system：表只有一条记录（等于系统表），这是const类型的特列

extra：

Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。出现这个更要立刻优化sql。
Using index：表示相应的select 操作中使用了覆盖索引（Covering index），避免访问了表的数据行，效果不错！如果同时出现Using where，表明索引被用来执行索引键值的查找。如果没有同时出现Using where，表示索引用来读取数据而非执行查找动作。
覆盖索引（Covering Index） ：也叫索引覆盖，就是select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select 列表中的字段，而不必根据索引再次读取数据文件。
Using index condition：在5.6版本后加入的新特性，优化器会在索引存在的情况下，通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。
Using where：表明使用了where 过滤
Using join buffer：表明使用了连接缓存
impossible where：where 语句的值总是false，不可用，不能用来获取任何元素
distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

参考：https://www.jianshu.com/p/838d5ff61ccc

1、范围查询之后的索引会失效

跳过范围查询的字段建立索引：比如查询sql为

select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1

建立联合索引应该跳过comments，只包含category_id和views

2、左连接左表索引失效，右连接右表索引失效

因为左连接的左表是主表，左表必定要全表扫描，所以应该将索引建在右表上；

因为右连接的右边是主表，右表必定要全表扫描，所以应该将索引建在坐标上；

3、违背最佳左前缀原则的条件查询将导致索引失效

如设置索引为idx_nameAgePos，条件中如果有以下情况，将导致索引失效

（1）name未出现在条件中（带头大哥丢失）

（2）name = '' and pos = ''(中间兄弟断掉)

总结：使用复合索引条件查询的条件中：带头大哥不能死，中间兄弟不能断

4、不在索引列上做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效

5、存储引擎不能使用索引中范围条件右边的列

6、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

7、mysql在使用不等于（!= 或者<>）得时候无法使用索引会导致全表扫描

8、is null,is not null也无法使用索引

9、like以通配符开头（'%abc..'）,索引会失效，变成全表扫描

'abc%'不会导致索引失效

如果必须要用'%abc%'时，那就使用覆盖索引

10、字符串不加单引号索引会失效

11、少用or,用它来连接时索引会失效

1、最好使用全值匹配查询

2、一定要遵守最佳左前缀规则

3、尽量不要在索引列上进行计算

4、范围查询之后的索引会失效

5、LIKE模糊查询时%要写在最右边

6、覆盖索引不要使用*查询，尽可能只查询所需字段

7、尽量不使用!=、<>、or