第二节 MySql基础之视图、存储过程和索引

　　　　视图： 也就是一个虚拟表（不是真实存在的），它的本质就是根据SQL语句获取动态的数据集，并为其命名。用户使用时只需要使用命名的视图即可获取结果集，并可以当做表来使用。它的作用就是方便查询操作，减少复杂的SQL语句，增强可读性，更加安全，数据库授权命令不能限定到特定行和特定列，但是通过合理创建视图，可以把权限限定到行列级别。那么对于它的使用场合就是：在我们进行权限控制的时候，不希望用户访问表中的某些敏感信息的列，比如salary...；另一种就是关键信息来源多个复杂关联表，通过创建视图提取我们需要的信息，简化操作。

　　　　①创建视图

-- 创建视图  
-- 格式 create view 视图名称 as SQL语句
create view user_view as select name,age,gender from userinfo

userinfo的表：

生成的视图user_view:

 

　　　　②删除视图：

　　　　　　格式：drop view 视图名称

　　　　　　drop view user_view

　　　　③修改视图：

-- 修改视图
-- 格式 alter view 视图名称 as SQL语句
alter view user_view as select name,age,gender from userinfo where age > 20

修改后的视图user_view:

　　　　案例：视图的查询操作

-- 现有三张表，用户（user），课程（course），用户课程中间表（user——course），表的定义如下：

-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `description` varchar(500) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', 'JAVA', 'JAVA课程');
INSERT INTO `course` VALUES ('2', 'C++', 'C++课程');
INSERT INTO `course` VALUES ('3', 'C', 'C课程');
INSERT INTO `course` VALUES ('4', 'Python', 'Python课程');

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `account` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `others` varchar(200) DEFAULT NULL,
  `others2` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'user1', '小陈', '美国', '1', '1');
INSERT INTO `user` VALUES ('2', 'user2', '小张', '日本', '2', '2');
INSERT INTO `user` VALUES ('3', 'user3', '小王', '中国', '3', '3');

-- ----------------------------
-- Table structure for `user_course`
-- ----------------------------
DROP TABLE IF EXISTS `user_course`;
CREATE TABLE `user_course` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `userid` bigint(20) NOT NULL,
  `courseid` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user_course
-- ----------------------------
INSERT INTO `user_course` VALUES ('1', '1', '2');
INSERT INTO `user_course` VALUES ('2', '1', '3');
INSERT INTO `user_course` VALUES ('3', '2', '1');
INSERT INTO `user_course` VALUES ('4', '2', '2');
INSERT INTO `user_course` VALUES ('5', '2', '3');
INSERT INTO `user_course` VALUES ('6', '3', '2');

生成的表如下：
1，用户表

2，课程表

3，用户课程中间表

-- 需求：查询小张所上课程的所有信息
-- 通过一般的查询来实现
SELECT
    user . name,
    course. name,
    course.description
FROM
    user
LEFT JOIN user_course ON (user.id = user_course.userid)
LEFT JOIN course ON (
    user_course.courseid = course.id
)
WHERE
    user . name= "小张"

从上面，我们发现，如果多处需要这样的功能的时候，我们每次都需要写这么长的SQL语句，现在我们通过视图来完成这个功能

-- 创建视图 view_user_course 
-- 如果这个视图存在,我们先将视图删除再创建
DROP VIEW
IF EXISTS view_user_course;

-- 指定视图的处理算法
CREATE ALGORITHM = UNDEFINED -- 指定视图的创建者
DEFINER = "root"@"localhost" -- 指定视图查询数据时的安全验证方式
SQL SECURITY DEFINER VIEW view_user_course AS (
    SELECT
        user.name as username,
        course.name as cname,
        course.description
    FROM
        (
            (
                user
                LEFT JOIN user_course ON (user.id = user_course.userid)
            )
            LEFT JOIN course ON (user_course.courseid = course.id)
        )
);

-- 通过视图视图来查询
select vuc.username,vuc.cname,vuc.description from view_user_course as vuc where username="小张"

以后如果我们需要查询这样的功能，一句SQL语句就可以了

关于视图的增删改问题：

-- 不能在一张由多张关联表连接而成的视图上做修改操作
-- 那么怎么才能进行增删改操作呢?
-- 视图与表是一对一关系情况:如果没有其他约束(如视图中没有的字段,在基本表中是必填字段),是可以进行增删改操作的
-- 视图与表是一对多关系情况:如果只修改一张表的数据,且没有其他约束,是可以进行改操作的

