MySql优化1

1.show engines;
2.innodb 支持事务，行及锁定，外键
3. show variables like 'innodb_file_per_table';//查看每个innodb表的数据和索引是否创建单独的文件存储
4. set global innodb_file_per_table=1; //设置每个innodb表的数据和索引创建单独的文件存储
5. 表结构文件orderl.frm 单独的数据和存储文件orderl.ibd,ibdata1 是全部innodb表的数据和索引的存储文件
6. innodb表数据的存储是按照主键的顺序排列每个写入的数据。—该特点决定了该类型表的写入操作较慢。
7. 外键：两个数据表A和B，B表的主键是A表的普通字段，在A表看这个普通的字段就是该表的“外键”，外键的使用有”约束”。 约束：必须先写B表的数据，在写A的数据，且A表的外键取值必须来之B标的主键的id，不能超过其范围。—-在真实项目中，极少使用外键，因为有约束。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
9. Myisam 表结构、数据、索引 都有独立的存储文件. .frm是表结构文件 .MYD是表数据文件 .MYI是表索引文件——-独立的存储文件可以单独备份、还原。
10. Myisam 表数据的存储是按照自然顺序排列每个写入的数据（即插入的顺序）
11. Myisam 并发性较低 ，锁的级别为表锁。
12. insert into order3 select null,num from order3; 复制当前表中的数据
13. flush table order3; 刷新数据表
14.innodb存储引擎：适合做修改、删除
Myisam存储引擎：适合做查询、写入
15.Archive 归档型存储引擎 只可以写入，查询操作，没有修改，删除操作–适合存储日志性质的信息。
16.memory 内存型存储引擎，操作速度快，比较适合存储临时信息。服务器断电，给存储引擎的数据立刻消失。
17.char 固定长度 运行速度快，长度为255字符限制
varchar长度不固定，该类型要保留1-2个字节保存当前数据的长度，有长度限制65535。存储汉字，utf8（每个汉字占3个字节），最多65535/3-2
18.ip与整数信息转化 select inet_aton('192.168.119.123');
select inet_ntoa(3232266107);
19.字段类型选择
原则：占据空间小、数据长度最好固定、数据内容最好为整型的
20.三范式
范式一：原子性，数据不可以再分割
范式二：数据没有冗余
范式三：数据表每个字段与当前表的主键产生直接关联(非间接关联)。

---

性能优化————-索引
1.索引本身是一个独立的存储单位，在该单位里边有记录着数据表某个字段和字段对应的物理空间。
2.索引类型
① 主键 primary key
auto_increment必须给主键索引设置
信息内容要求不能为null，唯一
② 唯一 unique index
信息内容不能重复
③ 普通 index
没有具体要求
④ 全文 fulltext index
myisam数据表可以设置该索引
复合索引：索引关联的字段是多个组成的，该索引就是复合索引。
3.创建表：

CREATE TABLE `student` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(32) NOT NULL DEFAULT '',  `height` tinyint(4) NOT NULL DEFAULT '0',  `addr` varchar(32) NOT NULL DEFAULT '',  `intro` text,  PRIMARY KEY (`id`),  UNIQUE KEY `nm` (`name`),  KEY `height` (`height`),  FULLTEXT KEY `intro` (`intro`)) ENGINE=MyISAM DEFAULT CHARSET=utf8

4.给现有的表添加索引：

alter table bww add primary key(id); alter table bww add unique index(addr); alter table bww add index(height);alter table bww add fulltext index(intro);

创建一个复合索引：索引没有名称，默认把第一个字段取出来当做名称使用。若该索引名称存在 则默认在该索引名字后面加 _2 .
alter table bww add index (height,addr);
5.删除索引：
删除主键索引：
alter table 表名 drop primary key;若表存在auto_increment的话，需要先修改，将该属性去除。alter table bww modify id int not null;
删除其他索引：alter table 表名 drop index 索引名称;
6.索引适用的场景
where查询条件–where 之后设置的查询条件字段都适合做索引。
排序查询–order by 字段
索引覆盖–我们查询的全部字段已经在索引里边存在，就直接获取即可
不用到数据表中再获取了。因此称为”索引覆盖”，该查询速度非常快，效率高，该索引也称为”黄金索引”。
7.字段独立原则
1）只有独立的条件字段才可以使用索引
select * from emp where empno=1325467; //empno条件字段独立
select * from emp where empno+2=1325467; //empno条件字段不独立
2）左原则— 查询条件信息在左边出现，就给使用索引
XXX% YYY_ 使用索引
%AAA% ABC %UUU 不使用索引
3）复合索引
ename复合索引 内部有两个字段（ename，job）
① ename(前者字段)作为查询条件可以使用复合索引
② job(后者字段)作为查询条件不能使用复合索引
4)OR原则
OR左右的关联条件必须都具备索引 才可以使用索引;
or的左右，只有一个有索引，导致整体都没有的使用;

---

1.索引设计依据
① 被频繁执行的sql语句要设置
② 执行时间比较长的sql语句(可以统计)
③ 业务逻辑比较重要的sql语句
2. 前缀索引
如果字段的前边N位的信息已经可以足够标识当前记录信息，就可以把前边N位信息设置为索引内容，好处：索引占据的物理空间小、运行速度就非常快。
select count(*)/count(distinct substring(字段，开始位置1，长度) ) from 表名； 长度一直变大，直到结果稳定在1附近，此时的长度n就是我们需要的值，
alter table 表名 add index(字段（位数）)
4.全文索引：mysiam和Innodb都支持全文检索，但是中文不支持全文检索。
单列全文索引：
alter table bww add fulltext index index_content(name);
普通sql模糊查询不能使用全文索引，需要变形为match（） against();
explain select * from bww where match(name) against('bww');
复合全文索引：
alter table bww add fulltext index index_con (addr,name);
explain select * from bww where match(addr,name) against ('nantong,bww');

两种索引结构
① 非聚集索引结构(Myisam)
② 聚集索引结构(Innodb)
5.设置缓存
查看： show variables like ‘query_cache%’;
设置：set global query_cache_size=64*1024*1024;
sql语句有变动的信息，就不使用缓存
例如有时间信息，随机数。
相同结果不同样子的sql语句会分别缓存（大小写）
针对特殊语句 不需要缓存
select sql_no_cache * from bww where empno=212;
查看缓存空间状态
show status like ‘Qcache’;