数据库分类

关系型数据库库：Relational Database Management System (RDBMS)

• oracle
• mysql：web使用最广泛的关系型数据库
• sql server
• sqlite:轻量级数据库

非关系型数据库

• redis
• mysql
• mongodb

数据库设计范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
　　目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
　　范式越高，冗余最低，一般到三范式，再往上，表越多，可能导致查询效率下降。所以有时为了提高运行效率，可以让数据冗余(反三范式，一般某个数据经常被访问时，比如数据表里存放了语文数学英语成绩，但是如果在某个时间经常要得到它的总分，每次都要进行计算会降低性能，可以加上总分这个冗余字段)。
　　后面的范式是在满足前面范式的基础上，比如满足第二范式的一定满足第一范式。
　　　　☆☆☆第一范式（1NF）：确保每一列的原子性
　　　　☆☆☆第二范式:非主键字段必须依赖于主键字段
　　　　☆☆☆第三范式：在1NF基础上，除了主键以外的其它列都不传递依赖于主键列

mysql的安装(ubuntu)

• 安装：sudo apt-get install mysql-server
• 启动：sudo service mysql start
• 停止：sudo service mysql stop
• 重启：sudo service mysql restart
• 设置密码：mysqladmin -u root password mysql（window）
• 启动：net start mysql（window）

配置

数据库的创建与使用

• 连接：mysql -uroot -p
• 创建：在登录的状态下执行 create database 数据库名 charset=utf8;
  （☆☆☆不指定 charset 那么默认是拉丁字符集，会有下面的报错信息）
  在进行数据库迁移、创建时报错，信息（ERROR 1366 (HY000): Incorrect string value: '\xE5\xB0\x84\xE9\x9B\x95...' for column 'name' at row 1
  ）
• 显示：show databases;
• 使用：use 数据库名;
• 删除：drop database 数据库名;
• 备份：mysqldump -uroot -p 数据库名 > python.sql
• 恢复：mysql -uroot -p 新数据库名 < python.sql (新数据库名已经创建好了)
• 退出：exit 、ctrl+d（清屏ctrl+l、退出执行语句ctrl+c+enter、挂起ctrl + z、从挂起返回fg）
  　　1、Ctrl+C比较暴力，就是发送Terminal到当前的程序，比如你正在运行一个查找功能，文件正在查找中，Ctrl+C就会强制结束当前的这个进程。
  　　2、Ctrl+Z是把当前的程序挂起，暂停执行这个程序，比如你正在mysql终端中，需要出来搞点其他的文件操作，又不想退出mysql终端（因为下次还得输入用户名密码进入，挺麻烦），于是可以ctrl+z将mysql挂起，然后进行其他操作，然后输入fg回车后就可以回来，当然可以挂起好多进程到后台，然后fg 加编号就能把挂起的进程返回到前台。当然，配合bg和fg命令进行前后台切换会非常方便。
  　　3、Ctrl+D 是发送一个exit信号，没有那么强烈，类似ctrl+C的操作，比如你从管理员root退回到你的普通用户就可以这么用。

表的创建和使用

• 创建： create table table_name(字段 类型 约束，字段2 类型 约束....);
  　　例 create table students(id tinyint unsigned not null primary key auto_increment, name varchar(20) default "", height decimal(5,2), ..... );
  mysql中主键用auto_increment关键字避免冲突
• 查看表结构：desc 表名；
• 显示：show tables;
• 删除：drop table 表名;
• 表中字段的数据类型
  　　整型：int, ,tinyint, bit（1byte = 8 bit）;bit只有0和1
  　　定长字符串: char；可变长度字符串: varchar
  　　浮点数：decimal(5,2) 共五位数，2位小数
  　　日期：date, time, datetime
  　　大文件存储：text 字符串类型
  　　枚举类型(enum)
  　　　　用法：在创建表时，gender enum('男','女','保密','人妖')
• 表中字段的常用约束条件
  　　主键： primary key 作用：可以通过唯一字段确定一行记录
  　　非空 ：not null 作用：不予许字段为空
  　　唯一 ：unique 作用：字段的值不允许重复
  　　默认： default 作用：默认参数，用户不指定则使用默认值
  　　有符号和无符号： signed unsigned
  　　自增长：auto_increment(一般用于id自动加1)
  　　外键：foreign key
