Mysql优化方案
一、建表原则：
1、核心且常用字段，直建成定长，放在一张表；变长字段和不常用字段，另放一张表，用主键和核心表关联起来。
2、在一对多需要统计的字段，添加冗余字段。
3、添加冗余字段 原因是尽量避免表的关联操作, 空间换时间.
二、列类型选择
1、int > date time > enum char > varchar > blob text
enum 枚举，主要起约束作用
blob text 空间大，且不能在内存排序
够用就行不要慷慨尽量避免使用null
三、索引优化策略
1、btree和hash
索引是一种可以高效查询的数据结构
mysiam 索引树
table.frm 表结构
table.myd 表数据
table.myi 表索引
四、hash索引
优势：速度快【O(1)】精确查找
拉链算法
劣势
1、内存地址产生很多空洞
2、无法对范围查询优化，无法查询where id>4 这样
3、无法利用前缀索引
4、排序无法优化
5、必须回行
索引: 高效查询的数据结构, 算法+数据在磁盘的位置
btree索引: 根据树形结构高效查询
优点:
缺点:
hash索引: 数据在磁盘的位置就是根据数据内容查询出来的hash值
优点: 精准查询速度快
缺点: 磁盘产生空洞, 无法对范围查询进行优化 例如where id > 4, 无法利用前缀索引, 排序无法优化, 必须回行
索引提高查询/排序/分组速度
五、建索引常见误区
1、在where条件上常用的列都加索引【独立索引同时只能用一个】
2、在多列上建立索引后，查询哪个列，索引都将发挥作用【多列索引上索引发挥作用，需要满足左前缀要求】
索引建立在列（a,b,c）上
a=3 可使用
a=3 and b=5 可使用
a=3 and b=4 and c=5 可使用
a=3 and b like'%word' a可以使用 b无法使用
a=3 || b=4 不会使用索引
a=3 and c=4 a列索引可用 其他不会
a=3 and b>10 and c=7 a能用，b能用，c不可用
a=3 and b like'10%' and c=7 a能用 b能用 c不能
六、聚簇索引和非聚簇索引
myisam 非聚簇索引---索引文件和数据文件分开
innodb 聚簇索引--- 索引文件和数据文件在一起;多插并有序
myisam 索引指向行在磁盘上的位置
innodb 索引指向一个对主键的引用
聚簇索引：
优势：根据主键查询条目比较少时，不用回行；
劣势：如果碰到不规则的数据插入时，造成频繁的页分裂。
索引覆盖：
Extra：using index
出现索引覆盖，不需要回行
显示性能时间
mysql > set profiling=1;
mysql > show profiles;
七、理想的索引
1、查询频繁
2、区分度高
3、长度小
4、尽量能覆盖常用的查询字段
伪哈希索引
php->crc32一种哈希算法，可以把字符串算为32位整数(加索引)
倒序索引
结合实际业务+索引 从左到右原则
index(字段1,字段2,字段3...)
using filesort - 这个需要优化
ORDER BY 和 GROUP BY是比较耗资源所以需要用索引优化
联合索引 多个字段联合顺序不一样
长期的数据更改后,索引和数据会产生碎片化 - 影响操作速度,可通过nop操作,来修复表.如表引擎为innodb:
alter table xxxx engine innodb
或
optimize table xxxx
八.SQL语句化
时间->等待和执行
执行->查找和取出(可提高)
查的快->联合索引的顺序,区分度,长度
取的快->索引覆盖
传得少,少行少列
插入多行可以折几次
查询分几条简单语句查(没有必要只用一条语句),尽量少用联合查询
不查,少查,高效查(尽量在索引上查)
explain sql...
select type:
*type: 索引利用率
*possible_keys：可能用到的键
*key:肯定用到的键
*key_len:用到键的长度
*ref：引用关系（比如说两表联查时）
count-全表计数快,如果按条件可以全表减条件表
limit offset,N - 随着offset增大,效率越低速度慢
控制offset如70页内
或搜索精确点
或用条件替代limit如where id > 数 limit 3
数据不要真删,假删
或用索引覆盖再关联
select * from xxxx inner join (select id from xxx limit 10000000,3) as tmp on xxxx.id=tmp.id
1、核心且常用字段，直建成定长，放在一张表；变长字段和不常用字段，另放一张表，用主键和核心表关联起来。
2、在一对多需要统计的字段，添加冗余字段。
3、添加冗余字段 原因是尽量避免表的关联操作, 空间换时间.
二、列类型选择
1、int > date time > enum char > varchar > blob text
enum 枚举，主要起约束作用
blob text 空间大，且不能在内存排序
够用就行不要慷慨尽量避免使用null
三、索引优化策略
1、btree和hash
索引是一种可以高效查询的数据结构
mysiam 索引树
table.frm 表结构
table.myd 表数据
table.myi 表索引
四、hash索引
优势：速度快【O(1)】精确查找
拉链算法
劣势
1、内存地址产生很多空洞
2、无法对范围查询优化，无法查询where id>4 这样
3、无法利用前缀索引
4、排序无法优化
5、必须回行
索引: 高效查询的数据结构, 算法+数据在磁盘的位置
btree索引: 根据树形结构高效查询
优点:
缺点:
hash索引: 数据在磁盘的位置就是根据数据内容查询出来的hash值
优点: 精准查询速度快
缺点: 磁盘产生空洞, 无法对范围查询进行优化 例如where id > 4, 无法利用前缀索引, 排序无法优化, 必须回行
索引提高查询/排序/分组速度
五、建索引常见误区
1、在where条件上常用的列都加索引【独立索引同时只能用一个】
2、在多列上建立索引后，查询哪个列，索引都将发挥作用【多列索引上索引发挥作用，需要满足左前缀要求】
索引建立在列（a,b,c）上
a=3 可使用
a=3 and b=5 可使用
a=3 and b=4 and c=5 可使用
a=3 and b like'%word' a可以使用 b无法使用
a=3 || b=4 不会使用索引
a=3 and c=4 a列索引可用 其他不会
a=3 and b>10 and c=7 a能用，b能用，c不可用
a=3 and b like'10%' and c=7 a能用 b能用 c不能
六、聚簇索引和非聚簇索引
myisam 非聚簇索引---索引文件和数据文件分开
innodb 聚簇索引--- 索引文件和数据文件在一起;多插并有序
myisam 索引指向行在磁盘上的位置
innodb 索引指向一个对主键的引用
聚簇索引：
优势：根据主键查询条目比较少时，不用回行；
劣势：如果碰到不规则的数据插入时，造成频繁的页分裂。
索引覆盖：
Extra：using index
出现索引覆盖，不需要回行
显示性能时间
mysql > set profiling=1;
mysql > show profiles;
七、理想的索引
1、查询频繁
2、区分度高
3、长度小
4、尽量能覆盖常用的查询字段
伪哈希索引
php->crc32一种哈希算法，可以把字符串算为32位整数(加索引)
倒序索引
结合实际业务+索引 从左到右原则
index(字段1,字段2,字段3...)
using filesort - 这个需要优化
ORDER BY 和 GROUP BY是比较耗资源所以需要用索引优化
联合索引 多个字段联合顺序不一样
长期的数据更改后,索引和数据会产生碎片化 - 影响操作速度,可通过nop操作,来修复表.如表引擎为innodb:
alter table xxxx engine innodb
或
optimize table xxxx
八.SQL语句化
时间->等待和执行
执行->查找和取出(可提高)
查的快->联合索引的顺序,区分度,长度
取的快->索引覆盖
传得少,少行少列
插入多行可以折几次
查询分几条简单语句查(没有必要只用一条语句),尽量少用联合查询
不查,少查,高效查(尽量在索引上查)
explain sql...
select type:
*type: 索引利用率
*possible_keys：可能用到的键
*key:肯定用到的键
*key_len:用到键的长度
*ref：引用关系（比如说两表联查时）
rows：估计扫描行数
count-全表计数快,如果按条件可以全表减条件表
limit offset,N - 随着offset增大,效率越低速度慢
控制offset如70页内
或搜索精确点
或用条件替代limit如where id > 数 limit 3
数据不要真删,假删
或用索引覆盖再关联
select * from xxxx inner join (select id from xxx limit 10000000,3) as tmp on xxxx.id=tmp.id