Mysql知识梳理数据类型//todo存储引擎InnoDBInnoDB是Mysql默认的事务型存储引擎。InnoDB只有MVCC支持高并发，实现了四个标准的隔离级别。默认级别是可重复的InnoDB存储引擎下的表是基于聚集索引构建的，大大提高了主键的查询性能。MyISAM提供了大量的特性，包括全文索引、压缩和空间函数。它不支持事物和行级锁。InnoDB和MyISAM的比较InnoDB：支持事物，在线热备份，行锁MyISAM：支持全文索引，地理空间索引Index索引是帮助MySQL高效获取数据的数据结构，所以索引本质上是一种数据结构索引分类B-树索引B-Tree索引是大多数Mysql存储引擎默认的索引类型。使用B-Tree索引后，不需要扫描全表，只需要查找树，查找速度会快很多。可以指定多个列作为索引Column，多个索引列共同组成keyB-Tree索引适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找另外用于查询，也可以用于排序和分组哈希索引是基于哈希表实现的。优点是查询速度非常快。在Mysql中，只有Memory存储引擎支持哈希索引。空间索引（R-Tree）MyISAM存储引擎支持空间索引，可用于地理数据存储全文索引MyISAM存储引擎支持全文索引，用于在文本中查找关键词而不是直接查找比较索引中的值。索引的优点是加快了数据查询的方式，提高了数据库查询的性能。大大减少了服务器需要扫描的数据量，帮助服务器避免排序和创建。临时表将随机I/O转换为顺序I/O。索引的缺点是索引实际上是一张表，里面存放的是主键和索引字段，指向的是实体表的记录，所以索引列也很占空间。索引虽然大大提高了查询速度，但是会降低更新表的速度，比如对表进行insert、update、delete。因为在更新表的时候，MySQL不仅要保存数据，还要保存索引文件。每次更新添加的索引列的字段，都会调整更新导致的键值变化后的索引信息。索引只是提高效率的一个因素。如果你的MySQL有一个数据量很大的表，你需要花时间研究和建立最好的索引，或者优化查询语句。创建索引的两种方式createindexCREATEINDEXindex_nameONtable_name(column_list)altertableALTERTABLE`table_name`ADDINDEXindex_name(column_list)操作index以文章类型字段为例--创建索引CREATEINDEXidx_typeONarticles(type);--deletetheindexdropindexidx_typeonarticles--查看索引showindexfromarticles何时使用索引一般情况下，where或join子句中出现的列需要加索引。但是，由于MySQL只对<,<=,=,>,>=,between,in，有时like使用索引（使用like时，mysql在查询通配符%和_时不会使用索引）在什么情况下呢？需要创建索引主键？自动创建唯一索引。应该对经常用作查询条件的字段进行索引。应该创建与查询中其他表关联的字段，并且应该索引外键关系。索引）查询中排序的字段，排序后的字段可以通过索引访问，会大大提高排序速度删除和修改表（因为每次增删改查不仅需要对数据进行操作，还需要对索引进行操作),where条件中没有用到的字段数据重复且字段分布均匀，索引的选择性低，即当前字段不重复的索引值与那个相同ofthetable字段中当前字段的记录数的比例。该值越大，越不推荐建索引。创建索引技术，为高维度的列创建索引。一个数据列中唯一值的个数。数字越大，维度越高。重复的数据会将维度缩小到纬度。为高列创建索引。比如user表的age维度就高于gender。这样的低维列不适合索引。对出现在where、on、groupby和orderby中的列使用索引。对较小的数据列使用索引。索引，会使索引文件变小，同时可以在内存中加载更多的索引，构建更长的字符串。使用前缀索引，不要创建太多索引。索引过多会增加额外的磁盘空间，不适合DML操作。速度影响很大，因为每次增删改查都要重建索引。使用复合索引可以减小文件索引的大小，使用时速度优于多个单列索引。使用索引的注意事项索引不会包含Null值的列，所以我们在设计数据库的时候不要让字段的默认值为NULL。使用短索引来索引字符串列。如果可能，您应该指定一个前缀长度索引列来对MySQL查询进行排序。只使用了一个索引，所以如果where子句已经使用了索引，那么orderby中的列将不会使用索引。因此，如果数据库默认的排序可以满足要求，就不要使用排序操作；尽量不要包含多列的排序，如果有必要，最好为这些列创建复合索引。在不对索引列进行操作的情况下构建组合索引时，使用最左边区分度最高的索引来优化独立列。查询时，索引不能是表达式的一部分，也不能是函数的参数，否则索引前缀索引不能用于blob、text、varchar类型的列，必须使用前缀索引，并且只索引第一部分人物。多列索引使用多列作为条件查询，使用多列索引的性能优于使用单列索引。索引列的顺序写在查询语句的时候，把选择性强的列放在join语句的前面。leftjoin是由leftside决定的，leftside肯定有，所以rightside是我们的重点，要在rightside建索引。当然，如果索引在左边，可以使用rightjoin。尽量减少Join语句中NestedLoop的循环次数：“总是用小结果集来驱动大结果集”，避免索引失败最佳左前缀规则：如果查询中使用了多个索引列，遵循最左前缀规则，参考从索引最左边的前列开始的查询，而不是跳过索引中的列。对索引列什么都不做（计算，函数，（自动/手动）类型转换）会导致索引失效，转为全表扫描。where条件的列=的判断放在比较运算符>、<等左边，而放在比较运算符右边的索引会失效。例如：select*fromuserwhereusername="saboran"andage>18andmobile="18862612345"其中username、age、mobile都有索引，但只有username和age的索引才会生效。select查询中不使用移动索引时，尽量减少select*操作，使用!=或<>时将*替换为必填字段，索引无法使用，会导致全表扫描为null和不为空。like以通配符开头的索引，mysql索引会失效，变成全表扫描。所以，如果要两边都用通配符，最好匹配右边的像'tssk%'这样的通配符如果匹配的话，把like条件放在最后一个。例如：从a=3和b=4的用户中选择年龄，c喜欢“%abcd%”；这样如果a,b,c有索引的话，a,b可以用，c不能用字符串不加单引号，索引会用less失败，或者用它连接时，索引会失败避免子查询，一般建议使用join对于单键索引，尽量为当前查询语句选择过滤性较好的索引作为查询条件在选择组合索引时，应将当前查询中过滤性最好的索引放在最前面where条件的更好。可以通过分析统计信息和调整查询的措辞来达到选择合适索引的目的。查询性能OptimizeExplain分析SQL语句。分析结果中比较重要的字段有：select_type：查询类型，包括简单查询、联合查询和子查询key：使用索引行：扫描行数减少，返回列慢。查询的主要原因是Accessingtoomuchdata，除了访问了太多的行，还包括访问了太多的列。最好不要使用select*语句，而是选择查询的列，减少查询的行。最好使用limit语句来获取你想要的行，也可以创建索引来减少条件语句的全表扫描。常用函数数学函数ABS(x)//返回x的绝对值selectabs(age)fromuserslimit1;--18BIN(x)//返回x的二进制数selectbin(age)fromuserslimit1;--10010CEILING(x)//返回大于x的最小整数值SELECTCEILING(19.1);--20FLOOR(x)//返回小于x的最大值SELECTfloor(19.1);--19RAND()//返回0到1之间的随机数SELECTrand();--0.8320153586864615randomnumberROUND(x,y)//返回参数x四舍五入的y位十进制值SELECTROUND(100.123456,3);--100.123聚合函数（常用于groupby子句的select查询）AVG(col)//返回指定列的平均数selectavg(age)fromusers;--14.0000COUNT(col)//返回指定列中非空值的个数SELECTcount(id)fromusers;--2MIN(col)//返回指定列的最小值selectmin(age)fromusers;--10MAX(colcol)//返回指定列的最大值selectmax(age)fromusers;--18SUM(col)//返回指定列所有值的总和selectsum(age)fromusers;--28GROUP_CONCAT(col)//返回连接属于一个组的列值形成的结果selectGROUP_CONCAT(age)fromusers;--18,20stringfunctionCONCAT(s1,s2,s3,sn)//将s1,s2,s3,sn连接成一个字符串selectCONCAT(id,age,name)fromuserslimit1;--118安小霞CONCAT_WS('|')//将s1,s2,s3,sn拼接成一个字符串，并使用|分开，|可以用任意分隔符代替SELECTCONCAT_WS('|',id,name,age)fromuserslimit1;--1|安小霞|18日期时间函数CURDATE()/CURRENT_DATE()//返回当前日期SELECTCURRENT_DATE();--2018-03-08CURTIME()/CURRENT_TIME()//返回当前时间SELECTCURRENT_TIME();--08:54:15DATE_FORMAT(date,fmt)//根据fmtformat格式化日期SELECTDATE_FORMAT(CURRENT_DATE(),'%Y/%m/%d');--2018/03/08DAYOFWEEK(date)//返回日期为星期几，从0开始，0代表第一天SELECTDAYOFWEEK(CURRENT_DATE());--5DAYOFMONTH(date)//返回日期是当月的第几天SELECTDAYOFMONTH(CURRENT_DATE());--8DAYOFYEAR(date)//返回一年中的日期SELECTDAYOFYEAR(CURRENT_DATE());--67DAYNAME(date)//返回日期的星期名称SELECTDAYNAME(CURRENT_DATE());--ThursdayFROM_UNIXTIME(timestimps,fmt)//将时间戳转换成fmt格式的字符串时间SELECTFROM_UNIXTIME(1520500384,"%Y/%m/%d");--2018/03/08HOUR(time)//返回时间的小时值(0-23)SELECTHOUR('20:10');--20MINUTE(time)//返回时间的分钟值(0-59)SELECTHOUR('20:10');--10MONTH(date)//返回日期(1-12)的月份值SELECTMONTH(CURRENT_DATE());--3MONTHNAME(日期)//返回日期的月份名称SELECTMONTHNAME(CURRENT_DATE());--MarchNOW()//获取当前日期和时间SELECTNOW();--2018-03-0809:26:55WEEK(date)//返回日期是一年中的第几周SELECTWEEK(CURDATE());--9YEAR(date)//返回日期的年份SELECTYEAR(CURDATE());--2018加密函数MD5(str)//计算字符串str的MD5校验值PASSWORD(str)//返回字符串str的加密版本，此加密不可逆SHA(str)//计算安全哈希算法检查字符串str的值控制流函数//todo格式化函数INET_ATON(ip)//返回ip代表的数字INET_NTOA(num)//返回数字代表的ipDistinct去重。单个distinct只能放在开头--会报错selectid,DISTINCT(name)fromtest;--不会报错selectDISTINCT(name)fromtest;