• 表字段的增删改查adcm(alter table 表名 ...)
  　　添加：alter table 表名 add 字段 类型(长度) 约束 条件；例如（alter table student add id int primary key not null;）
  　　修改（重命名）：alter table 表名 change 旧字段 新字段 类型(长度) 约束 条件；例如（alter table student add id id1 int primary key not null;）
  　　修改（不重命名）：alter table 表名 modify 字段 类型(长度) 约束 条件；例如（alter table student add id int primary key not null;）
  　　删除：alter table 表名 drop 字段 ；例如（alter table student drop id;）
• 表中记录的增删改查（crud）
  　　curd的解释: 代表创建（Create）、更新（Update）、读取（Retrieve）和删除（Delete）
  　　　查询(全列): select * from table_name;
  　　　　　指定列：select 字段1，字段2 from table_name;
  　　　　　表(字段)重命名：select 字段1 as 字段2 from table_name as ta_na;
  　　　　　消除重复行：select distinct 列1,... from 表名;
  　　　修改：uptdate 表名 set 列1=值1,列2=值2,... where 条件；
  　　　增加：insert into 表名 values(),(),()...; 指定列增加 insert into 表名 (字段1, 字段2,...) values(),(),()...;
  　　　　　当用子查询的方式获取value时，写法更改为insert into 表名 （字段） 子查询表达式；（不要带上values关键字）
  　　　　　实例：insert into bookinfo values(0,'新增２','2011-1-1',20,22,1)
  指定列增加时按照自己指定的字段(顺序可以和表中不一样)按照顺序插入值
  　　　逻辑删除：isDelete ；当值为1时代表要删除，update 表名 set isDelete=1 where 条件；
  　　删除：delete from 表名 where 条件；
  概要
• 表中记录的查询命令
  　　条件查询：select * from 表名 where 字段判断条件;
  　　比较运算符: = , >, <, >=, <=, != <>不等于的两种形式 ；
  　　逻辑运算符：and, not, or；
  　　模糊查询：like %表示任意多个字符；_表示一个任意字符；
  　　select * from students where name like '黄_';
  　　范围查询：in()非连续范围；between and 连续范围；
  　　空判断：is null ; is not null
  　　优先级：( ) > not > 比较运算符 > 逻辑运算符 ;
  　　排序：select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]； asc 升序 desc 降序
  　　应用；☆☆☆☆select gender,group_concat(name order by age) from students group by gender; 分组后各组按照年龄排序后显示对应的姓名
  结果以行的方式显示
  select readcount,group_concat(name) from bookinfo group by readcount order by null\G;
explain select readcount,group_concat(name) from bookinfo group by readcount order by null\G;
• 聚合函数：select 聚合函数(*或者字段 ) from表名；
  　总数：count(*)
  　最大值：max( )
  　最小值：min( )
  　求和：sum( )
  　平均值：avg( )
  select gender,avg(age),group_concat(name) from students group by gender with rollup;
  　round(小数, 保留的位数)
• 分组：select 字段 from 表名 group by 字段；将查询结果按照1个或多个字段进行分组，字段值相同的为一组
  　　1 输出：group_concat(字段名)可以作为一个输出字段来使用；
  　　2 group by + group_concat(字段名) 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合； 　
  　　3 例如：select gender,group_concat(name) from students group by gender;
  　　group by + 集合函数；例如select gender,avg(age) from students group by gender;
  　　group by + having；用法和where相同
  　　group by + with rollup； with rollup的作用是：在最后新增一行，来记录所显示字段所有记录的总和(平均值、最大值、最小值)
  　　　实例：☆☆☆☆ select gender,avg(age) from students group by gender with rollup;（显示所有人年龄的平均值）
• 获取部分行：select * from 表名 limit start,count; start从0开始
• 分页-显示第n页的m条数据；
  select * from students limit (n-1)*m,m；
  第一个数字可以理解为要显示数据的id，从该id开始；
  可以随意指定，按上述规则是均分显示；
  select * from students order by age,id limit 3,3; (order by 和limit处理时有mysql的bug存在，需要在限定id字段，可以防止bug出现)
