mysql 第6章 索引 mysql 第6章 索引

2018-03-02

一、索引原理

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  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

select_typetabletypepossible_keyskeykey_lenrowsExtra
表示查询的类型输出结果集的表表示表的连接类型表示查询时,可能使用的索引表示实际使用的索引索引字段的长度扫描出的行数(估算的行数)执行情况的描述和说明

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 的形式进行条件限制。

打赏

取消

感谢您的支持,我会继续努力的!

扫码支持
扫码打赏,你说多少就多少

打开微信扫一扫,即可进行扫码打赏哦

阅读 2638