SQL 优化

drop index name_index on tb ;

查询索引
show index from tb;
show index from tb \G
5.SQL 性能问题
     a.分析SQL的执行计划 ： explain ,可以模拟SQL优化器执行SQL语句 ，从而让开发人员知道自己编写
    b. MySQL 查询优化其会干扰我们的优化

    优化方法，官网： http://dev.mysql.com/doc/refman/5.5/en/optimization.html

查询执行计划: explain + SQL 语句
            explain select * from tb;
     id:编号
    select_type : 查询类型
    table ：表
    type : 类型
    possible_keys : 预测用到的索引  
    key :  实际使用的索引
    key_len ：实际使用索引的长度
    ref : 表之间的引用
    rows : 通过索引查询到的数据量
    Extra : 额外的信息

id 值不同 ： id值越大越优先查询（本质：在嵌套子查询时 ，先查内层，再查外层）
id值相同 ，又有不同 ： id 值越大越优先 ； id值相同，从上往下 顺序执行

select_type :
primary 包含子查询SQL中的主查询（最外层）
SUBQUERY　: 包含子查询SQL中的 子查询 （非最外层）
simple : 简单查询(不包含子查询 ， union)
derived : 衍生查询

union result 告知开发人员，哪些表之间存在union查询

type : 索引类型， 类型
    system>const>eq_ref> ref >range>index> all
其中system ，const 只是理想情况； 实际能达到 ref > range

system(忽略) : 只有一条数据的系统表 ; 或衍生表只有一条数据的主查询
const : 仅仅能查到一条数据的SQL 用于Primary key 或unique 索引 （类型与索引类型有关）
explain select tid from test01 where tid = 1 ;
alter table test01 drop primary key ;
create index test01_index on test01(tid) ;

eq_ref 唯一性索引 : 对于每个索引键的查询, 返回匹配唯一行数据（有且只有一个 ,不能多 ，不能0）
select ... from .. where id = ... 常见于唯一索引和主键索引
alter table teacherCard add constraint pk_tcid primary key(tcid);
alter table teacher add constraint uk_tcid unique index(tcid);

explain select t.tcid from teacher t ,teacherCard tc where t.cid = tc.tcid ;
以上SQL ，用到的索引键的拆线呢，返回匹配的所有行（0，多）
alter table teacher and index index_name (tname);
explain select * from teacher where tname = 'tz'

range : 检索指定范围的行 ，where后面是一个范围查询
（between and ，>< >= ,or ,in 有时候索引会失效，从而转为all）
alter table teacher add index tid_index(tid);
explain select t.* from teacher t where t.tid in(1,2);
explain select t.* from teacher t where t.tid <3;

index 查询全部索引中数据
explain select tid from teacher ; -- tid 是索引 ，只需要扫描索引表，不需要所有表中的数据

all 查询全部表中的数据
explain select cid from course ; -- cid 不是索引，需要全表所有 ，即需要表中的所有数据

system/const : 结果只有一条数据
eq_ref : 结果多条； 但是每条数据是唯一的;
ref: 结果多条；但是每条数据是是0或多条;
(4) possible_keys : 可能用到的索引，是一种预测，不准。
explain select t.tname ,tc.tcdesc from teacher t ,teacherCard tc
where t.tcid =tc.tcid 
and t.tid =(select c.tid  from course c where cname = 'sql');

(5) key : 实际使用到的索引
key_len : 索引的长度 ;
作用 : 用于判断复合索引是否被完全使用 （a,b,c）
create table test_kl(
name char(20) not null default ''
);
alter table test_kl add index index_name(name);
explain select * from test_kl where name = ''; -- key_len : 60
在utf8 : 1个字符站3个字节

alter table test_kl add column name1 char(20) ; -- name1可以为null

alter table test_kl add index index_name1(name1);
explain select * from test_kl where name1 = '';
--如果索引字段 可以为Null 则会使用1个字节用于标识。
drop index index_name on test_kl;
drop index index_name1 on test_kl;

增加一个复合索引
alter  table test_kl add index name_name1_index(name,name1);
explain select * from test_kl where name = '' ; --121
explain select * from test_kl where name = '' ; --60

alter table test_kl add column name2 varchar(20); --可以为Null
alter table test_kl add index name2_index(name2);

explain select * from test_kl where name2  = ''; --63
用2个字节 标识可变长度

utf8 : 1个字符3个字节
gbk 1个字符2个字节