　　　　存储过程：简单地说就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能。

　　　　①优点：

　　　　　　a：通过把处理封装在容易使用的单元中，简化复杂操作

　　　　　　b：由于不要求反复建立一些列处理步骤，这保证了数据的完整性，如果开发人员和应用程序都使用同一存储过程，则所使用的代码是相同的。还有就是防止错误，需要执行的步骤越多，出错的可能性越大。防止错误保证了数据的一致性。

　　　　　　c：简化对变动的管理，如果表名，列名或业务逻辑有变化，只需要更改存储过程的代码，使用它的人员不会改自己的代码了

　　　　　　d：提高性能，因为使用存储过程比使用单条语句要快

　　　　　　e：存在一些职能用在单个请求的MySql元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

　　　　　　总结起来就是：简单，安全，高性能

　　　　②缺点：

　　　　　　a：不同的数据库，语法差别大，移植困难，换了数据库，需要重写编写

　　　　　　b：不好管理，把过多的业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱，一般存储过程适用于个别对性能要求较高的业务

　　　　③创建

-- 创建
-- 如果存储过程存在,则先删除
drop procedure if exists p1;
-- delimiter 替换默认的输入结束符
delimiter //
-- procedure
create procedure p1()
begin 
        select * from course;
end// 
delimiter ;

-- 通过call来执行
call p1()

对于存储过程，可以接收三个参数：
　　in：仅用于传入参数用
　　out：仅用于返回值用
　　inout：既可以传入又可以当做返回值

