MYSQL(十一)

2023-04-06 Thursday

服务器

mysql学习笔记整理-索引优化

索引优化

基础知识

合理设置索引会对数据库性能带来很大提升。

测试数据

为了演示索引需要添加些测试数据，下面是添加学生数据的储存过程代码。如果你已经有测试数据可以省掉这一步。

DELIMITER $$
CREATE PROCEDURE add_stus(IN num int)
BEGIN
DECLARE i int DEFAULT 0;
DECLARE _birthday datetime;
WHILE num>i DO
    SET _birthday  = date_sub(now(),INTERVAL floor(RAND()*1000) day);
    INSERT INTO stu SET
    sname = concat(left(md5(RAND()),5),'后盾人向军'),
    class_id =1+FLOOR( RAND()*100000),
    birthday = _birthday ,
    sex = 1+FLOOR(RAND()*2 );

    SET i= i+1;
END WHILE;
END
$$
DELIMITER ;

调用储存过程添加五十万条记录

1	call add_stus(500000)

基础思路

选择合理范围内最小的

我们应该选择最小的数据范围，因为这样可以大大减少磁盘空间及磁盘 I/0 读写开销，减少内存占用，减少 CPU 的占用率。

选择相对简单的数据类型

数字类型相对字符串类型要简单的多，尤其是在比较运算时，所以我们应该选择最简单的数据类型，比如说在保存时间时，因为 PHP 可以良好的处理 LINUX 时间戳所以我们可以将日期存为 int(10)要方便、合适、快速的多。

字符串

字符串数据类型是一个万能数据类型，可以储存数值、字符串等。

保存数值类型最好不要用字符串数据类型，这样存储的空间显然是会更大，而且在排序时字符串的 9 是大于 22 的。如果进行运算时 mysql 会将字符串转换为数值类型，这种转换是不会走索引的。

如果明确数据在一个完整的集合中如男，女，那么可以使用 set 或 enum 数据类型，这种数据类型在运算及储存时以数值方式操作，所以效率要比字符串更好，同时空间占用更少。

数值类型

整数

整数类型很多比如 tinyint、int、smallint、bigint 等，那么我们要根据自己需要存储的数据长度决定使用的类型，同时 tinyint(10)与 tinyint(100)在储存与计算上并无任何差别，区别只是显示层面上，但是我们也要选择适合合适的数据类型长度。可以通过指定 zerofill 属性查看显示时区别。

浮点数与精度数值

浮点数 float 与 double 在储存空间及运行效率上要优于精度数值类型 decimal，但 float 与 double 会有舍入错误而 decimal 则可以提供更加准确的小数级精确运算不会有错误产生计算更精确，适用于金融类型数据的存储。

总结
数值数据类型要比字符串执行更快，范围区间小的数据类型占用空间更少，处理速度更快，如 tinyint 可比 bigint 要快的多。选择数据类型时要考虑内容长度，比如是保存毫米单位还是米而选择不同的数值类型。

EXPLAIN

EXPLAIN 指令可以帮助开发人员分析 SQL 问题，explain 显示了 mysql 如何使用索引来处理 select 语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句。
字段说明

字段	说明	备注
id	索引执行顺序
select_type	查询类型	simple:基本查询 union result:union 的结果
table	操作表
type	使用类型	const: 前面表匹配唯一行检索速度快，如果使用主键值比较 ref: 前面表中的非唯一数据 eq_ref:前面表中非唯一数据，使用了唯一索引字段，如表关联时使用主键 range:索引区间获得，如使用 IN(1,2,3)筛选 all:全表遍历 index:与 all 类似只是扫描所有表，而非数据表
possible_keys	可能用到的索引，不一定被真正使用
key	最终使用的索引
key_len	索引字节数
ref	列与索引的比较	const 为常量比较
rows	预计读出的记录条数
Extra	查询说明

使用没有添加索引的 birthday 字段会进行全表扫描

1	explain select * from stu where birthday = '19900231';

