mysql开发必知必会
mysql的数据库的数据库，即存储mysql数据库的底层目录，是在/var/lib/mysql目录下（Linux，win在目录下的data中）。
我们新创建的数据库db1就是在/var/lib/mysql目录下创建了一个文件夹db1。
mysql相关的目录
我们使用/usr/bin/mysqladmin创建过root用户的密码等操作。
mysql默认的配置文件读取顺序为：
[root@localhost bin]# which mysqld /usr/sbin/mysqld [root@localhost bin]# ^C [root@localhost bin]# /usr/sbin/mysqld --verbose --help |grep -A 1 'Default options' 2018-03-07 17:30:00 0 [Note] /usr/sbin/mysqld (mysqld 5.6.39) starting as process 14259 ... 2018-03-07 17:30:00 14259 [Note] Plugin 'FEDERATED' is disabled. Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf #mysql的配置信息的读取目录顺序 2018-03-07 17:30:00 14259 [Note] Binlog end 2018-03-07 17:30:00 14259 [Note] Shutting down plugin 'CSV' 2018-03-07 17:30:00 14259 [Note] Shutting down plugin 'MyISAM'
查看mysql的字符集
mysql> show variables like 'character%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) mysql> show variables like '%char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)
此时数据库的字符集为拉丁，我们需要改为utf8。
修改mysql的默认配置文件/etc/my.cnf。
[mysql] # 设置mysql客户端默认字符集 default-character-set=utf8 socket=/var/lib/mysql/mysql.sock [mysqld] skip-name-resolve #设置3306端口 port = 3306 socket=/var/lib/mysql/mysql.sock # 设置mysql的安装目录 basedir=/usr/local/mysql # 设置mysql数据库的数据的存放目录 datadir=/usr/local/mysql/data # 允许最大连接数 max_connections=200 # 服务端使用的字符集默认为8比特编码的latin1字符集 character-set-server=utf8 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB lower_case_table_name=1 max_allowed_packet=16M
关闭mysql并重新开启：
[root@localhost mysql-community-server-5.6.39]# service mysqld stop Stopping mysqld (via systemctl): [ 确定 ] [root@localhost mysql-community-server-5.6.39]# service mysqld start Starting mysqld (via systemctl): [ 确定 ] [root@localhost mysql-community-server-5.6.39]# mysql -uroot -p Enter password:
新建数据库db2显示db1与db2的数据库的字符编码：
mysql> create database db2; Query OK, 1 row affected (0.04 sec) mysql> use db2; Database changed mysql> show variables like '%char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) mysql> use db1; Database changed mysql> show variables like '%char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)
之前创建的数据库的字符编码仍为Latin，而修改过后的新建数据库已经使用utf8了，说明我们的配置生效了。
查看mysql的安装目录
[root@localhost mysql-community-server-5.6.39]# vim /etc/my.cnf [root@localhost mysql-community-server-5.6.39]# ps -ef|grep mysql root 14599 1 0 18:44 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql mysql 14941 14599 0 18:44 ? 00:00:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306 root 15008 11441 0 18:54 pts/0 00:00:00 grep --color=auto mysql
mysql主要的配置文件
二进制日志log-bin 用于主从复制；
错误日志log-error 默认关闭，记录严重警告和错误信息。
查询日志log 默认关闭，用来查询sql语句，开启会降低mysql整体性能
数据文件：前面提到的数据库的数据库文件，frm文件用于存放表结构，myd文件存放表数据，myi文件用于存放表索引。
配置文件：Linux为/etc/my.cnf，win为my.ini。
mysql架构介绍
第一层为连接层，connectors。
接下来就进入mysql层：
第二层为一部分是与外层的连接池（并发部分），备份安全集群等部份，然后到对输入sql语句进行解析的部分（sqlinterface接口对你要进行是curd的某项操作进行分开解析），解析到optimizer部分为mysql自己的逻辑操作部分（人进行数据查询首先关注select部分，但是机器关心的是从from哪张表开始，这个部分是mysql的优化器），最后一部分是缓存与缓冲，解决数据库存取时的io操作问题，所以这一层主要是业务逻辑处理层。
第三层为可拔插的存储引擎部分。有很多种，我们经常使用到的是innodb有时也用myisam。
第四层就是底层的文件存储层，底层的文件系统等。
与其他数据库相比，mysql的架构可以在多种不同场景应用并发挥良好作用主要体现在存储引擎的架构上，可插拔的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离，我们可以根据业务的实际需求选择合适的存储引擎。
查看存储引擎：
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | CSV | YES | CSV storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
主流的两种存储引擎的对比：
关于mysql性能下降的分析
如果mysql的性能下降，sql慢，执行时间长，等待时间长，那么问题可能出现在：
1.查询语句写得太烂了
2.索引失效了
3.关联查询的join写的太多了（可能是表的设计的问题）
4.服务器调优及各个参数的设置问题（缓冲及线程数等）
你写的sql：
mysql都出来的查询语句
机读的顺序
mysql索引
定义：索引是帮助mysql高效获取数据的数据结构。我们可以根据索引的特点理解为，索引就是排好序的快速查找的数据结构。
以后别人问你mysql索引是什么的时候，别再说什么字典什么查的快什么的了，你得专业的告诉他，索引就是用B+树实现的快速查找的一种数据类型。
索引影响的部分是where后面的查找与orderby后面的排序。
除了数据，数据库系统还维护了一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
为了加快col2的查找，可以维护一个二叉树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
我们一般说的索引都是b树结构组织索引，其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引都是使用b树索引。除了b树索引意外还有hash索引。
索引的优势：
查找时提高数据检索效率，降低数据库io成本。排序时，索引对数据进行排序，降低数据排序成本，降低了CPU的消耗。
索引的劣势：
索引本身是一张表，保存了主键与索引字段，并指向实体表的记录，所以索引本身也占空间。
索引虽然大大加快了查询速度，却降低了更新速度，在更新表时，不仅要存更新的数据，还要存索引文件每次更新添加的索引列字段，都会调整因为更=新带来的简直变化后的索引信息。
索引只是提高效率的一个因素，如果你的mysql有大量数据的表，那么就要花时间研究建立最优秀的索引或优化查询语句。
mysql索引分类
单值索引：一个索引只包含单个列，一个表可以有多个单列索引。
唯一索引：索引列的值必须唯一，但允许空值。
复合索引：一个索引包含多个列。
b树查找
这里磁盘块Ⅰ存储了指引搜索方向的数据项，17与35不一定存在于数据表中，只是一个趋势，叶子节点存的数据必须是真实存在的数据。根节点的数据表示，查找的数据范围是小于17还是位于17和35之间还是大于35的数据。然后从根出发一直找到索引的数据，只需要三步即可。
那些情况需要创建索引
1.主键自动建立唯一索引
2.频繁作为查找条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引
4.频繁更新的字段不适合创建索引，不只是更新记录还会更新索引，加重io负担
5.where条件里用不到的字段不创建索引
6.单键/组合索引的选择问题，在高并发下倾向创建组合索引
7.查询中排序的字段，排序字段通过索引访问将大大提高排序速度
8.查询中统计或者分组字段
哪些情况不要创建索引
1.表记录太少
2.经常增删改的表
3.数据重复且分布平均的表的字段。比如性别列。
Explain
除了mysql的optimizer自动的优化导致的sql性能下降和cache与buffer的硬件部分性能瓶颈，我们分析一条sql语句是否高效就必须用到Explain进行查询。
explain关键字可以模拟优化sql查询语句，从而知道mysql是如何处理你的sql语句的，可以用来分析你的查询语句或是表结构的性能瓶颈。
能查看什么：
表的读取顺序，数据读取操作的操作类型，那些索引可以使用，那些索引被实际使用，表之间的引用，每张表有多少行被优化器查询。
执行方法
explain+sql
explain中的字段
id字段
此字段用来表示mysql查询时执行select子句或操作表的顺序，即表的读取顺序。
查询结果可能有三种情况
1.id相同时。
即此时mysql加载的顺序为t1>t3>t2。
2.id不同
即此时mysql加载的顺序为t3>t1>t2。
3.混合存在
即此时mysql加载的顺序为t3><derived2>>t2。
select_type字段
即数据读取操作的操作类型
1.simple
简单的select查询，查询不包括子查询与union
2.primary
如果查询包含了任何复杂的字部分，最外层查询被标记为primary
3.subquery
在select或者where列表中包含了子查询
4.derived
在from列表包含的子查询被标记为derived衍生，mysql会递归执行这些子查询，把结果放到临时表中
5.union
若第二个select出现在union后则被标记为union，若union包含在from子句的子查询中，外层select将被标记为derived。
6.union result
从union表获取结果的select
table字段，就是表名字段，表时这一行数据是关于那张表的。
type字段
直接的显示了表是否被优化是否最佳状态的直接体现。
一般常见的类型为system>const>eq_ref>ref>range>index>all
当你的数据达到百万级并且sql语句执行为all，那么你的查询语句或者说表需要进行优化了。当你的sql执行为ref或者range就已经很优了。
type的不同状态：
1.system
表只有一行记录（等于系统表），是const类型的特例，平时基本遇不到可以忽略不计
2.const常量
表通过索引一次就找到了，const用于比较primary key或者unique索引，因为只匹配一行数据，所以很快
这里的id为主键索引，在t1表唯一只匹配一行数据，查找相当于查询一个常量。
一张表只有一行数据，相当于京东就你一个用户，快是快，但是实际基本不会用到。
3.eq_ref
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
类比员工表与部门表中，部门表id最为外键，员工表的外键关联到部门表id，将部门表id建成索引，ceo作为公司员工，在部门表索引时只能查到一个唯一的数据与之匹配，这种情况就是eq_ref，反之其他的部门拥有多名员工，查询就是ref。
4.ref字段
非唯一索引扫描，匹配某个单独值的所有行，可能找到符合条件的多个值，所以它是查找和扫描的混合体。
类比上述的表查找所有的程序员就是ref类型。
5.range
只检索给定范围行，使用一个索引来选择行，key列显示你使用了哪个索引，一般是where语句出现between，<，>，in等的查询，这种范围扫描优于全标搜索，因为他有起始位置。
6.index
全索引扫描，优于all因为只扫描了索引，索引往往小于数据的大小。
7.all
全表查询，匹配到所有行
possible_keys与key字段
possible_key是smysql判断你可能使用的索引，显示可能应用在这张表的索引，一个或多个，查询涉及到的字段上若存在索引，该索引将被列出，但不一定被查询实际使用
key字段
实际中使用的索引，如果为null则没有使用索引
这种情况为查询中使用了覆盖索引，该索引与查询的select重叠。
key_length字段
条件越精确，key_length越长，此字段为索引字段最大可能长度，并非实际使用长度，根据定义计算得出，不是通过表内检索得来的值。表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。
ref字段
显示索引的那一列被使用了，如果可能的话是一个常数，那些列或常量被用于查找索引列上的值。
即ref体现了实际被索引的列。
rows字段
根据表中信息，估算找到所需记录大致需要读取的行数。
extra
包含的额外信息，也是重要字段
1.using filesort
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
这里我们对表中的三列进行了联合索引，前者在orderby排序时使用了外部排序，这大大降低了表的排序性能，因为在使用索引排序时最好是按照它的索引顺序进行排序。
2.using temporary
使用了临时表保存中间结果，mysql在对查询结果排序时使用了临时表，常见于排序orderby和分组查询groupby。
在这张表中，创建col1与col2联合索引，groupby跳过col1直接groupby col2，导致断层，需要创建临时的表，mysql对临时表进行一次自己的排序。
3.using index
所以这是个好字段，使用覆盖索引。
还有一些其他字段不很重要的字段，就不一一解释了，有兴趣自行了解。
范围后的索引会失效
左右连接查表的时候给另一边加索引。
多表连接查询时的优化：
1.减少join查询的循环总次数，即永远以小结果集驱动大结果集。
2.优先优化nestedloop的内层循环。
3.保证join语句中被驱动表join字段已经被索引
4.无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要吝惜joinbuffer的设置。
如何避免索引失效
1.全值匹配我最爱
此为完全覆盖和部分覆盖，并且满足左前缀法则，索引正确使用。
2.最佳左前缀法则
带头大哥不能死，中间兄弟不能断。
3.不在索引列上做任何操作（计算函数类型转换）、会导致索引失效而转向全表扫描。
索引列上少计算。
4.存储引擎不能使用索引范围条件右边的列
后一条的key_length长度比之前完全覆盖短，age后索引其实已经失效了，即范围之后全失效。
5.尽量使用覆盖索引
即覆盖列与索引列一致，减少select *的使用
6.使用不等于时无法使用索引会导致全表扫描
7.isnull与isnotnull也无法使用索引
8.like以通配符开头，mysql索引失效变成全表扫描。
如果需求一定要两边都是百分号查找，那么需用使用覆盖索引来解决索引失效问题。
9.字符串不加单引号索引失效
10.少用or，用它来连接时会索引失效
这十条mysql索引优化的口诀是：
带头大哥不能死，中间兄弟不能断。
索引列上无计算，like百分加右边。
字符串里有引号。
mysql的自动调优，因为上述的都是常量级别，所以会被调成覆盖索引类似。
此表1，2是覆盖索引，3是范围索引，所以用到三个索引，因为3type降为range等级，范围之后全失效，所以4未索引。
同理，此处使用了四个索引。
此处，1，2为查找索引，3用于排序索引，所以，严格地认为此处使用了三个索引。
同理，与上述一致，完全一样。
c4空中阁楼，中间兄弟断了，mysql自己优化排序了，糟糕。
1查找索引，23为排序索引。
与上述不同，此处的2，3没有按照索引顺序排列，违背左前缀优先的理念，故出现filesort。
使用两个查找索引和两个排序索引。
上面c2被查询了，所以排序时相当于一个常量，所以并为filesort。
分组之前必排序，所以他与orderby基本一致。
熟记口诀：
数据库分析
1.先观察一下sql生产时的慢sql情况
2.开启慢日志查询，设置阙值，查询过慢的sql挑出来
3.explain+sql分析
4.show profile
5.对sql数据库服务器进行参数调优
总结就是：开启捕获慢查询语句，explain分析定位，show profile查询sql在MySQL服务器里执行细节与生命周期，sql数据库服务器调优。
in与exist的转换。
show profile
查看是否开启了，默认是关闭的
mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | +---------------+-------+ 1 row in set (0.01 sec)
所以我们需要打开
mysql> set profiling=on; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | ON | +---------------+-------+ 1 row in set (0.00 sec)
show profile cpu，block io for query （id）；
看出一条sql的完整生命周期。
比较严重的问题：
converting head to myisam 查询结果太大，内存不够用存到磁盘上了。
creating tmp table 创建临时表（拷贝数据到临时表，用完删除）
copying to tmp table on disk 把内存中临时表复制到磁盘，
locked 锁住了
