东莞专业网站设计建站公司,WordPress如何添加cnzz,自建网址,wordpress 插件评论区华子目录 索引概述优缺点 索引的原理索引的设计原则索引结构B-tree#xff08;多路平衡查找树#xff09;BtreeHash 为什么InnoDB存储引擎选择Btree#xff1f;索引分类聚集索引选取规则 单列索引和多列索引前缀索引创建索引1.创建表时创建索引2.在已经存在的表上创建索引3.… 华子目录 索引概述优缺点 索引的原理索引的设计原则索引结构B-tree多路平衡查找树BtreeHash 为什么InnoDB存储引擎选择Btree索引分类聚集索引选取规则 单列索引和多列索引前缀索引创建索引1.创建表时创建索引2.在已经存在的表上创建索引3.使用alter table语句创建索引 使用计划查询SQL使用索引情况explain查看索引删除索引案例SQL性能分析查看SQL执行频率慢查询日志profile详情explain执行计划 索引的使用验证索引效率最左前缀法则范围查询索引列运算字符串不加引号模糊查询or连接的条件数据分布的影响SQL提示覆盖索引前缀索引单列索引和联合索引 索引概述
索引index 是帮助MySQL高效获取数据的数据结构有序。在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式指向数据这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引无索引时需要一条一条查找每一条数据有索引时就不需要逐一查询数据在数据库中用来加速对表的查询通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O与表独立存放但不能独立存在必须属于某个表由数据库自动维护表被删除时该表上的索引自动被删除索引的作用类似于书的目录几乎没有一本书没有目录因此几乎没有一张表没有索引。
优缺点 索引的原理
就是把无序的数据变成有序的查询
把创建的索引的列的内容进行排序对排序结果生成倒排表在倒排表内容上拼上数据地址链在查询的时候先拿到倒排表内容再取出数据地址链从而拿到具体数据
索引的设计原则
为了使索引的使用效率更高在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。
选择惟一性索引为经常需要排序、分组和联合操作的字段建立索引为常作为查询条件的字段建立索引限制索引的数目尽量使用数据量少的索引尽量使用前缀来索引删除不再使用或者很少使用的索引 索引结构
MySQL的索引是在存储引擎层实现的不同的存储引擎有不同的结构主要包含以下几种
索引结构描述Btree索引最常见的索引类型大部分引擎都支持Btree索引Hash索引底层数据结构是用哈希表实现的只有精确匹配索引列的查询才有效通常使用较少R-tree空间索引空间索引是MyISAM引擎的一个特殊索引类型主要用于地理空间数据类型通常使用较少Full-text全文索引是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES B-tree多路平衡查找树 Btree Hash 为什么InnoDB存储引擎选择Btree 索引分类
分类含义特点关键字主键索引针对于表中主键创建的索引默认自动创建只能存在一个primary唯一索引避免同一个表中某数据列中的值重复可以有多个unique常规索引快速定位特定数据可以有多个无全文索引全文索引查找的是文本中的关键字而不是比较索引中的值可以有多个fulltext
在InnoDB存储引擎中根据索引的存储形式又可以分为以下两种
分类含义特点聚集索引将数据存储与索引放到了一块索引结构的叶子节点保存了完整的行数据必须存在且只有一个非聚集索引二级索引将数据与索引分开存储索引结构的叶子节点关联的是对应的主键可以存在多个
聚集索引选取规则
如果存在主键主键索引就是聚集索引如果不存在主键将使用第一个唯一(unique)索引作为聚集索引如果没有主键也没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引 第一个执行效率高因为第一个查的是主键索引只查一张表。第二个先查name字段的索引再根据name字段的索引值进行回表查询查两张表。
单列索引和多列索引
MySQL中的单列索引和多列索引都是用于提高数据库查询效率的工具它们有一些不同之处。 单列索引 单列索引是针对表中的单个列创建的索引。它可以加速针对该列的查找、排序和过滤操作。适用于单列条件查询例如SELECT * FROM table WHERE column value;单列索引可以包括在多列查询中但只有第一列索引将被用于加速查找。创建单列索引的语法示例CREATE INDEX index_name ON table_name (column_name); 多列索引 多列索引是针对表中多个列组合而成的索引。它可以加速涉及这些列组合的查询例如联合查询或者多列条件查询。当查询涉及到多个列时多列索引通常比单列索引更有效。多列索引的列顺序非常重要因为只有查询中使用的列的左侧前缀才会被索引所利用。创建多列索引的语法示例CREATE INDEX index_name ON table_name (column1, column2, ...);
前缀索引
在 MySQL 中你可以创建前缀索引来提高查询效率。前缀索引是指只对列值的一部分进行索引而不是整个列值。这在某些情况下可以减少索引的大小并提高查询性能尤其是对于较大的列类型如 TEXT 或 VARCHAR。
要在 MySQL 中创建前缀索引你可以使用以下语法
CREATE INDEX index_name ON table_name (column_name(prefix_length));在这里prefix_length 是你希望索引的列值的前缀长度。以下是一个示例
CREATE TABLE my_table (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100)
);CREATE INDEX idx_name_prefix ON my_table (name(10));在这个示例中我们为 name 列创建了一个前缀长度为 10 的索引。这意味着索引将仅包含 name 列值的前 10 个字符。你可以根据你的需求调整 prefix_length 的值。
请注意使用前缀索引时需要注意选择适当的前缀长度。如果前缀长度太短可能会导致索引失效而如果太长可能会增加索引的大小并降低性能提升效果。因此需要根据你的数据和查询模式来选择合适的前缀长度。
创建索引
创建索引是指在某个表的一列或多列上建立一个索引以便提高对表的访问速度。创建索引有三种方式这三种方式分别是创建表时创建索引、在已经存在的表上创建索引和使用alter table语句来创建索引。
1.创建表时创建索引
创建表的时候可以直接创建索引这种方式最简单、方便。其基本形式如下
mysql create table 表名(- 列名 数据类型 约束,- 列名 数据类型 约束,-------------------- 列名 数据类型 约束,- [unique/fulltext] index 索引名(列名 [asc/desc])- );mysql create table mytable(- id int auto_increment primary key,- name varchar(20),- age int(3),- phone int unique,- unique index index_mytable_phone(phone),#创建唯一索引- index index_mytable_name(name(3)),#创建前缀索引- index index_mytable_age(age)#创建常规索引- );
Query OK, 0 rows affected, 2 warnings (0.07 sec)mysql show index from mytable;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| mytable | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| mytable | 0 | phone | 1 | phone | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| mytable | 0 | index_mytable_phone | 1 | phone | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| mytable | 1 | index_mytable_name | 1 | name | A | 0 | 3 | NULL | YES | BTREE | | | YES | NULL |
| mytable | 1 | index_mytable_age | 1 | age | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------mysql create table mytable(- id int auto_increment primary key,- name varchar(20),- age int(3),- phone int unique,- index index_mytable_name(name(3)),- index index_mytable_age(age)- );
Query OK, 0 rows affected, 1 warning (0.05 sec)mysql show index from mytable;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| mytable | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| mytable | 0 | phone | 1 | phone | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| mytable | 1 | index_mytable_name | 1 | name | A | 0 | 3 | NULL | YES | BTREE | | | YES | NULL |
| mytable | 1 | index_mytable_age | 1 | age | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------注MySQL8会为主键和唯一和外键自动创建索引
2.在已经存在的表上创建索引
前提是该表上无索引需要手动添加索引
mysql create [unique/fulltext] index 索引名 on 表名(列名); #创建单列索引
mysql create [unique/fulltext] index 索引名 on 表名(列名1,列名2...); #创建多列索引 mysql create table mytable(- id int auto_increment primary key,- name varchar(20)- );mysql create unique index index_mytable_name on mytable(name(3));mysql show index from mytable;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| mytable | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| mytable | 0 | index_mytable_name | 1 | name | A | 0 | 3 | NULL | YES | BTREE | | | YES | NULL |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------3.使用alter table语句创建索引
mysql alter table 表名 add [unique/fulltext] index 索引名(列名);mysql create table mytable(- id int auto_increment primary key,- name varchar(20)- );#通过添加索引的方式添加约束
mysql alter table mytable add unique index index_mytable_name(name); mysql show index from mytable;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| mytable | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| mytable | 0 | index_mytable_name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------使用计划查询SQL使用索引情况explain
mysql desc stu;
----------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------------------
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | int | NO | | NULL | |
| classid | int | NO | MUL | NULL | |
----------------------------------------------------------mysql show index from stu;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| stu | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
| stu | 1 | stu_classid_foreign | 1 | classid | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------mysql select * from stu;
---------------------------
| id | name | age | classid |
---------------------------
| 101 | 小天 | 18 | 1001 |
| 102 | 小明 | 20 | 1003 |
| 103 | 小红 | 13 | 1002 |
---------------------------mysql explain select * from stu where id101;
----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | stu | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
----------------------------------------------------------------------------------------------------------mysql explain select * from stu where id103;
----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | stu | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
----------------------------------------------------------------------------------------------------------explain分析结果的含义:id: 每一行的编号select_type: 查询类型。这里是 SIMPLE表示这是一个简单的查询。table这是表的名字。partitions: 所使用的分区如果有type连接操作的类型ALL、index、range、 ref、eq_ref、const、system、NULL从左到右性能从差到好possible_keys可能可以利用的索引的名字Key它显示了MySQL实际使用的索引的名字。如果它为空或NULL则MySQL不使用索引。key_len索引中被使用部分的长度以字节计。ref它显示的是列的名字或单词“const”MySQL将根据这些列来选择行rowsMySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然这里最理想的数字就是1filtered: 表示按表的过滤条件过滤后的结果所占百分比。Extra这里可能出现许多不同的选项其中大多数将对查询产生负面影响查看索引
mysql show index from 表名;删除索引
mysql drop index 索引名 on 表名;案例
按照下列需求完成索引的创建
name字段为姓名字段该字段的值可能会重复为该字段创建索引创建一个常规索引
mysql create index index_stu_name on stu(name);phone手机号字段的值是非空且唯一primary为该字段创建唯一索引创建一个唯一索引
mysql create unique index index_stu_phone on stu(phone);为profession、age、status创建联合索引多个常规索引
mysql create index index_stu_profession_age_status on stu(profession,age,status);为email建立合适的索引来提升查询效率创建一个常规索引
mysql create index index_stu_email on stu(email);SQL性能分析
查看SQL执行频率
MySQL客户端连接成功后通过show [session/global] status命令可以提供服务器状态信息可以查看当前数据库insertupdatedeleteselect的访问频次
mysql show [session/global] status like Com_______;慢查询日志
慢查询日志记录了所有执行时间超过指定时间long_query_time默认10秒 的所有SQL语句的日志。MySQL的慢查询日志默认没有开启需要在MySQL的配置文件/etc/my.cnf中配置如下信息
#开启MySQL慢日志查询开关
slow_query_log1#设置慢日志时间为2秒当SQL语句执行时间超过2秒就会是为慢查询并将该慢查询记录在慢查询日志中
long_query_time2配置完成后重新启动MySQL服务进行测试查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling变量能够看到当前MySQL是否支持profile操作
mysql select have_profiling;默认profiling是关闭的可以通过set语句在session/global级别开启profiling:
mysql set profiling1;执行一系列SQL操作后然后通过如下指令查看SQL执行的耗时情况
#查看每一条SQL的耗时基本情况
mysql show profiles;#查看指定query_id的SQL语句各个阶段的耗时情况在SQL指定过程中的各个阶段
mysql show profile for query_id;#查看指定query_id的SQL语句cpu的使用情况
mysql show profile cpu for query query_id;explain执行计划
explain或desc命令获取MySQL执行select语句的信息包括select语句执行过程中如何连接和连接的顺序。
#直接在select语句之前加上关键字explain或desc
mysql explain/desc select 字段列表 from 表名 where 条件;索引的使用
验证索引效率 最左前缀法则 范围查询 索引列运算 字符串不加引号 模糊查询 or连接的条件 数据分布的影响 SQL提示 覆盖索引 前缀索引 单列索引和联合索引
