數(shù)據(jù)庫(kù)分類(lèi)

關(guān)系型數(shù)據(jù)庫(kù)庫(kù)：Relational Database Management System (RDBMS)

• oracle
• mysql：web使用最廣泛的關(guān)系型數(shù)據(jù)庫(kù)
• sql server
• sqlite:輕量級(jí)數(shù)據(jù)庫(kù)

非關(guān)系型數(shù)據(jù)庫(kù)

• redis
• mysql
• mongodb

數(shù)據(jù)庫(kù)設(shè)計(jì)范式

設(shè)計(jì)關(guān)系數(shù)據(jù)庫(kù)時(shí)存崖，遵從不同的規(guī)范要求，設(shè)計(jì)出合理的關(guān)系型數(shù)據(jù)庫(kù)，這些不同的規(guī)范要求被稱(chēng)為不同的范式，各種范式呈遞次規(guī)范选浑，越高的范式數(shù)據(jù)庫(kù)冗余越小崭歧。
　　目前關(guān)系數(shù)據(jù)庫(kù)有六種范式：第一范式（1NF）、第二范式（2NF）寄症、第三范式（3NF）、巴斯-科德范式（BCNF）矩动、第四范式(4NF）和第五范式（5NF有巧，又稱(chēng)完美范式）。
　　范式越高悲没，冗余最低篮迎，一般到三范式，再往上示姿，表越多甜橱，可能導(dǎo)致查詢(xún)效率下降。所以有時(shí)為了提高運(yùn)行效率栈戳，可以讓數(shù)據(jù)冗余(反三范式岂傲，一般某個(gè)數(shù)據(jù)經(jīng)常被訪問(wèn)時(shí)，比如數(shù)據(jù)表里存放了語(yǔ)文數(shù)學(xué)英語(yǔ)成績(jī)子檀，但是如果在某個(gè)時(shí)間經(jīng)常要得到它的總分镊掖，每次都要進(jìn)行計(jì)算會(huì)降低性能，可以加上總分這個(gè)冗余字段)褂痰。
　　后面的范式是在滿(mǎn)足前面范式的基礎(chǔ)上亩进，比如滿(mǎn)足第二范式的一定滿(mǎn)足第一范式。
　　　　☆☆☆第一范式（1NF）：確保每一列的原子性
　　　　☆☆☆第二范式:非主鍵字段必須依賴(lài)于主鍵字段
　　　　☆☆☆第三范式：在1NF基礎(chǔ)上脐恩，除了主鍵以外的其它列都不傳遞依賴(lài)于主鍵列

mysql的安裝(ubuntu)

• 安裝：sudo apt-get install mysql-server
• 啟動(dòng)：sudo service mysql start
• 停止：sudo service mysql stop
• 重啟：sudo service mysql restart
• 設(shè)置密碼：mysqladmin -u root password mysql（window）
• 啟動(dòng)：net start mysql（window）

配置

數(shù)據(jù)庫(kù)的創(chuàng)建與使用

• 連接：mysql -uroot -p
• 創(chuàng)建：在登錄的狀態(tài)下執(zhí)行 create database 數(shù)據(jù)庫(kù)名 charset=utf8;
  （☆☆☆不指定 charset 那么默認(rèn)是拉丁字符集镐侯，會(huì)有下面的報(bào)錯(cuò)信息）
  在進(jìn)行數(shù)據(jù)庫(kù)遷移、創(chuàng)建時(shí)報(bào)錯(cuò)驶冒，信息（ERROR 1366 (HY000): Incorrect string value: '\xE5\xB0\x84\xE9\x9B\x95...' for column 'name' at row 1
  ）
• 顯示：show databases;
• 使用：use 數(shù)據(jù)庫(kù)名;
• 刪除：drop database 數(shù)據(jù)庫(kù)名;
• 備份：mysqldump -uroot -p 數(shù)據(jù)庫(kù)名 > python.sql
• 恢復(fù)：mysql -uroot -p 新數(shù)據(jù)庫(kù)名 < python.sql (新數(shù)據(jù)庫(kù)名已經(jīng)創(chuàng)建好了)
• 退出：exit 苟翻、ctrl+d（清屏ctrl+l、退出執(zhí)行語(yǔ)句ctrl+c+enter骗污、掛起ctrl + z崇猫、從掛起返回fg）
  　　1、Ctrl+C比較暴力需忿，就是發(fā)送Terminal到當(dāng)前的程序诅炉，比如你正在運(yùn)行一個(gè)查找功能蜡歹，文件正在查找中，Ctrl+C就會(huì)強(qiáng)制結(jié)束當(dāng)前的這個(gè)進(jìn)程涕烧。
  　　2月而、Ctrl+Z是把當(dāng)前的程序掛起，暫停執(zhí)行這個(gè)程序，比如你正在mysql終端中，需要出來(lái)搞點(diǎn)其他的文件操作漱受，又不想退出mysql終端（因?yàn)橄麓芜€得輸入用戶(hù)名密碼進(jìn)入，挺麻煩）憨攒，于是可以ctrl+z將mysql掛起，然后進(jìn)行其他操作阀参，然后輸入fg回車(chē)后就可以回來(lái)肝集，當(dāng)然可以掛起好多進(jìn)程到后臺(tái)，然后fg 加編號(hào)就能把掛起的進(jìn)程返回到前臺(tái)蛛壳。當(dāng)然杏瞻，配合bg和fg命令進(jìn)行前后臺(tái)切換會(huì)非常方便。
  　　3衙荐、Ctrl+D 是發(fā)送一個(gè)exit信號(hào)伐憾，沒(méi)有那么強(qiáng)烈，類(lèi)似ctrl+C的操作赫模，比如你從管理員root退回到你的普通用戶(hù)就可以這么用。

