# 基础知识

houdunren.com @ 向军大叔

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

# 测试数据

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

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 ;

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

call add_stus(500000)

# 基础思路

选择合理范围内最小的

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

选择相对简单的数据类型

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

null

为什么这么说呢,因为MYSQL对NULL字段索引优化不佳,增加更多的计算难度,同时在保存与处理NULL类形时,也会做更多的工作,所以从效率上来说,不建议用过多的NULL。有些值他确实有可能没有值,怎么办呢?解决方法是数值弄用整数0,字符串用空来定义默认值即可。

# 字符串

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

保存数值类型最好不要用字符串数据类型,这样存储的空间显然是会更大,而且在排序时字符串的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字段会进行全表扫描

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

image-20200520171913944

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

explain select * from stu order by id ;

image-20200520173401907

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

explain select * from stu where id = 3;

image-20200520172054139

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

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

image-20200520171715351

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

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

image-20200520165838889

# 索引基础

索引就像一本书的目录一样,我们可以通过一本书的目录,快速的找到需要的页面,但是我们也不能过多的创建目录页(索引),原因是如果某一篇文章删除或修改将发变所有页码的顺序,就需要重新创建目录。

select sname from stu where sname="后盾人" 如果sname使用了索引,上面这个例子就会使用到sname索引

索引弊端

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

# 索引类型

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

# 索引维护

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

ALTER TABLE stu ADD INDEX sname_index(sname)

删除索引

ALTER TABLE stu DROP INDEX sname_index

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

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

查看表索引

show index from stu;

# 性能分析

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

普通字段

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

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添加索引

ALTER TABLE stu ADD INDEX class_id(class_id);

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

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>

多表操作

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

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

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

image-20200520135743445

下面来添加索引

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

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

image-20200520135556127

# 字段选择

# 维度思考

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

# 索引规则

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

# 前缀与组合

# 前缀索引

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

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

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

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

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

# 组合索引

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

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

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

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

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

explain select * from stu where class_id =3;

image-20200520152657898

只使用status字段不会走索引

explain select * from stu where status =1

image-20200520153008982

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

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

image-20200520153134786

# 查询优化

# 解析器

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

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

select * from houdunwang where false

image-20200520153543382

# 表过式影响

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

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

image-20200520153808056

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

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

image-20200520154003000

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

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

image-20200520154157528

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

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

image-20200520154422194

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

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

image-20200520154603423

# 类型比较

相同类型比较时走索引

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

image-20200520164917039

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

explain select * from stu where sname=1;

image-20200520165001950

# 排序

排序中尽量使用添加索引的列进行

下例使用数据表进行排序

explain select id from stu order by birthday

image-20200520173943728

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

explain select id from stu order by id ;

image-20200520173913232

# 慢查询

当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 |
+-----------------+-----------+

# 运行配置

会话配置

开启当前会话的慢查询

set session slow_query_log='ON';

设置当前会话慢查询时间为1妙

set session long_query_time=1;

全局配置

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

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

重起MYSQL服务

service mysqld restart