选择合适的索引列

1.在where，group by，order by，on从句中出现的列

2.索引字段越小越好(因为数据库的存储单位是页，一页中能存下的数据越多越好 )

3.离散度大的列放在联合索引前面

select count(distinct customer_id), count(distinct staff_id) from payment;

是index(sftaff_id,customer_id)好？还是index(customer_id,staff_id)好呢？

由于customer_id的离散度更大，所以应该使用index(customer_id,staff_id)好。

离散度：数据唯一值越多，离散度越高。例如，       select count(distinct id),count(distinct real_name) from user; id 是唯一值，所以离散度更高。而 real_name 会出现重复—>会有同名同姓的情况，所以离散度低。

查看离散度 通过统计不同的列值来实现 count越大 离散程度越高

优化limit查询

优化Limit查询

limit常用于分页处理，时常会伴随order by从句使用，因此大多时候会使用Filesorts这样会造成大量的io问题

1.使用有索引的列或主键进行order by操作

2.记录上次返回的主键，在下次查询时使用主键过滤

即将：select film_id,description from sakila.film order by film_id limit 50,5;

改为：select film_id,description from sakila.film where file_id >55 and film_id<=60 order by film_id limit 1,5;

使用这种方式有一个限制，就是主键一定要顺序排序和连续的，如果主键出现空缺可能会导致最终页面上显示的列表不足5条，解决办法是附加一列，保证这一列是自增的并增加索引就可以了

group by可能会出现临时表（Using temporary），文件排序（Using filesort）等，影响效率。<br> 可以通过关联的子查询，来避免产生临时表和文件排序，可以节省io

explain select actor.first_name,actor.last_name,count(*) from film_actor inner join actor on actor.actor_id = film_actor.actor_id group by actor.actor_id\G 

改写为：

 explain select actor.first_name,actor.last_name,c.cnt from actor inner join (select actor_id,count(*) AS cnt from film_actor GROUP BY actor_id) AS c USING(actor_id)\G

1.数据表是按照行的方式进行存储的，一个数据表中包含了许多行，而每一行由不同的字段组成，这些字段称为列，这就是数据表的结构；

2.在查询的时候，如果没有指定索引，那么必须要对每一行都进行扫描，然后才能找到每行中被查询的字段，所以可以说，在没有索引的情况下，要扫描整个表才可以查询到需要的数据；

3.在添加了索引之后，那么会增加一个索引表，这个索引表记录了索引值 与 对应字段的关系，然后，以该字段进行的查询操作，将不再需要扫描原来的数据表的每一行，而是扫描这个建立的索引表，显然，这个索引表的IO的操作就比原来的数据表要小很多了，所以可以提升查询的速度，并且如果表的字段比较多的情况，那么建立索引的总用越明显；同时，因为要维护这个索引表，所以当进行增，删，改的时候，性能会相对下降；

4.覆盖索引，就是说 通过索引的值，在索引表中就可以找到需要的值

count(*)    //统计数量，包含null在内

count(字段名）  //统计数量，不包含null在内

创建索引：create index 索引名 on 表名(字段名）;

count()和max()的优化：

比如max()查询最后支付时间：给支付时间列建立索引 create index idx_paydate on paydate(payment_date);

count() 在同一条SQL中同时查询出2006和2007年电影的数量：select count(release_year='2006' or null) as '2006年电影数量',count(release_year='2007' or null)as '2007年电影数量' from film;

SQL及索引优化

explain返回的含义

explain返回的信息

如何分析SQL查询：

explain返回各列的含义

table:显示这一行的数据是关于哪张表的

type:这是重要的列，显示连接使用了何种类型。从最好的到最差的连接类型为const/eq_reg/ref/range/index和ALL

possiable_keys:显示可能应用在这张表中的索引。如果为空，没有可能的索引。

key:实际使用的索引。如果为Null,则没有使用索引。

key_len:使用的索引的长度，在不损失精确性的情况下，长度越短越好。

ref:显示索引的哪一列被使用了，如果可能的话，是一个常数。

rows:MYSQL认为必须检查的用来返回请求数据的行数。

1.查询次数多且每次查询占用时间长的SQL

  通常为pt-query-digest分析的前几个查询

2.IO大的SQL

  注意pt-query-digest分析中的Rows examine项

3.未命中索引的SQL

  注意pt-query-digest分析中的Rows examine和Row send 的对比

当Rows examine>>Row send 基本上是使用了索引扫描或者表扫描的方式来进行查询，需要进行优化

pt-query-digest 慢查询日志文件路径 | more     //分析慢查询日志，并生成报表