search

Home >Database >Mysql Tutorial >添加mysql索引的3条原则_MySQL

添加mysql索引的3条原则_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOriginal
2016-06-01 14:02:06741browse

　　一，索引的重要性

　　索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。注意如果你需要访问大部分行，顺序读取要快得多，因为此时我们避免磁盘搜索。

　　假如你用新华字典来查找“张”这个汉字，不使用目录的话，你可能要从新华字典的第一页找到最后一页，可能要花二个小时。字典越厚呢，你花的时间就越多。现在你使用目录来查找“张”这个汉字，张的首字母是z，z开头的汉字从900多页开始，有了这条线索，你查找一个汉字可能只要一分钟，由此可见索引的重要性。但是索引建的是不是越多越好呢，当然不是，如果一本书的目录分成好几级的话，我想你也会晕的。

　　二，准备工作

<ol class="dp-c">
<li class="alt">//准备二张测试表     </li>
<li>mysql> CREATE TABLE `test_t` (     </li>
<li class="alt"> ->   `id` int(11) NOT NULL auto_increment,     </li>
<li> ->   `num` int(11) NOT NULL default '0',     </li>
<li class="alt"> ->   `d_num` varchar(30) NOT NULL default '0',     </li>
<li> ->   PRIMARY KEY  (`id`)     </li>
<li class="alt"> -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;     </li>
<li>Query OK, 0 rows affected (0.05 sec)     </li>
<li class="alt">    </li>
<li>mysql> CREATE TABLE `test_test` (     </li>
<li class="alt"> ->   `id` int(11) NOT NULL auto_increment,     </li>
<li> ->   `num` int(11) NOT NULL default '0',     </li>
<li class="alt"> ->   PRIMARY KEY  (`id`)     </li>
<li> -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;     </li>
<li class="alt">Query OK, 0 rows affected (0.05 sec)       </li>
<li>    </li>
<li class="alt">//创建一个存储过程，为插数据方便     </li>
<li>mysql> delimiter |     </li>
<li class="alt">mysql> create procedure i_test(pa int(11),tab varchar(30))     </li>
<li> -> begin     </li>
<li class="alt"> ->     declare max_num int(11) default 100000;     </li>
<li> ->     declare i int default 0;     </li>
<li class="alt"> ->     declare rand_num int;     </li>
<li> ->  declare double_num char;     </li>
<li class="alt"> ->     </li>
<li> ->  if tab != 'test_test' then     </li>
<li class="alt"> ->         select count(id) into max_num from test_t;     </li>
<li> ->         while i </li>
<li class="alt"> ->             if max_num </li>
<li> ->                 select cast(rand()*100 as unsigned) into rand_num;     </li>
<li class="alt"> ->                 select concat(rand_num,rand_num) into double_num;     </li>
<li> ->                 insert into test_t(num,d_num)values(rand_num,double_num);     </li>
<li class="alt"> ->             end if;     </li>
<li> ->             set i = i +1;     </li>
<li class="alt"> ->         end while;     </li>
<li> ->  else    </li>
<li class="alt"> ->         select count(id) into max_num from test_test;     </li>
<li> ->         while i </li>
<li class="alt"> ->             if max_num </li>
<li> ->                 select cast(rand()*100 as unsigned) into rand_num;     </li>
<li class="alt"> ->                 insert into test_test(num)values(rand_num);     </li>
<li> ->             end if;     </li>
<li class="alt"> ->             set i = i +1;     </li>
<li> ->         end while;     </li>
<li class="alt"> ->  end if;     </li>
<li> -> end|     </li>
<li class="alt">Query OK, 0 rows affected (0.00 sec)     </li>
<li>    </li>
<li class="alt">mysql> delimiter ;     </li>
<li>mysql> show variables like "%pro%";   //查看一下，记录执行的profiling是不是开启动了，默认是不开启的     </li>
<li class="alt">+---------------------------+-------+     </li>
<li>| Variable_name             | Value |     </li>
<li class="alt">+---------------------------+-------+     </li>
<li>| profiling                 | OFF   |     </li>
<li class="alt">| profiling_history_size    | 15    |     </li>
<li>| protocol_version          | 10    |     </li>
<li class="alt">| slave_compressed_protocol | OFF   |     </li>
<li>+---------------------------+-------+     </li>
<li class="alt">4 rows in set (0.00 sec)       </li>
<li>    </li>
<li class="alt">mysql> set profiling=1;           //开启后，是为了对比加了索引后的执行时间     </li>
<li>Query OK, 0 rows affected (0.00 sec)  </li>
</ol>

　　三，实例

　　1，单表数据太少，索引反而会影响速度

 

<ol class="dp-sql">
<li class="alt">mysql> call i_test(10,'test_t');    //向test_t表插入10条件     </li>
<li>Query OK, 1 row affected (0.02 sec)     </li>
<li class="alt">    </li>
<li>mysql> select num from test_t where num!=0;     </li>
<li class="alt">mysql> explain select num from test_t where num!=0/G;     </li>
<li>*************************** 1. row ***************************     </li>
<li class="alt"> id: 1     </li>
<li> select_type: SIMPLE     </li>
<li class="alt"> table: test_t     </li>
<li> type: ALL     </li>
<li class="alt"> possible_keys: NULL     </li>
<li> key: NULL     </li>
<li class="alt"> key_len: NULL     </li>
<li> ref: NULL     </li>
<li class="alt"> rows: 10     </li>
<li> Extra: Using where     </li>
<li class="alt">1 row in set (0.00 sec)     </li>
<li>    </li>
<li class="alt">ERROR:     </li>
<li>No query specified     </li>
<li class="alt">    </li>
<li>mysql> create index num_2 on test_t (num);     </li>
<li class="alt">Query OK, 10 rows affected (0.19 sec)     </li>
<li>Records: 10  Duplicates: 0  Warnings: 0     </li>
<li class="alt">    </li>
<li>mysql> select num from test_t where num!=0;     </li>
<li class="alt">    </li>
<li>mysql> explain select num from test_t where num!=0/G;     </li>
<li class="alt">*************************** 1. row ***************************     </li>
<li> id: 1     </li>
<li class="alt"> select_type: SIMPLE     </li>
<li> table: test_t     </li>
<li class="alt"> type: index     </li>
<li> possible_keys: num_2     </li>
<li class="alt"> key: num_2     </li>
<li> key_len: 4     </li>
<li class="alt"> ref: NULL     </li>
<li> rows: 10     </li>
<li class="alt"> Extra: Using where; Using index     </li>
<li>1 row in set (0.00 sec)     </li>
<li class="alt">    </li>
<li>ERROR:     </li>
<li class="alt">No query specified     </li>
<li>    </li>
<li class="alt">mysql> show profiles;     </li>
<li>+----------+------------+---------------------------------------------+     </li>
<li class="alt">| Query_ID | Duration   | Query                                       |     </li>
<li>+----------+------------+---------------------------------------------+     </li>
<li class="alt">|        1 | 0.00286325 | call i_test(10,'test_t')                    |    //插入十条数据     </li>
<li>|        2 | 0.00026350 | select num from test_t where num!=0         |     </li>
<li class="alt">|        3 | 0.00022250 | explain select num from test_t where num!=0 |     </li>
<li>|        4 | 0.18385400 | create index num_2 on test_t (num)          |    //创建索引     </li>
<li class="alt">|        5 | 0.00127525 | select num from test_t where num!=0         |    //使用索引后，差不多是没有使用索引的0.2倍     </li>
<li>|        6 | 0.00024375 | explain select num from test_t where num!=0 |     </li>
<li class="alt">+----------+------------+---------------------------------------------+     </li>
<li>6 rows in set (0.00 sec)   </li>
</ol>

　　解释：

　　id:表示sql执行的顺序

　　select_type:SIMPLE,PRIMARY,UNION,DEPENDENT UNION,UNION RESULT,SUBQUERY,DEPENDENT SUBQUERY,DERIVED不同的查询语句会有不同的select_type

　　table:表示查找的表名

　　type：表示使用索引类型，或者有无使用索引.效率从高到低const、eq_reg、ref、range、index和ALL，其实这个根你sql的写法有直接关系，例如：能用主键就用主键，where后面的条件加上索引，如果是唯一加上唯一索引等

　　possible_keys：可能存在的索引

　　key：使用索引

　　key_len：使用索引的长度

　　ref:使用哪个列或常数与key一起从表中选择行,一般在多表联合查询时会有。

　　rows：查找出的行数

　　Extra:额外说明

　　前段时间写过一篇博文mysql distinct和group by谁更好，里面有朋友留言，说测试结果根我当时做的测试结果不一样，当时我打比方解释了一下，今天有时间，以例子的形势，更直观的表达出索引的工作原理。

　　2，where后的条件，order by ,group by 等这样过滤时，后面的字段最好加上索引。根据实际情况，选择PRIMARY KEY、UNIQUE、INDEX等索引，但是不是越多越好，要适度。

　　3，联合查询，子查询等多表操作时关连字段要加索引

 

<ol class="dp-xml">
<li class="alt">mysql> call i_test(10,'test_test');    //向test_test表插入10条数据     </li>
<li>Query OK, 1 row affected (0.02 sec)     </li>
<li class="alt">    </li>
<li>mysql> explain select a.num as num1,b.num as num2 from test_t as a left join tes     </li>
<li class="alt">t_test as b on a.num=b.num/G;     </li>
<li>*************************** 1. row ***************************     </li>
<li class="alt"> id: 1     </li>
<li> select_type: SIMPLE     </li>
<li class="alt"> table: a     </li>
<li> type: index     </li>
<li class="alt"> possible_keys: NULL     </li>
<li> key: num_2     </li>
<li class="alt"> key_len: 4     </li>
<li> ref: NULL     </li>
<li class="alt"> rows: 10     </li>
<li> Extra: Using index     </li>
<li class="alt">*************************** 2. row ***************************     </li>
<li> id: 1     </li>
<li class="alt"> select_type: SIMPLE     </li>
<li> table: b     </li>
<li class="alt"> type: ref     </li>
<li> possible_keys: num_1     </li>
<li class="alt"> key: num_1     </li>
<li> key_len: 4     </li>
<li class="alt"> ref: bak_test.a.num   //bak_test是数据库名,a.num是test_t的一个字段     </li>
<li> rows: 1080     </li>
<li class="alt"> Extra: Using index     </li>
<li>2 rows in set (0.01 sec)     </li>
<li class="alt">    </li>
<li>ERROR:     </li>
<li class="alt">No query specified   </li>
</ol>

　　数据量特别大的时候，最好不要用联合查询，即使你做了索引。

　　上面只是个人的一点小结，抛砖引玉一下。

Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：MySQL的10件事—它们也许和你预想的不一样_MySQLNext article：mysql的乱码问题_MySQL

Related articles

See more