Mysql 数据库优化

Mysql 数据库优化

查询时指定列

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

1. 准备表和数据：

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. 创建存储过程，插入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;

1. 执行存储过程：

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哪些使用索引的情况

1. 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。

2. 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

3. 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的时候，后面的索引就不生效了。

4. 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)

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

   key_len: 4:只有一个字段命中索引，此处只有a命中索引，根据最左原则，会从最左开始匹配，a匹配完会找b，但是b是返回查找，不能使用索引，所以此时就只有a命中索引。

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

总结：

最左前缀原则

最左前缀原则指的的是在sql where 字句中一些条件或表达式中出现的列的顺序要保持和多索引的一致或以多列索引顺序出现，只要出现非顺序出现、断层都无法利用到多列索引。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配.

Explain使用分析

参考：https://segmentfault.com/a/1190000008131735