MySQL基础知识总结

1. MySQL基础

  MySQL数据类型:
    整数 TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT  属性:UNSIGNED  长度:可以为整数类型指定宽度,例如:INT(11),对大多数应用没有意义,不会限制值的合法范围,只会影响显示字符的个数(宽度),不够则zerofill
    实数 FLOAT, DOUBLE, DECIMAL(可存储比BIGINT还大的整数,可以存储精确的小数)
    字符串 VARCHAR(存储可变长字符串,比定长类型更节省时间,使用1或2个额外字节记录字符串的长度,列长度小于255字节使用1个字节表示,否则2个;如果存储内容超出指定长度,会被截断), CHAR(定长,根据定义的字符串长度分配足够的空间;会根据需要采用空格进行填充以方便比较;适合存储很短的字符串,或者所有值都接近同一个长度;超出设定的长度会被截断;对于经常变更的数据,CHAR比VARCHAR更好,CHAR不容易产生碎片,对于非常短的列,CHAR比VARCHAR在存储空间上更有效率,只分配真正需要的空间,更长的列会消耗更多的内存), TEXT, BLOB(尽量避免使用,查询会使用临时表,导致严重的性能开销)
    枚举:有时可以使用枚举代替常用的字符串类型,把不重复的集合存储成一个预定义的集合,非常紧凑,把列表值压缩到一个或两个字节,内部存储的是整数,尽量避免使用数字作为ENUM枚举的常量,易混乱,排序是按照内部存储的整数进行排序,枚举表会使表大小大大减小
    日期和时间:尽量使用TIMESTAMP,比DATETIME空间效率高;用整数保存时间戳的格式通常不方便处理,如果需要存储微秒可以使用bigint存储
    列属性:auto_increment default not null zerofill

  MySQL基础操作:
    连接和关闭:mysql -u -p -h -P  其它:\G \c \q \s \h \d

  MySQL数据表引擎:
    InnoDB表引擎:默认事务型引擎,最重要最广泛的存储引擎,性能非常优秀;数据存储在共享表空间,可以通过配置分开;对主键查询的性能高于其它类型的存储引擎;内部做了很多优化,从磁盘读取数据时自动在内存构建hash索引,插入数据时自动构建插入缓冲区;通过一些机制和工具支持真正的热备份;支持崩溃后的安全恢复;支持行级锁和外键 
    MyISAM表引擎:5.1版本前,MyISAM是默认的存储引擎;拥有全文、索引、压缩空间函数;不支持事务和行级锁,不支持崩溃后的安全恢复;表存储在两个文件,MYD和MYI;设计简单,某些场景下性能很好;
    其他表引擎:Archive Blackhole CSV Memory

  MySQL锁机制:
    表锁是日常开发当中常见的问题,当多个查询同一时刻进行数据修改时,就会产生并发控制的问题。共享锁和排他锁,就是读锁和写锁
    读锁:共享的,不堵塞,多个用户可以同时读一个资源,互不干扰
    写锁:排他的,会阻塞其它的写锁和读锁,这样可以只允许一个人进行写入,防止其他用户读取正在写入的资源
    锁粒度:表锁,系统性能开销最小,会锁定整张表,MyISAM使用表锁;行锁,最大程度支持并发处理,但是也带来了最大的锁开销,InnoDB实现行级锁

  MySQL事务处理:
    提供事务处理的表引擎,InnoDB;服务器层不管理事务,由下层的引擎实现,所以同一个事务中,使用多种存储引擎不靠谱;在非事务的表上执行事务操作MySQL不会发出提醒,也不会报错

  MySQL存储过程:
    为以后的使用而保存的一条或多条MySQL语句的集合;存储过程就是有业务逻辑和流程的集合;可以在存储过程中创建表,更新数据,删除等等;通过把处理封装在容易使用的单元中,简化复杂的操作,保证数据的一致性,简化对变动的管理

  MySQL触发器:
    提供给程序员和数据分析员来保证数据完整性的一种方法,是与表事件相关的特殊的存储过程;可通过数据库中的相关表实现级联更改;实时监控某张表中的某个字段的更改而需要做出相应的处理;某些业务编号的生成等;滥用会造成数据库及应用程序的维护困难

