本文所使用的测试表结构如下：
CREATE TABLE actor( id BIGINT AUTO_INCREMENT COMMENT '主键' PRIMARY KEY, first_name VARCHAR(255) DEFAULT '' NOT NULL COMMENT '姓', last_name VARCHAR(255) DEFAULT '' NOT NULL COMMENT '名', email VARCHAR(255) DEFAULT '' NOT NULL COMMENT '邮箱', gender TINYINT DEFAULT '1' NOT NULL COMMENT '性别：1-男士，2-女士' );文中使用的测试数据是使用程序生成的，具体的生成程序请克隆数据生成工具中的项目运行生成，部分示例数据如下：
mysql> select * from actor limit 10; +-----+---------------------+---------------------+---------------------------------+--------+ | id | first_name | last_name | email | gender | +-----+---------------------+---------------------+---------------------------------+--------+ | 1 | qWhNIZqxcbD | rxkPMBqBvWnzbJe | FKZUldeSggtZ@saN.vfS | 2 | | 2 | sFsCUyFsmrrRbSOa | rMqChueZJThP | tZqaNHJwQEjwobA@UaJtk.oxr | 1 | | 3 | rMqChueZJThP | YuzpZwrsYbCATXlsLxx | jiaPbEhUecygLj@lshCA.HKA | 1 | | 4 | aFGRpVCdQjvJrvg | ynZBRNYgCfowzNtCqR | ZxqdOxFIYinQBs@NnoXI.xTfBo | 1 | | 5 | RaGEPWLxepknJw | GOywmnVQEUtPNxSG | iNRUjDxGgbaKPZfGXE@MyMXc.QeHv | 2 | | 6 | EQwbpQODEuFUs | aUSwXoIKosuVlcjiKJ | WQSeQtQHdoDXUbn@JeCo.Xrof | 1 | | 7 | YbjiVKQfplqUAhNuKg | DqvNpyhPmMHgPPAF | qwXeylhiVkEYTnEne@PLab.NqE | 2 | | 8 | mSSLjRlVPFfVhve | HXqDkQTfQwh | XkUfqzJdeoBdbuPZK@Urp.bsfc | 2 | | 9 | mDZNpjfJJZOdB | rMqChueZJThP | XHNSFXfBRLG@IBFez.EMi | 1 | | 10 | zPeLuNPfStQyny | qWhNIZqxcbD | sKupgyQxud@aUD.mce | 2 | +-----+---------------------+---------------------+---------------------------------+--------+该表数据量为1000w，各个字段的选择性如下：first_name 10% last_name 10% email 1% gender 50%。字段选择性指的是按该字段分组后，数据量最大的组的数据量占总数据量的比例，比如这里first_name中同一个值的数据最多为10w条。
2.1 单列索引 2.1.1 单列索引单列索引指的是在表上为某一个字段建立的索引，一般索引的创建选择整型或者较小的定长字符串将更有利于效率的提升，这里作为演示，我们对first_name字段建立了索引，如下所示为使用该索引与不使用索引时在actor表上相同的查询效率情况比较：
SQL语句: select * from actor where first_name='rMqChueZJThP';
索引 无索引 first_name耗时 7.05s 0.15s
从查询结果可以看出，对于单字段查询，使用索引和不使用索引查询的耗时区别是非常大的。这里我们可以使用show profiles命令查看两次查询的详细耗时，关于show profiles的用法如下：
mysql> set profiling=1; mysql> 执行SQL语句; mysql> set profiling=0; mysql> show profiles; mysql> show profile cpu, block io for query 1;最后一步中的query id是前一步中查询结果中相关查询的id。对于前述SQL语句的执行耗时的查询结果如下：
+----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000075 | 0.000070 | 0.000017 | 0 | 0 | | checking permissions | 0.000019 | 0.000005 | 0.000002 | 0 | 0 | | Opening tables | 0.000029 | 0.000019 | 0.000007 | 0 | 0 | | init | 0.000048 | 0.000037 | 0.000006 | 0 | 0 | | System lock | 0.000010 | 0.000008 | 0.000003 | 0 | 0 | | optimizing | 0.000008 | 0.000007 | 0.000001 | 0 | 0 | | statistics | 0.000016 | 0.000015 | 0.000000 | 0 | 0 | | preparing | 0.000010 | 0.000009 | 0.000001 | 0 | 0 | | executing | 0.000002 | 0.000001 | 0.000001 | 0 | 0 | | Sending data | 7.051533 | 6.633832 | 0.366078 | 0 | 0 | | end | 0.000011 | 0.000005 | 0.000005 | 0 | 0 | | query end | 0.000007 | 0.000007 | 0.000001 | 0 | 0 | | closing tables | 0.000009 | 0.000007 | 0.000000 | 0 | 0 | | freeing items | 0.000047 | 0.000013 | 0.000035 | 0 | 0 | | cleaning up | 0.000021 | 0.000014 | 0.000007 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+不使用索引的情况
+----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000094 | 0.000085 | 0.000008 | 0 | 0 | | checking permissions | 0.000010 | 0.000008 | 0.000003 | 0 | 0 | | Opening tables | 0.000023 | 0.000022 | 0.000001 | 0 | 0 | | init | 0.000040 | 0.000038 | 0.000002 | 0 | 0 | | System lock | 0.000011 | 0.000009 | 0.000002 | 0 | 0 | | optimizing | 0.000012 | 0.000010 | 0.000001 | 0 | 0 | | statistics | 0.000123 | 0.000109 | 0.000015 | 0 | 0 | | preparing | 0.000017 | 0.000014 | 0.000003 | 0 | 0 | | executing | 0.000003 | 0.000002 | 0.000001 | 0 | 0 | | Sending data | 0.152585 | 0.142192 | 0.007219 | 0 | 0 | | end | 0.000016 | 0.000004 | 0.000005 | 0 | 0 | | query end | 0.000008 | 0.000007 | 0.000001 | 0 | 0 | | closing tables | 0.000010 | 0.000010 | 0.000001 | 0 | 0 | | freeing items | 0.000019 | 0.000010 | 0.000009 | 0 | 0 | | cleaning up | 0.000034 | 0.000015 | 0.000019 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+使用索引的情况