Innodb存储引擎 索引优化查询管理

最新推荐文章于 2023-07-11 19:00:21 发布
最新推荐文章于 2023-07-11 19:00:21 发布
阅读量218 收藏 1
点赞数
分类专栏： MySQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Yosigo_/article/details/118731063
版权

Innodb 索引优化管理

实验环境

  • 索引优化的实验效果需要在一张表中有海量数据的情况下，实验效果才会比较明显，我们在实验前需要先准备一张300万条数据的表。
# 创建表格
create table t1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

# 创建存储过程,插入3000000条数据
delimiter $$ 
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into t1 values(i,'nana','male',concat('nana',i,'@haha'));
        set i=i+1;
    end while;
END$$ 
delimiter ; 

# 查看存储过程
show create procedure auto_insert1\G 

# 调用存储过程
call auto_insert1();

查看数据文件大小
# 插入三百万条数据,大概大小为188M
[root@db01 db01]# ll -h /service/mysql/data/db01
total 189M
-rw-rw---- 1 mysql mysql   67 Jul 14 15:38 db.opt
-rw-rw---- 1 mysql mysql 8.5K Jul 14 15:39 t1.frm
-rw-rw---- 1 mysql mysql 188M Jul 14 16:06 t1.ibd

# 查看表格式内容
mysql> select * from t1 where id<3;
+------+------+--------+------------+
| id   | name | gender | email      |
+------+------+--------+------------+
|    1 | nana | male   | nana1@haha |
|    2 | nana | male   | nana2@haha |
+------+------+--------+------------+
2 rows in set (0.95 sec)

# 查看表属性
mysql> desc t1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

命中索引也未必会加速，如下所示：

关于等值查询

1. 以重复度低的字段为基础创建索引,加速效果明显
mysql> select count(id) from t1 where id = 33;
+-----------+
| count(id) |
+-----------+
|         1 |
+-----------+
1 row in set (0.83 sec)

# explain(查询优化神器)的rows值越小,查询效率越高
# explain查询sql语句的执行计划,rows为2990010,速度慢
mysql> explain select count(id) from t1 where id = 33;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 2990010 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

# 创建主键索引
mysql> create index xxx on t1(id);
Query OK, 0 rows affected (3.78 sec)
Records: 0  Duplicates: 0  Warnings: 0

# explain查询sql语句的执行计划,rows为1,速度快
mysql> explain select count(id) from t1 where id = 33;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | xxx           | xxx  | 5       | const |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> desc t1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  | MUL | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

# 命中主键索引后查询速度得到了优化
mysql> select count(id) from t1 where id = 33;
+-----------+
| count(id) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)


2. 以重复度高的字段为基础创建索引,加速效果不明显
# 查看无索引字段name的sql语句执行计划,rows为2990010,速度慢
mysql> explain select count(id) from t1 where name="nana";
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 2990010 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

# 创建name字段的辅助索引
mysql> create index yyy on t1(name);
Query OK, 0 rows affected (5.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 给name创建了普通索引,我们发现查看name字段的执行计划查询速度并没有得到很好的优化,rows为1495005,速度慢
mysql> explain select count(id) from t1 where name="nana";
+----+-------------+-------+------+---------------+------+---------+-------+---------+-----------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows    | Extra                 |
+----+-------------+-------+------+---------------+------+---------+-------+---------+-----------------------+
|  1 | SIMPLE      | t1    | ref  | yyy           | yyy  | 83      | const | 1495005 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------+---------+-----------------------+
1 row in set (0.00 sec)

# 我们发现命中了辅助索引name字段后,查询的速度依然很慢。
# 原因是因为我们的name字段,数据全部都是nana。
# 这时候我们给name字段添加普通索引,该辅助索引的key值全部都是nana,不但没有优化查询效率,反而使查询效率变得更低了。
mysql> select count(id) from t1 where name="nana";
+-----------+
| count(id) |
+-----------+
|   2999999 |
+-----------+
1 row in set (3.73 sec)

# 我们通过explain查询sql语句的执行计划,查看name字段不等于nana的值,查询速度得到了优化,rows为2,速度快
mysql> explain select count(id) from t1 where name!="nana";
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t1    | range | yyy           | yyy  | 83      | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

# 命中索引重复度高的字段,取反的情况下,查询效率的得到了优化
mysql> select count(id) from t1 where name!="nana";
+-----------+
| count(id) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)


