数据库---索引优化

nnzanna

于 2021-03-05 15:04:34 发布

阅读量144

点赞数

分类专栏：数据库文章标签：索引优化索引失效索引执行分析

本文链接：https://blog.csdn.net/nn1__2970/article/details/102256088

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

SQL及索引优化

索引的执行分析；

索引失效；

SQL和索引的优化；

explain 关键字是分析SQL索引执行过程；

操作Student表

mysql> select * from student;
+-----+-------+------+------+
| SID | Sname | Sage | Ssex |
+-----+-------+------+------+
|   1 | 赵雷  |   20 |    1 |
|   2 | 欠点  |   20 |    1 |
|   3 | 孙风  |   21 |    1 |
|   4 | 乌兰  |   18 |    0 |
|   5 | 孙兰  |   17 |    0 |
+-----+-------+------+------+

索引的执行和分析：

（1）单表查询索引执行过程分析及优化：

普通查询：
 mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| SID   | int(11)     | NO   | PRI | NULL    |       |
| Sname | varchar(10) | NO   |     | NULL    |       |
| Sage  | int(11)     | NO   |     | NULL    |       |
| Ssex  | tinyint(1)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

 select * from student where SID = 3;
+-----+-------+------+------+
| SID | Sname | Sage | Ssex |
+-----+-------+------+------+
|   3 | 孙风  |   21 |    1 |
+-----+-------+------+------+
1 row in set (0.00 sec)

 explain select * from student where SID = 3\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
//只查询了一行就找到，因为使用了PRIMARY主键索引（InNoDB会自动给主键字段创建主键索引树），当我们用id作为where子句的过滤条件，首先从主键B+索引树上快速找到id，INNODB采用聚集索引（索引关键字和数据在一起存放），因为找到id，就找到这一行的数据，不用把整个表扫描一遍。

 select * from Student where Sage = 18;
+-----+-------+------+------+
| SID | Sname | Sage | Ssex |
+-----+-------+------+------+
|   4 | 乌兰  |   18 |    0 |
+-----+-------+------+------+
1 row in set (0.05 sec)

//之前设置的idx_sage索引，所以INNODB辅助索引树上叶子结点存储的是辅助索引字段值和对应该行记录的主键，（这里应该是保存了Sage，SID）；

//下面这个SQL语句的执行过程是：先查询了Sage字段的辅助索引B+树,找到Sage=18 的结点，获取对应行的主键SID ,在通过主键id在主键索引树上拿到数据。

//整个过程一共搜索了两次B+树；
mysql> explain select * from Student where Sage = 18\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
   partitions: NULL
         type: ref
possible_keys: idx_sage
          key: idx_sage
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

explain select SID from Student where Sage = 18\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
   partitions: NULL
         type: ref
possible_keys: idx_sage
          key: idx_sage
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
//通过Sage对应的辅助索引B+ 树的叶子结点存储的辅助索引字段值和当前行的主键是（Sage，SID），可以直接拿到当前行对应的SID，获取到数据，不用查询两个树；

//通过Sage查询Ssex，
//SQL的执行过程：先找到Sage对应的辅助索引B+树，Sage= 18的对应主键SID，在通过查询主键的索引B+ 树，找到对应的Ssex
mysql> explain select Ssex from Student where Sage = 18\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
   partitions: NULL
         type: ref
possible_keys: idx_sage
          key: idx_sage
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

//====>优化：
//创建联合索引，alter table Student add index index_sage_sex(Sage,Ssex);
//SQL执行过程：通过Sage=18直接查询辅助索引树，找到Ssex，就不用去在查找主键索引树，此时辅助索引树叶子结点存储的是Sage，Ssex，SID（辅助索引字段值和对应所在行的主键），性能提高，注意的是：Sage作为查询条件，联合查询索引是要放在前面（Sage，Ssex）；
mysql> explain select Ssex from Student where Sage = 18\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
   partitions: NULL
         type: ref
possible_keys: idx_sage,index_sage_sex
          key: idx_sage
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

（2）单表查询索引的执行过程以及优化  ————————》普通查询+排序、分组
//插入data类型数据：insert into orderlist values（1,1，”2018-08-31 18:31:09“）；

select *  from orderlist;
+--------+-----------+---------------------+
| userid | productid | data                |
+--------+-----------+---------------------+
|      1 |         1 | 2018-08-31 10:52:46 |
|      2 |         1 | 2018-08-30 15:32:56 |
|      2 |         5 | 2018-07-30 18:12:56 |
|      1 |         2 | 2018-07-25 21:38:21 |
|      1 |         3 | 2018-07-28 13:31:24 |
|      1 |         9 | 2018-07-29 17:30:20 |
+--------+-----------+---------------------+

