MySQL数据索引与优化

ALTER TABLE table_name ADD INDEX index_name (column_list)  //普通索引
ALTER TABLE table_name ADD UNIQUE (column_list)   //唯一性索引，设置后，不允许插入重复值
ALTER TABLE table_name ADD PRIMARY KEY (column_list) //唯一索引，通常用于自增主键。

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

 SHOW INDEX FROM tblname;
 SHOW KEYS FROM tblname;

alter table com_pdl_stat add key `left_pdl`(pdl(9));

// 查看是否开启了慢查询日志
show variables like '%slow_query_log';
// 开启
set global slow_query_log='ON';
// 执行show variables like '%slow_query_log'就有log_file地址
+---------------------+--------------------------------------------+
| Variable_name       | Value                                      |
+---------------------+--------------------------------------------+
| slow_query_log      | ON                                         |
| slow_query_log_file | /usr/local/mysql/data/TEST-slow.log |
+---------------------+--------------------------------------------+
// 查看慢查询阈值
show variables like '%long_query_time%';
// 调整阈值
set global long_query_time = 1;
// 关闭当前session再开启，执行一个超过1s的SQL时，将被记录在 `/usr/local/mysql/data/TEST-slow.log`里

// 使用JOIN时会有两条记录
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | a2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  180 |   100.00 | NULL                                               |
|  1 | SIMPLE      | a1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  732 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
type字段含义
 	- all  全数据表扫描 == 这种情况下的是急需优化的
 	- index  全索引扫描，此时如果extra = Using index，则索引字段覆盖select的字段，不需要到回表扫描，效率更高些。
	- range  在索引中进行范围查找
	- index_merge 使用多个单列索引合并查找
- filtered 命中率。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，图为百分之10。此值过低也不行。
- key 查询中使用到的索引

// 查询profile是否开启
show variables like 'profiling';
// 开启
set profiling = 'ON';
// 再执行以下，将显示 id -> 最近操作的SQL
show profiles;

// show profile for query $id时，展示某个指令详细消耗情况。以下子命令可跟在profile后展示。
// help profile 可查看show profile子命令
type: {
    ALL  			// 展示所有
  | BLOCK IO		// 显示磁盘块IO消耗时间
  | CONTEXT SWITCHES	// 上下文切换开销
  | CPU				// 用户+系统cpu消耗时间
  | IPC				// 通信发送/接受消息消耗时间
  | MEMORY			// 暂未处理
  | PAGE FAULTS		// 页面错误消耗时间
  | SOURCE			// 显示和source_funcation、source_file、source_line相关的开销信息
  | SWAPS			// 显示交换次数
}

// 样例
mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000351 | 	// 开始
| checking permissions | 0.000009 |		// 检查权限
| checking permissions | 0.000007 |		// 检查权限(有两个表)
| Opening tables       | 0.000624 |		// 打开表
| init                 | 0.000116 |		// 初始化
| System lock          | 0.000154 |		// 系统?
| optimizing           | 0.000039 |		// 优化
| statistics           | 0.000047 |		// 统计
| preparing            | 0.000164 |		// 准备
| executing            | 0.000007 |		// 执行
| Sending data         | 0.012874 |		// 发送+接收数据
| end                  | 0.000008 |		// 结束
| query end            | 0.000019 |		// 查询结束标志
| closing tables       | 0.000032 |		// 关闭表
| freeing items        | 0.000017 |		// 释放信息
| logging slow query   | 0.000917 |		// 记录慢查询日志
| cleaning up          | 0.000078 |		// 清理
+----------------------+----------+