drop procedure if exists p1;
delimiter //
create procedure p1(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
begin 
    declare temp1 int;
    declare temp2 int default 0;
    set temp1 = 1;
    set r1 = i1 + i2 + temp1 + temp2;
    set i3 = i3 + 100;
end//

delimiter ;

-- 执行过程
-- @表示变量
set @t1 = 4;
set @t2 = 0;
call p1(1,2,@t1,@t2);

-- 返回值就是inout 和 out对应的结果
select @t1,@t2;

 

 

　　　　注：存储过程的作用就是获取两类数据--普通值和结果集，它可以执行多个sql语句，结果集只能是一个，也就是说，在存储过程中，如果有多个select只会拿一个

　　　　触发器：是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，比数据库本身标准功能有更精细更复杂的数据控制能力。

　　　　①作用：

　　　　　　a：安全性，可以基于数据库的值使用户具有操作数据库的某种权利，比如：可以基于时间限制用户操作，例如不允许下班后和节假日修改数据库数据；可以基于数据库中的数据限制用户的操作，例如不允许股票价格的升幅一次超过10%

　　　　　　b：审计，可以跟踪用户对数据库的操作，比如：审计用户操作数据库的语句；把用户对数据库的更新写入审计表

　　　　　　c：实现复杂的数据完整性规则，比如：实现非标准的数据完整性检查和约束，触发器可产生比规则更为复杂的限制，与规则不同，触发器可以引用列或数据库对象，例如，触发器可回退任何企图吃进超过自己保证金的期货；提供可变的缺省值

　　　　　　d：实现复杂的非标准的数据库相关完整性规则，触发器可以对数据中相关的表进行连环更新，比如：在auths表author_code列上删除触发器可导致相应删除在其它表中的与之匹配的行；在修改或删除时级联修改会删除其它表中的与之匹配的行；在修改或删除时把其他表中与之匹配的行设置NULL；在修改或删除时把其它表中的与之匹配的行级联设成缺省值；触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务，当插入一个与其主键不匹配的外部键时，这个时候触发器就起作用了，例如可以在books.author_code列生成一个插入触发器，如果新值与auths.author_code列中某值不匹配，插入被回退

　　　　　　e：同步实时的复制表中的数据

　　　　　　f：自动计算数据值，如果数据的值达到一定的要求，则进行特定的处理，比如：公司的账号上的资金低于5万元则立即给财务人员发送警告数据

　　　　②创建

-- 创建触发器
-- 格式 
-- create trigger trigger_name trigger_time trigger_event on tb_name for each row trigger_stmt
-- 参数说明:
-- trigger_name:触发器的名称
-- trigger_time:触发时机,分别为before和after
-- trigger_event:触发事件,事件分别为insert,update,delete
-- tb_name:建立触发器的表名
-- trigger_stmt:触发器程序体,触发器可以使用begin和end作为开始和结束,中间包含多条语句
表数据:

/*Table structure for table `t_a` */
DROP TABLE IF EXISTS `t_a`;
CREATE TABLE `t_a` (
  `id` smallint(1) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `groupid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
 
/*Data for the table `t_a` */
LOCK TABLES `t_a` WRITE;
UNLOCK TABLES;
 
/*Table structure for table `t_b` */
DROP TABLE IF EXISTS `t_b`;
CREATE TABLE `t_b` (
  `id` smallint(1) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `groupid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=57 DEFAULT CHARSET=latin1;
 
/*Data for the table `t_b` */
LOCK TABLES `t_b` WRITE;
UNLOCK TABLES;

-- 创建insert触发器
delimiter //
-- 判断数据库是否存在tr_a_insert触发器
drop trigger if exists tr_a_insert//
-- 创建触发器
-- 触发条件为insert成功后进行触发
create definer ="root"@"localhost" trigger tr_a_insert after insert on t_a for each row 
begin 
-- 触发后,同时对t_b新增同步一条数据
insert into t_b set username = NEW.username,groupid = NEW.groupid;
end;
//
delimiter ;

-- 创建update触发器
delimiter //
drop trigger if exists tr_a_update//
create trigger tr_a_update after update on t_a for each row 
begin 
-- 触发后,当t_a表中groupid,username数据有更改时,对t_b表同步一条更新的数据
if NEW.groupid != OLD.groupid or OLD.username != NEW.username then 
    update t_b set groupid=NEW.groupid,username=NEW.username where username=OLD.username and groupid=OLD.groupid;
end if;
end;
//
delimiter ;

-- 创建delete触发器
delimiter //
drop trigger if exists tr_a_delete//
create definer = "root"@"localhost" trigger tr_a_delete after delete on t_a for each row 
begin 
-- t_a表数据删除后,t_b表关联条件相同的数据也同步删除
    delete from t_b where username=OLD.username and groupid=OLD.groupid;
end;
//

delimiter;

insert触发器测试

-- 在t_a表中新增一条数据,然后分别查询t_a/t_b表数据是否数据同步,测试触发器成功标志
-- t_a无论何种情况下,新增了一条或多条记录集时,没有t_b表做任何数据insert操作,但是t_b
-- 同时新增了一样多的多条记录集

-- t_a 新增一条记录集
insert into t_a(username,groupid) values("Jasonhy","1")

-- 查询t_a表
select id,username,groupid from t_a;
-- 查询t_b表
select id,username,groupid from t_b;

t_a表

t_b触发数据

特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行
　　　　触发器是无法有用户直接调用的，当我们对表的增、删、改操作被动引发的

 　　　　事物：MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关：

　　　　　　　 存储引擎：

　　　　　　　　a：MyISAM--不支持事务，用于只读程序提高性能

　　　　　　　　b：InnoDB--支持ACID事务，行级锁，并发

　　　　　　　　c：Berkeley DB--支持事务

　　　　一个事务是一个连续的一组数据库操作，就好像它是一个单一的工作单元。换句话说，永远不会是完整的事务，除非该组内的每个单独的操作是成功的，如果在事务的任何操作失败，则整个事务将会失败

　　　　①特性：有以下四个标准属性的缩写ACID

　　　　　　a：原子型--确保工作单元内的所有操作都成功完成，否则事务将被中止在故障点，和以前的操作将回滚到以前的状态

　　　　　　b：一致性--确保数据库正确的改变状态后，成功提交事务

　　　　　　c：隔离性--使事务操作彼此独立和透明的

　　　　　　d：持久性--确保提交的事务的结果或效果的系统出现故障的情况仍然存在

　　　　MySQL中，事务的开始使用commit和rollback语句开始工作和结束，开始和结束语句的SQL命令之间形成了大量的事务。当一个成功的事务完成后，发出commit命令应使所有参与的表更改才会生效；如果发生故障，应发出一个人rollback命令返回的事务中引用的每一个表到以前的状态。

　　　　可以控制的事务行为称为autocommit设置会话变量，如果autocommit设置为1（默认值），然后每一个SQL语句（在事务与否）被认为是一个完整事务，并承诺在默认情况下，当它完成。autocommit设置为0时，发出set autocommit = 0命令，在随后的一系列语句的作用就像一个事务，直到一个明确的commit语句是，没有活动的提交。

　　　　②事务锁定模式：系统默认，不需要等待某事务结束，可直接查询到结果，但不能进行修改和删除，这种默认模式的缺点就是查询到的结果可能是已经过期的，优点就是不需要等待某事事务结束，可以直接查询到结果。所以我们需要设定一些锁定模式

　　　　　　a：select...lock in share mode（共享锁）--查到的数据，就是数据库在这一时刻的数据（其他已commit事务的结果，已经反应到这里了），select必须等待某个事务结束后才能执行

　　　　　　b：select...for update（排它锁）--比如

　　　　　　　  select * from tb where id < 200;那么id<200的数据，被查询到的数据，都将不能再进行修改，删除，select...lock in share mode操作一直到此事务结束

　　　　　　共享锁和排它锁的区别：在于是否阻断其他客户发出的select...lock in share mode命令

　　　　　　c：insert/update/delete--所有关联数据都会被锁定，加上排他锁

　　　　　　d：防插入锁--例如

　　　　　　　  select * from tb where id > 200;那么id>200的记录无法被插入

　　　　　　e：死锁--自动识别死锁，先进来的进程被执行，后进来的进程收到出错消息，并按rollback方式回滚，innodb_lock_wait_timeout=n来设置等待时间，默认是50秒

　　　　③事务隔离模式：set [session l global] transaction isolation level read uncommitted | read committed | repeattable read | serializable

　　　　　　a：不带session，global的set命令，只对下一个事务有效

　　　　　　b：set session，为当前会话设置隔离模式

　　　　　　c：set global，为以后创建的所有MySQL连接设置隔离模式（当前连接不包括在内）

　　　　　　d：read uncommitted--隔离模式，不隔离select，其他事务未完成的修改（未commit），其结果也考虑在内

　　　　　　e：read committed，把其他事务的commit修改考虑在内，同一事务中，同一select可能返回不同结果

　　　　　　f：repeatable read（默认），不把其他事务的修改考虑在内，无论其他事务是否用commit命令提交过同一个事务，同一个select返回同一结果（前提是本事务，不修改）

　　　　　　g：serializable，和repeatable read类似，给所有的select都加上共享锁

-- 事务执行案例
drop procedure if exists p1;
delimiter //

create procedure p1(
    out p_return_code tinyint
)

begin 
    declare exit handler for sqlexception 
    begin 
        -- error 
        set p_return_code = 1;
        rollback;
    end;

    declare exit handler for sqlwarning 
    begin 
        -- warning 
        set p_return_code = 2;
        rollback;
    end;

    start transaction;
        delete from tb;
        insert into part(name) values("后勤");
    commit;

    -- success
    set p_return_code = 0;
end;
//

delimiter ;

set @i = 0;
call p1(@i);
select @i;

 

　　　　函数：http://www.cnblogs.com/kissdodog/p/4168721.html

　　　　自定义函数：

-- 自定义函数
drop function if exists f1;
delimiter \\
create function f1(
    i1 int,
    i2 int
)
returns int 

begin 
    declare num int;
    set num = i1 + i2;
    return(num);
end;
\\

delimiter;

-- 在查询中使用
select f1(11,nid) as "result",name from tb;

　　　　与存储过程的区别：不能获取结果集，不允许写sql语句，通过returns返回，而存储过程可以写sql语句，通过out或inout返回

　　　　索引：是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据，对于索引，会保存在额外的文件中。是专门用于帮助用户快速查询数据的一种数据结构，类似于字典中的目录，查找字典内容时可以根据目录查找到数据库的存放位置，然后直接获取即可

　　　　①特点

　　　　　　a：由数据库中一列或多列组合而成，作用就是提高对表中数据的查询速度

　　　　　　b：创建和维护索引需要耗费时间，会减慢写入速度

　　　　关于索引一般用到的是B-tree结构，这种结构可以显著的减少定位记录时所经历的中间过程，从而加快存取速度，而B+tree是B-tree的一个变种，MySQL就普遍的使用这种结构。那数据库为什么使用这种结构呢？

　　　　一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上，这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中I/O操作次数的渐进复杂度，换句话说，索引的结构组织要尽量减少查找过程中I/O的存取次数，为了达到这个目的，磁盘按需读取，要求每次都会预读的长度一般为页的整数倍，而且数据库系统将一个节点的大小设为等于一个页，这样，每个节点只需要一次I/O就可以完全载入，每次新建节点时，直接申请一个页空间，这样就保证了一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐，就实现了一个node只需一次I/O，并把B-tree中m值设的非常大，就会让树的高度降低，有利于一次完全载入。

　　　　B-tree又叫做平衡多路查找，一个m阶的B-tree（m叉树）的特性如下：

　　　　　　a：树中每个节点至多有m个孩子

　　　　　　b：除根节点和叶子节点外，其他每个节点至少有ceil（m/2）个孩子

　　　　　　c：若根节点不是叶子节点，则至少有2个孩子（特殊情况：没有孩子的根节点，即根节点为叶子节点，整个树只有一个根节点）

　　　　　　d：所有的叶子节点都出现在同一层，叶子节点不包含任何关键字信息（可以看做是外部节点或查询失败的节点，实际上这些节点不存在，指向这些节点的指针为null）

　　　　　　e：每个非终端节点中包含有n个关键字信息　　　　

　　　　以下是一颗5阶的B-tree：

　　　　

　　　　除根节点和叶子节点外，其他每个节点至少有ceil（5/2）=3个孩子（至少两个关键字），当然最多5个孩子（最多4个关键字），图中关键字为大写字母，顺序为字母升序。

　　　　　　a：插入操作，插入一个元素时，首先在B-tree中是否存在，如果不存在，即在叶子节点处结束，然后在叶子结点中插入该新的元素，注意，如果叶子节点空间足够，这里需要向右移动该叶子节点中大于新插入关键字的元素，如果空间满了以致没有足够的空间去添加新的元素，则将该节点进行分裂，将一般数量的关键字元素分裂到新的其相邻右节点中，中间关键字上移到父节点中（当然，如果父节点空间满了，也同样需要分裂操作），而且当节点中关键字元素右移动了，相关的指针也要向右移，如果在根节点插入新的元素，空间满了，则进行分裂操作，这样原来的根节点中的中间关键字元素向上移动到新的根节点中，因此导致树的高度增加一层。

　　　　　　

　　　　②索引分类

　　　　　　a：普通索引--仅加速查询，数据列可以重复，不做约束

　　　　　　b：唯一索引--加速查找，约束列数据不能重复，可以有null

　　　　　　c：主键索引--加速查找，约束列数据不能重复，不能为null

　　　　　　d：组合索引--多列可以创建一个索引文件，联合唯一，加速查找，约束列数据不能重复，不能为null

　　　　　　e：全文索引--对文本的内容进行分词，进行搜索

　　　　③创建索引　

-- 创建索引
-- 格式
-- 创建表的时候创建
CREATE TABLE tb_name
)(
    字段名称 字段类型 [ 完整性约束条件 ], [ UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [ 索引名称 ] (字段名称 [(长度) ]) [ ASC | DESC ]
);

-- 已存在表创建

1,CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称 ON 表名{字段名称[(长度)] [ASC|DESC]}
2,ALTER TABLE tb_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称(字段名称[(长度)] [ASC|DESC]);

-- 删除索引
DROP INDEX 索引名称 ON tb_name

 

　　　　④普通索引

-- 普通索引
create table tb1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
  index ix_name(name)
)
-- 查看索引
show index from tb1

　　　　⑤唯一索引

-- 创建唯一索引
create table tb1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)

