mysql查询优化

@@@@@sql查询优化：
1，**避免全表扫描  --  索引
（优化查询，优先考虑通过在where 及 order by 涉及的列上建立索引避免全表扫描）
2，查询条件列不能有 NULL--做条件
（放弃使用索引，全局扫描）
select id from t where num is null
**最好不要给数据库留NULL 尽可能使用NOT NULL（备注，描述，评论可为NULL）
	CHAR --NULL占用空间
	VARCHAR--NULL不占用空间
select id from t where num = 0
3，查询条件表达式不能有 != <> （否则全表）
4，查询条件表达式不能有  or  （除非左右都有索引） --替代  union all
（or 一个字段有，一个没有索引全表）
select id from t where num = 10
union all
select id from t where name = 'admin'
5，in 和not in 也要慎用 替代 between exists()  --（否则全表）
对于连续数值 between 替代 in
select id from t where num between 1 and 3
exists 替代 in
select num from a where exists(select 1 from b where num = a.num)
6，like 也要慎用（%column%）特例（仅一个%）
7，避免在 where 子句中对字段进行函数操作
	substring -- 字符串截取
sekect id from t where name like "abc%"
8，where 子句中 = 左侧不能有函数、算数运算或其他表达式运算
select * from emp where sal * 0.5 = 1500  x
9，UPDATE如果只更改1、2个字段，不要更改全部字段
（频繁调用会引起性能消化，带来大量日志文件）
/10，对于多张大数据量（几百条就算大）的表，要先分页再JOIN
否则性能差--京东分页
11，不带任何条件  COUNT  全表扫描
SELECT COUNT(*) FROM TABLE  -- 分页
12，尽量使用数值型字段
       能使用数值就使用数值 （引擎在处理查询和连接时会
                                           逐个比较字符串中每个字符）
13，尽量使用VARCHAR/NVARCHAR 替代 CHAR/NCHAR
      (VARSHAR --255 ;VARCHAR2  --255*2  ;NVARCHAR  -- 255*N)
14，禁止使用 *
15，尽量避免使用游标 游标效率差
16，尽量避免大十五操作，提高系统并发能力
17，避免向客户端返回大量数据，数据量过大考虑需求--百度搜索
---实际案例分析：拆分大的 DELETE 或 INSERT 语句，批量提交 SQL 语句 
如果你把你的表锁上一段时间，比如 30 秒钟，
那么对于一个有很高访问量 的站点来说，
这 30 秒所积累的访问进程/线程，数据库链接，打开的文件数，
可 能不仅仅会让你的 WEB 服务崩溃，还可能会让你的整台服务器马上挂了
WHILE(1){
	//每次只做1000条
	MYSQL_QUERY("DELETE FROM LOGS WHERE
	LOG_DATE <= '2012-11-01' LIMIT 1000"
	");
	IF(MYSQL_AFFECTED_ROWS() == 0){
	//删除完成，退出1
	BREAK;
	}
	//每次暂停一段时间，释放表让其他进程或线程访问
	USLEEP(50000)
	}
@@@@@索引简介
1，索引分为单列索引（一个索引只包含单个列）；
             组合索引（一个索引包含多个列）
！！！一个表可包含多个单列索引（非组合索引）,
2，MYSQL 索引类型包括：
 **1）普通索引（最基本索引，无任何限制）--普通列
	CREATE INDEX indexName ON mytable(username(length));
	CHAR   VARCHAR length 可小于实际长度
	BLOB TEXT 必须指定length
    删除索引
	DROP INDEX [indexName] ON mytable 
**2)主键索引（NOT NULL 建表同时创建主键索引）--主键非空
	CREATE TABLE mytable(ID INT NOT NULL, 
	username carchar(16) NOT NULL, PRIMARY KEY(ID));
**3)唯一索引（类似普通索引，不同点，索引值必须唯一，可以为NULL）
	--若组合索引，列值组合必须唯一（---非主键 可空唯一）
	CREATE unique index indexName ON mytable(username(length));

