mysql基础3索引

存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是

基于库的，所以存储引擎也可被称为表类型。

1). 建表时指定存储引擎

CREATE TABLE 表名(字段1 字段1类型 [ COMMENT 字段1注释 ] ,......字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;   -- ENGINE = MyISAM 、Memoryshow create table emp;
show engines;  -- | InnoDB   | DEFAULT |

InnoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的MySQL 存储引擎。

2). 特点 ：事务、外键、行级锁
DML操作遵循ACID模型，支持事务；
行级锁，提高并发访问性能；
支持外键FOREIGN KEY约束，保证数据的完整性和正确性；
3). 文件
xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的 、sdi-新版的）、数据和索引。
show variables like 'innodb_file_per_table';  -- 每张表对应一个ibd文件mysql提供的一个指令 ibd2sdi ，通过该指令就可以从ibd文件中提取sdi信息，而sdi数据字典信息中就包含该表的表结构。

在这里插入图片描述

-- MyISAM
1). 介绍: MyISAM是MySQL早期的默认存储引擎。
2). 特点不支持事务，不支持外键支持表锁，不支持行锁访问速度快
3). 文件xxx.sdi：存储表结构信息json格式xxx.MYD: 存储数据xxx.MYI: 存储索引-- Memory
1). 介绍：Memory引擎的表数据时存储在内存中的，一般将这些表作为临时表或缓存使用。
2). 特点：内存存放，hash索引（默认）
3). 文件：xxx.sdi：存储表结构信息

1

InnoDB引擎与MyISAM引擎的区别 ?
①. InnoDB引擎, 支持事务, 而MyISAM不支持。
②. InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁。
③. InnoDB引擎, 支持外键, 而MyISAM是不支持的。

InnoDB:

是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

MyISAM ：

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。mongodb可替代

MEMORY：

将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。redis可替代

索引概述

索引（index）是帮助MySQL高效获取数据的数据结构。

优势 提高数据检索的效率，降低数据库的IO成本通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
劣势索引列也是要占用空间的。索引大大提高了查询效率，同时却也降低更新表的速度，效率降低。

索引结构

B+Tree索引: 最常见、常用的索引类型，大部分引擎都支持 B+ 树索引
Hash索：底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询，memory支持
R-tree(空间索引）空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引) 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。
红黑树也是一颗二叉树，所以也会存在一个缺点：大数据量情况下，层级较深，检索速度慢。
B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5个指针： 树的度数指的是一个节点的子节点个数。我们可以通过一个数据结构可视化的网站来简单演示一下。 https://www.cs.usfca.edu/~gall
es/visualization/BTree.htmlB+Tree
B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，B+Tree 与 B-Tree相比，主要有以下三点区别：所有的数据都会出现在叶子节点。叶子节点形成一个单向链表。非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点
的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。MySQL中除了支持B+Tree索引，还支持一种索引类型---Hash索引。
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。
特点A. Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）B. 无法利用索引完成排序操作C. 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

为什么InnoDB存储引擎选择使用B+tree索引结构?
A. 相对于二叉树，层级更少，搜索效率高；
B. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
C. 相对Hash索引，B+tree支持范围匹配及排序操作；

索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：

分类含义特点关键字
主键索引针对于表中主键创建的索引默认自动创建, 只能有一个PRIMARY
唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE
常规索引快速定位特定数据可以有多个
全文索引全文索引查找的是文本中的关键词，而不是比较索引中的值可以有多个FULLTEXT

而在在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引(ClusteredIndex)将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据必须有,而且只有一个二级索引(SecondaryIndex)将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键可以存在多个聚集索引选取规则:如果存在主键，主键索引就是聚集索引。如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

在这里插入图片描述
当我们执行 select * from user where name =‘Arm’; 的SQL语句时

①. 由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。

②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。

③. 最终拿到这一行的数据，直接返回即可。

这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

以下两条SQL语句，那个执行效率高? 为什么?
A. select * from user where id = 10 ;
B. select * from user where name = 'Arm' ; -- 备注: id为主键，name字段创建的有索引；A 语句的执行性能要高于B 语句。
因为A语句直接走聚集索引，直接返回数据。 而B语句需要先查询name字段的二级索引，
然后再查询聚集索引，也就是需要进行回表查询。

