本文从如何建立mysql索引以及介绍mysql的索引类型,再讲mysql索引的利与弊,以及建立索引时需要注意的地方

【mysql】索引的优化,mysql索引

写在前面的话

查询容易,优化不易,且写且珍惜

七、mysql索引选择,mysql索引

mysql 索引技巧,mysql索引

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的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’;时,如果在username上已经建立了索引,MySQL无须任何扫描,即准确可找到该记录。相反,MySQL会扫描所有记录,即要查询10000条记录。

索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。

MySQL索引类型包括:

(1)普通索引

这是最基本的索引,它没有任何限制。它有以下几种创建方式:

◆创建索引

CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。

◆修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length)) ◆创建表的时候直接指定

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX [indexName] (username(length))   );  删除索引的语法:

DROP INDEX [indexName] ON mytable;

(2)唯一索引

它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:

◆创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) ◆修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ◆创建表的时候直接指定

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   UNIQUE [indexName] (username(length))   ); 

(3)主键索引

它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   PRIMARY KEY(ID)   );  当然也可以用 ALTER 命令。记住:一个表只能有一个主键。

(4)组合索引

为了形象地对比单列索引和组合索引,为表添加多个字段:

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   city VARCHAR(50) NOT NULL,   age INT NOT NULL  );  为了进一步榨取MySQL的效率,就要考虑建立组合索引。就是将 name, city, age建到一个索引里:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 建表时,usernname长度为 16,这里用 10。这是因为一般情况下名字的长度不会超过10,这样会加速索引查询速度,还会减少索引文件的大小,提高INSERT的更新速度。

如果分别在 usernname,city,age上建立单列索引,让该表有3个单列索引,查询时和上述的组合索引效率也会大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引,其实是相当于分别建立了下面三组组合索引:

usernname,city,age   usernname,city   usernname  为什么没有 city,age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个SQL就会用到这个组合索引:

SELECT * FROM mytable WHREE username=”admin” AND city=”郑州”  SELECT * FROM mytable WHREE username=”admin” 而下面几个则不会用到:

SELECT * FROM mytable WHREE age=20 AND city=”郑州”  SELECT * FROM mytable WHREE city=”郑州”

(5)建立索引的时机

到这里我们已经学会了建立索引,那么我们需要在什么情况下建立索引呢?一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。例如:

SELECT t.Name  FROM mytable t LEFT JOIN mytable m    ON t.Name=m.username WHERE m.age=20 AND m.city=’郑州’ 此时就需要对city和age建立索引,由于mytable表的userame也出现在了JOIN子句中,也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时,MySQL不会使用索引。例如下句会使用索引:

SELECT * FROM mytable WHERE username like’admin%’ 而下句就不会使用:

SELECT * FROM mytable WHEREt Name like’%admin’ 因此,在使用LIKE时应注意以上的区别。

(6)索引的不足之处

上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:

◆虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。

索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

(7)使用索引的注意事项

使用索引时,有以下一些技巧和注意事项:

◆索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

◆使用短索引

对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

◆索引列排序

MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

◆like语句操作

一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

◆不要在列上进行运算

select * from users where YEAR(adddate)<2007; 将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成

select * from users where adddate<‘2007-01-01’; 

◆不使用NOT IN和<>操作

以上,就对其中MySQL索引类型进行了介绍。

索引技巧,mysql索引
索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。
在数…

首先:先假设有一张表,表的数据有10W条数据,其中有一条数据是nickname=’css’,如果要拿这条数据的话需要些的sql是
SELECT * FROM award WHERE nickname = ‘css’

mysql结构

从MySQL逻辑架构来看,MySQL有三层架构,第一层连接,第二层查询解析、分析、优化、视图、缓存,第三层,存储引擎

澳门皇冠金沙网站 1

 

七、mysql索引选择

1.myisam,bdb,innodb,memory 单表至少支持16个索引

2.create index id_index on emp (id) 为emp表创建一个名为id_index的id字段的索引

3.drop index id_index on emp 删除emp表的id_index 索引

4.explain xxxxxxx,可以查看相关的执行结果,用于优化表用

5.最合适的索引列是“条件列”,而不是“选择列”
  比如 select name from emp where year >= 2014;
  那么最好是在year字段上创建索引,而不是name字段

6.使用索引的字段,字段值最好是具有唯一性的,因为越具有唯一性,索引意义越大

