一、索引原理

1.1. 使用索引为什么会快?

btree类型的索引，就是使用的二分查找法，肯定快啊，算法复杂度是 log2N，也就是说16条数据查4次，32条数据查5次，64条数据查6次....依次类推。

btree 方式检索，算法复杂度： log2N

1.2. 索引的代价

磁盘占用空间增多

对dml(update delete insert)语句的效率影响

1.3、例子

①、假设我们创建了一个名为people的表：

CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL );

②、然后，我们完全随机把1000个不同name值插入到people表。

在数据文件中name列没有任何明确的次序。如果我们创建了name列的索引，MySQL将在索引中排序name列,对于索引中的每一项，MySQL在内部为它保存一个数据文件中实际记录所在位置的“指针”。

③、因此，如果我们要查找name等于“Mike”记录的peopleid

（SQL命令为“SELECT peopleid FROM people WHERE name='Mike';”），MySQL能够在name的索引中查找“Mike”值，然后直接转到数据文件中相应的行，准确地返回该行的peopleid（999）。

④、在这个过程中，MySQL只需处理一个行就可以返回结果。如果没有“name”列的索引，MySQL要扫描数据文件中的所有记录，即1000个记录！显然，需要MySQL处理的记录数量越少，则它完成任务的速度就越快。

二、索引类型

简述mysql四种索引的区别：

• PRIMARY 索引：在主键上自动创建

• UNIQUE 索引： 只要是 UNIQUE 就是 Unique 索引。(只能在字段内容不重复的情况下，才能创建唯一索引)

• INDEX 索引：普通索引

• FULLTEXT：只在 MYISAM 存储引擎支持, 目的是全文索引，在内容系统中用的多，在全英文网站用多(英文词独立). 中文数据不常用，意义不大，国内全文索引通常使用 sphinx 来完成，全文索引只能在 char、varchar、text 字段创建

三、关于增加索引中的原则

• 索引列数最好在1-2

• 根据where条件创建索引，select的字段不要包含什么索引（用＊号）

• 不要过多用索引，否则对表更新的效率有很大的影响，因为在操作表的时候要化大量时间花在创建索引中

• 对于复合索引，在查询使用时，最好将条件顺序按找索引的顺序，这样效率最高

• 不要试图分别基于单个列建立多个单列索引（因为虽然有多个单列索引，但是MySQL只能用到其中的那个它认为似乎最有效率的单列索引）

四、哪些列上适合添加索引

• 较频繁的作为查询条件字段应该创建索引

• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

• 更新非常频繁的字段不适合创建索引

• 不会出现在WHERE子句中的字段不该创建索引

五、索引常用操作

5.1. 添加PRIMARY KEY（主键索引）

mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

5.2. 添加UNIQUE(唯一索引)

mysql>ALTER TABLE `table_name` ADD UNIQUE (`column`)

5.3. 添加INDEX(普通索引)

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

5.4. 添加FULLTEXT(全文索引)

mysql>ALTER TABLE `table_name` ADD FULLTEXT (`column`)

5.5. 添加多列索引

注意：当搜索时候需要多个条件作为条件是的时候使用多列索引，搜索条件可为：column1;column1,column2;column1,column2,column3;

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

5.6. 查看索引

mysql> show index from tblname;

5.7.删除索引

DROP INDEX index_name ON table_name

六、如何让索引生效

查询要使用索引最重要的条件是查询条件中需要使用索引。

可以通过 explain，查看 SQL 中的索引是否生效：

explain select * from ip;

+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | ip    | ALL  | NULL          | NULL | NULL    | NULL |  400 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

6.1. 下列几种情况下有可能使用到索引

• 对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。

• 对于使用like的查询，查询如果是 '%aaa' 不会使用到索引， 'aaa%' 会使用到索引。

6.2. 下列的表将不使用索引

• 如果条件中有or，即使其中有条件带索引也不会使用。

• 对于多列索引，不是使用的第一部分，则不会使用索引。

• like 查询是以%开头

• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须'')

• 如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

七、工作原理解析

7.1、什么是优化器

