mysql优化之sql语句优化

从项目中迅速定位执行速度慢的语句(定义慢查询)，然后优化

1、首先我们了解mysql数据库的一些运行状态如何查询(mysql的运行时间、一共执行了多少次select update detele 当前连接)
指定：show status
常用的：
show status like 'uptime' 查询运行时间
show status like 'com_select'  show status like 'com_insert' 类似 update delete 查询curd次数
　　show [session/global] status like ... 查询curd次数 有参数值 默认是session 只是查询当前窗口执行情况，需要看从mysql启动到现在为止的，需要加上global参数
show status like 'connections' 显示当前连接数
show status like 'slow_queries' 显示慢查询次数

2、定位慢查询
mysql默认情况下会认为10秒才是一个慢查询
show vaeiables like 'long_query_time'; 显示当前慢查询时间
set long_query_time = 1; 修改慢查询定义时间

构建数据复杂的大表：记录数据不同，尽量接近真实情况

存储过程、视图、函数、触发器，都保存在数据库中，和表一样，是数据库的数据对象，都用dbms来操作调用。每创建一个存储过程、函数都会保存在数据库中，之后在sql语句中可以直接调用定义的函数或存储过程

mysql中有dual 被称为伪表，实际上不存在数据库中，但是select可以调用此表，用于sql语句的计算和操作

sql操作过长，show status like 'slow_queries' 显示慢查询次数，会记录下来慢查询

3、把慢查询统计到日志中去，需要启动mysql服务的时候，指定参数启动

bin\mysqld.exe --safe-mode  --slow-query-log [mysql5.5 可以在my.ini指定]

bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]

日志文件会放在my.ini 中datadir路径中，即数据库文件存放路径

然后就能查询到慢查询的记录

 

优化办法：
一、添加索引 
没有索引的话，数据库查询数据会依次遍历所有数据，所以反应很慢。
有索引：二叉树算法，分块，插半查找，找中间数向上取整

四种索引(主键索引、唯一索引、全文索引、普通索引)

1. 查询索引
   desc 表名 [该方法缺点是：不能够显示索引名]
   show index(es) from 表名\G
   show keys from 表名 与index效果一样
2. 添加索引

• 主键索引
  当一张表，把某个列设为主键的时候，则该列就是主键索引，只能有一个
  

  create table aaa

  (id int unsigned primary key auto_increment ,

  name varchar(32) not null default  '');

  这是id 列就是主键索引
  

  如果你创建表时，没有指定主键索引，也可以在创建表后，在添加, 指令: 

  alter table 表名 add primary key (列名);

• 普通索引
  一般来说，普通索引是先创建表，再创建普通索引
  create table aaa ( id int unsigned, name varchar(20));
  create index 索引名 on aaa (name);
• 全文索引
  主要是针对文字文本的检索，一般是文章，全文索引只对myISAM生效
  

  创建 ：

  CREATE TABLE articles (

         id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

         title VARCHAR(200),

         body TEXT,

         FULLTEXT (title,body)

       )engine=myisam charset utf8;

   

  INSERT INTO articles (title,body) VALUES

       ('MySQL Tutorial','DBMS stands for DataBase ...'),

       ('How To Use MySQL Well','After you went through a ...'),

       ('Optimizing MySQL','In this tutorial we will show ...'),

       ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),

       ('MySQL vs. YourSQL','In the following database comparison ...'),

       ('MySQL Security','When configured properly, MySQL ...');

  使用：
  

  错误用法:

  select * from articles where body like ‘%mysql%’; 【不会使用到全文索引】

  证明:

  explain  select * from articles where body like ‘%mysql%’

   

  正确的用法是:

  select * from articles where match(title,body) against(‘database’); 【可以】
  mysql全文索引要点：
  1、只针对myisam引擎生效
  2、默认只对英文生效，sphinx(coreseek)，技术处理中文
  3、使用方法是：match(字段名,) against ('关键字')
  4、全文索引有停止词的概念，因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词被称为停止词

  
  
  
• 唯一索引
  唯一索引此列内容不能重复为unique，可以有多个
  create table sss(id int primary key auto_increment, name varchar(32) unique);
  这是name就是唯一索引
  唯一索引可以为空，但不为空的内容不能重复，主键字段不能为空，不能重复
  create unique index 索引名 on 表名 (字段)
  
  

3、删除索引
alter table 表名 drop index 索引名
如果删除主键索引
alter table 表名 drop primary key     === 【  alter table aaa modify id int not null   先修改是该字段不是自增长】

4、修改
先删除 在创建

索引使用注意事项：(索引只是加快查询，不过一般网站都是查询多的)
索引使用的代价：
1、占用磁盘空间
2、对dml操作会有影响，会变慢(每次操作都会修改索引文件)
那些列上适合添加索引呢：
1、较频繁的作为查询条件字段应该创建索引 (id)
2、唯一性太差的字段不适合单独创建索引，即使频繁做查询条件也不使用 (sex logincount)
3、不会出现在查询where条件里面的字段

总结，适合创建索引的情况如下：
1、肯定在where中经常使用的
2、该字段的内容不是唯一的几个值(sex)
3、字段内容不是频繁变化的(每次变化 索引变化)