7.btree索引通过索引访问,而hash索引则进行全表扫描

 

一般情况下,在没有建立索引的时候,mysql需要扫描全表及扫描10W条数据找这条数据,如果我在nickname上建立索引,那么mysql只需要扫描一行数据及为我们找到这条nickname=’css’的数据,是不是感觉性能提升了好多咧….

MySQL有哪些索引类型 ?

从数据结构角度

1、B+树索引(O(log(n))):关于B+树索引,可以参考 MySQL索引背后的数据结构及算法原理

2、hash索引:
a 仅仅能满足”=”,”IN”和”<=>”查询,不能使用范围查询
b 其检索效率非常高,索引的检索可以一次定位,不像B-Tree
索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以
Hash 索引的查询效率要远高于 B-Tree 索引
c 只有Memory存储引擎显示支持hash索引

3、FULLTEXT索引(现在MyISAM和InnoDB引擎都支持了)

4、R-Tree索引(用于对GIS数据类型创建SPATIAL索引)

从物理存储角度

1、聚集索引(clustered index)

2、非聚集索引(non-clustered index)

从逻辑角度

1、主键索引:主键索引是一种特殊的唯一索引,不允许有空值

2、普通索引或者单列索引

3、多列索引(复合索引):复合索引指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用复合索引时遵循最左前缀集合

4、唯一索引或者非唯一索引

5、空间索引:空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。
MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须将其声明为NOT
NULL,空间索引只能在存储引擎为MYISAM的表中创建

