1.       跟踪并添加索引示例

1.1.寻找慢查询

 

mysql>show full processlist\G;

***************************1. row ***************************

     Id: 35641

   User: root

   Host: localhost

     db: test

Command:Query

   Time: 35

  State: Sending data

   Info: select * from sbtest where c is notnull

1.2.确认低效查询并记录执行时间

 

         使用命令行客户端或其他客户端运行SQL语句验证是简单的方法。

1.3.生成查询执行计划

         生成查询执行计划（QueryExecution Plan，QEP）

         当mysql要执行一个sql查询计划的时候，首先会对该sql语句进行语法检查，然后构造一个QEP，查询执行计划决定了mysql从底层存储引擎中获取信息的方式。要查看mysql查询优化器为sql语句构造的查询执行计划，只需使用explain命令。

mysql>EXPLAIN EXTENDED select * from sbtest where c is not null;

+----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+

| id |select_type | table  | type |possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |

+----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+

|  1 | SIMPLE      | sbtest | ALL  | NULL          | NULL | NULL    | NULL | 9000106 |   100.00 | Using where |

+----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+

1 row inset, 1 warning (0.04 sec)

 

mysql>show warnings\G;

***************************1. row ***************************

  Level: Note

   Code: 1003

Message:/* select#1 */ select `test`.`sbtest`.`id` AS `id`,`test`.`sbtest`.`k` AS`k`,`test`.`sbtest`.`c` AS `c`,`test`.`sbtest`.`pad` AS `pad` from`test`.`sbtest` where (`test`.`sbtest`.`c` is not null)

1 row inset (0.00 sec)

1.4.优化查询

 

         在没有进一步验证的情况下，千万不要在生产环境中添加索引。alter是阻塞操作，表添加或者修改数据的额外请求都被阻塞，根据数据操作语言DML的执行顺序，此时select语句也会被阻塞而无法完成。如果表更大的话，一个alter语句可能需要几小时甚至几天才能执行完成，另一个需要考虑的因素是在一个表有多个索引的情况下DML的性能开销。

1.5.确认优化

         重新运行SQL查询；

         通过查看修正了的QEP来确认新索引的效率；

1.6.为表添加索引的正确方式

 

         至少要做两项检查：

1.6.1.      验证表结构

         验证表现有的结构；

mysql>show create table sbtest\G;

***************************1. row ***************************

       Table: sbtest

CreateTable: CREATE TABLE `sbtest` (

  `id` int(10) unsigned NOT NULLAUTO_INCREMENT,

  `k` int(10) unsigned NOT NULL DEFAULT '0',

  `c` char(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

)ENGINE=InnoDB AUTO_INCREMENT=9000001 DEFAULT CHARSET=latin1

1 row inset (0.03 sec)

1.6.2.      确认表大小

         确认表的大小。

mysql>show table status like 'sbtest'\G;

***************************1. row ***************************

           Name: sbtest

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 9000106

 Avg_row_length: 224

    Data_length: 2022703104

Max_data_length:0

   Index_length: 123371520

      Data_free: 0

 Auto_increment: 9000001

    Create_time: 2014-09-19 16:38:34

    Update_time: NULL

     Check_time: NULL

      Collation: latin1_swedish_ci

       Checksum: NULL

 Create_options:

        Comment:

mysql>show table status\G;

***************************1. row ***************************

           Name: aa1

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 7

 Avg_row_length: 2340

    Data_length: 16384

Max_data_length:0

   Index_length: 0

      Data_free: 0

 Auto_increment: NULL

    Create_time: 2014-08-01 10:06:39

    Update_time: NULL

     Check_time: NULL

      Collation: latin1_swedish_ci

       Checksum: NULL

 Create_options:

       Comment:

***************************2. row ***************************

           Name: b2

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 0

 Avg_row_length: 0

    Data_length: 16384

Max_data_length:0

   Index_length: 0

      Data_free: 0

 Auto_increment: NULL

    Create_time: 2014-08-01 11:07:16

    Update_time: NULL

     Check_time: NULL

      Collation: latin1_swedish_ci

       Checksum: NULL

 Create_options:

        Comment:

***************************3. row ***************************

           Name: sbtest

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 9000106

 Avg_row_length: 224

    Data_length: 2022703104

Max_data_length:0

   Index_length: 123371520

      Data_free: 0

 Auto_increment: 9000001

    Create_time: 2014-09-19 16:38:34

    Update_time: NULL

     Check_time: NULL

      Collation: latin1_swedish_ci

       Checksum: NULL

 Create_options:

        Comment:

         注意：不必要的索引将会导致额外的开销。