MySql优化

1.创建一个数据库

create database temp

2.创建表

/*部门表*/
CREATE TABLE dept(
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

3.插入数据

#测试数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

#随机返回一个字符串
drop function if exists `rand_string`;
delimiter $$
create function rand_string(n INT)
returns varchar(255) #该函数会返回一个字符串
begin
#定义了一个变量 chars_str， 类型  varchar(100)
#默认给 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
 declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare return_str varchar(255) default '';
 declare i int default 0;
 while i < n do
    # concat 函数 : 连接函数
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
  return return_str;
end $$
delimiter ;

#随机返回一个部门号
drop function if exists `rand_numn`;
delimiter $$
create function rand_num()
returns int(5)
begin
    declare i int default 0;
    set i = floor(10+rand()*500);
    return i;
end $$
delimiter ;


#随机添加雇员max_num条，雇员的编号从start开始
drop proceduce if exists `insert_emp`;
delimiter $$
create procedure insert_emp(in start int(10), in max_num int(10))
begin
    declare i int default 0;
    #set autocommit=0 把autocommit设置为0，不要自动提交
    set autocommit = 0;
    while i <= max_num do
        insert into emp values((start+i), rand_string(6), 'SALESMAN', 0001, curdate(), 2000, 400, rand_num());
        set i = i + 1;
    end while;
    commit;#commit整体提交所有sql语句，提高效率
end $$
delimiter ;

call insert_emp(100001,4000000);

 

4.以记录慢查询的方式来启动mysql
先把mysql关闭后，再重启

{%mysql%}>bin/mysqld.exe --safe-mode --slow-query-log

5.查看当前数据库是否开启了日志：

mysql> show variables like 'log_%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | OFF                         |
| log_bin_trust_function_creators | OFF                         |
| log_error                       | E:\amp\mysql\Data\WIN-7.err |
| log_output                      | FILE                        |
| log_queries_not_using_indexes   | OFF                         |
| log_slave_updates               | OFF                         |
| log_slow_queries                | ON                          |
| log_warnings                    | 1                           |
+---------------------------------+-----------------------------+
8 rows in set (0.00 sec)

6.默认情况下，慢查询的时间是10s

show variables like 'long_query_time';

7.慢查询日志放在mysql的data目录下，在my.ini配置文件有data目录

#Path to the database root
datadir="E:/amp/mysql/Data/"

8.为了测试，我们修改一下mysql默认的慢查询时间

set long_query_time = 1;

查询：

9.慢查询日志的查看

# Time: 180319 22:03:17
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.633036  Lock_time: 0.001000 Rows_sent: 1  Rows_examined: 4000006
use temp;
SET timestamp=1521468197;
select * from emp where empno=347677;

 10.分析

mysql> explain select * from emp where empno=347677\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4000006
        Extra: Using where
1 row in set (0.00 sec)

type依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引
A：system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index
B：const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描
C：eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref
D：ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。
E：fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
F：ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。
G：unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值
H：index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
I：range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
J：index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
K：index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
L：all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

11.通过刚刚的分析，初步判断为没有索引造成的

在empno这个字段上面添加主键索引

mysql> alter table emp add primary key(empno);
Query OK, 4000006 rows affected (6.99 sec)
Records: 4000006  Duplicates: 0  Warnings: 0

索引文件变大，索引有开销，也需要占用磁盘

12.测试看看效果

mysql> select * from emp where empno=347677;
+--------+--------+----------+-----+------------+---------+--------+--------+
| empno  | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+--------+--------+----------+-----+------------+---------+--------+--------+
| 347677 | KwbVxC | SALESMAN |   1 | 2018-03-13 | 2000.00 | 400.00 |    208 |
+--------+--------+----------+-----+------------+---------+--------+--------+
1 row in set (0.00 sec)