Mysql_13 索引

索引

索引是高效获取数据的数据结构。

Linux中安装Mysql

首先在Linux中安装Mysql。还好前两天接触了一点虚拟机。

Mysql 下载地址

FinalShell可以直接点击上传标志上传下载的压缩包：

mkdir mysql

tar -xvf 压缩包名 -C mysql //解压到该文件夹内

//安装其中的所有rpm文件
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

启动：安装好mysql后，linux会自动提供mysqld服务。

systemctl start mysqld //启动mysql

mysql -uroot -p //然后去日志文件里找密码~

systemctl restart mysqld

systemctl stop mysqld

查找密码可以执行：

grep 'temporary password' /var/log/mysqld.log

也可以直接打开该文件看。登录后就是用熟悉的SQL改密码.

linux密码默认校验规则等级为Medium，要求>8位，包括数字，大小写字母和符号。可以设置强度为：

set global validate_password.policy = 0;
set global validate_password.length = 4;

然后可以设置个简单点的密码：

alter user 'root'@'localhost' identified by '1234';

然后为了DataGrip可以访问，创建一个可以远程访问新用户：

create user 'Windows'@'%' identified with mysql_native_password by '1234';

grant all privileges on *.* to 'Windows'@'%';

可以通过ifconfig查看ip地址。查看后就可以在DataGrip中链接啦。

索引介绍

是帮助Mysql高效获取数据的有序数据结构。

比如查找age=45的记录，如果age采用二叉树的存储方式，很明显右边比左边更高效。

索引使得查询和排序成本降低，但是增删改成本增加，而且索引也要占用空间（MYI文件）。

B树

二叉树比较简单，小数插在左子树，大数插在右子树。

但是如果一直往左或右插，树就变成一边倒的形状了，查询效率大大降低。

红黑树可以解决平衡问题。但是大数据情况下还是层级较深，检索速度较慢。

B树：又名多路平衡查找树。

首先每个节点的子节点个数叫度数。

以最大度数为5的5阶B树为例：

一开始插入4个数以内，都在一个节点里

然后插入第五个数，就开始分裂，中间的数作为父节点，左右两组数作为子节点

然后插入数还是往子节点里插入

如果子节点够5个，中间的提到父节点处。

一直插入到父节点达到5个，父节点再次分裂。

B+树

就是B树的所有叶子节点会形成一个单向链表

Mysql里的B+树就是叶子结点变成了双向循环链表，提高区间访问性能。

Hash 索引

通过一定的Hash算法，把值都归到对应hash值的槽位上。如果出现槽位冲突，采用链表的方式解决。

只能用于=等值查询，不能查询范围(>< between)，不能排序。

效率很高，通常一次查询就找得到。

只有Memory存储引擎支持。但是InnoDB具有自适应hash功能，可以在给定的条件下将B+树构建为hash索引。

索引分类

有主键，主键就是聚集索引；

没有主键，第一个unique是聚集索引；

没有主键和unique唯一索引，则表会自动生成一个rowid作为隐藏的聚集索引。

select ... where name='Arm';会先在二级索引中找到对应节点，然后根据id去聚集索引B+树中找主键，进而找到对应的row行数据。

因此直接select ... where id=10;效率会更高，哪怕name有索引也需要回表查询（回id表）。

创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (col_name,...);
-- 查看表中索引：
SHOW INDEX FROM table_name;
-- 删除索引
DROP INDEX index_name ON table_name;

性能分析

show global status like 'Com_______';查看当前数据库增删改查等操作的频次。如 Com_commit Com_select。

慢查询日志：记录了所有查询时间超过10s（long_query_time）sql语句的日志。查询变量：slow_query_log 可以看是否开启。

默认没有开启，需要到/etc/my.cnf中开启 slow_query_log。

开启后/var/lib/mysql/主机吗名-slow.log中就会生成慢查询日志。

vi /etc/my.cnf

// 编辑，添加：

slow_query_log=1
long_query_time=10

:x//保存退出

systemctl restart mysqld//重启

show variables like 'slow_query_log';//查询是否开启

但是慢查询查询不到少于它规定秒数的语句。

show profiles 可以帮助我们了解每条sql语句耗时。首先通过：have_profiling参数 查看当前数据库是否支持profile操作

select @@have_profiling;

select @@profiling;-- 默认关闭

set profiling=1;

show profiles;-- 查看刚才执行的所有sql耗时

show profile for query 数字序号;-- 查看某一条特定的sql具体查询耗时，从start到cleaning up

show profile cpu for query 数字序号;-- 还可以查看cpu占用情况

然而时间并不是准确的判定性能方法。explain才是更好的方法。explain可以查看select的详细执行状况。在任意select语句前加上explain或 desc 就可以了。

属性含义：

id：select查询的序列号，表示select或操作表的顺序，从大到小，相同id值从上到下。

比如查询学生表、成绩表、学生成绩表 id 都是1，但从上到下顺序是s, sc, c。子查询 id 会更大。

select_type：查询类型。

​ simple：简单类型

​ subquery：子查询

​ primary：主查询，外层查询

​ union：联合的后半部分

