文章目录
一、在创建数据表时创建索引
属性值的含义
INDEX 和 KEY 参数用于指定字段为索引的，用户在选择时，只需要选择其中的一种即可
UNIQUE: 可选参数，表示索引为唯一索引
FULLTEXT: 可选参数，表示索引为全文索引
SPATIAL: 可选参数，表示索引为空间索引
1.创建普通索引
不添加任何参数。
例如：在该表的name字段上建立索引
index(name)
CREATE TABLE `user2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) CHARACTER SET latin1 DEFAULT '',
`age` tinyint(4) DEFAULT '0',
`address` varchar(32) CHARACTER SET latin1 DEFAULT '',
PRIMARY KEY (`id`),
index(name)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
2.创建唯一索引
使用UNIQUE进行约束
例如：在该表的id字段上创建唯一索引
UNIQUE index user3(id ASC)
CREATE TABLE `user3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) CHARACTER SET latin1 DEFAULT '',
`age` tinyint(4) DEFAULT '0',
`address` varchar(32) CHARACTER SET latin1 DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE index user3(id ASC)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
3.创建全文索引
全文索引只能作用在char,varchar,text类型的字段上，创建全文索引需要FULLTEXT参数进行约束。
例如：在该表的name字段上建立全文索引
CREATE TABLE `user4` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) CHARACTER SET latin1 DEFAULT '',
`age` tinyint(4) DEFAULT '0',
`address` varchar(32) CHARACTER SET latin1 DEFAULT '',
PRIMARY KEY (`id`),
FULLTEXT KEY user4(name)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
4.创建单列索引
创建单列索引，即在数据表的单个字段上创建索引。创建该类型索引不需要引入约束参数，用户在建立时只需要指定单列字段名，即可创建单列索引。
例如：在该表指定字段age上建立名称为age_num的单列索引
CREATE TABLE `user5` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) CHARACTER SET latin1 DEFAULT '',
`age` tinyint(4) DEFAULT '0',
`address` varchar(32) CHARACTER SET latin1 DEFAULT '',
PRIMARY KEY (`id`),
index age_num(age)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
5.创建多列索引
创建多列索引即指定表的多个字段即可实现。
例如：在该表中指定name和age为多列索引
CREATE TABLE `user6` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) CHARACTER SET latin1 DEFAULT '',
`age` tinyint(4) DEFAULT '0',
`address` varchar(32) CHARACTER SET latin1 DEFAULT '',
PRIMARY KEY (`id`),
index info(name,age)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
6.创建空间索引
需要设置设置SPATIAL参数，只有MyISAM类型存储引擎支持该类型索引。而且索引字段必须有非空约束。
例如：创建一个名为userinfo的空间索引在goods字段上
CREATE TABLE `user7` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`goods` geometry not null,
PRIMARY KEY (`id`),
SPATIAL INDEX userinfo(goods)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
注意：goods字段不能为空，且数据类型是GEOMETRY（空间数据类型）。空间类型不能用其他类型代替，否则在生成空间索引时会产生错误。
二、判断查询中是否用到了索引
以下例子中用到的表：
表结构是：
创建的索引有：
可以使用explain关键字进行判断。
explain的用法：
EXPLAIN SELECT * FROM goods WHERE id=3
id：选择标识符；
select_type：表示查询的类型；
table：输出结果集的表；
partitions：匹配的分区；
type：表示表的连接类型；
possible_keys：表示查询时，可能使用的索引；
key：表示实际使用的索引；
key_len：索引字段的长度；
ref：列与索引的比较；
rows：扫描出的行数(估算的行数)；
filtered：按表条件过滤的行百分比；
extra：执行情况的描述和说明
索引失效的几种情况：
1.模糊查询中 %放在开头
like ‘%XXX’ 或者 like ‘%XXX%’ 索引会失效
like ‘XXX%’ 索引不会失效
例如：
like ‘%XXX’
EXPLAIN SELECT * FROM user_info3 WHERE name like '%a'
like ‘%XXX%’
EXPLAIN SELECT * FROM user_info3 WHERE name like '%a%'
like ‘XXX%’
EXPLAIN SELECT * FROM user_info3 WHERE name like 'a%'
2.查询条件中有or
查询条件中有or，即使其中有条件带索引也不会使用。(所以很少使用)
不加or时：
EXPLAIN SELECT * FROM user_info3 WHERE id=3
加上or后：
EXPLAIN SELECT * FROM user_info3 WHERE id=3 or age=6
要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
3.字符串不加’ ’
在查询时，没有对字符串加单引号，mysql的查询优化器会自动进行类型转换，造成索引失效
4.索引字段做运算
EXPLAIN SELECT * FROM user_info3 WHERE age*2>6
5.使用 != 和<>
EXPLAIN SELECT * FROM user_info3 WHERE name != 'a'
EXPLAIN SELECT * FROM user_info3 WHERE name <> 'a'
6.正则表达式不使用索引
7.全表扫描比使用索引快
mysql内部优化器会对sql语句进行优化，如果优化器使用全表全表扫描比使用索引快，则不使用索引。
在进行范围查询（比如>、< 、>=、<=、in等条件），会根据实际查询数据来判断，如果全盘扫描速度比索引速度要快则不走索引 。
在字段存在null 和 not null的情况下，如果表中该索引列数据大部分是非空值，则在is not null时使用索引，在is null时不使用索引（全表扫描），反之亦然。
注意：
避免使用select
因为它会进行全表扫描，不能有效利用索引，增大了数据库服务器的负担，以及它与应用程序客户端之间的网络IO开销。
例如：
EXPLAIN SELECT * FROM user_info3 WHERE name ='a' '
这里虽然结果显示用到了索引，但是为了获取非索引项字段，我们需要回表去查询数据，效率较低。
Extra为null表示没有用覆盖索引。
尽量使用覆盖索引(索引列完全包含查询列)
什么是覆盖索引？
select的数据列只用从索引中就能够获得，不必读取数据行，不用回表。
怎么判断有没有用到覆盖索引？
EXPLAIN SELECT age FROM user_info3 WHERE name ='a'
Extra输出Using Index 表示用到了覆盖索引。
