mysql的索引、视图、存储过程（自我理解总结）

一、索引

　　索引在MySQL中也叫‘键’或者‘key’，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要，减少IO次数，加快查询。

　　索引的数据类型是b+树，要想理解b+树，最好先依次理解二叉树、平衡二叉树就比较好理解b+树了，所以索引查找数据是从数据块的左边开始匹配，再匹配右边的。

　　索引的分类：

　　　　普通索引(index)：仅仅是加快查询速度

　　　　唯一索引(unique index)：行上的值不能重复

　　        主键索引(primary key)：主键不能重复，主键索引必唯一，但是唯一索引不一定

　　　　全文索引(fulltext index)：检索速度快，多服务器负荷降低

 　　　联合索引：就是把表的多个字段合起来作为一个索引。

　　　　PRIMARY KEY(id,name):联合主键索引

　　　 UNIQUE(id,name):联合唯一索引

　　　 INDEX(id,name):联合普通索引

　　但索引也不会绝对能提高查询效率的，要避免下面几个问题：

　　　　1、范围问题、或者条件不明确，条件中出现这些符号或关键字：>,<,>=,<=,!=,between  and 

　　　  2、like。  like模糊查询会导致查询不走索引进行全局检索。

　　　  3、尽量选择区分度高的字段作为索引，比如我们选择性别作为索引，不是男的就是女的，在查询的时候回得到很多数据，这样也会很慢，而且没有实际意义

　　　  4、索引最好不要参与计算，比如把条件写成where id*3=3000,这样相当于每次都要把id拿来计算一下才比，把所有数据都过一遍，很慢的，但我们写成where id = 3000/3，条件是一样的，但销量就高很多

　　     5、and，or。and和or的逻辑查询会依次进行比较，影响效率。

　　在查询是最好在select前面加上explain,能清楚看到查询语句走了那些索引，来优化sql语句。

二、视图

　　视图：视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。

　　　作用一：

   　　　　 提高了重用性，就像一个函数。如果要频繁获取user的name和goods的name。就应该使用以下sql语言。示例：

       　　　 select a.name as username, b.name as goodsname from user as a, goods as b, ug as c where a.id=c.userid and c.goodsid=b.id;

    　　　　但有了视图就不一样了，创建视图other。示例

       　　　 create view other as select a.name as username, b.name as goodsname from user as a, goods as b, ug as c where a.id=c.userid and c.goodsid=b.id;

    　　　　创建好视图后，就可以这样获取user的name和goods的name。示例：

        　　　select * from other;

    　　　　以上sql语句，就能获取user的name和goods的name了。

　　作用二：

    　　　　对数据库重构，却不影响程序的运行。假如因为某种需求，需要将user拆房表usera和表userb，该两张表的结构如下：

        　　　测试表:usera有id，name，age字段

        　　   测试表:userb有id，name，sex字段

    　　　　这时如果php端使用sql语句：select * from user;那就会提示该表不存在，这时该如何解决呢。解决方案：创建视图。以下sql语句创建视图：

        　　　create view user as select a.name,a.age,b.sex from usera as a, userb as b where a.name=b.name;

        　　　以上假设name都是唯一的。此时php端使用sql语句：select * from user;就不会报错什么的。这就实现了更改数据库结构，不更改脚本程序的功能了。

　　作用三：

   　　　　 提高了安全性能。可以对不同的用户，设定不同的视图。例如：某用户只能获取user表的name和age数据，不能获取sex数据。则可以这样创建视图。示例如下：

        　　  create view other as select a.name, a.age from user as a;

    　　　　这样的话，使用sql语句：select * from other; 最多就只能获取name和age的数据，其他的数据就获取不了了。

　　作用四：

    　　　　让数据更加清晰。想要什么样的数据，就创建什么样的视图。经过以上三条作用的解析，这条作用应该很容易理解了吧

 

三、存储过程

　　存储过程：储存过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向                         对象方法的模拟。它允许控制数据的访问方式。

　　好处：

　　　　　1、使用了存过程，很多相似性的删除，更新，新增等操作就变得轻松了，并且以后也便于管理！

　　　　　2、存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快。  

　　　　　3、存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。     

