mysql学习笔记之优化篇一

mysql学习笔记之优化篇一

用SQL技巧和常见问题

使用正则表达式

正则表达的的语法我就不说了，采用pcre风格的
基本语法就是
    regexp '正则表达式'

使用rand()提取随机行

select * from country order by rand() limit 1;

使用group by的with rollup进行聚合

select id,count(*) as c  from country group by id  with rollup;

用bit group function是做统计

bit_or函数就是对两个二进制数进行或运算。
bit_count函数的含义用来计算二进制数中包含1的个数。

计算数据中1的位数

注意数据库名大小写的问题

注意外键使用过程中的约束，只能是innodb引擎

SQL优化

优化sql语句的一般步骤

show status 了解sql的执行频率

show status like '%Com%';
各个操作的频率
    Com_select
    Com_insert
    Com_update
    Com_delete
只针对innodb引擎
    innodb_rows_select
    innodb_rows_insert
    innodb_rows_update
    innodb_rows_delete

了解数据库的基本情况
    connections:视图连接mysql服务器的次数
    uptime:服务器工作时间
    show_queries:慢查询次数

定位执行效率低的sql语句

    1 通过慢查询日志定位效率低的sql语句 --log-show-queies
    2 show processlist 查看当前线程状态和锁状况

explain 分析低效sql的执行计划

select_type:表示select的类型
    simple简单表,primary主查询,union后面的查询语句,subquery子查询的第一个子查询.
table  输出结果集中的表
type 表的连接类型性能由好到差
    system 表中仅有一行
    const 单表中最多有一个匹配行
    eq_ref 对于前面的每一行，在此表中只查询一条记录，多表查询中
    ref 与eq_ref类似区别是使用普通索引
    ref_or_null 与ref类似区别是包含null
    index_merge 索引合并优化
    unique_subquery in的后面是一个查询主键字段的子查询
    range 表中的范围查询
    index 对于前面的每一行都通过索引查询
    all 全表扫描
possibe_key
key 表示实际使用的索引
key_len 索引的长度
rows 扫描行数
extra 情况说明

确定问题，然后才去优化措施，常规的手段是创建索引

索引的基本知识

组成
    myisam索引和数据分开
    innodb是放在一个空间，但是由多个文件组成
索引的分类：
    btree和hash
myisam和innodb 只支持btree
memory和heap支持hash和btree

索引的使用

1 索引支持左前缀索引
2 like 第一行不能是%
3 对于大文本搜索不能使用%%，可以使用全文搜索
4 如果列名是索引，使用 column_name is null将使用索引

索引无法使用的情况

1 索引比全表扫描更慢
2 使用memory/heap 表并且where条件中不使用=进行索引列
3 使用or ，or不是所有列都添加了索引
4 不是索引列的一部分
5 like 以%开头
6 如果列类型是字符串，那么一定要where中的字符串用引号引起来

查看索引使用情况

show status  like 'Handler_read%';

Handler_read_key 代表索引值被读的次数，很低代表索引得到的性能改善不高，索引不是经常使用

    | Handler_read_first    | 0     |
    | Handler_read_key      | 2     |
    | Handler_read_last     | 0     |
    | Handler_read_next     | 0     |
    | Handler_read_prev     | 0     |
    | Handler_read_rnd      | 0     |
    | Handler_read_rnd_next | 0  

两种简单实用的优化方法

定期分析表和检查表
    analyze table xx分析和存储表的关键字分布
    check table xx检查表

定期优化表

    optimizie table xx表

常用的sql大批量插入

对应myisam引擎，大批数据插入，关闭非唯一索引的更新
        alter table xxx disable keys
        load data file xxx
        alter table xxx enable keys
innodb提高导入效率
    导入的数据按照主键顺序排列
    关闭唯一校验，set unique_checks=0,set unique_checks=1然后打开
    关闭自动提交 set autocommit=0,set autocommit=1

优化insert

如果从一个客户端插入数据，批量插入数据
如果不同客户插入很多行，可以insert delayed。让insert马上执行
将索引文件和数据文件分在不同的磁盘上存放
针对myisam可以使用bulk_insert_buffer_size提高速度
从文件到表可以采用load data insert

优化order by语句

使用索引来满足order by语句，如果与索引顺序一致，且order by的字段都是升序降序

优化嵌套查询

使用join代替子查询

优化or

or的列都加上索引

使用sql提示

use index 优先考虑某些索引
    select * from test use index(index_id) where id=123

ignore index 忽略某一个索引

force index 强制使用某个索引

优化数据库对象

优化表的数据类型

procedure analyse() 进行优化

select * from country procedure analyse()\G;
*************************** 1. row ***************************
             Field_name: test.country.name
              Min_value: book.sina.com.cn
              Max_value: book.sina.com.cn
             Min_length: 16
             Max_length: 16
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 16.0000
                    Std: NULL
      Optimal_fieldtype: ENUM('book.sina.com.cn') NOT NULL
*************************** 2. row ***************************
             Field_name: test.country.user_age
              Min_value: 3
              Max_value: 3
             Min_length: 1
             Max_length: 1
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 3.0000
                    Std: 0.0000
      Optimal_fieldtype: ENUM('3') NOT NULL
*************************** 3. row ***************************
             Field_name: test.country.id
              Min_value: 1
              Max_value: 12
             Min_length: 1
             Max_length: 2
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 7.3333
                    Std: 5.0332
      Optimal_fieldtype: ENUM('1','2','3','11','12') NOT NULL

看吧mysql给了一些建议

通过拆分表提高表的访问效率

垂直拆分，把表的信息拆分出去作为另外一个表
    1 常用的信息
    2 主键和其他信息

水平拆分，把一个表分成多个信息一致的表
水平拆分的场景
    1 表很大
    2 表数据独立性
    3 需要把数据放到不同的介质上

逆向规范化

增加冗余列
增加派生列
重新组表--两个表组合成一个新表
分割表

逆向规范化后维护数据的完整性，可以采用批处理，触发器等方式

使用中间表提高表的统计查询速度