mysql索引的新手入门详解_mysql索引的新手入门详解(转)

最新推荐文章于 2023-07-20 19:49:48 发布

藏漂老白

最新推荐文章于 2023-07-20 19:49:48 发布

阅读量67

点赞数

文章标签： mysql索引的新手入门详解

本文链接：https://blog.csdn.net/weixin_34872032/article/details/113197043

版权

原文：https://blog.csdn.net/u012954706/article/details/81241049

1、索引

1.1、简要

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

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

1.2、索引选取类型

①、越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

②、简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。

③、尽量避免NULL：应该指定列为NOT nuLL,在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂

1.3、什么场景不适合创建索引

①、对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

②、对于那些只有很少数据值的列也不应该增加索引。因为本来结果集合就是相当于全表查询了，所以没有必要。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

③、对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

④、当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

⑤、不会出现在where条件中的字段不该建立索引。

1.4、什么样的字段适合创建索引

①、表的主键、外键必须有索引；外键是唯一的，而且经常会用来查询

②、数据量超过300的表应该有索引；

③、经常与其他表进行连接的表，在连接字段上应该建立索引；经常连接查询，需要有索引

④、经常出现在Where子句中的字段，加快判断速度，特别是大表的字段，应该建立索引，建立索引，一般用在select ……where f1 and f2 ，我们在f1或者f2上建立索引是没用的。只有两个使用联合索引才能有用

⑤、经常用到排序的列上，因为索引已经排序。

⑥、经常用在范围内搜索的列上创建索引，因为索引已经排序了，其指定的范围是连续的

2、索引优缺点

2.1、优点

索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度

索引的优点是可以提高检索数据的速度

2.2、缺点

索引的缺点是创建和维护索引需要耗费时间

索引可以提高查询速度，会减慢写入速度

索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

3、索引分类

3.1、普通索引：

仅加速查询最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。

CREATE INDEX index_name on user_info(name) ;

3.2、唯一索引：

与普通索引类型，不同的是：加速查询 + 列值唯一(可以有null)

CREATE UNIQUE INDEX mail on user_info(name) ;

3.3、全文索引：

全文索引(FULLTEXT)仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。

3.4，组合索引：

将几个列作为一条索引进行检索，使用最左匹配原则。

5、索引使用

5.1、普通索引

①、创建表的时候同事创建索引

create table healerjean (

id bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',

name VARCHAR(32) NOT NULL COMMENT '姓名',

email VARCHAR(64) NOT NULL COMMENT '邮箱',

message text DEFAULT NULL COMMENT '个人信息',

INDEX index_name (name) COMMENT '索引name'

) COMMENT = '索引测试表';

②、在存在的表上创建索引

create index index_name on healerjean(name)

③、注意：对于创建索引时如果是blob 和 text 类型，必须指定length。

create index ix_extra on in1(message(200));

alter table employee add index emp_name (name);

⑤、删除索引

drop index_name on healerjean;

alter TABLE users drop index name_index ;

⑥、查看索引

这个时候，我们会发现其实主键id也是一个索引

show index from healerjean;

5.2、主键索引

我们一般都会提供主键的，默认主键就是索引

6、正确使用索引

6.1、对于创建的多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用

①、首先按 company_id,moneys 的顺序创建一个复合索引,具体如下:

mysql> create index ind_sales2_companyid_moneys on sales2(company_id,moneys);

Query OK, 1000 rows affected (0.03 sec)

Records: 1000 Duplicates: 0 Warnings: 0

②、然后按 company_id 进行表查询,具体如下:

mysql> explain select * from sales2 where company_id = 2006\G;

********* 1. row *********

id: 1

select_type: SIMPLE

table: sales2

type: ref

possible_keys: ind_sales2_companyid_moneys

208key: ind_sales2_companyid_moneys

key_len: 5

ref: const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

③、可以发现即便 where 条件中不是用的 company_id 与 moneys 的组合条件,索引仍然能用到,这就是索引的前缀特性。

④、但是如果只按 moneys 条件查询表,那么索引就不会被用到,具体如下:

mysql> explain select * from sales2 where moneys = 1\G;

********* 1. row *********

id: 1

select_type: SIMPLE

table: sales2

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 1000

Extra: Using where

1 row in set (0.00 sec)

6.2、对于使用 like 的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会被使用：

可以发现第一个例子没有使用索引,而第二例子就能够使用索引,

区别就在于“%”的位置不同,前者把“%”放到第一位就不能用到索引,而后者没有放到第一位就使用了索引。

mysql> explain select * from company2 where name like '%3'\G;

********* 1. row *********

id: 1

select_type: SIMPLE

table: company2

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 1000

Extra: Using where

1 row in set (0.00 sec)

mysql> explain select * from company2 where name like '3%'\G;

********* 1. row *********

id: 1

select_type: SIMPLE

table: company2

type: range

209possible_keys: ind_company2_name

key: ind_company2_name

key_len: 11

ref: NULL

rows: 103

Extra: Using where