① MySQL采用了基于开销的优化器，以确定处理查询的最解方式，也就是说执行查询之前，都会先选择一条自以为最优的方案，然后执行这个方案来获取结果。在很多情况下，MySQL能够计算最佳的可能查询计划，但在某些情况下，MySQL没有关于数据的足够信息，或者是提供太多的相关数据信息，估测就不那么友好了。

② MySQL怎么去寻找最优方案呢？

MySQL优化器中，一个主要的目标是只要可能就是用索引，而且使用条件最严格的索引来尽可能多、尽可能快地排除那些不符合索引条件的数据行，说白了就是选择怎样使用索引，当然优化器还受其他的影响。

7.2、例子

① 创建一个表

CREATE TABLE t8(
id1 INT NOT NULL ,
id2 INT NOT NULL,KEY id1_key(`id1`),KEY id2_key(`id2`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

② 插入数据

INSERT INTO t8 VALUES(1,2);
INSERT INTO t8 VALUES(3,4);
INSERT INTO t8 VALUES(1,3);
INSERT INTO t8 VALUES(1,1);
INSERT INTO t8 VALUES(5,0);
INSERT INTO t8 VALUES(1,0);

③ 思考：当我执行如下查询语句时候，查询优化器会怎样进行优化呢？

select * from t8 where id1=1 and id2=0;

当然，MySQL不会傻到，从t8表中的一行开始，然后一行行的去比较，id1与id2。优化器会先分析数据表，得知有索引id1_key与id2_key，如果先判断id1_key的话，然后需要从4行数据中排除3行数据；如果先判断id2_key的话，然后需要从2行中排除1行。

对人来说，这两种方式没有什么区别，但是对于程序而言，先判断id2_key需要较少的计算和磁盘输入输出。

因此，查询优化器会规定程序，先去检验id2_key索引，然后在从中挑出id2为0的数据行。

通过下面数据，我们可以看出，可以选择的索引有id1_key与id2_key，但是实际用到的索引只有id2_key 

mysql> explain select * from t8 where id1=1 and id2=0;
+----+-------------+-------+------+-----------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys   | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | t8    | ref  | id1_key,id2_key | id2_key | 4       | const |    2 | Using where |
+----+-------------+-------+------+-----------------+---------+---------+-------+------+-------------+

如果将SQL语句改为 select * from t8 where id1=1 and id2=0;执行情况也是一样的，不区分前后。

当然，如果将程序，修改为如下：

select * from t8 where id1=5 and id2=0;

也可以分析得出，会使用id1_key索引

mysql> explain select * from t8 where id1=5 and id2=0;
+----+-------------+-------+------+-----------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys   | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | t8    | ref  | id1_key,id2_key | id1_key | 4       | const |    1 | Using where |
+----+-------------+-------+------+-----------------+---------+---------+-------+------+-------------+

当然，如果在创建一个复合索引

ALTER TABLE t8 ADD KEY id1_id2_key(`id1`,`id2`)

此时，在此执行select * from t8 where id1=1 and id2=0; 当然会考虑使用id1_id2_key索引。

mysql> explain select * from t8 where id1=1 and id2=0;
+----+-------------+-------+------+-----------------------------+-------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys               | key         | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+-----------------------------+-------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | t8    | ref  | id1_key,id2_key,id1_id2_key | id1_id2_key | 8       | const,const |    1 | Using index |
+----+-------------+-------+------+-----------------------------+-------------+---------+-------------+------+-------------+

通过上面的例子，可以理解查询优化器在查询的时候，是选择哪一个索引作为最合适的索引。除此，也提示我们，要慎重选择创建索引。如，上面创建了三个索引（id1_key、id1_key、id1_id2_key），但是优化器优化程序时候，每次只能从中选择一个最合适的，如果创建过多，不仅仅是给数据的更新和插入带来了压力，同时也增加了优化器的压力。

7.3、优化

① 强制索引：FORCE INDEX

通过FORCE INDEX(索引1[，索引2])或者使用USE INDEX(索引1[，索引2])，来指定使用哪个索引，也可以指定多个索引，让优化器从中挑选。

explain select * from t8 force index("id1_key") where id1=1 and id2=0;

② 忽略索引：IGNORE INDEX

可以使用IGNORE INDEX(索引1[，索引2])来忽略一些索引，这样优化器，就不会考虑使用这些所有，减少优化器优化时间。

explain select * from t8 ignore index("id1_id2_key,id2_key") where id1=1 and id2=0;

③ 影响优化器使用数据表的顺序：STRAIGHT_JOIN

创建 t6 表格

CREATE TABLE t6(
id1 INT NOT NULL
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

插入数据

INSERT INTO t6 VALUES(1);
INSERT INTO t6 VALUES(2);
INSERT INTO t6 VALUES(3);
INSERT INTO t6 VALUES(4);
INSERT INTO t6 VALUES(5);

分析使用表格的先后顺序

mysql> explain select * from t8,t6 where t8.id1=t6.id1;
+----+-------------+-------+------+---------------------+-------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys       | key         | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------------+-------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | t6    | ALL  | NULL                | NULL        | NULL    | NULL        |    5 |             |
|  1 | SIMPLE      | t8    | ref  | id1_key,id1_id2_key | id1_id2_key | 4       | test.t6.id1 |    1 | Using index |
+----+-------------+-------+------+---------------------+-------------+---------+-------------+------+-------------+

可以看出，先检索t6中的表。

但是使用 STRAIGHT_JOIN 的话，就会按照SQL中顺序。

mysql> explain select straight_join * from t8,t6 where t8.id1=t6.id1;
+----+-------------+-------+-------+---------------------+-------------+---------+------+------+--------------------------------+
| id | select_type | table | type  | possible_keys       | key         | key_len | ref  | rows | Extra                          |
+----+-------------+-------+-------+---------------------+-------------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | t8    | index | id1_key,id1_id2_key | id1_id2_key | 8       | NULL |    6 | Using index                    |
|  1 | SIMPLE      | t6    | ALL   | NULL                | NULL        | NULL    | NULL |    5 | Using where; Using join buffer |
+----+-------------+-------+-------+---------------------+-------------+---------+------+------+--------------------------------+

为什么优化器要选择先判断t6中的数据呢？一个主要的原因，因为t6中数据更少：t6中5条数据、t8中6条数据。

7.4、优先权

① HIGH_PRIORITY

在高并发的网站中，因为MySQL默认的是写优先，有可能导致一些读操作有效时间内得不到执行机会，HIGH_PRIORITY可以使用在select和insert操作中，让MYSQL知道，这个操作优先进行。

select high_priority * from t8;

② LOW_PRIORITY

可以使用在insert和update操作中，让mysql知道，这个操作将优先权将降低。

update low_priority t8 set id1=3 where id2=0;

③ INSERT DELAYED

告诉MySQL，这个操作将会延时插入。

insert delayed into t8 values(5,5);

是客户端提交数据给MySQL，MySQL返回OK状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql有空余时，再插入。另一个重要的好处是，来自许多客户端的插入被集中在一起，并被编写入一个块。这比执行许多独立的插入要快很多，因为它较少了I/O操作。坏处是，不能返回自动递增的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。 

7.5、查询缓冲

在实际开发中，一些数据对实时性要求特别高，或者并不经常使用（可能几天就执行一次或两次）,这样就需要把缓冲关了,不管这条SQL语句是否被执行过，服务器都不会在缓冲区中查找该数据，每次都会从磁盘中读取。因为如果实时性要求特别高，缓存中数据可能和磁盘中的就不同步，如果数据不经常使用，被缓存起来，就会占用内存。

在my.ini中的query_cache_type，使用来控制表缓存的。这个变量有三个取值：0,1,2，分别代表了off、on、demand。

• 0：表示query cache 是关闭。

• 1：表示查询总是先到查询缓存中查找，即使使用了sql_no_cache仍然查询缓存，因为sql_no_cache只是不缓存查询结果，而不是不使用查询结果。

• 2：表示只有在使用了SQL_CACHE后，才先从缓冲中查询数据，仍然将查询结果缓存起来。

我本地缓存是开启的的，如下所示：

mysql> show variables like "query_cache_type";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+

八、小贴士

8.1、mysql in 中使用子查询，会不使用索引而走全表扫描

解决：以将 in 条件中 子查询转换成一张子表，从而通过 join 的形式进行条件限制。