表的优化

1. 定长和变长分离
2. 核心与常用的字段，建议定长。
3. 不常用的，如用户介绍，需要点击头像才能获取，可以变长，放其他表。
4. 适当加入冗余字段，比如主题内的帖子数。

列选择原则

1. 列选择优先级：整型、date、time、enum、char、varchar、blob、text

解释：
同样是一个字节 tinyint 和 char(1) ,选择 tinyint ，因为它不需要判断字符集，校对集（排序规则）。
time 定长，运算快，省空间。需要考虑时区。
enum：起到约束作用。内部用整型存储。
text 和 blob：无法使用内存临时表，所以排序等操作只能再磁盘上运行。

date 和 time 选择：大师明确意见，请用 unsigned int not null

举例：utf 8 情况下
tinyint()           1个字节
enum（‘男’， ‘女’）内部由数值存储，有个转化过程
char（1）3个字节

2. 够用就行，不要慷慨。

如：
age：tinyint unsigned （0 - 255）
varchar(20)  设置为 varchar(300) 关联表时候，占更多内存。

3. 避免用 null ，查询不便。

查询使用 is null ，is not null

b-tree 索引和 hash 索引

42 亿数据，普通查找，需要 21 亿次，b-tree 只要 32 次肯定找到。
myisam，innodb 默认 b-tree 索引。
memory 引擎用的 hash 索引。

hash 索引坏处：

1. 不利于范围查询，
2. 随机放置会有残余空间
3. 无法利用前缀索引，btree 搜索 helloworld 中的 hello 也是能利用到索引的。
4. 不利于排序。
5. Hash 索引遇到大量 Hash 值相等的情况后性能并不一定就会比 B-Tree 索引高
6. 需要回表

b-tree 常见误区

不是常用的列都要加上索引
     where user_id=1 and time>14564564564
     user_id 和 time 都加上索引
     这两个索引只会用到其中一个。
     解决方案：联合索引，无论使用其中哪个，索引都会起到作用。但是要满足左前缀。

image.png
下图，c1,c2,c3,c4 均为 tinyint
image.png

explain 只显示索引在查询时用了多少索引，group 和 order 不会显示。但是 group order 也会用到索引。
group 在高版本的 sql 里面，你 group by c2,c3 那么只能 select c2 或者 c3 或者 arvg（）等，不能有 c4 或其他的。

image.png image.png

聚簇索引和非聚簇索引

myisam 分为：（假设是 user 表）
users.frm   结构
users.MYD  数据
users.MYI  索引

1. mysiam 是非聚簇索引，MYD 和 MYI 分开。
2. innodb 是聚簇索引，数据和索引挤在一起，无需回表。

3. 无规律的数据插入 innnodb 会造成频繁页分裂，导致速度慢，但是在固态和内存中是随机写入。速度差异就不明显了。

   
   image.png

索引覆盖

要查询的字段和搜索的字段都在索引上

image.png
image.png
image.png
image.png

理想索引

1. 查询频繁
2. 区分度高 （性别字段属于区分度低，很低）
3. 长度小
4. 尽量能够覆盖到常用查询字段。

举例：中国常用短语

区分度
select ( (select count(distinct left(word,2)) from dictory) / (select count(*) from dictory) )

image.png

如何解决左前缀区分度低的字段查询

例如：http://baidu.com, http://qq.com

1. 倒序插入数据库
2. 伪 hash 索引，增加一个字段，存储之为 crc32(url)

多列索引原则

考虑因素：1. 列查询频度 2. 列的区分度 3. 列的顺序。 根本：按业务情景区分。

image.png

索引和排序

image.png

如果是 索引回行，索引回行。。。在回行时候将费时间，因为磁盘操作。
order by 要用索引，否则耗性能。

不能有重复索引，可多建冗余索引

index(user_id, phone_id)和index(user_id) // 重复索引
index(user_id, phone_id)和index(phone_id, user_id) // 冗余索引

索引碎片与修复

alter table t engine myisam;

optimize  table exam;

优化查询

查的快 联合索引顺序，区分度，长度
取得快 索引覆盖
传的少 只取出需要的数据

image.png

1.sql语句执行的时间：等待时间，执行时间
2.优化执行时间：查找时间 取出时间
3.优化大思路：不查->少查->高效的查

explain

id:查询语句的id
select_type: 简单查询：simple  复合查询：subquery(非from子查询) ,derived(from型子查询), (union, union result：结果的那次)
table:查询的表，derived(from子查询的表)，null
type:搜索的数据范围：
    ALL(全表扫描)<
    index(全索引扫描)<
    range(范围索引查找)<
    ref(通过索引列，可以直接饮用某些数据行)<eq_ref(通过索引列，引用某一行数据)<
    const system null 精准查询
pssible_keys:可能使用的key
key:使用的key
key_len:使用的key长度
ref:连接查询时，表之间的字段引用关系
rows:可能扫描的行数
Extra:描述
    using filesort:文件排序<
    using temporary:使用了临时表<
    using where：使用索引还不能完全定位，还需要where判断一下<
    index:使用到了索引覆盖

image.png

六. 其他注意

1.in型子查询：in其实是每一个数据的一次exists查询

2.强制使用索引：using index XXX

3.count()
4.union:最好使用union all

5.翻页优化：
尽量将limit 10000,10这样的优化成 where id>10000 limit 10
业务上少翻页
先索引覆盖查找主键，再连接需要的字段

image.png

MySQL的缓存是基于整个SQL语句的，只要SQL语句中任何一点发生变化，那么整个语句就不会被缓存。复杂的语句，重复出现的概率可能很低。所以，适当的拆分，有可能提高整体的缓存命中率。