1 row in set (0.00 sec)

6.3、如果列名，记得是列的名字，是索引,使用 column_name is null 将使用索引。

mysql> explain select * from company2 where name is null\G;

********* 1. row *********

id: 1

select_type: SIMPLE

table: company2

type: ref

possible_keys: ind_company2_name

key: ind_company2_name

key_len: 11

ref: const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

6.4、如果对大的文本进行搜索,使用全文索引而不用使用 like ‘%…%’。

6.5、存在索引，但是不使用

①、如果 MySQL 估计使用索引比全表扫描更慢,则不使用索引，例如，如果列key_part1 均匀分布在 1 和 100 之间,下列查询中使用索引就不是很好:

SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;

②、如果使用 MEMORY/HEAP 表并且 where 条件中不使用“=”进行索引列,那么不会用到索引。heap 表只有在“=”的条件下才会使用索引。

③、用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到,例如:，必须or前后都有索引才能被使用，而且必须是单独索引。

mysql> show index from sales\G;

*************************** 1. row ***************************

Table: sales

Non_unique: 1

Key_name: ind_sales_year

Seq_in_index: 1

Column_name: year

210Collation: A

Cardinality: NULL

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

1 row in set (0.00 sec)

6.6、如果列是字符型，，传入的是数字，则不上‘’不会使用索引

mysql> explain select * from company2 where name = 294\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: company2

type: ALL

possible_keys: ind_company2_name

key: NULL

key_len: NULL

ref: NULL

rows: 1000

Extra: Using where

1 row in set (0.00 sec)

mysql> explain select * from company2 where name = '294'\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: company2

type: ref

possible_keys: ind_company2_name

key: ind_company2_name

key_len: 23

ref: const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

7、联合索引

解释：可以通过key_len的长度来判断联合索引使用到了那些

CREATE TABLE `d001_index` (

`id` bigint(16) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(128) DEFAULT NULL,

`age` bigint(20) DEFAULT '0',

`country` varchar(50) DEFAULT NULL,

`a` int(11) DEFAULT '0',

`b` int(11) DEFAULT '0',

`c` int(11) DEFAULT '0',

`d` int(11) DEFAULT '0',

PRIMARY KEY (`id`),

KEY `idx_a_b_c_d` (`a`,`b`,`c`,`d`),

KEY `idx_age` (`age`),

KEY `idx_name` (`name`)

)

INSERT INTO `hlj-mysql`.d001_index (id, name, age, country, a, b, c, d) VALUES (1, 'zhangyj', 25, 'chine', 1, 2, 3, 4);

INSERT INTO `hlj-mysql`.d001_index (id, name, age, country, a, b, c, d) VALUES (2, 'healerjean', 24, 'china', 2, 3, 4, 5);

INSERT INTO `hlj-mysql`.d001_index (id, name, age, country, a, b, c, d) VALUES (3, 'n', 22, 'a', 2, 4, 5, 6);

INSERT INTO `hlj-mysql`.d001_index (id, name, age, country, a, b, c, d) VALUES (4, 'k', 2, 'b', 3, 5, 6, 8);

INSERT INTO `hlj-mysql`.d001_index ( name, age, country, a, b, c, d) VALUES ( 'zhangyj', 25, 'chine', 1, 2, 3, 4);

INSERT INTO `hlj-mysql`.d001_index ( name, age, country, a, b, c, d) VALUES ( 'healerjean', 24, 'china', 2, 3, 4, 5);

INSERT INTO `hlj-mysql`.d001_index ( name, age, country, a, b, c, d) VALUES ( 'n', 22, 'a', 2, 4, 5, 6);

INSERT INTO `hlj-mysql`.d001_index ( name, age, country, a, b, c, d) VALUES ( 'k', 2, 'b', 3, 5, 6, 8);

①、查询条件为a :用到了索引a (长度为5)

explain SELECT * from d001_index WHERE a = 1 ;

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

d001_index

NULL

ref

idx_a_b_c_d

const

100

NULL

②、查询条件为b：未用到索引

explain SELECT * from d001_index WHERE b = 1 ;

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

d001_index

NULL

ALL

NULL

Using where

③、查询条件为c：未用到索引 (d同理)

explain SELECT * from d001_index WHERE c = 1 ;

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

d001_index

NULL

ALL

NULL

Using where

④、查询条件为 b 、 c ：未用到索引

explain SELECT * from d001_index WHERE b = 1 and c = 2 ;

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

d001_index

NULL

ALL

NULL

Using where

⑤、查询条件为 a 、 b：用到了联合索引 a 、b (长度为10)

explain SELECT * from d001_index WHERE a = 1 and b = 2 ;

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

d001_index

NULL

ref

idx_a_b_c_d

const,const

100

NULL

⑥、查询条件为 a、c ：用到了联合索引a (长度为5)

explain SELECT * from d001_index WHERE a = 1 and c = 3 ;

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

d001_index

NULL

ref

idx_a_b_c_d

const

Using index condition