　　　　　4、存储过程运行比较稳定，不会有太多的错误。只要一次成功，以后都会按这个程序运行。     

　　　　　5、存储过程主要是在服务器上运行，减少对客户机的压力。   

　　　　　6、存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。   

　　 　　  7、存储过程可以在单个存储过程中执行一系列SQL语句。

　　　　   8、存储过程可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

　　要调用存储过程，必须先在数据库定义好存储过程：

　　　　

//定义存储过程

create or replace procedure p_getserialno(storeCode varchar2,tsCode varchar2,purchaseOrderNumber out varchar2)

begin
 select purchaseOrderNumber from PurchaseOrder where storeCode=storeCode and tsCode=tsCode
end;

　　在mapper接口中：

@Select({ "call p_getserialno(#{storeCode,mode=IN,jdbcType=VARCHAR},"
			+ "#{tsCode,mode=IN,jdbcType=VARCHAR},"
			+ "#{purchaseOrderNumber,mode=OUT,jdbcType=VARCHAR})" })
	@Options(statementType=StatementType.CALLABLE)
	void getPurchaseOrderNumber(Map<String,String> params);

　　Impl中：

public String getPurchaseOrderNumber(String storeCode) throws Exception {
		Map<String,String> params=new HashMap<String, String>(3);
		params.put("storeCode", storeCode);
		params.put("tsCode", "CGD");
		purchaseOrderMapping.getPurchaseOrderNumber(params);
		String purchaseOrderNumber=params.get("purchaseOrderNumber");
		return purchaseOrderNumber;
	}

　　存储过程中：

　　　　创建：create procedure sp_name()
　　　　　　　　begin
　　　　　　　　　.........
　　　　　　　　end

　　　　调用：call sp_name()              　　　注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

　　　　删除：drop procedure sp_name　　 注意：不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

　　　　显示所有存储过程：show procedure status

　　　　显示某一个存储过程：show create procedure sp_name

　　　　区块定义：常用
　　　　　　　　　　begin
　　　　　　　　　　　　......
　　　　　　　　　　end;
　　　　　　　　　也可以给区块起别名，如：
　　　　　　　　　　lable:begin
　　　　　　　　　　　　...........
　　　　　　　　　　end lable;

　　　　条件语句：

if 条件 then
statement
else
statement
end if;

　　　　循环语句：

　　　　　　

while循环
    [label:] WHILE expression DO

                   statements

                    END WHILE [label] ;   
loop循环
             [label:] LOOP

                statements

                END LOOP [label];
repeat until循环
            [label:] REPEAT

                statements

                UNTIL expression

                END REPEAT [label] ;

　　

　　　　存储过程对的运算符：

　　　　　　

算术运算符

+     加   SET var1=2+2;       4
-     减   SET var2=3-2;       1
*     乘   SET var3=3*2;       6
/     除   SET var4=10/3;      3.3333
DIV   整除 SET var5=10 DIV 3;  3
%     取模 SET var6=10%3 ;     1

 

比较运算符

>            大于 1>2 False
<            小于 2<1 False
<=           小于等于 2<=2 True
>=           大于等于 3>=2 True
BETWEEN      在两值之间 5 BETWEEN 1 AND 10 True
NOT BETWEEN  不在两值之间 5 NOT BETWEEN 1 AND 10 False
IN           在集合中 5 IN (1,2,3,4) False
NOT IN       不在集合中 5 NOT IN (1,2,3,4) True
=            等于 2=3 False
<>, !=       不等于 2<>3 False
<=>          严格比较两个NULL值是否相等 NULL<=>NULL True
LIKE         简单模式匹配 "Guy Harrison" LIKE "Guy%" True
REGEXP       正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" False
IS NULL      为空 0 IS NULL False
IS NOT NULL  不为空 0 IS NOT NULL True

 

逻辑运算符

与(AND)

或(OR)

异或(XOR)

 

位运算符

|   位或
&   位与
<<  左移位
>>  右移位
~   位非(单目运算，按位取反)

 

存储过程基本函数：

　　字符串类 

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个字符,

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 ) //返回符号,正负或0
SQRT(number2) //开平方

 

日期时间类

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) //分