3.2 索引
3.2.1 Linux安装mysql
本次使用的是MySQL版本是:mysql-8.0.26
下载地址:https://downloads.mysql.com/archives/community/
解压安装:
# 解压tar文件
cd /opt/mysql8.0
mkdir mysql
tar -xvf mysql-8.0.26-1.el7.x86_64.rpm-bundle.tar -C ./mysql
# 安装mysql的安装包
cd mysql
rpm -ivh mysql-community-common-8.0.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.26-1.el7.x86_64.rpm
yum install openssl-devel
rpm -ivh mysql-community-devel-8.0.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.26-1.el7.x86_64.rpm
如果执行报依赖检测失败,在命令后面加
--nodeps --force
启动和关闭命令:
# 启动
systemctl start mysqld
# 重启
systemctl restart mysqld
# 关闭
systemctl stop mysqld
密码设置:
# 查询自动生成的root用户密码
grep 'temporary password' /var/log/mysqld.log
# 输入密码登录
mysql -u root -p
# 修改root用户密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
#执行上述的SQL会报错,原因是因为设置的密码太简单,密码复杂度不够,可以修改密码校验策略,降低密码校验
set global validate_password.policy = 0;
set global validate_password.length = 6;
创建用户:
# 默认的root用户只能当前节点localhost访问,是无法远程访问的,我们还需要创建一个root账户,用户远程访问
create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
# 并给root用户分配权限
grant all on *.* to 'root'@'%';
# 重新连接MySQL
mysql -u root -p 密码
3.2.2 索引概述
-
介绍:索引(index)是帮助mysql高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构一模中方式引用指向数据,这样就可以在这些数据结构上实现高级查询算法。
-
-
索引优缺点
优:提高检索效率,降低数据库IO成本,通过索引列对数据进行排序,降低数据排序成本,降低cpu消耗
缺:索引需要占空间;索引降低了查询速度,同时降低了表的更新速度,如:insert、update、delete。
3.2.3 索引的数据结构
索引是在数据库的存储引擎层实现的,不同的表可以使用不同的引擎从而使用不同的索引。
索引结构 | 描述 |
---|---|
B+Tree 索引 | 最常见的索引类型,大部分引擎都支持B+树索引 |
Hash 索引 | 底层数据结构使用hash表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
R-tree(空间索引) | 空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,使用少 |
Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式,类似于Lucene,Solr,ES |
索引 | InnoDB | MyISAM | Memory |
---|---|---|---|
B+Tree | 支持 | 支持 | 支持 |
Hash | 不支持 | 不支持 | 支持 |
R-Tree | 不支持 | 支持 | 不支持 |
Full-text | 5.6版本后支持 | 支持 | 不支持 |
平常说的索引没有特殊说明都是B+数结构索引
3.2.4 B+Tree索引
二叉树:顺序插入时,会形成一个链表,查询性能降低;大数据情况,层级较深,检索速度慢
红黑树:大数据情况,层级较深,检索速度慢
3.2.5 Hash索引
特点:
- hash索引只能用于对等比较(=,in),不支持范围查询(between,>,<,…)
- 无法利用索引完成排序
- 查询效率高,通常只需要一次检索就可以,效率通常高于B+Tree索引
- 支持hash索引的只有Memory引擎;在InnoDB引擎中,具有自适应hash功能,hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的
为什么InnoDB存储引擎选择使用B+Tree索引结构
- 相对于二叉树,层数更少,搜索效率更高
- 对于B-Tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能通过增加树的高度,导致性能降低,而B+Tree非叶子节点只保存索引不保存数据,这样就可以有效降低树的高度;
- 再者,B+Tree相对与B-Tree和Hash,B+Tree只需要在叶子节点去查找数据,而且叶子节点是通过类似于双向链表连接的,对于范围查询,效率高。而B+Tree则需要去遍历树,才能范围查询。Hash则不支持范围查询和排序。
3.2.6 索引分类
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引 | 针对表中主键创建的索引 | 默认自动创建,只能有一个 | primary |
唯一索引 | 避免同一表中某数据列中的值重复 | 可以多个 | union |
常规索引 | 快速定位特定数据 | 可以多个 | |
全文索引 | 全文检索查询的是文本中的关键词,而不是比较索引中的值 | 可以多个 | fulltext |
在InnoDB存储引擎中,根据索引的存储形式,可分为两种:
分类 | 含义 | 特点 |
---|---|---|
聚集索引(Clustered Index) | 将数据存储与索引放到了一起,索引结构的叶子节点保存了行数据 | 有且只有一个 |
二级索引/非聚集索引(Secondary Index) | 将数据和索引分开存储,索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引的选取规则:
- 如果存在主键,那么主键索引就是聚集索引
- 如果不存在主键,将使用第一个唯一(union)索引作为聚集索引
- 如果没有主键或合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引
**聚集索引和二级索引的结构:**聚集索引叶子节点存储了索引和该行的数据;而非聚集索引(二级索引)的叶子节点存储了索引字段和行号。
**回表查询:**如果该表中的name列创建了索引,执行下面的SQL语句,会先去name创建的二级索引去查询定位到叶子节点,由于查询返回的结果是 * 也就是所有列,二级索引叶子节点存储了该行的行号,也就是10,拿到行号10后再次回表查询,去聚集索引查询行号为10的列数据信息并返回。
InnoDB主键索引的B+Tree高度:
假设一行数据为1k,一页占16k,一页中可以存储16条这样的数据,InnoDB的指针占用6个字节,若主键使用bigint,占用8个字节。
设:根节点有n个索引,那么指针数为n+1,则可以得到:
n*8+(n+1)*6=16*1024 => n=1170
根节点可以存放1170个索引值。可以存放1170+1个指针,那么高度为2的树就可以存放:
1171*16=18736
条数据。
那么若树的高度为3,则可以存放:
1171*1171*16=21939856
大约两千多万行数据。
3.2.7 索引语法
-- 创建索引,一个索引可以关联多个字段,关联一列称为:单列索引。关联多列称为:联合索引/组合索引
create [unique|fulltext] index index_name on table_name (index_col_name,...);
-- 查询索引
show index from table_name;
-- 删除索引
drop index index_name on table_name;
3.2.8 SQL性能分析
-
SQL的执行频率
MySQL客户端连接成功后,通过 show [session|global] status 命令可以提供服务器状态信息,通过如下指令,可以查看当前数据库的 insert、update、delete、select的访问频次:
mysql> show global status like 'Com_______'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_binlog | 0 | | Com_commit | 0 | | Com_delete | 0 | | Com_import | 0 | | Com_insert | 0 | | Com_repair | 0 | | Com_revoke | 0 | | Com_select | 127 | | Com_signal | 0 | | Com_update | 0 | | Com_xa_end | 0 | +---------------+-------+ 11 rows in set (0.00 sec)
-
慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10s)的所有SQL语句的日志
MySQL的慢查询日志默认是关闭的,要开启需要在MySQL的配置文件 /etc/my.cnf 中配置如下信息:
mysql> show variables like 'slow_query_log'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | OFF | +----------------+-------+ 1 row in set (0.01 sec)
#开启MySQL慢查询日志 slow_query_log=1 #设置慢查询日志的时间为2s,超过两秒,就会被记录 long_query_time=2
配置完成后,通过以下命令重新启动MySQL服务,查看慢查询日志文件中记录的信息 /var/lib/mysql/localhost-slow.log
-
profile详情
show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数,能够看到当前MySQL是否支持profile操作。若支持,profile默认是关闭的,需要通过指令打开
-- 查看是否支持profile select @@have_profiling; -- 打开profile set [session|global] profiling=1;
执行完SQL操作后,通过如下命令查看指令的耗时
#查看每一条sql的耗时情况 show profiles; #查看指定query_id的SQL语句的各个执行阶段的耗时情况 show profile for query query_id; #查看指定query_id的SQL语句cpu的使用情况 show profile cpu for query query_id;
事例:
mysql> show profiles; +----------+------------+--------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------------------------------------------+ | 1 | 0.00041050 | select @@profiling | | 2 | 0.00022675 | select count(*) from tb_sku | | 3 | 0.00018150 | SELECT DATABASE() | | 4 | 0.00283925 | show databases | | 5 | 0.00183500 | show tables | | 6 | 0.02890050 | select count(*) from tb_sku | | 7 | 0.05248775 | select count(*) from tb_sku where name like '%手机%' | +----------+------------+--------------------------------------------------------+ 7 rows in set, 1 warning (0.00 sec) mysql> show profile for query 7; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000071 | | Executing hook on transaction | 0.000003 | | starting | 0.000008 | | checking permissions | 0.000006 | | Opening tables | 0.000037 | | init | 0.000005 | | System lock | 0.000012 | | optimizing | 0.000009 | | statistics | 0.000019 | | preparing | 0.000081 | | executing | 0.052135 | | end | 0.000019 | | query end | 0.000005 | | waiting for handler commit | 0.000009 | | closing tables | 0.000010 | | freeing items | 0.000035 | | cleaning up | 0.000027 | +--------------------------------+----------+ 17 rows in set, 1 warning (0.00 sec) mysql> show profile cpu for query 7; +--------------------------------+----------+----------+------------+ | Status | Duration | CPU_user | CPU_system | +--------------------------------+----------+----------+------------+ | starting | 0.000071 | 0.000060 | 0.000000 | | Executing hook on transaction | 0.000003 | 0.000002 | 0.000000 | | starting | 0.000008 | 0.000008 | 0.000000 | | checking permissions | 0.000006 | 0.000005 | 0.000000 | | Opening tables | 0.000037 | 0.000034 | 0.000000 | | init | 0.000005 | 0.000004 | 0.000000 | | System lock | 0.000012 | 0.000011 | 0.000000 | | optimizing | 0.000009 | 0.000008 | 0.000000 | | statistics | 0.000019 | 0.000018 | 0.000000 | | preparing | 0.000081 | 0.000076 | 0.000000 | | executing | 0.052135 | 0.048325 | 0.000000 | | end | 0.000019 | 0.000009 | 0.000000 | | query end | 0.000005 | 0.000004 | 0.000000 | | waiting for handler commit | 0.000009 | 0.000008 | 0.000000 | | closing tables | 0.000010 | 0.000009 | 0.000000 | | freeing items | 0.000035 | 0.000033 | 0.000000 | | cleaning up | 0.000027 | 0.000024 | 0.000000 | +--------------------------------+----------+----------+------------+ 17 rows in set, 1 warning (0.00 sec)
-
explain执行计划
explain 或者 desc命令获取MySQL如何执行select语句信息,包括在select语句执行过程中表如何连接和连接的顺序
-- 直接在select语句之前加上explain/desc explain select 字段列表 from 表名 where 条件;
各字段含义:
- id:select查询的序列号,表示查询中执行select子句或者是操作表(多表查询)的顺序(id相同,执行顺序从上到下,id不同越大,越先执行)
- select_type(了解):表示select的类型,常见的取值有simple(简单表,即不使用表连接和子查询)、primary(主查询,即外层查询)、union(union中的第二个或者后面的查询语句)、subquery(select/where之后包含了子查询)等
- type(重要):表示连接类型,性能由好到差为:null(如select ‘A’)、system(一般是系统表)、const(如:唯一索引)、eq_ref、ref(如:非唯一索引)、range、index、all。
- possible_keys:显示可能应用在这张表上的索引,一个或多个。
- key:实际使用的索引,如果为null,则没有使用索引。
- key_len:表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精度性的前提下,长度越短越好。
- rows(了解):MySQL认为必须要执行查询的行数,在InnoDB引擎的表中,是一个估计值,可能并不总是准确的。
- filtered:查询返回的行数占需读取行数的百分比,filtered越大越好。
- extra:额外的信息。
3.2.9 索引使用
-
最左前缀法则
如果索引是多列(联合索引),要遵守最左前缀法则,最左前缀法则指查询从索引的最左列开始,并且不跳过索引中的列,如果跳过了某一列,则后面的索引将会失效。这里的最左和SQL语句中放的位置无关。
-
范围查询
联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效,尽量使用 <= 或 >=。
-
索引列运算
不要在索引列上进行运算操作,否则索引会失效。
-
字符串不加单引号
字符串类型的字段使用时,不加单引号,索引会失效。
-
模糊查询
如果仅仅是尾部模糊匹配,索引不会失效,如果是头部模糊匹配,索引会失效。
-
or 连接的条件
用 or 分隔开的条件,如果 or 前的条件中的列有索引,而后面的列没有索引,那么涉及的索引都不会被用到。要想使用到索引就要 or 两边都要有索引。
-
数据分布影响
如果MySQL评估使用索引比全表扫描更慢,则不使用索引。
-
SQL提示
在SQL语句中加入一些人为的提示来达到优化操作的目的
-- user index 建议使用哪个索引 explain select * from tb_user use index(idx_user_name) where name ='xxx'; -- ignore index 忽略哪个索引 explain select * from tb_user ignore index(idx_user_name) where name ='xxx'; -- force index 强制使用哪个索引 explain select * from tb_user force index(idx_user_name) where name = 'xxx';
-
覆盖索引
尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中能够全部找到),减少select *,避免回表查询
using index condition:查找使用了索引,但是需要回表查询数据
using where;using index:查找时使用了索引,但是需要的数据都在索引列中能找到,不需要回表查询
-
前缀索引
当字段类型为字符串(char、varchar等)时,有时候需要索引很长的字符串,这让索引变得很大,查询时,浪费大量的磁盘IO,影响查询效率,此时可以只将字符串的一部分前缀,建立索引,这样可以大大节省索引占用空间,从而提高效率
-- n 表示 前n个字符构建的索引
create index idx_xxx on table_name(column(n));
前缀长度:可以根据索引的选择来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择,性能也是最好的。
-- 计算参考
select count(distinct email)/count(*) from tb_user;
select count(distinct substring(email,1,5))/count(*) from tb_user;
-
单列索引和联合索引
单列索引:一个索引只包含单个列
联合索引:一个索引包含了多个列
在业务场景中,如果存在多个查询条件,考虑针对字段建立索引时,建议建立联合索引
联合索引结构:
3.2.10 索引设计原则
- 针对数据量较大,切查询比较频繁的表建立索引(超过百万)
- 针对常作为查询条件where、排序order by、分组group by 操作的字段建立索引
- 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高
- 如果是字符串类型的字段,字段长度较长,可针对字段特点,建立索引
- 尽量使用联合索引,减少单列索引,查询时,联合索引可以覆盖索引,节省存储空间,避免回表查询,提高查询效率
- 要控制索引的数量,索引越多,维护索引结构的代价也就越大,会影响增删改效率
- 如果索引列不能存储null值,请在创建表时用not null约束,当优化器知道每列是否包含null值时,他可以很好的确定哪个索引最有效的用于查询。