⑦、查询条件为 a 、b、c、c：用到了联合索引a b c d (长度为20)

explain SELECT * from d001_index WHERE a = 1 and b = 2 and c = 3 and d = 4 ;

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

d001_index

NULL

ref

idx_a_b_c_d

const,const,const,const

100

NULL

⑧、查询条件为 a or b ：未用到索引

explain SELECT * from d001_index WHERE a = 1 or b = 2;

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

d001_index

NULL

ALL

idx_a_b_c_d

NULL

Using where

⑨、精确查找联合索引总结

顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上

多个单列索引在多条件查询时只会生效第一个索引！所以多条件联合查询时最好建联合索引！当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；当然，我们上面测试过，a,c组合也可以，但实际上只用到了a的索引，c并没有用到！

具体使用 a b c 的顺序无关，mysql会自动优化，但是我们建议按照索引的顺序进行查询，而且尽量将筛选力度大的放到前面，其实这种也不要一定是准确的，其实真正有影响的是是否用到了索引

8、单列索引

①、查询条件为 name：使用到了索引 name(长度为512 = 4 * 128 + 2)

explain SELECT * from d001_index WHERE name = 'zhangyj' ;

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

d001_index

NULL

const

idx_name

515

const

100

NULL

②、查询条件为 name 、 age ：只使用了第一个 name索引(长度为512 = 4 * 128 + 2)

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

d001_index

NULL

const

idx_name,idx_age

idx_name

515

const

100

NULL

③、查询条件为 name or age ：两个索引都用上了 type = INDEX_MERGE 合并索引

我这里的测试失败了，应该是由于我的数据表数据量比较小的原因

9、联合索引

9.1、联合索引范围查询

解释：范围查询使用到第几列，则联合索引该列后面的字段的不能使用索引

注意：不要取极端值测试，因为mysql优化器会通过索引查找的数量造成一定的影响，即使使用了索引，但是索引却没能生效，比如下的 3 变成 1 会受到影响，因为我的数据量中a最小就是 1

explain SELECT * from d001_index WHERE a > 1 ;

-- 没有使用索引，因为数据均匀分布在1 以上 (有1，但是和1比较了，所以也算在了里面)

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

d001_index

NULL

ALL

idx_a_b_c_d

NULL

Using where

①、a > 3 使用了索引 a (长度为 5 )

explain SELECT * from d001_index WHERE a > 3;

-- 使用到了索引

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

d001_index

NULL

range

idx_a_b_c_d

NULL

100

Using index condition

②、a = 1 and b > 1 ：使用了联合索引 a、b(长度为10)

explain SELECT * from d001_index WHERE a = 1 and b > 1 ;

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

d001_index

NULL

range

idx_a_b_c_d

NULL

100

Using index condition

③、a = 5 AND b > 6 AND c = 7 ：使用了联合索引 a、b(长度为10)

explain SELECT * from d001_index WHERE a = 5 AND b > 6 AND c = 7;

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

d001_index

NULL

range

idx_a_b_c_d

NULL

12.5

Using index condition

网络1 、可以用到所以的情况

A>5

A=5 AND B>6

A=5 AND B=6 AND C=7

网络2、下面条件将不能用上组合索引查询：

B>5 ——查询条件不包含组合索引首列字段

B=6 AND C=7 ——查询条件不包含组合索引首列字段

网络3、下面条件将能用上部分组合索引查询：

A>5 AND B=2 ——当范围查询使用第一列，查询条件仅仅能使用第一列 A

A=5 AND B>6 AND C=2 ——范围查询使用第二列，查询条件仅仅能使用前二列 A B

9.2、联合查询范围排序

注意：表中的数据量和查询的数据量会造成影响，所以我这里都普遍使用了limit 1 ，博主测试有一些结果没有写，测试失败了，应该是由于数据量的原因，

①、order by a 使用到了联合索引 a b c d 按理说应该只用到a才对，这里博主有些疑惑

explain SELECT * from d001_index order by a limit 1;

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

d001_index

NULL

index

NULL

idx_a_b_c_d

NULL

100

NULL

②、order by b 未使用索引

explain SELECT * from d001_index order by b limit 1;

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

d001_index

NULL

ALL

NULL

100

Using filesort

网络1、组合索引排序的各种场景

ORDER BY A ——首列排序

A=5 ORDER BY B——第一列过滤后第二列排序

ORDER BY A DESC, B DESC——注意，此时两列以相同顺序排序

A>5 ORDER BY A——数据检索和排序都在第一列

网络2、不可以用到组合索引

ORDER BY B ——排序在索引的第二列

A>5 ORDER BY B ——范围查询在第一列，排序在第二列

A IN(1,2) ORDER BY B ——理由同上

ORDER BY A ASC, B DESC ——注意，此时两列以不同顺序排序

藏漂老白

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql索引的新手入门详解_mysql索引的新手入门详解(转)

原文：https://blog.csdn.net/u012954706/article/details/812410491、索引1.1、简要索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。索引是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。1...
复制链接

扫一扫