　　　　⑥主键索引

create table tb1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

OR

create table tb1(
    nid int not null auto_increment,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    primary key(nid),
    index ix_name (name)
)

　　　　⑦组合索引

-- 创建表
create table tb1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text
)
-- 创建组合索引
create index ix_name_email on tb1(name,email);

-- 在进行查找的时候，遵循的规则是最左匹配，也就是如果查找条件的最左边不是第一个建立的索引，就不会走索引查找，比如name和email
-- select * from tb1 where name="A" and email="aaa@126.com"  会走索引
-- select * from tb1 where email="aaa@126.com" 不会走索引

 　　　　⑧覆盖索引：应用上索引，并且不会去数据表中操作，不是真实存在，只需要在索引表中就能获取到数据，比如：

　　　　　　select nid from tb1 where nid < 10;

　　　　 ⑨合并索引：比如name和email都是单独索引，在查询的时候通过or，and来连接

　　　　　　select * from tb1 where name="A" or email="aaa@qq.com";

　　　　 ⑩执行计划：在实际的项目开发中，由于我们不知道实际查询的时候数据库里发生了什么事情，数据库软件是怎么扫描表的，怎么使用索引的，因此，一般情况我们的关注就是sql语句的运行时间，在数据库规模不大时，查询是瞬间的，因此，在写sql语句的时候，就很少考虑性能的问题，但是当数据库规模增大如千万、亿的时候，我们运行同样的sql语句却发现迟迟没有结果，这个时候主要是由于数据规模已经限制了查询速度，在MySQL中，提供了explian语法来供我们进行查询分析，比如：

