MySql-sql优化

索引常见问题

1. 通过show status命令了解各种的SQL的执行频率

我们可以通过使用 show [session|global] status 命令查询各种指标信息。
使用show status的时候默认是session的。 session代表的是本次会话， global代表是的数据库上传启动到现在
由于数据太多，我们需要进行筛选， 可以像show status like 'Com_%' 这样进行筛选
Com_XXX 表示每个XXX语句的执行次数，我们通常比较关心是以下几个统计参数

• Com_select select的次数
• Com_insert 执行insert次数
• Com_update
• Com_delete
  还有几个较为常用的
• Connections: 试图连接mySQL 服务器的次数
• Uptime: 服务器工作时间
• Slow_queries: 慢查询的次数
  通过这些数据可以分析命令的大致比例
  MySQL运行状态show status中参数

2.慢SQL日志

什么是慢SQL呢，如名字所示就是执行比较慢的SQL。 我们可以开启慢SQL日志，然后mySQL就会把查询比较慢的SQL放到日志里面，这样我们就可以发现哪些比较慢了。
使用show variables like 'slow_query_log' 查看慢SQL日志是否开启。 使用 set global slow_query_log = 1; 开启慢SQL日志。但是使用set global slow_query_log = 1; 只能对当前数据库起作用，当重启数据库后就会失效。想要永久生效，就需要修改配置文件my.cnf。（其他变量也是要这样）,还需要设置 slow_query_log_file， 也就是日志的位置。
那这个慢SQL日志是指多慢呢， 是由long_query_time的值来控制的，默认为10秒。 是大于，等于是不会的。
MySQL慢查询日志总结

3.使用ExPlain 分析慢SQL

在前面查出了慢sql。 然后我们就可以对这条sql为什么慢进行分析了。
ExPlain。 就是在select 前面加上EXPLAIN就会显示分析出来的信息了

• select_type: 表示SELECT的类型，常见的有SIMPLE(简单表，即不使用表连接或子查询)、PRIMARY(主查询，即外层的查询)、 UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个SELECT)等。
• table: 输出结果集的表
• type: 表示MySQL在表中找到所需行的方式，或者叫访问类型。
• possible_keys: 表示查hh询时可能使用的索引
• kye: 表示实际使用的索引
• key_len: 使用到索引字段的长度
• rows: 扫描行的数量
• Extra: 执行情况的说明和描述，包含不合适再其他列显示但是对执行计划非常重要的额外信息。

3.1 type解析

性能最差到最好的排序

1. ALL 全表扫描，MySQL遍历全表来找到匹配的行

EXPLAIN SELECT * FROM film WHERE rating > 9

2. index 索引全扫描，MySQL遍历整个索引来查询匹配的行

EXPLAIN SELECT title FROM film

3. range 索引范围扫描， 常见于<、<=、>、>=、between

EXPLAIN SELECT * FROM payment WHERE customer_id > 300 AND customer_id <= 350

4. ref 使用非唯一索引或唯一索引的前缀扫描，返回匹配某个单独值的记录行

EXPLAIN SELECT * FROM payment WHERE customer_id = 350

5. eq_ref 类似于ref, 区别就是再使用索引的时候是唯一索引, 简单来所，就是多表连接中使用prmay key 或者unique index作为关联条件。

EXPLAIN SELECT * FROM film a, film_text b WHERE a.film_id  = b.film_id

6. const,system 单表中最多有一个匹配行， 查询起来非常迅速，相当于常量查询，列如，根据primary key或唯一索引 unique index进行查询。

EXPLAIN SELECT * FROM customer WHERE customer_id = 1

7. NULL MySQL不用访问表或者索引，直接就能得到结果。

explain select 1 from dual where 1

4.通过show profile分析SQL

5. 通过trace分析优化器如何选择执行计划

什么是索引

白话一点的来说，就是一本书的目录， 官方的来说：索引(Index)是帮助MySQL高效获取数据的数据结构。
当我们对表设置主建的时候，mySql 就会自动添加一个索引。

索引的储存分类

索引是在mysql的引擎层中实现的，不是在服务器层实现的。

• B-Tree 索引， 也是就是B数索引。最常见的一个索引，大部分引擎也支持
• HASH 索引， 只有Memor引擎支持，使用场景简单
• R-Tree索引（空间索引）： 是MyISAM的一个特殊索引，主要用于地理空间
• FULL-text(全文索引)：是MyISAM的一个特殊索引，用于全文索引，InnoDB从5.6版本开始提供对全文索引的支持。

较为常用的就是B-Tree索引和Hash索引， 只有Memory/NDB引擎支持完全的hash索引，InnoDB存储引擎在MySQL 5.7中支持自适应的Hash索引。所谓的自适应，就是MySQL根据数据的访问频率和模式为某些热点页自动创建Hash索引，索引由bffer pool中的B-tree来自动生成，效率很高，
Hash索引适用于Key-Value查询，通过Hash索引要比通过B-Tree索引查询更迅速，Hash索引不适用范围查询，比如<、>、<=、>=这类操作。Memory/NDB引擎只有在where 中使用 '=‘的条件下才会使用索引

索引会生效的场景

1. 全值匹配: 对索引中的所有列都有等值匹配的条件。
2. 匹配值的范围查询 ，对索引的值能够进行范围查找。
3. 匹配最左前缀，复合索引（A，B, C）。 查询的时候可以a,b 但是不能b,c。
4. 仅仅对索引进行查询，当查询的列都在索引的字段时，查询的效率更高。
5. 匹配列前缀，仅仅使用索引中的第一例, 并且只包含索引第一列的开头一部分查找
6. 能够实现索引匹配部分精确而其他部分进行范围匹配。列如，需要查询出租日期rental_date为指定日期且客户编号customer_id为指定范围的库存。

索引会失效的场景（不使用索引）

1. 以%开头的LIKE查询会失效
2. 数据类型出现隐式转换的时候也会索引失效

比如 where name = 1。 这样就会不使用索引.需要使用引号包起来， where name = ‘1’

3. 复合索引的情况下， 假如查询条件不包含索引列最左边部分，即不满足最左匹配原则。
4. 如果MySQL估计使用索引比全表扫描更慢， 则不使用索引。
5. 用or分割开的条件。 只有or中有一列没有索引，就会进行全表索引

查看索引使用情况

show status like 'Hand_read'

1. Handler_read_first：此选项表明SQL是在做一个全索引扫描，注意是全部，而不是部分，所以说如果存在WHERE语句，这个选项是不会变的。
2. Handler_read_key：此选项数值如果很高，那么恭喜你，你的系统高效的使用了索引，一切运转良好。
3. Handler_read_next：此选项表明在进行索引扫描时，按照索引从数据文件里取数据的次数。
4. Handler_read_prev：此选项表明在进行索引扫描时，按照索引倒序从数据文件里取数据的次数，一般就是ORDER BY … DESC
5. Handler_read_rnd：就是查询直接操作了数据文件，很多时候表现为没有使用索引或者文件排序。
6. Handler_read_rnd_next：此选项表明在进行数据文件扫描时，从数据文件里取数据的次数。

6.定期优化表

optimze table 表名
如果已经删除了表的一大部分，或者如果已经对含有可变长度的表（含有VARCHAR、BLOB、 TETE的表）进行了很多修改，则应该使用OPTIMIZE TABLE命令进行表优化。