【MySQL】5. 性能调优

【MySQL】5. 性能调优

1. 分表:

1. 全值匹配 : 联合索引的情况下,使用了所有的索引值 查询。
2. 匹配左边:按顺序 索引, 缺少第一个 索引字段的情况下,索引将完全失效。
2. 列前缀匹配:  模糊查询时,使用前缀匹配,因为 只有前缀是 排序的。
3. 精准匹配 和 范围匹配:  并存
4. 索引用于排序: 避免 文件排序(读入内存之后排序)
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

5.使用联合索引进行排序注意事项:索引的顺序不能颠倒。(在 where 中有 索引优化器,所以可以颠倒,此处不支持)
SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;
6. WHERE子句中出现非排序使用到的索引列
SELECT * FROM person_info WHERE country = 'China' ORDER BY name LIMIT 10;
7.排序列包含非同一个索引的列
SELECT * FROM person_info ORDER BY name, country LIMIT 10;
8.排序列使用了复杂的表达式
SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;
9.用于分组
SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number
10. 查询 带非聚集索引 的表,需要做 回表操作:
在索引中查询时,查询到数据之后,会进行 顺序I/O处理, 在回表操作中,每个数据的 记录不在磁盘的连续位置,所以 要做 随机I/O。
如果 二级索引查询到的数据太多,需要到 聚集索引中做 随机I/O。效率很低,不如不用 二级 + 聚集索引。
而,MySQL设计中 存在一个 索引优化器,会对查询的工作量做统计计算操作,来判断 是否使用 二级 + 回表操作。
SELECT * FROM person_info ORDER BY name, birthday, phone_number;
select  *  通常考虑 聚集索引。 加 limit  *;限制,考虑 二级索引 + 回表操作。
11. 覆盖索引
SELECT name, birthday, phone_number FROM person_info WHERE name > 'Asa' AND name < 'Barlow'
为了彻底告别回表操作带来的性能损耗,我们建议:最好在查询列表里只包含索引列

12. 如何挑选索引:
1. 只为用于搜索、排序或分组的列创建索引 :出现在 where   、  group by   、 orader by 的列需要,出现在select 的不用。
2. 考虑列的基数: 可选的值,越多,越值得。
3. 索引列的类型尽量小 查询会快、占用空间 会少(主键的情况下,二级索引中也能节省空间)
4. 索引字符串值的前缀:
CREATE TABLE person_info(
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);    

2. 参数配置:

key_buffer_size            # 用于索引的缓冲区大小
query_cache_size           # 查询缓存,不开启请设为0
tmp_table_size             # 临时表缓存大小
innodb_buffer_pool_size    # Innodb 缓冲区大小
innodb_log_buffer_size     # Innodn 日志缓冲区大小
sort_buffer_size           # 连接数,每个线程排序的缓冲大小
read_buffer_size           # 连接数,读入缓冲区大小
read_rnd_buffer_size       # 连接数,随机读取缓冲区大小
join_buffer_size           # 连接数,关联表缓存大小
thread_stack               # 连接数,每个线程的堆栈大小
binlog_cache_size          # 连接数,(KB)二进制日志缓存大小(4096的倍数)
thread_cache_size          # 线程池大小
table_open_cache           # 表缓存(最大不要超过2048)
max_connections            # 最大连接数

continue…

参考文章:
https://www.cnblogs.com/miketwais/articles/mysql_partition.html
0 0 vote
Article Rating
Subscribe
提醒
guest
0 评论
Inline Feedbacks
View all comments