MYSQL数据库高级

1. 数据库高级

本节忽略了mysql数据库基础内容，即数据库的增删改查等基本用法，着重展示数据库结构及查询优化等内容。

1.1 数据库结构

连接层—>服务层—>引擎层—>存储层

1.2 性能下降的原因

• 查询语句书写不合理（自己写的烂）
• 索引失效（单值索引，复合索引）
• 关联查询使用太多join（设计缺陷或不得已的需求）
• 服务器调优及各个参数设置（缓冲，线程数量等设置）

2. 调优辅助工具explain

通过在sql语句前面添加explain字段，可显示执行计划，用于辅助调优

id	select_type	table	type	possible_key	key	key_len	ref	row	extra

• id
  id相同，sql由上至下执行表
  id不同，若是子查询，id递增，id越大越先执行（类似优先级）
  id相同又不同，先按照id大的执行，id相同的再顺序执行
• type
  扫面类型，主要有并按性能排序依此为system > const > eq_ref > ref > range > index >all
  system:只有一条记录的扫描（扫描速度当然快）
  const：通过索引一次找到
  eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
  ref：非唯一性索引扫描，表中只有多条记录与之匹配
  range：按索引范围扫描
  index：全索引扫描
  all:全表扫描
• possible_key
  mysql优化器认为可能用到的索引
• key
  实际用到的索引
• key_len
  记录索引字段的最大可能长度，并非实际使用长度
• ref
  显示索引的哪一列被使用，哪些列或常量被用于查询索引列上的值
• row
  每张表有多少行被优化器查询
• extra
  显示额外的性能信息，例如using where（使用了where语句），using index（使用了index的值，这种情情况相对较好，因为无需回表，覆盖索引），using filesort（查询过程中用到了文件重排，一般出现该字段需要优化），using temporary（查找过程中用到了临时表，性能影响比filesort还大）

3. 索引优化

索引是对数据库表中一列或多列的值进行排序的一种结构（B树结构），使用索引可快速访问数据库表中的特定信息。索引主要用于查找和排序。

3.1 索引设置方法

• 单表
  复合索引中，若有范围查询（< > !=)则出现部分往后索引失效，因此，尽量减少对有范围的字段建索引
• 双表
  左连接给右表字段建索引，右连接给左表字段建索引
• 三表
  同双表，前两结合后再分析同第三张表的关系。

3.2 join语句优化

• 尽量减少join语句中嵌套循环中的循总次数，永远小表驱动大表
• 优先优化嵌套循环的内层循环
• 保证join语句中被驱动表上join条件字段已经被索引
• 当无法并保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝啬join buffer的设置。

3.3 索引失效

假设在表中有id name age birth sex字段，对name age birth 建了索引

• 条件查询时按照索引顺序全值匹配select * from table where name= age= birth=,至少遵循最左前缀法则：若索引了多列，则要求查询从索引最左前列开始且不跳过索引中的列，否则索引失效。但可以乱序，因为优化器会自动优化顺序。
• 不在索引列上做任何操作（计算，函数，类型转换等），否则索引失效，转向全表扫描。
• 条件包含范围查询，则范围查询后的索引失效
• 尽量使用覆盖索引 减少select * 的使用，转而使用select name，age,birth
• 使用！= < >索引失效
• 使用is null,is not null 索引失效
• like以%开头索引失效，此时可使用覆盖索引解决
• 字符串不加单引号索引失效
• 使用or索引失效
  口诀总结：全值匹配我最爱，最左前缀要遵守。带头大哥不能死，中间兄弟不能断，索引列上少计算，范围之后全失效。like百分写最右，覆盖索引不写*。不等空值还有or，索引失效要少用。

3.4 查询优化

1.exist，in的用法（小表驱动大表原则）

select * from A where id in (select id from B);
等价于：
for select id from B
for select * from A where A.id = B.id
当B表小于A表时用in优于exists

select * from A where exists (select xx from B where B.id = A.id);
等价于：
for select * from A
for select * from B where B.id = A.id
当A表小于B表时用exists优于in

2.order by优化

• 少用select *
• 提高sort_buffer_size
• 提高max_length_for_sort_data

3.为排序使用索引

首先为abc字段建索引
order by 能使用索引符合最左前缀原则
order by a;
order by a,b;
order by a,b,c;
order by a DESC,b DESC,c DESC;

若where使用索引的最左前缀定义为常量，则order by能用索引
where a = const order by b,c;
where a = const and b = const order by c;
where a = const and b > const order by b,c;

不能用索引进行排序
order by a ASC,b DESC,c DESC /*排序不一致*/
where g = const order by b,c /*丢失a索引*/
where a = const order by c /*丢失b索引*/
where a = const order by a,d /*d不是索引的一部分*/

4. 数据库锁

4.1 表锁

表锁主要体现在MyISAM引擎，对MyISAM的表加读锁，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求；对MyISAM的表加写锁，会阻塞其他进程对同一表的读写操作。

4.2 行锁

行锁主要体现在InnoDB引擎，，因为InnoDB与MyISAM的最大区别就是InnoDB支持事务以及行锁功能。
行锁演示：session1对表某行更新，但未commit，此时读己之所写。此时session2对该表同行更新则挂起，直到session1 commit，则执行session2的更新，完成后session2 commit后生效。不同行的操作则不影响。

4.3 注意事项

• 索引失效则行锁变表所（在生产中varchar不加单引号会引起次情况，且较为隐蔽，难以发现）
• 间隙锁危害
  当范围条件检索数据并请求共享或排他锁时，InnoDB会把符合条件的所有已有记录的索引项加锁，对于键值在范围内但不存在的记录，叫“间隙”。InnoDB也会对这个间隙加锁，这种机制叫间隙锁。危害：即使某些不存在的键值也会被锁，导致其他进程无法插入锁定范围内的任何数据，从而阻塞。
• 如何手动锁定一行(添加for update)
  begin;
  select * from table where xx = xx for update;
  commit;

4.1 mysql常用函数

聚集函数
avg
count
max
min
sum

用于处理字符串的函数
合并字符串函数：concat(str1,str2,str3…)
比较字符串大小函数：strcmp(str1,str2)
获取字符串字节数函数：length(str)
获取字符串字符数函数：char_length(str)
字母大小写转换函数：大写：upper(x),ucase(x)；小写lower(x),lcase(x)

用于处理数值的函数
绝对值函数：abs(x)
向上取整函数：ceil(x)
向下取整函数:floor(x)
取模函数：mod(x,y)
随机数函数：rand()
四舍五入函数：round(x,y)
数值截取函数：truncate(x,y)

用于处理时间日期的函数
获取当前日期：curdate()，current_date()
获取当前时间：curtime()，current_time()
获取当前日期时间：now()
从日期中选择出月份数：month(date),monthname(date)
从日期中选择出周数：week(date)
从日期中选择出周数：year(date)
从时间中选择出小时数：hour(time)
从时间中选择出分钟数：minute(time)
从时间中选择出今天是周几：weekday(date),dayname(date)