首页 > 数据库 >SQL索引

SQL索引

时间：2023-10-08 16:03:36浏览次数：27  
标签：索引 select key SQL NULL root localhost

目录

MySQL索引及执行计划

什么是索引？

1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容。

2）让获取的数据更有目的性，从而提高数据库检索数据的性能。

给指定字段创建索引，索引会将该字段中所有数据进行排序

索引不是越多越好

创建索引，会占用磁盘空间

索引的排序方式

1)BTREE:B+树索引

2)HASH：HASH索引

3)FULLTEXT：全文索引

4)RTREE：R树索引

BTtree

三路Btree

  • 根节点
  • 枝节点
  • 叶子节点

B+Tree

1）在叶子节点添加了相邻节点的指针

2）优化了范围查询，提升了范围查询的执行效率

选择5.7的mysql里用的就是B+Tree但是显示的是BTREE这个只是统称。

B*Tree

还真是概念类

索引管理

索引分类

  • 主键索引（聚簇索引）

    特性：唯一且非空

    • 联合索引

  • 唯一键索引

    特性：唯一 可以为空

    • 前缀索引
    • 联合索引

  • 普通索引

    特性：可以不唯一，可以为空

    • 前缀索引
    • 联合索引

索引的增删查

表数据准备

# 建库
create database zls;

# 建表
create table student(
id int,
name varchar(10),
age tinyint,
gender enum('m','f'),
phone char(11)
);

# 传送数据
insert into student values
(1,'zls',18,'m',11111111111),
(2,'a',12,'f',11111131111),
(3,'b',13,'f',11241131111),
(4,'a',14,'m',11121131111);

root@localhost [zls] >select * from student;
+----+------+------+--------+-------------+
| id | name | age  | gender | phone       |
+----+------+------+--------+-------------+
|  1 | zls  |   18 | m      | 11111111111 |
|  2 | a    |   12 | f      | 11111131111 |
|  3 | b    |   13 | f      | 11241131111 |
|  4 | a    |   14 | m      | 11121131111 |
+----+------+------+--------+-------------+

主键索引primary

## 增
root@localhost [zls] >alter table student add primary key(id);

## 删
root@localhost [zls] >alter table student drop primary key;

# 索引类型的字段:key:(PRI:主键，UNI:唯一键，MUL:普通键)

## 查
root@localhost [zls] >desc student;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(11)       | NO   | PRI | NULL    |       |
| name   | varchar(10)   | YES  |     | NULL    |       |
| age    | tinyint(4)    | YES  |     | NULL    |       |
| gender | enum('m','f') | YES  |     | NULL    |       |
| phone  | char(11)      | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+
2.#-----
root@localhost [zls] >show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3.#-----
root@localhost [zls] >show index from student\G
*************************** 1. row ***************************
        Table: student          // 索引所属的表名
   Non_unique: 0                // 指示索引是否允许重复值。值为0表示唯一索引，值为1表示非唯一索引。
     Key_name: PRIMARY          // 显示索引的名称。
 Seq_in_index: 1                // 显示列在联合索引中的位置。对于单列索引，序列号始终为1。
  Column_name: id               // 显示索引所涵盖的列名。
    Collation: A                // 指定索引使用的排序规则。
  Cardinality: 4                // 表示索引中不重复的值的估计数量。
     Sub_part: NULL             // 如果索引是部分索引，则显示部分索引定义的长度。
       Packed: NULL             // 用于存储索引值的压缩类型。
         Null:                  // 指示列是否允许为空值（YES表示允许，空白表示不允许）。
   Index_type: BTREE            // 指示索引的类型，如BTREE、HASH等。
      Comment:                  // 可选的注释信息。
Index_comment:                  // 索引的注释信息。
4.#-----
root@localhost [zls] >show create table student;
student | CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `gender` enum('m','f') DEFAULT NULL,
  `phone` char(11) DEFAULT NULL,
  PRIMARY KEY (`id`),                   // 主键
  UNIQUE KEY `uni_phone` (`phone`),     // 唯一键
  KEY `name` (`name`)                   // 普通键
) ENGINE=InnoDB DEFAULT CHARSET=utf8

唯一键索引unique