CREATE TABLE table_name[col_name data type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

1、unique|fulltext|spatial为可选参数,分别表示唯一索引、全文索引和空间索引;
2、index和key为同义词,两者作用相同,用来指定创建索引
3、col_name为需要创建索引的字段列,该列必须从数据表中该定义的多个列中选择;
4、index_name指定索引的名称,为可选参数,如果不指定,MYSQL默认col_name为索引值;
5、length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度;
6、asc或desc指定升序或降序的索引值存储

MySQL数据库优化(七):MySQL怎使用索引

  索引用于快速找到特定一些值的记录。如果没有索引,MySQL就必须从第一行记录开始读取整个表来检索记录。表越大,资源消耗越大。如果在字段上有索引的话,MySQL就能很快决定该从数据文件的哪个位置开始搜索记录,而无须查找所有的数据。如果表中有1000条记录的话,那么这至少比顺序地读取数据快100倍。注意,如果需要存取几乎全部1000条记录的话,那么顺序读取就更快了,因为这样会使磁盘搜索最少。
  大部分MySQL索引(PRIMARY KEY, UNIQUE,INDEX 和
FULLTEXT)都是以B树方式存储。只有空间类型的字段使用R树存储,MEMORY
(HEAP)表支持哈希索引。
  字符串默认都是自动压缩前缀和后缀中的空格。
  通常,如下所述几种情况下可以使用索引。哈希索引(用于 MEMORY
表)的独特之处在后面会讨论到。
  想要尽快找到匹配 WHERE 子句的记录。
  根据条件排除记录。如果有多个索引可共选择的话,MySQL通常选择能找到最少记录的那个索引。
  做表连接查询时从其他表中检索记录。
  想要在指定的索引字段 key_col 上找到它的 MIN() 或 MAX()
值。优化程序会在检查索引的
  key_col 字段前就先检查其他索引部分是否使用了 WHERE key_part_# =
constant 子句。这样的话,
  MySQL会为 MIN() 或 MAX()
表达式分别单独做一次索引查找,并且将它替换成常数。当所有的表达式都被替换成常数后,查询就立刻返回。如下:
  SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE
key_part1=10;
  对表作排序或分组,当在一个可用的最左前缀索引上做分组或排序时(如
ORDER
  BY key_part1, key_part2)。如果所有的索引部分都按照 DESC
排序,索引就按倒序排序。
  有些时候,查询可以优化使得无需计算数据就能直接取得结果。当查询使用表中的一个数字型字段,且这个字段是索引的最左部分,则可能从索引树中能很快就取得结果:
  SELECTkey_part3FROMtbl_nameWHEREkey_part1=1
  假设有如下 SELECT 语句:
  如果在 col1 和 col2
上有一个多字段索引的话,就能直接取得对应的记录了。

mysql的索引分为单列索引(主键索引,唯索引,普通索引)和组合索引.

建立索引的原则

1、基数很低的字段不创建索引,更新非常频繁的字段不适合创建索引

2、MySQL不支持bitmap索引

3、采用第三方系统实现 Text/Blob
的全文索引(Sphinx、Coreseek、Lucene、ElashSearch)

4、常用的 where、ORDER BY 、GROUP BY 、DISTINCT 字段要建立索引

5、索引不能太多,会有负作用

6、多使用联合索引、少使用独立索引

7、字符型可创建前缀索引(如 username 字段 80%
的数据都小于18个字符,那么可以创建18个字符的前缀索引

8、字段的顺序对组合索引效率有至关重要的作用,过滤效果越好的字段需要更靠前

最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a
= 1 and b = 2 and c > 3 and d = 4
如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可=和in可以乱序,比如a
= 1 and b = 2 and c = 3
建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式

9、MySQL只对以下操作符才使用索引

  • <,<=,=,>,>=,between,
  • 某些时候的like(不以通配符%或_开头的情形)
  • 若已对名为col_name的列建了索引,则形如”col_name is
    null”的SQL会用到索引
  • 若sql语句中的where条件不只1个条件,则MySQL会进行Index
    Merge优化来缩小候选集范围

10、不要过度索引,只保持所需的索引。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能。
在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。

MySQL数据库优化(七):MySQL怎使用索引

  索引用于快速找到特定一些值的记录。如果没有索引,MySQL就必须从第一行记录开始读取整个表来检索记录。表越大,资源消耗越大。如果在字段上有索引的话,MySQL就能很快决定该从数据文件的哪个位置开始搜索记录,而无须查找所有的数据。如果表中有1000条记录的话,那么这至少比顺序地读取数据快100倍。注意,如果需要存取几乎全部1000条记录的话,那么顺序读取就更快了,因为这样会使磁盘搜索最少。
  大部分MySQL索引(PRIMARY KEY, UNIQUE,INDEX 和
FULLTEXT)都是以B树方式存储。只有空间类型的字段使用R树存储,MEMORY
(HEAP)表支持哈希索引。
  字符串默认都是自动压缩前缀和后缀中的空格。
  通常,如下所述几种情况下可以使用索引。哈希索引(用于 MEMORY
表)的独特之处在后面会讨论到。
  想要尽快找到匹配 WHERE 子句的记录。
  根据条件排除记录。如果有多个索引可共选择的话,MySQL通常选择能找到最少记录的那个索引。
  做表连接查询时从其他表中检索记录。
  想要在指定的索引字段 key_col 上找到它的 MIN() 或 MAX()
值。优化程序会在检查索引的
  key_col 字段前就先检查其他索引部分是否使用了 WHERE key_part_# =
constant 子句。这样的话,
  MySQL会为 MIN() 或 MAX()
表达式分别单独做一次索引查找,并且将它替换成常数。当所有的表达式都被替换成常数后,查询就立刻返回。如下:
  SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE
key_part1=10;
  对表作排序或分组,当在一个可用的最左前缀索引上做分组或排序时(如
ORDER
  BY key_part1, key_part2)。如果所有的索引部分都按照 DESC
排序,索引就按倒序排序。
  有些时候,查询可以优化使得无需计算数据就能直接取得结果。当查询使用表中的一个数字型字段,且这个字段是索引的最左部分,则可能从索引树中能很快就取得结果:
  SELECTkey_part3FROMtbl_nameWHEREkey_part1=1
  假设有如下 SELECT 语句:
  如果在 col1 和 col2
上有一个多字段索引的话,就能直接取得对应的记录了。如果在 col1 和 col2
分别有独立的索引,那么优化程序会先找到限制最多的那个索引,然后根据哪个索引能找到更少的记录就决定使用哪个索引。

七、mysql索引选择 1
.myisam,bdb,innodb,memory 单表至少支持16个索引 2 . create index
id_index on emp (id) 为emp表创建一个名…

单列索引:一个索引只包含一个列,一个表可以有多个单列索引.

无法使用索引的场景

1、通过索引扫描的记录数超过30%会进行全表扫描

2、第一个索引列使用范围查询不能使用索引

3、内存表使用Hash进行全表扫描

4、ORDER BY 、GROUP BY Hash索引只能进行等于/不等于的检索

5、SELECT … WHERE key1 = ? ORDER BY key2 ASC
对于key1和key2上的索引,查询优化器会自己判断用哪个(只能用到一个)

6、表关联字段类型要一样(包括长度),否则会有类型转换

7、使用函数时不能用到索引( WHERE func(key1) = ? 不能用到)( WHERE key1 +
1 = ? 不能用到)(WHERE key1 = ? + ? 可以用到)

组合索引:一个组合索引包含两个或两个以上的列,

索引有哪些“副作用”

1、增,删,改都需要修订索引,索引存在额外的维护成本

2、查找翻阅索引系统需要消耗时间,索引存在额外的访问成本

3、索引系统需要一个地方来存放,索引存在额外的空间成本

 

本文使用的案例的表

索引工具

mysqlidxchx/pt-index-usage/userstat/check-unused-keys

1、mysqlidxchx工具很长时间没有更新,但主要用来分析general
log、slow.log,来判断实例中那个索引是可以删除,但这个工具没有经过实战,风险很大。

2、pt-index-usage原理来类似mysqlidxchx,执行过程中性能消耗比较严重,如果要在生产库上部署,最好在凌晨业务低锋时使用,pt-index-usage只支持slow.log格式的文件,如果要全面分析整个实例索引使用情况,需要long_query_time设置成0,才能把所以的sql记录下来,但同时会对磁盘空间造成压力,同时pt-index-usage对大文件分析就是件痛苦的事。当然pt-index-usage可以考虑部分表索引使用情况的确认。

3、最看好的userstat,收集信息性能优越,成本低。这个patch是google贡献的(userstat_running),percona把它改名成userstat,默认是不开启的,开启是会收集客户端、索引、表、线程信息存储在CLIENT_STATISTICS、INDEX_STATISTICS、TABLE_STATISTICS、THREAD_STATISTICS。Userstat的bug导致的问题太严重,直接导致mysql
crash,到目前淘宝生产环境还没有使用。

4、Ryan
Lowe的check-unused-keys脚本基于userstat,能够比较方便输出需要删除的索引。

参考地址

写在前面的话
查询容易,优化不易,且写且珍惜 mysql结构
从MySQL逻辑架构来看,MySQL有三层架构,第一层连…

CREATE TABLE `award` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
   `aty_id` varchar(100) NOT NULL DEFAULT '' COMMENT '活动场景id',
   `nickname` varchar(12) NOT NULL DEFAULT '' COMMENT '用户昵称',
   `is_awarded` tinyint(1) NOT NULL DEFAULT 0 COMMENT '用户是否领奖',
   `award_time` int(11) NOT NULL DEFAULT 0 COMMENT '领奖时间',
   `account` varchar(12) NOT NULL DEFAULT '' COMMENT '帐号',
   `password` char(32) NOT NULL DEFAULT '' COMMENT '密码',
   `message` varchar(255) NOT NULL DEFAULT '' COMMENT '获奖信息',
   `created_time` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
   `updated_time` int(11) NOT NULL DEFAULT 0 COMMENT '更新时间',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='获奖信息表';

(一)索引的创建

1.单列索引

1-1)  
 普通索引,这个是最基本的索引,

其sql格式是
CREATE INDEX IndexName ON `TableName`(`字段名`(length)) 或者 ALTER
TABLE TableName ADD INDEX IndexName(`字段名`(length))

第一种方式
:

  CREATE INDEX account_Index ON `award`(`account`);

第二种方式: 

ALTER TABLE award ADD INDEX account_Index(`account`)

 

 

 

如果是CHAR,VARCHAR,类型,length可以小于字段的实际长度,如果是BLOB和TEXT类型就必须指定长度,

1-2)  
 唯一索引,与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值,

