您好,欢迎来到思海网络,我们将竭诚为您提供优质的服务! 诚征网络推广 | 网站备案 | 帮助中心 | 软件下载 | 购买流程 | 付款方式 | 联系我们 [ 会员登录/注册 ]
促销推广
客服中心
业务咨询
有事点击这里…  531199185
有事点击这里…  61352289
点击这里给我发消息  81721488
有事点击这里…  376585780
有事点击这里…  872642803
有事点击这里…  459248018
有事点击这里…  61352288
有事点击这里…  380791050
技术支持
有事点击这里…  714236853
有事点击这里…  719304487
有事点击这里…  1208894568
有事点击这里…  61352289
在线客服
有事点击这里…  531199185
有事点击这里…  61352288
有事点击这里…  983054746
有事点击这里…  893984210
当前位置:首页 >> 技术文章 >> 文章浏览
技术文章

添加mysql索引的几条原则

添加时间:2014-3-15 15:25:05  添加: 思海网络 

添加mysql索引的几条原则:

  一,索引的重要性

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

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

  二,准备工作

  1. //准备二张测试表     
  2. mysql> CREATE TABLE `test_t` (     
  3.  ->   `id` int(11) NOT NULL auto_increment,     
  4.  ->   `num` int(11) NOT NULL default '0',     
  5.  ->   `d_num` varchar(30) NOT NULL default '0',     
  6.  ->   PRIMARY KEY  (`id`)     
  7.  -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;     
  8. Query OK, 0 rows affected (0.05 sec)     
  9.     
  10. mysql> CREATE TABLE `test_test` (     
  11.  ->   `id` int(11) NOT NULL auto_increment,     
  12.  ->   `num` int(11) NOT NULL default '0',     
  13.  ->   PRIMARY KEY  (`id`)     
  14.  -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;     
  15. Query OK, 0 rows affected (0.05 sec)       
  16.     
  17. //创建一个存储过程,为插数据方便     
  18. mysql> delimiter      
  19. mysql> create procedure i_test(pa int(11),tab varchar(30))     
  20.  -> begin     
  21.  ->     declare max_num int(11) default 100000;     
  22.  ->     declare i int default 0;     
  23.  ->     declare rand_num int;     
  24.  ->  declare double_num char;     
  25.  ->     
  26.  ->  if tab != 'test_test' then     
  27.  ->         select count(id) into max_num from test_t;     
  28.  ->         while i < pa do    
  29.  ->             if max_num < 100000 then     
  30.  ->                 select cast(rand()*100 as unsigned) into rand_num;     
  31.  ->                 select concat(rand_num,rand_num) into double_num;     
  32.  ->                 insert into test_t(num,d_num)values(rand_num,double_num);     
  33.  ->             end if;     
  34.  ->             set i = i +1;     
  35.  ->         end while;     
  36.  ->  else    
  37.  ->         select count(id) into max_num from test_test;     
  38.  ->         while i < pa do    
  39.  ->             if max_num < 100000 then     
  40.  ->                 select cast(rand()*100 as unsigned) into rand_num;     
  41.  ->                 insert into test_test(num)values(rand_num);     
  42.  ->             end if;     
  43.  ->             set i = i +1;     
  44.  ->         end while;     
  45.  ->  end if;     
  46.  -> end     
  47. Query OK, 0 rows affected (0.00 sec)     
  48.     
  49. mysql> delimiter ;     
  50. mysql> show variables like "%pro%";   //查看一下,记录执行的profiling是不是开启动了,默认是不开启的     
  51. +---------------------------+-------+     
  52.  Variable_name              Value      
  53. +---------------------------+-------+     
  54.  profiling                  OFF        
  55.  profiling_history_size     15         
  56.  protocol_version           10         
  57.  slave_compressed_protocol  OFF        
  58. +---------------------------+-------+     
  59. 4 rows in set (0.00 sec)       
  60.     
  61. mysql> set profiling=1;           //开启后,是为了对比加了索引后的执行时间     
  62. Query OK, 0 rows affected (0.00 sec)  

  三,相关实例

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

  1. mysql> call i_test(10,'test_t');    //向test_t表插入10条件     
  2. Query OK, 1 row affected (0.02 sec)     
  3.     
  4. mysql> select num from test_t where num!=0;     
  5. mysql> explain select num from test_t where num!=0\G;     
  6. *************************** 1. row ***************************     
  7.  id: 1     
  8.  select_type: SIMPLE     
  9.  table: test_t     
  10.  type: ALL     
  11.  possible_keys: NULL     
  12.  keyNULL     
  13.  key_len: NULL     
  14.  ref: NULL     
  15.  rows: 10     
  16.  Extra: Using where     
  17. 1 row in set (0.00 sec)     
  18.     
  19. ERROR:     
  20. No query specified     
  21.     
  22. mysql> create index num_2 on test_t (num);     
  23. Query OK, 10 rows affected (0.19 sec)     
  24. Records: 10  Duplicates: 0  Warnings: 0     
  25.     
  26. mysql> select num from test_t where num!=0;     
  27.     
  28. mysql> explain select num from test_t where num!=0\G;     
  29. *************************** 1. row ***************************     
  30.  id: 1     
  31.  select_type: SIMPLE     
  32.  table: test_t     
  33.  type: index     
  34.  possible_keys: num_2     
  35.  key: num_2     
  36.  key_len: 4     
  37.  ref: NULL     
  38.  rows: 10     
  39.  Extra: Using where; Using index     
  40. 1 row in set (0.00 sec)     
  41.     
  42. ERROR:     
  43. No query specified     
  44.     
  45. mysql> show profiles;     
  46. +----------+------------+---------------------------------------------+     
  47.  Query_ID  Duration    Query                                            
  48. +----------+------------+---------------------------------------------+     
  49.         1  0.00286325  call i_test(10,'test_t')                        //插入十条数据     
  50.         2  0.00026350  select num from test_t where num!=0              
  51.         3  0.00022250  explain select num from test_t where num!=0      
  52.         4  0.18385400  create index num_2 on test_t (num)              //创建索引     
  53.         5  0.00127525  select num from test_t where num!=0             //使用索引后,差不多是没有使用索引的0.2倍     
  54.         6  0.00024375  explain select num from test_t where num!=0      
  55. +----------+------------+---------------------------------------------+     
  56. rows in set (0.00 sec)   

  解释:

  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,联合查询,子查询等多表操作时关连字段要加索引

  1. mysql> call i_test(10,'test_test');    //向test_test表插入10条数据     
  2. Query OK, 1 row affected (0.02 sec)     
  3.     
  4. mysql> explain select a.num as num1,b.num as num2 from test_t as a left join tes     
  5. t_test as b on a.num=b.num\G;     
  6. *************************** 1. row ***************************     
  7.  id: 1     
  8.  select_type: SIMPLE     
  9.  table: a     
  10.  type: index     
  11.  possible_keys: NULL     
  12.  key: num_2     
  13.  key_len: 4     
  14.  ref: NULL     
  15.  rows: 10     
  16.  Extra: Using index     
  17. *************************** 2. row ***************************     
  18.  id: 1     
  19.  select_type: SIMPLE     
  20.  table: b     
  21.  type: ref     
  22.  possible_keys: num_1     
  23.  key: num_1     
  24.  key_len: 4     
  25.  ref: bak_test.a.num   //bak_test是数据库名,a.num是test_t的一个字段     
  26.  rows: 1080     
  27.  Extra: Using index     
  28. 2 rows in set (0.01 sec)     
  29.     
  30. ERROR:     
  31. No query specified   

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

关键字:mysql、索引、数据库

分享到:

顶部 】 【 关闭
版权所有:佛山思海电脑网络有限公司 ©1998-2024 All Rights Reserved.
联系电话:(0757)22630313、22633833
中华人民共和国增值电信业务经营许可证: 粤B1.B2-20030321 备案号:粤B2-20030321-1
网站公安备案编号:44060602000007 交互式栏目专项备案编号:200303DD003  
察察 工商 网安 举报有奖  警警  手机打开网站