2. 创建高性能索引

  MySQL索引的基础和类型
    索引的基础:索引类似于书籍的目录,存储引擎使用类似的方式进行数据查询,先去索引当中找到对应的值,然后根据匹配的索引找到对应的数据行
    索引对性能的影响:大大减少服务器需要扫描的数据量;帮助服务器避免排序和临时表,将随机I/O变顺序I/O;大大提高查询速度,降低写的速度、占用磁盘
    索引的使用场景:对于非常小的表,大部分情况下全表扫描效率更高;中到大型表,索引非常有效;特大型的表,建立和使用索引的代价将随之增长,可以使用分区技术来解决
    索引的类型:都是实现在存储引擎层的
      普通索引:最基本的索引,没有任何约束限制
      唯一索引:与普通索引类似,但是具有唯一性约束
      主键索引:特殊的唯一索引,不允许有空值;一个表只能有一个主键索引,可以有多个唯一索引;主键可以与外键构成参照完整性约束,防止数据不一致
      组合索引:将多个列组合在一起创建索引,可以覆盖多个列
      外键索引:只有InnoDB类型的表可以使用,保证数据的一致性、完整性和实现级联操作
      全文索引:MySQL自带的全文索引只能用于MyISAM,并且只能对英文进行全文检索

  MySQL索引的创建原则
    最适合索引的列是出现在WHERE子句中的列,或连接子句中的列而不是出现在SELECT后的列;索引列的基数越大,索引的效果越好;对字符串进行索引,应该制定一个前缀长度,可以节省大量的索引空间;根据情况创建复合索引,可以提高查询效率;避免创建过多索引,会额外占用磁盘空间,降低写操作效率;主键尽可能选择较短的数据类型,可以有效减少索引的磁盘占用提高查询效率

  MySQL索引的注意事项
    复合索引遵循前缀原则;like查询,%不能在前,可以使用全文索引;column is null可以使用索引;如果MySQL估计使用索引比全表扫描更慢,会放弃使用索引;如果or前的条件中的列有索引,后面的没有,索引都不会被用到;列类型是字符串,查询时一定要给值加引号,否则索引失效

3. SQL语句编写

  关联更新:UPDATE A,B SET A.c1 = B.c1,A.c2 = B.c2 WHERE A.id = B.id
    UPDATE A INNER JOIN B ON A.id = B.id SET A.c1 = B.c1,A.c2 = B.c2 WHERE...... 

  6种关联查询:交叉连接CROSS JOIN,
    内连接INNER JOIN:SELECT * FROM A,B WHERE A.id=B.id 或者SELECT * FROM A INNER JOIN B ON A.id = B.id。多表中同时符合某种条件的数据记录的集合。分为等值连接,不等值连接,自连接:SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

    外连接LEFT/RIGHT JOIN:以左表为主,先查询出左表,按照ON后的关联条件匹配右表,没有匹配到的用NULL填充
    联合查询UNION与UNION ALL:SELECT * FROM A UNION SELECT * FROM B UNION ......。就是把多个结果集集中在一起,UNION前的结果为基准,需要注意的是联合查询的列数要相等,相同的记录行会合并;如果使用UNION ALL则不会合并重复的记录行
    全连接FULL JOIN:MySQL不支持全连接,可以使用LEFT JOIN和RIGHT JOIN和UNION联合使用:SELECT * FROM A LEFT JION B ON A.id=B.id UNION SELECT * FROM A RIGHT JION B ON A.id=B.id
    嵌套查询:用一条SQL语句的结果作为另外一条SQL语句的条件,如SELECT * FROM A WHERE id IN (SELECT id FROM B)

4. 查询优化

  查找分析查询速度慢的原因
    分析SQL查询慢的方法:
    记录慢查询日志;分析查询日志,不要直接打开慢查询日志进行分析,这样比较浪费时间和精力,可以使用pt-query-digest工具进行分析
    使用show profile:set profiling=1;开启,服务器上执行的所有语句会检测消耗的时间,存到临时表中;show profiles;show profile for query 临时表ID
    使用show status:会返回一些计数器,show global status查看服务器级别的所有计数;有时根据这些计数,可以猜测出哪些操作代价较高或者消耗时间多
    使用show  processlist:观察是否有大量线程处于不正常的状态或者特征
    使用explain\desc:分析单条SQL语句

  优化查询过程中的数据访问:访问数据太多导致查询性能下降;确定应用程序是否在检索大量超过需要的数据,可能是太多行或列;确认MySQL服务器是否在分析大量不必要的数据行
    避免使用如下SQL语句:查询不需要的记录,使用limit解决;多表关联返回全部列,指定A.id,A.name,B.age;总是取出全部列,SELECT * 会让优化器无法完成索引覆盖扫描的优化
    重复查询相同的数据,可以缓存数据,下次直接读取缓存
    是否在扫描额外的记录:使用explain进行分析,如果发现查询需要扫描大量的数据但只返回少数的行,可以通过如下技巧去优化:使用索引覆盖扫描,把所有用的列都放到索引中,这样存储引擎不需要回表获取对应行就可以返回结果;改变数据库和表的结构,修改数据表范式;重写SQL语句,让优化器可以以更优的方式执行查询

  优化长难的查询语句:
    一个复杂查询还是多个简单查询:MySQL内部每秒能扫描内存中上百万行数据,相比之下,响应数据给客户端就要慢得多;使用尽可能少的查询是好的,但是有时将一个大的查询分解为多个小的查询是很有必要的
    切分查询:将一个大的查询分为多个小的相同的查询;一次性删除1000w的数据要比一次删除1w,暂停一会的方案更加损耗服务器开销
    分解关联查询:可以将一条关联语句分解成多条SQL来执行,让缓存的效率更高,执行单个查询可以减少锁的竞争,在应用层做关联可以更容易对数据库进行拆分,查询效率会有大幅提升,较少冗余记录的查询

  优化特定类型的查询语句:
    优化count()查询:count(*)中的*会忽略所有的列,直接统计所有列数,因此不要使用count(列名);MyISAM中,没有任何WHERE条件的count(*)非常快,当有WHERE条件,MyISAM的count统计不一定比其他表引擎快;可以使用explain查询近似值,用近似值替代count(*);增加汇总表;使用缓存
    优化关联查询:确定ON或者USING子句的列上有索引;确保GROUP BY和ORDER BY中只有一个表中的列,这样MySQL才有可能使用索引
    优化子查询:尽可能使用关联查询来替代
    优化GROUP BY和DISTINCT:这两种查询均可使用索引来优化,是最有效的优化方法;关联查询中,使用标识列进行分组的效率会更高;如果不需要ORDER BY,进行GROUP BY时使用ORDER BY NULL,MySQL不会再进行文件排序;WITH ROLLUP超级聚合,可以挪到应用程序处理
    优化LIMIT分页:LIMIT偏移量大的时候,查询效率较低;可以记录上次查询的最大ID,下次查询时直接根据该ID来查询
    优化UNION查询:UNION ALL的效率高于UNION

