数据库结构优化、高可用架构设计、数据库索引
一、数据库结构优化1.1 数据库结构优化目的1、减少数据冗余：（数据冗余是指在数据库中存在相同的数据，或者某些数据可以由其他数据计算得到），注意，尽量减少不代表完全避免数据冗余；2、尽量避免数据维护中出现更新，插入和删除异常：总结：要避免异常，需要对数据库结构进行范式化设计。3、节约数据存储空间。4、提高查询效率。1.2 数据库结构设计步骤1、需求分析：全面了解产品设计的存储需求、数据处理需求、数据安全性与完整性；2、逻辑设计（重要）：设计数据的逻辑存储结构。数据实体之间的逻辑关系，解决数据冗余和数据维护异常。数据范式可以帮助我们设计；3、物理设计：表结构设计，存储引擎与列的数据类型；4、维护优化：索引优化、存储结构优化。1.3 数据库范式设计与反范式化传送门：数据库逻辑设计之三大范式通俗理解，一看就懂，书上说的太晦涩1.4 物理设计 相关传送门：MySQL中字段类型与合理的选择字段类型；int(11)最大长度是多少？，varchar最大长度是多少二、高可用架构设计 2.1 读写分离MaxScale：实现MySQL读写分离与负载均衡的中间件利器三、数据库索引优化3.1 两种主要数据结构：B-tree和Hash3.1.1 B-tree结构B-tree索引的限制：3.1.2 Hash结构Hash索引的限制：Hash索引必须进行二次查找Hash索引无法用于排序Hash索引不支持部分索引查找也不支持范围查找Hash索引中Hash码的计算可能存在Hash冲突，不适合重复值很高的列，如性别，身份证比较合适。3.1.3 MySQL常见索引和各种索引区别 PRIMARY KEY（主键索引） ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) UNIQUE(唯一索引) ALTER TABLE `table_name` ADD UNIQUE (`column`) INDEX(普通索引) ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) FULLTEXT(全文索引) ALTER TABLE `table_name` ADD FULLTEXT ( `column` ) 组合索引 ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` ) 普通索引：最基本的索引，没有任何限制唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。主键索引：它 是一种特殊的唯一索引，不允许有空值。全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。3.2 使用索引好处和索引缺陷3.2.1 为什么要使用索引1、索引大大减少了存储引擎需要扫描的数据量；2、索引可以帮助我们进行排序以避免使用临时表；3、索引可以把随机I/O变为顺序I/O。3.2.2 索引不是越多越好1、索引会增加写操作的成本；2、太多的索引会增加查询优化器的选择时间。索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引）并不是越多越好，假如这本书1000页，而有500页是目录，它当然效率低，目录是要占纸张的,而索引是要占磁盘空间的。3.3 索引优化策略3.3.1 索引列上不能使用表达式和函数3.3.2 前缀索引和索引列的选择性Innodb索引列最大宽度为667个字节(utf-8 差不多255个字符),MyIsam索引类宽度最大为1000个字节，于是出现前缀索引，索引的选择性。对于列的值较长，比如BLOB、TEXT、VARCHAR，就必须建立前缀索引，即将值的前一部分作为索引。这样既可以节约空间，又可以提高查询效率。但无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。语法： ALTER TABLE table\_name ADD KEY(column\_name(prefix\_length))如何选择索引列的顺序：1、经常会被使用到的列优先（选择性差的列不适合，如性别，查询优化器可能会认为全表扫描性能更好）；2、选择性高的列优先；3、宽度小的列优先（一页中存储的索引越多，降低I/O，查找越快）；3.3.3 组合/联合索引策略如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。 深入理解请移步：最左前缀原理与相关优化3.3.4 覆盖索引策略跟组合索引有点类似，如果索引包含所有满足查询需要的数据的索引则成为覆盖索引(Covering Index)，也就是平时所说的不需要回表操作。即索引的叶子节点上面包含了他们索引的数据(hash索引不可以)。判断标准：使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询。优点:1、可以优化缓存,减少磁盘IO操作； 2、可以减少随机IO,变随机IO操作变为顺序IO操作； 3、可以避免对InnoDB主键索引的二次查询； 4、可以避免MyISAM表进行系统调用；无法使用覆盖索引的情况：1、存储引擎不支持覆盖索引； 2、查询中使用了太多的列（如SELECT * ）； 3、使用了双%号的like查询（底层API所限制）；mysql高效索引之覆盖索引3.3.5 SQL索引优化总结口诀（套路重点）全值匹配我最爱，最左前缀要遵守； 带头大哥不能死，中间兄弟不能断； 索引列上不计算，范围之后全失效； LIKE百分写最右，覆盖索引不写 *； 不等空值还有or，索引失效要少用； 字符单引不可丢，SQL高级也不难 ；MySQL高级-索引优化3.4 使用索引来优化查询3.4.1 利用索引排序1、group by 实质是先排序后分组，遵照索引的最佳左前缀。；2、索引中所有列的方向(升序、降序)和Order By子句完全一致；3、当无法使用索引列，增大max\_length\_for\_sort\_data参数的设置+增大sort\_buffer\_size参数的设置；4、如果最左列使用了范围，则排序会失效；5、where 高于having，能写在where限定的条件就不要去having去限定了3.5 索引的维护和优化3.5.1 删除重复索引注：主键约束相当于(唯一约束 + 非空约束)一张表中最多有一个主键约束,如果设置多个主键,就会出现如下提示：Multiple primary key defined!!!3.5.2 删除冗余索引检查工具：pt-duplicate-key-checker
面试必备，MySQL索引优化实战总结，涵盖了几乎所
索引的种类众所周知，索引类似于字典的目录，可以提高查询的效率。索引从物理上可以分为：聚集索引，非聚集索引从逻辑上可以分为：普通索引，唯一索引，主键索引，联合索引，全文索引索引优化策略 不要在索引列上进行运算或使用函数在列上进行运算或使用函数会使索引失效，从而进行全表扫描。如下面例子在publish\_time，id列上分别加上索引，publish\_time为datetime类型，id为int类型 -- 全表扫描select * from article where year(publish_time) < 2019 -- 走索引select * from article where publish_time < '2019-01-01' -- 全表扫描select * from article where id + 1 = 5 -- 走索引select * from article where id = 4小心隐式类型转换 假设id为varchar类型 -- 全表扫描select * from article where id = 100 -- 走索引select * from article where id = '100'为什么呢？select * from article where id = 100 -- 等价于select * from article where CAST(id AS signed int) = 100上一条规则说过，不要在索引列上使用函数，隐式类型转换在索引字段上做了函数操作，因此会全表扫描那么如果id是int，执行下面这个语句是否会导致全表扫描呢？select * from article where id = '100'答案是会用到索引 前导模糊查询不会使用索引 -- 全表扫描select * from article where author like '%李'%李,%李%都会导致全表扫描，非前导模糊查询可以使用索引 -- 走索引select * from article where author like '李%' 联合索引最左前缀原则mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整1.将区分度最高的字段放在最左边当不需要考虑排序和分组时，将区分度最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找如果在a b列上建立联合索引，该如何建立，才能使查询效率最高 select count(distinct a) / count(*), count(distinct b) / count(*), count(*) from table执行如下语句，假设3个输出依次为0.001,0.373,16049，可以看到b列的选择性最高，因此将其作为联合索引的第一列，即建立(b, a)的联合索引2.查询时=可以乱序如果建立了联合索引（a, b）。例如下面的2个写法是等价的，因为MySQL会将查询的顺序优化成和联合索引的顺序一致select * from table where a = '1' and b = '1' select * from table where b = '1' and a = '1'3.优化查询，避免出现filesortselect * from table where a = ? and b = ? order by c最左前缀原则不仅用在查询中，还能用在排序中。MySQL中，有两种方式生成有序结果集：通过有序索引顺序扫描直接返回有序数据Filesort排序，对返回的数据进行排序因为索引的结构是B+树，索引中的数据是按照一定顺序进行排列的，所以在排序查询中如果能利用索引，就能避免额外的排序操作。EXPLAIN分析查询时，Extra显示为Using index。所有不是通过索引直接返回排序结果的操作都是Filesort排序，也就是说进行了额外的排序操作。EXPLAIN分析查询时，Extra显示为Using filesort，当出现Using filesort时对性能损耗较大，所以要尽量避免Using filesort对于如下sqlselect * from table where a = ? and b = ? order by c可以建立联合索引（a, b, c） 如果索引中有范围查找，那么索引有序性无法利用，如select * from table where a > 10 order by b索引(a，b)无法排序。放几个例子 -- 使用了a列 where a = 3 -- 使用了a b列 where a = 3 and b = 5 -- 使用了a b c列 where a = 3 and c = 4 and b = 5 -- 没有使用索引 where b = 3 -- 使用了a列 where a = 3 and c = 4 -- 使用了a b列 where a = 3 and b > 10 and c = 7 -- 使用了a b 列 where a = 3 and b like 'xx%' and c = 7 union，or，in都能命中索引，建议使用in select * from article where id = 1 union all select * from article where id = 2 select * from article where id in (1 , 2)新版MySQL的or可以命中索引 select * from article where id = 1 or id = 2效率从高到低为union，in，or。in和union的效率差别可以忽略不计，建议使用in负向条件索引不会使用索引，建议用in负向条件有：!=、<>、not in、not exists、not like 等 -- 全表扫描 select * from article where id != 1 and id != 2知道id的所有取值范围，可以改为类似如下形式 -- 走索引 select * from article where id in (0, 3, 4)建立覆盖索引 众所周知，表数据是放在一个聚集索引上的，而建立的索引为非聚集索引，非聚集索引的叶子节点存放索引键值，以及该索引键指向的主键。一般查找的过程是从非聚集索引上找到数据的主键，然后根据该主键到聚集索引上查找记录，这个过程称为回表，不清楚的看推荐阅读。如有下面这个sqlselect uid, login_time from user where username = ? and passwd = ?可以建立(username, passwd, login\_time)的联合索引，由于 login\_time的值可以直接从索引中拿到，不用再回表查询，提高了查询效率 经常更改，区分度不高的列上不宜加索引更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能。“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算 明确知道只会返回一条记录，可以加limit1当查询确定只有一条记录时，可以加liimit1，让MySQL停止游标移动，提高查询效率select uid from user where username = ? and passwd = ?可改为select uid from user where username = ? and passwd = ? limit 1对文本建立前缀索引用邮箱登录是一个常见的问题，如果对email整个字段建立索引，会让索引变得大且慢 select username from user where email='xxx'; 这时我们可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但这样也会降低索引的区分度。索引的区分度是指，不重复的索引值和数据表的记录总数的比值。索引的区分度越高则查询效率越高，因为区分度高的索引可以让MySQL在查找时过滤掉更多的行。因此我们选择足够长的前缀保证较高的区分度，同时又不能太长（以便节约空间）可以进行如下实验 select count(distinct left(email, 5)) / count(*) as col5, count(distinct left(email, 6)) / count(*) as col6, count(distinct left(email, 7)) / count(*) as col7 from user假设输出依次为0.0305，0.0309，0.0310查询显示当前缀长度达到7的时候，再增加前缀长度，区分度提升的幅度已经很小了，因此创建email(7)的前缀索引即可需要注意的一点是，前缀索引不能使用覆盖索引，因为从索引中获取不到完整的数据，还得回表查询建立索引的列不为NULL只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。因此，在数据库设计时，除非有一个很特别的原因使用 NULL 值，不然尽量不要让字段的默认值为 NULL。分页查询优化MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写，单开一文来讲
一、数学函数ABS(x) 返回x的绝对值BIN(x) 返回x的二进制（OCT返回八进制，HEX返回十六进制）CEILING(x) 返回大于x的最小整数值EXP(x) 返回值e（自然对数的底）的x次方FLOOR(x) 返回小于x的最大整数值GREATEST(x1,x2,...,xn)返回集合中最大的值LEAST(x1,x2,...,xn) 返回集合中最小的值LN(x) 返回x的自然对数LOG(x,y)返回x的以y为底的对数MOD(x,y) 返回x/y的模（余数）PI()返回pi的值（圆周率）RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。ROUND(x,y)返回参数x的四舍五入的有y位小数的值SIGN(x) 返回代表数字x的符号的值SQRT(x) 返回一个数的平方根TRUNCATE(x,y) 返回数字x截短为y位小数的结果二、聚合函数(常用于GROUP BY从句的SELECT查询中)AVG(col)返回指定列的平均值COUNT(col)返回指定列中非NULL值的个数MIN(col)返回指定列的最小值MAX(col)返回指定列的最大值SUM(col)返回指定列的所有值之和GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果三、字符串函数ASCII(char)返回字符的ASCII码值BIT_LENGTH(str)返回字符串的比特长度CONCAT(s1,s2...,sn)将s1,s2...,sn连接成字符串CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果FIND_IN_SET(str,list)分析逗号分隔的list列表，如果发现str，返回str在list中的位置LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果LEFT(str,x)返回字符串str中最左边的x个字符LENGTH(s)返回字符串str中的字符数LTRIM(str) 从字符串str中切掉开头的空格POSITION(substr,str) 返回子串substr在字符串str中第一次出现的位置QUOTE(str) 用反斜杠转义str中的单引号REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果REVERSE(str) 返回颠倒字符串str的结果RIGHT(str,x) 返回字符串str中最右边的x个字符RTRIM(str) 返回字符串str尾部的空格STRCMP(s1,s2)比较字符串s1和s2TRIM(str)去除字符串首部和尾部的所有空格UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果四、日期和时间函数CURDATE()或CURRENT_DATE() 返回当前的日期CURTIME()或CURRENT_TIME() 返回当前的时间DATE_ADD(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值DATE_SUB(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)DAYOFMONTH(date) 返回date是一个月的第几天(1~31)DAYOFYEAR(date) 返回date是一年的第几天(1~366)DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳tsHOUR(time) 返回time的小时值(0~23)MINUTE(time) 返回time的分钟值(0~59)MONTH(date) 返回date的月份值(1~12)MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);NOW() 返回当前的日期和时间QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);WEEK(date) 返回日期date为一年中第几周(0~53)YEAR(date) 返回日期date的年份(1000~9999)一些示例： 获取当前系统时间：SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()); SELECT EXTRACT(YEAR_MONTH FROM CURRENT_DATE); SELECT EXTRACT(DAY_SECOND FROM CURRENT_DATE); SELECT EXTRACT(HOUR_MINUTE FROM CURRENT_DATE);返回两个日期值之间的差值(月数)：SELECT PERIOD_DIFF(200302,199802);在Mysql中计算年龄：SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),'%Y')+0 AS age FROM employee;这样，如果Brithday是未来的年月日的话，计算结果为0。 下面的SQL语句计算员工的绝对年龄，即当Birthday是未来的日期时，将得到负值。SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') -(DATE_FORMAT(NOW(), '00-%m-%d') <DATE_FORMAT(birthday, '00-%m-%d')) AS age from employee 五、加密函数- AES_ENCRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES\_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储AES_DECRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法解密后的结果DECODE(str,key) 使用key作为密钥解密加密字符串strENCRYPT(str,salt) 使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串strENCODE(str,key) 使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储MD5() 计算字符串str的MD5校验和PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。SHA() 计算字符串str的安全散列算法(SHA)校验和示例：SELECT ENCRYPT('root','salt'); SELECT ENCODE('xufeng','key'); SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起 SELECT AES_ENCRYPT('root','key'); SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key'); SELECT MD5('123456'); SELECT SHA('123456');六、控制流函数 MySQL有4个函数是用来进行条件操作的，这些函数可以实现SQL的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台。 MySQL控制流函数：CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真，则返回resultN，否则返回defaultCASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等，则返回resultN，否则返回defaultIF(test,t,f) 如果test是真，返回t；否则返回fIFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1这些函数的第一个是IFNULL()，它有两个参数，并且对第一个参数进行判断。如果第一个参数不是NULL，函数就会向调用者返回第一个参数；如果是NULL,将返回第二个参数。如：SELECT IFNULL(1,2), IFNULL(NULL,10),IFNULL(4*NULL,'false');NULLIF()函数将会检验提供的两个参数是否相等，如果相等，则返回NULL，如果不相等，就返回第一个参数。如：SELECT NULLIF(1,1),NULLIF('A','B'),NULLIF(2+3,4+1);和许多脚本语言提供的IF()函数一样，MySQL的IF()函数也可以建立一个简单的条件测试，这个函数有三个参数，第一个是要被判断的表达式，如果表达式为真，IF()将会返回第二个参数，如果为假，IF()将会返回第三个参数。如：SELECTIF(1<10,2,3),IF(56>100,'true','false');IF()函数在只有两种可能结果时才适合使用。然而，在现实世界中，我们可能发现在条件测试中会需要多个分支。在这种情况下，MySQL提供了CASE函数，它和PHP及Perl语言的switch-case条件例程一样。 CASE函数的格式有些复杂，通常如下所示：CASE [expression to be evaluated] WHEN [val 1] THEN [result 1] WHEN [val 2] THEN [result 2] WHEN [val 3] THEN [result 3] ...... WHEN [val n] THEN [result n] ELSE [default result] END这里，第一个参数是要被判断的值或表达式，接下来的是一系列的WHEN-THEN块，每一块的第一个参数指定要比较的值，如果为真，就返回结果。所有的WHEN-THEN块将以ELSE块结束，当END结束了所有外部的CASE块时，如果前面的每一个块都不匹配就会返回ELSE块指定的默认结果。如果没有指定ELSE块，而且所有的WHEN-THEN比较都不是真，MySQL将会返回NULL。 CASE函数还有另外一种句法，有时使用起来非常方便，如下：CASE WHEN [conditional test 1] THEN [result 1] WHEN [conditional test 2] THEN [result 2] ELSE [default result] END这种条件下，返回的结果取决于相应的条件测试是否为真。 示例：mysql>SELECT CASE 'green' WHEN 'red' THEN 'stop' WHEN 'green' THEN 'go' END; SELECT CASE 9 WHEN 1 THEN 'a' WHEN 2 THEN 'b' ELSE 'N/A' END; SELECT CASE WHEN (2+2)=4 THEN 'OK' WHEN(2+2)<>4 THEN 'not OK' END ASSTATUS; SELECT Name,IF((IsActive = 1),'已激活','未激活') AS RESULT FROMUserLoginInfo; SELECT fname,lname,(math+sci+lit) AS total, CASE WHEN (math+sci+lit) < 50 THEN 'D' WHEN (math+sci+lit) BETWEEN 50 AND 150 THEN 'C' WHEN (math+sci+lit) BETWEEN 151 AND 250 THEN 'B' ELSE 'A' END AS grade FROM marks; SELECT IF(ENCRYPT('sue','ts')=upass,'allow','deny') AS LoginResultFROM users WHERE uname = 'sue';#一个登陆验证 七、格式化函数- DATE_FORMAT(date,fmt) 依照字符串fmt格式化日期date值FORMAT(x,y) 把x格式化为以逗号隔开的数字序列，y是结果的小数位数INET_ATON(ip) 返回IP地址的数字表示INET_NTOA(num) 返回数字所代表的IP地址TIME_FORMAT(time,fmt) 依照字符串fmt格式化时间time值其中最简单的是FORMAT()函数，它可以把大的数值格式化为以逗号间隔的易读的序列。 示例：SELECT FORMAT(34234.34323432,3); SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r'); SELECT DATE_FORMAT(NOW(),'%Y-%m-%d'); SELECT DATE_FORMAT(19990330,'%Y-%m-%d'); SELECT DATE_FORMAT(NOW(),'%h:%i %p'); SELECT INET_ATON('10.122.89.47'); SELECT INET_NTOA(175790383);八、类型转化函数 为了进行数据类型转化，MySQL提供了CAST()函数，它可以把一个值转化为指定的数据类型。类型有：BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED 示例：SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0; SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY);九、系统信息函数- DATABASE() 返回当前数据库名BENCHMARK(count,expr) 将表达式expr重复运行count次CONNECTION_ID() 返回当前客户的连接IDFOUND_ROWS() 返回最后一个SELECT查询进行检索的总行数USER()或SYSTEM_USER() 返回当前登陆用户名VERSION() 返回MySQL服务器的版本示例：SELECT DATABASE(),VERSION(),USER();SELECTBENCHMARK(9999999,LOG(RAND()*PI()));#该例中,MySQL计算LOG(RAND()*PI())表达式9999999次。
首先采用Mysql存储千亿级的数据，确实是一项非常大的挑战。Mysql单表确实可以存储10亿级的数据，只是这个时候性能非常差，项目中大量的实验证明，Mysql单表容量在500万左右，性能处于最佳状态。针对大表的优化，主要是通过数据库分库分表来解决，目前比较普遍的方案有三个：分区，分库分表，NoSql/NewSql。实际项目中，这三种方案是结合的，目前绝大部分系统的核心数据都是以RDBMS存储为主，NoSql/NewSql存储为辅。分区首先来了解一下分区方案。分区表是由多个相关的底层表实现的。这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个相同的索引。这个方案对用户屏蔽了sharding的细节，即使查询条件没有sharding column，它也能正常工作（只是这时候性能一般）。不过它的缺点很明显：很多的资源都受到单机的限制，例如连接数，网络吞吐等。如何进行分区，在实际应用中是一个非常关键的要素之一。下面开始举例：以客户信息为例，客户数据量5000万加，项目背景要求保存客户的银行卡绑定关系，客户的证件绑定关系，以及客户绑定的业务信息。此业务背景下，该如何设计数据库呢。项目一期的时候，我们建立了一张客户业务绑定关系表，里面冗余了每一位客户绑定的业务信息。基本结构大致如下：查询时，对银行卡做索引，业务编号做索引，证件号做索引。随着需求大增多，这张表的索引会达到10个以上。而且客户解约再签约，里面会保存两条数据，只是绑定的状态不同。假设我们有5千万的客户，5个业务类型，每位客户平均2张卡，那么这张表的数据量将会达到惊人的5亿，事实上我们系统用户量还没有过百万时就已经不行了。这样的设计绝对是不行的，无论是插入，还是查询，都会让系统崩溃。mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看）， 一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。这三个文件都非常的庞大，尤其是.myd文件，快5个G了。下面进行第一次分区优化，Mysql支持的分区方式有四种：在我们的项目中，range分区和list分区没有使用场景，如果基于绑定编号做range或者list分区，绑定编号没有实际的业务含义，无法通过它进行查询，因此，我们就剩下 HASH 分区和 KEY 分区了，HASH分区仅支持int类型列的分区，且是其中的一列。 KEY 分区倒是可以支持多列，但也要求其中的一列必须是int类型；看我们的库表结构，发现没有哪一列是int类型的，如何做分区呢？增加一列，绑定时间列，将此列设置为int类型，然后按照绑定时间进行分区，将每一天绑定的用户分到同一个区里面去。这次优化之后，我们的插入快了许多，但是查询依然很慢，为什么？因为在做查询的时候，我们也只是根据银行卡或者证件号进行查询，并没有根据时间查询，相当于每次查询，mysql都会将所有的分区表查询一遍。进行第二次方案优化，既然 HASH 分区和 KEY分区要求其中的一列必须是int类型的，那么创造出一个int类型的列出来分区是否可以？分析发现，银行卡的那串数字有秘密。银行卡一般是16位到19位不等的数字串，我们取其中的某一位拿出来作为表分区是否可行呢，通过分析发现，在这串数字中，其中确实有一位是0到9随机生成的，我们基于银行卡号+随机位进行KEY分区，每次查询的时候，通过计算截取出这位随机位数字，再加上卡号，联合查询，达到了分区查询的目的，需要说明的是，分区后，建立的索引，也必须是分区列，否则Mysql还是会在所有的分区表中查询数据。通过银行卡号查询绑定关系的问题解决了，那么证件号呢，如何通过证件号来查询绑定关系。前面已经讲过，做索引一定是要在分区健上进行，否则会引起全表扫描。我们再创建了一张新表，保存客户的证件号绑定关系，每位客户的证件号都是唯一的，新的证件号绑定关系表里，证件号作为了主键，那么如何来计算这个分区健呢，客户的证件信息比较庞杂，有身份证号，港澳台通行证，机动车驾驶证等等，如何在无序的证件号里找到分区健。为了解决这个问题，我们将证件号绑定关系表一分为二，其中的一张表专用于保存身份证类型的证件号，另一张表则保存其他证件类型的证件号，在身份证类型的证件绑定关系表中，我们将身份证号中的月数拆分出来作为了分区健，将同一个月出生的客户证件号保存在同一个区，这样分成了12个区，其他证件类型的证件号，数据量不超过10万，就没有必要进行分区了。这样每次查询时，首先通过证件类型确定要去查询哪张表，再计算分区健进行查询。作了分区设计之后，保存2000万用户数据时银行卡表的数据保存文件就分成了10个小文件，证件表的数据保存文件分成了12个小文件，解决了这两个查询的问题，还剩下一个问题：业务编号怎么办？一个客户有多个签约业务，如何进行保存？这时候，采用分区的方案就不太合适了，它需要用到分表的方案。分表我们前面有提到过对于mysql，其数据文件是以文件形式存储在磁盘上的。当一个数据文件过大时，操作系统对大文件的操作就会比较麻烦耗时，且有的操作系统就不支持大文件，这个时候就必须分表了。另外对于mysql常用的存储引擎是Innodb，它的底层数据结构是B+树。当其数据文件过大的时候，查询一个节点可能会查询很多层次，而这必定会导致多次IO操作进行装载进内存，肯定会耗时的。除此之外还有Innodb对于B+树的锁机制。对每个节点进行加锁，那么当更改表结构的时候，这时候就会树进行加锁，当表文件大的时候，这可以认为是不可实现的。所以综上我们就必须进行分表与分库的操作。如何进行分库分表，目前互联网上有许多的版本，比较知名的一些方案：阿里的TDDL，DRDS和cobar，京东金融的sharding-jdbc；民间组织的MyCAT；360的Atlas；美团的zebra；其他比如网易，58，京东等公司都有自研的中间件。这么多的分库分表中间件方案归总起来，就两类：client模式和proxy模式。client模式proxy模式无论是client模式，还是proxy模式。几个核心的步骤是一样的：SQL解析，重写，路由，执行，结果归并。个人比较倾向于采用client模式，它架构简单，性能损耗也比较小，运维成本低。如何对业务类型进行分库分表。分库分表最重要的一步，即sharding column的选取，sharding column选择的好坏将直接决定整个分库分表方案最终是否成功。而sharding column的选取跟业务强相关。在我们的项目场景中，sharding column无疑最好的选择是业务编号。通过业务编号，将客户不同的绑定签约业务保存到不同的表里面去，根据业务编号路由到相应的表中进行查询，达到进一步优化sql的目的。
设计数据库和表需要考虑哪些才不容易走弯路？
在数据库设计和表创建时，我们首要考虑的就是性能咯，不然的话，在后期数据更新到千亿级别时，再来优化，那成本就加大了。或者说你给后期的开发人员挖了一个大坑吧！ 考虑到性能，那你在设计的时候会考虑到哪些问题了，下面将来详细的介绍。 设计表时要注意： 1 表字段避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null。 2 尽量使用INT而非BIGINT，如果非负则加上UNSIGNED(这样数值容量会扩大一倍)，当然能使用TINYINT、SMALLINT、MEDIUM\_INT更好。 3 使用枚举或整数代替字符串类型 4 尽量使用TIMESTAMP而非DATETIME 5 单表不要有太多字段，建议在20以内 6 用整型来存IP 索引 1 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描 2 应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描 3 值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段 4 字符字段只建前缀索引 5 字符字段最好不要做主键 6 不用外键，由程序保证约束 7 尽量不用UNIQUE，由程序保证约束 8 使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引 简言之就是使用合适的数据类型，选择合适的索引 选择合适的数据类型 (1)使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob (2)使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数 (3)使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar (4)尽可能使用not null定义字段 (5)尽量少用text，非用不可最好分表 选择合适的索引列 (1)查询频繁的列，在where，group by，order by，on从句中出现的列 (2)where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符(%)出现的列 (3)长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好 (4)离散度大(不同的值多)的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高： sql的编写需要注意优化 1 使用limit对查询结果的记录进行限定 2 避免select *，将需要查找的字段列出来 3 使用连接(join)来代替子查询 4 拆分大的delete或insert语句 5 可通过开启慢查询日志来找出较慢的SQL 6 不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边 7 sql语句尽可能简单：一条sql只能在一个cpu运算;大语句拆小语句，减少锁时间;一条大sql可以堵死整个库 8 OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内 9 不用函数和触发器，在应用程序实现 10 避免%xxx式查询 11 少用JOIN 12 使用同类型进行比较，比如用'123'和'123'比，123和123比 13 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描 14 对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5 15 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大 引擎 目前广泛使用的是MyISAM和InnoDB两种引擎： MyISAM MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是： 1 不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁 2 不支持事务 3 不支持外键 4 不支持崩溃后的安全恢复 5 在表有读取查询的同时，支持往表中插入新纪录 6 支持BLOB和TEXT的前500个字符索引，支持全文索引 7 支持延迟更新索引，极大提升写入性能 8 对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用 InnoDB InnoDB在MySQL 5.5后成为默认索引，它的特点是： 1.支持行锁，采用MVCC来支持高并发 2.支持事务 3.支持外键 4.支持崩溃后的安全恢复 5.不支持全文索引 总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表 MyISAM速度可能超快，占用存储空间也小，但是程序要求事务支持，故InnoDB是必须的，故该方案无法执行，放弃! 真的挖坑了 如果真的出现亿万级别的大数据表，那你是埋了一个大坑， 这个又要如何优化呢！目前比较普遍的方案主要有分区，分库分表，NoSql/NewSql。 实际项目中，这三种方案是结合的，目前绝大部分系统的核心数据都是以RDBMS存储为主，NoSql/NewSql存储为辅。这里就不在这里做详细介绍，介绍不完啊！
数据库锁知识不少人在开发的时候，应该很少会注意到这些锁的问题，也很少会给程序加锁(除了库存这些对数量准确性要求极高的情况下)，即使我们不会这些锁知识，我们的程序在一般情况下还是可以跑得好好的。因为这些锁数据库隐式帮我们加了，只会在某些特定的场景下才需要手动加锁。对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁（X) MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行增、删、改操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预表锁首先，从锁的粒度，我们可以分成两大类： 表锁 :开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低 行锁:开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高 不同的存储引擎支持的锁粒度是不一样的==：InnoDB行锁和表锁都支持、MyISAM只支持表锁！InnoDB只有通过索引条件检索数据才使用行级锁==，否则，InnoDB使用表锁也就是说，InnoDB的行锁是基于索引的！表锁下又分为两种模式： 表读锁（Table Read Lock）&& 表写锁（Table Write Lock） 从下图可以清晰看到，在表读锁和表写锁的环境下：读读不阻塞，读写阻塞，写写阻塞！ 读读不阻塞：当前用户在读数据，其他的用户也在读数据，不会加锁 读写阻塞：当前用户在读数据，其他的用户不能修改当前用户读的数据，会加锁！ 写写阻塞：当前用户在修改数据，其他的用户不能修改当前用户正在修改的数据，会加锁！从上面已经看到了：读锁和写锁是互斥的，读写操作是串行。如果某个进程想要获取读锁，同时另外一个进程想要获取写锁。在mysql中，写锁是优先于读锁的！写锁和读锁优先级的问题是可以通过参数调节的：max_write_lock_count和low-priority-updates注：行锁InnoDB和MyISAM有两个本质的区别：InnoDB支持行锁、InnoDB支持事务InnoDB实现了以下两种类型的行锁：共享锁（S锁、读锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。即多个客户可以同时读取同一个资源，但不允许其他客户修改。排他锁（X锁、写锁)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的读锁和写锁。写锁是排他的，写锁会阻塞其他的写锁和读锁。另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。意向锁也是数据库隐式帮我们做了，不需要程序员关心！MVCCMVCC(Multi-Version ConcurrencyControl)多版本并发控制，可以简单地认为：MVCC就是行级锁的一个变种(升级版)。在表锁中我们读写是阻塞的，基于提升并发性能的考虑，MVCC一般读写是不阻塞的(很多情况下避免了加锁的操作)。 可以简单的理解为：对数据库的任何修改的提交都不会直接覆盖之前的数据，而是产生一个新的版本与老版本共存，使得读取时可以完全不加锁。事务的隔离级别事务的隔离级别就是通过锁的机制来实现，锁的应用最终导致不同事务的隔离级别，只不过隐藏了加锁细节，事务的隔离级别有4种：Read uncommitted：会出现脏读，不可重复读，幻读Read committed：会出现不可重复读，幻读Repeatable read：会出现幻读(Mysql默认的隔离级别，但是Repeatable read配合gap锁不会出现幻读！)Serializable：串行，避免以上的情况Read uncommitted： 出现的现象--->脏读：一个事务读取到另外一个事务未提交的数据，例子：A向B转账，A执行了转账语句，但A还没有提交事务，B读取数据，发现自己账户钱变多了！B跟A说，我已经收到钱了。A回滚事务【rollback】，等B再查看账户的钱时，发现钱并没有多...Read committed： 出现的现象--->不可重复读：一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改，例如：A查询数据库得到数据，B去修改数据库的数据，导致A多次查询数据库的结果都不一样【危害：A每次查询的结果都是受B的影响的，那么A查询出来的信息就没有意思了】Repeatable read： 避免不可重复读是事务级别的快照！每次读取的都是当前事务的版本，即使被修改了，也只会读取当前事务版本的数据至于虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。和不可重复读类似，但虚读(幻读)会读到其他事务的插入的数据，导致前后读取不 一致，幻读的重点在于新增或者删除(数据条数变化)，不可重复读的重点是修改，幻读和不可重复的区别？乐观锁和悲观锁无论是Read committed还是Repeatable read隔离级别，都是为了解决读写冲突的问题，现在考虑一个问题：有一张数据库表USER，只有id、name字段，现在有2个请求同时操作表A，过程如下：（模拟更新丢失，虽然不是很恰当）操作1查询出name="zhangsan"操作2也查询出name="zhangsan"操作1把name字段数据修改成lisi并提交操作2把name字段数据修改为wangwu并提交那么操作1的更新丢失啦，即一个事务的更新覆盖了其它事务的更新结果，解决上述更新丢失的方式有如下3种：使用Serializable隔离级别，事务是串行执行的！乐观锁悲观锁悲观锁我们使用悲观锁的话其实很简单(手动加行锁就行了)：select * from xxxx for update，在select 语句后边加了for update相当于加了排它锁(写锁)，加了写锁以后，其他事务就不能对它修改了！需要等待当前事务修改完之后才可以修改.也就是说，如果操作1使用select ... for update，操作2就无法对该条记录修改了，即可避免更新丢失。乐观锁乐观锁不是数据库层面上的锁，需要用户手动去加的锁。一般我们在数据库表中添加一个版本字段version来实现，例如操作1和操作2在更新User表的时，执行语句如下：update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version}， 此时即可避免更新丢失。间隙锁GAP当我们用范围条件检索数据而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在 的记录，叫做“间隙（GAP)”。InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。例子：假如emp表中只有101条记录，其empid的值分别是1,2,...,100,101Select * from emp where empid > 100 for update; 上面是一个范围查询，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁InnoDB使用间隙锁的目的有2个：为了防止幻读(上面也说了，Repeatable read隔离级别下再通过GAP锁即可避免了幻读)满足恢复和复制的需要：MySQL的恢复机制要求在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读死锁并发的问题就少不了死锁，在MySQL中同样会存在死锁的问题锁总结表锁其实我们程序员是很少关心它的：在MyISAM存储引擎中，当执行SQL语句的时候是自动加的。在InnoDB存储引擎中，如果没有使用索引，表锁也是自动加的。现在我们大多数使用MySQL都是使用InnoDB，InnoDB支持行锁：共享锁--读锁--S锁排它锁--写锁--X锁在默认的情况下，select是不加任何行锁的~事务可以通过以下语句显示给记录集加共享锁或排他锁。共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATEInnoDB基于行锁还实现了MVCC多版本并发控制，MVCC在隔离级别下的Read committed和Repeatable read下工作。MVCC实现了读写不阻塞
为什么要分表和分区？ 日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。什么是分表？ 分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。什么是分区？ 分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。mysql分表和分区有什么联系呢？ 1.都能提高mysql的性高，在高并发状态下都有一个良好的表现。 2.分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。 3.分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。 4.表分区相对于分表，操作方便，不需要创建子表。 我们知道对于大型的互联网应用，数据库单表的数据量可能达到千万甚至上亿级别，同时面临这高并发的压力。Master-Slave结构只能对数据库的读能力进行扩展，写操作还是集中在Master中，Master并不能无限制的挂接Slave库，如果需要对数据库的吞吐能力进行进一步的扩展，可以考虑采用分库分表的策略。 1.分表 在分表之前，首先要选中合适的分表策略（以哪个字典为分表字段，需要将数据分为多少张表），使数据能够均衡的分布在多张表中，并且不影响正常的查询。在企业级应用中，往往使用org\_id(组织主键)做为分表字段，在互联网应用中往往是userid。在确定分表策略后，当数据进行存储及查询时，需要确定到哪张表里去查找数据， 数据存放的数据表 = 分表字段的内容 % 分表数量 2.分库 分表能够解决单表数据量过大带来的查询效率下降的问题，但是不能给数据库的并发访问带来质的提升，面对高并发的写访问，当Master无法承担高并发的写入请求时，不管如何扩展Slave服务器，都没有意义了。我们通过对数据库进行拆分，来提高数据库的写入能力，即所谓的分库。分库采用对关键字取模的方式，对数据库进行路由。 数据存放的数据库=分库字段的内容%数据库的数量 3.即分表又分库 数据库分表可以解决单表海量数据的查询性能问题，分库可以解决单台数据库的并发访问压力问题 当数据库同时面临海量数据存储和高并发访问的时候，需要同时采取分表和分库策略。一般分表分库策略如下： 中间变量 = 关键字%（数据库数量*单库数据表数量） 库 = 取整（中间变量/单库数据表数量） 表 = （中间变量%单库数据表数量） **先谈谈分表的几种方式： 1、mysql集群** 事实它并不是分表，但起到了和分表相同的作用。集群可分担数据库的操作次数，将任务分担到多台数据库上。集群可以读写分离，减少读写压力。从而提升数据库性能。 2、自定义规则分表 大表可以按照业务的规则来分解为多个子表。通常为以下几种类型，也可自己定义规则。 1 Range（范围）–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。 2 Hash（哈希）–这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。 3 Key（键值）-上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。 4 List（预定义列表）–这种模式允许系统通过预定义的列表的值来对数据进行分割。 5 composite（复合模式） –以上模式的组合使用 以聊天信息表为例： 我事先建100个这样的表，message\_00,message\_01,message\_02……….message\_98,message\_99.然后根据用户的ID来判断这个用户的聊天信息放到哪张表里面，你可以用hash的方式来获得，可以用求余的方式来获得，方法很多，各人想各人的吧。下面用hash的方法来获得表名：<?php function get_hash_table($table,$userid) { $str = crc32($userid); if($str<0){ $hash = "0".substr(abs($str), 0, 1); }else{ $hash = substr($str, 0, 2); } return $table."_".$hash; } echo get_hash_table('message' , 'user18991'); //结果为message_10 echo get_hash_table('message' , 'user34523'); //结果为message_13 ?> 说明一下，上面的这个方法，告诉我们user18991这个用户的消息都记录在message\_10这张表里，user34523这个用户的消息都记录在message\_13这张表里，读取的时候，只要从各自的表中读取就行了。 优点：避免一张表出现几百万条数据，缩短了一条sql的执行时间 缺点：当一种规则确定时，打破这条规则会很麻烦，上面的例子中我用的hash算法是crc32，如果我现在不想用这个算法了，改用md5后，会使同一个用户的消息被存储到不同的表中，这样数据乱套了。扩展性很差。 3，利用merge存储引擎来实现分表 我觉得这种方法比较适合，那些没有事先考虑，而已经出现了得，数据查询慢的情况。这个时候如果要把已有的大数据量表分开比较痛苦，最痛苦的事就是改代码，因为程序里面的sql语句已经写好了，现在一张表要分成几十张表，甚至上百张表，这样sql语句是不是要重写呢？举个例子，我很喜欢举例子 mysql>show engines;的时候你会发现mrg\_myisam其实就是merge。mysql> CREATE TABLE IF NOT EXISTS `user1` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `name` varchar(50) DEFAULT NULL, -> `sex` int(1) NOT NULL DEFAULT '0', -> PRIMARY KEY (`id`) -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE IF NOT EXISTS `user2` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `name` varchar(50) DEFAULT NULL, -> `sex` int(1) NOT NULL DEFAULT '0', -> PRIMARY KEY (`id`) -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO `user1` (`name`, `sex`) VALUES('张映', 0); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1); Query OK, 1 row affected (0.00 sec) mysql> CREATE TABLE IF NOT EXISTS `alluser` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `name` varchar(50) DEFAULT NULL, -> `sex` int(1) NOT NULL DEFAULT '0', -> INDEX(id) -> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select id,name,sex from alluser; +----+--------+-----+ | id | name | sex | +----+--------+-----+ | 1 | 张映 | 0 | | 1 | tank | 1 | +----+--------+-----+ rows in set (0.00 sec) mysql> INSERT INTO `alluser` (`name`, `sex`) VALUES('tank2', 0); Query OK, 1 row affected (0.00 sec) mysql> select id,name,sex from user2 -> ; +----+-------+-----+ | id | name | sex | +----+-------+-----+ | 1 | tank | 1 | | 2 | tank2 | 0 | +----+-------+-----+ rows in set (0.00 sec) mysql> CREATE TABLE IF NOT EXISTS `user1` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `name` varchar(50) DEFAULT NULL, -> `sex` int(1) NOT NULL DEFAULT '0', -> PRIMARY KEY (`id`) -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE IF NOT EXISTS `user2` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `name` varchar(50) DEFAULT NULL, -> `sex` int(1) NOT NULL DEFAULT '0', -> PRIMARY KEY (`id`) -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO `user1` (`name`, `sex`) VALUES('张映', 0); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1); Query OK, 1 row affected (0.00 sec) mysql> CREATE TABLE IF NOT EXISTS `alluser` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `name` varchar(50) DEFAULT NULL, -> `sex` int(1) NOT NULL DEFAULT '0', -> INDEX(id) -> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select id,name,sex from alluser; +----+--------+-----+ | id | name | sex | +----+--------+-----+ | 1 | 张映 | 0 | | 1 | tank | 1 | +----+--------+-----+ rows in set (0.00 sec) mysql> INSERT INTO `alluser` (`name`, `sex`) VALUES('tank2', 0); Query OK, 1 row affected (0.00 sec) mysql> select id,name,sex from user2 -> ; +----+-------+-----+ | id | name | sex | +----+-------+-----+ | 1 | tank | 1 | | 2 | tank2 | 0 | +----+-------+-----+ rows in set (0.00 sec)从上面的操作中，我不知道你有没有发现点什么？假如我有一张用户表user，有50W条数据，现在要拆成二张表user1和user2，每张表25W条数据INSERT INTO user1(user1.id,user1.name,user1.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id <= 250000 INSERT INTO user2(user2.id,user2.name,user2.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id > 250000 这样就成功的将一张user表，分成了二个表，这个时候有一个问题，代码中的sql语句怎么办，以前是一张表，现在变成二张表了，代码改动很大，这样给程序员带来了很大的工作量，有没有好的办法解决这一点呢？办法是把以前的user表备份一下，然后删除掉，上面的操作中我建立了一个alluser表，只把这个alluser表的表名改成user就行了。但是，不是所有的mysql操作都能用的 a，如果你使用 alter table 来把 merge 表变为其它表类型，到底层表的映射就被丢失了。取而代之的，来自底层 myisam 表的行被复制到已更换的表中，该表随后被指定新类型。 b，网上看到一些说replace不起作用，我试了一下可以起作用的。mysql> UPDATE alluser SET sex=REPLACE(sex, 0, 1) where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from alluser; +----+--------+-----+ | id | name | sex | +----+--------+-----+ | 1 | 张映 | 0 | | 1 | tank | 1 | | 2 | tank2 | 1 | +----+--------+-----+ rows in set (0.00 sec) mysql> UPDATE alluser SET sex=REPLACE(sex, 0, 1) where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from alluser; +----+--------+-----+ | id | name | sex | +----+--------+-----+ | 1 | 张映 | 0 | | 1 | tank | 1 | | 2 | tank2 | 1 | +----+--------+-----+ rows in set (0.00 sec)c，一个 merge 表不能在整个表上维持 unique 约束。当你执行一个 insert，数据进入第一个或者最后一个 myisam 表（取决于 insert\_method 选项的值）。mysql 确保唯一键值在那个 myisam 表里保持唯一，但不是跨集合里所有的表。 d,当你创建一个 merge 表之时，没有检查去确保底层表的存在以及有相同的机构。当 merge 表被使用之时，mysql 检查每个被映射的表的记录长度是否相等，但这并不十分可靠。如果你从不相似的 myisam 表创建一个 merge 表，你非常有可能撞见奇怪的问题。 c和d在网上看到的，没有测试，大家试一下吧。 优点：扩展性好，并且程序代码改动的不是很大 缺点：这种方法的效果比第二种要差一点
正则表达式描述了一组字符串。最简单的正则表达式是不含任何特殊字符的正则表达式。例如，正则表达式hello匹配hello。 非平凡的正则表达式采用了特殊的特定结构，从而使得它们能够与1个以上的字符串匹配。例如，正则表达式hello|word匹配字符串hello或字符串word。 作为一个更为复杂的示例，正则表达式B[an]*s匹配下述字符串中的任何一个：Bananas，Baaaaas，Bs，以及以B开始、以s结束、并在其中包含任意数目a或n字符的任何其他字符串。 以下是可用于随REGEXP操作符的表的模式。 应用示例，查找用户表中Email格式错误的用户记录：SELECT * FROM users WHERE email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+.[A-Z]{2,4}$' MySQL数据库中正则表达式的语法，主要包括各种符号的含义。 ##### （^）字符 匹配字符串的开始位置，如“^a”表示以字母a开头的字符串。mysql> select 'xxxyyy' regexp '^xx'; +-----------------------+ | 'xxxyyy' regexp '^xx' | +-----------------------+ | 1 | +-----------------------+ 1 row in set (0.00 sec) 查询xxxyyy字符串中是否以xx开头，结果值为1，表示值为true，满足条件。##### （$）字符匹配字符串的结束位置，如“X^”表示以字母X结尾的字符串。 ##### （.）字符这个字符就是英文下的点，它匹配任何一个字符，包括回车、换行等。 ##### （*）字符星号匹配0个或多个字符，在它之前必须有内容。如：mysql> select 'xxxyyy' regexp 'x*'; 这个SQL语句，正则匹配为true。 ##### （+）字符加号匹配1个或多个字符，在它之前也必须有内容。加号跟星号的用法类似，只是星号允许出现0次，加号则必须至少出现一次。 ##### （?）字符问号匹配0次或1次。 实例: 现在根据上面的表，可以装置各种不同类型的SQL查询以满足要求。在这里列出一些理解。考虑我们有一个表为person\_tbl和有一个字段名为名称： 查询找到所有的名字以'st'开头mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st'; 查询找到所有的名字以'ok'结尾mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$'; 查询找到所有的名字包函'mar'的字符串mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar'; 查询找到所有名称以元音开始和'ok'结束 的mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$'; 一个正则表达式中的可以使用以下保留字 ##### ^所匹配的字符串以后面的字符串开头mysql> select "fonfo" REGEXP "^fo$"; -> 0（表示不匹配） mysql> select "fofo" REGEXP "^fo"; -> 1（表示匹配） $所匹配的字符串以前面的字符串结尾mysql> select "fono" REGEXP "^fono$"; -> 1（表示匹配） mysql> select "fono" REGEXP "^fo$"; -> 0（表示不匹配） . 匹配任何字符（包括新行）mysql> select "fofo" REGEXP "^f.*"; -> 1（表示匹配） mysql> select "fonfo" REGEXP "^f.*"; -> 1（表示匹配） a*匹配任意多个a（包括空串)mysql> select "Ban" REGEXP "^Ba*n"; -> 1（表示匹配） mysql> select "Baaan" REGEXP "^Ba*n"; -> 1（表示匹配） mysql> select "Bn" REGEXP "^Ba*n"; -> 1（表示匹配） a+匹配任意多个a（不包括空串)mysql> select "Ban" REGEXP "^Ba+n"; -> 1（表示匹配） mysql> select "Bn" REGEXP "^Ba+n"; -> 0（表示不匹配） a?匹配一个或零个amysql> select "Bn" REGEXP "^Ba?n"; -> 1（表示匹配） mysql> select "Ban" REGEXP "^Ba?n"; -> 1（表示匹配） mysql> select "Baan" REGEXP "^Ba?n"; -> 0（表示不匹配） de|abc匹配de或abcmysql> select "pi" REGEXP "pi|apa"; -> 1（表示匹配） mysql> select "axe" REGEXP "pi|apa"; -> 0（表示不匹配） mysql> select "apa" REGEXP "pi|apa"; -> 1（表示匹配） mysql> select "apa" REGEXP "^(pi|apa)$"; -> 1（表示匹配） mysql> select "pi" REGEXP "^(pi|apa)$"; -> 1（表示匹配） mysql> select "pix" REGEXP "^(pi|apa)$"; -> 0（表示不匹配） (abc)*匹配任意多个abc（包括空串)mysql> select "pi" REGEXP "^(pi)*$"; -> 1（表示匹配） mysql> select "pip" REGEXP "^(pi)*$"; -> 0（表示不匹配） mysql> select "pipi" REGEXP "^(pi)*$"; -> 1（表示匹配） {1} {2,3} 这是一个更全面的方法，它可以实现前面好几种保留字的功能a*可以写成a{0,} ##### a+可以写成a{1,} ##### a?可以写成a{0,1} 在{}内只有一个整型参数i，表示字符只能出现i次；在{}内有一个整型参数i，后面跟一个“，”，表示字符可以出现i次或i次以上；在{}内只有一个整型参数i，后面跟一个“，”，再跟一个整型参数j,表示字符只能出现i次以上，j次以下（包括i次和j次）。其中的整型参数必须大于等于0，小于等于 RE\_DUP\_MAX（默认是255）。 如果有两个参数，第二个必须大于等于第一个 ##### [a-dX]匹配“a”、“b”、“c”、“d”或“X” ##### [^a-dX]匹配除“a”、“b”、“c”、“d”、“X”以外的任何字符。 “[”、“]”必须成对使用mysql> select "aXbc" REGEXP "[a-dXYZ]"; -> 1（表示匹配） mysql> select "aXbc" REGEXP "^[a-dXYZ]$"; -> 0（表示不匹配） mysql> select "aXbc" REGEXP "^[a-dXYZ]+$"; -> 1（表示匹配） mysql> select "aXbc" REGEXP "^[^a-dXYZ]+$"; -> 0（表示不匹配） mysql> select "gheis" REGEXP "^[^a-dXYZ]+$"; -> 1（表示匹配） mysql> select "gheisa" REGEXP "^[^a-dXYZ]+$"; -> 0（表示不匹配）
有时候我们会遇到：在查询sql的时候，假如有100w条数据，会出现慢sql告警，这个时候你就应该到处sql日志来查找原因了。这里很有可能的主要原因就是没有命中索引和没有分页处理（原因有很多种，主要分析你的日志）。那接下来我们就得去优化sql了。 **如何优化呢？下面我们来谈谈有关的问题。 一、从sql优化入手** 谈到sql性能优化，那我们就离不开谈到大数据量和并发数，MySQL没有限制单表的最大记录数，它只是取决于操作系统对文件大小的限制。看表： 从表中我们可以看出，如果单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 假如你在操作过超过4亿行数据的单表，你可以用分页查询，分页查询最新的20条记录耗时0.6秒，SQL语句大致是select field_1,field_2 from table where id < #{prePageMinId} order by id desc limit 20，prePageMinId是上一页数据记录的最小ID。 这个查询出来的查询速度还凑合，不过随着数据不断增长，有朝一日必定不堪重负。所以分库分表是个周期长而风险高的大活儿，应该尽可能在当前结构上优化，比如升级硬件、迁移历史数据等等，实在没辙了再分。最大并发数并发数是指同一时刻数据库能处理多少个请求，由max\_connections和max\_user\_connections决定。max\_connections是指MySQL实例的最大连接数，上限值是16384，max\_user\_connections是指每个数据库用户的最大连接数。 MySQL会为每个连接提供缓冲区，意味着消耗更多的内存。如果连接数设置太高硬件吃不消，太低又不能充分利用硬件。一般要求两者比值超过10%，计算方法如下：max_used_connections / max_connections * 100% = 3/100 *100% ≈ 3% 查看最大连接数与响应最大连接数如下：show variables like '%max_connections%'; show variables like '%max_user_connections%'; 在配置文件my.cnf中你可以修改最大连接数[mysqld] max_connections = 100 max_used_connections = 20 查询耗时0.5秒建议将单次查询耗时控制在0.5秒以内，0.5秒是个经验值，源于用户体验的3秒原则。如果用户的操作3秒内没有响应，将会厌烦甚至退出。响应时间=客户端UI渲染耗时+网络请求耗时+应用程序处理耗时+查询数据库耗时，0.5秒就是留给数据库1/6的处理时间。 二、数据库设计也是影响性能的关键 数据类型的选择原则：更简单或者占用空间更小。你可以这样设计： 1 如果长度能够满足，整型尽量使用tinyint、smallint、medium\_int而非int。 2 如果字符串长度确定，采用char类型。 3 如果varchar能够满足，不采用text类型。 4 精度要求较高的使用decimal类型，也可以使用BIGINT，比如精确两位小数就乘以100后保存。 5 尽量采用timestamp而非datetime。 相比datetime，timestamp占用更少的空间，以UTC的格式储存自动转换时区。 避免空值 MySQL中字段为NULL时依然占用空间，会使索引、索引统计更加复杂。从NULL值更新到非NULL无法做到原地更新，容易发生索引分裂影响性能。尽可能将NULL值用有意义的值代替，也能避免SQL语句里面包含is not null的判断。 text类型优化 由于text字段储存大量数据，表容量会很早涨上去，影响其他字段的查询性能。建议抽取出来放在子表里，用业务主键关联。 三、索引优化，这个经常谈到 索引的分类有哪些？ 1 普通索引：最基本的索引 2 组合索引：多个字段上建立的索引，能够加速复合查询条件的检索。 3 唯一索引：与普通索引类似，但索引列的值必须唯一，允许有空值 4 组合唯一索引：列值的组合必须唯一 5 主键索引：特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用primary key约束。 6 全文索引：用于海量文本的查询，MySQL5.6之后的InnoDB和MyISAM均支持全文索引。由于查询精度以及扩展性不佳，更多的企业选择Elasticsearch。 索引优化 1 分页查询很重要，如果查询数据量超过30%，MYSQL不会使用索引。 2 单表索引数不超过5个、单个索引字段数不超过5个。 3 字符串可使用前缀索引，前缀长度控制在5-8个字符。 4 字段唯一性太低，增加索引没有意义，如：是否删除、性别。 合理使用覆盖索引，如下所示：select login_name, nick_name from member where login_name = ?四，sql语句如何优化 业务描述：更新用户所有已过期的优惠券为不可用状态。update status=0 FROM `coupon` WHERE expire_date <= #{currentDate} and status=1;如果大量优惠券需要更新为不可用状态，执行这条SQL可能会堵死其他SQL，分批处理伪代码如下：int pageNo = 1; int PAGE_SIZE = 100; while(true) { List<Integer> batchIdList = queryList('select id FROM `coupon` WHERE expire_date <= #{currentDate} and status = 1 limit #{(pageNo-1) * PAGE_SIZE},#{PAGE_SIZE}'); if (CollectionUtils.isEmpty(batchIdList)) { return; } update('update status = 0 FROM `coupon` where status = 1 and id in #{batchIdList}') pageNo ++; }操作符<>优化 通常<>操作符无法使用索引，举例如下，查询金额不为100元的订单：select id from orders where amount != 100;如果金额为100的订单极少，这种数据分布严重不均的情况下，有可能使用索引。鉴于这种不确定性，采用union聚合搜索结果，改写方法如下：(select id from orders where amount > 100) union all (select id from orders where amount < 100 and amount > 0)OR优化在Innodb引擎下or无法使用组合索引，比如：select id，product_name from orders where mobile_no = '13421800407' or user_id = 100;OR无法命中mobile\_no + user\_id的组合索引，可采用union，如下所示：(select id，product_name from orders where mobile_no = '13421800407') union (select id，product_name from orders where user_id = 100);此时id和product\_name字段都有索引，查询才最高效。 IN优化IN适合主表大子表小，EXIST适合主表小子表大。由于查询优化器的不断升级，很多场景这两者性能差不多一样了。 尝试改为join查询，举例如下：select id from orders where user_id in (select id from user where level = 'VIP');采用JOIN如下所示：select o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';不做列运算通常在查询条件列运算会导致索引失效，如下所示： 查询当日订单select id from order where date_format(create_time，'%Y-%m-%d') = '2019-07-01';date\_format函数会导致这个查询无法使用索引，改写后：select id from order where create_time between '2019-07-01 00:00:00' and '2019-07-01 23:59:59'; 避免Select all如果不查询表中所有的列，避免使用SELECT *，它会进行全表扫描，不能有效利用索引。 Like优化like用于模糊查询，举个例子（field已建立索引）：SELECT column FROM table WHERE field like '%keyword%'; 这个查询未命中索引，换成下面的写法：SELECT column FROM table WHERE field like 'keyword%'; 去除了前面的%查询将会命中索引，但是产品经理一定要前后模糊匹配呢？全文索引fulltext可以尝试一下，但Elasticsearch才是终极武器。 Join优化join的实现是采用Nested Loop Join算法，就是通过驱动表的结果集作为基础数据，通过该结数据作为过滤条件到下一个表中循环查询数据，然后合并结果。如果有多个join，则将前面的结果集作为循环数据，再次到后一个表中查询数据。 驱动表和被驱动表尽可能增加查询条件，满足ON的条件而少用Where，用小结果集驱动大结果集。 被驱动表的join字段上加上索引，无法建立索引的时候，设置足够的Join Buffer Size。 禁止join连接三个以上的表，尝试增加冗余字段。 Limit优化limit用于分页查询时越往后翻性能越差，解决的原则：缩小扫描范围，如下所示：select * from orders order by id desc limit 100000,10 耗时0.4秒select * from orders order by id desc limit 1000000,10 耗时5.2秒 先筛选出ID缩小查询范围，写法如下：select * from orders where id > (select id from orders order by id desc limit 1000000, 1) order by id desc limit 0,10 耗时0.5秒 如果查询条件仅有主键ID，写法如下：select id from orders where id between 1000000 and 1000010 order by id desc 耗时0.3秒 如果以上方案依然很慢呢？只好用游标了，感兴趣的朋友阅读JDBC使用游标实现分页查询的方法
为什么你写的sql查询慢？为什么你建的索引常失效？
为什么你写的sql查询慢？为什么你建的索引常失效？ 通过本篇内容，你将学会MySQL性能下降的原因，索引的简介，索引创建的原则，explain命令的使用，以及explain输出字段的意义。助你了解索引，分析索引，使用索引，从而写出更高性能的sql语句。 案例分析 我们先简单了解一下非关系型数据库和关系型数据库的区别。 MongoDB是NoSQL中的一种。NoSQL的全称是Not only SQL，非关系型数据库。它的特点是性能高，扩张性强，模式灵活，在高并发场景表现得尤为突出。但目前它还只是关系型数据库的补充，它在数据的一致性，数据的安全性，查询的复杂性问题上和关系型数据库还存在一定差距。 MySQL是关系性数据库中的一种，查询功能强，数据一致性高，数据安全性高，支持二级索引。但性能方面稍逊与MongoDB，特别是百万级别以上的数据，很容易出现查询慢的现象。这时候需要分析查询慢的原因，一般情况下是程序员sql写的烂，或者是没有键索引，或者是索引失效等原因导致的。 公司ERP系统数据库主要是MongoDB（最接近关系型数据的NoSQL），其次是Redis，MySQL只占很少的部分。现在又重新使用MySQL，归功于阿里巴巴的奇门系统和聚石塔系统。考虑到订单数量已经是百万级以上，对MySQL的性能分析也就显得格外重要。 我们先通过两个简单的例子来入门。后面会详细介绍各个参数的作用和意义。 场景一：订单导入，通过交易号避免重复导单 业务逻辑：订单导入时，为了避免重复导单，一般会通过交易号去数据库中查询，判断该订单是否已经存在。 最基础的sql语句 查询的本身没有任何问题，在线下的测试环境也没有任何问题。可是，功能一旦上线，查询慢的问题就迎面而来。几百上千万的订单，用全表扫描？啊？哼! 怎么知道该sql是全表扫描呢？通过explain命令可以清楚MySQL是如何处理sql语句的。打印的内容分别表示： id : 查询序列号为1。 select\_type : 查询类型是简单查询，简单的select语句没有union和子查询。 table : 表是 itdragon\_order\_list。 partitions : 没有分区。 type : 连接类型，all表示采用全表扫描的方式。 possible\_keys : 可能用到索引为null。 key : 实际用到索引是null。 key\_len : 索引长度当然也是null。 ref : 没有哪个列或者参数和key一起被使用。 Extra : 使用了where查询。 因为数据库中只有三条数据，所以rows和filtered的信息作用不大。这里需要重点了解的是type为ALL，全表扫描的性能是最差的，假设数据库中有几百万条数据，在没有索引的帮助下会异常卡顿。 初步优化：为transaction\_id创建索引 这里创建的索引是唯一索引，而非普通索引。 唯一索引打印的type值是const。表示通过索引一次就可以找到。即找到值就结束扫描返回查询结果。 普通索引打印的type值是ref。表示非唯一性索引扫描。找到值还要继续扫描，直到将索引文件扫描完为止。(这里没有贴出代码) 显而易见，const的性能要远高于ref。并且根据业务逻辑来判断，创建唯一索引是合情合理的。 再次优化：覆盖索引 这里将select * from 改为了 select transaction\_id from 后 Extra 显示 Using index，表示该查询使用了覆盖索引，这是一个非常好的消息，说明该sql语句的性能很好。若提示的是Using filesort(使用内部排序)和Using temporary(使用临时表)则表明该sql需要立即优化了。 根据业务逻辑来的，查询结构返回transaction\_id 是可以满足业务逻辑要求的。 场景二，订单管理页面，通过订单级别和订单录入时间排序 业务逻辑：优先处理订单级别高，录入时间长的订单。 既然是排序，首先想到的应该是order by， 还有一个可怕的 Using filesort 等着你。 最基础的sql语句 首先，采用全表扫描就不合理，还使用了文件排序Using filesort，更加拖慢了性能。 MySQL在4.1版本之前文件排序是采用双路排序的算法，由于两次扫描磁盘，I/O耗时太长。后优化成单路排序算法。其本质就是用空间换时间，但如果数据量太大，buffer的空间不足，会导致多次I/O的情况。其效果反而更差。与其找运维同事修改MySQL配置，还不如自己乖乖地建索引。 初步优化：为order\_level,input\_date 创建复合索引 创建复合索引后你会惊奇的发现，和没创建索引一样？？？都是全表扫描，都用到了文件排序。是索引失效？还是索引创建失败？我们试着看看下面打印情况 将select * from 换成了 select order\_level,input\_date from 后。type从all升级为index，表示（full index scan）全索引文件扫描，Extra也显示使用了覆盖索引。可是不对啊！！！！检索虽然快了，但返回的内容只有order\_level和input\_date 两个字段，让业务同事怎么用？难道把每个字段都建一个复合索引？ MySQL没有这么笨，可以使用force index 强制指定索引。在原来的sql语句上修改 force index(idx\_order\_levelDate) 即可。 再次优化：订单级别真的要排序么？ 其实给订单级别排序意义并不大，给订单级别添加索引意义也不大。因为order\_level的值可能只有，低，中，高，加急，这四种。对于这种重复且分布平均的字段，排序和加索引的作用不大。 我们能否先固定 order\_level 的值，然后再给 input\_date 排序？如果查询效果明显，是可以推荐业务同事使用该查询方式。 和之前的sql比起来，type从index 升级为 ref(非唯一性索引扫描)。索引的长度从68变成了5，说明只用了一个索引。ref也是一个常量。Extra 为Using index condition 表示自动根据临界值，选择索引扫描还是全表扫描。总的来说性能远胜于之前的sql。 上面两个案例只是快速入门，我们需严记一点：优化是基于业务逻辑来的。绝对不能为了优化而擅自修改业务逻辑。如果能修改当然是最好的。 索引简介 官方定义：索引（Index） 是帮助MySQL高效获取数据的数据结构。 大家一定很好奇，索引为什么是一种数据结构，它又是怎么提高查询的速度？我们拿最常用的二叉树来分析索引的工作原理。看下面的图片： 创建索引的优势 1 提高数据的检索速度，降低数据库IO成本：使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。 2 降低数据排序的成本，降低CPU消耗：索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则真好降低了排序的成本。 创建索引的劣势 1 占用存储空间：索引实际上也是一张表，记录了主键与索引字段，一般以索引文件的形式存储在磁盘上。 2 降低更新表的速度：表的数据发生了变化，对应的索引也需要一起变更，从而减低的更新速度。否则索引指向的物理数据可能不对，这也是索引失效的原因之一。 3 优质索引创建难：索引的创建并非一日之功，也并非一直不变。需要频繁根据用户的行为和具体的业务逻辑去创建最佳的索引。 索引分类 我们常说的索引一般指的是BTree（多路搜索树）结构组织的索引。其中还有聚合索引，次要索引，复合索引，前缀索引，唯一索引，统称索引，当然除了B+树外，还有哈希索引（hash index）等。 单值索引：一个索引只包含单个列，一个表可以有多个单列索引 唯一索引：索引列的值必须唯一，但允许有空值 复合索引：一个索引包含多个列，实际开发中推荐使用 实际开发中推荐使用复合索引，并且单表创建的索引个数建议不要超过五个 基本语法： 创建：create [unique] index indexName on tableName (columnName...) alter tableName add [unique] index [indexName] on (columnName...)删除：drop index [indexName] on tableName查看：show index from tableName哪些情况需要建索引： 1 主键，唯一索引 2 经常用作查询条件的字段需要创建索引 3 经常需要排序、分组和统计的字段需要建立索引 4 查询中与其他表关联的字段，外键关系建立索引 哪些情况不要建索引： 1 表的记录太少，百万级以下的数据不需要创建索引 2 经常增删改的表不需要创建索引 3 数据重复且分布平均的字段不需要创建索引，如 true,false 之类。 4 频发更新的字段不适合创建索引 5 where条件里用不到的字段不需要创建索引 性能分析 MySQL 自身瓶颈 MySQL自身参见的性能问题有磁盘空间不足，磁盘I/O太大，服务器硬件性能低。 1 CPU：CPU 在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候 2 IO：磁盘I/O 瓶颈发生在装入数据远大于内存容量的时候 3 服务器硬件的性能瓶颈：top,free,iostat 和 vmstat来查看系统的性能状态 explain 分析sql语句 使用explain关键字可以模拟优化器执行sql查询语句，从而得知MySQL 是如何处理sql语句。 id select 查询的序列号，包含一组可以重复的数字，表示查询中执行sql语句的顺序。一般有三种情况： 第一种：id全部相同，sql的执行顺序是由上至下； 第二种：id全部不同，sql的执行顺序是根据id大的优先执行； 第三种：id既存在相同，又存在不同的。先根据id大的优先执行，再根据相同id从上至下的执行。 select\_type select 查询的类型，主要是用于区别普通查询，联合查询，嵌套的复杂查询 simple：简单的select 查询，查询中不包含子查询或者union primary：查询中若包含任何复杂的子查询，最外层查询则被标记为primary subquery：在select或where 列表中包含了子查询 derived：在from列表中包含的子查询被标记为derived（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。 union：若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为：derived union result：从union表获取结果的select partitions 表所使用的分区，如果要统计十年公司订单的金额，可以把数据分为十个区，每一年代表一个区。这样可以大大的提高查询效率。 type 这是一个非常重要的参数，连接类型，常见的有：all , index , range , ref , eq\_ref , const , system , null 八个级别。 性能从最优到最差的排序：system > const > eq\_ref > ref > range > index > all 对java程序员来说，若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。 all：（full table scan）全表扫描无疑是最差，若是百万千万级数据量，全表扫描会非常慢。 index：（full index scan）全索引文件扫描比all好很多，毕竟从索引树中找数据，比从全表中找数据要快。 range：只检索给定范围的行，使用索引来匹配行。范围缩小了，当然比全表扫描和全索引文件扫描要快。sql语句中一般会有between，in，>，< 等查询。 ref：非唯一性索引扫描，本质上也是一种索引访问，返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事，匹配的结果是多个并非唯一值。 eq\_ref：唯一性索引扫描，对于每个索引键，表中有一条记录与之匹配。比如查询公司的CEO，匹配的结果只可能是一条记录， const：表示通过索引一次就可以找到，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快，若将主键至于where列表中，MySQL就能将该查询转换为一个常量。 system：表只有一条记录（等于系统表），这是const类型的特列，平时不会出现，了解即可 possible\_keys 显示查询语句可能用到的索引(一个或多个或为null)，不一定被查询实际使用。仅供参考使用。 key 显示查询语句实际使用的索引。若为null，则表示没有使用索引。 key\_len 显示索引中使用的字节数，可通过key\_len计算查询中使用的索引长度。在不损失精确性的情况下索引长度越短越好。key\_len 显示的值为索引字段的最可能长度，并非实际使用长度，即key\_len是根据表定义计算而得，并不是通过表内检索出的。 ref 显示索引的哪一列或常量被用于查找索引列上的值。 rows 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，值越大越不好。 extra Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。出现这个更要立刻优化sql。 Using index：表示相应的select 操作中使用了覆盖索引（Covering index），避免访问了表的数据行，效果不错！如果同时出现Using where，表明索引被用来执行索引键值的查找。如果没有同时出现Using where，表示索引用来读取数据而非执行查找动作。 覆盖索引（Covering Index） ：也叫索引覆盖，就是select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select 列表中的字段，而不必根据索引再次读取数据文件。 Using index condition：在5.6版本后加入的新特性，优化器会在索引存在的情况下，通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。 Using where：表明使用了where 过滤 Using join buffer：表明使用了连接缓存 impossible where：where 语句的值总是false，不可用，不能用来获取任何元素 distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。 filtered 一个百分比的值，和rows 列的值一起使用，可以估计出查询执行计划(QEP)中的前一个表的结果集，从而确定join操作的循环次数。小表驱动大表，减轻连接的次数。 通过explain的参数介绍，我们可以得知: 1 表的读取顺序(id) 2 数据读取操作的操作类型(type) 3 哪些索引被实际使用(key) 4 表之间的引用(ref) 5 每张表有多少行被优化器查询(rows) 性能下降的原因 从程序员的角度 1 查询语句写的不好 2 没建索引，索引建的不合理或索引失效 3 关联查询有太多的join 从服务器的角度 1 服务器磁盘空间不足 2 服务器调优配置参数设置不合理 总结 1 索引是排好序且快速查找的数据结构。其目的是为了提高查询的效率。 2 创建索引后，查询数据变快，但更新数据变慢。 3 性能下降的原因很可能是索引失效导致。 4 索引创建的原则，经常查询的字段适合创建索引，频繁需要更新的数据不适合创建索引。 5 索引字段频繁更新，或者表数据物理删除容易造成索引失效。 6 擅用 explain 分析sql语句 7 除了优化sql语句外，还可以优化表的设计。如尽量做成单表查询，减少表之间的关联。设计归档表等。 到这里，MySQL的索引优化分析就结束了，有什么不对的地方，大家可以提出来。如果觉得不错可以点一下推荐。