ref ： 注意与type中的ref值区分。
    作用: 指明当前表所 参照的 字段。
    select where a.c =b.x ；（其中b.x是常量 ， const）
    alter table course add index tid_index(tid);
    explain select * from course c,teacher t where c.tid = t.tid and t.tname = 'tw';
    rows : 被索引优化查询的 数据个数 （实际通过索引查询到的 数据个数）
    explain select * from course c ,teacher t where c.tid = t.tid
    and t.tname = 'tz';

(9)Extra :
using filesort : 性能消耗大 : 需要“额外”的一次排序(查询)

create table test02
(
a1 char(3),
a2 char(3),
a3 char(3),
index idx_a1(a1),
index idx_a2(a2),
index idx_a2(a3)
);

explain select * from test02 where a1 = '' order by a1;
explain select * from test02 where a2 = '' order by a2; -- using filesort
小结 : 对于单索引 ， 如果排序和查找是同一个字段, 则不会出现using filesort; 
如果排序和查找不是同一个字段

复合索引 ： 不能跨列 （最佳左前缀）
drop index idx_a1 on test02;
drop index idx_a2 on test02;
drop index idx_a3 on test02;

alter table test02 add index idx_a1_a2_a3(a1,a2,a3);
    小结  ： 避免where 和 order by 按照复合索引的顺序使用 ， 不要跨列或无序使用。
(ii). using temporary : 性能损耗大 ， 用到了临时表。 一般出现在group by 语句中。
      explain select a1 from test02 where a1 in ('1','2','3') group by a1;
      explain select a1 from test02 where a1 in('1','2','3') group by a2; -- using temporary
      避免 : 查询那些列 ，就根据那些列 group by 
(iii) using index 性能提升 ; 索引覆盖（覆盖索引）。原因: 不读取原文件，只从索引文件中获取数据（不需要回表查询）
（2）只要使用到的列 全部都在索引中就是索引覆盖 using index

例如 ： test02 表中有一个复合索引（a1,a2,a3）
    explain select  a1,a2 from test02 where a1 = '' or a2 = '' ;-- using index
    drop index idx_a1_a2 on test02;
    alter table test02 add index idx_a1_a2(a1,a2);
    explain select a1,a3 from test02 where a1='' or a3 = '';
    如果用到了索引覆盖（using index时）会对possible_keys 和key造成影响:
    a.如果没有where ,则索引只出现在key中；
    b.如果有where，则索引 出现在key和possible_keys中。

explain select a1,a2 from test02 where a1 = '' or a2 = '';
explain select a1,a2 from test02 ;
(iii) using where (需要回表查询)
       假设age是索引列
    但查询语句 select age,name from where age = ... ,此语句中必须回原表查name
    explain select a1,a3 from test02 where a3 = ''; --a3需要回原表查询
（iv）. impossible where : where 子句永远为false 
    explain select * from test01 where a1 = 'x'
    create table test03
    (
    a1 int(4) not null,
    a2 in(4) not null,
    a3 int(4) not null,
    a4 int(4) not null
    );
    alter table test03 add index idx_a1_a2_a3_4(a1,a2,a3,a4)
        explain select a1,a2,a3

explain select  a1,a2,a3,a4 from test03 a1 = 1 and a4 = 4 order by a2 , a3 --不会using temporary

--总结 : i.如果（a,b,c,d）复合索引 和使用的顺序全部一致（且不跨列使用）,则复合索引全部使用。 
select a,c where a = and b = and d =
ii. where 和order by 拼起来, 不要跨列使用
using temporary : 一般出现在group by 语句中; 已经有表了， 但不适用，必须再来一张表。

安装： rpm -ivh rpm 软件名
如果安装时 与 某个软件 xxx冲突， 则需要将冲突的软件卸载掉
yum -y remove xxx
安装时 有日志提示我们可以修改密码 ：/usr/bin/mysqladmin -u root password 'new-password'

注意 : 
    如果提示 "GPG keys..." 安装失败，解决方案:
    rpm -ivh rpm 软件名 -- force --nodoeps
验证 :
    启动mysql应用： service mysql start
    关闭 ： service mysql stop
    重启 ： service mysql restart

报错 : "/var/lib/mysql/mysql.sock不存在"
    -- 原因 ： 是Mysql 服务器没有启动
    启动服务 : 1.每次使用前 手动启动服务  /etc/init.d/mysql start
        2.开机自启 chkconfig mysql on , chkconfig mysql off
        检查开机是否自动启动 : ntsysv
给MySQL的超级管理员root增加密码 : /usr/bin/mysqladmin -u root password root

