By

Contents
  1. 1. Mysql的数据类型
  2. 2. mysql的存储过程
    1. 2.0.1. 简介：
    2. 2.0.2. 什么是存储过程：一组sql语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似java程序中的方法。
    3. 2.0.3. 存储过程的优点：
    4. 2.0.4. 存储过程的创建：
    5. 2.0.5. 存储过程的三个参数：IN OUT INOUT
    6. 2.0.6. 存储过程使用的案例：（只保留近三天的数据）
    7. 2.0.7. mysql存储过程的查询：
  • 3. MYSQL 创建视图
    1. 3.0.1. 什么是视图：
    2. 3.0.2. 视图的特性：
    3. 3.0.3. 视图的作用：
    4. 3.0.4. 视图的优点：
    5. 3.0.5. 视图的缺点：
    6. 3.0.6. 使用场合
  • 4. MYSQL索引
    1. 4.0.1. 普通索引
    2. 4.0.2. 唯一索引
    3. 4.0.3. 主键索引
    4. 4.0.4. 组合索引
    5. 4.0.5. 全文索引
    6. 4.0.6. 索引的优缺点
  • 5. MYSQL优化
    1. 5.0.1. 1.sql语句优化：
    2. 5.0.2. 2.选择合适的数据类型
    3. 5.0.3. 3.选择合适的索引列
    4. 5.0.4. 4.用命令分析

    • Mysql的数据类型

    Mysql中定义的数据类型对数据库的优化是非常重要的。

    mysql支持多种类型，大致可以分为三类：数值，日期/时间和字符串（字符）类型

    1.数值类型

    mysql支持所有的sql数值数据类型。这些数据包括严格数值数据类型（INTEGER,SMALLINT,DECIMAL,NUMERIC）,以及近似数据类型（FLOAT,REAL和DOUBLE）

    img

    2.日期/时间类型

    表示时间值得日期和时间类型为DATETIME,DATE,TIMESTAMP,TIME和YEAR。

    每一个时间类型有一个有效值范值。

    3.字符类型

    CHAR—-定长字符串

    VARCHAR—-变长字符串

    BINARY–

    注意：

    1.Mysql一个汉字占多少长度和编码有关：

    UTF-8:一个汉字=3个字节

    GBK： 一个汉字=2个字节

    2.varchar(n)表示n个字符，无论汉字和英文，mysql都能存n个字符，仅是实际字节长度有所区别。

    3.mysql检查长度，length()函数来实现。

    总结：

    1、整型

    MySQL数据类型 含义（有符号）
    tinyint(m) 1个字节 范围(-128~127)
    smallint(m) 2个字节 范围(-32768~32767)
    mediumint(m) 3个字节 范围(-8388608~8388607)
    int(m) 4个字节 范围(-2147483648~2147483647)
    bigint(m) 8个字节 范围(+-9.22*10的18次方)

    取值范围如果加了 unsigned，则最大值翻倍，如 tinyint unsigned 的取值范围为(0~255)。

    2、浮点型(float 和 double)

    MySQL数据类型 含义
    float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
    double(m,d) 双精度浮点型 16位精度(8字节) m总个数，d小数位

    设一个字段定义为 float(5,3)，如果插入一个数 123.45678,实际数据库里存的是 123.457，但总个数还以实际为准，即 6 位。

    3、定点数

    浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

    decimal(m,d) 参数 m<65 是总个数，d<30 且 d<m 是小数位。

    4、字符串(char,varchar,_text)

    MySQL数据类型 含义
    char(n) 固定长度，最多255个字符
    varchar(n) 可变长度，最多65535个字符
    tinytext 可变长度，最多255个字符
    text 可变长度，最多65535个字符
    mediumtext 可变长度，最多2的24次方-1个字符
    longtext 可变长度，最多2的32次方-1个字符

    char 和 varchar：

    • 1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以 char 类型存储的字符串末尾不能有空格，varchar 不限于此。
    • 2.char(n) 固定长度，char(4) 不管是存入几个字符，都将占用 4 个字节，varchar 是存入的实际字符数 +1 个字节（n<=255）或2个字节(n>255)，所以 varchar(4),存入 3 个字符将占用 4 个字节。
    • 3.char 类型的字符串检索速度要比 varchar 类型的快。

    varchar 和 text：

    • 1.varchar 可指定 n，text 不能指定，内部存储 varchar 是存入的实际字符数 +1 个字节（n<=255）或 2 个字节(n>255)，text 是实际字符数 +2 个字节。
    • 2.text 类型不能有默认值。
    • 3.varchar 可直接创建索引，text 创建索引要指定前多少个字符。varchar 查询速度快于 text, 在都创建索引的情况下，text 的索引似乎不起作用。

    mysql的存储过程

    简介：
    什么是存储过程：一组sql语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似java程序中的方法。

    mysql 5.0以前并不支持存储过程，在5.0以后支持存储过程，可以大大提高数据库的处理过程，同时可以提高

    是主动调用的，功能比触发器更加强大。

    存储过程的优点：

    1.增强SQL语言的功能和灵活性。

    2.标准组件式编程。

    3.较快的执行速度。

    存储过程的创建：
    存储过程的三个参数：IN OUT INOUT

    存储过程根据需要会有输入，输出，输入输出参数，如果有多个参数用， 分割开。存储过程的参数用在存储过程的定义。

    IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

    OUT**:**该值可在存储过程内部被改变，并可返回

    INOUT**:**调用时指定，并且可被改变和返回

    存储过程使用的案例：（只保留近三天的数据）
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    BEGIN
    	#Routine body goes here...
    	DECLARE s int DEFAULT 0;
      DECLARE tableName VARCHAR(32);
    	DECLARE c_tableNames CURSOR FOR select table_name from information_schema.tables where table_schema='ikms_exec' and table_name NOT LIKE 'base%' AND table_name  LIKE '%_his';
    	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;
    	set @timenow=DATE_ADD(NOW(),INTERVAL -3 DAY);
    	OPEN c_tableNames;
    		while s <> 1 DO
    	 		FETCH c_tableNames into tableName;
    			SET @v_sql=CONCAT("DELETE FROM ",tableName," WHERE create_time<'",@timenow,"'");
    			prepare stmt from @v_sql;
    			EXECUTE stmt;
          deallocate prepare stmt;
    	 	end while;
    	CLOSE c_tableNames;
    END

    如何执行存储过程：CALL procedure_name()

    mysql存储过程的查询：

    select name from mysql.proc where db = “数据库名”

    修改 Alert

    删除 DROP

    MYSQL 创建视图

    什么是视图：

    select　语句返回的结果。

    视图的特性：

    视图是若张基本表的引用，一张虚拟表；查询语句执行的结果，不存储具体的数据，只能做查询，基本的表数据发生了变化，视图就跟着发生变化。

    视图的作用：

    方便操作，特别是查询操作，减少负责的ＳＱＬ语句，增强可读性；

    视图的优点：

    1.使用视图可以执行用户数据，聚集特定数据。

    2.使用视图，可以简化数据操作。

    3.使用视图，基表的数据就有一定的安全性，因为视图是虚拟的，物理上是不存在的，只是存储

    4.可以合并分离的数据，创建分区视图。

    视图的缺点：

    1性能差：需要将对视图查询转化成对基表的查询，需要花费一点的时间。

    2.修改权限：如果需要修改视图的某些信息后，数据库必须把它转化成对基本表的某些信息的修改。

    使用场合

    多表复杂查询的时候，返回用户特性信息时。

    MYSQL索引

    mysql目前支持的索引有：普通索引，唯一索引，主键索引，组合索引，全文唯一

    普通索引

    最基本的索引，没有任何限制 INDEX

    唯一索引

    唯一索引的值必须是唯一的，但是允许有空值。如果是组合索引的话，则列值的组合必须是唯一，它有以下几种创建方式。 UNION INDEX

    主键索引

    主键索引是特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般在创建表的时候创建主键索引。

    组合索引

    指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。遵循最左原则。

    全文索引

    主要用来查找文本的关键字，而不是直接与索引中的值相比较。 FULLTEXT

    FULLTEXT索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。FULLTEXT索引配合match against操作使用，而不是一般的where语句加like.目前只支持 char，varchar，text列上可以创建全文索引

    索引的优缺点

    1.虽然索引提高了查询速度，同时会降低更新表的速度。 因为更新表时，不仅要保存数据，还要保存一下索引文件。

    2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。

    MYSQL优化

    1.sql语句优化：

    （1）使用limit对查询结果的记录进行限定
    （2）避免select *，将需要查找的字段列出来
    （3）使用连接（join）来代替子查询
    （4）拆分大的delete或insert语句

    2.选择合适的数据类型

    1）使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob
    （2）使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数
    （3）使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar
    （4）尽可能使用not null定义字段
    （5）尽量少用text，非用不可最好分表

    3.选择合适的索引列

    （1）查询频繁的列，在where，group by，order by，on从句中出现的列
    （2）where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
    （3）长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
    （4）离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高：

    1
    mysql> SELECT COUNT(DISTINCT column_name) FROM table_name;
    4.用命令分析

    （1）SHOW查看状态
    1.显示状态信息

    1
    mysql> SHOW [SESSION|GLOBAL] STATUS LIKE '%Status_name%';

    session（默认）：取出当前窗口的执行
    global：从mysql启动到现在
    （a）查看查询次数（插入次数com_insert、修改次数com_insert、删除次数com_delete）

    1
    mysql> SHOW STATUS LIKE 'com_select';

    （b）查看连接数(登录次数)

    1
    mysql> SHOW STATUS LIKE 'connections';

    （c）数据库运行时间

    1
    mysql> SHOW STATUS LIKE 'uptime';

    （d）查看慢查询次数

    1
    mysql> SHOW STATUS LIKE 'slow_queries';

    （e）查看索引使用的情况：

    1
    mysql> SHOW STATUS LIKE 'handler_read%';

    handler_read_key：这个值越高越好，越高表示使用索引查询到的次数。
    handler_read_rnd_next：这个值越高，说明查询低效。

    2.显示系统变量

    1
    mysql> SHOW VARIABLES LIKE '%Variables_name%';

    3.显示InnoDB存储引擎的状态

    1
    mysql> SHOW ENGINE INNODB STATUS;

    （2）EXPLAIN分析查询

    1
    mysql> EXPLAIN SELECT column_name FROM table_name;

    explain查询sql执行计划，各列含义：
    table：表名；
    type：连接的类型
    ​ -const：主键、索引；
    ​ -eq_reg：主键、索引的范围查找；
    ​ -ref：连接的查找（join）
    ​ -range：索引的范围查找；
    ​ -index：索引的扫描；
    ​ -all：全表扫描；
    possible_keys：可能用到的索引；
    key：实际使用的索引；
    key_len：索引的长度，越短越好；
    ref：索引的哪一列被使用了，常数较好；
    rows：mysql认为必须检查的用来返回请求数据的行数；
    extra：using filesort、using temporary（常出现在使用order by时）时需要优化。
    ​ -Using filesort 额外排序。看到这个的时候，查询就需要优化了
    ​ -Using temporary 使用了临时表。看到这个的时候，也需要优化
    （3）PROFILING分析SQL语句
    1.开启profile。查看当前SQL执行时间

    1
    2
    mysql> SET PROFILING=ON; 
    mysql> SHOW profiles;

    2.查看所有用户的当前连接。包括执行状态、是否锁表等

    1
    mysql> SHOW processlist;

    （4）PROCEDURE ANALYSE()取得建议
    通过分析select查询结果对现有的表的每一列给出优化的建议

    1
    mysql> SELECT column_name FROM table_name PROCEDURE ANALYSE();

    （5）OPTIMIZE TABLE回收闲置的数据库空间

    1
    mysql> OPTIMIZE TABLE table_name;

    对于MyISAM表，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）。
    对于InnoDB表，OPTIMIZE TABLE被映射到ALTER TABLE上，这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。
    只需在批量删除数据行之后，或定期（每周一次或每月一次）进行一次数据表优化操作即可，只对那些特定的表运行。
    （6）REPAIR TABLE修复被破坏的表

    1
    mysql> REPAIR TABLE table_name;

    （7）CHECK TABLE检查表是否有错误

    1
    mysql> CHECK TABLE table_name;
    Contents
    1. 1. Mysql的数据类型
    2. 2. mysql的存储过程
      1. 2.0.1. 简介：
      2. 2.0.2. 什么是存储过程：一组sql语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似java程序中的方法。
      3. 2.0.3. 存储过程的优点：
      4. 2.0.4. 存储过程的创建：
      5. 2.0.5. 存储过程的三个参数：IN OUT INOUT
      6. 2.0.6. 存储过程使用的案例：（只保留近三天的数据）
      7. 2.0.7. mysql存储过程的查询：
  • 3. MYSQL 创建视图
    1. 3.0.1. 什么是视图：
    2. 3.0.2. 视图的特性：
    3. 3.0.3. 视图的作用：
    4. 3.0.4. 视图的优点：
    5. 3.0.5. 视图的缺点：
    6. 3.0.6. 使用场合
  • 4. MYSQL索引
    1. 4.0.1. 普通索引
    2. 4.0.2. 唯一索引
    3. 4.0.3. 主键索引
    4. 4.0.4. 组合索引
    5. 4.0.5. 全文索引
    6. 4.0.6. 索引的优缺点
  • 5. MYSQL优化
    1. 5.0.1. 1.sql语句优化：
    2. 5.0.2. 2.选择合适的数据类型
    3. 5.0.3. 3.选择合适的索引列
    4. 5.0.4. 4.用命令分析