-- 执行计划查询
explain select * from tb1 where name="A"

显示效果图:

表达信息解释;
select_type:查询类型
　　　　　　 a：SIMLE--简单查询
　　　　　　 b：PRIMARY--最外层查询
　　　　　　 c：SUBQUERY--映射为子查询
　　　　　　 d：DERIVED--子查询
　　　　　　 e：UNION--联合
　　　　　　 f：UNION RESULT--使用联合的结果
table:查询的是哪一张表
type：显示连接使用了何种类型，从最差到最好：
　　　　　　　a：all--全盘扫描，对于数据表从头到尾找一遍
　　　　　　　   特别的，如果有limit限制，则找到之后就不会继续往下扫描
　　　　　　　b：index--全索引扫描，对索引从头到尾找一遍
　　　　　　　c：range--对索引进行范围查找
　　　　　　　　 select * from tb1 where name < "Y";
　　　　　　　　 如果是!= 和 > 时，是不走索引的
　　　　　　　d：index_meger--合并索引，使用多个单列索引搜索
　　　　　　　　 select * from tb1 where name="A" or nid in (11,22,33);
　　　　　　　e：ref--根据索引查找一个或多个值
　　　　　　　　 select * from tb1 where name="A";
　　　　　　　f：eq_ref--连接时使用primary key 或 unique类型
　　　　　　　　 select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
　　　　　　　g：const--常量，表最多有一个匹配行，因为仅有一行，在这行的列值可被优化器剩余部分认为是常数，const表很快，因为只读取一次
　　　　　　　　 select nid from tb1 where nid=2;
　　　　　　　h：system--系统，表仅有一行（=系统表）
　　　　　　　　 select * from (select nid from tb1 where nid = 1) as A;
possible_keys:可能使用的索引
key：真实使用的索引
key_len：MySQL中使用索引字节长度
rows：MySQL估计为了找到所需的行而要读取的行数 --只是预估值
extra：包含MySQL解决查询的详细信息

