MySQL介绍及sql语句

1、 数据类型的优化 创建高性能的索引 MySQL语句的优化MySQL基础 一、选择优化的数据类型更小的通常更好。简单就好。尽量避免NULL。1、整数类型存储整数，有这几种：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别使用8，16，24,32,64为存储空间。存储的值范围从-2(N-1)到2(N-1)-1，其中N是存储空间的位数。UNSIGNED属性表示不允许负值。例如，TINYINT存储范围是-128 127,TINYINT UNSIGNED存储范围是0 255。2、实数类型实数是带有小数部分的数字。它们不仅可以存储小数部分，也可以使用DECIMAL存储比BI

2、GINT还大的整数。MySQL数据类型的优化3、字符串类型(1) VARCHAR和CHAR类型VARCHAR是存储可变长字符串。列的长度小于或等于255字节，则使用1字节表示，否则用两个字节。使用：字符串列的最大长度比平均长度大很多；列的更新很少；使用像UTF-8复杂的字符集，每个字符使用不同的字节数进行存储。CHAR类型是定长的：适合存储很短的字符串，或者值都接近同一个长度。例如：密码的MD5值，也适合经常变更的数据，不容易产生碎片。VARCHAR会删除末尾空格，CHAR不会。(2) BINARY和VARBINARY二进制字符串，存储的是字节码。 (3) BLOB和TEXT类型字符串数据类型

3、，分别是二进制和字符。二进制类型包括：TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB，LONGBLOB。字符类型包括：TINYTEXT，SMALLTEXT，TEXT，MEDIUMTEXT，LONGTEXT；MySQL数据类型的优化3、字符串类型(4)使用枚举(ENUM)代替字符串类型枚举列把不重复的字符串存储成一个预定义集合，把每个值在列表中的位置保存为整数。CREATE TABLE enum_test( e ENUM(autohome,bitauto,pcauto) NOT NULL);INSERT INTO enum_test(e) VALUES (autohome)

4、 ,(pcauto), (bitauto) ;SELECT e+0 FROM enum_test;+-+|e|+-+| 1 | 3 | 2 |+-+枚举字段是按照内部存储的整数而不是字符串进行排序的。枚举的缺点是，字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE。MySQL数据类型的优化4、日期和时间类型MySQL能存储的最小时间粒度为秒。(1) DATETIME它能保存从1001到9999年，精度为秒。它把日期和时间封装为YYYYMMDDHHMMSS整数中。其中，DATE只保存日期YYYYMMDD，不保存时间。(2) TIMESTAMP它只能表示从1970到2038年的时间

5、。它显示的值依赖于时区。5、选择标识符一般我们可能用标识列与其他值进行比较(关联操作中)，或通过标识列寻找其他列。所以为标识列选择合适的数据类型很重要。比如，在关联表中对应的列要一样的类型。整数通常是最好的选择，它们很快并且可以使用AUTO_INCREMENT自增功能。MySQL数据类型的优化 二、MySQL scheme设计中的缺陷太多的列太多的关联：MySQL每个关联操作最多61张表，单个查询最好在12个表以内。全能的枚举：ENUM( ,0,1,2,3,.30) 三、范式和反范式1、范式的有点和缺点优点：范式化的更新操作快。 很少的重复数据，修改时只需要修改很少的数据。 缺点：需要关联表操

6、作。2、反范式的有点和缺点优点：不需要关联表，单独的表也能使用更有效的索引策略。缺点：一个表中会存在冗余的数据。MySQL数据类型的优化范式化和反范式化例子：范式化结构：SELECT message_text,user_name FROM message INNER JOIN user ON message.user_id=user.id WHERE user.account_type=VIP ORDER BY time;MySQL需要扫描message表的time字段索引。然后去user表检查是不是VIP，如果只有小部分是VIP，那么这种效率是低效的。反范式化结构：user_meaasge表

7、，加索引(account_type ,time)SELECT message_text,user_name FROM user_message WHERE account_type=VIPORDER BY time;3、混用范式化和反范式化比如user表和message表都存储account_type字段。但这样更新account_type操作代价就高了，需要同时更新两张表。MySQL数据类型的优化索引是用来快速地查找具有特定值的记录的一种数据结构，它是在存储引擎中实现的。如果没有建立索引，SQL查询时要从表的第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。索引优化应该是对查询性能

