简单了解添加mysql索引的3条原则

一,索引的重要性

索引用于快速找出在某个列中有一特定值的行。不使用索引,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 

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

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

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

(0)

相关推荐

  • Mysql如何适当的添加索引介绍

    这里先简单介绍一下索引: 添加索引是为了提高数据库查询性能,索引是最物美价廉的东西了,不用加内存,不用改程序,不用调sql,只要执行个正确的create index ,查询的速度就可能提高百倍千倍,这可是有诱惑力的,可是天下没有没费的午餐,查询的速度的提高是以牺牲insert update delete的速度为代价的.而且索引大小一般是数据的三分之一  ,再加上索引要加载进内存的,如果全部字段都加索引会以牺牲内存为代价的,所以才要设当的添加索引. 这里简单介绍一下mysql中常用索引: 在添加索

  • mysql 添加索引 mysql 如何创建索引

    1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEX

  • MySQL常用的建表、添加字段、修改字段、添加索引SQL语句写法总结

    本文实例讲述了MySQL常用的建表.添加字段.修改字段.添加索引SQL语句写法.分享给大家供大家参考,具体如下: 建表: DROP TABLE IF EXISTS bulletin; CREATE TABLE bulletin( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, # 主键 uid INT(11) NOT NULL DEFAULT 0, # 创建者id context VARCHAR(600) NOT NULL DEFAULT '', # 公告

  • mysql为字段添加和删除唯一性索引(unique) 的方法

    1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEX

  • MySQL修改表一次添加多个列(字段)和索引的方法

    MySQL修改表一次添加多个列(字段) ALTER TABLE table_name ADD func varchar(50), ADD gene varchar(50), ADD genedetail varchar(50); MySQL修改表一次添加多个索引 ALTER TABLE  table_name ADD INDEX idx1 ( `func`), ADD INDEX idx2 ( `func`,`gene`), ADD INDEX idx3( `genedetail`); 以上这篇

  • mysql占用CPU过高的解决办法(添加索引)

    下面是MYSQL占用CPU高处理的一个例子,希望对遇到类似问题的朋友们有点启发.一般来说MYQL占用CPU高,多半是数据库查询代码问题,查询数据库过多.所以一方面要精简代码,另一方面最好对频繁使用的代码设置索引. 今天早上起来 机器报警 一查负载一直都在4以上 top了一下 发现 mysql 稳居 第一 而且相当稳定 我擦 重启一下mysql不行 mysql> show processlist;一下 发现xxx网站有两条 查询语句 一直 在列,我擦 该站 也就30多万条记录 量也不大 不可能是机

  • 简单了解添加mysql索引的3条原则

    一,索引的重要性 索引用于快速找出在某个列中有一特定值的行.不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行.表越大,花费的时间越多.如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据.注意如果你需要访问大部分行,顺序读取要快得多,因为此时我们避免磁盘搜索. 假如你用新华字典来查找"张"这个汉字,不使用目录的话,你可能要从新华字典的第一页找到最后一页,可能要花二个小时.字典越厚呢,你花的时间就越多.现在你使用目录来

  • 图文详解Mysql索引的最左前缀原则

    目录 前言 1. 定义 2. 全索引顺序 3. 部分索引顺序 3.1 正序 3.2 乱序 4. 模糊索引 5. 范围索引 总结 前言 之所以有这个最左前缀索引 归根结底是mysql的数据库结构 B+树 在实际问题中 比如 索引index (a,b,c)有三个字段, 使用查询语句select * from table where c = '1' ,sql语句不会走index索引的 select * from table where b =‘1’ and c ='2' 这个语句也不会走index索引

  • MySQL索引下推(ICP)的简单理解与示例

    前言 索引下推(Index Condition Pushdown, 简称ICP)是MySQL 5.6 版本的新特性,它能减少回表查询次数,提升检索效率. MySQL体系结构 要明白索引下推,首先要了解MySQL的体系结构: 上图来自MySQL官方文档. 通常把MySQL从上至下分为以下几层: MySQL服务层:包括NoSQL和SQL接口.查询解析器.优化器.缓存和Buffer等组件. 存储引擎层:各种插件式的表格存储引擎,实现事务.索引等各种存储引擎相关的特性. 文件系统层: 读写物理文件. M

  • 简单谈谈Mysql索引与redis跳表

    摘要 面试时,交流有关mysql索引问题时,发现有些人能够涛涛不绝的说出B+树和B树,平衡二叉树的区别,却说不出B+树和hash索引的区别.这种一看就知道是死记硬背,没有理解索引的本质.本文旨在剖析这背后的原理,欢迎留言探讨 问题 如果对以下问题感到困惑或一知半解,请继续看下去,相信本文一定会对你有帮助 mysql 索引如何实现 mysql 索引结构B+树与hash有何区别.分别适用于什么场景 数据库的索引还能有其他实现吗 redis跳表是如何实现的 跳表和B+树,LSM树有和区别呢 解析 首先

  • 详解mysql索引总结----mysql索引类型以及创建

    关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车.对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢.还是以WordPress来说,其多个数据表都会对经常被查询的字段添加索引,比如wp_comments表中针对5个字段设计了BTREE索引. 一个简单的对比测试 以我去年测试的数据作为一个简单示例,20多条数据源随机生成200万条

  • MySQL索引类型总结和使用技巧以及注意事项

    在数据库表中,对字段建立索引可以大大提高查询速度.假如我们创建了一个 mytable表: 复制代码 代码如下: CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  ); 我们随机向里面插入了10000条记录,其中有一条:5555, admin. 在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时,如果在

  • 快速学习MySQL索引的入门超级教程

    所谓索引就是为特定的mysql字段进行一些特定的算法排序,比如二叉树的算法和哈希算法,哈希算法是通过建立特征值,然后根据特征值来快速查找.而用的最多,并且是mysql默认的就是二叉树算法 BTREE,通过BTREE算法建立索引的字段,比如扫描20行就能得到未使用BTREE前扫描了2^20行的结果,具体的实现方式后续本博客会出一个算法专题里面会有具体的分析讨论; Explain优化查询检测 EXPLAIN可以帮助开发人员分析SQL问题,explain显示了mysql如何使用索引来处理select语

  • MySQL索引的缺点以及MySQL索引在实际操作中有哪些事项

    以下的文章主要介绍的是MySQL索引的缺点以及MySQL索引在实际操作中有哪些事项是值得我们大家注意的,我们大家可能不知道过多的对索引进行使用将会造成滥用.因此MySQL索引也会有它的缺点: 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT.UPDATE和DELETE.因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件. 建立索引会占用磁盘空间的索引文件.一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快. 索引只是提高效

  • MySQL索引背后的数据结构及算法原理详解

    摘要 本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题.特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等.为了避免混乱,本文将只关注于BTree索引,因为这是平常使用MySQL时主要打交道的索引,至于哈希索引和全文索引本文暂不讨论. 文章主要内容分为三个部分. 第一部分主要从数据结构及算法理论层面讨论MySQL数据库索引的数理基础. 第二部分结合MySQL数据库中My

  • MySQL索引优化的实际案例分析

    Order by desc/asc limit M是我在mysql sql优化中经常遇到的一种场景,其优化原理也非常的简单,就是利用索引的有序性,优化器沿着索引的顺序扫描,在扫描到符合条件的M行数据后,停止扫描:看起来非常的简单,但是我经常看到很多性能较差的sql没有利用这个优化规律,下面将结合一些实际的案例来分析说明: 案例一: 一条sql执行非常的慢,执行时间为: root@test 02:00:44 SELECT * FROM test_order_desc WHERE END_TIME>

随机推荐