表的創(chuàng)建和使用

• 創(chuàng)建： create table table_name(字段 類(lèi)型 約束蒸矛，字段2 類(lèi)型 約束....);
  　　例 create table students(id tinyint unsigned not null primary key auto_increment, name varchar(20) default "", height decimal(5,2), ..... );
  mysql中主鍵用auto_increment關(guān)鍵字避免沖突
• 查看表結(jié)構(gòu)：desc 表名瀑罗；
• 顯示：show tables;
• 刪除：drop table 表名;
• 表中字段的數(shù)據(jù)類(lèi)型
  　　整型：int, ,tinyint, bit（1byte = 8 bit）;bit只有0和1
  　　定長(zhǎng)字符串: char；可變長(zhǎng)度字符串: varchar
  　　浮點(diǎn)數(shù)：decimal(5,2) 共五位數(shù)雏掠，2位小數(shù)
  　　日期：date, time, datetime
  　　大文件存儲(chǔ)：text 字符串類(lèi)型
  　　枚舉類(lèi)型(enum)
  　　　　用法：在創(chuàng)建表時(shí)斩祭，gender enum('男','女','保密','人妖')
• 表中字段的常用約束條件
  　　主鍵： primary key 作用：可以通過(guò)唯一字段確定一行記錄
  　　非空 ：not null 作用：不予許字段為空
  　　唯一 ：unique 作用：字段的值不允許重復(fù)
  　　默認(rèn)： default 作用：默認(rèn)參數(shù)，用戶(hù)不指定則使用默認(rèn)值
  　　有符號(hào)和無(wú)符號(hào)： signed unsigned
  　　自增長(zhǎng)：auto_increment(一般用于id自動(dòng)加1)
  　　外鍵：foreign key
• 表字段的增刪改查adcm(alter table 表名 ...)
  　　添加：alter table 表名 add 字段 類(lèi)型(長(zhǎng)度) 約束 條件乡话；例如（alter table student add id int primary key not null;）
  　　修改（重命名）：alter table 表名 change 舊字段 新字段 類(lèi)型(長(zhǎng)度) 約束 條件摧玫；例如（alter table student add id id1 int primary key not null;）
  　　修改（不重命名）：alter table 表名 modify 字段 類(lèi)型(長(zhǎng)度) 約束 條件；例如（alter table student add id int primary key not null;）
  　　刪除：alter table 表名 drop 字段 绑青；例如（alter table student drop id;）
• 表中記錄的增刪改查（crud）
  　　curd的解釋: 代表創(chuàng)建（Create）诬像、更新（Update）、讀日⒂ぁ（Retrieve）和刪除（Delete）
  　　　查詢(xún)(全列): select * from table_name;
  　　　　　指定列：select 字段1坏挠，字段2 from table_name;
  　　　　　表(字段)重命名：select 字段1 as 字段2 from table_name as ta_na;
  　　　　　消除重復(fù)行：select distinct 列1,... from 表名;
  　　　修改：uptdate 表名 set 列1=值1,列2=值2,... where 條件；
  　　　增加：insert into 表名 values(),(),()...; 指定列增加 insert into 表名 (字段1, 字段2,...) values(),(),()...;
  　　　　　當(dāng)用子查詢(xún)的方式獲取value時(shí)邪乍，寫(xiě)法更改為insert into 表名 （字段） 子查詢(xún)表達(dá)式降狠；（不要帶上values關(guān)鍵字）
  　　　　　實(shí)例：insert into bookinfo values(0,'新增２','2011-1-1',20,22,1)
  指定列增加時(shí)按照自己指定的字段(順序可以和表中不一樣)按照順序插入值
  　　　邏輯刪除：isDelete 对竣；當(dāng)值為1時(shí)代表要?jiǎng)h除，update 表名 set isDelete=1 where 條件榜配；
  　　刪除：delete from 表名 where 條件否纬；
  概要
• 表中記錄的查詢(xún)命令
  　　條件查詢(xún)：select * from 表名 where 字段判斷條件;
  　　比較運(yùn)算符: = , >, <, >=, <=, != <>不等于的兩種形式 ；
  　　邏輯運(yùn)算符：and, not, or蛋褥；
  　　模糊查詢(xún)：like %表示任意多個(gè)字符临燃；_表示一個(gè)任意字符；
  　　select * from students where name like '黃_';
  　　范圍查詢(xún)：in()非連續(xù)范圍壁拉；between and 連續(xù)范圍谬俄；
  　　空判斷：is null ; is not null
  　　優(yōu)先級(jí)：( ) > not > 比較運(yùn)算符 > 邏輯運(yùn)算符 ;
  　　排序：select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]； asc 升序 desc 降序
  　　應(yīng)用弃理；☆☆☆☆select gender,group_concat(name order by age) from students group by gender; 分組后各組按照年齡排序后顯示對(duì)應(yīng)的姓名
  結(jié)果以行的方式顯示
  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;
• 聚合函數(shù)：select 聚合函數(shù)(*或者字段 ) from表名溃论；
  　總數(shù)：count(*)
  　最大值：max( )
  　最小值：min( )
  　求和：sum( )
  　平均值：avg( )
  select gender,avg(age),group_concat(name) from students group by gender with rollup;
  　round(小數(shù), 保留的位數(shù))