InnoDB主键索引的B+tree高度为多高呢?
假设:
一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。高度为2：n * 8 + (n + 1) * 6 = 16*1024 , 算出n约为 11701171* 16 = 18736也就是说，如果树的高度为2，则可以存储 18000 多条记录。高度为3：1171 * 1171 * 16 = 21939856也就是说，如果树的高度为3，则可以存储 2200w 左右的记录。

-- 创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;-- ... 一个所有是可以关联多个字段的，联合（组合）索引
-- 查看索引
SHOW INDEX FROM table_name ; 1
-- 删除索引
DROP INDEX index_name ON table_name ;

数据准备

create table tb_user(id int primary key auto_increment comment '主键',name varchar(50) not null comment '用户名',phone varchar(11) not null comment '手机号',email varchar(100) comment '邮箱',profession varchar(11) comment '专业',age tinyint unsigned comment '年龄',gender char(1) comment '性别 , 1: 男, 2: 女',status char(1) comment '状态',createtime datetime comment '创建时间'
) comment '系统用户表';INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00');
INSERT INTO db.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');

添加索引

-- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
CREATE INDEX idx_user_name ON tb_user(name);-- phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);-- 为profession、age、status创建联合索引。
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);-- 为email建立合适的索引来提升查询效率。
CREATE INDEX idx_email ON tb_user(email);-- 查看索引
show index from tb_user;   -- 默认BTREE-- DROP INDEX idx_email ON tb_user ;  -- 删除索引

SQL性能分析

SQL执行频率查看

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

MySQL [db]> SHOW GLOBAL STATUS LIKE 'Com_______';    -- 判断插入为主，还是查询为主
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_insert    | 83    |	-- 插入
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 171   |	-- 查询
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log

MySQL [db]> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+-- （/etc/my.cnf）中配置慢日志
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

profile耗时详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

SELECT @@have_profiling ;    -- YES  支持
SELECT @@profiling;		-- 默认为0  关闭状态
SET profiling = 1;		-- 开启--  执行几个查询观察
select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';-- 查看每一条SQL的耗时基本情况
show profiles;
MySQL [db]> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration   | Query                                       |
+----------+------------+---------------------------------------------+
|        1 | 0.00024150 | SELECT @@profiling                          |
|        2 | 0.00042825 | select * from tb_user                       |
|        3 | 0.00045500 | select * from tb_user where id = 1          |  -- id更快
|        4 | 0.00073600 | select * from tb_user where name = '白起'   |
+----------+------------+---------------------------------------------+-- 查看指定query_id的SQL语句各个阶段的耗时情况
-- show profile for query query_id;
MySQL [db]> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000089 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000007 |
| init                 | 0.000015 |
| optimizing           | 0.000009 |
| executing            | 0.000013 |  -- 执行耗时
| end                  | 0.000007 |
| query end            | 0.000007 |
| closing tables       | 0.000006 |
| freeing items        | 0.000065 |
| cleaning up          | 0.000016 |
+----------------------+----------+-- 查看指定query_id的SQL语句CPU的使用情况
-- show profile cpu for query query_id;
MySQL [db]> show profile cpu for query 1;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000089 | 0.000019 |   0.000053 |
| checking permissions | 0.000009 | 0.000002 |   0.000006 |
| Opening tables       | 0.000007 | 0.000002 |   0.000005 |
| init                 | 0.000015 | 0.000004 |   0.000011 |
| optimizing           | 0.000009 | 0.000003 |   0.000007 |
| executing            | 0.000013 | 0.000003 |   0.000009 |
| end                  | 0.000007 | 0.000002 |   0.000005 |
| query end            | 0.000007 | 0.000002 |   0.000005 |
| closing tables       | 0.000006 | 0.000001 |   0.000004 |
| freeing items        | 0.000065 | 0.000018 |   0.000047 |
| cleaning up          | 0.000016 | 0.000004 |   0.000012 |
+----------------------+----------+----------+------------+

explain

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;MySQL [db]> explain select * from tb_user where id = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+--

其中 type | possible_keys | key | key_len |Extra 有一定的关联性

id
select查询的序列号，表示查询中执行select子句或者是操作表的顺序。
当查询为多表查询时，会有多个id。id相同，执行顺序从上到下；id不同，值越大，越先执行

