Mysql笔记

一.MySQLD服务器程序构成

mysqld是一个守护进程但是本身不能自主启动：

[root@db01 ~]# mysql -uroot -poldboy123
[root@db01 ~]# select user,host,password from mysql.user;

连接层
1、提供连接协议（socket、tcp/ip）
2、验证用户的合法性（用户名、密码、白名单）
3、提供一个专用连接线程（接收SQL、返回结果），将SQL语句交给SQL层继续处理

SQL层
1、接收到SQL语句，语法判断。
2、判断语义（判断语句类型：DML、DDL、DCL、DQL）
3、解析SQL语句，生成多种执行计划
4、优化器，选择他认为成本最低的执行计划。
5、执行器根据优化器的选择，按照优化器建议执行SQL语句，得到去哪儿找SQL语句需要访问的数据
5.1 具体：在哪个数据文件上的哪个数据页中？
5.2 将以上结果充送给下层继续处理
6、接收存储引擎层的数据，结构化成表的形式，通过连接层提供的专用线程，将表数据返回给用户。
7、提供查询缓存
7.1 query_cache, 使用memcache 或者redis 替代
8、日志记录（binlog）

存储引擎层
1、接收上层的执行结果
2、取出磁盘文件和相应数据
3、返回给SQL层，结构化之后生成表格，由专用线程返回给客户端

二.索引问题讨论

1.辅助索引（BTREE） 需要回表查询

alter table t1 add index idx_num(telnum);

根据创建索引时，指定的列的值，进行排序后，存储到叶子节点中

好处：
1.优化了查询，减少CPU ，内存，IO消耗
2.减少了文件排序

2.覆盖索引:(联合索引)

select * from people where gender= ,age= ,money=;

好处:
1.很大程度减少了回表查询的机率

3.聚集索引:（主键索引）
1.也是B树结构，区别在于叶子节点，在创建表时，还没有数据之前，主键已经创建好了，这个时候mysql规定，将来在我们插入数据的时候，按照主键的顺序，在磁盘上一行一行顺序的插入数据，实际上聚集索引是没有叶子节点的，对于聚集索引，叶子节点就是磁盘上的一行一行数据。
2.创建表时，如果有主键列，自动生成聚集索引，如果没有主键列，会去找unique，
3.聚集索引的叶子节点为真实的数据，这些数据是按照主键的规范在磁盘上顺序存储，然后生成支节点

好处：
1.减少回表
2.将随机IO转换成顺序IO

三.explain命令的应用

获取优化器选择后的执行计划
以world这张表为例:

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> explain select * from city where countrycode="CHN"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city   
         type: ref            --->索引类型
possible_keys: CountryCode    --->可能会走的索引
          key: CountryCode    --->真正走的索引
      key_len: 3
          ref: const    
         rows: 363
        Extra: Using index condition   --->额外信息
1 row in set (0.00 sec)

type:

• ALL —>全表扫描

explain select * from city;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | city  | ALL  | NULL          | NULL | NULL    | NULL | 4188 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

线上业务一般不会出现全表扫描
一般会使用备库：数据处理分析

• index —>全索引扫描

explain select countrycode from city;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | city  | index | NULL          | CountryCode | 3       | NULL | 4188 | Using index |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.00 sec)

基本上很少出现

• range —>索引范围扫描

针对索引键做以下查询时
 >  <  in  or   like
explain  select * from city where countrycode like 'CH%';
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | city  | range | CountryCode   | CountryCode | 3       | NULL |  397 | Using index condition |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

• ref —>辅助索引等值查询

mysql> explain select * from city where  countrycode in ('CHN','USA');
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | city  | range | CountryCode   | CountryCode | 3       | NULL |  637 | Using index condition |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
我们觉得使用索引优化查询的最低级别：
<   >  最好指定上，下限
like 语句前导字符尽量唯一性强一些，不能出现%在前面
对于 in 和 or 尽量改写为union all ,union all走的是等值查询  比range的查询效率要高效
+----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------------+
| id | select_type  | table      | type | possible_keys | key         | key_len | ref   | rows | Extra                 |
+----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------------+
|  1 | PRIMARY      | city       | ref  | CountryCode   | CountryCode | 3       | const |  274 | Using index condition |
|  2 | UNION        | city       | ref  | CountryCode   | CountryCode | 3       | const |  363 | Using index condition |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL        | NULL    | NULL  | NULL | Using temporary       |
+----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------------+
3 rows in set (0.00 sec)

• eq_ref —>表连接时on的条件列是主键或者唯一键

a join b
on a.id=b.id
如果达不到，至少要有辅助索引，一般和where条件列键联合索引

• system/const —>where条件列,是主键或唯一键的等值查询

mysql> explain select * from city where id=10;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | city  | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)