• 分組：select 字段 from 表名 group by 字段；將查詢(xún)結(jié)果按照1個(gè)或多個(gè)字段進(jìn)行分組痘昌，字段值相同的為一組
  　　1 輸出：group_concat(字段名)可以作為一個(gè)輸出字段來(lái)使用钥勋；
  　　2 group by + group_concat(字段名) 表示分組之后，根據(jù)分組結(jié)果辆苔，使用group_concat()來(lái)放置每一組的某字段的值的集合算灸； 　
  　　3 例如：select gender,group_concat(name) from students group by gender;
  　　group by + 集合函數(shù)；例如select gender,avg(age) from students group by gender;
  　　group by + having驻啤；用法和where相同
  　　group by + with rollup菲驴； with rollup的作用是：在最后新增一行，來(lái)記錄所顯示字段所有記錄的總和(平均值骑冗、最大值赊瞬、最小值)
  　　　實(shí)例：☆☆☆☆ select gender,avg(age) from students group by gender with rollup;（顯示所有人年齡的平均值）
• 獲取部分行：select * from 表名 limit start,count; start從0開(kāi)始
• 分頁(yè)-顯示第n頁(yè)的m條數(shù)據(jù)；
  select * from students limit (n-1)*m,m贼涩；
  第一個(gè)數(shù)字可以理解為要顯示數(shù)據(jù)的id巧涧，從該id開(kāi)始；
  可以隨意指定遥倦，按上述規(guī)則是均分顯示谤绳；
  select * from students order by age,id limit 3,3; (order by 和limit處理時(shí)有mysql的bug存在，需要在限定id字段袒哥，可以防止bug出現(xiàn))
• 連接查詢(xún)select * from 左表 join 右表; (返回的笛卡爾積缩筛，用join實(shí)現(xiàn))
  　　內(nèi)連接：select * from 左表 inner join 右表 on 條件；（在mysql中inner join和join是相同的统诺，在其他數(shù)據(jù)庫(kù)中join是笛卡爾積歪脏，inner join 是內(nèi)連接）
  　　右連接：select * from 左表 right join 右表 on 條件；（在連接的基礎(chǔ)上粮呢，添加額外數(shù)據(jù)-來(lái)自右表婿失，左表中沒(méi)有對(duì)應(yīng)的數(shù)據(jù)用Null填充）
  　　左連接：select * from 左表 right join 右表 on 條件钞艇；（在連接的基礎(chǔ)上，添加額外數(shù)據(jù)-來(lái)自左表豪硅，右表中沒(méi)有對(duì)應(yīng)的數(shù)據(jù)用Null填充）
  又稱(chēng)外連接
• 自關(guān)聯(lián)：select * from 表 join 表 on 條件 where 條件; (相同的表進(jìn)行笛卡爾積計(jì)算)
  　全局搜索-contains語(yǔ)法
  　　1. 查詢(xún)住址在北京的學(xué)生
  SELECT student_id,student_name FROM students WHERE CONTAINS( address, 'beijing' )
  remark: beijing是一個(gè)單詞哩照，要用單引號(hào)括起來(lái)。
  　　2. 查詢(xún)住址在河北省的學(xué)生
  SELECT student_id,student_name FROM students WHERE CONTAINS( address, '"HEIBEI province"' )
  remark: HEBEI province是一個(gè)詞組懒浮，在單引號(hào)里還要用雙引號(hào)括起來(lái)飘弧。
  　　3. 查詢(xún)住址在河北省或北京的學(xué)生
  SELECT student_id,student_name FROM students WHERE CONTAINS( address, '"HEIBEI province" OR beijing' )
  remark: 可以指定邏輯操作符(包括 AND ，AND NOT砚著，OR )次伶。
  　　4. 查詢(xún)有 '南京路' 字樣的地址
  SELECT student_id,student_name FROM students WHERE CONTAINS( address, 'nanjing NEAR road' )
  remark: 上面的查詢(xún)將返回包含 'nanjing road'，'nanjing east road'稽穆，'nanjing west road' 等字樣的地址冠王。
  A NEAR B，就表示條件： A 靠近 B
  　　5. 查詢(xún)以 '湖' 開(kāi)頭的地址
  SELECT student_id,student_name FROM students WHERE CONTAINS( address, '"hu*"' )
  remark: 上面的查詢(xún)將返回包含 'hubei'舌镶，'hunan' 等字樣的地址柱彻。
  記住是 *，不是 %餐胀。
  　　6. 類(lèi)似加權(quán)的查詢(xún)
  SELECT student_id,student_name FROM students WHERE CONTAINS( address, 'ISABOUT (city weight (.8), county wright (.4))' )
  remark: ISABOUT 是這種查詢(xún)的關(guān)鍵字哟楷，weight 指定了一個(gè)介于 0~1之間的數(shù)，類(lèi)似系數(shù)(我的理解)否灾。表示不同條件有不同的側(cè)重卖擅。
  　　7. 單詞的多態(tài)查詢(xún)
  SELECT student_id,student_name FROM students WHERE CONTAINS( address, 'FORMSOF (INFLECTIONAL,street)' )
  remark: 查詢(xún)將返回包含 'street'，'streets'等字樣的地址墨技。
  對(duì)于動(dòng)詞將返回它的不同的時(shí)態(tài)磨镶，如：dry，將返回 dry健提，dried，drying 等等伟叛。
  子主題 5