MySQL [db]> select s.*,c.* from student s,course c,student_course sc where s.id=sc.studentid and c.id=sc.courseid;
+----+-----------+------------+----+--------+
| id | name      | no         | id | name   |
+----+-----------+------------+----+--------+
|  1 | 黛绮丝    | 2000100101 |  1 | Java   |
|  1 | 黛绮丝    | 2000100101 |  2 | PHP    |
|  1 | 黛绮丝    | 2000100101 |  3 | MySQL  |
|  2 | 谢逊      | 2000100102 |  2 | PHP    |
|  2 | 谢逊      | 2000100102 |  3 | MySQL  |
|  3 | 殷天正    | 2000100103 |  4 | Hadoop |
+----+-----------+------------+----+--------+
6 rows in set (0.00 sec)MySQL [db]> explain select s.*,c.* from student s,course c,student_course sc where s.id=sc.studentid and c.id=sc.courseid;
+----+-------------+-------+------------+--------+--------------------------+---------+---------+-----------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys            | key     | key_len | ref             | rows | filtered | Extra                                              |
+----+-------------+-------+------------+--------+--------------------------+---------+---------+-----------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL    | PRIMARY                  | NULL    | NULL    | NULL            |    4 |   100.00 | NULL                                               |
|  1 | SIMPLE      | sc    | NULL       | ALL    | fk_courseid,fk_studentid | NULL    | NULL    | NULL            |    6 |    25.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | s     | NULL       | eq_ref | PRIMARY                  | PRIMARY | 4       | db.sc.studentid |    1 |   100.00 | NULL                                               |
+----+-------------+-------+------------+--------+--------------------------+---------+---------+-----------------+------+----------+----------------------------------------------------+
3

select id from course c where c.name ='MySQL';
select studentid from student_course sc where sc.courseid = 3;
select * from student s where s.id in(1,2);select * from student s where s.id in(select studentid from student_course sc where sc.courseid = (select id from course c where c.name ='MySQL'));MySQL [db]> explain select * from student s where s.id in(select studentid from student_course sc where sc.courseid = (select id from course c where c.name ='MySQL'));
+----+--------------+-------------+------------+--------+--------------------------+-------------+---------+---------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys            | key         | key_len | ref     | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+--------------------------+-------------+---------+---------+------+----------+-------------+
|  1 | PRIMARY      | s           | NULL       | ALL    | PRIMARY                  | NULL        | NULL    | NULL    |    4 |   100.00 | Using where |
|  1 | PRIMARY      | <subquery2> | NULL       | eq_ref | <auto_key>               | <auto_key>  | 4       | db.s.id |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | sc          | NULL       | ref    | fk_courseid,fk_studentid | fk_courseid | 4       | const   |    2 |   100.00 | Using where |
|  3 | SUBQUERY     | c           | NULL       | ALL    | NULL                     | NULL        | NULL    | NULL    |    4 |    25.00 | Using where |
+----+--------------+-------------+------------+--------+--------------------------+-------------+---------+---------+------+----------+-------------+-- 执行顺序：c > sc > <subquery2>(第二个子查询)  >  s

select_type 参考意义不大

表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等

type 重要指标

表示连接类型，性能由好到差：NULL、system、const、eq_ref、ref、range、 index、all 。

-- NULL，一般不访问表的时候才会出现，如： expLAin select "A";
-- system  访问系统表
-- const   使用主键或唯一索引时出现
MySQL [db]> explain select * from tb_user where phone ='17799990014';
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | const | idx_user_phone | idx_user_phone | 46      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
-- eq_ref
-- ref  非唯一性索引
explain select * from tb_user where name='白起';
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_name | idx_user_name | 202     | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

possible_key 显示可能应用在这张表上的索引，一个或多个。

key 实际使用的索引，如果为NULL，则没有使用索引。

key_len 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。

rows MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个预估值，可能并不总是准确的。

filtered 表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

extra 额外信息

索引使用

验证索引效率

在讲解索引的使用原则之前，先通过一个简单的案例，来验证一下索引，看看是否能够通过索引来提升
数据查询性能。在演示的时候，我们还是使用之前准备的一张表 tb_sku , 在这张表中准备了1000w
的记录。

