1. 为什么使用索引
在无索引的情况下, MySQL 会扫描整张表来查找符合 sql 条件的记录, 其时间开销与表中数据量呈正相关. 对关系型数据表中
的某些字段建索引可以极大提高查询速度(当然, 不同字段是否 selective 会导致这些字段建立的索引对查询速度的提升幅
度不同, 而且索引也并非越多越好, 因为写入或删除时需要更新索引信息).
2. 索引的类型:
唯一索引, 全文索引, 组合索引, 反向索引, 函数索引
注意: 当查询第一个使用了不走索引的条件整个语句都不会走索引
3.SQL 什么条件使用索引
1)= ,>,>=, <, <= ,BETWEEN 或者 IN 操作符时使用索引.
2)若 like 语句的条件是不以通配符开头的常量串, MySQL 也会使用索引, 如 LIKE 'XXX%', 则走索引.
3)若已对名为 col_name 的列建了索引, 则形如 "col_name is null" 的 SQL 会用到索引
4.MySQL 使用索引的场景
1)若 where 条件使用了多个索引字段, 则 MySQL 会优先使用能使候选记录集规模最小的那个索引, 以便尽快淘汰不符合条件的记录.
2)如果表中存在几个字段构成的联合索引, 则查找记录时, 这个联合索引的最左前缀匹配字段也会被自动作为索引来加速查找.
例如, 若为某表创建了 3 个字段 (c1, c2, c3) 构成的联合索引, 则 (c1), (c1, c2), (c1, c2, c3) 均会作为索引,
(c2, c3)就不会被作为索引, 而 (c1, c3) 其实只利用到 c1 索引.
4) 多表做 join 操作时会使用索引(如果参与 join 的字段在这些表中均建立了索引的话)
5) 若某字段已建立索引, 求该字段的 min()或 max()时, MySQL 会使用索引
6) 对建立了索引的字段做 sort 或 group 操作时, MySQL 会使用索引
5. 索引的创建
1)单列索引(普通索引这个是最基本的索引)
第一种方式
create index 索引名 on 表名 (字段);
第一种方式
alter table 表名 add index 索引名 (字段);
2)唯一索引
create unique index 索引名 on 表名 (字段);
3)组合索引
create index 索引名 ON 表名(字段 1, 字段 2, 字段 3);
3)全文索引
文本字段上 (text) 如果建立的是普通索引, 那么只有对文本的字段内容前面的字符进行索引, 其字符大小根据索引建立索引
时申明的大小来规定.
如果文本中出现多个一样的字符, 而且需要查找的话, 那么其条件只能是 where column lick '%xxxx%' 这样做会让索引失效
. 这个时候全文索引就祈祷了作用了
ALTER TABLE 索引名 ADD FULLTEXT(column1, column2)
有了全文索引, 就可以用 SELECT 查询命令去检索那些包含着一个或多个给定单词的数据记录了.
- ELECT * FROM tablename
- WHERE MATCH(column1, column2) AGAINST('xxx,'sss, 'ddd)
这条命令将把 column1 和 column2 字段里有 xxx,sss 和 ddd 的数据记录全部查询出来.
(一)使用索引的优点
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) 操作时不应该创建索引, 这两个操作是互斥的关系
为了形象地介绍索引, 再建一个表:
- create table muser (
- id bigint (22) not null auto_increment,
- name varchar(300) default null comment '用户名',
- age varchar(300) default null comment '年龄',
- address varchar(300) default null comment '地址',
- primary key(id)
- )engine=innodb auto_increment=1 default charset=utf8
- select * from muser;
- insert into muser(name, age, address) select name,age,'上海浦东新区陆家嘴' from emp;
- insert into `muser` (`id`, `name`, `age`, `address`) values('1','test02','25','上海浦东新区唐镇高科技园');
- insert into `muser` (`id`, `name`, `age`, `address`) values('3','马风雷','30','浙江杭州金融大厦 201 号 12 室');
- ## 创建索引
- ## 第一种方式
- CREATE INDEX INDEX_NAME ON MUSER(NAME)
- ## 第二种方式
- ALTER TABLE MUSER ADD INDEX INDEX_ADDRESS(ADDRESS)
- ## 删除索引
- DROP INDEX ADDRESS ON MUSER
- SHOW INDEX FROM MUSER
- ## 不走索引 第一个使用了不走索引的条件整个语句都不会走索引
- EXPLAIN SELECT * FROM MUSER WHERE NAME LIKE '% 李 %' AND AGE = 48 AND NAME = '小郑' AND ADDRESS LIKE '% 上海浦东新区 %'
- ## 走 name 和 address 索引
- EXPLAIN SELECT * FROM MUSER WHERE AGE = 48 AND NAME = '小郑' AND ADDRESS LIKE '上海浦东新区 %'
- ## 走 name 索引
- EXPLAIN SELECT * FROM MUSER WHERE AGE = 48 AND NAME = '小郑' AND ADDRESS LIKE '% 上海浦东新区 %'
- ## 不走索引
- EXPLAIN SELECT * FROM MUSER WHERE ID = 2
- EXPLAIN SELECT * FROM MUSER
- EXPLAIN SELECT * FROM MUSER WHERE NAME = '小郑';
- ## 走索引 以 XX 开头的会做索引 其余不走索引
- EXPLAIN SELECT * FROM MUSER WHERE NAME LIKE '李 %'
- ## 以下 like 不走索引
- EXPLAIN SELECT * FROM MUSER WHERE NAME LIKE '% 李 %'
- EXPLAIN SELECT * FROM MUSER WHERE NAME LIKE '% 李'
- ## 走 name 索引
- EXPLAIN SELECT * FROM muser WHERE NAME IS NOT NULL
- EXPLAIN SELECT * FROM muser WHERE NAME IN('小郑','test');
- ## 创建唯一索引
- CREATE UNIQUE INDEX primary_name ON muser (NAME);
- ## 创建组合索引
- CREATE INDEX index_age_name_address ON muser(NAME(13),address(20),age)
- DROP INDEX index_age_name_address ON muser
- /*
建表时, name 长度为 50, 这里为什么使用 13 呢, 因为一般情况下名字的长度不会超过 10, 这样会加速索引查询速度
肯定有人要问了, 如果分别在 name,address,age 上建立单列索引, 让该表有 3 个单列索引, 查询时和上述的组合索引效率一样吗?
大不一样, 远远低于我们的组合索引. 虽然此时有了三个索引, 但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引.
建立这样的组合索引, 其实是相当于分别建立了
(name,address,age) (name,address) (name)
这样的三个组合索引! 为什么没有 address,age 等这样的组合索引呢? 这是因为 mysql 组合索引 "最左前缀" 的结果. 简单的理解就
是只从最左面的开始组合. 并不是只要包含这三列的查询都会用到该组合索引, 下面的几个 T-SQL 会用到:
下面几个会用到:
- SELECT * FROM muser WHREE Name="erquan" AND address="郑州"
- SELECT * FROM muser WHREE Name="erquan"
而下面几个则不会用到:
- SELECT * FROM myIndex WHREE Age=20 AND address="郑州"
- SELECT * FROM myIndex WHREE address="郑州"
来源: http://www.bubuko.com/infodetail-2709654.html