MySQL基础、索引、查询优化

MySQL基础

MySQL数据类型

整数类型

TINYINT、 SMALLINT、 MEDIUMINT、 INT、 BIGINT

属性：UNSIGNED
长度：可以为整数类型指定宽度，例如：INT(11)、对大多数应用是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数。

int(3) 可以存 1234 ，不会出错。但是存储 12 并指定 zerofill属性的话，则会在前面补上0,成为012

实数类型

FLOAT、 DOUBLE、DECIMAL

DECIMAL可存储比BIGINT还大的整数；可以用于存储精确的小数
FLOAT和DOUBLE类型支持使用标准符点进行近似计算

字符串类型

VARCHAR CHAR TEXT BLOB

VARCHAR用于存储可变长字符串，它比定长类型更节省空间
VARCHAR使用1或2个额外字节记录字符串的长度，列长度小于255字节，使用1个字节表示，否则用2个
VARCHAR长度，如果存储内容超过制定长度，会被截断或报错
CHAR是定长的，根据定义的字符串长度分配足够的空间
CHAR会根据需要采用空格进行填充以方便比较
CHAR适合存储很段的字符串，或者所有值都接近同一个长度
CHAR长度，超过设定长度会被截断
CHAR、VARCHAR比较
- 对于经常变更的数据，CHAR比VARCHAR更好，CHAR不容易产生碎片
- 对于非常短的列，CHAR比VARCHAR在存储空间上更有效率

尽量避免使用TEXT和BLOB类型，因为查询会产生临时表，造成性能开销

枚举

尽量避免使用数字做为枚举的常量，易造成混乱

日期和时间类型

尽量使用TIMESTAMP，比DATETIME空间效率更高
用整数保存时间戳的格式通常不方便处理
如果需要存储微秒，可以使用BIGINT存储

列属性

auto_increment default not null zerofill

MySQL基础操作

MySQL的连接和关闭：mysql -u -p -h -P
其他：\G \c \s \h \d

MySQL存储引擎

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

优先使用InnoDB

MySQL锁机制

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

MySQL事务处理、存储过程、触发器

事务处理
- MySQL提供事务处理的表引擎：InnoDB
- 服务器层不管理事务，由下层的引擎实现，所以同一个事务中，使用多种存储引擎不靠谱
- 在非事务的表上执行事务操作，MySQL不会发出提醒，也不会报错
存储过程
- 为以后的使用而保存的一条或多条MySQL语句的集合
- 存储过程就是有业务逻辑和流程的集合
- 可以在存储过程中创建表，更新数据，删除等等
- 使用场景
  - 通过把处理封装在容易使用的单元中，简化复杂的操作
  - 保证数据的一致性
  - 简化对变动的管理
触发器
- 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程
- 使用场景
  - 可通过数据库中的相关表实现级联更改
  - 实时监控某张表中的某个字段的更改而需要做处相应的处理
  - 某些业务编号的生成等
  - 滥用会造成数据库及应用程序的维护困难

mysql索引

MySQL索引的基础和类型

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

mysql索引创建原则

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

mysql索引注意事项

符合索引遵循前缀原则
KEY(a,b,c) WHERE a = 1 and b = 2 and c = 3 #生效 WHERE a = 1 and b = 2 #生效 WHERE b = 2 and c = 3 #跳过了a，索引不能生效
like查询，%不能放在前面，否则索引失效
colume is null 可以使用索引
如果mysql判断使用索引会比全表扫描更慢，会自动放弃索引
如果or前的条件中的列有索引，后面的没有，索引都不会被用到
列类型是字符串类型的话，如果不写引号，索引会失效

MySQL的SQL语言编写特点

MySQL的关联UPDATE语句
```
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...
```
MySQL的关联查询语句
- 六种关联查询
  - 交叉（CROSS JOIN）
  - 内连接（INNER JOIN）
    --自连接 SELECT t1 as a INNER JOIN t1 as b WHERE a.pid = b.id
  - 外连接（LEFT JION/ RIGHT JOIN）
  - 联合（UNION UNION ALL）
    - union 会合并重复的
    - union 不合并重复的
  - 全连接（FULL JOIN ）
  - 嵌套查询
    用一条SQL语句的结果作为另一条SQL语句的条件

例子
表team
teamID teamName

表match
matchID hostTeamID guestTeamID matchTime matchResult

要求：
match赛程表中的hostTeamID与guestTeamID都和team表中的teamID关联，查询2006-6-1到2006-7-1之间举行的所有比赛，并且用以下形式列出：拜仁 2：0 不莱梅 2006-6-21

 1 --初始：
 2 SELECT hostTeamID,matchResult,guestTeamID,matchTime
 3 FROM match
 4 WHERE matchTime BETWEEN '2006-6-1' AND '2006-7-1'
 5 --这个查询只把队id查出来了，还需要联合team表查询出teamname，所以可以用match表left join team表
 6 
 7 SELECT t1.teamName,m.matchResult,t2.teamName,m.matchTime
 8 FROM match as m
 9 LEFT JOIN team as t1 on m.hostTeamID = t1.teamID,
10 LEFT JOIN team as t2 on m.guestTeamID = t2.teamID
11 WHERE m.matchTime BETWEEN '2006-6-1' AND '2006-7-1'

MySQL查询优化考点

分析SQL查询慢的方法
- 记录慢查询日志
  分析查询日志，不要直接打开慢查询日志进行分析，这样比较浪费时间和经历，可以使用pt-query-digest工具进行分析
- 使用show profile
  set profileing = 1;开启，服务器上执行所有语句会检测消耗的时间，存到临时表中
  show profiles
  show profile for query Query_ID