3. 以占用空间大的字段为基础创建索引,加速效果不明显
mysql> select count(id) from t1 where email="nana1562562@haha";
+-----------+
| count(id) |
+-----------+
|         1 |
+-----------+
1 row in set (1.16 sec)

# 创建普通索引
mysql> create index zzz on t1(email);
Query OK, 0 rows affected (6.48 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 以占用空间大的字段为基础创建索引,理论上加速效果是不明显的。
# 但是我们email字段的数据占用的空间不够大,所以实验效果不佳。
mysql> select count(id) from t1 where email="nana1562562@haha";
+-----------+
| count(id) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

关于等值查询结论 : 给重复度低、且占用空间小的字段值为基础构建索引！！

关于范围查询

mysql> desc t1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  | MUL | NULL    |       |
| name   | varchar(20) | YES  | MUL | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

1. innodb存储引擎能够加速范围查询,但是查询范围越大,加速效果越不明显
# id>33的数据范围很大,速度慢
mysql> explain select count(id) from t1 where id>33;
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | t1    | range | xxx           | xxx  | 5       | NULL | 1372191 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)

# id>33 and id<50的数据范围不大,速度快
mysql> explain select count(id) from t1 where id>33 and id<50;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | range | xxx           | xxx  | 5       | NULL |   15 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

# id between 33 and 50 等同于33<=id<=50数据
# 范围不大,速度快
mysql> explain select count(id) from t1 where id between 33 and 50;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | range | xxx           | xxx  | 5       | NULL |   18 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

# id>33 and id<100000数据范围大,速度慢
mysql> explain select count(id) from t1 where id>33 and id<100000;
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | t1    | range | xxx           | xxx  | 5       | NULL | 192494 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

# 查看命中id字段的sql语句查询计划,id!=1000数据,是一个很大的范围,rows为1373188
mysql> explain select count(id) from t1 where id!=1000;
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | t1    | range | xxx           | xxx  | 5       | NULL | 1373188 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)

# like模糊查询
# like指定一个明确的值，速度依然很快
mysql> explain select count(*) from t1 where email like "xxx";
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | range | zzz           | zzz  | 203     | NULL |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

# like匹配字符中有%,但是处于末尾,速度依然很快
mysql> explain select count(*) from t1 where email like "xxx%";
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | range | zzz           | zzz  | 203     | NULL |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

# like匹配字符中有%,但是处于开头,速度慢
mysql> explain select count(*) from t1 where email like "%xxx";
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | t1    | index | NULL          | zzz  | 203     | NULL | 2744383 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)

关于范围查询的结论：innodb存储能够加速范围查询，但是查询范围不能特别大
关于范围查询的符号:

  >    >=
  <     <=	
  !=
  between and	( 等同于>= and <= )
  like 后的内容应该尽量将%往右放，并且左半部分的内容应该尽量精确

针对范围查询命中了索引，如果范围很大，查询效率依然很低，如何解决?

  1. 要么把范围缩小
  2. 要么就分段取值，一段一段取最终把大范围给取完

关于条件字段参与运算查询

mysql> desc t1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  | MUL | NULL    |       |
| name   | varchar(20) | YES  | MUL | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

1. 关于条件字段参与运算
# 该sql语句是将id字段的数据当成条件遍历出来,依次乘12,再打印等值(命中主键索引)。速度慢
# 大量的时间花在了运算上
mysql> explain select count(id) from t1 where id*12=10000;
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | t1    | index | NULL          | xxx  | 5       | NULL | 2744383 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)

# 调整上一条sql语句的逻辑,先运算,再做等值查询(命中主键索引)。速度快
mysql> explain select count(id) from t1 where id=10000/12;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | xxx           | xxx  | 5       | const |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

# 先将sql语句id字段进行遍历当成条件,传递给函数进行代码逻辑的运行,再命中主键索引
# 大量的时间花在了代码逻辑上
select count(id) from t1 where func(id) = 10000/12;

关于条件字段参与运算的查询：不要让条件字段参与运算，或者说传递给函数

  • 如果生产环境中碰到需要将条件字段参与运算或者传递给其他函数的情况，尽量让开发换一种逻辑去写代码。
  • 因为出现让条件字段参与运算这种情况，会直接导致数据库查询效率变慢。

索引下推技术

索引下推技术默认是开启的

# 删除索引xxx
mysql> drop index xxx on t1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 删除索引yyy
mysql> drop index yyy on t1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