使用索引表排序所以 type 为 index

1	explain select * from stu order by id ;

使用主键查询会使用 const 类型

1	explain select * from stu where id = 3;

使用 IN 查询后使用了区间 range 索引

1	explain select * from stu where class_id in(1,2,3);

下面是多表关联使用索引的情况

1	explain select * from stu s inner join class c on s.class_id = c.id where sname = '后盾人'

索引基础

索引就像一本书的目录一样，我们可以通过一本书的目录，快速的找到需要的页面，但是我们也不能过多的创建目录页（索引），原因是如果某一篇文章删除或修改将发变所有页码的顺序，就需要重新创建目录。
select sname from stu where sname="后盾人" 如果 sname 使用了索引，上面这个例子就会使用到 sname 索引
索引弊端

创建索引会使查询操作变得更加快速，但是会降低增加、删除、更新操作的速度，因为执行这些操作的同时会对索引文件进行重新排序或更新
创建过多列的索引会大大增加磁盘空间开销
不要盲目的创建索引，只为查询操作频繁的列创建索引

索引类型

索引	说明
UNIQUE 唯一索引	不可以出现相同的值，可以有 NULL 值
INDEX 普通索引	允许出现相同的索引内容
PRIMARY KEY 主键索引	不允许出现相同的值，且不能为 NULL 值

索引维护

为 stu 学生表的 sname 字段设置索引

1	ALTER TABLE stu ADD INDEX sname_index(sname)

删除索引

1	ALTER TABLE stu DROP INDEX sname_index

删除主键索引，首先需要移除 auto_increment 然后删除主键索引

1 2	ALTER TABLE stu MODIFY id int; ALTER TABLE stu DROP PRIMARY KEY

查看表索引

1	show index from stu;

性能分析

索引是加快查询操作的重要手段，如果当发生查询过慢时添加上索引后会发现速度大大改观

普通字段

当没有地表 class_id 字段添加索引时，查找 cid 为 3 的记录会执行全表扫描，性能是最差的

1	EXPLAIN SELECT * FROM stu WHERE class_id =5 LIMIT 1;

通过结果的 type=ALL 可以看到执行了全表扫描，遍历了一百万条记录

id            | 1
select_type   | SIMPLE
table         | stu
partitions    | <null>
type          | ALL
possible_keys | <null>
key           | <null>
key_len       | <null>
ref           | <null>
rows          | 1000000
filtered      | 10.0
Extra         | Using where

索引字段

下面来为 class_id 添加索引

1	ALTER TABLE stu ADD INDEX class_id(class_id);

再次执行查询来看添加索引后的效果

1	EXPLAIN SELECT * FROM stu WHERE class_id =5 LIMIT 1;

通过查看 type 字段看到已经走了索引，本次查询遍历了 16 条记录

id            | 1
select_type   | SIMPLE
table         | stu
partitions    | <null>
type          | ref
possible_keys | class_id
key           | class_id
key_len       | 5
ref           | const
rows          | 16
filtered      | 100.0
Extra         | <null>

多表操作

在使用连接操作多个表时，如果没有添加索引性能会非常差。

1	explain select * from a join b on a.id=b.id join c on b.id=c.id

结果中会看到每张表都遍历了所有记录

下面来添加索引

1
2
3

ALTER TABLE a ADD INDEX id(id);
ALTER TABLE b ADD INDEX id(id);
ALTER TABLE c ADD INDEX id(id);

执行的结果会看到使用了索引，并且并没有进行全表遍历

字段选择

维度思考

数据列中不重复值出现的个数，维度的最大值是数据行的数量
如数据表中存在 8 行数据 a ,b ,c,d,a,b,c,d 这个表的维度为 4
要为维度高的列创建索引
性别这样的列不适合创建索引，因为维度过低

索引规则

对 where，on 或 group by 及 order by 中出现的列使用索引
对较小的数据列使用索引，这样会使索引文件更小，同时内存中也可以装载更多的索引键
为较长的字符串使用前缀索引
不要过多创建索引，除了增加额外的磁盘空间外，对于 DML 操作的速度影响很大