- 使用show status
  show status会返回一些计数器，show global status 查看服务器级别的所有计数
  有时根据这些计数，可以猜测出那些操作代价较高或消耗时间多
- show processlist
  观察是否有大量线程处于不正常的状态或特征
- explain
  分析单条的SQL语句
优化查询过程中的数据访问
- 访问数据太多会导致查询性能下降（不要用select *）
  - 确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
  - 确定MySQL服务器是否在分析大量不必要的数据行
- 避免使用如下SQL语句
  - 查询不需要的数据，使用limit解决
  - 所表关联返回全部列，制定A.id,A.name,B.age
  - 总是取出全部列，SELECT * 会让优化器无法完成索引覆盖扫描的优化
  - 重复查询相同的数据，可以缓存数据，下次直接读取缓存
- 是否在扫描额外的记录
  - 使用explain来进行分析，如果发现查询需要扫描大量的数据但是只返回少数行可以用如下技巧去优化：
    - 使用索引覆盖扫描，把所有用的列都放在索引中，这样存储引擎不需要回表获取对应行就可以返回结果
    - 改变数据库的表的结构，修改数据表范式（如果经常需要关联查询，可将另一张表的字段冗余地存储在要查询的表中，以空间换取时间）
    - 重写SQL语句，让优化器可以以更优的方式执行查询
优化长难的查询语句
- 切分查询
  将一个大的查询分为多个小的相同的查询
- 分解关联查询
  可以将一条关联查询分解成多条SQL来执行，可以让缓存的效率更高，执行单个查询可以减少锁的竞争，在应用层做关联可以更容易对数据进行拆分，查询效率会有大幅提升，较少冗余记录的查询
优化特定类型的查询语句
- 优化count()查询
  - count(*)中的*会忽略所有的列，直接统计所有列数，因此不要使用count(列名)
  - MyISAM中，没有任何WHERE条件的count(*)非常快，当有WHERE条件时，MyISAM的count统计不一定比其他表引擎快
  - 可以使用explain查询近似值，用近似值代替count(*)
  - 增加汇总表
  - 使用缓存
- 优化关联查询
  - 确定ON 或者 USING子句的列上有索引
    
    Mysql 中联接SQL语句中，ON子句的语法格式为：table1.column_name = table2.column_name。
    当模式设计对联接表的列采用了相同的命名样式时，就可以使用 USING 语法来简化 ON 语法，格式为：USING(column_name)。
  - 确保GROUP BY 和 ORDER BY中只有一个表中的列，这样MySQL才能使用索引
- 优化子查询
  - 用关联查询替代
- 优化GROUP BY 和 DISTINCT
  - 这两种查询均可以使用索引来优化，是最有效的优化方法
  - 关联查询中，使用表示列进行分组的效率会更高（尽量使用主键、自增列等）
  - 如果不需要ORDER BY ，进行GROUP BY 时使用ORDER BY NULL ，mysql不会再进行文件排序
- 优化LIMIT分页
  - LIMIT偏移量大时，查询效率低
  - 可以记录上次查询的最大ID，下次查询时直接根据该ID来查询
- 优化UNION查询
  - UNION ALL查询效率高于UNION

MySQL的高可扩展和高可用考察点

分区表的原理

工作原理
对用户而言，分区表是一个独立的逻辑表，但是底层MySQL将其分成了多个物理子表，这对用户来说是透明的，每一个分区表都会使用一个独立的表文件

创建表时使用 partition by 子句定义每个分区存储的数据，执行查询时，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询只需要查询所需数据在的分区即可

分区的目的是将数据按一个较粗的粒度分在不同的表中，这样就可以将相关的数据存放在一起，而且如果想一次性删除整个分区的数据也很方便
适用场景
1. 表非常大，无法全部存放在内存，或者只在表的最后有热点数据，其他都是历史数据
2. 分区表的数据更易维护，可以对独立的分区进行独立的操作
3. 分区表的数据可以分布在不同的机器上，从而高效使用资源
4. 可以使用分区表来避免某些特殊瓶颈
5. 可以备份和恢复独立分区
限制
1. 一个表最多只能有1024个分区
2. 5.1版本中，分区表表达式必须是整数，5.5可以使用列分区
3. 分区字段中如果有主键和唯一索引列，那么主键列和唯一列都必须包含进来
4. 分区表中无法使用外键约束
5. 需要对现有表的结构进行修改（partition by）
6. 所有分区都必须使用相同的存储引擎
7. 分区函数中可以使用的函数和表达式会有一定限制
8. 某些存储引擎不支持分区
9. 对MyISAM的分区表，不能使用load index into cahe
10. 对于MyISAM表，使用分区表时需要打开更多的文件描述符

分库分表的原理

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

MySQL的复制原理及负载均衡

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

MySQL 安全性考察点

SQL查询的安全方案
1. 使用预处理语句来处理sql注入
2. 写入数据库的数据要进行特殊字符的转义
3. 查询错误信息不要返回给用户，将错误记录到日志
  注意：PHP端尽量使用PDO对数据库进行相关操作，PDO拥有对预处理语句很好的支持的方法，MySQLi也有，但是可扩展性不如PDO，效率略高于PDO
MySQL其他安全设置

定期做数据备份
不给查询用户root权限，合理分配权限
关闭远程访问数据库的权限
修改root口令，不用默认口令
删除多余用户
修改root用户名称
限制一般用户浏览其他库
限制用户对数据文件的访问权限

posted @ 2018-08-29 17:07 L1230205 阅读(189) 评论(0) 编辑收藏举报

刷新页面返回顶部

L1230205