Mysql之sql优化基础

一、记录慢SQL
1、查看sql查询相关配置

show variables like '%quer%';

long_query_time：查询超过指定的时间就是慢sql，mysql默认是秒
slow_query_log：保存慢sql到文件的开关，默认不开启
slow_query_log_file：慢sql保存的位置
注意：这些系统变量可以用set指令这只值，但是只在一个会话窗口起作用，重新打开窗口或者终端便会失效
2、使用配置文件设置相关慢sql参数
windows下配置文件的目录为安装目录：C:\ProgramData\MySQL\MySQL Server 8.0\my.ini 在my.ini中设置long_query_time 1 秒，日志文件名字改为my-slow-sql.log
重启mysql服务

3、测试慢sql
查看慢sql日志

创建user_info表，并插入500万条数据
CREATE TABLE `user_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL COMMENT '姓名', `phone` varchar(45) DEFAULT NULL COMMENT '手机号', `id_no` varchar(45) DEFAULT NULL COMMENT '身份证号码', `age` int(11) DEFAULT NULL COMMENT '年龄', `sex` varchar(45) DEFAULT NULL COMMENT '性别', `eduction` varchar(45) DEFAULT NULL COMMENT '教育程度', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


ps：数据全部由程序随机生成
执行慢sql

这个时候就会在慢sql文件中看到记录了

从记录中可以看到查询时间是3.07326，对表上锁0.000108秒，mysql返回了4692行，扫描了500万行
二、explain的使用
可以使用explain在查看一个sql的性能

> id：表示sql执行的优先级，id越大越先执行，在联合查询下，explain+sql会返回多行，每一行对应一个查询
> select_type : 显示每个查询的类型，例如SIMPLE、PRIMARY、UNION、SUBQUERY等
> table : 显示查询是从哪个表
> type ：表示sql的查询方式，代表这sql的查询新能，system > const > eq_ref > ref > ref_or_null >  index_merge > unique_subquery > index_subquery > range > index > all
> possible_keys :此列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用
> key : 此列显示MySQL实际决定使用的键(索引)。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
> key_len：此列显示MySQL决定使用的键长度
> ref：此列列显示使用哪个列或常数与key一起从表中选择行
> rows：此列显示MySQL认为它执行查询时必须检查的行数
> Extra：该列包含MySQL解决查询的详细信息，常见Distinct、Using temporary、Using index、Using where等

三、索引
1、什么是索引？
索引是数据库表中一列或者多列排序的的结构，使用索引可以快速的访问数据库库中的行和列信息，建立索引会占用磁盘物理空间，在innodb中索引的算法是b+tree，是一种多路查找树，所有的非叶子节点只保存关键字已经指向父节点的指针和子节点的指针，叶子节点保存数据以及指向下一个叶子节点的指针，非叶子节点子树指针个数与关键字个数相同
2、索引分类
主键索引（primary）：创建一个表时，如果有设置主键，就会生成主键索引，如果没有主键索引，mysql数据库会生成默认的主键索引，生成索引文件，主键索引只能有一个，保存在树的根节点
唯一索引（unique）：一个表中可以有多个唯一索引，索引的值可以是null，保存树的二阶位置，列的值必须唯一
普通索引（index）：最普通的索引没有任何限制
使用索引需要满足最左匹配原则，在使用联合索引时，搜索数据会从最左边的索引开始，对索引列做操作或者使用not in 、<> 、like、is null 等语句可能导致索引失效。

四、使用索引
1、首先不加索引在500万条数据中根据名字查询一个人

看到需要两秒多，在高并发的情况下，如果在百万级别的并发情况下，更本难以支撑查询
通过exlpain mysql 将执行这句sql采用的方案

2、建立普通索引
对name列加上索引

ALTER TABLE user_info ADD INDEX index_name (name);

添加索引用了21s，添加索引会生成索引文件，重新排序每一行在物理磁盘上位置，所以在设计表的时候就应该建立好索引
执行同样的sql，结果如下
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190228111330348.png这时候就非常快了，0.0001秒的时间都没到，再次使用explain

可以看到mysql查询解决方案是通过索引来查询的，并且将where条件中值作为常量作为参数，在B+tree中进行搜索。
接下来对phone字段加上普通索引，对id_no加上唯一索引

这里对两列分开建立索引，并不是联合索引
使用name和phone去查询一个人

此时可以看到使用的查询关键字是index_name，也就意味着phone的索引没有生效，Using where 表示需要使用where后条件过滤，也就是phone过滤

3、联合索引
删除之前的单列索引，建立联合索引

查看表索引信息 show index from user_info

可以看到索引顺序为name > phone，此时相当于创建了index(name),index(name,phone)两个索引
再次使用explain查看相同的sql

4、索引失效
索引优化器会采用最左匹配原则，比如有索引a->b->c 如果a失效，则b和c的索引都会无效。
例如

模糊查询导致索引失效
5、常见sql实践
（1）负向条件查询不能使用索引
select * from order where status!=0 and stauts!=1
可以优化为：
select * from order where status in(2,3)
（2）区分度不大的数据列不应该建立索引
比如：性别，状态，颜色，因为区分度不大，过滤不了太多的数据
（3）在属性上进行计算不能命中索引
例如：select * from order where year(date) > ‘2018’，即使在date字段上建立索引，也会进行全表扫描，可优化为select * from order where date > ‘2017-12-30’
(4) 对于一些变化不大，可以枚举的字段使用枚举，尽可能的不要存储字符串使用tinyint，因为字符串占用的空间较大，效率且低
(5)当我们确定只有一条数据返回时，可以使用limit 1 ，当查找发现一条数据后就不会再往下去查找

6、索引的缺点
(1)创建索引和维护索引需要消耗时间，随着数据量的增大而增大
(2)索引还需要占用物理空间，没创建一个索引就会多占用一定的物理空间
(3)对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度