Mysql优化

作者: 蜡笔没了小新_e8c0 | 来源:发表于2019-05-16 00:17 被阅读0次

优化

首先，按照三范式的要求创建表，即满足数据不冗余。同时还可以对一些表进行分表操作，分表分为水平分表和垂直分表，水平分表可以按照时间，id，hash值进行分表，例如微博就可以采用时间分表，一些旧的微博内容就可以单独提取出来。垂直分表是将字段拆分出来，将一些长度比较大的字段拆分成一个新表，例如博客系统，文章内容单独拆成一个表，因为文章内容查看的次数相对较少，内容又比较大所以可以拆分出来。创建表时还应该选择合适的数据库引擎，比如mysql常用的InnoDB和myisam，InnoDB支持事务和外键，采用的也是行级锁，适合一些修改操作频繁的表，而myisam支持全文索引，采用的是表级锁，适合查询比较多的表。同时，还可以借助redis实现缓存功能。此外，也可以采用主从服务的方式，实现读写分离，主服务器进行写操作，从服务器进行读操作，从服务器负责监听主服务器，如果主服务器执行了写操作，会将相应的写操作发给从服务器进行数据的更新。

索引

如何定位并优化慢查询Sql

根据慢日志定位慢查询sql
使用explain等工具分析sql
修改sql或者尽量让sql走索引

sql语句：

show variables like '%quer%';   //显示属性
show status like '%slow_queries%';    //显示慢语句条数
set global slow_query_log = on;    //开启慢语句查询日志
set global long_query_time = 1;    //设置时间

InnoDB和MyISAM之间的区别

InnoDB是聚集索引，数据文件是和索引绑在一起的，即数据都是存放在叶子节点中的，必须要有主键，通过索引效率很高。但是辅助索引需要两次查询，先查询到主键，再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是稀疏索引，数据文件是分离的，结点保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB支持外键，MyISAM不支持外键。
InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务。
InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；不过如果查询语句加上了where条件，MyISAM也会进行全表扫描。
DELETE FROM table时，InnoDB会一行一行删除记录；MyISAM会重新创建该表。

	InnoDB	MyISAM
事务	支持	不支持
外键	支持	不支持
具体行数	不保存	保存
索引类型	聚集索引	稀疏索引

聚集索引和稀疏索引

聚集索引：行数据和主键B+树存储在一起，辅助键B+树只存储辅助键和主键。如果直接使用主键进行查找记录的话，直接通过检索主键B+树就能获得相应的记录数据；如果使用其余条件进行检索的话，就需要两个步骤：1.在辅助索引B+树中检索出对应的主键；2.使用主键在主键B+树中进行检索操作，最终到达叶子节点，获取到相应的记录数据。
稀疏索引：两棵B+树结构相同，只不过主键B+树节点存储的是主键，辅助键B+树节点存储的是辅助键，两棵树的叶子节点都指向真正的表数据地址。无论通过什么键进行查询，都只需遍历相应的B+树到达叶子节点，获取到相应记录真正的地址即可。

聚集索引的好处？

行数据和叶子节点存储在一起，主键和行数据都在内存中，查询到叶子节点就可以立刻将行数据返回，查询数据更快。
辅助索引使用主键作为指针，避免当出现行移动或者数据页分裂时，需要进行的索引维护工作。

锁

InnoDB和MyISAM的锁不同

InnoDB采用的索引行级锁，即通过索引进行操作时使用的是行级锁，其他操作都是表级锁。行级锁在加锁和释放锁的过程中都会比表锁消耗更多的资源。在两个事务发生死锁的情况下，会计算出每个事务影响的行数，然后回滚行数少的事务。
MyISAM采用的是表级锁。

事务

原子性A（Atomicity）
一致性C（Consistency）
隔离性I（Isolation）
持久性D（Durability）

sql操作：

select @@tx_isolation;   // 显示隔离级别

事务并发访问问题

更新丢失——mysql所有事务隔离级别在数据库层面上均可避免
脏读——READ-COMMITTED事务隔离级别以上可避免
不可重复读——REPEATABLE-READ事务隔离级别以上可避免
幻读——SERIALIZABLE事务隔离级别可避免

当前读和快照读

当前读：select ... lock in share mode , select ... for update,update,delete insert
快照读：不加锁的非阻塞读，select

事务的快照时间是按照第一个select出现来确认的，即如果在第一个select语句之前执行相应的修改操作，快照取出的值也是按照修改后的。

对主键索引或者唯一索引使用Gap锁情况

如果where条件全部命中，则不会用Gap锁，只会加记录锁
如果where条件部分命中或者全部命中，则会加Gap锁
Gap锁会用在非唯一索引或者不走索引的当前读中

存储过程

DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=100000000 DO
        INSERT INTO user(username,age) VALUES(CONCAT('用户',i),i/1000);
        SET i = i+1;
    END WHILE;
END $
CALL proc_initData();

慢查询

show variables like '%slow_query_log%'; 查询相关字段
set global slow_query_log=ON; 将慢查询开启
show variables like '%log_output%'; 日志存储格式
set global log_output='TABLE'; 修改日志存储格式
show variables like 'log_queries_not_using_indexes'; 未使用索引的查询是否会被记录

1.select * from table where a>0 and b<0，ab是联合索引，问索引能不能命中？

可以命中，不过key_len只有一半。

2.MySql的char和varchar的区别？

char不管值的长度都会占用给定的字符数；varchar会根据实际的情况占用字节空间。
char最大字节数为255字节，varchar为65535字节（如果使用utf8编码格式，一个字符占用3个字节，最大设定值为21844）。
char在存储时是会对尾部的空格进行裁剪，varchar不会。

3.sql索引失效场景？

