Mysql 数据库优化

最新推荐文章于 2022-12-01 18:17:21 发布

置顶简单简单小白

最新推荐文章于 2022-12-01 18:17:21 发布

阅读量1.9k

点赞数

分类专栏： mysql 文章标签： mysql 索引优化

本文链接：https://blog.csdn.net/wagnteng/article/details/80526495

版权

mysql 专栏收录该内容

6 篇文章 3 订阅

订阅专栏

Mysql 数据库优化

查询时指定列

在select 是禁止使用select *from table，尽量指定自己需要使用的列，下面测试一下指定列和不指定的执行时间

准备表和数据：

create table user_test.user_info
(
  id       bigint auto_increment
    primary key,
  username varchar(11)  null,
  gender   varchar(2)   null,
  password varchar(100) null
)
  engine = MyISAM
  charset = utf8;

创建存储过程，插入1千万条数据：

create procedure myproc()
  begin
declare num int;
set num=1;
while num <= 10000000 do
insert into user_info(username,gender,password) values(num,'保密',PASSWORD(num));
set num=num+1;
end while;
 end;

执行存储过程：

call myproc()；

查看查询执行结果：

sql> select * from user_info
[2018-05-30 16:05:24] 1000000 rows retrieved starting from 1 in 1 m 9 s 103 ms (execution: 65 ms, fetching: 1 m 9 s 38 ms)
sql> select username from user_info
[2018-05-30 16:05:39] 1000000 rows retrieved starting from 1 in 11 s 862 ms (execution: 38 ms, fetching: 11 s 824 ms)

结果显而易见。

创建索引

索引是可以对记录集合的字段进行排序的方法。在一张表中给一个字段创建一个索引，将创建另外一个数据结构，这个数据结构包含该字段的数值以及指向这一列的指针，然后创建完之后会对这个索引进行排序。如下图：

索引创建原则

查询频繁的列，在where，group by，order by，on从句中出现的列
长度小的列，字段越小越好
尽可能使用not null 的字段作为索引
最左原则，（后面会详细说）
频繁更新的列尽量上放到索引中
多使用聚集索引

性能分析

创建索引与不创建索引的查询比较：

在user_info 中创建字段username 为索引

没有创建索引之前

sql> select id,username,gender,password from user_info where username='9000000'
[2018-05-31 11:34:06] 1 row retrieved starting from 1 in 3 s 182 ms (execution: 3 s 82 ms, fetching: 100 ms)

耗时：3 s 182 ms

创建索引之后

sql> select id,username,gender,password from user_info where username='9000000'
[2018-05-31 11:37:31] 1 row retrieved starting from 1 in 66 ms (execution: 40 ms, fetching: 26 ms)

耗时：66 ms

创建聚合索引原则

新建一个表test ，创建一个聚合索引 a,b,c,d

create table user_test.test
(
  id int not null
    primary key,
  a  int not null,
  b  int not null,
  c  int not null,
  d  int not null,
  e  int not null
);
create index test_a_b_c_d_index
  on user_test.test (a, b, c, d);

分析下面的sql哪些使用索引的情况

select a,b,c,d from test where c=1 and a =1 and b=1 and d=1;

explain select  a,b,c,d from test where c=1 and a =1 and  b=1 and d=1;

执行结果：

mysql> explain select  a,b,c,d from test where c=1 and a =1 and  b=1 and d=1 \G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: test
        type: ref
possible_keys: test_a_b_c_d_index
         key: test_a_b_c_d_index
     key_len: 16
         ref: const,const,const,const
        rows: 1
       Extra: Using index
1 row in set (0.00 sec)

Extra: Using index 说明此处有命中索引；

此处的key_len 是16 ，因为int是4个字节，此处命中的索引是a,b,c,d,所以是16。此处的c=1 and a =1 and b=1 and d=1等价于a =1 and b=1 and c=1 and d=1。

select a,b,c,d from test where a =1 and b = 1 group by c;

mysql> explain select   a,b,c,d from test where  a =1 and b = 1 group by c \G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: test
        type: ref
possible_keys: test_a_b_c_d_index
         key: test_a_b_c_d_index
     key_len: 8
         ref: const,const
        rows: 1
       Extra: Using where; Using index
1 row in set (0.00 sec)

Extra: Using where; Using index ：表示有命中索引；

key_len: 8:次数只有连个字段命中索引，即a,b

select a,b,c,d from test where a =1 and b = 1 and d= 1 group by c;

mysql> explain select  a,b,c,d from test where  a =1 and b = 1 and d= 1 group by c \G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: test
        type: ref
possible_keys: test_a_b_c_d_index
         key: test_a_b_c_d_index
     key_len: 8
         ref: const,const
        rows: 1
       Extra: Using where; Using index
1 row in set (0.00 sec)

Extra: Using where; Using index ：表示有命中索引；

key_len: 8:只有两个字段命中索引，还是a,b，因为遇到group by的时候，后面的索引就不生效了。

select a,b,c,d from test where c=1 and a =1 and b>1;

mysql> explain select  a,b,c,d from test where c=1 and a =1 and  b>1 \G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: test
        type: ref
possible_keys: test_a_b_c_d_index
         key: test_a_b_c_d_index
     key_len: 4
         ref: const
        rows: 1
       Extra: Using where; Using index
1 row in set (0.00 sec)