• 连接查询select * from 左表 join 右表; (返回的笛卡尔积，用join实现)
  　　内连接：select * from 左表 inner join 右表 on 条件；（在mysql中inner join和join是相同的，在其他数据库中join是笛卡尔积，inner join 是内连接）
  　　右连接：select * from 左表 right join 右表 on 条件；（在连接的基础上，添加额外数据-来自右表，左表中没有对应的数据用Null填充）
  　　左连接：select * from 左表 right join 右表 on 条件；（在连接的基础上，添加额外数据-来自左表，右表中没有对应的数据用Null填充）
  又称外连接
• 自关联：select * from 表 join 表 on 条件 where 条件; (相同的表进行笛卡尔积计算)
  　全局搜索-contains语法
  　　1. 查询住址在北京的学生
  SELECT student_id,student_name FROM students WHERE CONTAINS( address, 'beijing' )
  remark: beijing是一个单词，要用单引号括起来。
  　　2. 查询住址在河北省的学生
  SELECT student_id,student_name FROM students WHERE CONTAINS( address, '"HEIBEI province"' )
  remark: HEBEI province是一个词组，在单引号里还要用双引号括起来。
  　　3. 查询住址在河北省或北京的学生
  SELECT student_id,student_name FROM students WHERE CONTAINS( address, '"HEIBEI province" OR beijing' )
  remark: 可以指定逻辑操作符(包括 AND ，AND NOT，OR )。
  　　4. 查询有 '南京路' 字样的地址
  SELECT student_id,student_name FROM students WHERE CONTAINS( address, 'nanjing NEAR road' )
  remark: 上面的查询将返回包含 'nanjing road'，'nanjing east road'，'nanjing west road' 等字样的地址。
  A NEAR B，就表示条件： A 靠近 B
  　　5. 查询以 '湖' 开头的地址
  SELECT student_id,student_name FROM students WHERE CONTAINS( address, '"hu*"' )
  remark: 上面的查询将返回包含 'hubei'，'hunan' 等字样的地址。
  记住是 *，不是 %。
  　　6. 类似加权的查询
  SELECT student_id,student_name FROM students WHERE CONTAINS( address, 'ISABOUT (city weight (.8), county wright (.4))' )
  remark: ISABOUT 是这种查询的关键字，weight 指定了一个介于 0~1之间的数，类似系数(我的理解)。表示不同条件有不同的侧重。
  　　7. 单词的多态查询
  SELECT student_id,student_name FROM students WHERE CONTAINS( address, 'FORMSOF (INFLECTIONAL,street)' )
  remark: 查询将返回包含 'street'，'streets'等字样的地址。
  对于动词将返回它的不同的时态，如：dry，将返回 dry，dried，drying 等等。
  子主题 5