前缀与组合

前缀索引

大使用 text/长 varchar 字段时创建索引，会造成索引列长度过长，从而生成过大的索引文件影响检索性能。使用前缀索引方式进行索引，可以有效解决这个问题。前缀索引应该控制在一个合适的点，控制在 0.31 黄金值即可。

下面是取前缀索引的计算公式，有时也根据字段保存内容确定，比如标题 100 可以取 30 个字符为索引

1	select count(distinct(left(title,10)))/count(*) from news

下面为文章表 article 的 title 字段添加 30 个长度的前缀索引

1	ALTER TABLE article ADD INDEX title(title(30));

组合索引

组合索引为是多个字段统一设计索引

可以较为每个字段设置索引文件体积更小
使用速度优于多个索引操作
前面字段没出现，只出现后面字段时不走索引

下面为学生表中的班级字段 class_id 与学生状态 status 设置组合索引

1	Alter table stu add index class_id_status(class_id,status);

使用 class_id 时会走索引，因为 class_id 在组合索引最前面

1	explain select * from stu where class_id =3;

只使用 status 字段不会走索引

1	explain select * from stu where status =1

当 class_id 与 status 字段一起使用时会走索引

1	explain select * from stu where status =1 and class_id=5;

查询优化

解析器

Mysql 的解析器非常智能，会对发出的每条 SQL 进行分析，决定是否使用索引或是否进行全表扫描。

下面发送的 SQL 解析器分析后已经清楚不会有任何语句符合操作，所以不会操作任何表或索引

1	select * from houdunwang where false

表达式影响

下面 SQL 语句不会使用索引，因为所有索引列参与了计算

1	explain select * from stu where status+1=1;

下面 SQL 不会使用索引，因为使用了函数运算，原理与上面相同

1	explain select * from stu where left(sname,1)='后盾人'

下面 SQL 不会使用索引，因为索引列是模糊匹配的

1	explain select * from stu where sname like '%向军大叔%'

下面 SQL 会使用索引，因为不是模糊匹配

1	explain select * from stu where sname like '后盾人%'

正则表达式也不会使用索引

1	explain select * from stu where sname regexp '^后盾人'

类型比较

相同类型比较时走索引

1	explain select * from stu where sname="1";

字符串类型使用数值时不走索引

1	explain select * from stu where sname=1;

排序

排序中尽量使用添加索引的列进行
下例使用数据表进行排序

1	explain select id from stu order by birthday

排序字段为索引列后使用索引表排序

1	explain select id from stu order by id ;

慢查询

当 Mysql 性能下降时，通过开启慢查询来获得哪条 SQL 语句造成的响应过慢进行分析处理。当然开启慢查询会带来 CPU 损耗与日志记录的 IO 开销，所以我们要间断性的打开慢查询日志来查看 Mysql 运行状态。

慢查询能记录下所有执行超过 long_query_time 时间的 SQL 语句, 用于找到执行慢的 SQL, 方便我们对这些 SQL 进行优化。

状态查看

查看开启慢查询状态

show variables like 'slow_query%';

+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | OFF                               |
| slow_query_log_file | /var/lib/mysql/homestead-slow.log |
+---------------------+-----------------------------------+

查看慢查询设置的时间

show variables like "long_query_time"

+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

运行配置

会话配置

通过以下指令开启全局慢查询（如果重起 Mysql 后需要重新执行）

1	set global slow_query_log='ON';

设置慢查询时间为 1 秒，即超过 1 秒将会被记录到慢查询日志

1	set session long_query_time=1;

全局配置
通过修改配置 mysql 配置文件 my.cnf 来开启全局慢查询配置，在配置文件中修改以下内容

1
2
3

slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

重起 MYSQL 服务

1	service mysqld restart

MYSQL笔记

MYSQL(十二) MYSQL(十)