mysql索引必须了解的几个重要问题

本文讲述了mysql索引必须了解的几个重要问题。分享给大家供大家参考，具体如下：

1、索引是做什么的?

索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。
表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。
大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。

2、索引好复杂，我该怎么理解索引，有没一个更形象点的例子？

有，想象一下，你面前有本词典，数据就是书的正文内容，你就是那个cpu，而索引，则是书的目录

3、索引越多越好？

大多数情况下索引能大幅度提高查询效率，但：

数据的变更（增删改）都需要维护索引，因此更多的索引意味着更多的维护成本

更多的索引意味着也需要更多的空间 （一本100页的书，却有50页目录？）

过小的表，建索引可能会更慢哦 ：）  （读个2页的宣传手册，你还先去找目录？）

4、索引的字段类型问题

text类型，也可建索引（需指定长度）

myisam存储引擎索引键长度综合不能超过1000字节

用来筛选的值尽量保持和索引列同样的数据类型

5、like 不能用索引？

尽量减少like，但不是绝对不可用，"xxxx%" 是可以用到索引的，

想象一下，你在看一本成语词典，目录是按成语拼音顺序建立，查询需求是，你想找以 "一"字开头的成语（"一%"），和你想找包含一字的成语（"%一%"）

除了like，以下操作符也可用到索引：

<，<=，=，>，>=，BETWEEN，IN
<>，not in ，！=则不行

6、什么样的字段不适合建索引？

一般来说，列的值唯一性太小（如性别，类型什么的），不适合建索引（怎样叫太小？一半说来，同值的数据超过表的百分之15，那就没必要建索引了）

太长的列，可以选择只建立部分索引，（如：只取前十位做索引）

更新非常频繁的数据不适宜建索引（怎样叫非常？意会）

7、一次查询能用多个索引吗?

不能

8、多列查询该如何建索引?

一次查询只能用到一个索引，所以 首先枪毙 a，b各建索引方案
a还是b？ 谁的区分度更高（同值的最少），建谁！
当然，联合索引也是个不错的方案，ab，还是ba，则同上，区分度高者，在前

9、联合索引的问题?

where a = "xxx" 可以使用 AB 联合索引
where b = "xxx" 则不可 （再想象一下，这是书的目录？）
所以，大多数情况下，有AB索引了，就可以不用在去建一个A索引了

10、哪些常见情况不能用索引?

like "%xxx"

not in ， !=

对列进行函数运算的情况（如 where md5(password) = "xxxx"）

WHERE index=1 OR A=10

存了数值的字符串类型字段（如手机号），查询时记得不要丢掉值的引号，否则无法用到该字段相关索引，反之则没关系

也即

select * from test where mobile = 13711112222;

可是无法用到mobile字段的索引的哦（如果mobile是char 或 varchar类型的话）

btw，千万不要尝试用int来存手机号（为什么？自己想！要不自己试试）

11、覆盖索引(Covering Indexes)拥有更高效率

索引包含了所需的全部值的话，就只select 他们，换言之，只select 需要用到的字段，如无必要，可尽量避免select *

12、NULL 的问题

NULL会导致索引形同虚设，所以在设计表结构时应避免NULL 的存在（用其他方式表达你想表达的NULL，比如 -1？）

13、如何查看索引信息，如何分析是否正确用到索引?

show index from tablename;
explain select ……;

关于explain，改天可以找个时间专门写一篇入门帖，在此之前，可以尝试 google

14、了解自己的系统，不要过早优化!

过早优化，一直是个非常讨厌而又时刻存在的问题，大多数时候就是因为不了解自己的系统，不知道自己系统真正的承载能力

比如：几千条数据的新闻表，每天几百几千次的正文搜索，大多数时候我们可以放心的去like，而不要又去建一套全文搜索什么的，毕竟cpu还是比人脑厉害太多

15、分享个小案例：

曾经有个朋友找板子，说：大师帮看看，公司网站打不开
板子笑了笑：大师可不敢当啊，待我看看再说
板子花了10分钟分析了下：中小型企业站，量不大（两三万pv每天），独立服务器，数据量不大（100M不到），应该不至于太慢
某个外包团队做的项目，年久失修，彻底改造？不现实！
于是，板子花了20分钟给可以加索引的字段都加上了索引，于是，世界安静了
朋友说：另外一个哥们说，优化至少得2w外包费，你只用30分钟，看来，大师你是当之无愧了，选个最好的餐馆吧
板子：那就来点西餐吧，常熟路地铁站肯德基等你！

16、最后：永远别忘记的关键词 sql注入

希望本文所述对大家MySQL数据库程序设计有所帮助。

赞 (0)