4)添加FULLTEXT(全文索引)
	mysql > ALTER TABLE 'table_name' ADD FULLTEXT ('column')
5)添加多列索引
	mysql > ALTER TABLE 'table_name' ADD INDEX index_name
		('column1,column2,column3')

@@@事务 ----背 理解背必考
一、事务的基本要素（ACID）---提交 回滚
1,原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，
	不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，
	所有的操作就像没有发生过一样。就是说事务是一个不可分割的整体，
	就像化学中学过的原子，是物质构成的基本单位。
2，一致性（Consistency）：事务开始前和结束后，数据库的完整性约束
	没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。
3，隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，
	不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，
	在A取钱的过程结束前，B不能向这张卡转账。
4，持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，
	不能回滚。
二、事务的并发问题
1，脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取
	到的数据是脏数据。
2、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，
	但是系统管理员B就在这个时候插入了一条具体分数的记录，当管理员A
	改结束后发现还有一条记录每次改过来，就好像发生了幻觉一样，
	这就叫幻读。
3，不可重复读：事务A多次读取同一数据，事务B在事务A多次读取的过程中，
	对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。
*不可重复读侧重修改，幻读侧重新增或删除
*解决不可重复读--锁住满足条件的行，解除幻读需要锁表
三、解决上述问题--MySQL事务隔离级别
读未提交（read--uncommitted） 允许脏读 不可重复读 幻读
不可重复读（read--committed） 不允许脏读，允许不可重复读 幻读
（默认）可重复读（repeatable--read） 允许幻读 不允许 脏读 不可重复读
串行化 （serializable）不允许脏读 不可重复读 幻读
@@@存储过程简介（stored procedure）
  一组为了完成特定功能的sql语句集，经编译后楚村镇数据库中，用户通过指定存储过程的名字
  并给定参数（如果该存储过程带有参数）来调用执行它
  存储过程是可编程的函数，在数据库中创建并保存，可以由sql语句和控制语句组成，
  当在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。
  数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。
存储过程的优点：重点
1）增强sql语言的功能和灵活性
2）标准组件式编程--多次调用
3）软件的操作速度--预编译
4）减少网络流量--降低网络负载
5）作为一种安全机制充分利用--控制访问权限
MySQL 存储过程的创建 
语法 
CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 
数据类型 [,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 
过程体 
DELIMITER //   
	CREATE PROCEDURE myproc(OUT s int)     
		BEGIN       
		SELECT COUNT(*) INTO s FROM students; 
   		END     
// DELIMITER ;
分隔符 MySQL 默认以";"为分隔符，如果没有声明分割符，
则编译器会把存储过程当成 SQL 语句进行处理，
因此编译过程会报错，所以要事先用“DELIMITER //”声明 
当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，
不会执行 这些代码；“DELIMITER ;”的意为把分隔符还原。 
参数 存储过程根据需要可能会有输入、输出、输入输出参数，
如果有多个参数用"," 分割开。
MySQL 存储过程的参数用在存储过程的定义，
共有三种参数类 型,IN,OUT,INOUT:   
IN 参数的值必须在调用存储过程时指定，
在存储过程中修改该参数的值不能 被返回，为默认值   
OUT:该值可在存储过程内部被改变，并可返回   
INOUT:调用时指定，并且可被改变和返回 过程体 过程体的开始与结束使用 BEGIN 与 END 进行标识。 
过程体 过程体的开始与结束使用 BEGIN 与 END 进行标识
IN 参数例子 --不返回
DELIMITER //   
	CREATE PROCEDURE in_param(IN p_in int)     
		BEGIN     
		SELECT p_in;     
		SET p_in=2;     
		SELECT p_in;     
		END;     
		// 
DELIMITER ; 
#调用 
SET @p_in=1; 
CALL in_param(@p_in); 
SELECT @p_in; 
输入参数不返回  输出参数不可输入
OUT 参数例子 
#存储过程 OUT 参数 
DELIMITER //   
	REATE PROCEDURE out_param(OUT p_out int)     
		BEGIN       
		SELECT p_out;       
		SET p_out=2;       
		SELECT p_out;     
		END;    
		// 
DELIMITER ; 
#调用 SET 
SET @p_out=1; 
CALL out_param(@p_out); 
SELECT @p_out; 

INOUT 参数例子 
#存储过程 INOUT 参数 
DELIMITER //   
	CREATE PROCEDURE inout_param(INOUT p_inout int)     
		BEGIN       
		SELECT p_inout;       
		SET p_inout=2;       
		SELECT p_inout;     
		END;     
		// 
DELIMITER ; 
#调用 
SET @p_inout=1; 
CALL inout_param(@p_inout) ; 
SELECT @p_inout; 
INT FLOAD DOUBLE DATE DATETIME YEAR CHAR VARCHAR TEXT
#在 MySQL 客户端使用用户变量 
SELECT 'Hello World' into @x; #前给后
SELECT @x; 
SET @y='Goodbye Cruel World';#后给前
 SELECT @y; 
SET @z=1+2+3; SELECT @z;
#在存储过程中使用用户变量 
CREATE PROCEDURE GreetWorld() 
SELECT CONCAT(@greeting,' World'); 
SET @greeting='Hello'; 
CALL GreetWorld(); 
#在存储过程间传递全局范围的用户变量 
CREATE PROCEDURE p1() SET @last_proc='p1'; 
CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc);
CALL p1(); 
CALL p2(); 
MySQL 存储过程的调用 
用 call 和你过程名以及一个括号，括号里面根据需要，加入参数，
参数包括输 入参数、输出参数、输入输出参数。
 MySQL 存储过程的查询 #查询存储过程
 SELECT name FROM mysql.proc WHERE db='数据库名'; 