如果字段类型是字符串需要加上引号
使用or
最左匹配原则
like以%开头
如果mysql估计使用全表扫描要比使用索引快,则不使用索引

4.事务是什么？事务的四大特性？

事务是用户定义的一个操作序列。

原子性：操作要么全做，要么全不做。
一致性：事务执行的记过必须是使数据库从一个一致性状态变到另一个一致性状态。
隔离性：一个事务的执行不能被其他事务干扰。
永久性：一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。

1.整形宽度

对于MySQL而言，可以为整数类型指定宽度，但是对于MySQL而言，无论设置什么宽度对于存储和计算而言都是一样的，只是规定了交互工具用来显示字符的个数。

2.varchar和char

varchar需要额外的1或2个字节来记录字符串的长度（255字节以内的用一个字节记录长度）。varchar在记录执行update操作时，有可能会使原来的行更长。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

3.datatime和timestamp

datatime：能保存从1001到9999年之间的。与时区无关。使用8个字节的存储空间。
timestamp：1970-2038。保存了从1970年1月1日（格林尼治标准时间）以来的秒数。显示的值依赖于时区。只使用4个字节的存储空间。默认为NOT NULL。

4.IP地址存储

使用无符号整数存储IP地址。

INET_NTOA() ：将整数转换为IP地址字符串
INET_ATON()：将IP地址字符串转换为整数

5.存储计数内容？

可以一个计数器表，里面就一个字段count用来保存计数值，然后为了提高并发更新性能，可以将先创建若干条计数记录，进行更新时只需随机选择一行进行更新，当获取总计数值时对相关记录进行求和操作。

如果需要按照日期进行计数可以按照如下方式建立表：

create table daily_hit_counter(
    day date not null,
    slot tinyint unsigned not null,
    cnt int unsigned not null,
    primary key(day,slot)
)ENGINE = INNODB;

利用ON DUPLICATE KEY UPDATE 的方式进行值的加1。


insert into daily_hit_counter(day,slot,cnt) values(CURRENT_DATE,RAND()*100,1) on DUPLICATE key UPDATE cnt = cnt +1;

同时可以开启定时任务对计数值进行合并。

update daily_hit_counter as c inner join (
    select day,SUM(cnt) as cnt,MIN(slot) as mslot
    from daily_hit_counter group by day
) as x using(day) 
set c.cnt = IF(c.slot = x.mslot,x.cnt,0),
 c.slot = IF(c.slot = x.mslot,0,c.slot);
 delete from daily_hit_counter where slot <> 0 and cnt = 0;

6.ALTER COLUMN 和 MODIFY COLUMN区别

alter column：通过直接修改.frm文件中的属性值
modify column：重建表

7.如何选择组合索引列的排序？

在不考虑排序和分组的情况下可以按照选择性最高的列放在最前面的原则。有的列如果需要经常进行范围查询（age），就需要把这类字段放在最后；或者可以使用IN查询罗列条件（sex），就可以把这样的列放在前面。

8.InnoDB使用随机主键的缺点？

写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致产生大量的随机I/O。
因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

9.覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就之为“覆盖索引”。

10.MySQL中的状态

Sleep：线程正在等待客户端发送新的请求。
Query：线程正在执行查询或者正在将结果发送给客户端。
Locked：在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。
Analyzing and Statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划。
Copying to tmp table [ on disk ] ：线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上。
Sorting result：线程正在对结果集进行排序。
Sending data：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

11.IN()查询

在大部分数据库系统中，IN()完全等同于多个OR条件的子句。而在MySQL中，会先将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O(logn)复杂度的操作，而OR查询的复杂度为O(n)。对于IN()列表中有大量取值的时候，MySQL的处理速度将会更快。

12.查询优化器

优化器的作用是找到查询语句多种执行方式中最好的那种，是基于成本优化。

12.1 查询优化器不能选择最优的执行计划

统计信息不准确。存储引擎不能提供准确的统计信息。（InnoDB不能维护数据表精确的行数统计信息。）
MySQL层面由于不知道页面在内存还是磁盘中，所以无法得知实际执行过程需要多少次物理I/O。
成本最优 != 速度最快
+MySQL不考虑并发执行的查询情况。
基于成本的优化是以固定规则为前提的。例如，如果使用了全文搜索的MATCH()，则在存在全文索引的时候就是用全文索引。即使有时候使用别的索引和WHERE条件可能远比这种方式要快。
MySQL不会考虑不受其控制的操作的成本（存储过程或者用户自定义函数）。

12.2 优化的类型

静态优化，也即编译时优化。是直接对解析树进行分析，并完成优化。只需进行一次静态优化。
动态优化，运行时优化，hhui和查询的上下文、WHERE条件中的取值、索引中条目对应的数据行数等有关。在每次执行时都需要重新评估。

13.explain中的rows是什么？

rows是对SQL执行过程中会被扫描的行数的一个估计值。

14.mysql获取锁的过程示例

事务A获取Id为6的行的排他锁，并未提交

SELECT * FROM users WHERE id = 6 FOR UPDATE;

1.事务A先拥有users表的意向排他锁；
2.拥有id为6的行排他锁。

事务B想要获取users表的共享锁

LOCK TABLES users READ;

1.首先检测到事务A拥有users表的排他锁；
2.事务B阻塞。

事务C想要获取id为5的行的排他锁

SELECT * FROM users WHERE id = 5 FOR UPDATE;

1.事务C申请users表的意向排他锁；
2.检测到事务A执行意向排他锁，由于意向锁之间兼容，所以拿到users表的意向排他锁；
3.检测id为5的行是否存在锁，因为没有加锁，所以事务C获取到id为5的行的排他锁。