演示数据库获取

MySQL数据库优化

SELECT
    a.TABLE_SCHEMA AS '数据名' ,
    a.TABLE_NAME AS '表名' ,
    a.INDEX_NAME AS '索引1' ,
    b.INDEX_NAME AS '索引2' ,
    a.COLUMN_NAME AS '重复列名'
FROM
    STATISTICS a
JOIN STATISTICS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
AND a.COLUMN_NAME = b.COLUMN_NAME
WHERE
    a.SEQ_IN_INDEX = 1
AND a.INDEX_NAME <> b.INDEX_NAME

慢查日志的内容格式：

使用Mysql慢查询日志对有效率问题的sql进行监控的相关语句：

服务器硬件优化:

     1.cpu选择:mysql一个sql的执行只能用到单核的cpu,其次,在复制进程的时候也是只能用到单核的cpu,所以cpu并不是越多越好,mysql5.5是的服务器不要超过32核,偏向选择单核频率更快的cpu;

    2.Disk IO 优化

    常用的RAID 级别简介

    RAID0: 也称为条带,多个磁盘接成一个使用,io最好(但是磁盘坏了,数据没了,安全性差)

    RAID1: 也称镜像,要求至少两个磁盘,每组磁盘存储的数据相同;

    RAID1+0:  就是RAID1和RAID0的结合,同时具备两个级别的优缺点,推荐数据库使用这个级别;

  目前可能还有更好的存储设备:比如ssd卡

1. 表的垂直拆分:将表中的不常用的列和大字段的列拆分到另外一个表或者多个表中,减少表的宽度;

2. 表的水平拆分:主要是解决数据量过大的问题,水平拆分每个表的表结构都是完全一致的(当单表的数据大于一亿时,尽管加了索引,还是会比较慢);

3. 水平拆分方法:对id进行hash运算(取模),如果拆分为5个表,则使用mod(id)取出0-4个值,针对不同的hashID吧数据存到不同的表中;
   

数据库反范式化:符合第三范式基础上适当增加冗余,已达到查询优化的目的;

数据库结构设计:数据表中不存在非关键字段对任意的候选关键字段的传递函数依赖

1. 用 not null 定义字段,因为定义null的字段会采用额外的字段去存储,io开销大;

2. 尽量不使用text类型,使用简单而小的数据类型;

3. 时间类型可以存储int类型(占用内存小)利用from_unixtime()(时间戳转时间)和unix_timestamp()(时间转时间戳)函数进行转化

4. IP地址使用使用bigint存储,利用inet_aton(),inet_ntoa()两个函数转换(inet_aton()是将一个字符串IP地址转换为一个32位的网络序列IP地址,inet_ntoa()相反)
   

mysql中只能通过分析慢查日志配置pt-index-usage工具进行索引使用情况的分析,看哪些索引不常用,从而删除;

1. 增加索引增加查询速度,但是减慢了insert update; 

2. 因此要查找处重复和冗余的索引(多个索引的前缀相同,或者联合索引中包含了主键的索引);可以 pt-duplicate-key-checker工具检查重复的索引,然后删除重复的索引

建立联合索引:离散度大的放在前面;

离散度查询: select count(distinct custom_id),count(distinct staff_id) from table;

count的数值越大说明离散度越大,则建立联合索引就放前面;index(customer_id,staff_id);

limit 优化:

limit会使用Filesorts这样造成大量的IO问题;

例如:select  film_id , description from salika.film order by title limit 500,5;

分析:当limit 后面的数据越大,IO越高,性能越差;可以使用主键排序进行优化,避免过多的扫描;

优化:select film_id , description from sakila.film where film_id >=500 and film_id <= 505 order by film_id limit 1,5;

这样每次秒扫描都只有5行  ,有个问题是:每个film_id索引列必须是连续的  ,如果不连续可能会导致查出来的列不足5行,解决方法:可以加一个索引列,保证是连续的,对该列查询;

1. using()用于两张表的join查询，要求using()指定的列在两个表中均存在，并使用之用于join的条件;例如: select a.*, b.* from a left join b using(colA);

2. group by 的列尽量要使用在有索引的列上,否则就会使用临时表和文件