8、优化最有效的手段了。 一、索引的类型1、B-Tree索引默认就是B-Tree索引，它把所有的值都是按顺序存储的。(1) 全值匹配全值匹配指的是和索引中的所有列进行匹配。(2) 匹配最左前缀使用索引的第一列。(3) 匹配列前缀可以只匹配某一列的值的开头部分。(4) 匹配范围值可以查找某一列的值的一定范围的值。(5) 精确匹配某一列并范围匹配另外一列第一列全匹配，第二列范围匹配。创建高性能的索引下面是一些B-Tree索引的限制：(1) 如果不是按照索引的最左列开始查找，则无法使用索引。Index(a,b,c)(2) 不能跳过索引的列。 Index(a,b,c)(3) 如果查询中有某个列的范围查询，

9、则其右边所有列都无法使用索引优化。 index(a,b,c) a=1 AND b LIKE J% AND C = 2016。2、哈希索引哈希索引基于哈希表实现。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希索引将所有的哈希码存储在索引中，并在哈希表中保存指向每个数据行的指针。MySQL中只有Memory引擎显示支持哈希索引。+-+-+ 假设索引使用哈希函数f()，它返回下面的值：|fname |lname| f(Arjen ) = 2323 +-+-+ f(Peter ) = 8784 槽(Slot) 值(Value)|Arjen |Lentz | f(Vadim ) = 24

10、58 2323 指向第1行的指针|Peter |Zaitsev| 则哈希索引的数据结构是： 2456 指向第3行的指针|Vadim |Tkache| 8784 指向第2行的指针创建高性能的索引 二、高性能的索引策略1、独立的列独立的列指索引列不能是表达式的一部分，也不能是函数的参数。SELECT actor_id FROM actor WHERE actor_id+1 = 4;SELECT actor_id FROM actor WHERE TO_DAYS(CURRENT_DATE) TO_DAYS(date_col) DATE_SUB(NOW(),INTERVAL 7 DAY) AND ag

11、e BETWEEN 18 AND 25。last_online和age列两个范围条件，但是无法同时使用它们进行索引。 一、MySQL查询速度慢1、查询性能低下最基本的原因是访问的数据太多，向数据库请求了不需要的数据。一些典型案例：(1)、查询不需要的数据，例如，查询出100条数据只看前10条，此时可以在查询后加上LIMIT 10 条件。(2)、多表关联时返回全部列 select * from tableA inner join tableB USING(id) where正确的是: select tableA.* from tableA inner join tableB USING(id)

12、where(3)、总是取出全部列每次看到select *的时候要考虑下是否真的需要查询全部列数据。这样会让优化器无法完成索引覆盖扫描(覆盖索引)，还会为服务器带来额外的I/O、内存和CPU消耗。2、MySQL是否在扫描额外的记录(1)、扫描的行数和返回的行数(2)、扫描的行数和访问类型(索引、非索引)MySQL语句的优化 二、重构查询方式1、一个复杂大查询OR多个简单查询2、切分查询：将大查询切分成小查询，每个查询完全一样且只完成一小部分查询功能(锁住数据、占满整个事务日志、消耗系统资源、阻塞很多小查询)DELETE FROM messages WHERE created DARE_SUB(N

13、OW(), INTERVAL 3 MONTH);DELETE FROM messages WHERE created DARE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 1000;3、分解关联查询很多高性能的应用会把关联查询进行分解.先对每一个表进行单表查询再将结果在应用程序中关联SELECT * FROM tag JOIN tag_post ON tag_post.id = tag.id JOIN post ON tag_post.id = post.id WHERE tag.tag = mysql;改成 SELECT * FROM tag WHERE tag =

14、 mysql; SELECT * FROM tag_post WHERE tag_id = 1234;SELECT * FROM post WHERE post.id in (123,456,567,9094);分解关联查询的优势：(1) 让缓存的效率更高。应用程序可以方便的缓存单表查询对应的结果对象。(2) 将查询分解后，执行单个查询可以减少锁的竞争。(3) 可以减少冗余记录的查询。MySQL语句的优化MySQL语句的优化 三、查询执行的基础下面是MySQL执行一个查询的过程：(1) 客户端发送一条查询给服务器(2)服务器先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下

15、一阶段。(3)服务器进行SQL解析、预处理，再由优化器生成对应的执行计划。(4)MySQL根据执行计划，调用存储引擎的API来执行查询。(5)将结果返回给客户端。1.MySQL客户端/服务器通信协议通信协议是”半双工”的，即在任何一个时刻，要么由客户端向服务器发送数据，要么有服务器向客户端发送数据。并且一旦一端开始发送消息，另一端要接收完整个消息才能响应它。 三、查询执行的基础2、查询缓存SQL语句来了后，如果查询缓存是打开的，MySQL会通过一个对大小写敏感的哈希查找来优先检查该SQL语句是否命中查询缓存。有一个字节不同也不会匹配缓存结果。命中缓存后会再检查用户权限，可以的花会直接从缓存中拿