其sql格式是 CREATE
UNIQUE INDEX IndexName ON `TableName`(`字段名`(length));
或者 ALTER TABLE TableName ADD UNIQUE (column_list)  

CREATE UNIQUE INDEX account_UNIQUE_Index ON `award`(`account`);

1-3)  
 主键索引,不允许有空值,(在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)

主键索引建立的规则是
int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为
int 而且是 AUTO_INCREMENT自增类型的

 

2.组合索引

一个表中含有多个单列索引不代表是组合索引,通俗一点讲
组合索引是:包含多个字段但是只有索引名称

其sql格式是 CREATE INDEX IndexName On
`TableName`(`字段名`(length),`字段名`(length),…);

 CREATE INDEX nickname_account_createdTime_Index ON `award`(`nickname`, `account`, `created_time`);

 

澳门皇冠金沙网站 2

如果你建立了
组合索引(nickname_account_createdTime_Index)
那么他实际包含的是3个索引 (nickname)
(nickname,account)(nickname,account,created_time)

在使用查询的时候遵循mysql组合索引的”最左前缀”,下面我们来分析一下
什么是最左前缀:及索引where时的条件要按照建立索引的时候字段的排序方式

1、不按索引最左列开始查询(多列索引) 例如index(‘c1’, ‘c2’, ‘c3’) where ‘c2’ = ‘aaa’ 不使用索引,where
`c2` = `aaa` and `c3`=`sss` 不能使用索引