# and查询
# 对于连续多个and: mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询。
# 区分度最高的是email(命中普通索引)，所以制作联合索引为(email,name,gender)
mysql> explain select count(id) from t1 where name="nana" and email="nana666@haha" and gender="male";
+----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | t1    | ref  | zzz           | zzz  | 203     | const |    1 | Using index condition; Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------+

# or查询
# 对于连续多个or: mysql会按照条件的顺序，从左到右依次判断，即联合索引为(name,email，gender)，并不会加速查询
mysql> explain select count(id) from t1 where name="nana" or email="nana666@haha" or gender="male";
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t1    | ALL  | zzz           | NULL | NULL    | NULL | 2744383 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

and与or的逻辑：

条件1 and 条件2：所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立

条件1 or 条件2：只要有一个条件成立则最终结果就成立

and连接的多个条件属于小范围，or连接的多个条件属于大范围。
对于连续多个and:

  • mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样可以快速锁定很小的范围)，加速查询。

对于连续多个or:

  • mysql会按照条件的顺序，从左到右依次判断，并不会加速查询。

联合索引与最左前缀匹配原则

条件中需要用到多个字段，并且多次查询中的多个字段都包含某一个字段，即可创建联合索引。

mysql> drop index zzz on t1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

# 重复度低且占用空间较小的字段应该尽量往左放,让其成为最左前缀
# 创建联合索引,索引创建为最左边的字段email
mysql> create index xxx on t1(email,name,gender);
Query OK, 0 rows affected (13.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 查询多个字段,命中普通索引email字段,加速查询
mysql> explain select count(id) from t1 where name="nana" and email="nana666@haha" and gender="male";
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref               | rows | Extra                 |
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | t1    | ref  | xxx           | xxx  | 311     | const,const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-----------------------+
1 row in set (0.00 sec)

# 查询多个字段,命中普通索引email字段,加速查询
mysql> explain select count(id) from t1 where name="nana" and email="nana666@haha";
+----+-------------+-------+------+---------------+------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+---------------+------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | t1    | ref  | xxx           | xxx  | 286     | const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

# 查询单个字段,命中普通索引email字段,加速查询
mysql> explain select count(id) from t1 where email="nana666@haha";
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t1    | ref  | xxx           | xxx  | 203     | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

# 查看单个字段name,没有命中索引email,查询不加速
mysql> explain select count(id) from t1 where name="nana";
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 2744383 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

什么时候创建联合索引？

  • 条件中需要用到多个字段，并且多次查询中的多个字段都包含某一个字段。

创建联合索引需要注意的问题：

  • 重复度低且占用空间较小的字段应该尽量往左放，让其称为最左前缀。

使用联合索引，满足加速条件的规则:

  • 使用select查询数据的时候，where条件中必须包含最左前缀的字段，命中索引才可以实现加速。

总结索引使用原则

  • 1、在创建索引的时候，会把该列所有的数据按照b+ tree的方式进行排序

  • 2、为常作为查询条件的字段建立索引

  • 3、限制索引的数目，不要每列都创建索引每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间

  • 4、在同一列上，尽量避免创建多个索引，可以创建多个但是它们是有优先级的，先走一个就不会再走另一个索引了；

    alter table student add index idx_name(name);

alter table country add unique key uni_name(name);

  • 5、避免对大列建索引，在数据很长的列上创建前缀索引

  • 6、如果可以创建唯一索引,就创建唯一索引(该列的数据不重复)，查询速度快

  • 7、不要对重复度高的字段创建索引

  • 8、索引不要参与计算

  • 9、为经常要排序，分组，联合操作的列，创建联合索引经常需要order by、group by、distinct和union等操作的字段，排序操作会浪费很多时间。 如果为其建立索引，可以有效地避免排序操作

  • 10、尽量使用前缀来索引创建索引的时候，可以给该列所有数据进行排序

    create index xxxx on tb(title(19)) # text类型，必须制定长度

  • 11、删除不再使用或者很少使用的索引 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

  • 12、避免使用select *

  • 13、count(1) 或 count(列) 代替 count(*)， ps：mysql中没有差别了

  • 14、创建表时尽量时 varchar 代替 char

  • 15、表的字段顺序固定长度的字段优先

  • 16、使用连接（JOIN）来代替子查询(Sub-Queries)

  • 17、连表时注意条件类型需一致

MySQL常见面试题

1. 如何定位运行速度慢的sql

  • 开启慢查询日志。

2. 如何分析sql语句

  • 使用explain工具

3. 如何优化sql语句

  1. 逻辑(能链表就不要使用子查询)===》rows是否变低

  2. 对查询范围大的sql建议：分段取，缓存

  3. 建立索引

  • 尽量给占用空间小，并且重复度低的字段建立索引
  • 不要让索引字段参与运算
  • 尽量减少范围查询(能分段取就不要一下子全取出来)
  • 能用覆盖索引就不要回表(尽量用主键字段作为查询条件)
  • 多条sql语句用的都是and连接的多个条件，而条件中都出现了某一个字段，可以创建联合索引
我的紫霞辣辣
关注
专栏目录
MySQL InnoDB 查询优化实现分析
01-24
MySQL InnoDB 查询优化实现分析，欢迎下载，1111111111111
Innodb查询优化器
weixin_34267123的博客
03-17 334
查询优化器如何找到最优执行计划 优化器的主要作用就是为待执行的sql语句找到最优的执行计划，其基本优化方式如下： 等价变化规则 例如： 5=5 and a>5 改写成 a>5 a<b and a=5 改写成 b>5 and a=5 基于联合索引，调整条件位置等 例如联合索引(name,age)，当查询age=5 and name=...
mysql innodb 查询优化_mysql8 参考手册--优化InnoDB查询
weixin_42360953的博客
01-27 172
优化InnoDB查询要调整InnoDB表查询，请在每个表上创建一组适当的索引。有关详细信息，请参见 第8.3.1节“ MySQL如何使用索引”。请遵循以下InnoDB索引准则：1、因为每个InnoDB表都有一个 主键(无论您是否请求一个主键)，所以请为每个表指定一组主键列，这些列用于最重要且时间紧迫的查询中。2、不要在主键中指定太多或太长的列，因为这些列值在每个辅助索引中都是重复的。当索引包含不必...
mysql查询和优化_MySQL查询优化
weixin_35662493的博客
01-19 156
Mysql存储引擎最常使用的2种存储引擎:Myisam是Mysql的默认存储引擎，当create创建新表时，未指定新表的存储引擎时，默认使用Myisam。每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm(存储表定义)、.MYD (MYData，存储数据)、.MYI (MYIndex，存储索引)。数据文件和索引文件可以放置在不同的目录，获得更快的速度。InnoDB存储...
MySQL InnoDB存储引擎性能调优
愤怒的苹果ext的博客
03-27 878
Innodb_buffer_pool_read_ahead_evicted ： 预读的页，但是没有被读取就从缓冲池被替换的页的数量，一般用来判断预读的效率。Innodb_buffer_pool_read_requests ： 从缓冲池中读取页的次数。Innodb_data_reads ： 发起读取请求的次数，每次读取可能需要读取多个页。Innodb_buffer_pool_reads : 表示从物理磁盘读取页的次数。Innodb_buffer_pool_read_ahead ： 预读的次数。
MySQL学习（七）：Innodb存储引擎索引的实现原理详解
09-09
MySQL的InnoDB存储引擎是数据库管理中常用的一种存储引擎，尤其在处理大量数据和事务处理方面表现出色。InnoDB存储引擎的索引实现主要依赖于B+树数据结构，这有助于加速数据检索，减少磁盘I/O操作，从而提高查询性能...
MySQL内核：InnoDB存储引擎 卷1.pdf.zip
05-16
深入学习《MySQL内核：InnoDB存储引擎 卷1》，读者可以了解到InnoDB的内部工作机制，如如何处理B+树索引、事务的提交与回滚、锁的实现以及内存管理等内容，这对于优化数据库性能、解决并发问题、设计高效的数据模型...
Mysql InnoDB引擎的索引与存储结构详解
09-09
MySQL数据库中，InnoDB存储引擎的索引与存储结构是数据库性能优化的关键，了解这些机制对于数据库管理员和开发人员至关重要。InnoDB是MySQL中支持事务处理的存储引擎，它具有ACID（原子性、一致性、隔离性、持久性）...
Mysql innodb索引与查询优化
weixin_30235225的博客
02-25 196
一、引言 概要 (1)innodb索引类型与B+树 (2) 索引使用注意事项 (3)车点评业务表索引优化案例 问题 (1)InnoDb索引为什么要用b+树？ (2)聚集索引与普通索引有什么区别？ (3)为什么主键最好是自增？ (4)组合索引有什么优势？ (5) 有哪些实际业务场景索引搞不定？ 二、innodb索引类型与B+树 IN...
InnoDB存储引擎锁的相关优化
qq_33674508的博客
06-30 106
InnoDB存储引擎实现了行级锁，行锁的实现带来性能的损耗比表锁更高，但在整体并发处理能力方面要远远高于MyISAM的表锁的，当系统的并发量较高时， InnoDB的整体性能和MyISAM相比有明显的优势； 1.尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表所； update tb set name='11' where id='1',当id走索引了，则为行锁，并发高，否则升级为表锁 2.合理设计索引，尽量缩小锁的范围 3.尽可能减少索引条件，及索引范围，避免间隙锁，例如id<10,在i
InnoDB存储引擎的备份与性能调优
AIJXB的博客
02-02 184
备份分类 可以根据不同的类型来划分备份的方法。 根据备份的方法不同可以将备份分为: Hot Backup（热备) Hot Backup是指数据库运行中直接备份，对正在运行的数据库操作没有任何的影响。这种方式在MySQL官方手册中称为Online Backup（在线备份)。 Cold Backup（冷备) Cold Backup是指备份操作是在数据库停止的情况下，这种备份最为简单，–般只需要复制相关的数据库物理文件即可。这种方式在MySQL官方手册中称为Offine Backup（离线备份)。
MySQL InnoDB存储引擎原理与缓存组件参数优化
Mr.xing的博客
03-11 1791
AHI有一个要求，对这个页的连续访问模式(查询条件)必须一样的。缓冲池简单来说就是一块内存区域，在数据库中进行读取页的操作时，先从磁盘读到的页存放在缓冲池中，下一次读取相同的页时，先判断该页是不是在缓冲池中，若在则该页在缓冲池中被命中，直接读取该页，否则就读取磁盘上的页然后再放到缓冲池中。缓冲池是通过LRU(Latest Recent Used，最近最少使用)算法来进行管理的，即最频繁使用的页在LRU列表的最前段，最少使用的页在LRU列表的尾端，当缓冲池不能存放新读取的页时，首先释放LRU列表尾端的页。
MySql InnoDB 存储引擎表优化
最新发布
2301_78834737的博客
07-11 249
如果设置的值大于innodb_page_size，那么服务器会使用innodb_page_size值。对于较大的表，此操作可以节省大量的磁盘 I/O，因为 InnoDB 可以使用它的 change buffer（change buffer 的主要目的是将对二级索引的数据操作缓存下来，以此减少二级索引的随机IO，并达到操作合并的效果）来批量写二级索引记录。InnoDB 如会每秒刷盘一次日志，如果可以承受最新事务崩溃的数据损失，可以设置innodb_flush_log_at_trx_commit = 0。
mysql5.7官网直译InnoDB表优化--只读事物优化，Redo日志优化，数据加载优化，innoDB查询优化，DDL操作优化
Seeker-Wu的专栏
12-12 460
8.5.3 Optimizing InnoDB Read-Only Transactions 只读事物的优化 InnoDB能够避免对一个只读事物设置事物ID(TRX_ID属性)的相关开销。一个事物ID只有在写事物或者是锁读例如SELECT .... FOR UPDATE时才需要。排除不需要的事物id减少了内部数据结构的大小,该数据结构在每次查询中都用到或者是在数据改变语句中构造一个读试图。 I
InnoDB性能调优基础（进阶）
热门推荐
zhu19774279的专栏
08-17 1万+
InnoDB性能调优基础（进阶）
MySQL数据库InnoDB存储引擎查询优化器实现的分析
u012387318的专栏
10-09 525
一、 目的 　　分析MySQL数据库的查询优化器如何优化InnoDB存储引擎的查询语句： 　　(1). 如何实现查询优化的功能？ 　　(2). 为了能实现查询优化，存储引擎需要做哪些方面的配合？ 　　二、 测试准备 　　MySQL数据库软件版本：5.1.49-debug-log 　　测试用例的存储引擎：InnoDB 　　测试用例的表结构： 　　CREATE TABLE `nkeys
【MySQL】InnoDB存储引擎
见见大魔王
03-17 2569
对数据的修改操作，首先修改内存结构中的缓冲区的页，缓冲区的页与磁盘中页的数据不一致，因此称缓冲区中的页为脏页。
Innodb重要参数优化
Able
01-29 1448
1.简单介绍 InnoDB给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。 下面是对线上mysql5.6版本的数据
InnoDB存储引擎索引深度解析
"辛星笔记之InnoDB索引主要涵盖了算法基础、B+树索引、哈希算法和小结等内容，摘自姜承尧先生的《MySQL技术内幕：InnoDB存储引擎》。笔记作者辛星致力于知识的提炼与传播，笔记中强调了二分查找法在InnoDB索引查询中...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值