【Mysql】优化实战

索引优化实战

慢查询优化

索引和慢查询

• 索引和慢查询

  • 如何判断是否为慢查询？
    MySQL判断一条语句是否为慢查询语句，主要依据SQL语句的执行时间，它把当前语句的执
    行时间跟 long_query_time 参数做比较，如果语句的执行时间 > long_query_time，就会把
    这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s，该参数值可
    以根据自己的业务需要进行调整。
  • 如何判断是否应用了索引？
    SQL语句是否使用了索引，可根据SQL语句执行过程中有没有用到表的索引，可通过 explain
    命令分析查看，检查结果中的 key 值，是否为NULL。

  我们在使用索引时，需要关心索引是否减少了查询扫描的数据行数，如果
  扫描行数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑索引过滤性，过
  滤性好，执行速度才会快。

创建数据库表

##创建student数据表
mysql> create table student( id int primary key auto_increment, name varchar(20), sex char(1), age int)engine=innodb charset=utf8;

##造数据
mysql> insert into student (name, sex, age) values ('zhangsan', 'M', 18);
Query OK, 1 row affected (0.01 sec)

mysql> insert into student (name, sex, age) values ('lisi', 'M', 19);
Query OK, 1 row affected (0.01 sec)

mysql> insert into student (name, sex, age) values ('guniu', 'F', 19);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student (name, sex, age) values ('qiaoqiao', 'F', 20);
Query OK, 1 row affected (0.01 sec)

mysql> insert into student (name, sex, age) values ('memem', 'F', 17);
Query OK, 1 row affected (0.01 sec)

mysql> insert into student (name, sex, age) values ('gugu', 'M', 21);
Query OK, 1 row affected (0.00 sec)

mysql>  insert into student (name, sex, age) values ('xiaoqiang', 'M', 18);
Query OK, 1 row affected (0.04 sec)

mysql>  insert into student (name, sex, age) values ('xiongda', 'M', 18);
Query OK, 1 row affected (0.02 sec)

mysql>  insert into student (name, sex, age) values ('guage', 'M', 18);
Query OK, 1 row affected (0.02 sec)

##重复造数据，使得数据成倍增长
mysql> 
mysql> insert into student (name, sex, age) select name, sex, age from student;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

执行查询语句

通过explain执行计划分析当前sql没有走索引，执行的是全表扫描

mysql> select count(*) from student where age=18 and name like 'zhang%';
+----------+
| count(*) |
+----------+
|     8192 |
+----------+
1 row in set (0.01 sec)


mysql> explain select * from student where age=18 and name like 'zhang%';
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1176872 |     1.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

添加单个索引age

发现查询语句走了索引key ”age_1”，且过滤的记录数量由1176872条下降到588436条；

mysql> create index age_1 on student(age);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> explain select * from student where age=18 and name like 'zhang%';
+----+-------------+---------+------------+------+---------------+-------+---------+-------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key   | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | age_1         | age_1 | 5       | const | 588436 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

	

尝试创建组合索引

可以看到通过组合索引，查询的结果做了进一步优化

mysql>  create index age_name on student(age, name);
Query OK, 0 rows affected (5.66 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> explain select * from student where age=18 and name like 'zhang%';
+----+-------------+---------+------------+------+----------------+----------+---------+-------+--------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys  | key      | key_len | ref   | rows   | filtered | Extra                 |
+----+-------------+---------+------------+------+----------------+----------+---------+-------+--------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | ref  | age_1,age_name | age_name | 5       | const | 294218 |    11.11 | Using index condition |
+----+-------------+---------+------------+------+----------------+----------+---------+-------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

创建虚拟列，生成组合索引

• 根据name的前缀生成虚拟列，并对表进行等值查询
• 直接好处消除了回表查询

mysql>  alter table student add first_name varchar(5) generated always as (left(name,5)),add index(first_name, age);
Query OK, 0 rows affected (5.73 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from student where age=18 and first_name='zhang';
+----+-------------+---------+------------+------+---------------------------+------------+---------+-------------+--------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys             | key        | key_len | ref         | rows   | filtered | Extra |
+----+-------------+---------+------------+------+---------------------------+------------+---------+-------------+--------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | age_1,age_name,first_name | first_name | 23      | const,const | 248126 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------------------+------------+---------+-------------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

慢查询原因总结

• 全表扫描：explain分析type属性all
• 全索引扫描：explain分析type属性index
• 索引过滤性不好：靠索引字段选型、数据量和状态、表设计
• 频繁的回表查询开销：尽量少用select *，使用覆盖索引