2、查询中某个列有范围查询,则其右边的所有列都无法使用查询(多列查询)

Where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ 改查询只会使用索引中的前两列,因为like是范围查询

3、不能跳过某个字段来进行查询,这样利用不到索引,比如我的sql
是 

explain
select * from `award` where nickname > ‘rSUQFzpkDz3R’ and account
= ‘DYxJoqZq2rd7’ and created_time = 1449567822;
那么这时候他使用不到其组合索引.

因为我的索引是
(nickname, account, created_time),如果第一个字段出现
范围符号的查找,那么将不会用到索引,如果我是第二个或者第三个字段使用范围符号的查找,那么他会利用索引,利用的索引是(nickname),

因为上面说了建立组合索引(nickname,
account, created_time), 会出现三个索引

澳门皇冠金沙网站 3

 澳门皇冠金沙网站 4

(3)全文索引

文本字段上(text)如果建立的是普通索引,那么只有对文本的字段内容前面的字符进行索引,其字符大小根据索引建立索引时申明的大小来规定.

如果文本中出现多个一样的字符,而且需要查找的话,那么其条件只能是
where column lick ‘%xxxx%’ 这样做会让索引失效

.这个时候全文索引就祈祷了作用了

ALTER TABLE tablename ADD FULLTEXT(column1, column2)

有了全文索引,就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。

ELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘xxx′, ‘sss′, ‘ddd′)

这条命令将把column1和column2字段里有xxx、sss和ddd的数据记录全部查询出来。

 

(二)索引的删除

删除索引的mysql格式
:DORP INDEX IndexName ON `TableName`

 

(三)使用索引的优点

1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
2.建立索引可以大大提高检索的数据,以及减少表的检索行数
3.在表连接的连接条件
可以加速表与表直接的相连
4.在分组和排序字句进行数据检索,可以减少查询时间中
分组 和 排序时所消耗的时间(数据库的记录会重新排序)
5.建立索引,在查询中使用索引
可以提高性能

 

(四)使用索引的缺点

1.在创建索引和维护索引
会耗费时间,随着数据量的增加而增加
2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
3.当对表的数据进行
INSERT,UPDATE,DELETE
的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快)。

(五)使用索引需要注意的地方

在建立索引的时候应该考虑索引应该建立在数据库表中的某些列上面
哪一些索引需要建立,哪一些所以是多余的.
一般来说,
1.在经常需要搜索的列上,可以加快索引的速度
2.主键列上可以确保列的唯一性
3.在表与表的而连接条件上加上索引,可以加快连接查询的速度
4.在经常需要排序(order
by),分组(group by)和的distinct 列上加索引 可以加快排序查询的时间,
 (单独order by 用不了索引,索引考虑加where 或加limit)
5.在一些where
之后的 < <= > >= BETWEEN IN 以及某个情况下的like
建立字段的索引(B-TREE)

6.like语句的
如果你对nickname字段建立了一个索引.当查询的时候的语句是 nickname lick
‘%ABC%’ 那么这个索引讲不会起到作用.而nickname lick ‘ABC%’
那么将可以用到索引

7.索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者
‘ ‘字符串

8.使用短索引,如果你的一个字段是Char(32)或者int(32),在创建索引的时候指定前缀长度
比如前10个字符
(前提是多数值是唯一的..)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作.

9.不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描

10.选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中
占用的空间很少,处理起来更快

(六)什么情况下不创建索引

1.查询中很少使用到的列
不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
2.很少数据的列也不应该建立索引,比如
一个性别字段
0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
3.定义为text和image和bit数据类型的列不应该增加索引,
4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系

 

 

 

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图