MySQL索引详解

程序员文章站 2024-01-21 13:10:40

...

什么是索引？

1、索引

索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。

2、索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度
索引的优点是可以提高检索数据的速度
索引的缺点是创建和维护索引需要耗费时间
索引可以提高查询速度，会减慢写入速度

索引分类

1.普通索引
2.唯一索引
3.全文索引
4.单列索引
5.多列索引
6.空间索引
7.主键索引
8.组合索引

普通索引：仅加速查询
唯一索引：加速查询 + 列值唯一（可以有null）
主键索引：加速查询 + 列值唯一 +　表中只有一个（不可以有null）
组合索引：多列值组成一个索引，
专门用于组合搜索，其效率大于索引合并
全文索引：对文本的内容进行分词，进行搜索

索引合并，使用多个单列索引组合搜索
覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

如何创建索引？记住一个单词—explain

创建表的时候创建索引

    CREATE TABLE tbl_name( 

    字段名称 字段类型 [完整性约束条件]， 

    ，，，, 

    [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY索引名称 

    [ASC|DESC] 

    );

在已经存在的表上创建索引：

1.CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称 ON 表名{字段名称[(长度)] [ASC|DESC]} 

2.ALTER TABLE tbl_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX索引名称(字段名称[(长度)][ASC|DESC]);

如何删除索引？

DROP INDEX 索引名称 ON tbl_name

1.普通索引

普通索引仅有一个功能：加速查询

创建表+索引

create table in1( 

    nid int not null auto_increment primary key, 
name varchar(32) not null, 

    email varchar(64) not null, 

    extra text, 
index ix_name (name) 

)

创建索引

create index index_name on table_name(column_name)

删除索引

drop index_name on table_name;

查看索引

show index from table_name;

注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。

create index ix_extra on in1(extra(32));

2、唯一索引

唯一索引有两个功能：加速查询和唯一约束（可含null）

创建表+唯一索引

create table in1( 

    nid int not null auto_increment primary key, 
name varchar(32) not null, 

    email varchar(64) not null, 

    extra text, 
unique ix_name (name) 

)

创建唯一索引

create unique index 索引名 on 表名(列名)

删除唯一索引

drop unique index 索引名 on 表名

3、主键索引

主键有两个功能：加速查询和唯一约束（不可含null）

创建表+创建主键

create table in1( 

    nid int not null auto_increment primary key, 
name varchar(32) not null, 

    email varchar(64) not null, 

    extra text, 
index ix_name (name) 

)

create table in1(
nid int not null auto_increment,
name varchar(32) not null,
email varchar(64) not null,
extra text,
primary key(ni1),
index ix_name (name)
)

创建主键

alter table 表名 add primary key(列名);

删除主键

alter table 表名 drop primary key; 
alter table 表名  modify  列名 int, drop primary key;

4、组合索引

组合索引是将n个列组合成一个索引

其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = ‘alex’ and n2 = 666。

创建表

create table in3( 

    nid int not null auto_increment primary key, 
name varchar(32) not null, 

    email varchar(64) not null, 

    extra text 

)

创建组合索引

create index ix_name_email on in3(name,email);

如上创建组合索引之后，查询：

name and email – 使用索引
name — 使用索引
email — 不使用索引

注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。

使用索引和不使用索引

由于索引是专门用于加速搜索而生，所以加上索引之后，查询效率会快到飞起来。


# 有索引 

mysql> select * from tb1 where name = 'wupeiqi-888'; 

+-----+-------------+---------------------+----------------------------------+---------------------+ 
| nid | name        | email               | radom                            | ctime               | 

+-----+-------------+---------------------+----------------------------------+---------------------+ 
| 889 | wupeiqi-888 | aaa@qq.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 | 

+-----+-------------+---------------------+----------------------------------+---------------------+ 
1 row in set (0.00 sec)

# 无索引 

mysql> select * from tb1 where email = 'aaa@qq.com'; 

+-----+-------------+---------------------+----------------------------------+---------------------+ 
| nid | name        | email               | radom                            | ctime               | 

+-----+-------------+---------------------+----------------------------------+---------------------+ 
| 889 | wupeiqi-888 | aaa@qq.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 | 

+-----+-------------+---------------------+----------------------------------+---------------------+ 
1 row in set (1.23 sec)

正确使用索引

数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。

即使建立索引，索引也不会生效：

- like '%xx' 
select * from tb1 where name like '%cn'; 

- 使用函数 
select * from tb1 where reverse(name) = 'wupeiqi'; 

- or 
select * from tb1 where nid = 1 or email = 'aaa@qq.com'; 

    特别的：当or条件中有未建立索引的列才失效，以下会走索引 
select * from tb1 where nid = 1 or name = 'seven'; 
select * from tb1 where nid = 1 or email = 'aaa@qq.com' and name = 'alex' 

- 类型不一致 

    如果列是字符串类型，传入条件是必须用引号引起来，不然... 
select * from tb1 where name = 999; 

- != 
select * from tb1 where name != 'alex' 

    特别的：如果是主键，则还是会走索引 
select * from tb1 where nid != 123 

- > 
select * from tb1 where name > 'alex' 

    特别的：如果是主键或索引是整数类型，则还是会走索引 
select * from tb1 where nid > 123 
select * from tb1 where num > 123 

- order by 
select email from tb1 order by name desc; 

    当根据索引排序时候，选择的映射如果不是索引，则不走索引 

    特别的：如果对主键排序，则还是走索引： 
select * from tb1 order by nid desc;

组合索引最左前缀 

如果组合索引为：(name,email) 

name and email       -- 使用索引 

name                 -- 使用索引 

email                -- 不使用索引

其他注意事项

- 避免使用select * 
- count(1)或count(列) 代替 count(*) 
- 创建表时尽量时 char 代替 varchar 
- 表的字段顺序固定长度的字段优先 
- 组合索引代替多个单列索引（经常使用多个条件查询时） 
- 尽量使用短索引 
- 使用连接（JOIN）来代替子查询(Sub-Queries) 
- 连表时注意条件类型需一致 
- 索引散列值（重复少）不适合建索引，例：性别不适合

limit分页

无论是否有索引，limit分页是一个值得关注的问题

MySQL索引详解

每页显示10条： 

当前 118 120， 125


倒序： 

            大      小 

            980    970  7 6  6 5  54  43  32

21 19 98 

下一页：

数据库查询优化索引的一些注意点 

尽量不要在where 条件之后使用函数来作为查询条件，因为这样做会使得该查询字段的索引失效

在做查询的时候如果in条件中仍然有select子查询，那么我们应该使用连接查询join代替子查询，子查询会很影响查询的效率

多表查询时把数据量最大的表最后连接。或者直接将之前数据量较小的表都连接之后在括号外再去连接数据量特别大的那张表，这样会避免数据量大的表进行全表查找。

对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

多条件添加复合索引比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;

如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效

率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,

salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀

特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

alter table users add index area_age_salary(area,age,salary)

索引不会包含有NULL值的列

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

使用短索引

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

alter table users add index index_title(title(10))

排序的索引问题

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

like语句操作

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

不要在列上进行运算

select * from users where YEAR(adddate)

不使用NOT IN操作

NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替

尽量少用or条件

or条件会使条件带索引的失效，如果想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

select * from order where id=10 or user_id=88 假如id为索引user_id不是索引则此条件查询索引失效，如果想让索引有效user_id也得设为索引

如果列类型为字符串，则一定要在条件中将数据使用引号引用起来，否则不使用索引

select * from user where user_name=111 需改为 select * from user where user_name=’111’

不要在索引字段上使用not，<>，!=

不要在索引列上使用is null 和is not null

不要在索引列上出现数据类型转换

MySql 各种存储引擎的特性对照详单： 


从中我们能够看出，

InnoDB 支持事务。支持行级别锁定。支持 B-tree、Full-text 等索引。不支持 Hash 索引。 

MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引。不支持 Hash 索引； 

Memory 不支持事务，支持表级别锁定。支持 B-tree、Hash 等索引，不支持 Full-text 索引。 

NDB 支持事务，支持行级别锁定。支持 Hash 索引。不支持 B-tree、Full-text 等索引； 

Archive 不支持事务。支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引。

上一篇：小米11对比红米k40pro哪个值得买?

下一篇： php+html点击a标签之后如何下载mp3格式的文件而不是直接打开？在线等！_html/css_WEB-ITnose

MySQL索引详解

什么是索引？

索引分类

如何创建索引？记住一个单词—explain

相关命令

使用索引和不使用索引

正确使用索引

其他注意事项

limit分页

NULL与MySQL空字符串的区别_MySQL

PHP 脚本数据库功能详解_MySQL

mysql查询结果排序，name字段有中文名字，有英文名字，如何按a-z的顺序把名字排序

Windows下忘记MySQL root密码的解决方法

解决MYSQL出现Can''t create/write to file ''#sql_5c0_0.MYD''的问题

mysql基本运算符

Percona Thread Pool性能基准测试_MySQL

mysql explain 说明文档 mysql索引说明文档

AngularJS基础 ng-mouseleave 指令详解

C语言函数传递指针参数的问题详解