MySQL常见问题和sql优化

MySQL常见问题和sql优化

数据库的三范式

第一范式：1NF是对属性的原子性约束，要求字段具有原子性，不可再分解
第二范式：2NF是在满足第一范式的前提下，非主键字段不能出现部分依赖主键
第三范式：3NF实在满足第二范式的前提下，非主键字段不能出现依赖传递

事务

1，事务的四大特性

原子性：不可分割
一致性：前后一致
隔离性：四种级别
持久性：结果持久

2，mysql的事务隔离级别
读未提交（Read Uncommitted）：允许脏读
读已提交(Read Committed)：只能读取已提交的数据
可重复度(Repeated Read)（mysql默认的）：无论其他事务是否修改了数据，在这个事务中的数据值始终不受影响
串行化：完全串行化的读

脏读：是指事务1将某一个值修改，事务2读取该值，此后事务1因为某种原因撤销对 该值的修改，这就导致了事务2所读取的数据是无效的。
不可重复读：是指数据库访问时，一个事务范围内两次相同查询却返回了不同数据。
幻读：

索引

1,索引类型
index ------------- 普通索引
unique ------------唯一索引：要求索引列上的值必须唯一，但允许有空值
primary key ---------主键索引：一个表只能有一个主键，不允许空值
组和索引
全文索引

2，mysql索引的实现原理：
B+tree的高度是可控的。
B+Tree只在最末端的叶子节点存数据，叶子节点是以链表的形式互相指向的。
一般不用事务的时候，count计数多的时候适合myIsam引擎：
当我们执行 select * from user where id = 1的时候，它的执行流程。
　　　　(1)查看该表的myi文件有没有以id为索引的索引树。
　　　　(2)根据这个id索引找到叶子节点的id值，从而得到它里面的数据地址。(叶子节点存的是索引和数据地址)。
　　　　(3)根据数据地址去myd文件里面找到对应的数据返回出来。

对可靠性要求高就使用innoDB引擎：
例如：select * from user where name=“jack”;
它的执行过程：一，先找到name索引树
二，根据name的值找到该树下叶子的name索引和主键值
三，用主键值去主键索引树叶子节点找到该条数据的信息
B+Tree的特性：

一，由图可以看出，单节点能存更多数据，使得IO次数更少。
二，叶子节点形成有序链表，便于执行范围操作
三，聚合索引中，叶子节点的data直接包含数据；非聚合索引中，叶子节点存储数据地址的指针。

3，数据库索引失效（原因）
一，like是以%开头的查询语句
二，使用多列索引的查询语句
三，使用or关键字查询语句
四，如果列表类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不适用索引。

sql语句分类

DDL： 数据定义语言
DML：数据操作语言
DQL
DCL
TPL: 事务语句

存储引擎 （MyISAN和InnoDB区别）

innoDB支持事务，支持外键，不支持全文索引，支持行锁（默认）和表锁，mysql5.5之后默认引擎，适合频繁修改以及涉及到安全性能较高的应用
MyISAM不支持事务，不支持外键，支持全文索引，支持表锁，适合查询以及插入为主的应用

表锁和行锁

表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低
行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发量最高

存储过程

常用的sql语句在执行时需要先编译，在执行，而存储过程是一组为了完成特定功能的sql
语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数来调用它。

mysql的逻辑分层

一，连接层 ----------------------提供与客户端连接的服务
二，服务层 ----------------------提供客户端使用的各种接口，提供sql优化器
三，引擎层------------------------提供各种存储数据的方式（存储引擎：innoDB,MylSAM）
四，存储层 ------------------------存储数据

返回当前系统时间

now()：以’yyyy-mm-dd hh:mm:ss’返回当前的日期时间，可以直接存到datetime字段中。
curdate()：’yyyy-mm-dd’的格式返回今天的日期，可以直接存到date字段中。

mysql表进程卡死，无法增删改查等操作

一，列出进程show full processlist;
二，删除第一列ID， kill ID;

MySQL优化

1，最大连接数（JMeter进行压测）
SHOW VARIABLES LIKE ‘%max_connections%’;
SET GLOBAL max_connections = 1000; //此修改方式重启数据库之后会重置。

2，查询缓存

3，索引优化
索引要针对性的创建，考虑在where或order By命令上建索引，可根据EXPlain来查看是否用了索引还是全表扫描
应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃索引而全表扫描
值分布很稀少的字段不适合建索引
字符字段只建前缀索引
不用外键，由程序保证约束
尽量不要使用unique，由程序保证约束
使用多列索引时注意顺序和查询条件保持一致，同时删除不必要的单列索引

4，sql优化
一，可以通过开启慢查询日志来找出慢的sql
重心是查询优化，查询优化的重心是建立索引----------------保证索引不失效
二，不要进行算术运算
三，sql语句尽可能简单
四，不要使用select *
五，OR改成IN，OR的效率时n级别的，IN的效率是log（n）
六，不要使用函数和触发器，由程序实现
七，避免%xxx式查询
八，少用JOIN
九，使用同类型进行比较，比如用’123’和’123’比，123和123比
十，尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
十一，对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
十二，列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

5，存储引擎
MyISAM适合select密集型的表
InnoDB适合insert和update密集型的表

6，mysql调优
工具
Jmeter压测：
sysbench:
iibench-mysql:
tpcc-mysql:

参数：
back_log:
wait_timeout:
max_user_connection:
thread_concurrency:
skip_name_resolve:
key_buffer_size:
innodb_buffer_pool_size:
innodb_additional_mem_pool_size:
innodb_log_buffer_size:
query_cache_size:
read_buffer_size:
record_cache_size:
table_cache:

7,读写分离
从库读主库写

8，表分区
分区的类型：
Range分区：基于属于一个给定连续区间的列值，把多行分配给分区
list分区： 类似于range分区：基于列值匹配一个散列值集合中的某个值来进行选择
hash分区：基于用户自定义的表达式的返回值来进行选择的分区
key分区：类似于hash分，只支持计算一列或多列

垂直拆分与水平拆分

9，mysql优化命令
show profiles
显示哪些线程正在运行 SHOW PROCESSLIST
命令： show status;
查看配置参数：SHOW VARIABLES LIKE ‘%timeout%’

10，总结：
一，存储引擎的选择：多读用MyISAM,多写用InnoDB
二，字段类型的选择
三，索引的选择：在where，order by子句中常涉及到的字段
四，分区分表
五，主从复制
六，读写分离
七，sql优化 ： 单条查询最后添加LIMIT 1，停止全盘扫描
where子句中不使用！=，否则放弃索引而全盘扫描
尽量避免null值判断，否则放弃索引而全盘扫描
尽量避免or连接条件，否则放弃索引而全盘扫描
尽量避免使用in和not in，否则全盘扫描
模糊查询尽量避免前置%，否则全盘扫描