51工具盒子

依楼听风雨
笑看云卷云舒,淡观潮起潮落
当前位置：工具盒子 > 开源工具 > 正文

你会使用 mysql索引吗

2024-12-14 分类：开源工具 阅读(3) 评论(0)

索引 {#索引}

  1. 索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。
  2. 对于索引，会保存在额外的文件中。
  3. 索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

索引选取类型 {#索引选取类型}

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

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

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

什么场景不适合创建索引 {#什么场景不适合创建索引}

  1. 对于那些在查询中很少使用或者参考的列不应该创建索引。既然这些列很少使用到，那么有索引或者无索引，并不能提高查询速度。反而由于增加了索引降低了系统的维护速度和增大了空间需求。
  2. 对于那些数据种类稀疏的列也不应该增加索引。比如性别，一般只有两种类型男(0)或者女(1),即使加上索引在查询过程中也不能过滤很多数据，和全表扫描差别不大，但是却会增加索引维护的成本。
  3. 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么很少。
  4. 当修改频率远远大于查询频率时，不应该创建索 引。这是因为，修改和查询是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因 此，当修改性能远远大于检索性能时，不应该创建索引。
  5. 不会出现在where条件中的字段不该建立索引。

什么样的字段适合创建索引 {#什么样的字段适合创建索引}

  1. 表的主键、外键必须有索引；外键是唯一的，而且经常会用来查询
  2. 数据量超过300的表应该有索引；
  3. 经常与其他表进行连接的表，在连接字段上应该建立索引；经常连接查询，需要有索引
  4. 经常出现在Where子句中的字段，加快判断速度，特别是大表的字段，应该建立索引，建立索引，一般用在select ......where f1 and f2 ，我们在f1或者f2上建立索引是没用的。只有两个使用联合索引才能有用
  5. 经常用到排序的列上，因为索引已经排序。
  6. 经常用在范围内搜索的列上创建索引，因为索引已经排序了，其指定的范围是连续的

索引优缺点 {#索引优缺点}

优点 {#优点}

  1. 索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度
  2. 索引的优点是可以提高检索数据的速度

缺点 {#缺点}

  1. 索引的缺点是创建和维护索引需要耗费时间
  2. 索引可以提高查询速度，会减慢写入速度
  3. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

索引分类 {#索引分类}

普通索引 {#普通索引}

仅加速查询 最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。常见的创建索引的方式如下：

CREATE INDEX index_name on user_info(name);

创建表的时候同事创建索引 {#创建表的时候同事创建索引}

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  = '索引测试表';

注意 {#注意}

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

create index idx_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;
`

查看索引 {#查看索引}

show index from healerjean;

唯一索引 {#唯一索引}

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

CREATE UNIQUE INDEX  mail  on user_info(name);

全文索引 {#全文索引}

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

组合索引 {#组合索引}

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

正确使用索引 {#正确使用索引}

规则1 {#规则1}

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

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

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

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

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

mysql> explain select * from sales2 where moneys = 1;
*************************** 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)

规则2 {#规则2}

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

  1. 可以发现第一个例子没有使用索引,而第二例子就能够使用索引,
  2. 区别就在于"%"的位置不同,前者把"%"放到第一位就不能用到索引,而后者没有放到第一位就使用了索引。
mysql> explain select * from company2 where name like '%3';
*************************** 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%';
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: range
possible_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)

规则3 {#规则3}

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

mysql> explain select * from company2 where name is null;
*************************** 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)

规则4 {#规则4}

如果对大的文本进行搜索,使用全文索引而不用使用 like '%...%'

存在索引，但是不使用 {#存在索引但是不使用}

规则1 {#规则1-1}

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

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

规则2 {#规则2-1}

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

规则3 {#规则3-1}

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

mysql> show index from sales;
*************************** 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)

规则4 {#规则4-1}

如果列是字符型，传入的是数字，那么不上''不会使用索引

mysql> explain select * from company2 where name = 294;
*************************** 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';
*************************** 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)
`

联合索引和单列索引 {#联合索引和单列索引}

参考连接

联合索引 {#联合索引}

解释 ：可以通过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) {#查询条件为a-用到了索引a-长度为5}

explain  SELECT * from d001_index WHERE a = 1 ;

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |----|-------------|------------|------------|------|---------------|-------------|---------|-------|------|----------|-------| | 1 | SIMPLE | d001_index | NULL | ref | idx_a_b_c_d | idx_a_b_c_d | 5 | const | 1 | 100 | NULL |

查询条件为b：未用到索引 {#查询条件为b未用到索引}

explain  SELECT * from d001_index WHERE b = 1 ;

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |----|-------------|------------|------------|------|---------------|------|---------|------|------|----------|-------------| | 1 | SIMPLE | d001_index | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25 | Using where |

查询条件为c：未用到索引 (d同理) {#查询条件为c未用到索引-d同理}

explain  SELECT * from d001_index WHERE c = 1 ;

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |----|-------------|------------|------------|------|---------------|------|---------|------|------|----------|-------------| | 1 | SIMPLE | d001_index | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25 | Using where |

查询条件为 b 、 c ：未用到索引 {#查询条件为-b--c-未用到索引}

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |----|-------------|------------|------------|------|---------------|------|---------|------|------|----------|-------------| | 1 | SIMPLE | d001_index | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25 | Using where |

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

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |----|-------------|------------|------------|------|---------------|-------------|---------|-------------|------|----------|-------| | 1 | SIMPLE | d001_index | NULL | ref | idx_a_b_c_d | idx_a_b_c_d | 10 | const,const | 1 | 100 | NULL |

查询条件为 a、c ：用到了联合索引a (长度为5) {#查询条件为-ac-用到了联合索引a-长度为5}

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |----|-------------|------------|------------|------|---------------|-------------|---------|-------|------|----------|-----------------------| | 1 | SIMPLE | d001_index | NULL | ref | idx_a_b_c_d | idx_a_b_c_d | 5 | const | 1 | 25 | Using index condition |

查询条件为 a 、b、c、d：用到了联合索引a b c d (长度为20) {#查询条件为-a-bcd用到了联合索引a-b-c-d-长度为20}

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |----|-------------|------------|------------|------|---------------|-------------|---------|-------------------------|------|----------|-------| | 1 | SIMPLE | d001_index | NULL | ref | idx_a_b_c_d | idx_a_b_c_d | 20 | const,const,const,const | 1 | 100 | NULL |

查询条件为 a or b ：未用到索引 {#查询条件为-a-or-b-未用到索引}

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |----|-------------|------------|------------|------|---------------|------|---------|------|------|----------|-------------| | 1 | SIMPLE | d001_index | NULL | ALL | idx_a_b_c_d | NULL | NULL | NULL | 4 | 50 | Using where |

单列索引 {#单列索引}

查询条件为 name：使用到了索引 name（长度为512 = 4 * 128 + 2） {#查询条件为-name使用到了索引-name长度为512--4--128--2}

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

查询条件为 name 、 age ：只使用了第一个 name索引（长度为512 = 4 * 128 + 2） {#查询条件为-name--age-只使用了第一个-name索引长度为512--4--128--2}

explain  SELECT * from d001_index WHERE name = 'zhangyj' AND age=18;

参考原文：

https://blog.csdn.net/u012954706/article/details/81241049

参考索引优化：

https://www.cnblogs.com/Cheney222/articles/5876382.html
赞(0)
未经允许不得转载：工具盒子 » 你会使用 mysql索引吗
标签：

厉飞雨

众生皆苦，唯有自渡！

相关推荐

最新文章

猜你喜欢

快捷分类

云服务器 日常运维 在线免费 开源工具 科学上网 开发笔记 区块链 白嫖帮 新视野 人工智能 网赚思路 Python笔记 Php笔记 Java笔记 链知识 币知识 IOS笔记 Android笔记 Mysql C语言笔记 前端开发 R笔记 Go笔记 mybatis笔记 Golang笔记 数据库 市场营销 软件教程 vps Rust笔记 软件使用 经验分享 Stable Diffusion chatgpt Midjourney 操作系统 Photoscape 加密货币 挖矿 Coze Meta 网络安全 网络运营 开源软件 Github ComfyUI 数字人 Docker笔记 Claude Oracle web3 linux windows mac redis git笔记 nginx kafka maven spring RocketMQ Zabbix ubuntu centos kubesphere ffmpeg jenkins hbase JavaScript笔记 WordPress nginx Hadoop vuejs mongo SQL Server PostgreSQL SQLite ElasticSearch Spark netty zookeeper RabbitMQ ClickHoust cocos2d gradle tomcat Markdown MinIO react Hexo git intellij idea android studio svn C++ spdlog nodejs C WordPress 宝塔面板 vim shell logstash 软件渗透 NeoDB CSS3 HTML Lua笔记 数据算法 嵌入式 debian 红帽RHEL Neo4j jquery nas 1panel aapanel 浏览器 谷歌 .net 微信 telegram Cloudflare 大数据 rust layui