select * from tb_sku where id =1 ;	-- 1 row in set (0.02 sec)
select sn from tb_sku where id =1 ;	-- 100000003145001
select * from tb_sku where sn="100000003145001" ;  -- 1 row in set (32.53 sec)show index from  tb_sku;	-- sn上无索引
create index idx_sku_sn on tb_sku(sn);	-- 创建索引
select * from tb_sku where sn="100000003145001" ; 	-- 1 row in set (0.00 sec)MySQL [db]> explain select * from tb_sku where sn="100000003145001" \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tb_skupartitions: NULLtype: ref
possible_keys: idx_sku_sn	-- 可能使用的索引key: idx_sku_sn	-- 刚创建的索引，实际使用的索引key_len: 402ref: constrows: 1filtered: 100.00Extra: NULL
1 row in set, 1 warning (0.02 sec)

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。以 tb_user 表为例，我们先来查看一下之前 tb_user 表所创建的索引。

show index from tb_user;
-- idx_user_pro_age_sta  依次  1 | profession，2 | age，3 | status-- 执行以下三个查询，发现  possible_keys与 key均为 索引idx_user_pro_age_sta
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select * from tb_user where profession = '软件工程' and age = 31;
explain select * from tb_user where profession = '软件工程';
我们发现只要联合索引最左边的字段 profession存在，索引就会生效，只不过索引的长度不同。而且由以上三组测试，
我们也可以推测出profession字段索引长度为47、age字段索引长度为2、status字段索引长度为5。--  当跳过 age 查询status时，索引长度为47，从跳过处往后索引失效
explain select * from tb_user where profession = '软件工程' and status = '0';-- 执行以下两个个查询，发现 type：ALL， possible_keys与 key均为 NULL
explain select * from tb_user where age = 31 and status = '0';
explain select * from tb_user where status = '0';
索引并未生效，原因是因为不满足最左前缀法则，联合索引最左边的列profession不存在-- 将profession放置最后发现，索引长度54，这跟你所放的位置无关，但是必须有，才能使用索引
explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程';

范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

-- key_len 54 ，>右侧status索引失效
explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';-- key_len 57，使用>= 或 <= 时，走联合索引，所有的字段都是走索引
explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';-- 所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <

索引失效情况

索引列运算

不要在索引列上进行运算操作， 索引将失效。

-- 当根据phone字段进行等值匹配查询时, 索引生效。
explain select * from tb_user where phone = '17799990015';-- 当根据phone字段进行string函数运算操作之后，索引失效。  type：ALL
explain select * from tb_user where substring(phone,10,2) = '15';
字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

-- 查看字符串类型的字段
desc tb_user; --  | status     | char(1)    -- status的字段值加'',走索引 idx_user_pro_age_sta， key_len 54
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';-- status的字段值不加'',走索引 idx_user_pro_age_sta， key_len 49
explain select * from tb_user where profession = '软件工程' and age = 31 and status = 0;explain select * from tb_user where phone = '17799990015'; -- key idx_user_phone
explain select * from tb_user where phone = 17799990015;  -- type：ALL key：NULL
模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

-- key_len 47
explain select * from tb_user where profession like '软件%';-- key_len NULL
explain select * from tb_user where profession like '%工程';-- key_len NULL
explain select * from tb_user where profession like '%工%';
or连接条件

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

-- id 主键索引  age无索引（有联合索引），  key： NULL
explain select * from tb_user where id = 10 or age = 23;-- phone 有索引  age无索引，  key： NULL
explain select * from tb_user where phone = '17799990017' or age = 23;-- 创建索引，重新执行  key：PRIMARY,idx_user_age
create index idx_user_age on tb_user(age);
数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。
is null 、is not null是否走索引，得具体情况具体分析，并不是固定的

-- >=17799990001 几乎是全表的数据了，扫全表，不走索引，  key： NULL
explain select * from tb_user where phone >= '17799990001';-- >= '17799990020' 只有4个，走索引 key：idx_user_phone 
explain select * from tb_user where phone >= '17799990020';-- 都有数据，非null  走索引快
explain select * from tb_user where profession is null;-- 清空数据后，都null  走索引
explain select * from tb_user where profession is null;
SQL提示

SQL提示，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

drop index idx_user_age on tb_user;
drop index idx_email on tb_user;

1

-- 在profession存在联合索引的情况下，创建单个字段索引
create index idx_user_pro on tb_user(profession);-- possible_keys: idx_user_pro_age_sta,idx_user_pro  
-- 自动选择的key: idx_user_pro_age_sta
explain select * from tb_user where profession = '软件工程';-- use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';-- ignore index ： 忽略指定的索引。
explain select * from tb_user ignore index(idx_user_pro_age_sta) where profession = '软件工程';-- force index ： 强制使用索引。
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';
覆盖索引

尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？ 覆盖索引是指 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。

