mysql优化

1、 慢查询日志：
1）开启慢日志（2）时间界限
mysql.ini 设置 log-slow-queries = '路径' long_query_time = 时间
2） profile 机制查询操作需要的时间
set profiling = 1;开启 0关闭
show profiles; 查询记录结果
3）索引的创建
①建表时
add primary key (id)
②创表后
alter table 表名 add primary key (id)
删除索引(主键有自增长先删自增长)
alter table 表名 change id id int
alert table 表名 drop primary key id
4) 索引的数据结构
① innodb
主索引文件上存放该行的数据，非主键索引指向主键id,称为聚簇索引非主索引指向对主键的引用
② myisam
主索引和非主索引都指向物理行（磁盘位置）
5) explain(执行计划)工具使用
语法：explain sql语句\G 或 desc sql语句\G
6) 索引使用
①多列(复合)索引（需要按顺序执行的)
where c1=x and c2=x and c4=x order by c3
②索引的常见的误区
”%aaa”,不会使用到索引
条件中有or，则要求or的索引字段都必须有索引否则不能用到索引。
取出的数据量超过表中数据的20%，优化器就不会使用索引，而是全表扫描
③索引覆盖
查询的列恰好是索引的一部分，那么查询只需要在索引文件上进行
一：主键ID索引 二：复合索引 查询的字段是复合字段条件是复合的索引字段
通过复合主键查询出单条字段 >(时间相差不大) 所有字段
没有复合主键字段去查询，查询时间远 > 复合主键查询

④前缀索引
字段数据的前部分作为索引，称为前缀索引
5) 优化处理
①优化翻页
1、设置最大的翻页页数
2、select * from 表名 where s_delete=1 and limit offset ,count
select * from 表名 where s_delete=1 and id > 1000 limit count
第三种没有优化
3. select a.c1 from dd a inner join (select id from dd limit 10000,10) as b on b.id = a.id;
6) 数据碎片
1、alter table xxx engine innodb
2、optimize table 表名
7) 锁机制
表锁
①上锁 lock table 表名 read | write 解锁：unlock tables;
同时锁定多张表：
lock table 表1read|write,表2 read|write,表3 read|write
read:所有人都只可以读，只有释放锁之后才可以写。
write:只有锁表的客户可以操作这个表，其他客户读都不能读。
②行锁的演示
上锁begin;commit;
只有通过索引条件检索数据，innodb才会使用行级锁，否则，innodb使用表锁。
8）分区分表技术
语法：
利用表选项：partition 完成。
create table table_name(
字段信息，
索引，
)表选项
partition by 分区算法（分区选项）
分区算法：
条件分区：list (列表) range(范围) 取模轮询（hash,key）
测试是否用到了分区：
explain partitions select * from p_list where store_id=20\G
② 分区表的限制
只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列
如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内
按日期进行分区很非常适合
9）实际应用
① 定长型 decimal(9,2)
② 手机录入用bigint
③ ip地址采用int
mysql inet_aton() 转成整形
inet_ntoa() 转ip地址
PHP ip2long(),long2ip()
④ 表示多重状态可以用数字表示
⑤ 字段值不能为null
原因：null不利于索引，要用特殊的字节来标注，在磁盘上占据的空间其实更大。
判断：is null,is not null
default '' 可以避免 null 的问题