extra对应的值：

“Using index”
            此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
        “Using where”
            这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
        “Using temporary”
            这意味着mysql在对查询结果排序时会使用一个临时表。
        “Using filesort”
            这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
        “Range checked for each record(index map: N)”
            这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。

 

 　　　　⑪如何命中索引

　　　　　　a：在like语句中，%前面有具体值的时候，能够命中索引，后边有的话，不会命中索引

　　　　　　　　select * from tb1 where name like "a%"; -- 命中

　　　　　　　   如果是"%a"就不会命中了

　　　　　　b：在查询的时候，不要通过函数计算，这样不会走索引，应该先进行函数操作之后，再进行sql操作

　　　　　　c：or

　　　　　　　　 select * from tb1 where nid=1 or email="aaa@126.com";

　　　　　　　  特别的，当or条件中有未建立索引的列才失效

　　　　　　d：类型不一致，如果列是字符串类型，传入条件是必须用引号引起来，当数据类型不一致，也不会走索引
　　　　　　e：!= 如果是主键，会走索引

　　　　　　f： > 如果是主键或者索引是整数类型，会走索引

　　　　　　g：order by，当根据索引排序的时候，选择的映射如果不是索引，则不走索引

　　　　　　　　如果对主键排序，则还是会走索引

　　　　⑫ 慢日志查询

-- 配置MySQL自动记录慢日志

slow_query_log = OFF                            是否开启慢日志记录
long_query_time = 2                              时间限制，超过此时间，则记录
slow_query_log_file = /usr/slow.log        日志文件
log_queries_not_using_indexes = OFF     为使用索引的搜索是否记录


注：查看当前配置信息：
　　     show variables like '%query%'
     修改当前配置：
　　　　set global 变量名 = 值

　　　　其他注意事项：

　　　　①避免使用select * 

　　　　②count（1）或count（列）代替count（*）

　　　　③创建表时，尽量使用char代替varchar

　　　　④表的字段顺序固定长度字段优先

　　　　⑤组合索引代替多个单列索引（经常使用多个条件查询时）

　　　　⑥尽量使用短索引

　　　　⑦使用连接（join）来代替子查询（sub-queries）

　　　　⑧连表时注意条件类型一致

　　　　⑨索引散列值（重复少）不适合建索引，比如：性别