相关推荐

  • Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE

    Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE

    场景 产品中有一张图片表pics,数据量将近100万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化 表结构很简单,主要字段: 复制代码 代码如下: user_id 用户ID picname 图片名称 smallimg 小图名称 一个用户会有多条图片记录,现在有一个根据user_id建立的索引:uid,查询语句也很简单:取得某用户的图片集合: 复制代码 代码如下: select picname, smallimg from pics where user_id = xxx; 优化

  • Mysql性能优化案例 - 覆盖索引分享

    Mysql性能优化案例 - 覆盖索引分享

    场景 产品中有一张图片表,数据量将近100万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化 表结构很简单,主要字段: 复制代码 代码如下: user_id 用户ID picname 图片名称 smallimg 小图名称 一个用户会有多条图片记录 现在有一个根据user_id建立的索引:uid 查询语句也很简单:取得某用户的图片集合 复制代码 代码如下: select picname, smallimg from pics where user_id = xxx; 优化前 执行查

  • Mysql使用索引实现查询优化

    索引的目的在于提高查询效率,可以类比字典,如果要查"mysql"这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql.如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的. 1.索引的优点 假设你拥有三个未索引的表t1.t2和t3,每个表都分别包含数据列i1.i2和i3,并且每个表都包含了1000条数据行,其序号从1到1000.查找某些值匹配的数据行组合的查询可能如下所示: SELECT t1.i1, t2.i2, t3.i3 FROM t1, t2,

  • mysql 索引详细介绍

    mysql 索引详解: 在mysql 中,索引可以分为两种类型 hash索引和 btree索引. 什么情况下可以用到B树索引?  1.全值匹配索引 比如: orderID="123" 2.匹配最左前缀索引查询  比如:在userid 和 date字段上创建联合索引. 那么如果输入 userId作为条件,那么这个userid可以使用到索引,如果直接输入 date作为条件,那么将不能使用到索引. 3.匹配列前缀查询 比如: order_sn like '134%' 这样可以使用到索引. 4

  • 详解mysql建立索引的使用办法及优缺点分析

    前言 索引(index)是帮助MySQL高效获取数据的数据结构. 它对于高性能非常关键,但人们通常会忘记或误解它. 索引在数据越大的时候越重要.规模小.负载轻的数据库即使没有索引,也能有好的性能, 但是当数据增加的时候,性能就会下降很快. 为什么要创建索引呢? 这是因为,创建索引可以大大提高系统的性能. 第一.通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性. 第二.可以大大加快数据的检索速度,这也是创建索引的最主要的原因. 第三.可以加速表和表之间的连接,特别是在实现数据的参考完整性方

  • Mysql数据库之索引优化

    Mysql数据库之索引优化

    MySQL凭借着出色的性能.低廉的成本.丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库.虽然性能出色,但所谓"好马配好鞍",如何能够更好的使用它,已经成为开发工程师的必修课,我们经常会从职位描述上看到诸如"精通MySQL"."SQL语句优化"."了解数据库原理"等要求.我们知道一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,

  • Mysql判断表字段或索引是否存在

    判断字段是否存在: DROP PROCEDURE IF EXISTS schema_change; DELIMITER // CREATE PROCEDURE schema_change() BEGIN DECLARE CurrentDatabase VARCHAR(); SELECT DATABASE() INTO CurrentDatabase; IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schem

  • MySQL索引用法实例分析

    本文实例分析了MySQL索引用法.分享给大家供大家参考,具体如下: MYSQL描述: 一个文章库,里面有两个表:category和article.category里面有10条分类数据.article里面有20万条.article里面有一个"article_category"字段是与category里的"category_id"字段相对应的.article表里面已经把 article_category字义为了索引.数据库大小为1.3G. 问题描述: 执行一个很普通的查

  • 详解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索引

    数据库索引是一种数据结构,目的是提高表的操作速度.可以使用一个或多个列,提供快速随机查找和访问记录的高效排序来创建索引. 要创建的索引,应当认为哪列将用于使SQL查询,创建对这些列的一个或多个索引. 实际上,索引也是表,其中保存主键或索引字段的指针并指向每个记录到实际的表的类型. 用户无法看到索引,它们只是用来加速查询,并将被用于数据库搜索引擎在查找记录时提高速度. INSERT和UPDATE语句需要更多的时间来创建索引,作为在SELECT语句快速在这些表上操作.其原因是,在执行插入或更新数据时

  • mysql 索引分类以及用途分析

    一. MySQL: 索引以B树格式保存 Memory存储引擎可以选择Hash或BTree索引,Hash索引只能用于=或<=>的等式比较. 1.普通索引:create index on Tablename(列的列表) alter table TableName add index (列的列表) create table TableName([...], index [IndexName] (列的列表) 2.唯一性索引:create unique index alter ... add uniqu

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

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

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

  • MySQL 索引分析和优化

    MySQL 索引分析和优化

    一.什么是索引? 索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存.如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录.表里面的记录数量越多,这个操作的代价就越高.如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置.如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍. 假设我们创建了一个名为people的表: CREATE TABLE people ( p

  • MySql索引详细介绍及正确使用方法

    MySql索引详细介绍及正确使用方法 1.前言: 索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点. 索引是存储引擎用于快速查找记录的一种数据结构,通过合理的使用数据库索引可以大大提高系统的访问性能,接下来主要介绍在MySql数据库中索引类型,以及如何创建出更加合理且高效的索引技巧. 注:这里主要针对的是InnoDB存储引擎的B+Tree索引数据结构 2.索引的优点 1.大大减轻了服务器需要扫描的数据量,从而提高了数据的检索速度 2.帮助服务器避免排序和临时表 3.可以将

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

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

  • mysql索引必须了解的几个重要问题

    本文讲述了mysql索引必须了解的几个重要问题.分享给大家供大家参考,具体如下: 1.索引是做什么的? 索引用于快速找出在某个列中有一特定值的行.不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行. 表越大,花费的时间越多.如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据. 大多数MySQL索引(PRIMARY KEY.UNIQUE.INDEX和FULLTEXT)在B树中存储.只是空间列类型的索引使用R-树,并且MEMORY

随机推荐