mysql索引理解

mysql中设置数据库的作用：

提高查询速度。索引是指把你设置为索引的字段A的内容储存在一个独立区间S里，里面只有这个字段的内容。在找查这个与这个字段A的内容时会直接从这个独立区间里查找，而不是去到数据表里查找。找到的这些符合条件的字段后再读取字段A所指向真实的数据记录的物理地址，再把对应的数据内容输出。 如果你查找的不是索引的字段那么他会从数据表里面查找。因为数据表有很多不相关的字段，数据库程序是不会省略不查找。要判断那些不相关的字段以及多次在记录中跳转是花费一定的资源的。 当然不是设置越多索引就越好。因为索引都放到这个独立区间S，独立区间S越大搜索所占的资源就越大。如果你只有一个字段为索引，那么你搜索这个字段是非常快速的。

以项例子中用到的数据库表结构如下：

CREATE TABLE `indextest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `nickname` varchar(32) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

mysql中的四种索引类型：

1.普通索引

这是最基本的索引类型，而且它没有唯一性之类的限制。

创建
alter table indextest add index name_index(name)

2.唯一性索引
在普通索引的基础上，增加了数据的唯一性，索引在字段是唯一约束的时候，添加该索引。

3.主键索引
alter table indextest add primary key name_unique(name)
每个表只能有一个主键，可以创建联合主键

4.全文索引

文本字段上的普通索引只能加快对出现在字段内容最前面的字符串进行的检索操作。如果字段里存放的是由学多个单词构成的大段文字，普通索引就没什么作用“%word%”费时，效率低，响应时间长。生成全文索引时，MySQL将把文中出现的所有单词创建一份清单，查询操作根据这份清单去检索相关数据记录。
但目前全文索引对中文支持很差，需要相关分词插件。（如：http://code.google.com/p/mysqlcft/）

多重索引

创建多列索引就是多重索引了
alter table indextest add index nackname_name_index(name,nickname)
多重索引还有另外一个优点，它通过称为最左前缀（Leftmost Prefixing）的概念体现出来。
例如：创建多重索引firstname、lastname、age列，则查询条件：
firstname，lastname，age
firstname，lastname
firstname
就会用到该索引

sql语句中什么位置会用到索引？

在WHERE子句中出现的列
在join子句中出现的列

MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符（%或者_）开头的情形。
举例：
SELECT
    people.age, ##不使用索引,town.name ##不使用索引
FROM
    people
LEFT JOIN
    town
ON
  people.townid=town.townid ##考虑使用索引
WHERE
    firstname='Mike'##考虑使用索引
AND
    lastname='Sullivan'##考虑使用索引

分析索引：

使用关键字explain，格式explain sql

以上表为例创建多重索引：

alter table indextest add index nackname_name_index(name,age,nickname)

分析sql：

explain select * from indextest where name = "aaa" 

输出结果如下：

table：表名

type：连接

possible_keys：索引利用的可能名称

key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。

key_len：索引字符的长度

ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行。

rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1。

*索引的底层结构是B-二叉树，在搜索的基础上大大提高了效率！

哪些操作将会导致添加的索引失效：

1.where条件后面添加!=条件查询

explain select * from indextest where age != -1

2. 量避免使用 GROUP BY、DISTINCT、OR、IN等语句的使用，避免使用联表查询和子查询，有可能会导致索引创建失效

3.使用like模糊查询时候%,_不能为第一个字符，否则索引会失效