16、到结果返回给客户端。3、查询优化处理查询的生命周期的下一步是将SQL转换成一个执行计划再和存储引擎进行交互。3.1 语法解析器和预处理SQL语句被解析成一颗”解析树”。再使用MySQL语法规则验证和解析查询。(关键字和其顺序是否正确等)预处理进一步检查解析树是否合法(表和数据列是否存在 解析名字是否有歧义)MySQL语句的优化 三、查询执行的基础3.2、查询优化器 3.3、MySQL如何执行关联查询MySQL中每一个查询，每一个片段(包括子查询，单表的SELECT)都可能是关联。MySQL关联执行的策略很简单:嵌套循环关联操作，即先在一个表中循环取出单条数据，再嵌套循环下一个表寻找匹配的行，依

17、次下去找到所有表匹配度行。然后根据这些行返回查询中需要的列。例如：查询先查找第一个表记录，再嵌套查询下一个关联表，再回溯到上一个表。SELECT tab1.col1, tab2.col2 FROM tab1 INNER JOIN tab2 USING(col3) WHERE tab1.col1 IN (5,6);假设MySQL按照查询中表的顺序进行关联操作，伪代码如下：outer_iter = iterator over tab1 where col1 IN(5,6)out_row = outer_iter.nextwhile outer_row inner_iter = iterator o

18、ver tab2 where col3 = outer_row.col3 inner_row = inner_iter.next while inner_row output outer_row.col1, inner_row.col2 MySQL语句的优化 三、查询执行的基础3.3、MySQL如何执行关联查询伪代码: inner_row = inner_iter.next end outer_row = outer_iter.nextend另一种可视化查询执行计划的方法是根据优化器执行的路径绘制出对应的“泳道图”。如图所示：MySQL语句的优化4、查询执行引擎存储引擎根据前面的解析和优化阶段

19、生成的执行计划来完成整个查询。5、返回结果给客户端MySQL将结果集返回客户端是一个增量、逐步返回的过程。例如，关联操作查询中，一旦服务器处理完最后一个关联表，开始生产第一条结果时，就可以开始返回结果集了。如果查询可以被缓存，那么MySQL会将结果放到查询缓存中。 四、MySQL查询优化器的局限性1、关联子查询MySQL的一些子查询实现的比较糟糕，特别是where中包含IN()的子查询。我们可以用EXISTS和(LEFT/INNER)JOIN 关联表(有时候性能也是非常好的甚至是最优的)SELECT film_id FROM file WHERE EXISTS(SELECT * FROM fi

20、lm_actor WHEREfilm_id=film_actor.film_id;2、在同一个表上查询和更新 UPDATE tab INNER JOIN (SELECT type,count(*) AS cnt FROM tab GROUP BY type) AS der USING(type) SET t = t;MySQL语句的优化 五、优化特定类型的查询1、优化COUNT()查询COUNT()的两种作用：它可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值非空(不统计NULL)。COUNT(col)时统计的是col有值的结果数。它统计结果集的行数，当它括号内的值不为NULL时，

21、实际上就是在统计行数，直接用COUNT(*)就可以了。(1) 简单的优化如何在一个查询中统计同一个列的不同值的数量，以减少查询的语句？MySQL语句的优化(1) 简单的优化如何在一个查询中统计同一个列的不同值的数量，以减少查询的语句？假设通过一个查询返回各种不同颜色的商品数量。SELECT SUM(IF(color = blue,1,0) AS bule,SUM(IF(color = red,1,0)AS red FROM color_id;SELECT color,COUNT(color) FROM color_id GROUP BY color;2、优化关联查询(1) 确保ON或者USIN

22、G字句中的列有索引。当表A和表B用列c关联时，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建立索引。(2) 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列。这样MySQL才可能使用索引来优化。 MySQL语句的优化MySQL语句的优化1、使用连接(join)来代替子查询 使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作： SQL语句：SELECT * FROM comments2014 WHERE post_id NOT in (SELECT post_id FROM comments2015); 如果使用连接(join)来完成这个查询，速度会快很多。尤其是给post_id字段建立索引的话，性能会更好。但join表时目标字段的数据类型要一致。SQL语句：SELECT * FROM comments20