1.mysql基础

1.1 sql语言基础--类别

DML(Data Manipulation Language)数据操纵语言。这个模块可以让我们检索、修改、增加、删除数据库中的数据。（重点）

DDL(Data Definition Language)数据定义语言。是的我们能够创建和修改数据库本身。如：DDL提供ALTER语句，他让我们可以修改数据库中表的设计。如create、drop、alter等

DCL(Data Control Language)数据控制语言，用于维护数据库的安全。如grant、revoke等

1.2 索引

索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

1.2.1 索引设计原则

    最合适索引的列是出现在where字句中的列，或连接子句中指定的列，而不是出现在select关键字后的选择列中的列

    对于唯一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。例如，存放年龄的列具有不同值，很容易区分各行，而用来记录性别的列，只含有m和f，则对此列进行索引没有多大用处

    使用短索引，例如，如果有个CHAR（200）列，如果在前10或者20个字符内，多数值是唯一的，那么久不要对整列进行索引，对前10个或20个字符醒醒索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘io较少，较短的值比较起来更快。

    不要过度索引，不要以为索引越多越好，什么东西都用索引是错误的，每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时候，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长

1.2.2索引类型

    普通索引-最基本的索引，它没有任何限制

    唯一索引-索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

1.2.3 索引失效

隐式转换导致索引失效，容易被开发忽略：<>  not in   not exist  != like

对索引列进行运算导致索引失效，对索引列进行运算包括：+ - * / ！ 等，错误列子：select * from test where id-1=9;

不要将空的变量直接与比较运算符号比较： 如果变量可能为空，使用IS NULL 或 NOT NULL进行比较

不要再sql中使用双引号

1.3 测试数据准备

    使用存储过程- 存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合

    使用语言就脚本批量insert

    使用工具datafactory

    使用INSERT INTO SELECT语句，数据呈几何倍数增长

1.4 status与variables

---    show global status; mysql服务器运行各种状态值，status是状态是系统的状态不可更改，是系统现在的运行状态参数

---    show global variables ; 查看的是mysql系统变量，是MySQL系统运行时的参数，如字符集设置、版本信息、默认参数等，除非手动修改，否则运行时一般不会改变

    查看mysql允许的最大连接数     show variables like 'max_connections' 这个参数过大占用的内存也会大

    查看mysql以往响应的最大连接数   show global status like 'Max_used_connections'

1.5 query cache

    缓存机制简单的说就是缓存sql文本及查询结果，如果运行相同的sql，服务器直接从缓存中渠道结果，而不需要再去解析和执行sql。如果表更改了，那么使用这个表的所有缓存查询将不在有效，查询缓存的相关条目被清空。

    查询必须是完全相同的才能够被认为是相同的。

    开启query cache方法：1.修改my.cnf配置   2.set global query_cache_type='ON'

    query cache运行状态分析

        show status like '%qcache%'

        query cache 命中率=Qcache_hits/( Qcache_hits+Qcache_inserts)

        查询缓存利用率=（query_cache_size-qcache_free_memory）/query_cache_size*100%

        查询缓存利用率在25%之下的话说明query_cache_size设置的过大，可适当的减少；查询缓存利用率在80%以上而却Qcache_lowmem_prunes>50的话说明query_cache_size可能有点小，要不就是碎片太多

1.6 开启慢查询

    修改配置文件my.cnf添加内容：log-show-queries=/var/log/slow.log    long_query_time=2重启

    执行show variables like '%slow%'查看慢查询日志是否开启

    慢查询分析

    mysqldmpslow -s c -t 20 /var/log/slow.log 可以看出访问次数最多的20个sql语句

    mysqldumpslow -s r -t 20 /var/log/slow.log 可以看出返回记录集最多的20个sql

    mysqldumpslow -t 10 -s t -g 'left join' /var/log/slow.log 按照时间返回前十调里面包含做链接的语句

    -s 标识按照何种方式排序，c t l r 分别是按照次数、时间、查询时间、返回的记录数来排序，ac at alar 表示相应的倒序

    -t 是top n的意思，即返回签名多少条数据

    -g 后面可以跟正则表达式，大小写不敏感

1.7 explain分析查询语句

    id：所选定执行计划中的查询的序列号，没有具体含义

    select_type：所使用的查询类型，主要是区别普通查询和联合查询子查询之类的复杂查询

    table：所访问的数据库中的表名

    type：对表所使用的访问方式。是较为重要的一个指标，一般来说，得保证查询至少到range级别，最好能到ref

    possible_keys：该查询可以利用的索引。指出mysql能使用哪个索引在该标中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验where字句，看是否引用某些字段，或者检查字段不是适合索引

    type分析：表示mysql在表中找到所需行的方式，即访问类型，常见类型如下，由左到右，由最差到最好

    key分析：显示mysql在查询中实际使用的索引，若没有使用索引，显示为NULL

    rows：表示mysql根据表统计信息及索引选用情况，估算的找到所需的距离所需要读取的行数

    extra（重点）：如果是only index，意味着信息只用索引树种的信息检索出的，这比扫描整个表要快

        如果是where used 就是使用上了where限制

        如果是impossible where 表示用不着where，一般就是没查出来啥。

        如果此信息显示using filesort或者是using temporary的话会很吃力，where和order by的索引以经常无法兼顾，如果按照where来确定索引那么在order by时，就必要会引起using filesort，这就要看是先过滤在排序划算，还是先排序再过滤划算。

explain局限性

    explain不会告诉你关于触发器、存储过程的信息或者用户自定义函数对查询的影响情况

    explanin不考虑各种cache

    explain不能显示mysql在执行查询时所做的优化工作，只能解释select操作