MySQL索引和SQL优化

-- 查看数据库的编码
show variables like 'character%';

-- 查看数据库的SQL引擎
show engines;

-- 查看当前数据库的SQL引擎
show variables like 'default_storage_engine%';

-- 查看表的索引
-- Non_unique: 0代表唯一索引，1代表非唯一索引
-- Key_name:索引名称
-- Seq_in_index:索引中列序号，如果Key_name相同则代表是联合索引，序号代表前后顺序
-- Column_name:索引的列名
-- Cardinality:基数；基数越大，代表重复的值越少，适合建索引。基数越小，代表重复的值多，不适合建索引。
SHOW INDEX FROM index_statistics_day_copy1 IN kkb_bac_statistics;

-- 创建索引
ALTER TABLE index_statistics_day_copy1 ADD INDEX index_test(year,month);

-- 删除索引
DROP INDEX index_test ON index_statistics_day_copy1;

-- MySQL的索引数据结构：B+Tree/Hash索引（看视频）

-- 需要创建索引的字段
-- （1）主键自动创建主键索引（唯一+非空）
-- （2）WHERE条件里面的字段
-- （3）外键
-- （4）ORDER BY的字段
-- （5）GROUP BY的字段

-- 不需要建索引的情况
-- （1）小表
-- （2）经常增删改的表
-- （3）频繁更新的字段
-- （4）WHERE条件里没有的字段
-- （5）字段内值大多为重复的字段

EXPLAIN SELECT * FROM index_statistics_day_copy1;

id:表的读取和加载顺序。

值的三种情况：

id相同，执行顺序由上到下。

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先执行。

select_type:
-- SIMPLE:简单查询无子查询或者UNION。
-- PRIMARY:查询中如果包含任务复杂的子部分，最外层查询则被标记为PRIMARY。
-- SUBQUUERY:select或where子句中包含了子查询。
-- DERIVER:在FORM子句中包含的子查询。
-- UNION：从UNION表获取结果的SELECT。
type：
从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL。除了ALL没有用到索引，其他级别都用到索引了。
System：库里只有一条记录；
Const：通过索引一次找到；
eq_ref:唯一性索引
ref：非唯一性索引
Range:范围性查询
index:全索引
ALL：全表扫描

possible_keys和key

possible_kyes:显示可能应用在这张表的索引，一个或者多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。

key：实际使用的索引，如果为null，则没有使用索引。

key_len：表示索引中使用的字节数。

ref:显示索引的哪一列被使用。

rows：表示表统计信息及索引选用情况。

Extra：包含不适合在其他列但十分重要的额外信息。

     Using filesort：MySQL使用了文件内排序，没有使用到索引进行order by。

     Using temporary：使用了临时表保存中间结果，MySQL在查询结果排序时创建了临时表。临时表对系统性能损耗很大。

   Using index：表示相应的SELECT操作中使用了覆盖索引，避免访问了表的数据行。

覆盖索引：select的数据列可以直接在索引中获取，不必从数据表中读取。

   Using where：使用了where过滤

  Using join buffer：使用了连接缓存

  impossible where：where子句的值总是false，不能获取任何结果。