索引是什么？

mysql索引：索引是帮助mysql高效获取数据的数据结构

可以的到索引的本质：数据结构

你可以简单理解为 排好序的快速查找数据结构

为什么要建索引？

优势：

提高数据检索的效率

降低数据排序的成本

劣势：

索引也要占空间的

提高查询效率，降低更新表的速度（增，删，改）

索引的基本语法（一张表最多建立5个索引）

创建：crea te index indexName on table (colum)

删除：drop index indexName on table

查看：show index from tabls; show index from tablsG

mysql常见的瓶颈

CPU:CPU饱和的时候一般发生在数据装入内存或从磁盘上读取数据

IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

服务器硬件的性能瓶颈

explain

使用explain关键字可以模拟优化SQL查询语句，从而知道mysql是如何处理你的SQL语句，分析你的查询语句或者表结构的性能瓶颈

优化总结口诀

全值匹配我最爱，最左前缀要遵守

带头大哥不能死，中间兄弟不能断

索引列上少计算，范围之后全失效

like百分写最右，覆盖索引不写星

不等空值还有or，索引失效要少用

索引失效

建表

crea te table user(

id int primary key auto_increment,

name varchar(24) not null,

age int not null,

pos varchar(20) not null,

add_time timestamp not null

)charset utf8;

inse rt into user(name,age,pos,add_time) values ('z3',22,manager,now());

inse rt into user(name,age,pos,add_time) values ('July',23,'dev',now());

inse rt into user(name,age,pos,add_time) values ('2000',23,‘dev’,now());

创建索引

crea te index idx_user_nameAgePos on user(name,age,pos);

1.全值匹配我最爱

explain sele ct * from user where name=’July’;

explain sele ct * from userwhere name=’July’ and age=25;

explain sele ct * from userwhere name=’July’ and age=25 and pos=’dev’;

explain select * from user where age=25 and pos=’dev’; 没有按照索引顺序查询

2最佳左前缀法则

指的是查询从索引的最左前列开始并且不跳过索引中的列。

用到的索引，但是是部分用到了。

3.不在索引列上做任何操作（计算、函数、类型转化），会导致索引失效而转向全表扫描

name是varchar类型，如果不加引号就变成了int类型，发生类型转换索引会失效

sele ct * from product where to_days(out_date)-to_days(current_date)

sele ct * from prodect where out_date

4.存储引擎不能使用索引中范围条件右边的列

用到了name 和age索引，范围之后全失效

a1 = 1 and a2 > 'a2' and a3>'a3'

a1 = 1 and a3 > 'a3' and a2>'a2'

5.尽量使用覆盖索引（避免sele ct * ）

查询效率第二条大于第一条 第二条Using index

6.mysql在使用不等于（！=或者）的时候无法使用索引会导致全表扫描

age22 改为 age>22 or age

EXPLAIN SELE CT `name`FROM satffs WHERE `name`='July' AND age >22 OR age

7.is null,is not null 也无法使用索引

8.like以通配符开头（‘%abc’）mysql索引失效会变成全表扫描的操作

问题：解决like %字符串%创建索引不被使用的方法？

sphinx(斯芬克斯)

9.字符串不加单引号索引失效

10.少用or,用它连接时会索引失效

EXPLAIN SELE CT `name` FROM user WHERE `name`='July' OR `age`=22

练习

假设index(a,b,c)

where语句索引是否被用到

where a = 3Y,使用到a

where a = 3 and b = 5Y,使用到a,b

where a = 3 and b = 5 and c = 4Y,使用到a,b,c

where b = 3 or where b = 3 and c = 4N

where a = 3 and c = 5 用到a但是c没用到

where a = 3 and b>4 and c = 5 用到a,b但是c没用到

where a = 3 and b like 'kk%' and c = 4 用到a,b但是c没用到

随机返回一条数据 order by

EXPLAIN SELE CT * FROM satffs ORDER BY RAND() LIMIT 1

$r = sele ct count(*) from user

$d = mysql_fetch_row($r)

$rand = mt_rand(0,$d[0]-1)

sele ct * from user limit $rand,1

慢查询日志

mysql的慢查询日志是mysql提供的一种日志记录，它用来记录mysql在响应时间超过阙值，具体运行时间超过long_quety_time值的sql，则会被记录到慢查询日志中。

具体运行时间超过long_query_time值的sql，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行超过10秒以上的语句。

在mysql源码中判断是大于,而不是大于等于这个阙值。

默认没有开启慢查询日志，需要手动来设置这个参数。

如果不是调优需要的话，一般不建议启动该参数，因为开始慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件

在测试环境中如何开启？

查看 show variables like '%slow_query_log%'

开启 set global slow_query_log=1;

使用 set global slow_query_log=1; 开启慢查询日志只对当前数据库生效，如果mysql重启后则会失效。

windows

my.ini

long_query_time = 1

log-slow-queries = D:phpStudyMySQLlogmysqlslowquery.log

set global long_query_time=3;

设置后查看不出变化：

需要重新连接或新开一个会话才能看到修改值，或者刷新权限，flush privilges

show global variables like 'long_query_time%';

sele ct sleep(4);

慢查询日志工具

可以返回记录集最多的10个SQL

得到访问次数最多的10个SQL

得到按照时间排序的前10条里面含有左连接的查询语句

建库

crea te database bigData;

use bigData;

建表

crea te table dept(

id intprimary key auto_increment,

deptno mediumint not null,

dname varchar(20) not null,

loc varchar(13) not null

)engine=innodb default charset=gbk;

crea te table emp(

id int primary key auto_increment,

empno mediumint not null,

ename varchar(20) not null,

job varchar(9) not null,

mgr mediumint not null,

hiredate DATE not null,

sal decimal(7,2) not null,

comm decimal(7,2) not null,

deptno mediumint not null

)engine=innodb default charset=gbk;

创建函数，假如报错：this function has none of DETERMINISTIC...

查看参数

show variables like 'log_bin_trust_function_creators';

set global log_bin_trust_function_creators=1;

重启后，参数就会消失

windows 配置文件

myslq.ini log_bin_trust_function_creators=1

随机产生字符串

DELIMITER $$

CREA TE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)

BEGIN

DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';

DECLARE return_str VARCHAR(255) DEFAULT '';

DECLARE i INT DEFAULT 0;

WHILE i

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 $$

CREA TE FUNCTION rand_num() RETURNS INT(5)

BEGIN

DECLARE i INT DEFAULT 0;

SET i = FLOOR(100+RAND()*10);

RETURN i;

END $$

假如要删除

drop function rand_num;

创建存储过程

插入数据

DELIMITER $$

CREA TE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))

BEGIN

DECLARE i INT DEFAULT 0;

SET autocommit = 0;

REPEAT

SET i = i + 1;

INSE RT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());

UNTIL i = max_num

END REPEAT;

COMMIT;

END $$

插入数据

DELIMITER $$

CREA TE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))

BEGIN

DECLARE i INT DEFAULT 0;

SET autocommit = 0;

REPEAT

SET i = i + 1;

INSE RT INTO dept(deptno,dname,loc) VALUES ((START + i),rand_string(10),rand_string(8));

UNTIL i = max_num

END REPEAT;

COMMIT;

END $$

修改结束语句

delimiter ;

call insert_dept(100,10);

delimiter ;

call insert_emp(100001,500000);

memory_limit