## 增
# 统计,统计去重
root@localhost [zls] >select count(name) from student;
+-------------+
| count(name) |
+-------------+
|           4 |
+-------------+
root@localhost [zls] >select count(distinct(name)) from student;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
|                     3 |
+-----------------------+
root@localhost [zls] >select count(age) from student;
+------------+
| count(age) |
+------------+
|          4 |
+------------+
root@localhost [zls] >select count(distinct(age)) from student;
+----------------------+
| count(distinct(age)) |
+----------------------+
|                    4 |
+----------------------+
### 要创建唯一键的时候去重前后的数值要一样，

## 1.不给索引起名
root@localhost [zls] >alter table student add unique key(age);
## 2.给索引起名
root@localhost [zls] >alter table student add unique key uni_phone(phone);

## 删
root@localhost [zls] >alter table student drop index age;
root@localhost [zls] >alter table student drop key uni_phone;

普通索引

## 创建
# 表内有重复的也可以创建，这样的索引。慢

# 1.不起名，以字段名，命名
root@localhost [zls] >alter table student add index(name);

# 2.起名
root@localhost [zls] >alter table student add key idx_name(name);

## 删除
root@localhost [zls] >alter table student drop index idx_name;
root@localhost [zls] >alter table student drop key name;

前缀索引

什么是前缀索引？

创建索引时，按照指定数值对列进行排序

为什么使用前缀索引？

1）给大列创建索引时，可以减少排序时间，提升创建索引速度

2）insert，update，delete插入数据时，提升写入速度

3）提升查询速率

## 唯一键前缀索引创建
root@localhost [zls] >alter table student add unique key uni_phone(phone(7));
## 普通索引创建
root@localhost [zls] >alter table student add key idx_name(name(2));

联合索引

## 主键联合索引
root@localhost [zls] >alter table student add primary key(id,name);

## 唯一键联合索引创建
root@localhost [zls] >alter table student add unique key uni_all(gender,age,phone);

## 普通索引创建联合索引
root@localhost [zls] >alter table student add index idx_all(gender,age,phone);

## 删除
root@localhost [zls] >alter table student drop index idx_all;
root@localhost [zls] >alter table student drop key idx_all;

## 查询规则

alter table student add index idx_all(a,b,c,d);

where a.女生 and b.身高 and c.体重 and d.身材好
index(a,b,c)
特点：前缀生效特性
a,ab,ac,abc,abcd 可以走索引或部分走索引
b bc bcd cd c d ba ... 不走索引

网站访问速度慢，如何排查，如何解决？

  • 网络问题
  • 设备配置
  • 代码bug
  • 应用（nginx、lb、proxy）
  • 数据库慢查询

使用explain分析SQL语句

慢查询，开启慢查询日志，记录执行速度慢的SQL语句

找出执行慢的SQL语句后，使用explain进行分析

# 在查询语句前面加上explain
root@localhost [zls] >explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

root@localhost [zls] >explain select * from student\G
*************************** 1. row ***************************
           id: 1              // 表示当前行的标识符。在单个查询中，如果有多个表被连接，每个表都会有一个唯一的标识符。
  select_type: SIMPLE         // 表示查询的类型。常见的类型包括SIMPLE、PRIMARY、SUBQUERY等。
        table: student        // 表示查询涉及的表名。
   partitions: NULL           // 表示查询涉及的分区信息。
         type: ALL            // 表示访问表的方式或连接类型。常见的类型包括ALL、index、range、ref等。较好的连接类型通常是使用索引（index）或范围查找（range）。
possible_keys: NULL           // 表示可能用于此查询的索引列表。
          key: NULL           // 表示实际使用的索引。如果为NULL，则表示没有使用索引。
      key_len: NULL           // 表示使用的索引的长度。较小的长度通常是更好的。
          ref: NULL           // 表示与索引匹配的列或常数。
         rows: 4              // 表示根据表统计信息估计出的查询返回的行数。
     filtered: 100.00         // 表示在表中过滤记录所占的百分比。
        Extra: NULL           // 表示关于查询执行的附加信息，例如使用了临时表(Temporary Table)、文件排序(Filesort)等。

Type：全量扫描

练习用表结构

root@localhost [world] >desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   | MUL | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

全表扫描

  • ALL
root@localhost [world] >explain select * from city where district='shanghai'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4188
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

