mysql进阶优化

一、数据库引擎 innodb

查看所有引擎
show engines;
默认 innodb 原来是 myisam

指定引擎
create table 表名（....）engine=innodb
1.逻辑结构
 tablespace-segment-extent(1M)-page(16k)-row(数据)
2.特点
支持事务，行级锁，外键

二、索引
 主键、唯一索引、常规索引、全文索引

创建索引
create [unique]  index 索引名 on 表名(字段名);
//一般索引
create index idx_user_name on ta_user(name);
//联合索引
create index idx_name_pro_age on tb_user(name,professio,age);

//查看索引
showi index from table
//删除索引
drop index 索引名 on ta_user;

三、优化
1.产看使用频率
show global status like 'Com_______';//7个_;

2.可以开启慢查询日志
/etc/my.cnf文件中配置
//开启日志
show_query_log=1
//慢查询日志的时间
long_query_time=2

在文件/var/lib/mysql/localhost_slow.log

3.性能分析
性能分析是否支持
select @@have_profiling;
//查看开启状态
select @@profiling;
//开启
set profiling=1;
//查看所有语句耗时
show profiles;
//查看分析一条语句耗时
show profile for query query_id;
//查看cpu 使用情况
show profile cpu for query query_id;

//执行计划分析
explain sql语句；

4.联合索引失效问题
  从最左边开始生效。没有，后续就不生效。
  >< 范围右侧失效，>=,<=不会失效

5。索引失效
 1.字符串不加引号
 2.字段调用函数
 3.模糊匹配，头部模糊匹配，会索引失效
 4.or会让索引失效（解决，or前后条件都加索引）

三、sql 优化

1.插入数据
多条数据，批量操作。
手动控制事务。
主键顺序插入。

使用load
set global local_infile=1;

load data local infile '/root/sql.log' into table 'tb_user' fields terminated by ',' lines treminated by '\n';

2.update 跟新
跟新条件要有索引，不然会表锁。
3.limit 优化
覆盖索引+子查询
select * from tb_user limit 20000,10;
select * from tb_user u,(select id from tb_user limit 20000,10) i where u.id=i.id;

四、全局锁、表锁、行级锁

1.全局锁(备份数据库时使用)
加锁(只能读取数据)
flush tables with read lock; 
备份数据库
mysqldump  -uroot -p123456
解锁
unlock tables;

不加锁一致性备份。平时用
mysqldump --single-transaction -uroot -p123456 数据库名>TEST.sql;

2.表锁
加
lock tables store read\write;
释放锁
unlock tables; 

在一个事务中，增删改查会添加shared_read锁，其他的连接只能读不能写，会阻塞。

在一个事务中，对表结构进行修改时，其他的连接其他操作都不行。