5. 高扩展和高可用

  分区表的原理:
  工作原理:对用户而言,分区表是一个独立的逻辑表,但是底层MySQL将其分成了多个物理子表,这对用户来说是透明的,每一个分区表都会使用一个独立的表文件;创建表时使用partition by子句定义每个分区存放的数据,执行查询时,优化器会根据分区定义过滤那些没有我们需要数据的分区,这样查询只需要查询所需数据在的分区即可;分区的主要目的是将数据按照一个较粗的粒度分在不同的表中,这样可以将相关的数据存放在一起,而且如果想一次性删除整个分区的数据也很方便
  使用场景:表非常大,无法全部存在内存,或者只在表的最后有热点数据,其他都是历史数据;分区表的数据更易维护,可以对独立的分区进行独立的操作;分区表的数据可以分布在不同的机器上,从而高效使用资源;可以使用分区表来避免某些特殊的瓶颈;可以备份和恢复独立的分区;
  限制:一个表最多只能有1024个分区;5.1版本中,分区表表达式必须是整数,5.5可以使用列分区;分区字段中如果有主键和唯一索引列,那么主键列和唯一列都必须包含进来;分区表中无法使用外键约束;需要对现有表的结构进行修改;所有分区都必须使用相同的存储引擎;分区函数中可以使用的函数和表达式会有一些限制;某些存储引擎不支持分区;对于MyISAM的分区表,不能使用load index into cache;对于MyISAM表,使用分区表时需要打开更多的文件描述符

  分库分表的原理:
  工作原理:通过一些HASH算法或者工具实现将一张数据表垂直或者水平进行物理切分
  适用场景:单表记录条数达到百万到千万级别时;解决表锁的问题;
  分表方式:
    水平分割:表很大,分割后可以降低在查询时需要读的数据和索引的页数,同时也降低了索引的层数,提高查询速度;使用场景:表中的数据本身就有独立性,例如表中分别记录各个地区的数据或者不同时期的数据,特别是有些数据常用,有些不常用;需要把数据存放在多个介质上;缺点:给应用增加复杂度,通常查询时需要多个表名,查询所有数据都需UNION操作;在许多数据库应用中,这种复杂性会超过它带来的优点,查询时会增加读一个索引层的磁盘次数。
    垂直分表:把主键和一些列放在一个表,然后把主键和另外的列放在另一个表中;使用场景:如果一个表中某些列常用,而另外一些列不常用;可以使数据行变小,一个数据页能存储更多数据,查询时减少I/O次数;缺点:管理冗余列,查询所有数据需要JOIN操作
  分表缺点:有些分表的策略基于应用层的逻辑算法,一旦逻辑算法改变,整个分表逻辑都会改变,扩展性较差;对于应用层来说,逻辑算法无疑增加开发成本

  MySql的复制原理及负载均衡
  MySQL主从复制工作原理:在主库上把数据更改记录到二进制日志,从库将主库的日志复制到自己的中继日志,从库读取中继日志中的事件,将其重放到从库数据中
  解决的问题:数据分布:随意停止或开始复制,并在不同地理位置分布数据备份;负载均衡:降低单个服务器的压力;高可用和故障切换:帮助应用程序避免单点失败;升级测试:可以使用更高版本的MySQL作为从库

6. 安全性

  SQL查询的安全方案:使用预处理语句防SQL注入;写入数据库的数据要进行特殊字符的转义;查询错误信息不要返回给用户,将错误记录到日志。PHP端尽量使用PDO对数据库进行相关操作,PDO拥有对预处理语句很好的支持的方法,MySQLi也有,但是可扩展性不如PDO,效率略高于PDO,MySQL函数在新版本中已经趋向于淘汰,所以不建议使用,而且它没有很好的支持预处理的方法

  MySQL的其他安全设置:定期做数据备份;不给查询用户root权限,合理分配权限;关闭远程访问数据库权限;修改root口令,不用默认口令,使用较复杂的口令;删除多余的用户;改变root用户的名称;限制一般用户浏览其他库;限制用户对数据文件的访问权限

     

相关文章