数据库

简单了解添加mysql索引的3条原则
数据库 2024/12/27 佚名
3 2

一，索引的重要性

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

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

二，准备工作

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

三，实例

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

mysql> call i_test(10,'test_t'); //向test_t表插入10条件 
Query OK, 1 row affected (0.02 sec) 
mysql> select num from test_t where num!=0; 
mysql> explain select num from test_t where num!=0\G; 
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: test_t 
type: ALL 
possible_keys: NULL 
key: NULL 
key_len: NULL 
ref: NULL 
rows: 10 
Extra: Using where 
1 row in set (0.00 sec) 
ERROR: 
No query specified 
mysql> create index num_2 on test_t (num); 
Query OK, 10 rows affected (0.19 sec) 
Records: 10 Duplicates: 0 Warnings: 0 
mysql> select num from test_t where num!=0; 
mysql> explain select num from test_t where num!=0\G; 
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: test_t 
type: index 
possible_keys: num_2 
key: num_2 
key_len: 4 
ref: NULL 
rows: 10 
Extra: Using where; Using index 
1 row in set (0.00 sec) 
ERROR: 
No query specified 
mysql> show profiles; 
+----------+------------+---------------------------------------------+ 
| Query_ID | Duration | Query | 
+----------+------------+---------------------------------------------+ 
| 1 | 0.00286325 | call i_test(10,'test_t') | //插入十条数据 
| 2 | 0.00026350 | select num from test_t where num!=0 | 
| 3 | 0.00022250 | explain select num from test_t where num!=0 | 
| 4 | 0.18385400 | create index num_2 on test_t (num) | //创建索引 
| 5 | 0.00127525 | select num from test_t where num!=0 | //使用索引后，差不多是没有使用索引的0.2倍 
| 6 | 0.00024375 | explain select num from test_t where num!=0 | 
+----------+------------+---------------------------------------------+ 
6 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，联合查询，子查询等多表操作时关连字段要加索引

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

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

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

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。

标签：
添加mysql索引,原则
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
无争山庄资源网 Copyright www.whwtcm.com

评论“简单了解添加mysql索引的3条原则”

暂无“简单了解添加mysql索引的3条原则”评论...
www.whwtcm.com 无争山庄资源网
129,905影音资源
244,626技术资源
111,817软件资源
578,645站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
无争山庄资源网 Copyright www.whwtcm.com