drop index idx_user_age on tb_user;
drop index idx_email on tb_user;
drop index idx_user_pro on tb_user;MySQL [db]> show index from tb_user;
| Key_name             | Seq_in_index | Column_name |
| PRIMARY              |            1 | id          |
| idx_user_phone       |            1 | phone       |
| idx_user_name        |            1 | name        |
| idx_user_pro_age_sta |            1 | profession  |
| idx_user_pro_age_sta |            2 | age         |
| idx_user_pro_age_sta |            3 | status      |
| idx_user_pro         |            1 | profession  |

1

-- Extra: Using index condition / NULL。key: idx_user_pro_age_sta
explain select * from tb_user where profession = '软件工程' and age = 31 and status= '0';-- Extra: Using where; Using index / Using indexkey: idx_user_pro_age_sta
explain select id,profession from tb_user where profession = '软件工程' and age = 31 and status = '0' ;
explain select id,profession,age from tb_user where profession = '软件工程' and age = 31 and status = '0' ;
explain select id,profession,age, status from tb_user where profession = '软件工程'and age = 31 and status = '0' ;-- Extra: Using index condition / NULL。key: idx_user_pro_age_sta
explain select id,profession,age, status, name from tb_user where profession = '软件工程' and age = 31 and status = '0' ;--  Extra    mysql8有这个，mysql 5.7好像没有,这里是： Using index，NULL  两种
Using where; Using index   性能高于 Using index condition
Using where; Using index  查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
Using index condition	查找使用了索引，但是需要回表查询，加载数据
前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

create index idx_xxxx on table_name(column(n)) ;

select * from tb_user;
select count(*) from tb_user;
select count(email) from tb_user;
select count(distinct email) from tb_user;
select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ; -- 5-9都是这个值
+-------------------------------------------------+
| count(distinct substring(email,1,5)) / count(*) |
+-------------------------------------------------+
|                                          0.9583 |
+-------------------------------------------------+--  创建索引  截取前缀五个字段
create index idx_email_5 on tb_user(email(5));
explain select * from tb_user where email = 'daqiao666@sina.com';  -- key：idx_email_5
单列索引与联合索引

单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。

--  possible_keys idx_user_phone,idx_user_name
--  key:idx_user_phone   ,只走了一个索引，会回表查询影响效率
--  key_len 46
explain select  id,phone,name from tb_user where phone = '17799990010' and name ='韩信';-- 创建 唯一联合索引
create unique index idx_user_phone_name on tb_user(phone,name);--  possible_keys idx_user_phone,idx_user_phone_name,idx_user_name
--  key:idx_user_phone   mysql 自选的
--  key_len 46
--  Extra: NULL   回表查询
explain select  id,phone,name from tb_user where phone = '17799990010' and name ='韩信';--  possible_keys,key : idx_user_phone_name
--  key_len: 248
--  Extra: Using index
explain select  id,phone,name from tb_user use index(idx_user_phone_name) where phone = '17799990010' and name ='韩信';

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

索引设计原则

1). 针对于数据量较大，且查询比较频繁的表建立索引。
2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

自选的
– key_len 46
– Extra: NULL 回表查询
explain select id,phone,name from tb_user where phone = ‘17799990010’ and name =‘韩信’;

– possible_keys,key : idx_user_phone_name
– key_len: 248
– Extra: Using index
explain select id,phone,name from tb_user use index(idx_user_phone_name) where phone = ‘17799990010’ and name =‘韩信’;


在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。### 索引设计原则```mysql
1). 针对于数据量较大，且查询比较频繁的表建立索引。
2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/285404.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

Docker进阶：Docker-compose 实现服务弹性伸缩

Docker进阶：Docker-compose 实现服务弹性伸缩

Docker进阶&#xff1a;Docker-compose 实现服务弹性伸缩 一、Docker Compose基础概念1.1 Docker Compose简介1.2 Docker Compose文件结构 二、弹性伸缩的原理和实现步骤2.1 弹性伸缩原理2.2 实现步骤 三、技术实践案例3.1 场景描述3.2 配置Docker Compose文件3.3 使用 docker-…
阅读更多...
【项目管理后台】Vue3+Ts+Sass实战框架搭建二

【项目管理后台】Vue3+Ts+Sass实战框架搭建二