type：连接类型，性能由好到坏分别为：null（不查询表，查询常量时）, system（系统表）, const（主键约束查询，唯一性约束查询）, eq_ref（子查询的 const）, ref（非唯一性约束查询）, range, index（用到了索引，但也是全表索引扫描）, all（全表扫描）

尽量往前靠。

possible_key：这张表中可能用到的索引。

key：实际用到的索引。

key_len：使用到的索引中的最大可能字节数。越短越好。

rows：Mysql认为必须要执行查询的行数（估计值）。

filtered：返回的行数/读取的行数百分比，越大越好。比如查主键就不用遍历，查一条就查得出来。

使用索引

通过 create index 给某个属性添加索引。

查询结果结尾加 /G ，可以把列转换为行显示。

使用规则

1. 联合索引的最左前缀法则。比如一个索引要关联多列，那么查询从最左列开始，且不跳过索引中的列。比如我们给 profession age status 三列添加了一个索引，那么查询时就该写：select * from table where profession="" and age="" and status=""; 不写 profession 查询或者只写 profession 和 status 查询会出错，会出现有 null 的列。（好像8.0可以不写最左列）原理大概是复合属性的索引是先按第一个排序，再按第二个，再第三个……所以只按后面的排序查找就出错。当然这三个查询顺序无所谓，关键是左边的一定得有。（这里的左指的是创建索引的时候的顺序，比如 create index index_name on table_name(left_column, right column);）
2. 联合索引中如果出现范围查询（< >），范围查询右侧的属性失效。比如 select * from table where profession="" and age>30 and status=""; status 查询就会失效。解决办法是尽量用 >= <=。
3. 索引列上不要做运算，否则会失效。 比如查询姓名三个字以“松”结尾的学生，姓名有索引，但是 select * from students where substring(name,3,1)="松"; 不走索引。
4. 查询字符串没加单引号也失效。
5. 尾部模糊匹配（“abc%”）不会失效，头部模糊匹配（“%abc”）失效。
6. or 的失效情况：有索引列条件 or 无索引列条件 ，结果是所有索引都不会被用到。处理办法就是右边的属性也建立索引。

总结就是，mysql 评估说全表扫描比索引查找快，那么就决定用全表。比如当前列 null 值比较多，is null 查询就会全表查询，is not null 是索引查询。

SQL 提示

有的列可能用了多个索引。可以人为提示指明用哪一个。

explain select * from table use index(index_name) where 条件;

explain select * from table ignore index(index_name) where 条件;

explain select * from table force index(index_name) where 条件; use 可能不接受。force 是强制使用这个索引。

覆盖索引

查询使用了索引，且查询返回的列都在该索引当中。

覆盖索引效率高于 select *。

如果查询多列，包括有索引列和没索引列，explain 中 extra 显示 using index condition，意为使用了索引查找，但是最后又回表查询非索引列。

如果查询的多列只包含索引列，explain 中 extra 显示 using where; using index 意为只使用了索引查找，因为要查询的列只在索引列中就找得到。

比如上例，如果 select * from table where id=2; 先去聚集索引表中找 id=2 的行，然后回表找到这一行，找到这一行的所有数据。

如果 select id, name from table where name='Arm'; 先去辅助索引表中找到 Arm，辅助索引表中也会存储其 id 信息。这样就已经能查到所有字段了，就不用回表了。

但是如果查询的字段是 id, name, gender ，gender 不在辅助索引表中，因此还是用回表的。先根据 arm 对应的 id 值，去聚集索引表中找到相关的行信息，再回表查询 gender。这就超出了覆盖索引的范围。

例：select id, username, pwd from user where username='jingqing'; 怎样设置索引使得查询最优？

答：id 是主键索引，为了达成覆盖索引，我们要给 username pwd 建立复合索引。

前缀索引

varchar int 等类型可能是很长的字符串，导致索引变得很大，查询时浪费大量磁盘 IO。

创建索引时可以只选择一定长度的前缀建立索引。如：

create index index_name on table_name(column(n)); 表示前n个字符建立前缀。

至于n取多少合适，可以根据索引的选择性决定，选择性=不重复的索引值/记录总数，越大越好，1是最好的唯一索引。（count(distinct column)/count(column)）

我们可以用 substring(column, 1, n) / count(*) 来计算前n个前缀的选择性。

单列索引和联合索引

包含多列的是联合索引。

多个查询条件时推荐使用建立联合索引。因为比如 column a 和 column b 建立了两个单列索引，同时查询两者时 mysql 也只会选择其中一个索引查询，另一个字段回表查询（如果 mysql 自动选择了单列索引，可以用 use index() 限制）。

设计原则

1. 什么时候用索引？数据量比较大，且查询比较频繁的表要建立。
2. 哪几列建立索引？常作为查询条件的列（where, order by, group by)。
3. 建议区分度高的列建立唯一索引。
4. 太长的列可以建立前缀索引。
5. 尽量用联合索引而不是单列索引，避免回表。
6. 索引也会影响维护，因此并不是多多益善。
7. 如果索引列不能存储 null，建表的时候请用 not null 约束该列。当优化器知道每列是否有 null 值时，可以更好的决定用哪个索引查询。