• 子查詢(xún)
  　　標(biāo)量子查詢(xún)：返回唯一值 select * from students where age > (select avg(age) from students);
  　　列級(jí)子查詢(xún)：select * from classes where id in (select cls_id from students); （列級(jí)子查詢(xún)返回的數(shù)據(jù)是一個(gè)集合私痹，有自動(dòng)去重的作用）
  　　行級(jí)子查詢(xún)：select * from students where (age,height) = (select max(age), max(height) from students); 行級(jí)子查詢(xún)返回的數(shù)據(jù)是一個(gè)元組
  　　表子查詢(xún)：
  mysql語(yǔ)句執(zhí)行順序：完整的select 語(yǔ)句 - 格式 - 執(zhí)行順序（各公司可能更改執(zhí)行順序） select distinct * from 表 where 條件 group by 字段 having 條件 order by 條件 limit start,count; (執(zhí)行順序從左到右)
  子主題 1
  　　模糊查詢(xún)用法總結(jié)
  　　　　1，%：表示任意0個(gè)或多個(gè)字符统刮∥勺瘢可匹配任意類(lèi)型和長(zhǎng)度的字符，有些情況下若是中文侥蒙，請(qǐng)使用兩個(gè)百分號(hào)（%%）表示暗膜。
  比如 SELECT * FROM [user] WHERE u_name LIKE '%三%'
  將會(huì)把u_name為“張三”，“張貓三”鞭衩、“三腳貓”学搜，“唐三藏”等等有“三”的記錄全找出來(lái)娃善。
  另外，如果需要找出u_name中既有“三”又有“貓”的記錄瑞佩，請(qǐng)使用and條件
  SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE '%貓%'
  若使用 SELECT * FROM [user] WHERE u_name LIKE '%三%貓%'
  雖然能搜索出“三腳貓”聚磺，但不能搜索出符合條件的“張貓三”。
  　　　　2炬丸，_： 表示任意單個(gè)字符瘫寝。匹配單個(gè)任意字符，它常用來(lái)限制表達(dá)式的字符長(zhǎng)度語(yǔ)句：
  比如 SELECT * FROM [user] WHERE u_name LIKE '_三_'
  只找出“唐三藏”這樣u_name為三個(gè)字且中間一個(gè)字是“三”的稠炬；
  再比如 SELECT * FROM [user] WHERE u_name LIKE '三__'; 只找出“三腳貓”這樣name為三個(gè)字且第一個(gè)字是“三”的焕阿；
  　　　　3，[ ]：表示括號(hào)內(nèi)所列字符中的一個(gè)（類(lèi)似正則表達(dá)式）首启。指定一個(gè)字符暮屡、字符串或范圍，要求所匹配對(duì)象為它們中的任一個(gè)闽坡。
  比如 SELECT * FROM [user] WHERE u_name LIKE '[張李王]三'將找出“張三”栽惶、“李三”、“王三”（而不是“張李王三”）疾嗅；
  如 [ ] 內(nèi)有一系列字符（01234外厂、abcde之類(lèi)的）則可略寫(xiě)為“0-4”、“a-e”
  SELECT * FROM [user] WHERE u_name LIKE '老[1-9]'
  將找出“老1”代承、“老2”汁蝶、……、“老9”论悴；
  　　　　4掖棉，[^ ]：表示不在括號(hào)所列之內(nèi)的單個(gè)字符。其取值和 [] 相同膀估，但它要求所匹配對(duì)象為指定字符以外的任一個(gè)字符幔亥。
  比如 SELECT * FROM [user] WHERE u_name LIKE '[^張李王]三' 將找出不姓“張”、“李”察纯、“王”的“趙三”帕棉、“孫三”等；
  SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]'; 將排除“老1”到“老4”饼记，尋找“老5”香伴、“老6”、……
  　　　　5具则，查詢(xún)內(nèi)容包含通配符時(shí)
  由于通配符的緣故即纲，導(dǎo)致我們查詢(xún)特殊字符“%”、“_”博肋、“[”的語(yǔ)句無(wú)法正常實(shí)現(xiàn)低斋，而把特殊字符用“[ ]”括起便可正常查詢(xún)蜂厅。據(jù)此我們寫(xiě)出以下函數(shù)：
  function sqlencode(str) str=replace(str,"';","';';")
str=replace(str,"[","[[]") ';此句一定要在最先 str=replace(str,"","[]") str=replace(str,"%","[%]") sqlencode=str end function

python中操作mysql的步驟

• 開(kāi)始：import pymysql

• 連接：con = mysql.connect(參數(shù)列表)
  　　參數(shù)host：連接的mysql主機(jī)，如果本機(jī)是'localhost'
  　　port：連接的mysql主機(jī)的端口拔稳，默認(rèn)是3306
  　　參數(shù)database：數(shù)據(jù)庫(kù)的名稱(chēng)
  　　參數(shù)user：連接的用戶(hù)名
  　　參數(shù)password：連接的密碼
  　　參數(shù)charset：通信采用的編碼方式葛峻，推薦使用utf8(必須指定，否則拉丁編碼格式巴比，容易出錯(cuò))

• 創(chuàng)建Cursor對(duì)象：cur = con.cursor()

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

• 執(zhí)行：cur.execute(sql,[參數(shù)列表])术奖，除了執(zhí)行，還會(huì)返回查到符合條件的記錄的條數(shù)轻绞，沒(méi)有查詢(xún)到記錄則返回0
  例子：cur.execute(sql,[code])

• 提交：con.commit()
  在沒(méi)有提交前可以用con.rollback()進(jìn)行回滾操作

• 關(guān)閉：cur.close()

• 關(guān)閉：con.close()
  ☆☆防止參數(shù)化注入
  1采记、問(wèn)題引入# SQL注入問(wèn)題　－> 后臺(tái)直接根據(jù)用戶(hù)給定的數(shù)據(jù)　直接用字符串方式進(jìn)行拼接

• select * from hero where name='妲己' #' and id = 123456789;（這樣就把a(bǔ)nd id = 123456789注釋掉了政勃，默認(rèn)不執(zhí)行）

• 2唧龄、實(shí)際實(shí)現(xiàn)形式# 實(shí)際是內(nèi)置了一個(gè)函數(shù)mogrify， 對(duì)特殊字符進(jìn)行轉(zhuǎn)義奸远，打印出來(lái)結(jié)果print(cur.mogrify(sql, [name, id]))：select * from hero where name='妲己\' #' and id = '1234567';

• 3既棺、解決方法# 參數(shù)化解決問(wèn)題 - 防止SQL 對(duì)特殊字符進(jìn)行\(zhòng)字符進(jìn)行轉(zhuǎn)義
  sql = "select * from hero where name=%s and id = %s;"
row_count = cur.execute(sql, [name, id])

msql高級(jí)

視圖

• 定義視圖：create view 試圖名稱(chēng) as select語(yǔ)句
• 查看視圖：show tables;
• 使用視圖：select * from 視圖名稱(chēng)（一般定義以v_開(kāi)頭）
• 刪除視圖：drop view 視圖名稱(chēng)
• 視圖作用：1.提高重用性；2.不影響程序的基礎(chǔ)上對(duì)數(shù)據(jù)庫(kù)重構(gòu)懒叛；3.提高了安全性丸冕；4.讓數(shù)據(jù)更清晰。
  事務(wù)
• 原子性：事務(wù)不可分割薛窥，要么都成功胖烛，要么都失敗
• 一致性：總是從一個(gè)一致性狀態(tài)到另一個(gè)一致性狀態(tài)
• 隔離性：事務(wù)（在提交之前）對(duì)外不可見(jiàn)
• 持久性：一旦提交，永久保存
• 事務(wù)命令
  　　開(kāi)啟事務(wù)：begin;或者start transaction;
  　　提交事務(wù)：commit
  　　回滾事務(wù)：rollback;（在提交之前有效）
• 注意點(diǎn)：
  　　1诅迷、使用事務(wù)命令前提是表的引擎是innodb佩番；
  　　2、修改數(shù)據(jù)的命令會(huì)自動(dòng)觸發(fā)事務(wù)罢杉；
  　　3趟畏、在SQL語(yǔ)句中有手動(dòng)開(kāi)啟事務(wù)的原因是：可以進(jìn)行多次數(shù)據(jù)的修改，如果成功一起成功滩租，否則一起會(huì)滾到之前的數(shù)據(jù)拱镐。
  索引
• 創(chuàng)建索引：create index 索引名稱(chēng) on 表名(字段長(zhǎng)度());
• 查看索引：show index from 表名；
• 查看語(yǔ)句執(zhí)行時(shí)間：set profiling=1; (操作后) show profiles;
• 刪除索引：drop index 索引名稱(chēng) on 表名; （索引名稱(chēng)命名i_開(kāi)頭）
• 作用：索引是一種特殊的文件持际，包含對(duì)數(shù)據(jù)表里所有記錄的引用指針。
  索引能加快數(shù)據(jù)庫(kù)的查詢(xún)速度
  賬戶(hù)管理
• 查看所有用戶(hù)：desc user; select host, user, authentication_string from user;
• 創(chuàng)建賬戶(hù)哗咆、授權(quán)：grant 權(quán)限名稱(chēng) on 數(shù)據(jù)庫(kù)名 to '用戶(hù)名'@'訪問(wèn)主機(jī)' identified by '密碼';
  all privileges： 所有權(quán)限
  %：所有主機(jī)
• 修改權(quán)限： grant 權(quán)限名稱(chēng) on 數(shù)據(jù)庫(kù)名 to '用戶(hù)名'@'訪問(wèn)主機(jī)' with grant option;
• 修改密碼：update user set authentication_string=password('新密碼') where user='用戶(hù)名';
• 刷新權(quán)限：flush privileges蜘欲；
• 刪除用戶(hù)：drop user '用戶(hù)名'@'訪問(wèn)主機(jī)';
• 為項(xiàng)目創(chuàng)建數(shù)據(jù)庫(kù)用戶(hù)
  create user meiduo identified by 'meiduo';
grant all on meiduomail.* to 'meiduo'@'%';
flush privileges;
  設(shè)置主從服務(wù)配置
  常見(jiàn)問(wèn)題
  show full processlist ；
  　　顯示的數(shù)據(jù)里有個(gè)id字段晌柬，就是sessionid姥份，執(zhí)行 kill id就可郭脂，關(guān)閉session
  　　（注：應(yīng)用程序一般和mysql都是做短連接的澈歉，執(zhí)行完sql后都會(huì)關(guān)閉session展鸡，除非是卡在那，或者執(zhí)行時(shí)間太長(zhǎng)埃难，才有機(jī)會(huì)在show processlist中看到）

mysql優(yōu)化原理

mysql查詢(xún)過(guò)程圖解

mysql查詢(xún)過(guò)程.png

優(yōu)化原理

我們總是希望MySQL能夠獲得更高的查詢(xún)性能莹弊，最好的辦法是弄清楚MySQL是如何優(yōu)化和執(zhí)行查詢(xún)的。
　很多的查詢(xún)優(yōu)化工作實(shí)際上就是遵循一些原則讓MySQL的優(yōu)化器能夠按照預(yù)想的合理方式運(yùn)行而已涡尘。

MySQL邏輯架構(gòu)

• 客戶(hù)端層：并非MySQL所獨(dú)有忍弛，諸如：連接處理、授權(quán)認(rèn)證考抄、安全等功能均在這一層處理细疚。
• 核心服務(wù)層：包括查詢(xún)解析、分析川梅、優(yōu)化疯兼、緩存、內(nèi)置函數(shù)(比如：時(shí)間贫途、數(shù)學(xué)吧彪、加密等函數(shù))。所有的跨存儲(chǔ)引擎的功能也在這一層實(shí)現(xiàn)：存儲(chǔ)過(guò)程潮饱、觸發(fā)器来氧、視圖等。
• 存儲(chǔ)引擎：其負(fù)責(zé)MySQL中的數(shù)據(jù)存儲(chǔ)和提取香拉。和Linux下的文件系統(tǒng)類(lèi)似啦扬，每種存儲(chǔ)引擎都有其優(yōu)勢(shì)和劣勢(shì)。中間的服務(wù)層通過(guò)API與存儲(chǔ)引擎通信凫碌，這些API接口屏蔽了不同存儲(chǔ)引擎間的差異扑毡。

具體查詢(xún)過(guò)程

• 客戶(hù)端向MySQL服務(wù)器發(fā)送一條查詢(xún)請(qǐng)求
  　　在任一時(shí)刻，要么是服務(wù)器向客戶(hù)端發(fā)送數(shù)據(jù)盛险，要么是客戶(hù)端向服務(wù)器發(fā)送數(shù)據(jù)瞄摊，這兩個(gè)動(dòng)作不能同時(shí)發(fā)生。
  　　當(dāng)服務(wù)器響應(yīng)客戶(hù)端請(qǐng)求時(shí)苦掘，客戶(hù)端必須完整的接收整個(gè)返回結(jié)果换帜，而不能簡(jiǎn)單的只取前面幾條結(jié)果，然后讓服務(wù)器停止發(fā)送鹤啡。因而在實(shí)際開(kāi)發(fā)中惯驼，盡量保持查詢(xún)簡(jiǎn)單且只返回必需的數(shù)據(jù)，減小通信間數(shù)據(jù)包的大小和數(shù)量是一個(gè)非常好的習(xí)慣，這也是查詢(xún)中盡量避免使用SELECT *以及加上LIMIT限制的原因之一
• 服務(wù)器首先檢查查詢(xún)緩存祟牲，如果命中緩存隙畜，則立刻返回存儲(chǔ)在緩存中的結(jié)果。否則進(jìn)入下一階段
• MySQL的查詢(xún)緩存系統(tǒng)會(huì)跟蹤查詢(xún)中涉及的每個(gè)表说贝，如果這些表（數(shù)據(jù)或結(jié)構(gòu)）發(fā)生變化议惰，那么和這張表相關(guān)的所有緩存數(shù)據(jù)都將失效。
  　　任何的查詢(xún)語(yǔ)句在開(kāi)始之前都必須經(jīng)過(guò)檢查乡恕，即使這條SQL語(yǔ)句永遠(yuǎn)不會(huì)命中緩存
• 如果查詢(xún)結(jié)果可以被緩存言询，那么執(zhí)行完成后，會(huì)將結(jié)果存入緩存几颜，也會(huì)帶來(lái)額外的系統(tǒng)消耗
  基于此倍试，我們要知道并不是什么情況下查詢(xún)緩存都會(huì)提高系統(tǒng)性能，緩存和失效都會(huì)帶來(lái)額外消耗蛋哭，只有當(dāng)緩存帶來(lái)的資源節(jié)約大于其本身消耗的資源時(shí)县习，才會(huì)給系統(tǒng)帶來(lái)性能提升

數(shù)據(jù)庫(kù)設(shè)計(jì)上針對(duì)緩問(wèn)題的優(yōu)化

• 用多個(gè)小表代替一個(gè)大表，注意不要過(guò)度設(shè)計(jì)
• 批量插入代替循環(huán)單條插入
• 合理控制緩存空間大小谆趾，一般來(lái)說(shuō)其大小設(shè)置為幾十兆比較合適
• 可以通過(guò)SQL_CACHE和SQL_NO_CACHE來(lái)控制某個(gè)查詢(xún)語(yǔ)句是否需要進(jìn)行緩存
• 不要輕易打開(kāi)查詢(xún)緩存躁愿，特別是寫(xiě)密集型應(yīng)用。如果你實(shí)在是忍不住沪蓬，可以將query_cache_type設(shè)置為DEMAND彤钟，這時(shí)只有加入SQL_CACHE的查詢(xún)才會(huì)走緩存，其他查詢(xún)則不會(huì)跷叉，這樣可以非常自由地控制哪些查詢(xún)需要被緩存逸雹。
• 服務(wù)器進(jìn)行SQL解析、預(yù)處理
  　　MySQL通過(guò)關(guān)鍵字將SQL語(yǔ)句進(jìn)行解析云挟，并生成一顆對(duì)應(yīng)的解析樹(shù)梆砸。
  　　這個(gè)過(guò)程解析器主要通過(guò)語(yǔ)法規(guī)則來(lái)驗(yàn)證和解析。
  　　比如SQL中是否使用了錯(cuò)誤的關(guān)鍵字或者關(guān)鍵字的順序是否正確等等园欣。
  　　預(yù)處理則會(huì)根據(jù)MySQL規(guī)則進(jìn)一步檢查解析樹(shù)是否合法帖世。比如檢查要查詢(xún)的數(shù)據(jù)表和數(shù)據(jù)列是否存在等等。
  再由優(yōu)化器生成對(duì)應(yīng)的查詢(xún)計(jì)劃
  MySQL使用基于成本的優(yōu)化器沸枯，它嘗試預(yù)測(cè)一個(gè)查詢(xún)使用某種執(zhí)行計(jì)劃時(shí)的成本日矫，并選擇其中成本最小的一個(gè)。
  在MySQL可以通過(guò)查詢(xún)當(dāng)前會(huì)話的last_query_cost的值來(lái)得到其計(jì)算當(dāng)前查詢(xún)的成本绑榴。
  MySQL認(rèn)為的最優(yōu)跟我們想的不一樣（我們希望執(zhí)行時(shí)間盡可能短哪轿，但MySQL值選擇它認(rèn)為成本小的，但成本小并不意味著執(zhí)行時(shí)間短）等等翔怎。
• MySQL的查詢(xún)優(yōu)化器的優(yōu)化策略
  重新定義表的關(guān)聯(lián)順序（多張表關(guān)聯(lián)查詢(xún)時(shí)窃诉，并不一定按照SQL中指定的順序進(jìn)行，但有一些技巧可以指定關(guān)聯(lián)順序）
  優(yōu)化MIN()和MAX()函數(shù)（找某列的最小值，如果該列有索引褐奴，只需要查找B+Tree索引最左端，反之則可以找到最大值
  提前終止查詢(xún)（比如：使用Limit時(shí)于毙，查找到滿(mǎn)足數(shù)量的結(jié)果集后會(huì)立即終止查詢(xún)）
  優(yōu)化排序（在老版本MySQL會(huì)使用兩次傳輸排序敦冬，即先讀取行指針和需要排序的字段在內(nèi)存中對(duì)其排序，然后再根據(jù)排序結(jié)果去讀取數(shù)據(jù)行唯沮，而新版本采用的是單次傳輸排序脖旱，也就是一次讀取所有的數(shù)據(jù)行，然后根據(jù)給定的列排序介蛉。對(duì)于I/O密集型應(yīng)用萌庆，效率會(huì)高很多）
  等等
  MySQL根據(jù)執(zhí)行計(jì)劃，調(diào)用存儲(chǔ)引擎的API來(lái)執(zhí)行查詢(xún)
  查詢(xún)執(zhí)行引擎根據(jù)執(zhí)行計(jì)劃給出的指令逐步執(zhí)行得出結(jié)果币旧。整個(gè)執(zhí)行過(guò)程的大部分操作均是通過(guò)調(diào)用存儲(chǔ)引擎實(shí)現(xiàn)的接口來(lái)完成践险，這些接口被稱(chēng)為handler API。
  將結(jié)果返回給客戶(hù)端吹菱，同時(shí)緩存查詢(xún)結(jié)果

性能優(yōu)化建議

• Scheme設(shè)計(jì)與數(shù)據(jù)類(lèi)型優(yōu)化
  選擇數(shù)據(jù)類(lèi)型只要遵循小而簡(jiǎn)單的原則就好巍虫，越小的數(shù)據(jù)類(lèi)型通常會(huì)更快，占用更少的磁盤(pán)鳍刷、內(nèi)存占遥，處理時(shí)需要的CPU周期也更少。
• 創(chuàng)建高性能索引
  索引相關(guān)的數(shù)據(jù)結(jié)構(gòu)和算法
  通常我們所說(shuō)的索引是指B-Tree索引输瓜，它是目前關(guān)系型數(shù)據(jù)庫(kù)中查找數(shù)據(jù)最為常用和有效的索引瓦胎，大多數(shù)存儲(chǔ)引擎都支持這種索引。
  InnoDB就是使用的B+Tree尤揣。
  B+Tree中的B是指balance搔啊，意為平衡。
  B+Tree就是一種多路搜索樹(shù)芹缔。
  理解B+Tree時(shí)坯癣，只需要理解其最重要的兩個(gè)特征即可
  第一，所有的關(guān)鍵字（可以理解為數(shù)據(jù)）都存儲(chǔ)在葉子節(jié)點(diǎn)（Leaf Page）最欠，非葉子節(jié)點(diǎn)（Index Page）并不存儲(chǔ)真正的數(shù)據(jù)示罗，所有記錄節(jié)點(diǎn)都是按鍵值大小順序存放在同一層葉子節(jié)點(diǎn)上。
  其次芝硬，所有的葉子節(jié)點(diǎn)由指針連接蚜点。如下圖為高度為2的簡(jiǎn)化了的B+Tree。
  高性能策略
  MySQL不會(huì)使用索引的情況：非獨(dú)立的列
  “獨(dú)立的列”是指索引列不能是表達(dá)式的一部分拌阴，也不能是函數(shù)的參數(shù)绍绘。
  前綴索引
  如果列很長(zhǎng)，通常可以索引開(kāi)始的部分字符陪拘，這樣可以有效節(jié)約索引空間厂镇，從而提高索引效率。
  多列索引和索引順序
  當(dāng)出現(xiàn)多個(gè)索引做相交操作時(shí)（多個(gè)AND條件）左刽，通常來(lái)說(shuō)一個(gè)包含所有相關(guān)列的索引要優(yōu)于多個(gè)獨(dú)立索引捺信。
  當(dāng)出現(xiàn)多個(gè)索引做聯(lián)合操作時(shí)（多個(gè)OR條件），對(duì)結(jié)果集的合并欠痴、排序等操作需要耗費(fèi)大量的CPU和內(nèi)存資源迄靠，特別是當(dāng)其中的某些索引的選擇性不高，需要返回合并大量數(shù)據(jù)時(shí)喇辽，查詢(xún)成本更高掌挚。所以這種情況下還不如走全表掃描。
  避免多個(gè)范圍條件
  覆蓋索引
  索引條目遠(yuǎn)小于數(shù)據(jù)行大小菩咨，如果只讀取索引吠式，極大減少數(shù)據(jù)訪問(wèn)量
  索引是有按照列值順序存儲(chǔ)的，對(duì)于I/O密集型的范圍查詢(xún)要比隨機(jī)從磁盤(pán)讀取每一行數(shù)據(jù)的IO要少的多
  使用索引掃描來(lái)排序
  避免冗余和重復(fù)索引
  刪除長(zhǎng)期未使用的索引
  特定類(lèi)型查詢(xún)優(yōu)化
  優(yōu)化COUNT()查詢(xún)
  如果要統(tǒng)計(jì)行數(shù)旦委，直接使用COUNT(*)奇徒，意義清晰，且性能更好缨硝。
  優(yōu)化關(guān)聯(lián)查詢(xún)
  確保ON和USING字句中的列上有索引摩钙。
  確保任何的GROUP BY和ORDER BY中的表達(dá)式只涉及到一個(gè)表中的列，這樣MySQL才有可能使用索引來(lái)優(yōu)化查辩。
  優(yōu)化LIMIT分頁(yè)
  優(yōu)化UNION

常見(jiàn)錯(cuò)誤理解與技巧

通常來(lái)說(shuō)把可為NULL的列改為NOT NULL不會(huì)對(duì)性能提升有多少幫助胖笛，只是如果計(jì)劃在列上創(chuàng)建索引，就應(yīng)該將該列設(shè)置為NOT NULL宜岛。
　　對(duì)整數(shù)類(lèi)型指定寬度长踊，比如INT(11)，沒(méi)有任何卵用萍倡。INT使用32位（4個(gè)字節(jié)）存儲(chǔ)空間身弊，那么它的表示范圍已經(jīng)確定，所以INT(1)和INT(20)對(duì)于存儲(chǔ)和計(jì)算是相同的列敲。
　　UNSIGNED表示不允許負(fù)值阱佛，大致可以使正數(shù)的上限提高一倍。比如TINYINT存儲(chǔ)范圍是-128 ~ 127戴而，而UNSIGNED TINYINT存儲(chǔ)的范圍卻是0 - 255凑术。
　　通常來(lái)講，沒(méi)有太大的必要使用DECIMAL數(shù)據(jù)類(lèi)型所意。即使是在需要存儲(chǔ)財(cái)務(wù)數(shù)據(jù)時(shí)淮逊，仍然可以使用BIGINT催首。比如需要精確到萬(wàn)分之一，那么可以將數(shù)據(jù)乘以一百萬(wàn)然后使用BIGINT存儲(chǔ)泄鹏。這樣可以避免浮點(diǎn)數(shù)計(jì)算不準(zhǔn)確和DECIMAL精確計(jì)算代價(jià)高的問(wèn)題郎任。
　　TIMESTAMP使用4個(gè)字節(jié)存儲(chǔ)空間，DATETIME使用8個(gè)字節(jié)存儲(chǔ)空間备籽。因而涝滴，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范圍小得多胶台，而且TIMESTAMP的值因時(shí)區(qū)不同而不同。
　　大多數(shù)情況下沒(méi)有使用枚舉類(lèi)型的必要杂抽，其中一個(gè)缺點(diǎn)是枚舉的字符串列表是固定的诈唬，添加和刪除字符串（枚舉選項(xiàng)）必須使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）缩麸。
　　schema的列不要太多铸磅。原因是存儲(chǔ)引擎的API工作時(shí)需要在服務(wù)器層和存儲(chǔ)引擎層之間通過(guò)行緩沖格式拷貝數(shù)據(jù)，然后在服務(wù)器層將緩沖內(nèi)容解碼成各個(gè)列杭朱，這個(gè)轉(zhuǎn)換過(guò)程的代價(jià)是非常高的阅仔。如果列太多而實(shí)際使用的列又很少的話，有可能會(huì)導(dǎo)致CPU占用過(guò)高弧械。
　　大表ALTER TABLE非常耗時(shí)八酒，MySQL執(zhí)行大部分修改表結(jié)果操作的方法是用新的結(jié)構(gòu)創(chuàng)建一個(gè)張空表，從舊表中查出所有的數(shù)據(jù)插入新表刃唐，然后再刪除舊表羞迷。尤其當(dāng)內(nèi)存不足而表又很大，而且還有很大索引的情況下画饥，耗時(shí)更久衔瓮。當(dāng)然有一些奇技技巧可以解決這個(gè)問(wèn)題，有興趣可自行查閱抖甘。

詳細(xì)優(yōu)化網(wǎng)址 http://www.reibang.com/p/d7665192aaaf?utm_campaign=haruki&utm_content=note&utm_medium=reader_share&utm_source=qq