Vue3TsSass搭建 git cz的配置mock 数据配置viteMockServe 建立mock/user.ts文件夹测试一下mock是否配置成功 axios二次封装解决env报错问题&#xff0c;ImportMeta”上不存在属性“env” 统一管理相关接口新建api/index.js 路由的配置建立router/index.ts将路由进行集中封装&am…
阅读更多...
AIGC实战——Transformer模型

AIGC实战——Transformer模型

AIGC实战——Transformer模型 0. 前言1. T52. GPT-3 和 GPT-43. ChatGPT小结系列链接 0. 前言 我们在 GPT (Generative Pre-trained Transformer) 一节所构建的 GPT 模型是一个解码器 Transformer&#xff0c;它逐字符地生成文本字符串&#xff0c;并使用因果掩码只关注输入字…
阅读更多...
海外媒体宣发：十大国外中文网站-大舍传媒

海外媒体宣发：十大国外中文网站-大舍传媒

十大国外中文网站 1、欧洲时报 覆盖欧洲且较具影响力的华文媒体 国外中文新闻网站&#xff0c;欧洲时报文化传媒集团旗舰日报《欧洲时报》旗下官方网站&#xff0c;总部设在法国巴黎&#xff0c;创刊于1983年&#xff0c;现已成为唯一发行覆盖全欧、发行量最大、最具影响力的华…
阅读更多...
Vue3 上手笔记

Vue3 上手笔记

1. Vue3简介 2020年9月18日&#xff0c;Vue.js发布版3.0版本&#xff0c;代号&#xff1a;One Piece&#xff08;n 经历了&#xff1a;4800次提交、40个RFC、600次PR、300贡献者 官方发版地址&#xff1a;Release v3.0.0 One Piece vuejs/core 截止2023年10月&#xff0c;最…
阅读更多...
STM32最小核心板使用HAL库ADC读取MCU温度(使用DMA通道)

STM32最小核心板使用HAL库ADC读取MCU温度(使用DMA通道)

STM32自带CPU的温度数据&#xff0c;需要使用ADC去读取。因此在MX创建项目时如图配置&#xff1a; 模块初始化代码如下&#xff1a; void MX_ADC1_Init(void) {/* USER CODE BEGIN ADC1_Init 0 *//* USER CODE END ADC1_Init 0 */ADC_ChannelConfTypeDef sConfig {0};/* USER…
阅读更多...
Docker 入门使用说明

Docker 入门使用说明

Docker 入门使用说明 Docker 安装 Docker 官网&#xff1a;Docker Docker 安装说明&#xff1a;Docker 安装说明 这里由于 Docker 在实时更新&#xff0c;所以每次安装 Docker 用来导入 key 的链接可能会有变化&#xff0c;这里就参考官方的安装方法即可 Docker 常用命令说…
阅读更多...
（ES6）前端八股文修炼Day2

（ES6）前端八股文修炼Day2

1. let const var 的区别 var&#xff1a; var 是在 ES5 中引入的声明变量的关键字。 具有函数作用域&#xff0c;而不是块作用域&#xff0c;这意味着使用 var 声明的变量在函数内部是可见的。 变量可以被重复声明&#xff0c;而且变量的值可以在声明前使用&#xff0c;这可能…
阅读更多...
生成微信小程序二维码

生成微信小程序二维码

首页 -> 统计 可以通过上面二个地方配置&#xff0c;生成小程序的二维码&#xff0c;并且在推广分析里&#xff0c;有详细的分析数据&#xff0c;
阅读更多...
Vue3更新Package.json版本号

Vue3更新Package.json版本号

由于我之前已经更新过了&#xff0c;下面的方法提示我已经是最新的了&#xff0c;记录一下&#xff0c;过段时间在测试一下 npm install -g vue/clivue upgrade
阅读更多...
【Hadoop大数据技术】——Hadoop高可用集群（学习笔记）

【Hadoop大数据技术】——Hadoop高可用集群（学习笔记）

&#x1f4d6; 前言&#xff1a;Hadoop设计之初&#xff0c;在架构设计和应用性能方面存在很多不如人意的地方&#xff0c;如HDFS和YARN集群的主节点只能有一个&#xff0c;如果主节点宕机无法使用&#xff0c;那么将导致HDFS或YARN集群无法使用&#xff0c;针对上述问题&#…
阅读更多...
项目1-加法计算器

项目1-加法计算器

