MySQL索引优化

1. 什么是MySQL的索引。

答：索引是帮助MySQL高效获取数据的数据结构。

2. 二叉树数据结构的弊端。

答：当极端情况下，数据递增插入时，会一直向右插入，形成链表，查询效率会降低。

3. MySQL中常用的的索引数据结构

答：MySQL中常用的的索引数据结构有BTree索引(Myisam普通索引)，B+Tree索引（Innodb普通索引），Hash索引(memory存储引擎)等等。

4. 索引使用的场景

答：1.主键自动建立唯一索引

2.频繁作为查询条件的字段应该创建索引(where 后面的语句)

3.查询中与其它表关联的字段，外键关系建立索引

4.多字段查询下倾向创建组合索引

5.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

6.查询中统计或者分组字段

5. 哪些情况不推荐建立索引:

1. 表记录太少

2. 经常增删改的表

3. Where条件里用不到的字段不建立索引

6. 索引的分类

1. 主键索引：表中的列设定为主键后，数据库会自动建立主键索引。

1. ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2. 唯一索引：表中的列创建了唯一约束时，数据库会自动建立唯一索引

1. ALTER TABLE `table_name` ADD UNIQUE (`column` )

3. 单值索引： 即一个索引只包含单个列，一个表可以有多个单值索引。建表时可随表一起建立单值索引

1. ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4. 复合索引：即一个索引包含多个列；建表时可随表一起建立复合索引

1. ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

全文索引：ALTER TABLE `table_name` ADD FULLTEXT ( `column` )

7. 索引测试

1. 通过存储过程往数据库中插入300W条数据。

2. 分别测试使用索引和没有使用索引的情况下，where查询的一个效率对比。

8. 什么是Explain：使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理SQL语句的。可以用来分析查询语句或是表的结构的性能瓶颈。

9. Explain的作用

1. 表的读取顺序

2. 哪些索引可以使用

3. 数据读取操作的操作类型

4. 那些索引被实际使用

5. 表之间的引用

6. 每张表有多少行被优化器查询

EXPLAIN关键字使用起来比较简单： explain + SQL语句：

10. Explain的重要字段

1. id：select查询的序列号，表示查询中执行select子句或操作表的顺序。

1. id相同，相当于是一组，执行顺序由上向下

2. id不同，id越大优先级越高

2. select_type:查询的类型

1. SIMPLE :简单的 select 查询,查询中不包含子查询或者UNION。

2. PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为Primary。

3. DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询, 把结果放在临时表里。

4. SUBQUERY: 在SELECT或WHERE列表中包含了子查询。

3. table：显示这一行的数据是关于哪张表的。

4. type：访问类型

1. System：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。

2. Const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。（在联表扫描时，where条件是两个表的主键相等，被联的表是eq_ref级别）

4. Ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。（where查找普通索引时）

5. Range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引；一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。（使用索引进行范围查询）

6. Index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的。比如Explain SELECT id FROM `sys_user` ;，查询的是索引

7. All：Full Table Scan，将遍历全表以找到匹配的行。（没有索引或者查询中条件where后是不是主键的字段）

从最好到最差依次是:system>const>eq_ref>ref>range>index>All 。一般来说，最好保证查询能达到range级别，最好能达到ref。

5. possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上如果存在索引，则改索引将会被列出来，但不一定会被查询实际使用上。

6. key

查询中实际使用的索引，如果为NULL，则没有使用索引。

7. ref

显示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值。

8. rows

rows列显示MySQL认为它执行查询时必须检查的行数。一般越少越好。

9. extra

一些常见的重要的额外信息：

1. Using filesort：MySQL无法利用索引完成的排序操作称为“文件排序”。没有使用索引排序（要优化）

2. Using temporary：Mysql在对查询结果排序时使用临时表，常见于排序order by和分组查询group by一起出现。（分组时没有使用索引）（要优化）

3. Using index：表示索引被用来执行索引键值的查找，避免访问了表的数据行，效率不错

4. Using where：表示使用了where过滤。

11. 查询优化

1. 索引失效

1. 最佳左前缀法则：如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。（如下前条查询语句走的是索引，最后一条是全表查询）

2. 不在索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描。

3. 存储引擎不能使用索引中范围条件右边的列。

4. Mysql在使用不等于时无法使用索引会导致全表扫描。

5. is null可以使用索引，但是is not null无法使用索引。

6. like以通配符开头会使索引失效导致全表扫描。结尾可以使用索引

7. 字符串不加单引号也会使索引失效

8. 使用or连接时索引失效。

建议：1.对于单值索引，尽量选择针对当前查询字段过滤性更好的索引。2.对于组合索引，当前where查询中过滤性更好的字段在索引字段顺序中位置越靠前越好。（组合索引那个字段使用次数多那个放前面）3.对于组合索引，尽量选择能够包含在当前查询中where子句中更多字段的索引。4.尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

2. 排序优化

1. 尽量避免使用Using FileSort方式排序。

2. order by语句使用索引最左前列或使用where子句与order by子句条件组合满足索引最左前列。

3. where子句中如果出现索引范围查询会导致order by索引失效。

3. 关联查询优化

内连接时，mysql会自动把小结果集的选为驱动表，所以大表的字段最好加上索引。左外连接时，左表会全表扫描，所以右边大表字段最好加上索引，右外连接同理。我们最好保证被驱动表上的字段建立了索引。 数据量小的作为驱动表，数据量大的作为索引表

4. 分组优化：跟排序优化思路一样，分组的底层用的是排序

12. 慢查询日志

1. 慢查询日志简介

MySQL的慢查询日志是MySQL提供的一种日志记录，他用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。可以由它来查看哪些SQL超出了我们最大忍耐时间值。

2. 慢查询日志使用

默认情况下，MySQL数据库没有开启慢查询日志，需要手动设置参数。

查看是否开启：show variables like '%slow_query_log%';

开启日志：set global slow_query_log = 1;

设置时间: set global long_query_time = 1;

查看时间: SHOW VARIABLES LIKE 'long_query_time%';

查看超时的sql记录日志：Mysql的数据文件夹下

5.5\Data\设备名称-slow.log

注意：非调优场景下，一般不建议启动改参数，慢查询日志支持将日志记录写入文件，开启慢查询日志会或多或少带来一定的性能影响。