mysql> select *  from orderlist where userid = 1 order by data;
+--------+-----------+---------------------+
| userid | productid | data                |
+--------+-----------+---------------------+
|      1 |         2 | 2018-07-25 21:38:21 |
|      1 |         3 | 2018-07-28 13:31:24 |
|      1 |         9 | 2018-07-29 17:30:20 |
|      1 |         1 | 2018-08-31 10:52:46 |
+--------+-----------+---------------------+
4 rows in set (0.30 sec)
//可以看到SQL语句是查询id号为1的用户的购物信息，按日期升序排序；
mysql> explain select *  from orderlist where userid = 1 order by data\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orderlist
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

//分析SQL语句，是进行了整表查询，效率相当低，由于查询userid = 1所有行记录后，还要按照data字段升序排序，所有出现了Using filesort，涉及文件排序，效率相当低，一般遇到Using filesort一定要进行优化！！

//====>优化
//给 data字段和userid分别创建索引；查看区别：
create index index_idx_userid on orderlist(userid);
 create index index_idx_data on orderlist(data);
//执行分析SQL
 explain select *  from orderlist where userid = 1 order by data\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orderlist
   partitions: NULL
         type: ref
possible_keys: index_idx_userid
          key: index_idx_userid
      key_len: 4
          ref: const
         rows: 4
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.03 sec)
//用到了索引，但只用到了index_idx_userid，还是出现了Using filesort
//SQL语句的执行流程：从查询userid的辅助索引B+ 树，找到所在当前行对应的主键id，拿到主键，到主键索引树去搜索数据，拿到之后需要根据data字段文件排序，效率还会很低！

//对userid和data创建联合索引， create index userid_data on orderlist(userid,data);
//命中userid_data 索引，根据userid=1查询辅助索引树，找到的数据也已经按照data排序好了，然后去主键索引树上去拿去数据就OK，效率提高。
//force index（userid_data）:强制索引，MYSQL在5.6版本后加入的新特性，SQL优化器在索引存在的情况下，通过符合RANGE范围的条件和总数的比例来选择是使用索引还是进行全表遍历，MYSQL server认为使用索引还不如整表查询来的快，他就不会使用索引了，强制索引的作用是指定一定会使用指定的索引。
//防止SQL优化器优化索引的使用。
mysql> explain select *  from orderlist force index(userid_data) where userid = 1 order by data\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orderlist
   partitions: NULL
         type: ref
possible_keys: userid_data
          key: userid_data
      key_len: 4
          ref: const
         rows: 4
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)


（3）多表查询---连接查询索引的执行过程以及优化

一个SQL查询一张表，一次只能使用一个索引，所以使用了userid的索引data的索引就使用不到了；

索引的失效：

1.like后面的通配符在前面：

eg:

select * from Student where Sname like “F%”;

select * from Student where Sname like “%F”;(用不到索引)

mysql> select * from Student;
+-----+-------+------+------+
| SID | Sname | Sage | Ssex |
+-----+-------+------+------+
|   1 | 赵雷  |   20 |    1 |
|   2 | 欠点  |   20 |    1 |
|   3 | 孙风  |   21 |    1 |
|   4 | 乌兰  |   18 |    0 |
|   5 | 孙兰  |   17 |    0 |
+-----+-------+------+------+
5 rows in set (0.00 sec)

mysql> select * from Student where Sname like "孙%";
+-----+-------+------+------+
| SID | Sname | Sage | Ssex |
+-----+-------+------+------+
|   3 | 孙风  |   21 |    1 |
|   5 | 孙兰  |   17 |    0 |
+-----+-------+------+------+
 create index index_sname on Student(Sname);
Query OK, 0 rows affected (0.76 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from Student where Sname like "孙%"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
   partitions: NULL
         type: range
possible_keys: index_sname
          key: index_sname
      key_len: 42
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.03 sec)

//没有命中索引
mysql> explain select * from Student where Sname like "%兰"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

如何定位哪些SQL操作效率低，优化？？？

（1）慢查询日志：

当SQL执行的时间超过我们设定的时间，这些SQL会被记录在慢查询日志当中，我们可以通过查询日志，用explain分析这些SQL的执行过程，来判断效率低下的原因，是不是没有使用到索引，还是索引本身的创建问题？或者是索引使用到了，数量太大了，花费时间久长了（将大小分为多个小表）；

mysql> show variables like "long%";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
//超过10秒的SQL就会被记录在慢查询日志中
可以自己设置时间 set long_query_time=XX；
慢查询日志：MYSQL\MYSQL Server\Data   host_name-slow.log

（2）查看磁盘I/O读写的数据量；

MYSQL数据库是磁盘存储，任务管理器–查看–进程—选择列；

优化的总结：

对查询进行优化，尽量避免全表扫描，首相要考虑where和order by涉及列上建立索引；
会失效的SQL查询；
避免where子句中使用or来连接，否则将导致MYSQL放弃使用索引而今次那个全表扫描；

nnzanna

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库---索引优化

0628 SQL及索引优化索引的执行分析；索引失效；SQL和索引的优化；explain 关键字是分析SQL索引执行过程；操作Student表mysql> select * from student;+-----+-------+------+------+| SID | Sname | Sage | Ssex |+-----+-------+------+----...
复制链接

扫一扫

专栏目录