index：全索引扫描

索引扫描

root@localhost [world] >explain select countrycode from city\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: index
possible_keys: NULL
          key: CountryCode
      key_len: 3
          ref: NULL
         rows: 4188
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

range：范围扫描

一般来说一条sql语句能达到range级别就ok了

root@localhost [world] >explain select * from city where population < 100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: range
possible_keys: Population
          key: Population
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

ref：非唯一索引

root@localhost [world] >explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA'\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: city
   partitions: NULL
         type: ref
possible_keys: CountryCode
          key: CountryCode
      key_len: 3
          ref: const
         rows: 363
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: city
   partitions: NULL
         type: ref
possible_keys: CountryCode
          key: CountryCode
      key_len: 3
          ref: const
         rows: 274
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

eq_ref：只有在连表查询时，使用join on 才能出现

并且在连接条件中使用了唯一索引或主键列时，优化器可以通过索引快速定位到满足条件的行，从而提供更高效的查询性能。

root@localhost [linux50] >select * from score;
+-----+-----+------+
| sno | cno | mark |
+-----+-----+------+
|   1 |   2 | 30.0 |
+-----+-----+------+
1 row in set (0.00 sec)

root@localhost [linux50] >select * from student;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday           | class |
+-----+-------+------+------+---------------------+-------+
|   1 | 旭    |   20 | 1    | 2023-07-26 17:33:14 | 5     |
|   2 | 东    |   21 | 1    | 2023-07-26 17:33:27 | 5     |
|   3 | 徐    |   22 | 1    | 2023-07-26 17:33:39 | 5     |
+-----+-------+------+------+---------------------+-------+
3 rows in set (0.00 sec)

root@localhost [linux50] >explain select score.mark from student join score on student.sno=score.sno where student.sname='旭'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: score
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: linux50.score.sno
         rows: 1
     filtered: 50.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

const、system 主键精准查询时

root@localhost [world] >explain select * from city where id=10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

null

root@localhost [world] >explain select * from city where population>1000000000000000000000000000000000000000000000000000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: no matching row in const table
1 row in set, 1 warning (0.00 sec)

没有走索引原因？

该字段没创建索引

创建索引了，但是没走

不使用where条件

# 1.使用select * 查询数据不接where条件（杀个程序员祭天）

root@localhost [world] >explain select * from city;

查询结果集大于25%

root@localhost [world] >explain select * from city where population > 1000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ALL
possible_keys: Population
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4188
     filtered: 97.13               // 表中占比97.13
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
使用limit分页查询方式优化结果集
root@localhost [world] >explain select * from city where population > 1000 limit 60\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: range
possible_keys: Population
          key: Population
      key_len: 4
          ref: NULL
         rows: 4068
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

使用字段列做计算

同不加where条件的一起。

root@localhost [world] >explain select * from city where id-1=10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4188
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
## 可行的计算方法
root@localhost [world] >explain select * from city where id=10-1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

隐式转换导致索引失效

## 创建表时，数据类型和查询时不一致导致，不走索引

字符串类型：加引号
root@localhost [world] >explain select * from user where phone='21111111111';

数字类型：不加引号
root@localhost [world] >explain select * from user where phone=21111111111;

使用like模糊查询时，%在前面的

如果需求是必须百分号在前面，不要使用MySQL

请使用elastsearch

root@localhost [world] >explain select * from city where countrycode like '%h'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4188
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

root@localhost [world] >explain select * from city where countrycode like 'h%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: range
possible_keys: CountryCode
          key: CountryCode
      key_len: 3
          ref: NULL
         rows: 22
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

使用 <> 、 not in查询数据

root@localhost [world] >explain select * from city where countrycode not in ('CHN','USA');
root@localhost [world] >explain select * from city where countrycode <> 'CHN' and countrycode <> 'USA';

root@localhost [world] >explain select * from city where countrycode <> 'CHN' and countrycode <> 'USA'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ALL
possible_keys: CountryCode
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4188
     filtered: 82.19
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

root@localhost [world] >explain select * from city where countrycode <> 'CHN' and countrycode <> 'USA' limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: range
possible_keys: CountryCode
          key: CountryCode
      key_len: 3
          ref: NULL
         rows: 3442
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