SELECT routine_name FROM information_schema.routines 
WHERE routine_schema='数据库名';
 SHOW PROCEDURE STATUS WHERE db='数据库名'; 
#查看存储过程详细信息 
SHOW CREATE PROCEDURE 数据库.存储过程名;   
MySQL 存储过程的修改 
ALTER PROCEDURE 更改用 CREATE PROCEDURE 
建立的预先指定的存储过程，其不 会影响相关存储过程或存储功能。 
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
 characteristic: { CONTAINS SQL | NO SQL | READS SQL DATA |
 MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | 
COMMENT 'string'   
sp_name 参数表示存储过程或函数的名称；  
 characteristic 参数指定存储函数的特性。   
CONTAINS SQL 表示子程序包含 SQL 语句，
但不包含读或写数据的语句；   
NO SQL 表示子程序中不包含 SQL 语句；  
 READS SQL DATA 表示子程序中包含读数据的语句；  
 MODIFIES SQL DATA 表示子程序中包含写数据的语句。   
SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行，
DEFINER 表示 只有定义者自己才能够执行；
INVOKER 表示调用者可以执行。   COMMENT 'string'是注释信息。
 实例： #将读写权限改为 MODIFIES SQL DATA，
并指明调用者可以执行。 ALTER  PROCEDURE  num_from_employee  
 MODIFIES SQL DATA   SQL SECURITY INVOKER ; 
#将读写权限改为 READS SQL DATA，并加上注释信息'FIND NAME'。
 ALTER  PROCEDURE  name_from_employee   READS SQL DATA   
COMMENT 'FIND NAME' ;  
 MySQL 存储过程的删除 DROP PROCEDURE [过程 1[,过程 2…]] 
从 MySQL 的表格中删除一个或多个存储过程。   
MySQL 存储过程的控制语句 变量作用域 
内部变量在其作用域范围内享有更高的优先权，
当执行到 end 时，内部变量消失， 不再可见了，
在存储 过程外再也找不到这个内部变量，
但是可以通过 out 参数或者将其值指派给会话 变量来保存其值。 
#变量作用域 
DELIMITER //   
	CREATE PROCEDURE proc()     
		BEGIN 
      			DECLARE x1 VARCHAR(5) DEFAULT 'outer';         
			BEGIN           
      				DECLARE x1 VARCHAR(5) DEFAULT 'inner';           
      				SELECT x1;      
      			END;
     			SELECT x1;
      		END; 
      		// 
DELIMITER ; 
#调用 
CALL proc(); 
条件语句 
IF-THEN-ELSE 语句 
#条件语句 
IF-THEN-ELSE DROP PROCEDURE IF EXISTS proc3; 
	DELIMITER // 
	CREATE PROCEDURE proc3(IN parameter int)   
		BEGIN     
		DECLARE var int;     
		SET var=parameter+1;     
		IF var=0 THEN       
			INSERT INTO t VALUES (17);     
		END IF ;     
			IF parameter=0 THEN       
				UPDATE t SET s1=s1+1;     
			ELSE       
				UPDATE t SET s1=s1+2;     
		END IF ;   
			END ;  
			// 
	DELIMITER ; 
CASE-WHEN-THEN-ELSE 语句 
#CASE-WHEN-THEN-ELSE 语句 
DELIMITER //   
CREATE PROCEDURE proc4 (IN parameter INT)     
	BEGIN       
		DECLARE var INT;       
		SET var=parameter+1;       
		CASE var         
			WHEN 0 THEN           
				INSERT INTO t VALUES (17);         
			WHEN 1 THEN           
				INSERT INTO t VALUES (18);         
			ELSE           
				INSERT INTO t VALUES (19);       
		END CASE ;     
		END ;  
		 // 
		DELIMITER ;   
循环语句 WHILE-DO…END-WHILE 
DELIMITER //   
CREATE PROCEDURE proc5()     
	BEGIN       
		DECLARE var INT;       
		SET var=0;       
		WHILE var<6 DO         
			INSERT INTO t VALUES (var);         
		SET var=var+1;       
		END WHILE ;     
	END;   
	// 
	DELIMITER ;   
REPEAT...END REPEAT 此语句的特点是执行操作后检查结果 
DELIMITER //   
CREATE PROCEDURE proc6 ()     
	BEGIN       
		DECLARE v INT;       
		SET v=0;       
		REPEAT         
			INSERT INTO t VALUES(v);         
			SET v=v+1; 
        			UNTIL v>=5       
		END REPEAT;     
		END;   
		// 
		DELIMITER ;   
LOOP...END LOOP 
DELIMITER //   
CREATE PROCEDURE proc7 ()     
           BEGIN       
	DECLARE v INT;       
	SET v=0;       
		LOOP_LABLE:LOOP         
			INSERT INTO t VALUES(v);         
			SET v=v+1;         
			IF v >=5 THEN          
		 		LEAVE LOOP_LABLE;         
			END IF;       
		END LOOP;     
            END;   
            // 
            DELIMITER ; 

LABLES 标号 
标号可以用在 begin repeat while 或者 loop 语句前，
语句标号只能在合法的 语句前面使用。可以跳出循环，
使运行指令达到复合语句的最后一步。 
ITERATE 迭代 
通过引用复合语句的标号,来从新开始复合语句 
#ITERATE DELIMITER //   
CREATE PROCEDURE proc8()   
BEGIN     
DECLARE v INT;     
SET v=0;    
LOOP_LABLE:LOOP      
IF v=3 THEN         
SET v=v+1;         
ITERATE LOOP_LABLE;       
END IF;       
INSERT INTO t VALUES(v);       
SET v=v+1;       
IF v>=5 THEN 
LEAVE LOOP_LABLE;       
END IF;     
END LOOP;   
END;   
// DELIMITER ;   
MySQL 存储过程的基本函数 
字符串类 
CHARSET(str) //返回字串字符集
 CONCAT (string2 [,... ]) //连接字串 
INSTR (string ,substring ) //返回 substring 首次在 string 中出现的位置, 不存在返回 0
 LCASE (string2 ) //转换成小写 LEFT
(string2 ,length ) //从 string2 中的左边起取 length 个字符 
LENGTH (string ) //string 长度
 LOAD_FILE (file_name ) //从文件读取内容
 LOCATE (substring , string [,start_position ] ) 同 INSTR,但可指定开始 位置 LPAD
 (string2 ,length ,pad ) //重复用 pad 加在 string 开头,直到字串长度 为 length 
LTRIM (string2 ) //去除前端空格 REPEAT 
(string2 ,count ) //重复 count 次 
REPLACE (str ,search_str ,replace_str ) //在 str 中用 replace_str 替换 search_str 
RPAD (string2 ,length ,pad) //在 str 后用 pad 补充,直到长度为 length 
RTRIM (string2 ) //去除后端空格 
STRCMP (string1 ,string2 ) //逐字符比较两字串大小, 
SUBSTRING (str , position [,length ]) //从 str 的 position 开始,取 length 个字符,
 注：mysql 中处理字符串时，默认第一个字符下标为 1，
即参数 position 必须大 于等于 1 SELECT SUBSTRING('abcd',0,2); 
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的 指定字符 UCASE (string2 ) //转换成大写 RIGHT(string2,length) //取 string2 最后 length 个字符 SPACE(count) //生成 count 个空格 
数学类 
ABS (number2 ) //绝对值 
BIN (decimal_number ) //十进制转二进制 
CEILING (number2 ) //向上取整 
CONV(number2,from_base,to_base) //进制转换 
FLOOR (number2 ) //向下取整 
FORMAT (number,decimal_places ) //保留小数位数 
HEX (DecimalNumber ) //转十六进制 
注：HEX()中可传入字符串，则返回其 ASC-11 码，
如 HEX('DEF')返回 4142143 也可以传入十进制整数，
返回其十六进制编码，
如 HEX(25)返回 19 
LEAST (number , number2 [,..]) //求最小值 
MOD (numerator ,denominator ) //求余 
POWER (number ,power ) //求指数 
RAND([seed]) //随机数 
ROUND (number [,decimals ]) //四舍五入,decimals 为小数位数] 
注：返回类 型并非均为整数， 
SIGN (number2 ) // 正数返回 1，负数返回-1   
日期时间类 
ADDTIME (date2 ,time_interval ) //将 time_interval 加到 date2 
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区 
CURRENT_DATE ( ) //当前日期 
CURRENT_TIME ( ) //当前时间 
CURRENT_TIMESTAMP ( ) //当前时间戳 
DATE (datetime ) //返回 datetime 的日期部分 
DATE_ADD (date2 , INTERVAL d_value d_type ) //在 date2 中加上日期或时间 
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示 datetime 
DATE_SUB (date2 , INTERVAL d_value d_type ) //在 date2 上减去一个时间 
DATEDIFF (date1 ,date2 ) //两个日期差 DAY (date ) //返回日期的天 
DAYNAME (date ) //英文星期 DAYOFWEEK (date ) //星期(1-7) ,1 为星期天 
DAYOFYEAR (date ) //一年中的第几天 
EXTRACT (interval_name FROM date ) //从 date 中提取日期的指定部分 
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串 
MAKETIME (hour ,minute ,second ) //生成时间串 
MONTHNAME (date ) //英文月份名 
NOW ( ) //当前时间 
SEC_TO_TIME (seconds ) //秒数转成时间 
STR_TO_DATE (string ,format ) //字串转成时间,以 format 格式显示 
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差 
TIME_TO_SEC (time ) //时间转秒数] 
WEEK (date_time [,start_of_week ]) //第几周 
YEAR (datetime ) //年份 
DAYOFMONTH(datetime) //月的第几天 
HOUR(datetime) //小时 
LAST_DAY(date) //date 的月的最后日期 
MICROSECOND(datetime) //微秒 
MONTH(datetime) //月 
MINUTE(datetime) //分返回符号,正负或 0 
SQRT(number2) //开平方