数据库-索引的优化

最新推荐文章于 2023-06-12 18:15:54 发布

Fly_Fly_Zhang

最新推荐文章于 2023-06-12 18:15:54 发布

阅读量914

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/fly_fly_zhang/article/details/94337966

版权

MySQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

索引的创建和删除SQL

索引的创建：

在创建表时指定索引字段：

mysql> create table manman(id int,
    -> name varchar(10),
    -> index(id));
Query OK, 0 rows affected (0.56 sec)

mysql> show create table manman\G
*************************** 1. row ***************************
       Table: manman
Create Table: CREATE TABLE `manman` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

在已创建的表上创建索引字段
create [unique|fulltext|spatial] index idx_id(索引名) on 表名(属性名)

mysql> create index idx_name on nn(name);
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

alter table 表名 add [unique | fulltext | spatial] index index_name（属性名）;

alter table nn add index  idx_name(name);
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除索引：

drop index index_name on 表名

mysql> drop index idx_name on nn;
Query OK, 0 rows affected (0.88 sec)
Records: 0  Duplicates: 0  Warnings: 0

索引优化：

单表查询索引的执行过程以及优化：普通索引

学生表的索引

 mysql >  show   create  table Student \ G
  * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * *
       Table: Student
Create Table: CREATE TABLE `student` (
  `SID` int(11) NOT NULL,
  `Sname` varchar(20) DEFAULT NULL,
  `Sage` int(11) DEFAULT NULL,
  `Ssex` char(1) DEFAULT NULL,
  PRIMARY KEY (`SID`),
  KEY `idx_name` (`Sname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

可知，存储引擎为INNODB, 主键索引为SID,辅助索引为Sname。

下面分别通过主键索引和辅助索引进行查询。

 mysql >  explain select  *   from Student where SID = 2 \ 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  //搜索行数为1行
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.07 sec)

可以看到，表里面虽然只有四行数据，但是查找数据时，值查询了一行数据。因为使用了PRIMORY主键索引，当我们使用SID作为其中子句的过滤条件时，首先从主键B +索引树上快速找到ID时，InnoDB采用聚集索引，因此找到ID就等于找到这一行数据，不用扫描全表，效率很高。

 mysql >  explain select  *   from Student where Sname = '赵雷' \ G
  * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. 行* * * * * * * * * * * * * * * *
           id: 1
  select_type: SIMPLE
        table: Student
   partitions: NULL
         type: ref
possible_keys: idx_name //命中索引为辅助索引
          key: idx_name
      key_len: 83
          ref: const
         rows: 1 //搜索行数为1行
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.06 sec)

查询Sname字段信息时，用到了idx_name辅助索引。innodb的辅助索引叶子结点存储的是辅助索引字段值和对应行记录的主键，因此上面的SQL语句，先查询Sname字段的辅助索引B+树，找到Sname=‘赵雷’ 的结点后，获取对应的主键SID=3,然后再用SID=3去主索引树去寻找数据。

单表查询索引的执行过程以及优化:普通查询+排序或者分组

mysql> explain select * from bn where userid=1 order by date\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: bn
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8
     filtered: 12.50
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.05 sec)

上面sql是对userid=1的所有date进行排序。可以看到上面做了整表查询，效率相当低，由于查询完userid=1的所有行记录后，还要按date字段进行升序排序，所以出现了Using filesort,这涉及文件排序，效率相当低，一般遇见using filesort一定要进行优化 。
那么我们分别给userid和date创建索引。然后进行查询

 mysql >  explain select  *  from bn force  index （ idx_id ） where userid = 1  order  by  date \ G 
 * * * * * * * * * * * * * * * * * * * 1. 行* * * * * * *

           id: 1
  select_type: SIMPLE
        table: bn
   partitions: NULL
         type: ref
possible_keys: idx_id
          key: idx_id
      key_len: 4
          ref: const
         rows: 3
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

可以看到我们使用了索引，但是只用到了idx_id,并且还是出现了Using filesort，上面的SQL是先查询userid辅助索引树，找到主键id(没有自定义主键，innodb会生成默认主键)，然后在拿主键id，到主键索引树上去搜索数据，最后根据date字段进行文件排序，效率还是很低。

force index(index_name):

这是强制索引，是MYSQL再5.6版本后加入的新特性，SQL优化器会再索引存在的情况下，通过符合range范围的条数的条数和总数的比例来选择是使用索引还是进行全表遍历，mysql Server 认为使用索引，还不如整表搜索来的快，那么就不会使用索引了。强制索引是一定会使用的索引

注意：

一个SQL查询一张表，只能使用一个索引。

使用联合索引解决问题：

删除两个单独索引，创建联合索引，因为userid是where的过滤条件，因此userid在前，date在后。

mysql> alter table bn drop index idx_id;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table bn drop index idx_date;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings：0 


mysql> alter table bn add index idx_id_date(userid,date);
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用联合索引解决问题。

mysql> explain select * from bn where userid=1 order by date\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: bn
   partitions: NULL
         type: ref
possible_keys: idx_id_date
          key: idx_id_date
      key_len: 4
          ref: const
         rows: 3
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

由上表可以可以看到，创建联合索引后，就不会出现 using filesort ，根据 userid=1查询辅助索引树，找到的数据已经按照date排序好了，然后再去主键索引树，这样效率很高。
如果上面没有出现索引，那么就是被优化了，我们加强制索引 force index。

联合索引分析：

上面的联合索引中，我们使用联合索引只能从userid进去，因为联合索引树是以userid为主搭建的，date只是在userid相等时，创建自己局部的小索引，但是不能别外界直接使用。

多表查询：连接查询索引的执行过程以及优化

在使用多表联合查询时，Mysql会首先确定哪个表小，表小主要指行数，那么mysql就会对小表进行整表遍历，显然Student是小表。小表是使用不到索引的，但是大表SC的SID字段将被创建索引。
小表决定查询次数，大表决定查询时间

mysql> explain select * from Student inner join SC on SC.SID=Student.SID\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL  //小表，没有用到索引
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: SC
   partitions: NULL
         type: ref
possible_keys: idx_id
          key: idx_id  //大表，使用到索引
      key_len: 5
          ref: sqldemo.Student.SID
         rows: 2
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

where 条件过滤之后的大小表

mysql> explain select * from Student inner join SC on SC.SID=Student.SID where SC.SID=1\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
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: SC
   partitions: NULL
         type: ref
possible_keys: idx_id
          key: idx_id //这里索引的使用是因为where 条件
      key_len: 5
          ref: const
         rows: 3
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

在连接查询中，大表小表的角色是不一定的，没有where子句，那么就按照表的行数来定，如果由where子句，那么就按条件过滤完的行数来定大小表。

多表查询：连接查询代替子查询

子查询在多表查询时的效率是很低的，因为要产生中间表(产生和销毁->效率低)，多表查询最好优化成连接查询，效率很高。

mysql> explain select * from Student where SID in(select SID from hh where score>66)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: <subquery2> //临时表，子查询有中间表的产生和销毁，效率低
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: <subquery2>.SID
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: MATERIALIZED
        table: hh
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 33.33
        Extra: Using where
3 rows in set, 1 warning (0.00 sec)

上面子查询会产生中间表，效率没有连接查询高，把子查询优化为连接查询

mysql> explain select * from Student inner join hh on Student.SID=hh.SID where hh.score>66\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: hh
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 33.33
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: sqldemo.hh.SID
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

索引失效：

like 后面的通配符在前面，索引失效.
没有使用联合索引的第一列（上文有解释），not in，!=，使用函数，类型转换,or 等都无法使用索引！

SQL和索引的优化：

如何去定位哪些SQL操作效率低，然后根据实际情况在想优化措施。

慢查询日志：

mysq可以设置慢查询日志，当sql执行的实现超过我们设定的时间，那么这些sql就会被记录在慢查询日志中，然后我们查看日志，用于解释分析sql的执行过程，来判定为何效率低下，是不是没有使用索引的问题？还是索引本身创建有问题？或者索引使用到了，但是由于表的数据量太大，花费的时间就很长，那么我们此时可以把表分成ñ个小表。

mysql> show variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.05 sec)

表示超过十秒的SQL都会被记录在查询日志中。通过下面SQL修改慢查询时间阈值。

mysql> set long_query_time=1;
Query OK, 0 rows affected (0.04 sec)

慢查询日志放在数据中的的slow.log文件中。

show status

MySQL提供了show status 命令，查看MySQL Server的运行参数，可以查看select，insert,delete,update语句的执行频率，慢查询次数，事务的提交和回滚的次数，如下
信息较多，只展示部分

mysql> show status like 'Com_%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Com_delete                          | 0     | 
| Com_insert                          | 0     |//执行insert操作的次数，对于批量插入只类加一次
| Com_select                          | 2     |//执行select操作的次数，每次查询只类加1
| Com_update                          | 0     |

针对INNODB存储引擎的参数：

mysql> show status like 'InnoDB_%';
+------------------------------+---------------+
| Variable_name                         | Value    
+------------------------------+---------------+
 Innodb_rows_deleted                   | 579  //操作删除的行数       
| Innodb_rows_inserted                  | 776 //操作插入的行数                                            
| Innodb_rows_read                      | 8437 //查询返回的行数                                            
| Innodb_rows_updated                   | 580 //操作更新的行数

优化总结：

对于查询进行优化，应尽量避免全表扫描，首先应考虑在where 和 order by涉及的列上建立索引。
注意会使索引失效的运算符，这样的SQL是无法使用索引的。
应尽量避免在where子句中使用or来连接条件，否则将导致mysql放弃使用索引而进行全表扫描。
in和not in 也要慎用，否则会导致全表扫描，对于in里面的连续数据，使用between 好点。
尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全局扫描。如：select id from t where num/2=100。应改为：select id from t where num=100*2；
尽量避免在where子句中对字段进行函数操作，这样导致全局扫描。
不要在where子句的=左边进行函数，算术运算或者其它表达式运算，否则系统将可能无法正确使用索引。
在使用索引字段作为条件时，如果该索引时联合索引，那么必须使用联合索引中第一个字段作为条件1时才能保证系统使用该索引，否则该索引将不会被使用，并且尽量让字段顺序与索引顺序一样(可以更多使用索引)。
并不是所有索引对查询都有效，sql是根据表中数据进行优化的，当索引列中有大量的重复数据时，sql查询可能不会去利用索引。这属于MySQL的SQL优化器对索引的一种优化。
索引并不是越多越多，索引固然可以提高相应的select效率，但同时降低了insert和update的效率，因为insert和update有时可能会重建索引。一个表的索引最好不要超过6个，若太多则应考虑一些不长使用到的列上建的索引是否有必要。
尽量使用数字型字段，若只含数值信息尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符(like 数据%可以索引查询，%数据不能使用索引的原因)，而对数字一般之比较一个字符。
尽可能使用varchar/nvarchar代替char和nchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于子查询来说，在一个相对比较小的字段内搜索效率显然要高些。

Fly_Fly_Zhang

关注

0
点赞
踩
3

收藏

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

索引优化：单表查询索引的执行过程以及优化：普通索引Student表的索引mysql> show create table Student\G*************************** 1. row *************************** Table: StudentCreate Table: CREATE TABLE `student` ...
复制链接

扫一扫