联合索引，where条件后面没有按照创建索引的顺序查询

1）按顺序查
解决：2）用户行为分析（数据分析）

索引损坏，失效

删了索引
重建，使用前缀索引

索引创建原则

1）索引不是越多越好

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

2）有必要添加索引的列，如何选择索引？

删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理 员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

# 1.优先选择，唯一键索引
# 2.给经常要排序、分组这种查询的列，创建联合索引
# 3.普通索引尽量使用前缀索引

标签：索引,select,key,SQL,NULL,root,localhost
From： https://www.cnblogs.com/xiutai/p/17749302.html

相关文章

  • MySQL日志管理
    目录MySQL日志管理日志简介错误日志一般查询日志二进制日志binlogbinlog的工作模式statement语句模式row行级模式mixed混合模式查看binlog查看binlog内容和导出事件介绍使用binlog恢复数据案例模拟数据模拟数据故障恢复数据binlog的刷新和删除binlog的刷新删除binlogMySQL慢查......
  • MySQL数据备份
    目录MySQL数据备份binlog存在问题binlog作用为什么要备份备份类型备份方式备份策略（每天一次全备，每小时一次增备）MySQL逻辑备份工具mysqldump所有库备份备份单个库备份时刷新binlog打点备份参数快照备份实用扩展选项额外扩展选项（很好用的）完整备份语句mysqldump的恢复(binlog日志临时......
  • mysql基础
    MySQL基础入门MySQL介绍什么是数据？数据(data)是事实或观察的结果，是对客观事物的逻辑归纳，是用于表示客观事物的未经加工的的原始素材。数据可以是连续的值，比如声音、图像，称为模拟数据。也可以是离散的，如符号、文字，称为数字数据。在计算机系统中，数据以二进制信息单元0,1的形式......
  • mysql三种安装方式
    mysql5.6三种安装方式mysql官网:mysql.com数据库排行目录mysql5.6三种安装方式MySQL安装包下载源码安装mysqlMySQL二进制安装源码安装启动报错报错MySQLyum安装包下载MySQLyum安装MySQL-5.7的版本源码安装二进制安装默认的生成初始密码缺少boost报错版本选择5.6：GA6-1......
  • MySQL用户权限管理
    目录MySQL用户权限管理MySQL用户管理MySQL用户管理：MySQL用户密码管理误删除root用户忘记root密码真正的root权限误删除了所有用户MySQL权限管理企业中权限设置MySQL用户权限管理MySQL用户管理MySQL中的用户不是只看用户名##优化MySQL用户mysql>truncatemysql.user;MySQ......
  • MySQL体系管理结构
    MySQL客户端与服务端模型MySQL是C/S结构的服务MySQL客户端mysqlmysqladminmysqldump图形化SQLyognavicatMySQL服务端mysqldMySQL服务端的连接方式TCP/IP连接mysql-uroot-p123-h10.0.0.51Socket连接mysql-uroot-p123-S/app/mysql/tmp/mysql.sock......
  • .~SQL语句入门
    SQL语句入门目录SQL语句入门MySQL的连接管理MySQL启动关闭流程启动命令关闭命令MySQL实例初始化配置优先级练习初始化配置的作用字符集设置操作系统设置字符集连接工具设置字符集数据库配置设置字符集建库建表级别设置字符集MySQL的SQL语句客户端命令SQL层的SQL语句什么是SQL语句......
  • MySQL客户端命令
    目录MySQL客户端命令优化命令提示符mysqlmysqladminmysqldumpSQL层SQL语句DDL（DatabaseDefinitionLanguage）数据定义语言数据库create增：建库drop删：删库alter改表操作create增：建表数据类型数字类型字符串类型枚举类型浮点型时间戳类型字段属性（约束）drop删alter改DMLinsert增delete......
  • 资源清单编写MySQL，wordpress
    目录mysqlwordpresshttp://k8s.driverzeng.com/v1.19/mysql[root@master-1mysql]#catmysql.yamlapiVersion:"v1"kind:"Pod"metadata:name:mysql57//资源清单叫mysql57spec:nodeName:node-1......
  • MySQL MHA
    MySQLMHA1．什么是MHAMHA（MasterHighAvailability）是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。MHA的出现就是解决MySQL单点的问题。MySQL故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作。MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99