1.创建项目 2.导入前端代码 2.1 static包内 2.2 测试前端代码是否有误 显示成功说明无误 2.3 定义用户接口 请求路径&#xff1a;calc/sum 请求方式&#xff1a;GET/POST 接口描述&#xff1a;计算两个整数相加 请求参数: 参数名类型是否必须备注num1Integer是参与计算的第…
阅读更多...
YiYi-Web项目介绍

YiYi-Web项目介绍

YiYi-Web项目介绍 1. 简介2. 使用2.1 后端开发环境2.2 前端开发环境 3. 测试环境&#xff1a;4. 更新日志5. 打包情况6.项目截图 本项目前端是html、css、js、jQuery基础技术。 后端都是最新的SpringBoot技术&#xff0c;不分离版本&#xff0c; 是最基础的项目开发教程&#x…
阅读更多...
【C语言】linux内核pci_alloc_irq_vectors

【C语言】linux内核pci_alloc_irq_vectors

一、注释 代码中包含了几个关于PCI&#xff08;外围组件互联&#xff09;设备中断请求&#xff08;IRQ&#xff09;向量分配的函数&#xff0c;以及内联函数声明&#xff0c;下面是对这些函数的中文注释&#xff1a; static inline int pci_alloc_irq_vectors_affinity(struc…
阅读更多...
Java安全 反序列化(3) CC1链-TransformedMap版

Java安全 反序列化(3) CC1链-TransformedMap版

Java安全 反序列化(3) CC1链-TransformedMap版 本文尝试从CC1的挖掘思路出发&#xff0c;理解CC1的实现原理 文章目录 Java安全 反序列化(3) CC1链-TransformedMap版配置jdk版本和源代码配置前记 为什么可以利用一.CC链中的命令执行我们可以尝试一下通过InvokerTransformer.tr…
阅读更多...
曲线生成 | 图解Reeds-Shepp曲线生成原理(附ROS C++/Python/Matlab仿真)

曲线生成 | 图解Reeds-Shepp曲线生成原理(附ROS C++/Python/Matlab仿真)

目录 0 专栏介绍1 什么是Reeds-Shepp曲线&#xff1f;2 Reeds-Shepp曲线的运动模式3 Reeds-Shepp曲线算法原理3.1 坐标变换3.2 时间翻转(time-flip)3.3 反射变换(reflect)3.4 后向变换(backwards) 4 仿真实现4.1 ROS C实现4.2 Python实现4.3 Matlab实现 0 专栏介绍 &#x1f5…
阅读更多...
如何使用PHP和RabbitMQ实现消息队列？

如何使用PHP和RabbitMQ实现消息队列？

前言 今天我们来做个小试验&#xff0c;用PHP和RabbitMQ实现消息队列功能。 前期准备&#xff0c;需要安装好docker、docker-compose的运行环境。 如何使用docker部署php服务_php如何使用docker发布-CSDN博客 一、安装RabbitMQ 1、创建相关目录&#xff0c;执行如下命令。…
阅读更多...
吴恩达2022机器学习专项课程(一） 3.3 成本函数的公式

吴恩达2022机器学习专项课程(一） 3.3 成本函数的公式

问题预览 模型的参数&#xff08;w和b&#xff09;有什么作用&#xff1f;不同的w和b对线性回归模型有什么影响&#xff1f;训练集里的y和线性回归模型预测的y&#xff08;y帽&#xff09;的区别是什么&#xff1f;成本函数的作用是什么&#xff1f;成本函数的公式是什么&…
阅读更多...
neo4j所有关系只显示RELATION，而不显示具体的关系

neo4j所有关系只显示RELATION，而不显示具体的关系

当看r时&#xff0c;真正的关系在properties中的type里&#xff0c;而type为“RELATION” 造成这个的原因是&#xff1a; 在创建关系时&#xff0c;需要指定关系的类型&#xff0c;这是固定的&#xff0c;不能像属性那样从CSV文件的一个字段动态赋值。标准的Cypher查询语言不支…
阅读更多...
【 Mysql8.0 忘记登录密码 可以试试 】

【 Mysql8.0 忘记登录密码 可以试试 】

** Mysql8.0 忘记登录密码 可以试试 ** 2024-3-21 段子手168 1、首先停止 mysql 服务 &#xff0c;WIN R 打开运行&#xff0c;输入 services.msc 回车打开服务&#xff0c;找到 mysql 服务&#xff0c;停止。 然后 WIN R 打开运行&#xff0c;输入 CMD 打开控制台终端输…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023