MySQL的系统架构如图所示:

image.png
在解分析查询缓存的性能优化后,我们还要考虑数据在磁盘上的组织和使用恰当的index和SQL语句来提高性能的问题。MySQL的索引数据结构采用的是B+TREE树:

image.png
只有sql语句的写法和index序列的相匹配才能充分发挥index的作用。
查看MySQL中index情况的工具
- explain
MariaDB [hellodb]> explain select * from students where age=20;
+------+-------------+----------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | students | ref | idx_age | idx_age | 1 | const | 2 | |
+------+-------------+----------+------+---------------+---------+---------+-------+------+-------+
- 变量userstate
SET GLOBAL userstat=1; # or add it to my.cnf
MariaDB [(none)]> SHOW INDEX_STATISTICS;
+--------------+------------+------------+-----------+
| Table_schema | Table_name | Index_name | Rows_read |
+--------------+------------+------------+-----------+
| hellodb | students | PRIMARY | 2 |
+--------------+------------+------------+-----------+
索引设计原则
- 独立的使用列:应避免使用index参与运算,表达式和函数的操作,在where语句中将索引列单独放在比较符号一侧
# 这样的语句是无法使用索引的
MariaDB [hellodb]> explain select * from students where stuid + 10 > 20;
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
- 左前缀索引:构建指定index字段左侧的字符数,字符数可以通过不重复的索引值和数据表的记录总数的比值,大概在90%即可
create index idx_name on students(name(10));
MariaDB [hellodb]> explain select * from students where name like 's%';
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | students | range | idx_name | idx_name | 32 | NULL | 4 | Using where |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
- 多列索引:AND操作时更适合使用多列索引,而不是为每个列创建单独索引
create index idx_name_age on students(name,age);
MariaDB [hellodb]> explain select * from students where name like 's%' and age=20;
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
| 1 | SIMPLE | students | range | idx_name_age | idx_name_age | 153 | NULL | 4 | Using index condition |
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
- 选择合适的index顺序:无排序和分组时,将选择性最高的放在左侧
- 列中有NULL值,不要设置index
- 尽量使用短索引
- 对于经常在where子句使用的列,最好设置索引
- 对于多个列where或order by子句,应该建立复合索引
- 对于like子句,以%或_开头的不会使用index
MariaDB [hellodb]> explain select * from students where name like '%s%';
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
MariaDB [hellodb]> explain select * from students where name like '%s';
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
- 不要使用not in 和<>操作
SQL语句建议
- 尽量不要使用*,要写全字段名
- join效率远大于子查询
- 多表连接时,小表join大表
- 有大量记录的表分页时使用limit
- 对于经常使用的查询,可以开启缓存
- 使用explain和profile分析查询语句
- 查看慢查询日志,找出执行时间长的sql语句进行优化
分析查询语句性能
慢查询日式
- slow_query_log=ON|OFF #开启或关闭慢查询
- long_query_time=N #慢查询的阀值,单位秒,
- slow_query_log_file=HOSTNAME-slow.log #慢查询日志文件
- log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk # 慢查询日志记录的类别
- log_queries_not_using_indexes=ON #记录未使用index的查询,默认OFF,即不记录
- log_slow_rate_limit = 1 #多少次查询才记录,mariadb特有
- log_slow_verbosity= Query_plan,explain #记录内容格式
利用profile进行分析
MariaDB [hellodb]> show variables like "%profiling";
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| have_profiling | YES |
| profiling | OFF |
+----------------+-------+
2 rows in set (0.00 sec)
MariaDB [hellodb]> set profiling=ON;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> select sleep(1) from teachers;
+----------+
| sleep(1) |
+----------+
| 0 |
| 0 |
| 0 |
| 0 |
+----------+
4 rows in set (4.01 sec)
MariaDB [hellodb]> show profiles;
+----------+------------+-------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------+
| 1 | 4.00351854 | select sleep(1) from teachers |
+----------+------------+-------------------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000086 |
| checking permissions | 0.000009 |
| Opening tables | 0.000020 |
| After opening tables | 0.000005 |
| System lock | 0.000004 |
| Table lock | 0.000002 |
| After table lock | 0.000005 |
| init | 0.000013 |
| optimizing | 0.000008 |
| statistics | 0.000029 |
| preparing | 0.000009 |
| executing | 0.000003 |
| Sending data | 0.000030 |
| User sleep | 1.000813 |
| User sleep | 1.000540 |
| User sleep | 1.000857 |
| User sleep | 1.001032 |
| end | 0.000018 |
| query end | 0.000010 |
| closing tables | 0.000006 |
| freeing items | 0.000004 |
| updating status | 0.000015 |
| cleaning up | 0.000002 |
+----------------------+----------+