• 子查询
  　　标量子查询：返回唯一值 select * from students where age > (select avg(age) from students);
  　　列级子查询：select * from classes where id in (select cls_id from students); （列级子查询返回的数据是一个集合，有自动去重的作用）
  　　行级子查询：select * from students where (age,height) = (select max(age), max(height) from students); 行级子查询返回的数据是一个元组
  　　表子查询：
  mysql语句执行顺序：完整的select 语句 - 格式 - 执行顺序（各公司可能更改执行顺序） select distinct * from 表 where 条件 group by 字段 having 条件 order by 条件 limit start,count; (执行顺序从左到右)
  子主题 1
  　　模糊查询用法总结
  　　　　1，%：表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
  比如 SELECT * FROM [user] WHERE u_name LIKE '%三%'
  将会把u_name为“张三”，“张猫三”、“三脚猫”，“唐三藏”等等有“三”的记录全找出来。
  另外，如果需要找出u_name中既有“三”又有“猫”的记录，请使用and条件
  SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE '%猫%'
  若使用 SELECT * FROM [user] WHERE u_name LIKE '%三%猫%'
  虽然能搜索出“三脚猫”，但不能搜索出符合条件的“张猫三”。
  　　　　2，_： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：
  比如 SELECT * FROM [user] WHERE u_name LIKE '_三_'
  只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的；
  再比如 SELECT * FROM [user] WHERE u_name LIKE '三__'; 只找出“三脚猫”这样name为三个字且第一个字是“三”的；
  　　　　3，[ ]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
  比如 SELECT * FROM [user] WHERE u_name LIKE '[张李王]三'将找出“张三”、“李三”、“王三”（而不是“张李王三”）；
  如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e”
  SELECT * FROM [user] WHERE u_name LIKE '老[1-9]'
  将找出“老1”、“老2”、……、“老9”；
  　　　　4，[^ ]：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
  比如 SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三' 将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；
  SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]'; 将排除“老1”到“老4”，寻找“老5”、“老6”、……
  　　　　5，查询内容包含通配符时
  由于通配符的缘故，导致我们查询特殊字符“%”、“_”、“[”的语句无法正常实现，而把特殊字符用“[ ]”括起便可正常查询。据此我们写出以下函数：
  function sqlencode(str) str=replace(str,"';","';';")
str=replace(str,"[","[[]") ';此句一定要在最先 str=replace(str,"","[]") str=replace(str,"%","[%]") sqlencode=str end function

python中操作mysql的步骤

• 开始：import pymysql

• 连接：con = mysql.connect(参数列表)
  　　参数host：连接的mysql主机，如果本机是'localhost'
  　　port：连接的mysql主机的端口，默认是3306
  　　参数database：数据库的名称
  　　参数user：连接的用户名
  　　参数password：连接的密码
  　　参数charset：通信采用的编码方式，推荐使用utf8(必须指定，否则拉丁编码格式，容易出错)

• 创建Cursor对象：cur = con.cursor()

• sql语句
  　　例子：sql = 'insert into focus (info_id) select id from info where code =%s'

• 执行：cur.execute(sql,[参数列表])，除了执行，还会返回查到符合条件的记录的条数，没有查询到记录则返回0
  例子：cur.execute(sql,[code])

• 提交：con.commit()
  在没有提交前可以用con.rollback()进行回滚操作

• 关闭：cur.close()

• 关闭：con.close()
  ☆☆防止参数化注入
  1、问题引入# SQL注入问题　－> 后台直接根据用户给定的数据　直接用字符串方式进行拼接

• select * from hero where name='妲己' #' and id = 123456789;（这样就把and id = 123456789注释掉了，默认不执行）

• 2、实际实现形式# 实际是内置了一个函数mogrify， 对特殊字符进行转义，打印出来结果print(cur.mogrify(sql, [name, id]))：select * from hero where name='妲己\' #' and id = '1234567';

• 3、解决方法# 参数化解决问题 - 防止SQL 对特殊字符进行\字符进行转义
  sql = "select * from hero where name=%s and id = %s;"
row_count = cur.execute(sql, [name, id])

msql高级

视图

• 定义视图：create view 试图名称 as select语句
• 查看视图：show tables;
• 使用视图：select * from 视图名称（一般定义以v_开头）
• 删除视图：drop view 视图名称
• 视图作用：1.提高重用性；2.不影响程序的基础上对数据库重构；3.提高了安全性；4.让数据更清晰。
  事务
• 原子性：事务不可分割，要么都成功，要么都失败
• 一致性：总是从一个一致性状态到另一个一致性状态
• 隔离性：事务（在提交之前）对外不可见
• 持久性：一旦提交，永久保存
• 事务命令
  　　开启事务：begin;或者start transaction;
  　　提交事务：commit
  　　回滚事务：rollback;（在提交之前有效）
• 注意点：
  　　1、使用事务命令前提是表的引擎是innodb；
  　　2、修改数据的命令会自动触发事务；
  　　3、在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据。
  索引
• 创建索引：create index 索引名称 on 表名(字段长度());
• 查看索引：show index from 表名；
• 查看语句执行时间：set profiling=1; (操作后) show profiles;
• 删除索引：drop index 索引名称 on 表名; （索引名称命名i_开头）
• 作用：索引是一种特殊的文件，包含对数据表里所有记录的引用指针。
  索引能加快数据库的查询速度
  账户管理
• 查看所有用户：desc user; select host, user, authentication_string from user;
• 创建账户、授权：grant 权限名称 on 数据库名 to '用户名'@'访问主机' identified by '密码';
  all privileges： 所有权限
  %：所有主机
• 修改权限： grant 权限名称 on 数据库名 to '用户名'@'访问主机' with grant option;
• 修改密码：update user set authentication_string=password('新密码') where user='用户名';
• 刷新权限：flush privileges；
• 删除用户：drop user '用户名'@'访问主机';
• 为项目创建数据库用户
  create user meiduo identified by 'meiduo';
grant all on meiduomail.* to 'meiduo'@'%';
flush privileges;
  设置主从服务配置
  常见问题
  show full processlist ；
  　　显示的数据里有个id字段，就是sessionid，执行 kill id就可，关闭session
  　　（注：应用程序一般和mysql都是做短连接的，执行完sql后都会关闭session，除非是卡在那，或者执行时间太长，才有机会在show processlist中看到）

---

mysql优化原理

mysql查询过程图解

mysql查询过程.png

优化原理

我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的。
　很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。

MySQL逻辑架构

• 客户端层：并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。
• 核心服务层：包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。
• 存储引擎：其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。

具体查询过程

• 客户端向MySQL服务器发送一条查询请求
  　　在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。
  　　当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一
• 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
• MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。
  　　任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存
• 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗
  基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升

数据库设计上针对缓问题的优化

• 用多个小表代替一个大表，注意不要过度设计
• 批量插入代替循环单条插入
• 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
• 可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存
• 不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。
• 服务器进行SQL解析、预处理
  　　MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。
  　　这个过程解析器主要通过语法规则来验证和解析。
  　　比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。
  　　预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。
  再由优化器生成对应的查询计划
  MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。
  在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。
  MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）等等。
• MySQL的查询优化器的优化策略
  重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）
  优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值
  提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）
  优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多）
  等等
  MySQL根据执行计划，调用存储引擎的API来执行查询
  查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。
  将结果返回给客户端，同时缓存查询结果

性能优化建议

• Scheme设计与数据类型优化
  选择数据类型只要遵循小而简单的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。
• 创建高性能索引
  索引相关的数据结构和算法
  通常我们所说的索引是指B-Tree索引，它是目前关系型数据库中查找数据最为常用和有效的索引，大多数存储引擎都支持这种索引。
  InnoDB就是使用的B+Tree。
  B+Tree中的B是指balance，意为平衡。
  B+Tree就是一种多路搜索树。
  理解B+Tree时，只需要理解其最重要的两个特征即可
  第一，所有的关键字（可以理解为数据）都存储在叶子节点（Leaf Page），非叶子节点（Index Page）并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。
  其次，所有的叶子节点由指针连接。如下图为高度为2的简化了的B+Tree。
  高性能策略
  MySQL不会使用索引的情况：非独立的列
  “独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。
  前缀索引
  如果列很长，通常可以索引开始的部分字符，这样可以有效节约索引空间，从而提高索引效率。
  多列索引和索引顺序
  当出现多个索引做相交操作时（多个AND条件），通常来说一个包含所有相关列的索引要优于多个独立索引。
  当出现多个索引做联合操作时（多个OR条件），对结果集的合并、排序等操作需要耗费大量的CPU和内存资源，特别是当其中的某些索引的选择性不高，需要返回合并大量数据时，查询成本更高。所以这种情况下还不如走全表扫描。
  避免多个范围条件
  覆盖索引
  索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量
  索引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多
  使用索引扫描来排序
  避免冗余和重复索引
  删除长期未使用的索引
  特定类型查询优化
  优化COUNT()查询
  如果要统计行数，直接使用COUNT(*)，意义清晰，且性能更好。
  优化关联查询
  确保ON和USING字句中的列上有索引。
  确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化。
  优化LIMIT分页
  优化UNION

常见错误理解与技巧

通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL。
　　对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。
　　UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。
　　通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。
　　TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。
　　大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。
　　schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。
　　大表ALTER TABLE非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。当然有一些奇技技巧可以解决这个问题，有兴趣可自行查阅。

详细优化网址 https://www.jianshu.com/p/d7665192aaaf?utm_campaign=haruki&utm_content=note&utm_medium=reader_share&utm_source=qq