登陆 :
mysql -u root -p 
数据库存放目录 ：
    ps -ef | grep mysql  可以看到
    数据库目录 : datadir = /var/lib/mysql
    pid 文件目录 ： -- pid-file = /var/lib/mysql/bigdata01.pid
    MySQL核心目录 : 
    /var/lib/mysql: mysql 安装目录
    /usr/share/mysql : 配置文件
    /usr/bin : 命令目录 （MySQLadmin ,mysqldump 等）
    /etc/init.d/mysql 启停脚本
MySQL 配置文件
    my-huge.cnf    高端服务器 1-2G内存
      my-large.cnf    中等规模
    my-medium.cnf    一般
    my-small.cnf    较小
    但是，以上配置文件mysql 默认不能识别，默认只能识别  /etc/my.cnf

    但是，以上配置文件mysql 默认只能识别 /etc/my.cnf
    采用 my-huge.cnf
    cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
    注意 ： mysql5.5 默认配置文件 /etc/my.cnf; mysql5.6 默认配置文件/etc/mysql-default.cnf
    mysql字符编码 :
    show variables like 'char';
    可以发现部分编码是latin, 需要统一设置为utf-8
    设置编码 :
    vi /etc/my.cnf:
    mysql    
    default -character-set =utf8
    client
    default -character-set = utf8
    mysqlid
    character_set_server = utf8
    character_set_client = utf8
    collation_server = utf8_general_ci
重启Mysql service mysql restart
    character_set_server = utf8
    character_set_client = utf8 
    collation_server = utf8_general_ci

    sql : show variables like '%char%'
    注意事项 : 修改编码 只对 “之后“ 创建的数据库生效 因此 我们建议 在mysql安装完毕后， 第一时间 ：
    mysql : 清屏 ctrl+L  , system clear

原理
    MYSQL逻辑分层 
        InnoDB : 事物优先 （适合高并发操作 ; 行锁）
        MyISAM : 性能优先 （表锁）
    查询数据库引擎 ： 支持哪些引擎？  show engines ;
    查看当前使用的引擎  show variables like '%storage_engine%'
    指定数据库对象的引擎 : 
    create table tb (
        id int(4) auto_increment ,
        name varchar(5),
        dept varchar(5),
        primary key(id)
    )ENGINE=MyISAM AUTO_INCREMENT =1
    DEFAUT CHARSET = utf8 ;

3.SQL优化
    原因 : 性能低 ， 执行时间太长 ，等待时间太长 SQL语句欠佳(连接查询)，索引失效， 服务器参数设置不合理（缓冲，线程数）

    sql编写过程:
        select  dinstinct  ..  from  ..  join .. on  ..  where
        ..  group by 
        .. having 
        .. order by 
        .. limit

    解析过程: 
        from  .. on .. join .. where .. group by ... having  .. select ..dinstinct.. order by limit

http://www.cnblogs.com/annsshadow/p/5037667.html

sql的优化主要就是 在优化索引
    索引 ： 相当于书的目录
    索引 ：index是帮助MYSQL高效获取数据的数据结构，索引是数据结构(树: B 树（默认） ， Hash树 ...)
    索引的弊端 :
      1. 索引本身很大， 可以存放在内存/硬盘(通常为 硬盘)
      2. 索引不是所有情况均适用： a. 少量数据  b.频繁更新的字段 c. 很少使用的字段
      3. 索引会降低增删改的效率（增删改 查）|

     优势 : 1提高查询效率 （降低IO使用率）
              2. 降低CPU使用率（... order by age desc ，因为 B 树索引 本身就是一个 好排序的结构 ，因此在排序时 可以直接使用）
      4.索引
        分类 : 单列 ， age ; 一个表可以多个单值索引 ，name.
        唯一索引  : 不能重复 。 id
        复合索引 ： 多个列构成的索引（相当于 二级目录）
        创建索引 :
            create 索引类型 索引名 on 表（字段）
            单值 ：
            create  index dept_index on tb(dept);
            唯一 :
             create unique index name_index on tb(name) ;
            复合索引 :
            create index dept_name_index on tb(dept,name);

        注意 ： 如果一个字段是 primary key 则改字段默认就是 主键索引
        
        删除索引 : 
        drop index 索引名 on  表 名;
        drop index name_index on tb;
        查询索引 :
        show index from 表名 ;
        show index from 表名  \G    
5.SQL 性能问题 
    a. 分析SQL 的执行计划 ：explain 可以模拟SQL优化器执行SQL语句
    b. MySQL 查询优化其会干扰我们的优化
    优化方法， 官网 https: // dev.mysql.com/doc/refman/5.5/en/optimization.html