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_keykey 表示实际使用的索引key_len 索引的长度rows 扫描行数extra 情况说明
确定问题，然后才去优化措施，常规的手段是创建索引
索引的基本知识
组成 myisam索引和数据分开 innodb是放在一个空间，但是由多个文件组成索引的分类： btree和hashmyisam和innodb 只支持btreememory和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 keysinnodb提高导入效率 导入的数据按照主键顺序排列 关闭唯一校验，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=123ignore index 忽略某一个索引force index 强制使用某个索引
优化数据库对象
优化表的数据类型
procedure analyse() 进行优化
select * from country procedure analyse()\G;*************************** 1. row *************************** Field_name: test.country.name Min_value: book.sina.cn Max_value: book.sina.cn Min_length: 16 Max_length: 16 Empties_or_zeros: 0 Nulls: 0Avg_value_or_avg_length: 16.0000 Std: NULL Optimal_fieldtype: ENUM('book.sina.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: 0Avg_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: 0Avg_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 需要把数据放到不同的介质上
逆向规范化
增加冗余列增加派生列重新组表--两个表组合成一个新表分割表逆向规范化后维护数据的完整性，可以采用批处理，触发器等方式
使用中间表提高表的统计查询速度